Planning an upgrade from PostgreSQL version 13 is essential as standard support will conclude on February 28, 2026. This transition requires careful consideration of the benefits, potential breaking changes, and various strategies available for a successful upgrade.
Standard support for Amazon Aurora PostgreSQL-Compatible Edition and Amazon RDS for PostgreSQL version 13 will end on the aforementioned date. As you prepare for this upgrade, it’s crucial to evaluate how the new updates may impact application compatibility. While upgrading can introduce challenges, the latest releases promise enhanced features, performance, and security. A thorough planning and testing phase will ensure that you reap the maximum benefits with minimal disruption.
Key benefits of PostgreSQL newer versions
Transitioning to newer PostgreSQL versions can significantly enhance your database performance and introduce new capabilities. Below are some notable features that have emerged in recent versions.
Performance enhancements
- Vacuum emergency mode (v14+) – This feature helps prevent fatal transaction ID wraparound by managing old row versions more aggressively.
- Improved I/O performance (v17) – Experience up to two times better write throughput thanks to enhanced WAL processing.
- Query optimization (v17+) – Enhanced performance for IN clauses with B-tree indexes and parallel BRIN index builds.
- Memory efficiency (v17) – The new vacuum memory structure consumes up to 20 times less memory.
Advanced monitoring and diagnostics
- pg_stat_io (v16+) – Offers detailed statistics on I/O operations.
- pg_wait_events (v17+) – Provides in-database references for wait events, eliminating the need for manual documentation lookups.
Logical replication improvements
- Failover support (v17+) – Automatically synchronize logical replication slots from primary to standby servers.
- Slot migration (v17+) – Logical replication slots can now migrate through pg_upgrade, simplifying the upgrade process.
- Parallel apply (v16+) – This feature allows data to be written directly to the target table using multiple background worker processes.
- Row filtering (v15+) – Provides fine-grained control over the data that is replicated.
Developer experience
- JSONB subscripting (v14+) – Offers an intuitive syntax for accessing and modifying JSONB data.
- SQL/JSON JSON_TABLE (v17+) – Enables transformation of JSON data into relational views.
- Query pipelining (v14+) – Reduces network latency for high-latency connections.
Security enhancements
- pg_read_all_data and pg_write_all_data roles (v14+) – Streamlines read/write access control.
- pg_maintain role (v17+) – Allows users to perform database maintenance tasks.
- (v15+) – Removal of PUBLIC creation permission on the public schema.
Amazon Aurora PostgreSQL-Compatible with Aurora Optimized Reads
For users of Amazon Aurora PostgreSQL-Compatible, upgrading to versions v14.9+, v15.4+, v16.1+, and higher can yield significant performance optimizations. Aurora Optimized Reads can deliver up to eight times faster query latency and up to 30% cost savings for large datasets. This feature supports:
- Tiered cache – Extends DB instance caching capacity by up to five times more instance memory (on Aurora I/O-Optimized clusters).
- Temporary objects – Achieve up to two times faster latency for advanced queries using local NVMe storage.
PostgreSQL v13 deprecation: Catalog view changes and upgrade benefits (v14-v17)
As you upgrade from PostgreSQL v13 to newer versions, be aware of changes that may impact your applications. Below, we summarize changes related to system catalogs and configuration parameters.
Changes in system catalog views
The following table outlines changes in PostgreSQL v17:
| Change Type | Column Name | Action | Notes |
| Removed from pg_stat_bgwriter | buffers_backend | REMOVED | – |
| Removed from pg_stat_bgwriter | buffers_backend_fsync | REMOVED | – |
| New View | pg_stat_checkpointer | CREATED | Separates checkpointer statistics from background writer. |
| New View | pg_wait_events | CREATED | Wait event information. |
Additionally, the following table summarizes pg_stat_progress_vacuum column renames:
| Change Type | Old Name | New Name | Description |
| Renamed | max_dead_tuples | max_dead_tuple_bytes | Column renamed. |
| Renamed | num_dead_tuples | dead_tuple_bytes | Column renamed. |
| New column | – | indexes_total | New column added. |
| New column | – | indexes_processed | New column added. |
| New column | – | dead_tuple_bytes | New column added. |
The following table summarizes additional catalog changes:
| View/Table | Change type | Old name | New name | Description |
| pg_database | New column | – | dathasloginevt | New column added. |
| pg_database | Renamed | daticulocale | datlocale | Column renamed. |
| pg_collation | Renamed | colliculocale | colllocale | Column renamed. |
Lastly, the following table summarizes modified system views:
| View name | New column(s) added |
| pg_replication_slots | failover; synced; invalidation_reason; inactive_since |
| pg_stat_progress_copy | tuples_skipped |
| pg_stat_subscription | worker_type |
| pg_stats | range_length_histogram; range_empty_frac; range_bounds_histogram |
| pg_subscription | subfailover |
The following table summarizes PostgreSQL v14 system catalog changes:
| View name | Change type | Column name | Notes |
| pg_stat_activity | New column | query_id | Requires compute_query_id parameter. |
| pg_stat_statements | New column | toplevel | New column added. |
Important parameter-related changes
The following table summarizes parameter-related changes in PostgreSQL v14:
| Change type | Parameter name | Description/Notes |
| New | compute_query_id | Controls query identifier computation. |
| New | client_connection_check_interval | Sets the time interval between checks for disconnection while running queries. |
| New | idle_session_timeout | Ends sessions not in a transaction that have been idle longer than the specified time. |
| New | default_toast_compression | Sets the default compression method for compressible values. |
| New | vacuum_failsafe_age | Age at which VACUUM should trigger failsafe to avoid a wraparound outage. |
| New | huge_page_size | The size of huge page that should be requested. |
| Removed | operator_precedence_warning | Completely removed. |
| Removed | vacuum_cleanup_index_scale_factor | Removed (deprecated in v12). |
The following table summarizes parameter-related changes in PostgreSQL v15, v16, and v17:
| Version | Change type | Parameter name | Description/Notes |
| PostgreSQL 15 | Enhanced | wal_compression | Supports new algorithms: zstd, lz4. |
| PostgreSQL 15 | New | wal_decode_buffer_size | Buffer size for WAL decoding. |
| PostgreSQL 16 | New | vacuum_buffer_usage_limit | Limits buffer usage during vacuum. |
| PostgreSQL 16 | New | logical_replication_mode | Controls logical replication behavior. |
| PostgreSQL 17 | New | sync_replication_slots | Enables synchronization of replication slots. |
Upgrade strategy options
When it comes to upgrading your Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL databases, several approaches are available:
- In-place upgrade – This method can be executed via the AWS Command Line Interface (AWS CLI) or AWS Management Console. In-place upgrades necessitate downtime that correlates with your database size. Testing the duration by upgrading a snapshot first is advisable. This method is suitable for workloads that can accommodate downtime while preferring simpler administration.
- Amazon RDS blue/green deployment – Utilizing PostgreSQL logical replication, this approach maintains two synchronized environments. Upgrade the green (staging) environment with a one-click upgrade, thoroughly test your application, and then switch production traffic with minimal downtime—typically under a minute. While this method is straightforward to implement, be cautious that DDL changes aren’t replicated, which could disrupt the replication process.
- Logical replication – Both Amazon Aurora PostgreSQL-Compatible and Amazon RDS for PostgreSQL support logical replication through pglogical. This process involves creating an initial snapshot of the publisher database, copying it to the subscriber, and then continuously replicating real-time changes. It offers minimal downtime and continuous replication but requires a complex initial setup and longer synchronization for large databases. Note that logical replication cannot replicate DDL, sequence, and large object operations.
- AWS Database Migration Service (AWS DMS) – AWS DMS supports both Amazon Aurora PostgreSQL-Compatible and Amazon RDS for PostgreSQL as source and target databases, with change data capture (CDC) capabilities. While it allows for minimal-downtime upgrades and continuous replication, it does not support all data types (such as timestamp with time zone) and incurs additional costs during the migration period.
For comprehensive information about in-place upgrades and various out-of-place upgrade options, refer to Upgrade your Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL database, Part 1: Comparing upgrade approaches. This resource examines the advantages and disadvantages of each approach.
Preparing to upgrade
Prior to initiating an upgrade, consider the following actions:
- Review your current database configuration.
- Test the upgrade process in a staging environment.
- Validate application compatibility.
- Create comprehensive backup strategies.
If an immediate upgrade is not feasible, Amazon RDS Extended Support offers up to three years of continued security patches and bug fixes. This paid service provides critical security and bug fixes for Amazon Aurora PostgreSQL-Compatible and Amazon RDS for PostgreSQL major versions, extending up to three years beyond the standard support end date. Pricing will increase based on the years elapsed since standard support expiration. Utilize the RDS Extended Support window wisely to identify the optimal upgrade path for your databases and applications, thereby streamlining your upgrade process for your production environment.
