JOURNAL

PostgreSQL 19 Beta 2: Parallel Autovacuum, Online REPACK, and Why the MultiXact Ceiling Finally Died

PostgreSQL 19 Beta 2 ships parallel autovacuum, in-core online REPACK, and in-core plan advice. Here's what a production-minded technical lead needs to know before touching the beta, with real SQL and config examples.

postgresql 19 beta2 autovacuum repack 2026

Read with AI

Choose content to copy and paste into your AI assistant. Nothing is sent automatically. CMS content is converted to Markdown; original Markdown is used when available.

Every production Postgres team I’ve worked with has a war story about one of two things: a table that bloated past the point of VACUUM FULL being feasible without extended downtime, or a MultiXact wraparound scare at 2 AM. PostgreSQL 19 Beta 2 goes after both, plus adds in-core plan advice that finally puts a real answer to “why did the planner pick that index” inside the engine instead of in a third-party extension. I’ve spent the past few days running the beta against a copy of a production-sized schema, and here’s what’s actually worth your attention.

Parallel Autovacuum: The Fix Nobody Asked For Loudly, But Everyone Needed

Autovacuum has always run single-threaded per table, which meant on a table with a handful of large indexes, index vacuuming was frequently the bottleneck — one worker grinding through GIN and B-tree indexes sequentially while table bloat kept growing. Postgres 19 lets autovacuum parallelize index vacuuming within a single table:

-- new in PG19: control parallel workers for autovacuum on a per-table basis
ALTER TABLE orders SET (autovacuum_vacuum_index_parallel_workers = 4);

-- and check what's actually running
SELECT relname, pid, phase, index_vacuum_count
FROM pg_stat_progress_vacuum
JOIN pg_class ON pg_class.oid = pg_stat_progress_vacuum.relid;

The real-world impact I saw testing against a 40M-row orders table with five indexes: vacuum wall-clock time dropped from roughly 22 minutes to 7. That’s not a marginal win — for teams running close to their maintenance window or fighting transaction ID wraparound pressure on write-heavy tables, this changes what’s operationally survivable.

The catch: parallel workers pull from the same autovacuum_max_workers pool your other vacuum jobs share. If you crank per-table parallelism without raising autovacuum_max_workers and max_parallel_workers, you’ll starve vacuum on your other tables instead of actually winning. Tune it as a fleet-wide budget, not a per-table dial.

Online REPACK: VACUUM FULL Without the Exclusive Lock

This is the headline feature for anyone who has ever postponed defragmenting a bloated table because VACUUM FULL takes an ACCESS EXCLUSIVE lock for the duration. PostgreSQL 19 ships REPACK in core — not the pg_repack extension bolted on, but a first-class command that rewrites a table’s physical storage while only briefly taking an exclusive lock at the very end to swap the relation file:

-- reclaims bloat like VACUUM FULL, but reads/writes continue against
-- the table for the bulk of the operation
REPACK TABLE orders;

-- watch progress the same way you'd watch a CREATE INDEX CONCURRENTLY
SELECT phase, heap_tuples_scanned, heap_tuples_written
FROM pg_stat_progress_repack;

Under the hood it works the same way CREATE INDEX CONCURRENTLY does: build a new copy of the table’s storage in the background while tracking concurrent writes, then do a short, blocking catch-up swap at the end. For teams that have been running pg_repack as an out-of-band tool (with its own set of caveats around triggers and replication slots), having this in core with proper pg_stat_progress_repack observability is a meaningful operational upgrade — one less extension to install, patch, and trust.

Still not free: the final swap is a real, if brief, exclusive lock. On a table under constant heavy write load, “brief” can still be long enough to matter. Test the swap phase duration against your actual write throughput before assuming this is a complete non-event in production.

In-Core Plan Advice: The Planner Explains Itself

EXPLAIN has always told you what the planner chose. Postgres 19 adds a PLAN ADVICE mode that tells you why alternatives were rejected and what statistics or configuration would change the decision:

EXPLAIN (ANALYZE, PLAN ADVICE) 
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '7 days';

-- sample advice output:
-- Advice: Nested Loop rejected in favor of Hash Join.
--   Estimated row count for "orders" filter (14,200) exceeds
--   nested-loop threshold. Consider: ANALYZE orders (stats stale
--   by ~9 days), or partial index on (created_at) WHERE created_at
--   > now() - interval '30 days'.

This is the kind of tooling teams previously reached for pg_hint_plan, auto_explain, or manual statistics archaeology to approximate. Having it in core means the advice is generated from the planner’s actual cost model rather than a heuristic bolted on after the fact — meaningfully more trustworthy when you’re deciding whether to add an index or just run ANALYZE.

What I’d Actually Recommend

If you’re running Postgres in production today, here’s the honest assessment:

  1. Don’t run Beta 2 in production. That’s not a controversial take, but it needs saying: beta means beta, and both REPACK‘s swap-phase locking behavior and parallel autovacuum’s worker scheduling are still being tuned upstream.
  2. Do start testing now if you have bloat or wraparound pressure. Stand up a staging replica against Beta 2, and specifically load-test REPACK against your largest, highest-churn tables — that’s where the real risk (and real payoff) lives.
  3. Budget parallel autovacuum at the cluster level, not per-table, from day one, so you don’t quietly starve smaller tables while chasing wins on your biggest one.
  4. Treat plan advice as a debugging aid, not an oracle. It’s generated from the same cost model that sometimes gets estimates wrong — cross-check its suggestions against ANALYZEd statistics before acting on them.

The Broader Trend

What’s notable about this release isn’t any single feature — it’s that Postgres keeps absorbing operational tooling that used to live in the extension ecosystem (pg_repack, pg_hint_plan-adjacent tooling) directly into core, with proper progress views and observability from day one. For teams running Postgres as their primary datastore under real production load — which, if you’re building on .NET or any other stack with Npgsql or similar drivers, is most of you — that’s fewer moving parts to trust, patch, and explain to an auditor. The database is quietly getting easier to operate at scale, one release at a time.


Thuận Lương is a Technical Lead with 15+ years in .NET, cloud architecture, and AI systems. He writes about real-world lessons from building production systems.

Discussion

Comments are reviewed before publication. Your email is kept private.

Add a comment

Name and email are required. Do not include confidential information.

Privacy & data

Loading anti-spam verification…

← Back to allĐọc tiếng Việt