Postgres has introduced new functionalities with the addition of UPDATE and DELETE FOR PORTION OF, enhancing its capabilities for temporal use cases. Time, being a fundamental aspect of many real-world applications, benefits significantly from improved temporal syntax within core SQL. Furthermore, the expansion of RANDOM() temporal functions marks a notable advancement, thanks to the contributions of Paul Ramsey and Greg Sabino Mullane.
Performance improvements across the board
The planner and executor components of Postgres are receiving consistent enhancements in version 19. Special acknowledgment goes to Tom Lane from Snowflake for his substantial contributions to performance and various other areas in this release. Key improvements encompass:
- Refinements in anti-joins and semi-joins
- Constant folding optimizations
- Incremental sorting with append paths
- Enhanced aggregate processing prior to joins
- Improved join selectivity computation
- More comprehensive function statistics
Rather than merely cataloging these planner changes, it is essential to recognize the broader implication: Postgres is increasingly adept at understanding the structure of common queries, thereby minimizing unnecessary processing. For instance, certain aggregate processing can now occur before joins, which effectively reduces the volume of rows that need to be handled. Additionally, more scenarios involving NOT IN and LEFT JOIN can now be optimized as efficient anti-joins. The visibility of memoization in EXPLAIN has been enhanced, while sort performance benefits from the implementation of radix sort. Foreign key constraint checks have also seen speed improvements, and the COPY FROM command for text and CSV inputs can now leverage SIMD instructions.
These enhancements often do not necessitate changes to application code. In many instances, simply upgrading allows Postgres to perform more effectively, which represents one of the most advantageous types of database improvements.
Why Postgres 19 feels important
What distinguishes Postgres 19 is not a singular standout feature, but rather its extensive range of improvements. This release offers:
- For application developers: enhanced graph queries, refined SQL syntax, improved window functions, and better upsert behavior.
- For operators: the introduction of
REPACK CONCURRENTLY, advancements in autovacuum, improved monitoring capabilities, online checksum modifications, and increased replication visibility. - For performance enthusiasts: planner optimizations, SIMD enhancements, visibility into asynchronous I/O, expedited foreign key checks, and improved sorting mechanisms.
- For those building on Postgres: new hooks, planner advice modules, enhancements to extensions, retrieval of FDW statistics, and ongoing investment in the extension ecosystem.
This breadth of improvements is a key factor in Postgres’s continued success. It is evolving not just for a specific workload or user persona, but as a robust solution for application databases, operational databases, analytical databases, extensible databases, and platforms alike.
As Postgres 19 is still in beta, now is the opportune moment to conduct tests. Evaluate your applications, perform migration tests, assess your extensions, and analyze execution plans for critical queries. If you rely on logical replication, ensure to test that as well. Run through your maintenance workflows and identify any potential issues while there is still time for resolution. The quality of Postgres releases improves through real-world testing, and the features present in the Postgres 19 beta offer ample opportunity for valuable evaluation.
