Postgres 19 at Beta 4: five features shipping, two pulled, and the timeline to GA

I run a handful of consumer apps on hosted Postgres. Every supabase/config.toml I own still says major_version = 17, a year after Postgres 18 went GA, because Supabase's 18 rollout is still not done. RDS had 18 within seven weeks and Neon had a preview the next day, so your lag depends on your host. For a lot of us a "what's new in 19" post is really a "what to plan for" post. I read the release notes and all four beta announcements so you can skim this instead.

1. INSERT ... ON CONFLICT DO SELECT ends the get-or-create dance

You can now insert a row or fetch the existing one in a single atomic statement, and RETURNING hands you the row either way. Before 19, "get or create" meant one of two hacks. Either a fake update (DO UPDATE SET id = excluded.id) that wrote WAL and left a dead tuple behind for nothing, or two round trips with a race between them.

INSERT INTO profiles (user_id, plan)
      VALUES ($1, 'free')
      ON CONFLICT (user_id) DO SELECT
      RETURNING *;

Three rules from the INSERT docs. RETURNING is mandatory, unlike DO NOTHING and DO UPDATE. A conflict target is mandatory. You need SELECT privilege on the table. You can also add DO SELECT FOR UPDATE to lock the existing row, which is what you want when the next statement modifies it. That variant also needs UPDATE privilege on at least one column.

Someone will suggest the CTE: WITH ins AS (INSERT ... ON CONFLICT DO NOTHING RETURNING *) SELECT * FROM ins UNION ALL SELECT .... That races too. A row committed by another session after your snapshot started isn't visible to the fallback SELECT, so under load you get zero rows back. DO SELECT reads the conflicting row directly.

My signup trigger does insert into profiles ... on conflict (id) do nothing. When the row already exists, DO NOTHING returns nothing, so every caller that needs the row does a second SELECT. In 19 that's one honest statement.

2. IGNORE NULLS finally works in window functions

"Carry forward the last non-null value" no longer needs a self-join, a correlated subquery, or a custom aggregate. Postgres 19 adds the SQL-standard null treatment clause to lag, lead, first_value, last_value, and nth_value. The default stays RESPECT NULLS, so nothing changes until you ask.

SELECT day,
             last_value(price) IGNORE NULLS OVER (
               ORDER BY day
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
             ) AS price_filled
      FROM daily_prices;

Oracle, Snowflake, and DuckDB have had this for years. It matters for any sparse time series: sensor readings, price ticks, feature flags that only log changes. Mood trackers are the classic case. People skip logging on weekends, and every chart wants the last known value carried forward.

3. WAIT FOR LSN gives you read-your-writes on a replica

After a write on the primary, capture the write-ahead log position, then make the replica wait for it before you read. The classic bug this kills: a user saves, the page reloads, the read hits a replica that is 40 ms behind, and the user sees their old data.

-- On the primary, right after the commit
      SELECT pg_current_wal_insert_lsn();
      -- 0/0306EE20

      -- On the replica, before the read
      WAIT FOR LSN '0/0306EE20' WITH (TIMEOUT '200ms', NO_THROW);
      SELECT * FROM orders WHERE user_id = $1;

Stash the LSN from the write response in the session or a cookie, and send it with the next read. WAIT FOR returns success, timeout, or not in recovery, so your app can fall back to the primary when the replica is too far behind. Restrictions worth knowing: it must be a top-level command (no functions or DO blocks), and it has to be the first statement of a transaction or run outside one. It is also rejected outright if your session already holds a lock and the target LSN hasn't arrived. The MODE option picks what you wait for. standby_replay is the default and the one that gives you read-your-writes. standby_flush waits only until the WAL is durable on the replica, and standby_write only until it is received.

4. REPACK CONCURRENTLY rebuilds a bloated table without the long lock

REPACK unifies VACUUM FULL and CLUSTER into one command (the old ones stay for compatibility), and CONCURRENTLY takes the ACCESS EXCLUSIVE lock only for the final file swap. Under the hood it uses logical decoding to capture the writes that land during the rebuild and applies them before the swap. It's pg_repack, in core.

REPACK (CONCURRENTLY, VERBOSE) events USING INDEX events_created_at_idx;

Read the caveats before you run it on a hot table. The docs say plainly that REPACK CONCURRENTLY is not MVCC-safe. It needs free disk at least equal to the table plus its indexes, and a bit more to buffer the concurrent writes. The table needs a primary key or an index-based replica identity, and it can't be partitioned, unlogged, or a materialized view. It can't run inside a transaction block, DDL on the table from another session can make it fail, and you need the MAINTAIN privilege. VACUUM FULL blocks reads and writes for the entire rewrite, which on a 50 GB events table is a very long outage. I'd upgrade for this alone.

5. pg_plan_advice: plan hints, the sanctioned kind

Postgres has resisted Oracle-style query hints for about twenty years. pg_plan_advice is the closest thing now in contrib. It's a loadable module that prints the advice string reproducing the plan you have. Set that string and the planner is steered toward that plan while you fix the real cause.

LOAD 'pg_plan_advice';

      EXPLAIN (COSTS OFF, PLAN_ADVICE)
      SELECT * FROM orders o JOIN customers c ON c.id = o.customer_id;
      -- the plan output now includes the advice that reproduces it

      SET pg_plan_advice.advice = 'JOIN_ORDER(o c) HASH_JOIN(c)';

The advice vocabulary covers join order, join method, scan method, and parallelism: JOIN_ORDER, HASH_JOIN, NESTED_LOOP_PLAIN, SEQ_SCAN, INDEX_SCAN, NO_GATHER. Two honest limits. Advice can only pick among plans the planner already considers, and a SET only affects your own session. For the app's connections you either apply it with ALTER DATABASE ... SET pg_plan_advice.advice = ..., or use the companion pg_stash_advice extension, which stores advice per query id and applies it automatically. Turn on pg_plan_advice.feedback_warnings and the planner tells you when it couldn't follow your advice. The 3 a.m. use case is a plan that flips after an ANALYZE on a table that just crossed a size threshold. Stash it, page nobody, fix the statistics in the morning.

The 2 that got pulled before release

SQL/PGQ graph queries (reverted September 7)

Beta 1 shipped CREATE PROPERTY GRAPH and GRAPH_TABLE, the SQL:2023 way to run graph queries over ordinary tables. Friends-of-friends, dependency chains, and permission graphs in plain SQL with no Neo4j. It was reverted by its own committer, Peter Eisentraut, with unresolved problems: DROP TABLE ... CASCADE left orphaned graph metadata, label scoping was wrong, and pg_dump hit dependency loops with materialized views that queried GRAPH_TABLE. Tom Lane's take on the risk: "I'd be willing to bet dinner that if we ship it in v19 there will be post-release bug discoveries that are unfixable until v20." The earliest it can return is Postgres 20, expected around September 2027.

FOR PORTION OF temporal updates (reverted September 15)

UPDATE prices FOR PORTION OF valid_range FROM '2026-07-01' TO '2026-10-01' SET price = 34.99 would have split a row automatically and completed the SQL:2011 temporal feature set. It was pulled for a concurrency anomaly at READ COMMITTED, the default isolation level. Two sessions updating overlapping ranges could leave the second one silently updating zero rows. The feature's own documentation described the problem and prescribed an explicit row lock as the workaround, and the community chose to revert rather than ship with a footnote. Dimitri Fontaine has the full walkthrough.

Also pulled this cycle: GROUP BY ALL (reverted in July, listed in the Beta 3 notes), ALTER TABLE ... MERGE/SPLIT PARTITIONS (August 27, the second time it has been pulled before release, after Postgres 17 in 2024), and online data checksums (September 16). Five feature reverts in one beta cycle is unusual, and I read it as the project protecting its five-year support promise.

Defaults that change under you

None of these need new SQL. All of them can surprise you on upgrade day. Every one is listed in the migration section of the release notes.

  • JIT is off by default. If a reporting query got faster from JIT, it gets slower again unless you turn it back on.
  • max_locks_per_transaction doubles from 64 to 128. An explicit setting keeps your old value, so check your config.
  • log_lock_waits is on by default. Expect more log volume on a contended database.
  • default_toast_compression becomes lz4. Faster, slightly larger, and it only affects newly compressed values. Existing TOAST data stays as it is.
  • RADIUS authentication is gone and md5 logins now warn on every successful login. Move to scram-sha-256.
  • standard_conforming_strings can no longer be turned off. Old dumps that relied on it won't load.
  • btree_gist indexes on inet and cidr are flagged as broken. GiST is the new default opclass for those types. pg_upgrade disallows any cluster that still has one of the old indexes, so rebuild them first.
  • json_array() over zero rows returns [], not NULL. Any IS NULL check or coalesce on that result changes behavior silently.
  • Database, role, and tablespace names can't contain a carriage return or line feed. pg_upgrade rejects them, so rename first.

What to do this week

If you're on Supabase you probably won't run 19 in production before well into 2027. On RDS or Neon it could arrive within weeks of GA. Either way, you can prep now. Diff the defaults list against your postgresql.conf, because every one of them is a config change you can stage in advance. Then find your get-or-create paths and your fill-forward queries and mark them, because those are the first two things to rewrite the day your host offers 19. And try ON CONFLICT DO SELECT against a copy of your schema on the beta, not in production:

docker run -d --name pg19 -e POSTGRES_PASSWORD=pg -p 5433:5432 postgres:19beta4
      psql -h localhost -p 5433 -U postgres

Postgres 19 upgrade checklist: the defaults and removals to check before you upgrade

I write these from real work at astraedus.dev, where I build apps and tools. Building something, or stuck on something like this? Reach me at astraedus.dev or [email protected].

Get the next one in your inbox → subscribe at astraedus.dev.