DEVOURED
Getting Ready for PostgreSQL 19
PostgreSQL 19 brings powerful SQL-level enhancements like 'FOR PORTION OF' temporal updates and 'ON CONFLICT DO SELECT' while tightening security defaults.
What: The upcoming PostgreSQL 19 release—following the project's late-year release cycle—introduces 'FOR PORTION OF' for range-based updates, 'DO SELECT' for idempotent upserts, and improved window function control with 'IGNORE NULLS'. It also removes RADIUS support and disables JIT by default to prioritize stability and security.
Why it matters: The last-minute removal of 'MERGE PARTITIONS' highlights the project's commitment to release quality over deadlines, preferring to delay complex features rather than ship unstable DDL logic.
Takeaway: Before upgrading to PG 19, re-dump your data using current versions of 'pg_dump' to account for the mandatory 'standard_conforming_strings' setting and ensure you migrate away from MD5 authentication to 'scram-sha-256'.
Deep dive
- 'UPDATE ... FOR PORTION OF' allows modifying specific segments of range data without manual record splitting.
- 'INSERT ... ON CONFLICT DO SELECT' simplifies upsert-and-fetch logic by returning the existing row during a conflict without a secondary select.
- Window functions 'lead', 'lag', and value lookups now support 'IGNORE NULLS'.
- 'REPACK' introduces a unified command to replace 'VACUUM FULL' and 'CLUSTER', supporting concurrent execution.
- Breaking changes include: JIT disabled by default, RADIUS authentication removed, MD5 warnings, and stricter rules on CR/LF in object names.
- 'max_locks_per_transaction' default doubled to 128 to accommodate internal memory shifts.
- 'MERGE PARTITIONS' was pulled from the release branch due to silent data loss and constraint handling bugs found late in testing.
Decoder
- Upsert: A database operation that updates an existing row if a conflict occurs (like a primary key violation) or inserts a new row if it does not exist.
- JIT (Just-in-Time compilation): A method of optimizing performance in Postgres that compiles SQL expressions into machine code at runtime.
- Temporal Data: Data that is relevant to specific time intervals; here managed using PostgreSQL 'range' types and the new SQL-standard 'FOR PORTION OF' syntax.
Original article
Full article content is not available for inline reading.
Explore all 57 articles in today's digest
Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi