A practical guide to what's actually changing in PostgreSQL 19, covering six compatibility breaks you need to know before upgrading and four SQL features worth using once you do. The author tested everything against Beta 3 and explains what each change means for your workload.

• Six breaking changes ship in PG 19: JIT disabled by default, standard_conforming_strings forced on, RADIUS auth removed, MD5 warnings enabled, inet/cidr GiST indexes rebuilt, and max_locks_per_transaction doubled—most are silent behavior shifts that break things only if you don't know to look.
• FOR PORTION OF lets you split and update a time-bounded row in one statement instead of hand-rolling UPDATE then INSERT, useful for temporal data like contract period changes.
• INSERT ... ON CONFLICT DO SELECT returns the conflicting row directly without a separate query, solving the common pattern of "upsert and fetch back the row" in registration flows and dedup-on-write pipelines.
• Window functions now support IGNORE NULLS to skip over NULL values and find the nearest actual value, replacing workarounds like window frame tricks or separate counters.

PostgreSQL 19 introduces WAIT FOR LSN to solve a real problem in modern apps: when you write data and immediately read it back from a replica, you often get stale data because replication lag is invisible to your application. This feature lets you wait for the replica to catch up before reading, eliminating the guesswork of sleep delays or Redis flags.

• Naive reads from replicas miss 99.2% of the time in the test setup because the app has no way to know if the replica has replayed the write yet—WAIT FOR LSN fixes this by blocking until a specific WAL position is replayed, then succeeds with only 1-2ms overhead.
• Synchronous commit doesn't actually solve this problem; even with remote_apply, you're paying the cost on every write including batch jobs that never read from replicas, whereas WAIT FOR only blocks the specific reads that need consistency.
• The timeout parameter is an architectural decision, not a tuning knob—when lag exceeds your budget, reads fall back to the primary, which can create a stampede during cluster-wide lag events and exhaust connection pools.

PostgreSQL 18 introduces temporal constraints that simplify managing time-related data, allowing developers to maintain referential integrity across temporal relationships with ease. By utilizing GiST indexes and the WITHOUT OVERLAPS constraint, developers can efficiently handle overlapping time periods in applications without complex coding.

• PostgreSQL 18 adds native temporal constraints, letting the database enforce non-overlapping time ranges without custom application logic
• The WITHOUT OVERLAPS clause combined with GiST indexes enables primary/unique keys and foreign keys that account for time periods
• This allows referential integrity to be maintained across temporal relationships (e.g., preventing overlapping bookings or contracts) directly at the database level