Postgres 18 Upgrades
The official Postgres documentation is in my opinion the most succinct documentation ever. It’s one of the list of things that got me interested in postgres as my defacto RDB of choice. I am always on the lookout for new additions to postgres. There's details on the enhancements on the upcoming version 18 here.
I made this list of improvements on the upcoming Postgres 18 release that seemed interesting (or things amongst my realm of understanding). I thought It would be nice to share without going into too much technical details:
1. Async I/O
Current Capability: PostgreSQL traditionally uses background processes like WAL writer, Checkpointer to help batch I/O tasks, but all these are synchronous under the hood. This can lead to higher latency, especially under heavy load. There’s no built-in native async I/O like you might find in some modern databases.
Postgres 18: Azure OSS team have now introduced true async on all read streams. In future this will inevitably lead to writes being asynchronous as well.
2. VACUUM WAL Volume Reduction
Current Capability: The VACUUM process generates a significant amount of Write-Ahead Logs (WAL), which can consume considerable storage and impact performance during maintenance operations.
Postgres 18: The optimization reduces the WAL volume generated by VACUUM operations. This leads to lower storage requirements and less impact on performance during maintenance, making the database more efficient and cost-effective.
3. Query Planner Improvements
Current Capability: The query planner in PostgreSQL is already robust, but complex queries involving UNION and IS [NOT] NULL conditions can sometimes result in suboptimal execution plans.
Postgres 18: Adds support for hash-based right joins and hash-based full outer joins, which were previously unsupported. The planner can now choose hash join strategies for these join types. Merge joins now support incremental sort. It will enable parallel nested loop joins with better memory materialization of row sets.
4. Partitioned Tables
Current Capability: PostgreSQL supports partitioned tables, which help manage large datasets by dividing them into smaller, more manageable pieces. However, performance can degrade with very large partitions.
Postgres 18: Adds support for runtime partition pruning in more contexts, especially in cases involving parameterized queries and prepared statements. DMLs that target partitioned tables now have reduced overhead. Merge joins involving partitioned tables now use partition-wise joins more effectively. ANALYZE command now gathers more accurate info for partitioned tables without scanning all partitions, using the new sample-based optimization.
5. Memory Performance Enhancements
Current Capability: PostgreSQL's memory management is efficient, but there is always room for optimization to handle high-memory workloads better.
Postgres 18: Various memory performance enhancements improve memory allocation and usage. This reduces memory overhead and speeds up database operations, making PostgreSQL more efficient in handling large and complex workloads.
3. Query Planner Improvements
Current Capability: The query planner in PostgreSQL is already robust, but complex queries involving UNION and IS [NOT] NULL conditions can sometimes result in suboptimal execution plans.
Postgres 18: Adds support for hash-based right joins and hash-based full outer joins, which were previously unsupported. The planner can now choose hash join strategies for these join types. Merge joins now support incremental sort. It will enable parallel nested loop joins with better memory materialization of row sets.
4. Partitioned Tables
Current Capability: PostgreSQL supports partitioned tables, which help manage large datasets by dividing them into smaller, more manageable pieces. However, performance can degrade with very large partitions.
Postgres 18: Adds support for runtime partition pruning in more contexts, especially in cases involving parameterized queries and prepared statements. DMLs that target partitioned tables now have reduced overhead. Merge joins involving partitioned tables now use partition-wise joins more effectively. ANALYZE command now gathers more accurate info for partitioned tables without scanning all partitions, using the new sample-based optimization.
5. Memory Performance Enhancements
Current Capability: PostgreSQL's memory management is efficient, but there is always room for optimization to handle high-memory workloads better.
Postgres 18: Various memory performance enhancements improve memory allocation and usage. This reduces memory overhead and speeds up database operations, making PostgreSQL more efficient in handling large and complex workloads.
Comments
Post a Comment