PostgreSQL 19 vs. 18: What Actually Changed, Feature by Feature

Every major PostgreSQL release gets the same LinkedIn headline treatment: "this changes everything." Most of the time it doesn't, it's a dozen genuine improvements wrapped in the language of a paradigm shift. PostgreSQL 19 is a real release with real substance, but the honest way to evaluate it isn't the headline feature, it's asking what specifically didn't work in PG18 that now does, and what the actual cost of adopting it is.

The Shape of the Two Releases

PostgreSQL 18 was primarily an I/O and observability release: the Asynchronous I/O subsystem rewired how the engine reads from disk, pg_stat_io got a near-total overhaul, and B-Tree skip scans fixed a decade-old multicolumn-index limitation. PostgreSQL 19 is primarily a DDL/DML ergonomics and operational-maintenance release: the headline feature (SQL/PGQ) is a new query language surface, but the features that will actually change your on-call life are REPACK CONCURRENTLY, native partition reshaping, and parallel autovacuum.

That distinction matters because it tells you where to look for value. If PG18 already fixed your I/O-bound scan performance, PG19 isn't going to double it again, it's going to fix the maintenance operations that PG18 left untouched.

1. SQL/PGQ: Property Graph Queries

What it is: PostgreSQL 19 adds SQL/PGQ, the SQL:2023 standard for querying graph-shaped data. You define a property graph as a read-only view over existing relational tables (a node table, an edge table), then query it with MATCH and Neo4j-style arrow syntax: MATCH (a:Employee)-[:MANAGES]->(b:Employee). Internally, PostgreSQL rewrites the arrow syntax into ordinary joins against your existing tables before the planner ever runs, so it inherits your existing indexes and your existing row-level security automatically.

Comparison (PG18 vs. PG19): In PG18, and every version before it, modeling a graph relationship (a dependency tree, an org chart, an authorization graph, "who reports to whom") meant writing a recursive CTE, WITH RECURSIVE, walking a self-referencing table one level at a time. Recursive CTEs work, but they're procedural: you write the recursion, you write the termination condition, and the planner frequently struggles to estimate the cost of a deep or unbounded traversal, which is exactly the kind of query that "chokes the planner" that gets complained about on social media. There was no declarative way to say "find all paths matching this pattern" — you had to hand-build the loop yourself in SQL.

Pros:

• No secondary graph database, no sync pipeline. If your graph queries are shallow-to-medium depth over data that's already relational, you get graph-shaped querying without standing up Neo4j and building an ETL job to keep it current.
• Declarative pattern matching gives the planner a clearer picture of intent than a hand-written recursive CTE, which can lead to better plans for the same logical query.
• Inherits your existing security model for free, since a property graph is "just a view."

Cons:

• It's still running joins under the hood. You do not get index-free adjacency, the property that makes a native graph database like Neo4j fast for deep, multi-hop traversal (5+ hops over millions of edges). If your actual workload is that kind of traversal, SQL/PGQ won't rescue you from needing a dedicated graph store.
• New syntax surface means new things to learn and new things the planner can misjudge; it's not yet battle-tested the way recursive CTEs are, having existed for two decades.
• The realistic audience for this feature is "developers who currently reach for a recursive CTE for shallow hierarchical queries," not "teams running production graph analytics at Neo4j scale." Know which one you are before switching.

2. REPACK: Unifying and Unlocking VACUUM FULL / CLUSTER

What it is: PostgreSQL 19 introduces a single REPACK command that replaces both VACUUM FULL (rewrite the table to reclaim bloat) and CLUSTER (rewrite the table in index order). The critical addition is a CONCURRENTLY option — REPACK ... CONCURRENTLY — that rebuilds the table without taking the ACCESS EXCLUSIVE lock that made both of the old commands unusable on a live, high-traffic table.

Comparison (PG18 vs. PG19): In PG18, if a table had accumulated enough bloat (dead row versions from updates/deletes that VACUUM alone couldn't reclaim) that you needed to physically shrink it, your only in-core option was VACUUM FULL, which locks the table exclusively for the entire rewrite. On a table serving live traffic, that's not a maintenance task, it's a scheduled outage. The workaround was the third-party pg_repack extension, which achieves a similar result without full locking by building a shadow copy and swapping it in, but that means installing and trusting an external extension for something this fundamental.

Pros:

• This is the single biggest operational upgrade in the release for anyone who has had to fight table bloat on a production system: the capability that pg_repack (the extension) existed specifically to provide is now in core, with CONCURRENTLY.
• One command name instead of two (VACUUM FULL and CLUSTER remain for backward compatibility, but REPACK is now the unified entry point).
• Removes a dependency on a third-party extension for a core maintenance operation.

Cons:

• CONCURRENTLY avoiding the exclusive lock doesn't mean it's free, it still consumes I/O and CPU rewriting the table, and the new max_repack_replication_slots variable exists because concurrent repack has its own resource considerations to tune.
• If your team already has pg_repack (the extension) working reliably, migrating to core REPACK CONCURRENTLY is a "nice to have simplify," not an urgent fix, don't rip out something that already works without testing the native replacement first.

3. Native Partition Merge/Split

What it is: ALTER TABLE ... MERGE PARTITIONS and ALTER TABLE ... SPLIT PARTITIONS, letting you reshape an existing partitioned table's partition boundaries natively.

Comparison (PG18 vs. PG19): PG18 invested heavily in partition query performance, more efficient planning over many partitions, better partitionwise joins, reduced memory use for partition pruning, but did nothing for partition maintenance. If a monthly partition grew too large and you wanted to split it into two, or several small partitions had accumulated and you wanted to merge them, there was no native command. You did it by hand: create new partition(s), migrate the relevant rows with INSERT ... SELECT, detach and drop the old partition, all while carefully managing locks and application downtime windows.

Pros:

• Turns a multi-step, error-prone, DBA-scripted operation into a single DDL statement.
• Makes it realistic to actually right-size partitions over time as data volume and access patterns change, rather than living with whatever partition scheme you picked at design time.
• Complements PG18's partition query-planning work: PG18 made partitioned tables fast to query, PG19 makes them practical to maintain.

Cons:

• Any partition reshape on a large table still means physically moving rows, this is not a free metadata-only operation, plan for I/O and lock impact accordingly.
• Doesn't retroactively fix a bad initial partitioning key choice, it makes boundary changes easier, not a full re-partitioning strategy change.

4. GROUP BY ALL

What it is: New SELECT syntax, GROUP BY ALL, which automatically groups by every column in the SELECT list that isn't an aggregate or window function, no manual enumeration required.

Comparison (PG18 vs. PG19): PG18 addressed a related but distinct problem at the planner level: it learned to ignore GROUP BY columns that were functionally dependent on other grouped columns (for example, if you group by a table's unique primary key, other same-table columns don't logically need to be listed, and PG18's planner recognized this and dropped them from the actual grouping operation for efficiency). That's an internal cost optimization, it didn't change what you had to type. In PG18 you still hand-wrote every column in the GROUP BY clause, no matter how wide the SELECT list.

Pros:

• Removes the tedious, error-prone task of keeping a long GROUP BY list in sync with the SELECT list, adding a column to one and forgetting the other is a classic source of "column must appear in GROUP BY" errors.
• Particularly valuable for wide analytical queries with 10+ grouping dimensions.

Cons:

• Implicit grouping means it's slightly easier to accidentally group by more (or fewer) columns than you intended if the SELECT list changes later, explicit lists are more self-documenting for complex queries. Use with intention, not as a default habit for every query.

5. FOR PORTION OF: Temporal UPDATE/DELETE

What it is: New clause for UPDATE and DELETE, FOR PORTION OF <period> FROM <start> TO <end>, that lets you modify or delete just a sub-range of a temporal (range-based) row, automatically splitting the surrounding range as needed.

Comparison (PG18 vs. PG19): PG18 introduced the constraint half of temporal tables: WITHOUT OVERLAPS for PRIMARY KEY/UNIQUE and PERIOD for foreign keys, letting you enforce that time ranges in a table never overlap. But it gave you no corresponding operation half. If you had a row valid from Jan–Dec and needed to change just the March–June portion, you had to manually delete the original row and insert two (or three) new rows representing the split ranges yourself, exactly the kind of fiddly, off-by-one-prone logic a database feature should be doing for you.

Pros:

• Closes the gap PG18 left half-finished: PG18 gave you guardrails (non-overlapping constraints), PG19 gives you the verbs (safely editing a slice of history without hand-rolling the split).
• Meaningful for any system tracking effective-dated data, pricing history, contract terms, HR assignment periods, insurance coverage windows.

Cons:

• Temporal tables remain a niche feature relative to PostgreSQL's overall audience, most applications don't model data this way, and adopting WITHOUT OVERLAPS + FOR PORTION OF is a genuine schema-design commitment, not a drop-in swap.

6. INSERT ... ON CONFLICT DO SELECT ... RETURNING

What it is: Extends ON CONFLICT beyond DO NOTHING/DO UPDATE with a new DO SELECT ... RETURNING option, returning (and optionally locking, via FOR UPDATE/FOR SHARE) the row that caused the conflict, without modifying it.

Comparison (PG18 vs. PG19): PG18 made real progress on returning row state, adding OLD/NEW alias support to RETURNING across INSERT/UPDATE/DELETE/MERGE, so you could see before-and-after values in a single statement. But ON CONFLICT itself was unchanged: still only DO NOTHING (silently skip, tell you nothing about the existing row) or DO UPDATE (you must actually modify something to get a RETURNING result). The common "get-or-create" pattern, insert a row if it doesn't exist, otherwise just give me the existing one, had no clean native expression; teams worked around it with a no-op DO UPDATE SET col = col just to trigger a RETURNING clause.

Pros:

• Directly solves get-or-create without a no-op write, which matters for both clarity and for avoiding unnecessary row versions from a fake update (fewer wasted row versions means less bloat, which loops back to why REPACK CONCURRENTLY matters less often).
• Optional row locking (FOR UPDATE/FOR SHARE) on the conflicting row means you can safely read-then-act on it within the same statement, useful for concurrent upsert-adjacent logic.

Cons:

• Another ON CONFLICT branch to learn and to get right in mixed application code that already juggles DO NOTHING and DO UPDATE logic; worth auditing existing upsert helper functions to see where this actually simplifies things versus where it's unnecessary.

7. TOAST Default Compression: pglz → lz4

What it is: The default compression algorithm for TOASTed (large, out-of-line) values changes from pglz to lz4.

Comparison (PG18 vs. PG19): lz4 TOAST compression already existed as an option in PG18 (and earlier), you could set default_toast_compression = lz4 or specify it per-column. But almost nobody did, because defaults are what most schemas actually run with. PG18 shipped with pglz as the out-of-the-box default; PG19 flips that default.

Pros:

• A genuinely free win for anyone storing large JSONB, text, or bytea values who never manually tuned this setting, lz4 compresses and decompresses meaningfully faster than pglz at a comparable ratio.
• Zero migration effort for new databases; the new default just applies.

Cons:

• Existing tables don't retroactively recompress, this only affects newly TOASTed values going forward (or values rewritten via REPACK/VACUUM FULL), so an existing large database won't see the benefit until data is naturally rewritten or explicitly reprocessed.

8. Parallel Autovacuum Workers

What it is: A single autovacuum job on one table can now use multiple parallel workers, controlled globally by autovacuum_max_parallel_workers and per-table by the autovacuum_parallel_workers storage parameter. PG19 also adds a scoring system (autovacuum_vacuum_score_weight, autovacuum_freeze_score_weight, and related variables) to decide which tables get processed first, replacing a simpler threshold-only heuristic.

Comparison (PG18 vs. PG19): PG18 improved autovacuum's behavior significantly: "eager freezing" let normal vacuums freeze all-visible pages proactively (reducing the cost of a later full freeze), autovacuum_worker_slots let you raise the effective worker cap at runtime without a restart, and autovacuum_vacuum_max_threshold let you set a fixed dead-tuple trigger point instead of relying purely on percentages. What PG18 didn't change: each individual vacuum job on a given table still ran as one single worker process. On a genuinely huge, high-churn table, that one worker was the throughput ceiling, no matter how many total autovacuum worker slots your cluster had available.

Pros:

• Directly attacks the specific case that bites teams running very large, high-write tables: a vacuum job that used to take hours on one worker can now split the work across several, on the same table, at the same time.
• The new scoring system for processing order is a real improvement over pure threshold checks, better prioritizing which of many candidate tables actually needs attention most urgently.
• Complements, rather than duplicates, PG18's improvements, PG18 made each vacuum pass smarter and more proactive; PG19 makes the biggest passes faster by parallelizing them.

Cons:

• More parallel workers means more concurrent I/O and CPU contention during vacuum; on a system already tight on resources, this needs the same careful tuning any parallelism feature does, it's not automatically a net win without headroom to spend.
• Per-table autovacuum_parallel_workers is one more tuning knob DBAs now need to understand and set deliberately for their biggest tables, rather than relying entirely on cluster-wide defaults.

9. Logical Replication: Native Sequence Synchronization

What it is: Sequences can now be included in logical replication. CREATE/ALTER PUBLICATION ... ALL SEQUENCES publishes all sequences, and ALTER SUBSCRIPTION ... REFRESH SEQUENCES syncs sequence values (not just existence) on the subscriber to match the publisher. pg_get_sequence_data() lets you inspect sync state directly.

Comparison (PG18 vs. PG19): PG18 made solid logical replication improvements, generated column values could finally be replicated, and the default streaming mode for new subscriptions switched from off to parallel for better apply performance. But sequences were entirely untouched by logical replication in PG18 and every prior version: a subscriber's sequences had no relationship to the publisher's. If you promoted a logically-replicated subscriber to primary during a failover, its sequences would still be wherever they last were on that subscriber, not caught up to the publisher's actual nextval() position, unless you manually reset them yourself before cutting traffic over.

Pros:

• Removes a genuinely dangerous, well-known logical-replication gap: without this, a poorly-timed failover onto a logically-replicated subscriber can hand out primary-key values that collide with rows the old primary already committed, a duplicate-ID bug hiding specifically in your disaster-recovery path, the worst possible place for a bug to hide since it only shows up when you're already in an incident.
• pg_get_sequence_data() gives visibility into sync state that simply didn't exist before, useful for confirming a subscriber is actually safe to promote.

Cons:

• This is a correctness fix for a previously-silent gap, not a performance feature, teams need to actively adopt ALL SEQUENCES and REFRESH SEQUENCES in their subscription setup; it isn't retroactively applied to existing subscriptions without action.

Which of These Actually Change How You Operate Postgres

Ranking by realistic production impact, not headline appeal:

1. REPACK CONCURRENTLY — removes a hard operational constraint (mandatory downtime for bloat reclaim) that has existed since the beginning of Postgres.
2. Parallel autovacuum — directly extends throughput on the exact tables where autovacuum has historically fallen behind.
3. Logical replication sequence sync — closes a real correctness gap sitting specifically in failover paths.
4. Partition merge/split — makes partition schemes maintainable as data grows, rather than fixed at design time.
5. SQL/PGQ — genuinely useful for a specific class of query (shallow hierarchical/relationship modeling), not a universal graph-database replacement, know which camp you're in before treating it as a headline reason to upgrade.
6. Everything else (GROUP BY ALL, FOR PORTION OF, ON CONFLICT DO SELECT, TOAST lz4 default) — real, welcome ergonomics and default-quality improvements, but incremental rather than architectural.

Where This Fits

If you're running the PostgreSQL In-Depth course, the maintenance-and-bloat modules built around pg_repack and manual VACUUM FULL tradeoffs are the direct prerequisite for understanding why REPACK CONCURRENTLY matters as much as it does, and the partitioning phase is the natural place to slot in the new merge/split DDL. For the deep architectural picture PG19 builds on top of (process model, MVCC, the planner, WAL), see "The PostgreSQL Elephant in the Room."

PostgreSQL 19 isn't a rewrite of what Postgres is, it's the same server process, managing files intelligently, that "What the PostgreSQL Server Is Actually Doing" describes, just with more of the maintenance and ergonomic rough edges sanded down. That's not a smaller story than "Postgres killed the recursive CTE", it's a more honest one.

#PostgreSQL #Database #Backend #SoftwareEngineering #DatabaseInternals #SQL #DevOps