Back to Devoured - September 07, 2026
DEVOURED
Getting ready for PostgreSQL 19

Getting ready for PostgreSQL 19

Data databasepostgresqlsql Tapoueh
PostgreSQL 19 Beta 3 introduces temporal SQL, idempotent upsert-and-fetch, and `IGNORE NULLS` window functions while removing several legacy features.
What: PostgreSQL 19 disables JIT compilation by default, removes RADIUS authentication, and introduces `FOR PORTION OF` for temporal updates, `DO SELECT` for conflict resolution, and the `REPACK` command to replace `VACUUM FULL`. Notably, the `MERGE PARTITIONS` feature was reverted late in the cycle due to data integrity concerns.
Why it matters: The last-minute reversion of `MERGE PARTITIONS` underscores the PostgreSQL project's priority of correctness over release schedule, contrasting with commercial software release cycles that often prioritize meeting deadlines regardless of unfinished stability.
Takeaway: If you are upgrading to PostgreSQL 19, enable JIT manually if required, migrate from RADIUS and MD5 authentication, and double any explicit `max_locks_per_transaction` settings to account for new internal lock sizing.
Deep dive
  • Temporal Updates: FOR PORTION OF allows updating or deleting specific time ranges within a row without manual splitting or multiple statements.
  • Conflict Handling: ON CONFLICT ... DO SELECT provides a way to retrieve existing rows during an insert conflict without needing a secondary round-trip query.
  • Window Functions: IGNORE NULLS is now supported in functions like lead, lag, and first_value.
  • Breaking Changes: JIT is off by default, RADIUS auth is removed, and MD5 is further discouraged; inet/cidr GiST indexes require REINDEX.
  • REPACK: A unified command for VACUUM FULL and CLUSTER, supporting CONCURRENTLY to avoid long-held access-exclusive locks.
  • Partitioning: The MERGE PARTITIONS feature was pulled from the branch on August 27, 2026, due to severe bugs involving silent constraint drops and inconsistent column generation.
Decoder
  • Idempotent: An operation that can be applied multiple times without changing the result beyond the initial application.
  • JIT: Just-In-Time compilation; used in PostgreSQL to speed up complex query execution by compiling expressions to machine code at runtime.
  • Temporal SQL: Support for time-based data modeling, allowing queries to treat data as valid over specific intervals.
  • Toast: The mechanism PostgreSQL uses to store large values out-of-line from the main table row.
Original article

Full article content is not available for inline reading.

Read the original article →

Explore all 68 articles in today's digest

Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi