PostgreSQL 18 on Amazon Aurora and Amazon RDS: Security, monitoring, and developer enhancements

In Part 1 of this series, we explored the performance enhancements in PostgreSQL 18, including skip scan optimization, enhanced EXPLAIN output, automatic self-join removal, and vacuum/autovacuum improvements. In this second part, we focus on security, monitoring, developer productivity, and logical replication enhancements that improve operational efficiency and the overall developer experience.

Deprecation of MD5 password

PostgreSQL 18 deprecates MD5 password authentication in favor of more secure authentication methods. MD5 authentication still works in PostgreSQL 18, but it will be removed in a future release. We recommend migrating to SCRAM-SHA-256.

If you use Amazon Relational Database Service (Amazon RDS) or Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL), the default password_encryption is already set to scram-sha-256. However, if you’re still using MD5, or if existing users still have MD5-hashed passwords from before the default was updated, you need to act. You can modify the password_encryption parameter through your DB parameter group: create a new custom parameter group or modify your existing one, set password_encryption to scram-sha-256, then apply the parameter group to your instance. Because this is a dynamic parameter, the change takes effect immediately without a reboot. Then have affected users reset their passwords so that new SCRAM hashes are generated.

PostgreSQL 18 also introduces a new GUC parameter called md5_password_warnings, which is enabled (on) by default. It’s a dynamic parameter (PostgreSQL context: superuser) and can be enabled or disabled in your parameter group without a reboot. When active, it causes CREATE ROLE and ALTER ROLE to emit a deprecation warning in the engine logs whenever a password is stored using MD5 encryption. Use the warnings as a signal to identify which roles still have MD5 passwords and migrate those roles to SCRAM-SHA-256. After the migration finishes, the warnings naturally disappear because there are no more MD5 passwords to warn about, at which point turning the parameter off has no effect.

Enhanced monitoring of parallel worker activity

PostgreSQL 18 adds two new columns to both pg_stat_database and pg_stat_statements that track parallel worker activity:

The gap between these two values is the key signal. When the planner requests workers but the system can’t provide them because max_parallel_workers or max_worker_processes is exhausted, queries silently fall back to fewer workers or serial execution. Before PostgreSQL 18, this was invisible. You could see that parallelism was happening using IPC wait events in Performance Insights, but you had no way to quantify how often workers failed to launch or which queries were affected.

Database-level monitoring

The pg_stat_database view accumulates parallel worker activity across queries for the database. A persistent worker_shortage indicates that you’re consistently hitting max_parallel_workers or max_worker_processes limits:

SELECT datname,
       parallel_workers_to_launch,
       parallel_workers_launched,
       parallel_workers_to_launch - parallel_workers_launched AS worker_shortage
FROM pg_stat_database
WHERE datname = current_database();

 datname  | parallel_workers_to_launch | parallel_workers_launched | worker_shortage
----------+----------------------------+---------------------------+-----------------
 postgres |                         66 |                        20 |              46
(1 row)

This database has requested 66 workers total but only launched 20, with 46 workers failing to launch. This is a strong signal to increase max_parallel_workers, or to disable parallelism for some queries to reduce contention.

Query-level monitoring

pg_stat_statements shows the same metrics per query, so you can identify which specific queries are most affected:

SELECT LEFT(query, 60) AS query,
       calls,
       parallel_workers_to_launch,
       parallel_workers_launched,
       ROUND(100.0 * parallel_workers_launched / NULLIF(parallel_workers_to_launch, 0), 2) AS launch_success_rate
FROM pg_stat_statements
WHERE parallel_workers_to_launch > 0
ORDER BY parallel_workers_to_launch DESC
LIMIT 5;

                       query                        | calls | parallel_workers_to_launch | parallel_workers_launched | launch_success_rate
-----------------------------------------------------+-------+----------------------------+---------------------------+---------------------
 SELECT COUNT(*) FROM timing_demo WHERE id % $1 = $2 |     2 |                          4 |                         4 |              100.00
 SELECT COUNT(*) FROM timing_demo                    |     1 |                          2 |                         2 |              100.00
(2 rows)

A launch_success_rate below 100 percent means the system is consistently unable to provide the workers the planner requested. For example, a query running 13 times that requests 2 workers per call (26 total) but gets only 1 per call (13 total) shows a 50 percent success rate. Half the time, a worker couldn’t be launched because of resource contention.

Finding problematic queries

To find queries where workers consistently fail to launch, use the following query. For affected queries, consider either increasing max_parallel_workers to provide more worker capacity, or disabling parallelism for those specific queries with pg_hint_plan or session-level SET max_parallel_workers_per_gather = 0:

SELECT LEFT(query, 80) AS query_preview,
       calls,
       parallel_workers_to_launch,
       parallel_workers_launched,
       parallel_workers_to_launch - parallel_workers_launched AS workers_failed
FROM pg_stat_statements
WHERE parallel_workers_to_launch > 0
  AND parallel_workers_launched < parallel_workers_to_launch
ORDER BY workers_failed DESC
LIMIT 10;

These metrics complement the IPC wait events visible in Amazon RDS Performance Insights (IPC:ExecuteGather, IPC:BgWorkerStartup, IPC:ParallelFinish). Wait events tell you that parallelism is happening. The new columns tell you whether the system is delivering the workers the planner requested.

Replication monitoring improvements in pg_stat_subscription_stats

PostgreSQL 18 adds new columns to pg_stat_subscription_stats that track specific conflict types encountered during logical replication apply. Previously, the view only reported aggregate error counts. Now it breaks down conflicts by category, making it much easier to diagnose replication issues.

New conflict columns

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'pg_stat_subscription_stats'
ORDER BY ordinal_position;

           column_name
---------------------------------
 subid
 subname
 apply_error_count
 sync_error_count
 confl_insert_exists
 confl_update_origin_differs
 confl_update_exists
 confl_update_missing
 confl_delete_origin_differs
 confl_delete_missing
 confl_multiple_unique_conflicts
 stats_reset

The seven new confl_* columns track:

Querying conflict statistics

SELECT subname,
       apply_error_count,
       confl_insert_exists,
       confl_update_missing,
       confl_delete_missing
FROM pg_stat_subscription_stats;

Why these matter

Before PostgreSQL 18, when logical replication stopped because of a conflict, the only information available was the total apply_error_count and the error message in the logs. Determining the type of conflict required parsing log files.

The new columns let you identify conflict patterns at a glance:

• High confl_insert_exists – The subscriber has rows the publisher is trying to insert, likely caused by data being written directly to the subscriber, or a failed initial sync.
• High confl_update_missing or confl_delete_missing – Rows exist on the publisher but not the subscriber. This is common after manual data manipulation on the subscriber.
• High confl_update_origin_differs – Rows are being modified by multiple origins. This is relevant in bidirectional replication setups.

Optimizer statistics preservation during pg_upgrade

Every major PostgreSQL upgrade previously ignored optimizer statistics. After upgrading, the query planner had no information about table sizes, column distributions, or index selectivity. Every query ran with default estimates until ANALYZE finished across the database. With pg_upgrade (the fast upgrade path), the impact was particularly severe. The upgrade finishes in minutes, but running ANALYZE on a large database could take hours, during which query performance was degraded.

What changes in PostgreSQL 18

pg_upgrade now automatically transfers optimizer statistics from the old cluster to the new one as part of the upgrade process. No manual intervention is required. Two new catalog functions handle the import: pg_restore_relation_stats() and pg_restore_attribute_stats().

For dump/restore upgrades, pg_dump --with-statistics exports statistics explicitly.

After a pg_upgrade to PostgreSQL 18, the new cluster has the same planner statistics as the old one. Queries run with good plans from the first connection.

What is not preserved

Extended statistics (created with CREATE STATISTICS) are not preserved. Only the object definition is carried over, not the computed data. After upgrading, run:

vacuumdb --all --analyze-only --missing-stats-only

Note: The --all flag applies this command to each database in the cluster.

The new --missing-stats-only flag (also added in PostgreSQL 18) collects only statistics that are absent. In practice, only the extended statistics after an upgrade. This is far faster than a full ANALYZE of the entire database.

Monitoring statistics (pg_stat_* views) are also not preserved, so autovacuum and autoanalyze lose their memory of which tables were last processed. For busy, large tables, consider running a manual VACUUM (ANALYZE) shortly after the upgrade.

For Amazon RDS for PostgreSQL

For RDS major version upgrades (such as PostgreSQL 16 to 18), the upgrade process uses pg_upgrade internally. With PostgreSQL 18 as the target, optimizer statistics are preserved automatically. After the upgrade finishes, verify statistics are present:

SELECT relname, reltuples, relpages
FROM pg_class
WHERE relkind = 'r'
  AND reltuples > 0
ORDER BY reltuples DESC
LIMIT 5;

If extended statistics exist on any tables, identify them and run the targeted analyze:

-- Find tables with extended statistics that need re-analyzing
SELECT n.nspname AS schema,
       c.relname AS table,
       s.stxname
FROM pg_statistic_ext s
JOIN pg_class c ON c.oid = s.stxrelid
JOIN pg_namespace n ON n.oid = c.relnamespace;

Generate timestamp-ordered UUIDs with uuidv7()

PostgreSQL 18 introduces uuidv7(), a native function for generating UUID version 7 as specified in RFC 9562. UUIDv7 combines a Unix timestamp in milliseconds with random bits, producing UUIDs that are both globally unique and naturally sortable by creation time.

The problem with UUIDv4

The existing gen_random_uuid() function (now also aliased as uuidv4()) generates fully random UUIDs. While globally unique, random UUIDs cause a well-known B-tree index problem: every new row inserts at a random position in the index, causing frequent page splits and cache misses. On high-insert workloads with UUID primary keys, this leads to index bloat and degraded write performance.

-- UUIDv4: fully random, no ordering
SELECT gen_random_uuid() AS uuidv4 FROM generate_series(1,3);

                uuidv4
--------------------------------------
 a0b20c40-3cb4-4a0c-8f41-2aba1ba9cfb0
 cace1513-5687-4847-9d92-5fb80fd71e33
 882aa09d-39d2-4acc-886c-5fe91730b490

No pattern, each UUID is scattered randomly.

UUIDv7: Timestamp-ordered

SELECT uuidv7() FROM generate_series(1,5);

                uuidv7
--------------------------------------
 019d7445-07d1-7797-832d-ffcf72dcd3c3
 019d7445-07d1-77b2-a049-bf0a3c7b9fa4
 019d7445-07d1-77b5-ab69-3d75d1416736
 019d7445-07d1-77b7-8e90-d195313b70e6
 019d7445-07d1-77b9-8108-3e70dfdd7e6d

The first 12 characters (019d7445-07d1) encode the millisecond timestamp. They’re identical for UUIDs generated within the same millisecond. The remaining bits provide uniqueness and sub-millisecond monotonicity. New rows always insert near the end of the B-tree index, eliminating random page splits.

Extracting the timestamp

PostgreSQL 18 extends uuid_extract_timestamp() to support UUIDv7:

SELECT uuid_extract_timestamp(uuidv7()) AS extracted, now() AS current_time;

          extracted          |         current_time
-----------------------------+-------------------------------
 2026-04-09 22:02:54.289+00  | 2026-04-09 22:02:54.287568+00

The extracted timestamp matches now() to millisecond precision. You can recover when any UUIDv7 was generated directly from the value itself.

Time-shifted UUIDs

uuidv7() accepts an optional interval to shift the embedded timestamp:

SELECT uuidv7('-1 hour') AS uuid,
       uuid_extract_timestamp(uuidv7('-1 hour')) AS timestamp;

                 uuid                 |          timestamp
--------------------------------------+----------------------------
 019d740e-1951-7b76-befc-691ed8d8e6ae | 2026-04-09 21:02:54.289+00

Using uuidv7() as a primary key

CREATE TABLE events (
    id uuid DEFAULT uuidv7() PRIMARY KEY,
    payload text,
    created_at timestamptz DEFAULT now()
);

Because UUIDv7 values are monotonically increasing, ORDER BY id returns rows in insertion order. The timestamp is embedded in the key itself, removing the need for a separate sort column in many use cases.

Default streaming changed to parallel

PostgreSQL 18 changes the default value of the streaming option for CREATE SUBSCRIPTION from off to parallel. This makes parallel apply the default behavior for logical replication, improving throughput and reducing lag for large transactions.

Background

Logical replication applies changes on the subscriber by replaying transactions from the publisher’s WAL stream. Before PostgreSQL 16, large transactions were buffered entirely on the subscriber before being applied. This caused replication lag to spike during bulk operations and required significant memory or temporary file usage.

PostgreSQL 16 introduced streaming = parallel as an option, allowing large transactions to start streaming to the subscriber before they commit on the publisher, with multiple apply workers processing changes simultaneously. However, it was not the default. DBAs had to explicitly opt in.

PostgreSQL 18 makes parallel the default, reflecting its maturity and the performance benefits it provides for most workloads.

What changes

Before PostgreSQL 18, streaming defaulted to off:

CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=mydb'
    PUBLICATION my_pub;
-- equivalent to: streaming = off

PostgreSQL 18, streaming defaults to parallel:

CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=publisher dbname=mydb'
    PUBLICATION my_pub;
-- equivalent to: streaming = parallel

With streaming = parallel:

• Large transactions begin replicating before they commit on the publisher.
• Multiple apply workers process changes simultaneously.
• Parallel apply reduces replication lag for bulk operations.
• The subscriber uses less memory because it doesn’t buffer full transactions.

Impact on existing subscriptions

Existing subscriptions created before upgrading to PostgreSQL 18 retain their previous streaming setting. Check the current setting:

SELECT subname, substream FROM pg_subscription;

The substream column shows f (off), t (on), or p (parallel).

To update an existing subscription:

ALTER SUBSCRIPTION my_sub SET (streaming = parallel);

Parallel streaming requires logical replication protocol version 4 or higher, supported between PostgreSQL 16+ publishers and subscribers.

Inactive replication slot auto-invalidation

PostgreSQL 18 introduces idle_replication_slot_timeout, a new parameter that automatically invalidates replication slots that have been inactive for longer than a specified duration. This addresses one of the most common operational hazards with logical replication: abandoned slots that silently accumulate WAL and eventually cause disk exhaustion.

The problem with inactive replication slots

Replication slots prevent the WAL sender from discarding WAL segments that haven’t been consumed by the slot’s subscriber. When a subscriber disconnects because of a network failure, application crash, or misconfiguration, the slot remains active on the primary, holding back WAL indefinitely. On busy systems, this can fill the pg_wal directory within hours, causing the primary to stop accepting writes.

Before PostgreSQL 18, the only protection was manual monitoring: periodically querying pg_replication_slots for slots with active = false and a stale confirmed_flush_lsn, then manually dropping them.

The new parameter

idle_replication_slot_timeout specifies how long a slot can remain inactive before it’s automatically invalidated.

The default is 0 (disabled):

SHOW idle_replication_slot_timeout;

 idle_replication_slot_timeout
-------------------------------
 0
(1 row)

When set to a non-zero value, PostgreSQL automatically invalidates any slot that has had no active connection for longer than the specified duration. The slot is marked as invalid rather than dropped. It remains visible in pg_replication_slots with invalidation_reason = 'idle_timeout', so you can identify and clean it up.

Configuring in Amazon RDS for PostgreSQL

Set the parameter in your parameter group (no reboot required):

idle_replication_slot_timeout = 1d

Monitor for invalidated slots:

SELECT slot_name,
       slot_type,
       active,
       inactive_since,
       invalidation_reason
FROM pg_replication_slots
WHERE invalidation_reason IS NOT NULL;

Drop invalidated slots:

SELECT pg_drop_replication_slot(slot_name)
FROM pg_replication_slots
WHERE invalidation_reason = 'idle_timeout';

For Aurora PostgreSQL, where WAL retention directly affects storage costs and cluster availability, we recommend enabling this parameter for any cluster using logical replication.

COPY command enhancements

PostgreSQL 18 adds two improvements to the COPY command that give you more control over error handling during bulk data loads: REJECT_LIMIT and a new LOG_VERBOSITY level called silent.

REJECT_LIMIT for controlled error tolerance:

Before PostgreSQL 18, the ON_ERROR = 'ignore' option for COPY FROM would skip all rows with data type conversion errors, with no upper bound. This was useful for best-effort loading, but dangerous in production: a corrupted file could silently discard thousands of rows with no safeguard. You had to choose between failing on the first error (the default) or tolerating an unlimited number of errors. PostgreSQL 18 introduces REJECT_LIMIT, which sets a maximum number of errors COPY FROM will tolerate before aborting. After the error count exceeds the specified value, the command fails, even with ON_ERROR = 'ignore' set.

-- Load a CSV file, skip rows with type conversion errors, but fail if more than 100 rows are bad
COPY sensor_readings FROM '/data/readings.csv'
WITH (FORMAT csv, HEADER true, ON_ERROR ignore, REJECT_LIMIT 100);

If the file contains 50 malformed rows, the load completes and reports 50 rows skipped. If it contains 101 malformed rows, the command fails at the 101st error. This gives you a safety net: you accept that real-world data has occasional issues, but you catch situations where the source file is fundamentally broken.

LOG_VERBOSITY silent:

When ON_ERROR = 'ignore' is active, PostgreSQL emits a NOTICE for each discarded row (at verbose level) or a summary count at the end (at default level). For large loads where you expect and accept a known error rate, these messages add noise to your logs without providing actionable information. PostgreSQL 18 adds a third LOG_VERBOSITY level: silent. This suppresses all messages about discarded rows, including the final summary count.

COPY sensor_readings FROM '/data/readings.csv'
WITH (FORMAT csv, HEADER true, ON_ERROR ignore, REJECT_LIMIT 100, LOG_VERBOSITY silent);

Combine REJECT_LIMIT with LOG_VERBOSITY silent for production ETL pipelines where you want a hard cap on errors but do not need per-row logging.

OLD and NEW in RETURNING clauses (INSERT, UPDATE, DELETE, and MERGE)

PostgreSQL 18 introduces OLD and NEW aliases in the RETURNING clause of INSERT, UPDATE, DELETE, and MERGE commands. This allows a single DML statement to return both the previous and current state of modified rows, eliminating the need for separate queries or trigger-based workarounds to capture before-and-after values.

Previously, RETURNING had fixed behavior depending on the command type: INSERT returned the newly inserted row, UPDATE returned the row after modification, and DELETE returned the row as it existed before deletion. There was no way to get both old and new values from a single statement.

-- UPDATE: return both old and new prices
UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name,
            old.price AS old_price,
            new.price AS new_price,
            new.price - old.price AS price_change;

   name    | old_price | new_price | price_change
-----------+-----------+-----------+--------------
 Widget A  |     49.99 |     54.99 |         5.00
 Widget B  |     99.99 |    109.99 |        10.00

The same syntax works with MERGE, where combining OLD/NEW with merge_action() gives you a full change report from a single statement:

MERGE INTO products p USING staging s ON p.product_no = s.product_no
  WHEN MATCHED THEN UPDATE SET price = s.price, name = s.name
  WHEN NOT MATCHED THEN INSERT VALUES (s.product_no, s.name, s.price)
  RETURNING merge_action(),
            p.product_no,
            old.price AS old_price,
            new.price AS new_price;

These features require no configuration changes. On Amazon RDS for PostgreSQL and Aurora PostgreSQL running PostgreSQL 18, OLD and NEW work in RETURNING clauses for all DML commands. Applications that previously relied on triggers or multi-statement transactions for change capture can consolidate into single statements, reducing round trips and simplifying application code.

Conclusion

In this second part of our series, we covered the security, monitoring, developer, and logical replication enhancements in PostgreSQL 18. These include the deprecation of MD5 passwords in favor of SCRAM-SHA-256, improved parallel worker monitoring, and the new uuidv7() function for timestamp-ordered UUIDs. Together, these features strengthen the operational and development experience on Aurora PostgreSQL and Amazon RDS for PostgreSQL.

Together with the performance enhancements covered in Part 1, PostgreSQL 18 delivers improvements across performance, security, observability, and developer productivity.

Get started today: Upgrade your Aurora PostgreSQL cluster or Amazon RDS for PostgreSQL instance to version 18. For more information, see the Aurora PostgreSQL upgrade documentation or the Amazon RDS for PostgreSQL upgrade guide. If you have questions or feedback, reach out through AWS re:Post or leave a comment.

About the authors