PostgreSQL 19: REPACK, Temporal Tables, No Downtime

PostgreSQL 19 Beta 3 is out as of August 13, and stable is weeks away. This is not a patch release. It ships built-in concurrent table repacking, native temporal DML, SQL:2023 property graph queries, and the closest thing to official query hints Postgres has ever had. If you run Postgres in production, this release changes your maintenance playbook.

REPACK CONCURRENTLY: Vacuum Downtime Is Over

The single most impactful change in PostgreSQL 19 is one that doesn’t touch your SQL queries at all. REPACK — now a first-class SQL command — consolidates VACUUM FULL and CLUSTER into a single operation, and its CONCURRENTLY mode does it without killing your application.

VACUUM FULL takes an ACCESS EXCLUSIVE lock from start to finish. That means zero reads, zero writes, for the entire rewrite duration. On a multi-hundred-GB table in production, that’s minutes of hard downtime. The pg_repack extension existed as a workaround, but it was an extension — something to install, version-pin, and explain to every new team member.

REPACK CONCURRENTLY runs in three phases: it takes a snapshot and copies tuples to a new heap, replays ongoing changes via logical decoding, then acquires a brief exclusive lock only for the final swap. The table stays readable and writable throughout the bulk of the operation.

The River team tested this on a ~500MB job queue table. Under VACUUM FULL, throughput dropped to zero for about four seconds. Under REPACK CONCURRENTLY, throughput dipped but never hit zero — the difference between a scheduled maintenance window and maintenance you can run at noon on a Tuesday.

-- Block reads and writes (old approach)
VACUUM FULL table_name;

-- Keep the table live (PostgreSQL 19)
REPACK table_name CONCURRENTLY;

The trade-off: CONCURRENTLY requires extra disk space equal to roughly the table size during the operation. Budget for it before running against your largest tables.

Temporal Tables: FOR PORTION OF Finally Lands

Temporal data is one of those problems every team solves badly the first time. Booking systems, insurance policies, SaaS subscription periods — they all involve records that need to be updated or deleted within a specific time range while leaving the rest of the history intact. Most teams handle this at the application layer, with manual row splitting, range checks, and the inevitable edge case that breaks at 11 PM.

PostgreSQL 19 implements FOR PORTION OF from SQL:2011. When you target a time range in an UPDATE or DELETE, PostgreSQL automatically partitions overlapping rows, modifies the targeted portion, and inserts temporal leftovers — new rows representing the untouched history — atomically. The depesz deep dive is worth reading before you implement this in production.

-- Increase coverage only during hurricane season
UPDATE policy_coverage
FOR PORTION OF coverage_period FROM '2025-06-01' TO '2025-11-30'
SET coverage_amount = coverage_amount * 1.5
WHERE policy_id = 500;

One gotcha: INSERT triggers fire on leftover rows even though the originating operation was an UPDATE or DELETE. If your triggers have side effects — audit logs, notification queues, CDC streams — audit them before rolling this out.

SQL/PGQ: Graph Queries Without a Graph Database

PostgreSQL 19 implements SQL:2023 property graph queries. You define a graph view over your existing relational tables — no data migration, no new storage engine, no separate Neo4j instance for your authorization dependency graph — and query it with Cypher-like pattern matching syntax.

CREATE PROPERTY GRAPH company_graph
  VERTEX TABLES (employees, departments)
  EDGE TABLES (
    reports_to SOURCE KEY (manager_id) REFERENCES employees,
    belongs_to SOURCE KEY (dept_id) REFERENCES departments
  );

SELECT *
FROM GRAPH_TABLE(company_graph
  MATCH (e:employees)-[r:reports_to]->(m:employees)
  COLUMNS (e.name AS employee, m.name AS manager)
);

The current implementation supports fixed-depth queries. Variable-length path traversal — what you would need for arbitrary-depth org chart flattening or multi-hop fraud graph analysis — is planned for a future release. For authorization graphs, dependency resolution, and product relationship queries, fixed-depth is usually sufficient. For serious graph analytics at scale, a dedicated graph database is still the right tool.

Parallel Autovacuum and Better Observability

Autovacuum has always processed indexes sequentially. On tables with many indexes, that single-threaded index cleanup was the bottleneck that let vacuum fall behind on write-heavy workloads. PostgreSQL 19 adds autovacuum_max_parallel_workers to parallelize index vacuuming — disabled by default, set it explicitly in postgresql.conf.

The bigger day-to-day win is observability. pg_stat_autovacuum_scores finally surfaces the scoring logic that determines which tables autovacuum prioritizes. Combined with log_lock_waits now on by default, plus new pg_stat_lock and pg_stat_recovery system views, PostgreSQL 19 gives operators substantially more visibility into what the database is actually doing.

Breaking Changes: Read This Before You Upgrade

Four changes that can ruin your upgrade if you skip the release notes:

• standard_conforming_strings is now permanently ON. Dumps made with it set to off will fail to restore. Create fresh dumps with PostgreSQL 19 tooling before migrating.
• JIT is disabled by default. Analytical workloads that relied on automatic JIT need jit = on added explicitly.
• RADIUS authentication is removed. Update pg_hba.conf before upgrading — connections from RADIUS-configured users will fail.
• btree_gist indexes on inet/cidr columns must be dropped before pg_upgrade. The default opclass changed. Drop, upgrade, recreate.

Test It Now

Beta 3 is available at postgresql.org/download. The upgrade path is pg_upgrade from PostgreSQL 14+, or dump-and-restore from any supported version. AWS RDS also has Beta 3 in its Database Preview Environment if you want to test without managing an instance.

Don’t wait for GA to start compatibility testing. The temporal table and graph query features in particular may suggest schema changes — adding application-time range columns, restructuring FK-heavy tables as explicit graph edges — that are design decisions worth making before you are on a production upgrade timeline. Bytebase has a solid overview of the features worth evaluating first.