PostgreSQL 19 Preview: Changes That Affect Schema Design and Operations (as of Beta 4)

Release Schedule and the Basis for This Post

PostgreSQL 19 Beta 4 was released on September 24, 2026. According to the official announcement, the next step is a release candidate (RC) in early October, and depending on test results, the general availability (GA) release may also come in October. Because it is still in beta, detailed behavior can still change. This post is based on the official release notes as of Beta 4.

First, something to be aware of. In Beta 4, the following features were removed from 19. They were deferred to a later major version for the sake of stability and a predictable schedule.

• SQL/PGQ (property graph queries)
• Enabling and disabling data checksums online
• Time-range UPDATE/DELETE using FOR PORTION OF
• ALTER TABLE ... MERGE PARTITIONS / SPLIT PARTITIONS

Some explanatory articles published during development presented these features, or features not in the Beta 4 release notes such as GROUP BY ALL, as features of 19. Check your upgrade plans against the release notes.

1. REPACK: Merging VACUUM FULL and CLUSTER, and Running Without Locking

When a table bloated from updates and deletes, you used VACUUM FULL or CLUSTER to reclaim space, but both lock the table for the entire run, which made them hard to use while the service is running. 19 adds REPACK, which replaces both commands, and with the CONCURRENTLY option it rewrites the table without blocking reads or writes.

-- Reclaim space (reads and writes allowed while running)
REPACK (CONCURRENTLY, VERBOSE) orders;

-- Also physically sort in index order (clustering)
REPACK (CONCURRENTLY) orders USING INDEX orders_ordered_at_idx;

Here is how it works. Changes that come in while the new table is being built are collected through logical decoding and applied, and the strongest lock (ACCESS EXCLUSIVE) is taken only for the brief moment when the files are swapped.

From a design perspective, what matters is the conditions under which CONCURRENTLY cannot be used.

• Tables with neither a primary key nor an index-based replica identity
• Partitioned tables and UNLOGGED tables
• When run inside a transaction block
• When max_repack_replication_slots does not allow any more replication slots

In other words, having a primary key on every table now decides whether you can use these operational tools at all. The official documentation also warns that CONCURRENTLY is not MVCC-safe, so read the caveats about the data seen by long-running transactions during execution (Section 13.6 of the documentation) before you use it.

2. CHECK Constraints Can Also Switch Between ENFORCED and NOT ENFORCED

Since PostgreSQL 18, CHECK and foreign key constraints can be declared NOT ENFORCED. In 19, you can change whether a CHECK constraint is enforced later on with ALTER CONSTRAINT (through 18, only foreign keys allowed this). When you switch it to ENFORCED, existing rows are checked against the rule, and then the rule is applied to new rows as well.

-- For now, only declare a rule that existing data may violate
ALTER TABLE orders
  ADD CONSTRAINT orders_amount_positive CHECK (amount_krw > 0) NOT ENFORCED;

-- Enforce it after cleaning up the violating data
ALTER TABLE orders ALTER CONSTRAINT orders_amount_positive ENFORCED;

You can use this for staged migrations: document the rule in the schema first, then actually enforce it once the data cleanup is done.

3. Faster Foreign Key Checks

The release notes overview lists faster foreign key checks among its performance improvements. This is a change in the direction of lower write costs for normalized schemas with many foreign keys (the size of the improvement varies by workload, so measure it yourself). In addition, inside CREATE SCHEMA you can now reference, as a foreign key, a table that has not been created yet.

4. Behavior Changes to Check Before Upgrading

These are the compatibility items that may affect applications.

• standard_conforming_strings is always on: if any code relies on backslashes inside ordinary string literals being interpreted as escapes, it needs to be checked
• json_array() calls with no result rows return an empty JSON array instead of NULL: queries that used IS NULL to detect "nothing" will get different results
• max_locks_per_transaction default 64 → 128
• Newlines (CR, LF) are not allowed in database, role, and tablespace names
• The default index opclass for inet and cidr changes from btree_gist to GiST: if you have been creating indexes on these types with the btree_gist extension, check this

Separately, casts between bytea and uuid are now allowed, so UUIDs received as binary can be converted directly.

5. Replication and Read Consistency

Logical replication of sequence values. Logical replication now replicates sequence values as well. When you do a major version upgrade through logical replication, this cuts down the manual work of syncing sequence values right before the switchover. Also, if wal_level is replica, you can enable logical replication without restarting the server.

"Read your own writes" with WAIT FOR LSN. In a setup that sends writes to the primary and reads to a standby, data you just wrote may not be on the standby yet. The new WAIT command lets you wait on the standby until changes up to a specific WAL position have been applied.

-- Primary: check the current WAL position right after writing
SELECT pg_current_wal_lsn();   -- e.g., 0/306EE20

-- Standby: read once changes up to that position are applied (wait up to 1 second)
WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '1s');
SELECT * FROM orders WHERE id = 1001;

With the NO_THROW option, a timeout returns a status (success, timeout, etc.) instead of an error.

6. Other Changes

• autovacuum cleans up indexes with parallel workers and introduces a scoring system that processes the tables that need it most first
• New jsonpath string methods: lower(), upper(), initcap(), replace(), split_part(), btrim()/ltrim()/rtrim()
• The pg_plan_advice extension for pinning and controlling planner decisions, and pg_stash_advice, which applies advice automatically per query

Checking Ahead with Erdmates

If you import your schema into an ERD before upgrading to 19, you can see at a glance the tables with no primary key marked (tables that can't use REPACK CONCURRENTLY) and your foreign key relationships. Erdmates imports PostgreSQL CREATE TABLE and COMMENT ON statements and draws them as an ERD.

Conclusion

In PostgreSQL 19, there are three things schema designers should take care of first: put a primary key on every table so you can use REPACK CONCURRENTLY; use the NOT ENFORCED → ENFORCED switch for CHECK constraints in staged migrations; and test behavior changes such as standard_conforming_strings and json_array() before upgrading. The feature list may change before the official release, so check the final release notes again.