Monitoring and troubleshooting

Aurora PostgreSQL reports foreign table query activity through Amazon CloudWatch, SQL diagnostic functions, Database Insights, and the PostgreSQL log, the same tools you already use for Aurora PostgreSQL. Monitoring covers the metrics, wait events, and diagnostic queries for watching a workload; Troubleshooting covers common symptoms and how to resolve them.

Monitoring

This section describes the metrics, wait events, and diagnostic queries you use to monitor your analytics workload, and how to build dashboards from them. For specific symptoms, see Troubleshooting.

Aurora PostgreSQL reports its analytics activity through four channels. Each channel answers a different question, and most investigations use more than one.

Amazon CloudWatch metrics

The following Amazon CloudWatch metrics are published for Aurora Analytics.

Monitoring local storage performance

Aurora PostgreSQL uses local storage for the on-disk read cache and for query spill files, such as sorts and hash tables that exceed query_mem. Which metrics report that I/O depends on how local storage is attached to the instance class, so check your class first and then use the matching set of metrics below.

Instances with Amazon EBS storage only

Classes such as db.r8g, db.r7g, and db.r7i have no instance store, so the read cache and spill files share the Amazon EBS-backed volume with the rest of the database. Analytical I/O therefore competes with normal PostgreSQL I/O, and it is reported by the general storage metrics rather than by a set of metrics specific to Analytics.

Instances with local NVMe storage

Classes such as db.r8gd and db.r6gd include an NVMe instance store, and Aurora PostgreSQL places the read cache and spill files there instead of on the Amazon EBS volume. This keeps analytical I/O off the database volume and gives the cache much lower read latency. The NVMe device reports through its own set of ephemeral storage metrics, so ReadIOPS, ReadLatency, and DiskQueueDepth no longer reflect analytical I/O on these classes.

Standard Aurora metrics relevant to Analytics

Beyond the metrics specific to analytics, the following standard Aurora metrics are useful for monitoring an analytical workload.

SQL-based diagnostic functions

Aurora PostgreSQL provides SQL-callable functions that report counters Amazon CloudWatch cannot attribute: exact bytes, spill, and thread counts for one query or one backend. Start with the following table to choose a function, then see the SQL functions reference for its columns.

All byte columns report raw bytes, so wrap them in pg_size_pretty() for readable output. For the syntax, permissions, and worked examples of each function, see SQL functions reference.

Sample queries and output

To find queries reading from Amazon S3 instead of the cache, run the following query.

-- Optional: enable the detailed columns, such as CPU time
SET aurora_analytics.track_detailed_metrics = true;

-- Run your queries, then check statistics
SELECT SUBSTR(query, 1, 45) AS query,
       calls,
       pg_size_pretty(analytics_cache_hit_bytes) AS cache_hits,
       pg_size_pretty(analytics_remote_read_bytes) AS s3_reads,
       pg_size_pretty(analytics_total_disk_spill) AS spill,
       round(analytics_total_cpu_time::numeric, 2) AS cpu_ms
FROM aurora_analytics_stat_statements()
WHERE analytics_cache_hit_bytes > 0 OR analytics_remote_read_bytes > 0
ORDER BY analytics_remote_read_bytes DESC;

Interpret the results as follows.

To attribute memory, threads, and spill to a backend, run the following query.

SELECT pid,
       pg_size_pretty(current_memory_usage) AS mem_used,
       active_threads,
       pg_size_pretty(disk_spill_size) AS spill
FROM aurora_analytics_stat_resource_usage()
WHERE current_memory_usage > 0
ORDER BY current_memory_usage DESC;

Join the pid column to pg_stat_activity to see which query each backend is running. Use this when AuroraAnalyticsMemoryUsage is high and you need to know which session is responsible.

To view instance-wide Amazon S3 and cache activity, run the following query.

SELECT s3_head_request_count,
       s3_get_request_count,
       pg_size_pretty(s3_cache_hit_bytes) AS cache_hit,
       pg_size_pretty(s3_remote_read_bytes) AS s3_read
FROM aurora_analytics_instance_metrics();

To check cache size, run the following query.

SELECT pg_size_pretty(aurora_analytics_cache_size()) AS cache_size;

To find active analytical queries, run the following query.

WITH analytics_tables AS (
    SELECT c.relname
    FROM pg_foreign_table ft
    JOIN pg_class c ON c.oid = ft.ftrelid
    JOIN pg_foreign_server fs ON fs.oid = ft.ftserver
    WHERE fs.srvname = 'aurora_analytics_server'
)
SELECT a.pid, a.usename, a.datname,
       NOW() - a.query_start AS runtime, a.state,
       LEFT(a.query, 100) AS query_preview
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND EXISTS (SELECT 1 FROM analytics_tables t WHERE a.query ILIKE '%' || t.relname || '%')
ORDER BY a.query_start;

Interpreting the active thread count

The AuroraAnalyticsActiveThreads Amazon CloudWatch metric and the active_threads column of aurora_analytics_stat_resource_usage() report the number of analytics worker threads that are actively executing at the moment of measurement. This count is often much higher than your number of concurrent sessions, which is expected. The active thread count includes two kinds of worker threads.

Only threads that are actively doing work are counted. A thread that is idle and waiting for work is not included, so the count rises and falls continuously as a query runs.

Because both kinds of threads are counted together, the total can be much higher than the number of concurrent queries. A handful of concurrent queries reading from Amazon S3 on cache misses can produce several hundred active threads. This is normal. A persistently high count driven by Amazon S3 I/O threads indicates a low cache hit ratio, so consider warming or scaling the cache.

These worker threads are internal to the analytics engine and do not appear in pg_stat_activity, which shows only your PostgreSQL sessions. This is why a small number of sessions can drive high CPU usage: a single session can run dozens or hundreds of active threads.

The active thread count answers "why is CPU high with only a few sessions?", while the Extension:AuroraAnalyticsExecute wait event tells you how many sessions are waiting on the analytics engine. For more information, see Interpreting the AuroraAnalyticsExecute wait event.

Monitoring xmin horizon on reader instances

Long-running analytical queries on reader instances hold an xmin snapshot open, which can prevent autovacuum on the writer from reclaiming dead tuples. Running analytical workloads on a dedicated reader isolates this impact; for more information, see Using a dedicated reader instance for analytics.

Set statement_timeout to bound how long a single query can run. Like other PostgreSQL settings, you can set it at the session, role, database, or cluster parameter group level; use the lowest level that fits. Set it per session for a single connection.

SET statement_timeout = 'XX';

Or set it as a default for the analytics role.

ALTER ROLE analytics_user SET statement_timeout = 'XX';

Database Insights

Where Amazon CloudWatch tells you that the instance is under pressure, Database Insights tells you which sessions and which SQL are responsible. Foreign table queries appear there like any other PostgreSQL query, so you use the same average active sessions (AAS) chart, the same Top SQL list, and the same wait event breakdown.

Aurora PostgreSQL adds one wait event for foreign table queries.

To identify top analytical queries, do the following.

Interpreting the AuroraAnalyticsExecute wait event

When a query references a foreign table, the PostgreSQL backend hands execution to the embedded analytics engine and blocks until the engine returns results. During this time, the backend reports the Extension:AuroraAnalyticsExecute wait event. In Database Insights, this appears in the average active sessions (AAS) chart as Extension:AuroraAnalyticsExecute. Keep the following in mind.

Tracking query duration trends

Use Database Insights to track whether average query time is stable or degrading over time. A sudden increase might indicate the following.

Logging

Aurora PostgreSQL can write analytics engine messages to the PostgreSQL error log. Logging is a diagnostic tool rather than a monitoring channel: it produces detail about individual operations, not time series you can trend or alarm on. Two parameters control it. Set them in a DB cluster parameter group to cover the whole workload, or with SET in a single session to scope the output to one investigation. The session-scoped SET requires the rds_superuser role.

Messages go to the PostgreSQL error log. Retrieve it in one of three ways.

Key metrics reference

The following table provides a quick reference for analytics health. Two rows depend on your instance class, so check which storage the class uses before you build alarms from this table. On NVMe classes, FreeLocalStorage does not track the volume that holds the read cache and spill files, so an alarm on it will stay quiet while the instance store fills.

Troubleshooting

This section describes specific symptoms, their causes, and how to resolve them. For the metrics, wait events, and diagnostic queries used to watch a workload, see Monitoring.

Setup and configuration issues

The following are common setup and configuration issues.

Extension not available

Symptom: CREATE EXTENSION aurora_analytics fails with the following error.

ERROR:  extension "aurora_analytics" is not available

Causes and solutions: This error means you are running a version of Aurora PostgreSQL that does not support the aurora_analytics extension. To use this feature, upgrade to Aurora PostgreSQL 17.11 or higher, or 18.6 or higher. Use the following table to identify the cause and resolve it.

To diagnose, run the following queries. The second returns no rows on an unsupported version.

SELECT version();

SELECT * FROM pg_available_extensions WHERE name = 'aurora_analytics';

Feature not enabled

Symptom: A query against a foreign table fails with the following error. This is distinct from the extension-not-available error: the extension is installed successfully, but queries are rejected because the feature is turned off at the DB cluster level.

ERROR:  aurora_analytics is not enabled
HINT:  Set aurora_analytics.enabled to true in the parameter group and retry the query.

Cause: aurora_analytics.enabled is set to off.

Solution: Set aurora_analytics.enabled to on in your DB cluster parameter group. As a dynamic parameter, the change is applied immediately without a reboot.

To diagnose, run the following query.

SHOW aurora_analytics.enabled;

Permission denied to create extension

Symptom: Creating the extension fails with the following error.

ERROR:  permission denied to create extension "aurora_analytics"

Solution: To create the extension, you must have the rds_superuser role, or be a user that has been granted the rds_extension role and delegated this extension. Being the database owner is not sufficient. Grant the rds_superuser role and retry.

GRANT rds_superuser TO your_user;
CREATE EXTENSION aurora_analytics;

Alternatively, to allow a non-superuser to create the extension, grant the rds_extension role and delegate this specific extension. Run the following as rds_superuser.

GRANT rds_extension TO your_user;
ALTER USER your_user SET rds.allowed_delegated_extensions = 'aurora_analytics';

The rds_extension grant alone is not sufficient. Without aurora_analytics in rds.allowed_delegated_extensions, CREATE EXTENSION fails with "This extension is not specified in \"rds.allowed_delegated_extensions\"". For more information, see Using Amazon RDS delegated extension support for PostgreSQL.

IAM role not active

Symptom: Foreign table queries fail with access denied errors, or the role status shows Pending instead of Active.

Diagnostic: Run the following command to check the role association status.

aws rds describe-db-clusters \
  --db-cluster-identifier <cluster-id> \
  --query "DBClusters[0].AssociatedRoles"

Solution: Use the following table to act on the status.

Amazon S3 and data access errors

The following are common Amazon S3 and data access errors.

Access denied reading Amazon S3

Symptom: A foreign table query fails with the following error.

ERROR:  [Aurora Analytics] HTTP 403 Forbidden

Causes and solutions: Check the following.

Diagnostic: Run the following query to inspect the foreign table options.

SELECT ft.ftrelid::regclass, ftoptions
FROM pg_foreign_table ft
WHERE ft.ftrelid::regclass::text = 'ft_orders';

AWS Glue timeout or connection error

Symptom: A query fails with the following error.

ERROR:  [Aurora Analytics] Glue GetTable API failed: curlCode: 28, Timeout was reached; Connection timed out after 301 milliseconds

Cause: No AWS Glue VPC interface endpoint exists, or a security group is blocking TCP port 443.

To resolve this issue: Do the following.

AWS Glue table not found

Symptom: A query fails with the following error.

ERROR:  [Aurora Analytics] Glue GetTable API failed: EntityNotFoundException: Database not found: <name>

-- or

ERROR:  [Aurora Analytics] Glue GetTable API failed: EntityNotFoundException: Table not found: <name>

Causes and solutions: Use the following table to identify the cause and resolve it.

Diagnostic: Run the following command.

aws glue get-table \
  --database-name <db> \
  --name <table> \
  --region <region>

Wrong format specified

Symptom: A query fails with the following error.

ERROR:  [Aurora Analytics] failed to fetch column information...
HINT:  If the S3 location contains Parquet files, verify the foreign table was created with format 'parquet' instead of 'iceberg'.

Solution: Recreate the foreign table with the correct format.

DROP FOREIGN TABLE ft_my_table;

CREATE FOREIGN TABLE ft_my_table ()
SERVER aurora_analytics_server
OPTIONS (
    location 's3://my-bucket/data/',
    format 'parquet',
    region 'us-east-1'
);

DNS resolution failure

Symptom: A query fails with the following error.

ERROR:  [Aurora Analytics] Glue GetTable API failed: curlCode: 6, Could not resolve host: glue.<region>.amazonaws.com

Cause: Wrong Region in the AWS Glue ARN, or the VPC endpoint is not available in that Region.

Solution: Do the following.

Query performance issues

The following are common query performance issues.

Queries are slow

Symptom: Queries take longer than expected.

Causes and solutions: Use the following table to identify why queries are slow and how to respond.

Useful queries: To check whether queries are served from the cache or from Amazon S3, and whether they spill, run the following query.

SET aurora_analytics.track_detailed_metrics = true;
-- Run your query, then check per-query stats
SELECT query, analytics_cache_hit_bytes, analytics_remote_read_bytes, analytics_total_disk_spill
FROM aurora_analytics_stat_statements()
ORDER BY analytics_remote_read_bytes DESC;

If analytics_remote_read_bytes is high relative to analytics_cache_hit_bytes, the cache is cold or the working set exceeds cache capacity.

To check how many analytical sessions are active, run the following query.

SELECT COUNT(*) FROM pg_stat_activity a
WHERE a.state = 'active'
  AND EXISTS (
      SELECT 1 FROM pg_foreign_table ft
      JOIN pg_class c ON c.oid = ft.ftrelid
      JOIN pg_foreign_server fs ON fs.oid = ft.ftserver
      WHERE fs.srvname = 'aurora_analytics_server'
        AND a.query ILIKE '%' || c.relname || '%'
  );

Out of memory errors

Symptom: Two errors share the same ERROR line but differ in the HINT or DETAIL. In the first, the query needs more memory.

ERROR:  [Aurora Analytics] Out of Memory Error: Failed to allocate memory for query execution.
HINT:  Possible solution: Increasing the query mem (SET aurora_analytics.query_mem='...GB')

In the second, the DB instance is under instance-wide memory pressure.

ERROR:  [Aurora Analytics] Out of Memory Error: Failed to allocate memory for query execution.
DETAIL:  Transaction cancelled due to insufficient memory

Cause: Distinguish the two cases by the accompanying line.

Confirm the cause with metrics: Check the following.

To resolve this issue: Choose the path that matches the message. If the error carries a HINT (query memory too low), do the following.

If the error carries a DETAIL (instance memory pressure), do the following.

Out of storage due to query spills

Symptom: A query fails with the following error.

ERROR:  [Aurora Analytics] could not write to temporary file: No space left on device

Cause: When a query's intermediate results exceed query_mem, the engine spills data to temporary local storage. If multiple concurrent queries are spilling simultaneously, or a single query spills a very large volume of data, local storage can be exhausted. query_mem is a per-query budget rather than an instance-wide pool, which means each concurrent query can spill its own overflow, and spill files share local storage with the read cache. Together these are why concurrency, rather than any single large query, is the usual trigger for exhaustion.

Diagnostic: Watch AuroraAnalyticsDiskSpillSize in Amazon CloudWatch for instance-wide spill volume, then attribute it to a backend.

SELECT pid,
       pg_size_pretty(disk_spill_size) AS spill,
       pg_size_pretty(current_memory_usage) AS mem_used,
       active_threads
FROM aurora_analytics_stat_resource_usage()
WHERE disk_spill_size > 0
ORDER BY disk_spill_size DESC;

To resolve this issue: Do the following.

Foreign table issues

The following are common foreign table issues.

Schema mismatch

Symptom: A query fails with the following error, or returns incorrect data types.

ERROR:  column "column_name" does not exist

Causes: The column definitions do not match the actual Parquet or Iceberg schema, or the source schema changed after the foreign table was created.

To resolve this issue: If the foreign table already exists and only its column definitions have drifted from the source, refresh it in place with aurora_analytics_refresh_foreign_table, which reconciles the column definitions without dropping and recreating the table (preserving grants and dependent objects). For the full syntax and permissions, see SQL functions reference. The apply_changes argument is required: pass false to preview the changes without modifying the table, or true to apply them. Run a dry run first, then apply.

-- Preview the schema changes
SELECT aurora_analytics_refresh_foreign_table('ft_my_table', false);
-- Apply them
SELECT aurora_analytics_refresh_foreign_table('ft_my_table', true);

Alternatively, recreate the foreign table with schema inference.

DROP FOREIGN TABLE ft_my_table;
CREATE FOREIGN TABLE ft_my_table ()
SERVER aurora_analytics_server
OPTIONS (location 's3://...', format 'parquet', region 'us-east-1');
-- Check inferred schema
\d ft_my_table

Unsupported column type error

Symptom: Creating a foreign table fails with the following error.

ERROR:  unsupported data type for column "complex_column"

Solution: Enable skipping of unsupported columns, then recreate the foreign table. The unsupported columns are skipped.

SET aurora_analytics.skip_unsupported_columns = true;
-- Recreate the table, unsupported columns are skipped
DROP FOREIGN TABLE ft_my_table;
CREATE FOREIGN TABLE ft_my_table ()
SERVER aurora_analytics_server
OPTIONS (location 's3://...', format 'parquet', region 'us-east-1');

Connection and session issues

The following are common connection and session issues.

Long-running queries blocking other work

Symptom: A few analytical queries consume most of the instance's resources, slowing down performance. In Amazon CloudWatch, this shows up as sustained high CPUUtilization together with a high AuroraAnalyticsActiveThreads count, even though only a few sessions are active. Those sessions report the Extension:AuroraAnalyticsExecute wait event in the average active sessions (AAS) chart. A few sessions can drive high CPU because each analytical query runs on many worker threads; see Interpreting the active thread count.

Diagnostic: Run the following query to find the long-running analytical queries.

WITH analytics_tables AS (
    SELECT c.relname
    FROM pg_foreign_table ft
    JOIN pg_class c ON c.oid = ft.ftrelid
    JOIN pg_foreign_server fs ON fs.oid = ft.ftserver
    WHERE fs.srvname = 'aurora_analytics_server'
)
SELECT a.pid, a.usename, a.datname,
       NOW() - a.query_start AS runtime, a.state,
       LEFT(a.query, 100) AS query_preview
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND EXISTS (SELECT 1 FROM analytics_tables t WHERE a.query ILIKE '%' || t.relname || '%')
ORDER BY a.query_start;

Solution: Set a statement_timeout for analytical users to bound query runtime.

ALTER USER analyst SET statement_timeout = '30min';

To stop a query that is already running, cancel or terminate its backend.

SELECT pg_cancel_backend(<pid>);     -- cancel the query
SELECT pg_terminate_backend(<pid>);  -- terminate the session

Connection storms

Symptom: DatabaseConnections spikes suddenly, and queries slow down or fail.

Solution: Implement connection pooling (for example, Amazon RDS Proxy) to manage analytical connections, and set appropriate statement_timeout values to prevent runaway queries.

Global Database switchover and failover

The following are common issues after a Global Database switchover or failover.

Cross-Region Amazon S3 latency after switchover

Symptom: After a Global Database switchover or failover, analytical queries become slower.

Cause: The foreign tables still point to Amazon S3 buckets in the original Region, so the new primary reads cross-Region.

Confirm the cause with metrics: Elevated AuroraAnalyticsRemoteRead and AuroraAnalyticsS3GetRequest after the switchover, especially when queries that previously served from cache now show sustained remote reads, indicate that the new primary is fetching from the original Region rather than locally. A low AuroraAnalyticsCacheHitRatio on the new primary is expected until its cache warms.

These volume metrics alone don't distinguish cross-Region reads from a cold cache re-warming locally, because both raise remote-read volume right after a switchover. Amazon S3 request latency is what separates them: a spike in per-request latency points to cross-Region reads, while a local cold cache re-warms at normal latency. Aurora PostgreSQL doesn't emit a remote-read latency metric, so to measure this, enable Amazon S3 Request Metrics on the bucket and watch TotalRequestLatency (average and p99) in the AWS/S3 Amazon CloudWatch namespace. Amazon S3 Request Metrics are not enabled by default.

To resolve this issue: Do the following.

Missing IAM role association in the secondary Region

Symptom: Queries fail with the following error.

ERROR:  credential to access AWS service is unavailable
HINT:  Please attach IAM role with proper permission policy to this cluster with the feature name "AuroraAnalytics".

Cause: The IAM role was never associated with this Region's cluster.

Solution: Associate the IAM role with the feature name AuroraAnalytics, then wait for its status to become Active.

IAM role has no permissions in the secondary Region

Symptom: For Parquet in Amazon S3, queries fail with the following error.

ERROR:  [Aurora Analytics] Executor Error: ... HTTP 403 Forbidden
        AccessDenied: User: arn:aws:sts::<account>:assumed-role/AuroraAnalyticsRole/...
        is not authorized to perform: s3:GetObject on resource: "arn:aws:s3:::<bucket>/<key>"

For Iceberg through AWS Glue, queries fail with the following error.

ERROR:  [Aurora Analytics] Glue GetTable API failed: User: ... is not authorized to perform:
        glue:GetTable on resource: arn:aws:glue:<region>:<account>:catalog

Cause: The role is associated, but its policy lacks the required permissions.

Solution: Attach a policy that grants the required actions. The error names the exact action and resource that was denied.

No network path to Amazon S3 or AWS Glue in the secondary Region

Symptom: For Amazon S3, queries fail with the following error.

ERROR:  [Aurora Analytics] Executor Error: ... Timeout was reached error for HTTP HEAD to
        'https://<bucket>.s3.<region>.amazonaws.com/<key>'

For AWS Glue, queries fail with the following error.

ERROR:  [Aurora Analytics] Glue GetTable API failed: curlCode: 28,
        Timeout was reached; Details: Connection timed out after 300 milliseconds

Cause: There is no VPC endpoint or NAT path in this Region's VPC.

Solution: Create an Amazon S3 gateway endpoint, an AWS Glue interface endpoint, and an AWS STS interface endpoint.

Enabling detailed logging

For transient or hard-to-reproduce issues, enable verbose logging temporarily.

-- As rds_superuser
SET aurora_analytics.enable_logging = true;
SET aurora_analytics.logging_level = 'DEBUG1';

-- Reproduce the issue
SELECT * FROM ft_problematic_table WHERE ...;

-- Reset logging level after investigation
RESET aurora_analytics.logging_level;
RESET aurora_analytics.enable_logging;

Analytics engine diagnostic messages are written to the PostgreSQL error log. For the parameter reference and the three ways to retrieve the log, see Logging.