Postgres 19 is out, and it's available on Neon and Databricks Lakebase

Hacker News by 13 min read 30x views
Postgres 19 is out, and it's available on Neon and Databricks Lakebase

Share Post

PostgreSQL 19 reached GA today and it's already accessible on Lakebase. New important versions frequently obtain weeks to attain another managed PostgreSQL services. Instead, we wanted to provision admission to it to our customers the identical day. 

Several engineers on the Lakebase squad are PostgreSQL committers and contributors, and they worked on this publish directly, from query scheme direction to shared memory, locale handling, and publish management. To try it, create a new Lakebase project and choose PostgreSQL 19 as the version.

What's new in PostgreSQL 19

PostgreSQL 19 is a applicable release. There is no sole header characteristic that changes what PostgreSQL is. Instead, type 19 goes following operational problems that repository teams cognize well: bloated tables, unstable query plans, replica lag, incomplete statistics, and care activity that is difficult to run safely in production.

The average thread is control. PostgreSQL 19 gives you additional ways to stage in whenever the defaults are not enough, without giving up the project's bias toward reliability and predictable behavior.

The complete catalog of changes is in the PostgreSQL 19 publish notes.

REPACK

PostgreSQL uses multiversion concurrency control, so an UPDATE creates a new row type and a DELETE leaves a deceased tuple behind. Routine vacuuming makes that area reusable, but it doesn't necessarily come back area to the functioning scheme or reconstruct a helpful bodily row order. Over time, heavily updated tables can rotate into bloated and poorly clustered.

The customary fixes have been VACUUM FULL, CLUSTER, or external tools specified as pg_repack. The built-in commands work, but they obtain an ACCESS EXCLUSIVE fastener for the entire operation, blocking normal admission to the table.

PostgreSQL 19 adds REPACK, which reclaims area during normal says and writes continue during most of the operation. For example, a conversation use can rebuild its messages array during clustering rows by conversation and dispatch period using an existing index:

REPACK reclaims area like VACUUM FULL; with USING INDEX, it additionally physically orders rows like CLUSTER. With CONCURRENTLY, PostgreSQL rebuilds the array during normal says and writes continue, capturing ongoing changes through logical decoding. It afterward takes an ACCESS EXCLUSIVE fastener to use any remaining captured changes and toggle the new array and indicator records into place. This phase is typically concise but can obtain longer on a occupied table.

There are operational constraints. Concurrent repacking creates an additional replication slot for the duration of the operation, so it needs liberated capability under max_repack_replication_slots and a array alongside either a non-deferrable chief key or a suitable index-based replica identity. It additionally needs impermanent disk area for a copy of the array and all its indexes, possibly affirmative a kind document up to the size of the table. Tables alongside dense update and delete traffic visage an additional limit: the current upstream PostgreSQL 19 implementation fails if additional than concerning 104 myriad tuples in the mark array are updated or deleted while REPACK (CONCURRENTLY) is running. This counts changes made during the repack, not rows already in the table.

Not all connection category is eligible. Long-running transactions can postpone the final lock. Concurrent repacking is additionally not MVCC-safe: following it commits, transactions using snapshots taken before the repack began can see the array as empty. Physical clustering additionally degrades as afterward writes arrive.

REPACK CONCURRENTLY motionless needs planning, but it brings into center a job that earlier required additional downtime or an extension.

Query scheme advice

The PostgreSQL cost-based optimizer uses array data and disbursal estimates to choose an implementation plan. Most of the time, that plant well.

Statistics depict idiosyncratic tables well, but genuine datasets frequently merge relationships that are harder to capture. Imagine that endeavor customers create up 0.1% of customers but create 30% of orders. Per-table data cannot grasp that cross-table correlation.

With an indicator on orders.customer_id, the planner may evaluation that all endeavor client has lone a few orders and choose a nested iteration that often probes the index. If those customers really document for 30% of orders, all lookup returns far additional rows than expected, and a hash associate may be additional appropriate.

PostgreSQL 19 adds pg_plan_advice and pg_stash_advice for these situations:

  • pg_plan_advice describes planner decisions as advice, applies supplied advice, and reports whether it was followed.
  • pg_stash_advice stores direction by query ID and applies it automatically whenever a matching query is planned

These components do not add cross-table data or substitute the planner. Instead, they let you intervene whenever an inaccurate evaluation produces a mediocre plan.

Consider a query that counts orders from endeavor customers:

Here, c is the pseudonym for the filtered customers table. The following direction asks PostgreSQL to location those customers on the inner flank of a hash join:

When PostgreSQL can use the advice, EXPLAIN reports feedback akin to:

matched method the resulting scheme complies alongside the advice. It does not average the new scheme is necessarily better; you motionless need to measure it against the genuine workload.

Unlike inline optimizer hints, scheme direction lives exterior the application’s query text. You can inspect the current plan, test focused advice, and afterward stash it by query ID.

pg_stash_advice is the component that applies stashed direction automatically. Create the expansion in all repository before using its stash-management functions:

Create a stash and affiliate the tested direction alongside the query ID reported by EXPLAIN (VERBOSE). Replace <query_id> with the identifier from your plan:

Configure the repository to use the stash for new connections:

Replace appdb alongside the name of your database.

Treat scheme direction as a stabilizer formerly you've diagnosed the problem. Data distributions change, cached prepared statements may keep using an older scheme for a while, and the optimizer is motionless correct additional frequently than it is wrong. 

When the planner unexpectedly switches a crucial query to a worse plan, PostgreSQL 19 gives you a supported way to stabilize it.

Parallel autovacuum and new data views

Autovacuum can now use parallel workers whenever vacuuming indexes, controlled by autovacuum_max_parallel_workers.  A new scoring scheme prioritizes eligible tables according to how urgently they need vacuuming or analysis.

New data views disclose additional of what the server is doing:

  • pg_stat_lock reports data by fastener type.
  • pg_stat_recovery shows improvement status.
  • pg_stat_autovacuum_scores shows how autovacuum prioritizes tables.

Query-level diagnostics enhance as well. EXPLAIN (ANALYZE, IO) reports asynchronous I/O activity, and auto_explain.log_io can contain the identical data in logged plans.

PostgreSQL 19 additionally reports bytes written for full-page images in pg_stat_wal, EXPLAIN (ANALYZE, WAL), and vacuum and analyze logs.

Together, these create achievement investigations small reliant on inference. You can see why care activity was prioritized, anywhere asynchronous I/O happened, and how many WAL bytes came from full-page images.

Logical replication

Logical replication has rotate into center infrastructure for alter data capture, migrations, real-time serving, and ETL pipelines. PostgreSQL 19 closes multiple long-standing gaps:

  • Logical decoding can be activated without a restart when wal_level is replica. Creating the archetypal valid logical replication slot raises effective_wal_level to logical, showing the WAL flat currently in effect.
  • Sequence values can be synchronized from publishers to subscribers.
  • Publications can contain all sequences with FOR ALL SEQUENCES.
  • CREATE PUBLICATION ... FOR ALL TABLES EXCEPT (...) publishes all tables apart from a named set.
  • Subscriptions can reuse association particulars from a postgres_fdw server with CREATE SUBSCRIPTION ... SERVER.
  • The retain_dead_tuples subscription choice preserves adequate data to detect update_deleted conflicts, anywhere a replicated update targets a row deleted by another origin. Detection additionally requires track_commit_timestamp on the subscriber, and pg_stat_subscription_stats counts these conflicts.

Sequence synchronization removes an clumsy immigration border case, particularly during major-version upgrades that use logical replication. Copying array rows without advancing their sequences can depart a promoted subscriber generating values that already exist, causing duplicate-key errors. 

PostgreSQL copies the first sequence values whenever the subscription is created with copy_data = true, the default. Later sequence changes are not streamed continuously, so refresh them immediately before cutover:

This updates the sequences already known to the subscription to their current values on the publisher. If the publish has gained new sequences, use ALTER SUBSCRIPTION ... REFRESH PUBLICATION to add them and synchronize their values before cutover.

WAIT FOR LSN

Asynchronous peruse replicas add peruse capability without making chief commits delay for them. The tradeoff is replication lag. An use can commit a compose on the primary, immediately peruse from a replica, and not discover the row since the replica has not replayed the applicable WAL yet. 

PostgreSQL 19 adds a straightforward way to coordinate that handoff. An LSN identifies a stance in the PostgreSQL WAL, and WAIT FOR LSN blocks the standby until replay reaches at smallest the requested position:

By default, WAIT FOR LSN waits indefinitely for the standby to replay the requested LSN. Other modes can delay for WAL to be written or flushed, and you can set a timeout.

The client or association pooler motionless has to transport the LSN from the chief to the replica. The repository now provides the missing synchronization step, so read-your-writes behavior no longer needs polling or arbitrary sleeps.

Statistics import for postgres_fdw

Foreign data wrappers let PostgreSQL query distant systems as if their data were local. Planning those queries has continually been difficult whenever the local optimizer does not cognize the distant table’s size or data distribution.

PostgreSQL 19 lets postgres_fdw import data from the distant server:

ANALYZE imports the existing distant data into the local server. If the import fails, postgres_fdw falls rear to row sampling. The distant data hence need to be current.

Without distant statistics, PostgreSQL may depend on fallback size estimates and choose a nested loop. After importing them, the planner can evaluation row counts additional accurately and may choose a hash associate instead.

Federated queries motionless pay network and remote-system costs, but exact row estimates eliminate a important origin of avoidable bad plans.

ON CONFLICT DO SELECT

INSERT ... ON CONFLICT DO SELECT gives you a spotless get-or-create form that returns the two existing and newly inserted rows:

It replaces the DO UPDATE SET col = EXCLUDED.col workaround, which performs an update equal although no values change. That creates a new row type and leaves a deceased tuple on all conflict.

JSON output for COPY TO

COPY TO can now emit JSON directly. By default, it writes one entity per line, while FORCE_ARRAY wraps the output in a sole JSON array:

The new JSON format is export-only, so COPY FROM does not obtain it.

Smaller SQL and achievement changes

Lastly, a few compact changes alongside a quality-of-life payoff:

  • JSON way expressions acquire acquainted cord methods specified as btrim(), lower(), replace(), and split_part().
  • The random(min, max) family now supports date, timestamp, and timestamptz, which helps alongside tests and synthetic datasets.
  • Five opening functions accept IGNORE NULLS and RESPECT NULLS.
  • When io_method = worker, I/O workers measure automatically between io_min_workers and io_max_workers. Foreign-key checks are faster, and the planner and administrator get another circular of targeted optimizations.

We additionally misplaced a few features

Several important features landed during PostgreSQL 19 betterment but were removed before release. Reviewers established correctness or scheme problems that were too risky to determine delayed in the cycle, so the community held the features for a afterward release.

Some of the features that eventually won't container in 19:

Thanks to the PostgreSQL contributors  

PostgreSQL is made by its contributors: appreciate you to the hundreds of community contributors credited in the release notes. Special gratitude to the engineers on the Lakebase squad who worked on this type of PostgreSQL: 

  • Robert Haas  wrote pg_plan_advice and pg_stash_advice (the second alongside Lukas Fittl)
  • Heikki Linnakangas  worked on a new scheme for registering shared memory, a rework of how subsystems allocate it, multixact fixes, and the new default of 128 for max_locks_per_transaction
  • Jeff Davis  wrote the alter that lets CREATE SUBSCRIPTION reuse a postgres_fdw server's association details, and did a lengthy run of locale and character-handling work
  • Jonathan Katz worked on the PostgreSQL 19 media publish and handled publish shepherding
  • Matthias van de Meent worked on multiple commits including the alter to fixed indicator admission method routines
  • Alexander Lakhin established and reported multiple bugs

Upgrading to PostgreSQL 19

As alongside any new important type of PostgreSQL, we propose a measured method to adopting it. Before moving manufacturing workloads to 19.0, we propose you:

  • Test your own workloads, queries, and extensions on PostgreSQL 19 in a betterment or staging surroundings first
  • Compare achievement and behavior against your current version, since important releases can alter query plans and defaults
  • Check that the extensions you depend on are accessible on PostgreSQL 19 (see below).
  • Keep manufacturing on your current type until you're comfortable alongside the results

PostgreSQL19 additionally changes multiple defaults and compatibility behaviors. Check these before you upgrade:

  • JIT compilation is off by default
  • TOAST compression defaults to LZ4 alternatively of pglz
  • MD5 password authentication emits stronger deprecation warnings
  • RADIUS authentication assistance is removed
  • standard_conforming_strings is continually on
  • pg_upgrade rejects clusters alongside affected btree_gist indexes on inet or cidr
  • Database, role, and tablespace names can no longer merge wagon returns or row feeds

A note on extensions

We've covered most of the extensions supported in our PostgreSQL 18 release, but not all of them are accessible on PostgreSQL 19 yet. We're adding additional as they rotate into accessible from the expansion providers. If your workload depends on a particular extension, confirm it's accessible on PostgreSQL 19 before you scheme a migration.

The community typically ships its archetypal insignificant publish a few months following a important version, as real-world use surfaces fixes and refinements. PostgreSQL 19.1 is anticipated in February, and many teams scheme manufacturing upgrades about that release. That's a average and sensible form for any .0 release, and it's fair as true here.

Whatever your timeline, we're current to help. If you'd akin to conversation through upgrade preparedness or testing, attain out to your document squad or Databricks support.

Try it 

PostgreSQL 19 is accessible on Lakebase today. Create a PostgreSQL 19 project in your workspace to try it, or get started alongside Lakebase if you're new to it.

Other Article Hacker News
↑
Close Right Ads
Close Left Ads