Resource management
Aurora PostgreSQL manages four types of resources to run queries against foreign tables that reference your Iceberg and Parquet data.
-
Query memory: The working memory (from instance memory) that a query uses for hash tables, sorting, aggregation, and intermediate results, controlled by the
aurora_analytics.query_memparameter. For more information, see Query memory. -
Disk spilling: When a query exceeds its memory budget, Aurora PostgreSQL writes intermediate data to temporary files on local storage and then automatically deletes them when the query finishes. This allows most large queries to complete without failing, though queries with very large intermediate results might still exhaust the available spill space. For more information, see Disk spilling.
-
Worker threads: The CPU parallelism the engine uses to run a query. Aurora manages this automatically and scales it from the query's memory budget. For more information, see Worker threads.
-
On-disk read cache: Local storage (NVMe or Amazon EBS) that caches Amazon S3 data so repeated reads are served locally. For more information, see On-disk read cache.
This topic explains how each resource is allocated, how they interact, and how you can tune them for your workload.
Topics
Query memory
Aurora PostgreSQL allocates a per-query memory budget from instance memory for hash tables, sorting, aggregation buffers, and intermediate results during query execution. The aurora_analytics.query_mem parameter controls this budget, and it works as follows.
-
It sets the maximum memory a single query can use.
-
You can specify it as a string with a unit (for example,
'4GB'or'16GB') or as an integer in kilobytes (kB). -
The effective limit is capped at instance memory minus the memory reserved for PostgreSQL
shared_buffers. Settingquery_memabove that cap has no additional effect. -
When a query's intermediate results exceed its budget, Aurora PostgreSQL spills to temporary local storage so the query still completes instead of failing. For more information, see Disk spilling.
-
Its default value is derived from instance memory.
Memory budget across concurrent sessions
By default, shared_buffers reserves approximately two-thirds of instance memory. The remainder is shared by query_mem and PostgreSQL's other memory needs. Queries involving foreign tables do not use shared_buffers, which leaves the effective memory available for concurrent foreign table queries at roughly the following.
available_memory ≈ instance_memory − shared_buffers
If you run workloads that are predominantly foreign table queries, reducing shared_buffers frees more memory for query_mem and is often the most effective tuning step.
Aurora PostgreSQL includes built-in memory protection that cancels queries when system-wide memory pressure becomes critical. To reduce the risk of cancellations, do the following.
-
Reduce
shared_buffers. -
Lower
query_mem. -
Limit concurrent sessions.
Because actual memory consumption per query varies with the data and the plan, there is no fixed formula for the safe number of concurrent queries. Monitor AuroraAnalyticsMemoryUsage in Amazon CloudWatch and adjust the preceding settings based on observed memory pressure.
Disk spilling
When a query's intermediate results exceed query_mem, Aurora PostgreSQL spills data to temporary files on local storage. Due to the spill, the query completes slowly instead of failing with an out-of-memory error.
Common causes of spilling
The following are common causes of spilling.
-
Large
ORDER BYwithoutLIMIT. -
Hash joins between two large unfiltered tables.
-
GROUP BYwith high cardinality (millions of distinct groups).
Performance impact
Spilling lets a query finish even when its data is larger than memory, but it does not guarantee completion. Queries with very large intermediate results might still exhaust the available spill space. Because reading and writing to disk is slower than working in memory, a query that spills runs more slowly than one that stays in memory. If a query spills often, increase memory or reduce the data it processes.
Impact on other workloads
Spilling creates I/O contention on the same local storage used by the read cache. When multiple concurrent sessions spill simultaneously, the following can occur.
-
Disk space is shared: Spill files and the read cache both occupy local storage.
-
OLTP impact: On instances running mixed workloads, heavy spilling can create local storage I/O contention that indirectly affects local PostgreSQL operations.
The following table compares spilling, out-of-memory errors, and query cancellation.
| Scenario | What happens | Query completes? |
|---|---|---|
Intermediate results exceed query_mem |
Spills to local storage, query runs slowly | Yes |
Cannot allocate memory due to insufficient query_mem |
out of memory error |
No |
| System-wide memory pressure is critical | Query cancelled by memory protection | No |
| Spill files exhaust local storage | No space left on device error |
No |
Monitor the Amazon CloudWatch metric AuroraAnalyticsDiskSpillSize to detect when queries are routinely spilling, and consider increasing query_mem or optimizing the query.
Worker threads
Aurora PostgreSQL uses a multi-threaded execution engine for each query. Threads are used for both computation and cache I/O (fetching data from the on-disk cache or Amazon S3).
-
Query execution threads: Execute the query plan. Aurora PostgreSQL manages the number of these threads automatically, scaled from the query's memory budget.
-
Cache I/O threads: Read from the on-disk cache or download Amazon S3 data in parallel on cache misses. These are separate from query execution threads and are also managed internally.
Both kinds of threads are internal to the engine and managed by Aurora PostgreSQL. You do not configure either one.
The AuroraAnalyticsActiveThreads Amazon CloudWatch metric reports the active worker threads. For how to interpret it, see Interpreting the active thread count.
On-disk read cache
Aurora PostgreSQL caches data fetched from Amazon S3 on local storage to accelerate repeated access. The cache is sized automatically based on the instance's available memory and storage, and a larger instance provides a larger cache. On instances with local NVMe storage (such as db.r8gd) and using Aurora I/O-Optimized, the cache uses NVMe for higher performance. On Amazon EBS-only instances (such as db.r8g and db.r8i), the cache uses Amazon EBS storage.
On the first query, data is fetched from Amazon S3 in fixed-size blocks and stored in the read cache, and that first read's latency includes the Amazon S3 network round-trip. Subsequent queries read that data from local storage at NVMe or Amazon EBS speeds, which is significantly faster than reading from Amazon S3. A background worker monitors cache and disk usage and evicts the oldest (least-recently-accessed) blocks when thresholds are exceeded.
Note
The on-disk cache is cleared when a DB instance restarts or fails over.
Cache and data freshness
New Parquet files added to an existing Amazon S3 prefix are discovered and cached on the next query, and for Iceberg tables the cache never affects which snapshot you see. If you overwrite an existing Parquet file, an open connection can read stale data briefly because it holds a cached file handle. To see the updated data right away, reconnect. You do not need to clear the cache.
Cache management functions
Aurora PostgreSQL provides the following functions to manage the on-disk read cache.
| Function | Description | Required permission |
|---|---|---|
aurora_analytics_cache_size() |
Returns the current cache size in bytes. | All users. |
aurora_analytics_clear_cache() |
Clears the entire on-disk cache. | rds_superuser only. |
When to clear the cache
Consider clearing the cache in the following situations.
-
Disk space pressure: When the
FreeLocalStorageorFreeEphemeralStorageAmazon CloudWatch metrics are low and you need immediate disk space for query spills. -
Performance testing: When benchmarking cold-cache performance.
Memory protection (query cancellation under memory pressure)
Aurora PostgreSQL includes a built-in memory protection mechanism that prevents runaway queries from consuming all instance memory and forcing the DB instance to restart. When free instance memory drops low enough that continuing to allocate would risk the stability of the DB instance, Aurora PostgreSQL gracefully cancels an in-progress query to reduce pressure and keep the DB instance stable for all workloads. The threshold is managed by Aurora based on available instance memory. It is not a value you configure.
This protection is provided by the Aurora PostgreSQL improved memory management feature, which is enabled by default. For more information, see Improved memory management in Aurora PostgreSQL in the Amazon Aurora User Guide.
How it works
When total memory consumption by sessions running foreign table queries approaches the point where further allocation would risk DB instance stability, the mechanism cancels one or more currently running queries to reduce memory pressure. It acts on in-progress queries that are consuming memory, not on queries that have not started yet. A new query submitted after pressure is relieved runs normally. The cancelled query receives an error, but the DB instance remains stable and all other sessions continue unaffected.
When a query is cancelled, it returns the following error.
ERROR: [Aurora Analytics] Out of Memory Error: Failed to allocate memory for query execution. DETAIL: Transaction cancelled due to insufficient memory
How to reduce query cancellations
To reduce query cancellations, do the following.
-
Reduce per-session memory: Lower
query_memso each session uses less memory, allowing more headroom. -
Reduce concurrency: Fewer concurrent foreign table queries means less total memory pressure.
-
Scale up the instance: A larger DB instance provides more instance memory for the same workload.
Note
Improved memory management is controlled by the rds.enable_memory_management parameter (enabled by default). We recommend keeping it enabled. Disabling it removes the protection against workload-induced DB instance restarts from memory exhaustion. For more information, see Improved memory management in Aurora PostgreSQL in the Amazon Aurora User Guide.
Note
Queries run by users with the rds_superuser role are exempt from memory pressure cancellation.
