PostgreSQL 18 on Amazon Aurora and Amazon RDS: Performance enhancements

June 20, 2026

Managing query performance across composite indexes, diagnosing memory spills in materialized Common Table Expressions (CTEs), and upgrading major versions without plan regressions are common hurdles for PostgreSQL users. The release of PostgreSQL 18 directly addresses these challenges with several enhancements, including skip scan optimization that eliminates redundant indexes, improved EXPLAIN functionality that reveals storage behavior, and optimizer statistics that persist through major version upgrades. Users can explore these features now on both Amazon Aurora PostgreSQL-Compatible Edition and Amazon Relational Database Service (Amazon RDS) for PostgreSQL.

This article is the first in a two-part series that delves into the key features of PostgreSQL 18, focusing specifically on performance enhancements such as skip scan optimization for multicolumn indexes, enriched EXPLAIN output, automatic removal of unnecessary self-joins, and various vacuum and autovacuum improvements that ensure efficient database operation. For further insights, please refer to Part 2, which will cover security, monitoring, developer, and logical replication enhancements. Additional details can be found in the PostgreSQL 18 release notes.

Prerequisites

While this post can be read without executing any commands, those interested in trying the examples will need to meet the following prerequisites:

Create an Aurora PostgreSQL cluster or RDS for PostgreSQL instance if one does not already exist. Instructions can be found in the following links: Create an Amazon Aurora PostgreSQL-Compatible DB cluster or Create a PostgreSQL DB instance.
Create an Amazon Elastic Compute Cloud (Amazon EC2) instance to install the PostgreSQL client for accessing the Aurora PostgreSQL or RDS for PostgreSQL instance. For guidance, see Create your EC2 resources and launch your EC2 instance. Alternatively, you can set up connectivity between your RDS database and EC2 compute instance in 1-click.
Install the PostgreSQL client. On Amazon Linux 2023, the following commands can be used to download the psql command line tool:
```
sudo dnf install -y gcc readline-devel libicu-devel zlib-devel openssl-devel make bison flex perl-core
```
Download the PostgreSQL 18 source code, replacing 18.x with the current minor version (for example, 18.4):
```
wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz
```
Extract the source:
```
tar -xvzf postgresql-18.x.tar.gz
```
Navigate to the source directory:
```
cd postgresql-18.x
```
Configure the build (set bindir to /usr/bin for system-wide installation):
```
./configure --bindir=/usr/bin --with-openssl
```
Build and install only the client binaries, libraries, and headers:
```
sudo make -C src/bin install
sudo make -C src/include install
sudo make -C src/interfaces install
```
Verify the installation:
```
psql --version
```

Skip scan optimization for multicolumn indexes

PostgreSQL 18 introduces skip scan optimization, enhancing the efficiency of multicolumn B-tree indexes when leading columns are not specified in the WHERE clause. This feature significantly improves query performance in scenarios involving composite indexes where queries do not always filter the leading columns.

Understanding the skip scan optimization

In earlier PostgreSQL versions, a multicolumn index was effectively utilized only when the query included the leading column(s) in the WHERE clause. For instance, with an index on (status, created_date), a query filtering solely on created_date could not leverage the index. The skip scan functionality allows PostgreSQL to skip through the distinct values of the leading column(s) and utilize the index for the subsequent columns, effectively treating the multicolumn index as multiple single-column indexes.

Example scenario

To illustrate the skip scan optimization, let’s create a table with a multicolumn index:

-- Create a table for customer orders
CREATE TABLE customer_orders (
    order_id serial PRIMARY KEY,
    status varchar(20),
    created_date date,
    customer_id integer,
    amount numeric(10,2)
);

-- Create a multicolumn index
CREATE INDEX idx_orders_status_date ON customer_orders(status, created_date);

-- Insert sample data with a few distinct status values
INSERT INTO customer_orders (status, created_date, customer_id, amount)
SELECT
    CASE (random() * 4)::integer
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'processing'
        WHEN 2 THEN 'shipped'
        WHEN 3 THEN 'delivered'
        ELSE 'cancelled'
    END,
    current_date - (random() * 365)::integer,
    (random() * 10000)::integer,
    (random() * 1000)::numeric(10,2)
FROM generate_series(1, 1000000);

-- Analyze the table
ANALYZE customer_orders;

Next, we can execute a query that filters only on created_date (omitting the leading column):

-- Query filtering on created_date only
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM customer_orders
WHERE created_date = '2026-03-15';

PostgreSQL 18 output (with skip scan):

Bitmap Heap Scan on customer_orders  (cost=47.66..5422.68 rows=2726 width=27) (actual time=0.581..3.223 rows=2659 loops=1)
   Recheck Cond: (created_date = '2026-03-15'::date)
   Heap Blocks: exact=2229
   Buffers: shared hit=2266
   ->  Bitmap Index Scan on idx_orders_status_date  (cost=0.00..46.98 rows=2726 width=0) (actual time=0.305..0.305 rows=2659 loops=1)
         Index Cond: (created_date = '2026-03-15'::date)
         Index Searches: 11
         Buffers: shared hit=37
Planning Time: 0.065 ms
Execution Time: 3.374 ms

In contrast, PostgreSQL 17 and earlier would likely resort to a sequential scan or necessitate a separate index on created_date:

PostgreSQL 17 output (without skip scan):

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

When skip scan is beneficial

Skip scan optimization proves most effective under the following conditions:

The leading columns exhibit low cardinality (few distinct values).
The query filters on non-leading columns.
The selectivity of the non-leading column filter is high.

Performance comparison

In testing conducted with Aurora PostgreSQL 17.10 and Aurora PostgreSQL 18.4, both on db.r6g.large with 1 million rows and 5 distinct values in the leading status column (warm buffer cache), the performance was as follows:

Without skip scan (Aurora PostgreSQL 17), the planner could not utilize the composite index on (status, created_date) due to the absence of the leading column in the WHERE clause, resulting in a Parallel Seq Scan that read all 7,353 blocks and completed in 73.2 ms.

With skip scan (Aurora PostgreSQL 18), the planner effectively employed the existing composite index, performing 11 index searches, reading only 2,266 blocks, and finishing in 3.4 ms. This represents a remarkable 21x improvement without necessitating any additional index.

The skip scan functionality offers performance comparable to a dedicated single-column index while avoiding the storage overhead and maintenance costs associated with maintaining one.

Enhanced EXPLAIN

PostgreSQL 18 introduces significant enhancements to the EXPLAIN command, making query performance analysis more intuitive and comprehensive. These improvements provide deeper insights into query execution, resource usage, and optimization opportunities, enabling developers and database administrators to identify performance bottlenecks more effectively.

Automatic buffer statistics in EXPLAIN ANALYZE

One notable usability enhancement in PostgreSQL 18 is that EXPLAIN ANALYZE now includes buffer statistics by default, eliminating the need to explicitly specify the BUFFERS option. This change addresses a long-standing issue where critical I/O information was obscured unless the BUFFERS flag was added.

Previously, analyzing query performance required the following syntax:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

Without the BUFFERS option, users would see execution time and row counts but miss vital information regarding cache hits, disk reads, and I/O patterns. In PostgreSQL 18, buffer statistics are included automatically:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

The output now displays:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

The buffer statistics immediately reveal that most data was served from cache (18,234 shared buffer hits) with only 1,245 blocks read from disk. This insight helps determine whether performance issues arise from I/O bottlenecks or other factors.

Memory and disk usage for materialized nodes

PostgreSQL 18 introduces visibility into the storage of materialized nodes in query plans. When the executor materializes a CTE for repeated access, the plan now indicates whether the intermediate results were stored in memory or spilled to disk, along with the peak storage consumed. This information was previously invisible in PostgreSQL 17 and earlier, requiring users to estimate based on work_mem settings and row width estimates.

Example 1: Memory storage (default)

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

The line to focus on is Storage: Memory Maximum Storage: 296kB on the CTE Scan node, indicating that the entire materialized result set, comprising 4,948 rows, was held in RAM with a peak footprint of 296kB. No temporary files were created, and no disk I/O was needed beyond reading the source table from shared buffers.

Example 2: Disk storage (low work_mem)

Now, let’s observe what occurs when we artificially constrain work_mem so the materialized CTE can no longer fit in memory:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

Notice the differences. Storage: Disk replaces Storage: Memory, and several new indicators appear. temp written=20 indicates temporary file I/O occurred, while I/O Timings: temp write=0.127 quantifies the time spent writing to those temporary files. Execution time increased from 3.057 ms to 3.268 ms, which may seem modest here due to the small dataset, but on larger result sets, the penalty from disk spills can compound significantly.

Index searches metric in EXPLAIN ANALYZE

PostgreSQL 18 introduces a new metric in EXPLAIN ANALYZE output: Index Searches. This metric indicates how many times the database traversed the index tree during query execution, providing valuable insights into index usage patterns.

What is the Index Searches counter?

The Index Searches counter appears in Index Scan, Bitmap Index Scan, and Index-Only Scan nodes. It reports the total number of index tree traversals across all node executions and loops.

Example: IN clause with multiple values

Consider a query searching for products with specific prices:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

Notice Index Searches: 4 in the output. The query searches for four different price values, and PostgreSQL performed four separate index tree traversals, one for each value in the IN clause. Each search starts from the index root and navigates to the appropriate leaf page.

Why this matters

Before PostgreSQL 18, users could see that an index was utilized but lacked insight into how many times the executor descended into the tree. Now, this information is readily available. A high search count on an IN clause indicates that each value triggered a separate root-to-leaf traversal. If those values are contiguous, rewriting the predicate as a BETWEEN range may reduce the number of searches. In nested loop joins, this metric reveals the total searches across all loop iterations, allowing for quick assessment of whether the inner index is being excessively accessed and whether a different join strategy would be more cost-effective.

Automatic removal of unnecessary self-joins

PostgreSQL 18 introduces Self-Join Elimination (SJE), a query planner optimization that automatically detects and removes inner joins of a table to itself when the join is likely redundant. This applies to SELECT queries where the planner can safely determine that one side of the self-join contributes no additional rows or columns to the result and can eliminate it entirely.

When a self-join is unnecessary

A self-join can be eliminated when two conditions are met:

The join condition is on a unique or primary key column, meaning at most one matching row per outer row, thus preventing duplicates or filtering rows.
No columns from the inner table alias are referenced in the SELECT list or WHERE clause that could not be satisfied by the outer alias alone.

When these conditions are satisfied, the planner rewrites the query to scan the table once, eliminating the join entirely.

Example: ORM-generated self-join

Self-joins on primary keys are commonly generated by Object-Relational Mappers (ORMs) and query builders that compose queries from reusable fragments without awareness of redundancy. Consider an employees table with a primary key on id:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

A query joining the table to itself on the primary key:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

PostgreSQL 17 and earlier would execute a real join:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

PostgreSQL 18 eliminates the join, performing a single scan:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

The planner recognizes that joining employees to itself on the primary key id is redundant. Each row matches exactly one row in the other alias, which is the same physical row. The join is rewritten to a simple scan.

With a WHERE clause

SJE also applies when additional filters are present:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

Again, a single scan occurs. The self-join is eliminated, and the filter is applied directly.

When self-join elimination does not apply

SJE requires a unique or primary key join condition. A self-join on a non-unique column, such as finding colleagues in the same department, cannot be eliminated because multiple rows can match:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

In this case, the join is preserved. The dept column is not unique, so one employee can match many colleagues, and the join genuinely alters the result set.

Scope: SELECT only

The PostgreSQL 18 implementation of SJE applies solely to SELECT queries. Self-joins in UPDATE, DELETE, and MERGE statements are not eliminated. This decision is intentional, as Data Manipulation Language (DML) queries involve EvalPlanQual (EPQ) re-evaluation for concurrent row updates, where the two table aliases may need to reference different row versions. Eliminating the join in that context could yield incorrect results. The PostgreSQL community has conservatively scoped the initial implementation, with DML support planned for a future release.

Fine-tuned autovacuum control with autovacuum_vacuum_max_threshold

PostgreSQL 18 introduces autovacuum_vacuum_max_threshold, a new parameter that caps the number of dead tuples that can accumulate before autovacuum triggers a VACUUM, regardless of table size. This enhancement addresses a long-standing issue with very large tables, where the scale-factor-based formula could delay autovacuum for an extended period.

The problem with large tables

Autovacuum determines when to vacuum a table by comparing the number of updated or deleted tuples against a threshold calculated as:

tar -xvzf postgresql-18.x.tar.gz

With the defaults (threshold = 50, scale_factor = 0.2), this approach works well for small and medium tables. However, for very large tables, the formula can yield thresholds that permit substantial bloat to accumulate before autovacuum activates:

Table size	Dead tuples needed to trigger autovacuum (before PG18)
1 million rows	200,050
100 million rows	20,000,050
500 million rows	100,000,050
1 billion rows	200,000,050

For a table with 1 billion rows, autovacuum would wait for 200 million dead tuples before executing. This can lead to significant table bloat, degraded query performance, and increased risk of transaction ID wraparound pressure.

Prior to PostgreSQL 18, the workaround involved manually setting autovacuum_vacuum_scale_factor = 0 and autovacuum_vacuum_threshold = N for each table, necessitating the identification of every large table and the maintenance of those settings as tables grew.

How autovacuum_vacuum_max_threshold works

PostgreSQL 18 modifies the formula to:

tar -xvzf postgresql-18.x.tar.gz

The new parameter acts as a ceiling. When the scale-factor calculation exceeds the max threshold, autovacuum triggers at the cap instead. The default is 100,000,000 tuples, ensuring that no table will accumulate more than 100 million dead tuples before autovacuum runs, regardless of its size.

Configuring in Amazon RDS for PostgreSQL and Aurora PostgreSQL

autovacuum_vacuum_max_threshold is a dynamic parameter (sighup-level), allowing changes in your parameter group to be applied without a reboot.

Amazon RDS for PostgreSQL: Modify the DB instance parameter group.

Aurora PostgreSQL: Modify the DB cluster parameter group (applies to all instances in the cluster).

In both cases, the parameter change takes effect without requiring a reboot. You can verify the setting using SHOW autovacuum_vacuum_max_threshold; from any connected session.

Lower the global cap for all tables:

tar -xvzf postgresql-18.x.tar.gz

Override per table:

tar -xvzf postgresql-18.x.tar.gz

Disable the global cap entirely:

tar -xvzf postgresql-18.x.tar.gz

Choosing the right value

The default of 100 million serves as a reasonable safety net for most workloads. For databases with large tables exhibiting high churn across both old and new rows, lowering it to 10 million can help autovacuum stay ahead of dead tuple accumulation while balancing freeze activity. Conversely, for append-only or read-heavy tables where dead tuples accumulate slowly, the default or even -1 (disabled) may be appropriate.

To check the current effective threshold for any table, query pg_stat_user_tables alongside pg_class:

tar -xvzf postgresql-18.x.tar.gz

vacuum_truncate server variable

PostgreSQL 18 introduces vacuum_truncate as a server-wide GUC (Grand Unified Configuration) parameter, providing database administrators with a single control point to disable VACUUM’s file truncation behavior across all tables without modifying individual table storage options.

Understanding VACUUM truncation

When VACUUM runs, it goes through multiple phases, including scanning the heap, vacuuming indexes and the heap, and ultimately truncating the heap. The truncation phase removes empty pages from the end of a table data file, returning that disk space to the operating system. While this may seem beneficial, it comes with a significant cost: truncation requires an AccessExclusiveLock on the table, the same lock level used by ALTER TABLE, DROP TABLE, and similar DDL operations.

During the first three phases, VACUUM holds only a ShareUpdateExclusiveLock, allowing concurrent reads and writes. However, when it enters the truncation phase, it escalates to AccessExclusiveLock, briefly blocking all other access to the table. This lock escalation can lead to noticeable query latency spikes on busy OLTP systems with large tables.

Impact on read replicas

The truncation lock has a particularly significant effect on Aurora PostgreSQL and RDS for PostgreSQL read replicas. When the primary instance truncates a table, it writes an XLOG_STANDBY_LOCK record to the WAL stream. The replica’s WAL replay process must acquire the same AccessExclusiveLock on the relation before it can apply this record. If a long-running query on the replica holds a conflicting lock, the replica will cancel that query after a 30-second wait to proceed with WAL replay. Users on the read replica may experience:

tar -xvzf postgresql-18.x.tar.gz

This is a common source of unexpected query cancellations on read replicas, especially when autovacuum is actively truncating large tables on the primary.

Before PostgreSQL 18: per-table control only

Prior to PostgreSQL 18, vacuum_truncate existed solely as a storage-level parameter set per table:

cd postgresql-18.x

This approach necessitated identifying every table where truncation caused issues and modifying each one individually, creating a maintenance burden in databases with numerous tables.

PostgreSQL 18: server-wide GUC

PostgreSQL 18 promotes vacuum_truncate to a server-wide GUC, allowing global control over truncation behavior through your parameter group. The default value remains on (truncation enabled), preserving backward compatibility.

To check the current setting:

cd postgresql-18.x

Configuring vacuum_truncate in Amazon RDS for PostgreSQL

To disable truncation globally, create a custom parameter group with vacuum_truncate = off and apply it to your instance. This is a dynamic parameter, requiring no reboot. You can verify the setting after the change:

cd postgresql-18.x

Demonstrating the behavior difference

The effect of vacuum_truncate can be observed directly in the VACUUM verbose output, specifically regarding how many pages were removed, how many remain, and whether a truncation line appears.

With vacuum_truncate = on (default):

cd postgresql-18.x

The line truncated 4425 to 45 pages confirms that VACUUM removed 4,380 empty pages and returned the space to the OS. This truncation necessitates an AccessExclusiveLock.

With vacuum_truncate = off:

cd postgresql-18.x

The truncation line is absent. Dead tuples are still removed (990,000 rows cleaned up), but the empty pages remain in the file. No AccessExclusiveLock is acquired, and no WAL truncation record is sent to replicas.

When to use vacuum_truncate = off

Disabling truncation is advisable when:

Read replicas experience frequent query cancellations due to canceling statement due to conflict with recovery errors caused by VACUUM truncation on the primary.
Tables have high write churn where deleted space will quickly be reused by new inserts. Truncating and then re-extending the file wastes I/O.
Large tables on busy OLTP systems where even a brief AccessExclusiveLock causes noticeable latency spikes.

Conversely, keep truncation enabled (the default) when:

Tables are periodically bulk-deleted and the space genuinely won’t be reused, truncation returns disk space to the OS.
Storage costs matter and you want VACUUM to actively reclaim space.
No read replicas are present, eliminating the WAL conflict concern.

Per-table override

The server-wide GUC can still be overridden at the table level. If you set vacuum_truncate = off globally but want truncation enabled for a specific table:

cd postgresql-18.x

Conversely, if the global setting is on but a specific high-traffic table needs truncation disabled:

cd postgresql-18.x

This layered control (server default using parameter group, per-table override using storage options) provides precise control over truncation behavior across your database.

autovacuum_worker_slots

PostgreSQL 18 introduces autovacuum_worker_slots, a new parameter that separates the concepts of allocating autovacuum worker slots from using them. This separation allows autovacuum_max_workers to be adjusted at runtime without requiring a server restart, marking a significant operational improvement for tuning autovacuum under varying workloads.

The problem before PostgreSQL 18

Prior to PostgreSQL 18, autovacuum_max_workers was the sole parameter controlling autovacuum concurrency. As it directly governed how many backend slots were reserved at startup, it had a postmaster context, necessitating a full server restart for any changes. If a temporary increase in autovacuum workers was needed to address table bloat during a high-write period, a server restart was unavoidable.

How it works in PostgreSQL 18

PostgreSQL 18 introduces a two-level model:

autovacuum_worker_slots sets the maximum number of backend slots reserved for autovacuum workers at startup. This is a postmaster-level parameter and still requires a restart to change. It acts as a hard ceiling.
autovacuum_max_workers controls how many of those slots are actually utilized at any given time. It is now a dynamic parameter (sighup context), allowing changes at runtime by reloading the configuration without a restart.

Configuring in Amazon RDS for PostgreSQL

In RDS and Aurora PostgreSQL, autovacuum_max_workers can be updated in your parameter group with Apply immediately, requiring no reboot. To increase the ceiling (autovacuum_worker_slots), update the parameter group and reboot the instance.

Example: temporarily increase autovacuum workers during a high-write period

Update autovacuum_max_workers in your parameter group from 3 to 6, apply immediately, and verify:

cd postgresql-18.x

Afterward, revert it back when the workload normalizes.

Important: autovacuum_max_workers cannot exceed autovacuum_worker_slots. If you attempt to set it higher, PostgreSQL will cap it at the slots value. To allow a higher autovacuum_max_workers, first increase autovacuum_worker_slots (which requires a restart), then adjust autovacuum_max_workers freely within the new ceiling.

Per-table vacuum and analyze timing

PostgreSQL 18 adds four new columns to pg_stat_all_tables (and its variants pg_stat_user_tables, pg_stat_sys_tables) that track the cumulative time spent on maintenance operations per table:

Column	Description
`total_vacuum_time`	Cumulative time spent in manual VACUUM operations (milliseconds)
`total_autovacuum_time`	Cumulative time spent in autovacuum operations (milliseconds)
`total_analyze_time`	Cumulative time spent in manual ANALYZE operations (milliseconds)
`total_autoanalyze_time`	Cumulative time spent in autoanalyze operations (milliseconds)

Previously, pg_stat_user_tables only indicated when the last vacuum or analyze ran (last_vacuum, last_autovacuum) but lacked information on how long it took. Identifying which tables consumed the most maintenance time required either parsing log files or using pg_stat_progress_vacuum during an active operation. These new columns make that information permanently available and cumulative.

Example

Create a table with 5 million rows, delete half of them, then run VACUUM VERBOSE and ANALYZE VERBOSE:

wget https://ftp.postgresql.org/pub/source/v18.x/postgresql-18.x.tar.gz

Now query the new timing columns:

cd postgresql-18.x

The elapsed: 0.18 s from VACUUM VERBOSE corresponds to 189ms in total_vacuum_time, and elapsed: 0.10 s from ANALYZE VERBOSE maps to 107ms in total_analyze_time. The view accumulates these values across every manual and automatic operation, providing a running total of maintenance time per table.

Since these columns are cumulative, running VACUUM and ANALYZE again adds to the totals, offering a continuous picture of maintenance overhead over the table’s lifetime.

Practical use cases

Identify tables with high maintenance overhead

./configure --bindir=/usr/bin --with-openssl

Compare manual and automatic maintenance time

Tables where total_autovacuum_time is significantly high relative to total_vacuum_time might benefit from tuning autovacuum thresholds. Tables where both are high are candidates for investigating bloat, index health, or write patterns.

Clean up resources

If you created database objects while following along with this post, run the following commands to remove them:

./configure --bindir=/usr/bin --with-openssl

If you created an Aurora PostgreSQL cluster or RDS for PostgreSQL instance specifically for this walkthrough and no longer need it, delete the instance to avoid ongoing charges. For instructions, refer to Deleting a DB instance or Deleting an Aurora DB cluster. Similarly, terminate any Amazon EC2 instance that you created solely for the PostgreSQL client connection.

Tech Optimizer