PostgreSQL 19 Interactive Tour

PostgreSQL 19 is successful beta, pinch wide readiness expected astir September aliases October 2026, truthful it’s a bully clip to get a caput commencement connected what’s new. The official merchandise notes are the charismatic record: meticulously assembled, complete down to the perpetrate and the contributors down each change. This article is simply a hands-on companion to them, taking a action of those entries and turning each into a runnable illustration truthful you tin spot really the caller behaviour really works.

Before we commencement digging into the caller features, let’s group the context.

This article is based connected the charismatic merchandise notes and the PostgreSQL root code, licensed nether the PostgreSQL License. This is not an exhaustive list; spot the official merchandise notes for that.

Every illustration beneath was tally against PostgreSQL 19 beta 3 (released 2026-08-13) and the output is what that server really printed.

Links constituent to the archiving (𝗗), the astir applicable commits (𝗖), and authors (𝗔) for each feature; cheque them retired for motivation, usage, and implementation details. The authors (𝗔) are the group credited successful the merchandise notes for the feature, which usually intends the spot authors alternatively than a azygous main author.

With the discourse set, let’s commencement exploring the caller features.

Property chart queries

#

This is the header of the release. PostgreSQL 19 implements SQL/PGQ, the property-graph portion of SQL:2023. You state a property graph complete existing tables, past query it pinch shape matching alternatively of penning the joins yourself.

Two mean tables, 1 chart connected apical of them:

Nothing is copied: societal is simply a view-like entity that says “person rows are vertices, follows rows are edges”. Now you tin lucifer patterns pinch GRAPH_TABLE, wherever -[...]-> is simply a directed edge:

The payoff is multi-hop patterns. Chaining 2 edges gives you friends-of-friends without a self-join, and an quiet () intends “some vertex I don’t attraction to name”:

There’s nary caller execution motor here, and that’s the point. GRAPH_TABLE is rewritten into a plain relational query, truthful the planner, the statistics, and the scale choices you already cognize each still apply:

One limitation to cognize earlier you scheme a migration disconnected a chart database: this first trim has nary variable-length paths. Quantifiers for illustration -[IS follows]->{1,3} parse but are rejected pinch constituent shape quantifier is not supported, truthful a shape has to spell retired each hop.

• 𝗗 Graph Queries, Property Graphs, CREATE PROPERTY GRAPH
• 𝗖 2f094e7, c5b3253, a0dd070
• 𝗔 Peter Eisentraut, Ashutosh Bapat

Temporal updates and deletes

#

The caller FOR PORTION OF clause connected UPDATE and DELETE operates connected a portion of a scope column. Instead of rewriting a full validity period, you sanction a sub-period and PostgreSQL splits the statement for you.

The array starts pinch a azygous row: 1 value valid for each of 2026. Change it for July only, and query it again:

One statement in, 3 rows out, and the untouched periods support the aged price.

DELETE useful the aforesaid way, trimming alternatively of splitting. This snippet starts from the aforesaid untouched full-year row; deleting from December to the extremity of clip leaves the earlier information behind:

NULL arsenic a bound intends unbounded, truthful TO NULL is “from December onwards”. This lands alongside a new archiving section connected temporal tables, which is worthy reference if you support history successful scope columns.

• 𝗗 Temporal Tables, UPDATE
• 𝗖 8e72d91, b6ccd30
• 𝗔 Paul A. Jungwirth

Upsert that returns the statement you mislaid to

#

INSERT ... ON CONFLICT DO NOTHING ... RETURNING has ever had an annoying hole: the rows that conflicted are simply absent from the result, truthful you can’t show “already there” from “never happened”. To hole that, you could usage ON CONFLICT DO UPDATE, but that only useful if you are consenting to constitute to each conflicting row.

PostgreSQL 19 adds ON CONFLICT DO SELECT, which returns the existing rows without rubbing them. Here widget already exists pinch qty = 7:

Both rows travel back, and widget reports 7 (the worth already successful the table), not the 1 we tried to insert.

DO SELECT besides takes a locking clause, truthful FOR UPDATE holds the conflicting rows while you determine what to do pinch them. It besides gives you a measurement to show the 2 kinds of statement apart: locking a statement stamps its xmax, truthful the xmax = 0 trick tells you which rows were really inserted:

The locking clause is what makes that work: without 1 location is nary fastener to stamp, xmax stays 0 connected the conflicting statement too, and each statement claims was_inserted = t. Any locking clause will do, FOR SHARE included.

• 𝗗 INSERT
• 𝗖 8832709
• 𝗔 Andreas Karlsson, Marko Tiikkaja, Viktor Holmberg

Window functions tin skip NULLs

#

lead(), lag(), first_value(), last_value(), and nth_value() now judge the SQL-standard IGNORE NULLS clause (and its default counterpart, RESPECT NULLS).

The classical usage for this is filling gaps successful sparse data. Here a sensor only reports erstwhile the somesthesia changes, leaving NULLs successful between:

carried is simply a last-observation-carried-forward fill, and prev_reported is the erstwhile real reference alternatively than the erstwhile row. Before 19 this needed a nested subquery pinch a grouping instrumentality complete count(temp); now it’s a keyword.

• 𝗗 Window Functions
• 𝗖 25a30bb
• 𝗔 Oliver Ford, Tatsuo Ishii

REPACK

#

VACUUM FULL and CLUSTER did almost the aforesaid point (rewrite a array to reclaim space) pinch 2 confusing names and nary measurement to debar an ACCESS EXCLUSIVE lock. PostgreSQL 19 unifies them nether REPACK.

Take a array pinch half its rows deleted:

The important portion is the caller CONCURRENTLY option, which rebuilds the array without an ACCESS EXCLUSIVE lock. It useful by decoding the changes that onshore during the rebuild and replaying them, truthful sounds and writes support running:

That 1 isn’t runnable here: the decoding requires wal_level to beryllium replica aliases higher, and the sandbox runs pinch minimal. It besides intends CONCURRENTLY consumes a replication slot; the caller max_repack_replication_slots mounting (default 5) caps really galore tin tally astatine once.

What CLUSTER utilized to do (physically bid the rows by an index) is now spelled REPACK ... USING INDEX. Adding VERBOSE makes the bid study really it rewrote the table: either an scale scan, aliases a sequential scan followed by a sort.

VACUUM FULL and CLUSTER still work, truthful thing breaks; they’re conscionable the aged spellings now.

• 𝗗 REPACK
• 𝗖 ac58465, 28d534e, 8fb95a8, e76d8c7
• 𝗔 Antonin Houska, Mihail Nikalayeu, Álvaro Herrera

Merge and divided partitions (reverted aft beta 3)

#

Heads-up: reverted connected 2026-08-27, 2 weeks aft beta 3, “due to aggregate creation issues which are excessively precocious to reside successful this merchandise cycle”. It won’t vessel successful 19; earliest is 20. The examples still tally connected beta 3, truthful dainty them arsenic a preview.

Reshaping a walled array utilized to beryllium a manual creation of DETACH, create, INSERT ... SELECT, ATTACH. The caller ALTER TABLE ... MERGE PARTITIONS and ALTER TABLE ... SPLIT PARTITION commands do it successful 1 statement, moving the rows for you.

Splitting is the inverse. This illustration starts from a metrics array pinch a azygous oversized partition covering the full range:

The rows onshore successful the correct partition automatically, according to the bounds you declared.

Both return an ACCESS EXCLUSIVE fastener connected the genitor and rewrite the data, truthful this is simply a maintenance-window operation, not an online one. Handy for the communal “monthly partitions sewage excessively granular, rotation them up into quarters” cleanup.

• 𝗗 ALTER TABLE
• 𝗖 f2e4cc4, 4b3d173, reverted by 3e8bcc8
• 𝗔 Dmitry Koval, Alexander Korotkov, Tender Wang, Richard Guo, Dagfinn Ilmari Mannsåker, Fujii Masao, Jian He

Autovacuum gets priorities

#

Autovacuum utilized to locomotion tables successful immoderate bid it recovered them, which meant the array astir to origin a wraparound emergency waited down a twelve boring ones. PostgreSQL 19 gives each array a score and processes the highest first, and exposes the full calculation successful a caller view.

Here’s a 20,000-row array pinch a 3rd of its rows conscionable deleted (rounded for readability, since the columns are double precision):

Read a people arsenic “how acold past its period this array is”. Above 1 intends it’s due: vacuum_score of 1.65 intends 65% much dormant tuples than it takes to trigger a vacuum. The wide people is conscionable the highest of the components, truthful you tin benignant by it and spot the queue. There are much components successful the position (xid_score, mxid_score, vacuum_insert_score), which is what yet makes “why is autovacuum engaged connected that table” answerable.

Each constituent has a weight you tin tune, if freezing matters much than dormant tuples successful your workload, aliases group them each to 0.0 to get the pre-19 ordering back:

• 𝗗 Autovacuum priority, Autovacuum configuration
• 𝗖 d7965d6, 87f61f0
• 𝗔 Nathan Bossart, Sami Imseih

Plan advice, officially

#

PostgreSQL has refused query hints for its full history. The caller pg_plan_advice module is the compromise: not hints that override the planner, but a measurement to record the scheme you person and past constrain the planner to it.

It’s a loadable module alternatively than an extension, truthful LOAD it (or adhd it to shared_preload_libraries) and EXPLAIN grows a PLAN_ADVICE option:

That artifact is the plan, written successful a mini proposal language: thrust from f, subordinate d to it, hash subordinate pinch d connected the soul side, sequential scans, nary parallelism. Feed a drawstring for illustration that backmost done pg_plan_advice.advice and the planner is constrained to match. Usually you’d support only the parts you really attraction about:

The scheme flipped to a nested loop driven by dim, and each portion of proposal reports /* matched */. That feedback is the champion portion of the design: proposal that doesn’t use says truthful alternatively of silently doing nothing. Ask for thing intolerable and you get told, positive a scheme marked Disabled: true:

Mechanically, proposal ne'er adds plans; it only removes candidates the planner would different consider. So you tin ne'er unit a scheme the planner considered invalid, and intolerable proposal degrades to a bad-but-correct scheme alternatively than an error.

The companion pg_stash_advice hold is wherever this becomes operational: it stores proposal keyed by query ID and applies it automatically, truthful you don’t person to SET thing from the application.

• 𝗗 pg_plan_advice, pg_stash_advice
• 𝗖 5883ff3, 6455e55, e8ec19a, c10edb1
• 𝗔 Robert Haas, Lukas Fittl

Aggregate earlier you join

#

Also known arsenic eager aggregation. When you group by a file from 1 broadside of a join, the planner tin now push a partial aggregate underneath the join, shrinking the number of rows the subordinate has to process, past finalize connected top.

orders has 100,000 rows complete 100 chopped dim_id values. The Partial HashAggregate collapses them to 100 rows before the join, truthful the hash subordinate handles 100 rows alternatively of 100,000. On PostgreSQL 18 the aforesaid query aggregates only astatine the top:

This is cost-based, truthful the planner only does it erstwhile the pre-aggregation is expected to salary for itself. The triumph scales pinch really overmuch the grouping collapses the input. It has its ain switch, enable_eager_aggregate, which is connected by default; group it to disconnected and you get the PostgreSQL 18 scheme back, which is simply a speedy measurement to cheque whether it’s helping aliases hurting a peculiar query.

• 𝗗 enable_eager_aggregate
• 𝗖 8e11859, bd94845, 3a08a2a
• 𝗔 Richard Guo, Antonin Houska

NOT IN yet becomes an anti-join

#

NOT IN (subquery) has been a capacity trap forever. Because SQL’s three-valued logic makes NOT IN behave strangely if the subquery tin nutrient a NULL, the planner refused to move it into an anti-join and fell backmost to a hashed subplan instead.

PostgreSQL 19 proves the NULL lawsuit distant erstwhile it can. With NOT NULL connected some columns:

PostgreSQL 18 produced this for the aforesaid query:

The drawback is the precondition: both the outer look and the subquery output must beryllium provably non-nullable, which successful believe intends NOT NULL columns. Drop the constraint and you’re backmost to the subplan, which is simply a decent statement for declaring NOT NULL wherever you can.

Two smaller optimizer changes vessel alongside it: much LEFT JOIN ... WHERE right.col IS NULL patterns now go anti-joins, and the changeless files simplifies IS [NOT] DISTINCT FROM into plain operators erstwhile the inputs can’t beryllium NULL:

On 18 the select stays arsenic (id IS DISTINCT FROM 42), which can’t usage an scale aliases a normal operator’s statistics. Now it’s an mean <>.

• 𝗗 Release notes: Optimizer
• 𝗖 383eb21, cf74558, 0a37961
• 𝗔 Richard Guo, Tender Wang

EXPLAIN knows more

#

Three additions worthy knowing, each successful EXPLAIN.

The large 1 is the caller IO option, which reports what asynchronous I/O really did. PostgreSQL 18 introduced AIO; now you tin spot it. Part of the array beneath is still successful shared buffers erstwhile the scan runs, truthful only immoderate of it comes disconnected disk; that portion is what the caller counters describe:

Two caller lines. Prefetch describes really acold up the publication watercourse was looking (avg and max blocks, retired of a capacity of 204). I/O describes the sounds themselves: 24 I/O operations averaging 13.33 blocks each, six of which the scan really had to wait for; the remainder had already completed by the clip it needed them.

This is the 1 illustration successful this station whose output isn’t fixed: avg, waits, and in-progress dangle connected really accelerated the kernel returns the reads, and everything depends connected really overmuch of the array happens to beryllium cached already, truthful expect different numbers connected your ain run. Warm the cache wholly and the I/O statement vanishes, because location was nary I/O to report.

auto_explain gets the aforesaid point via auto_explain.log_io.

Second, Memoize now explains itself. A Memoize node utilized to show you thing astir why the planner chose it:

That Estimates: statement is new. It shows the planner expected 100 chopped keys complete 100,000 lookups, a 99.9% deed rate, which is precisely why Memoize won. When a Memoize node underperforms successful production, you tin now put that estimate adjacent to the existent Hits/Misses that EXPLAIN ANALYZE reports and show whether the planner guessed incorrect aliases the information changed nether it.

Third, EXPLAIN (ANALYZE, WAL) now separates retired really galore WAL bytes went to full-page images, arsenic fpi bytes adjacent to the existing fpi count. Full-page writes are often the bulk of WAL measurement correct aft a checkpoint, and antecedently you could spot the count but not the size.

• 𝗗 EXPLAIN
• 𝗖 681daed, 3b1117d, e157fe6, 4bc62b8, 5ab0b6a
• 𝗔 Tomas Vondra, Ilia Evdokimov, Lukas Fittl, Shinya Kato

COPY grows up

#

Four independent improvements to COPY, and together they screen astir of the reasons group scope for a book instead.

COPY TO tin emit JSON, 1 entity per row:

That’s JSON Lines, which is what astir log and analytics pipelines want. If you request 1 valid JSON archive instead, FORCE_ARRAY wraps it:

On the input side, HEADER now takes a count, truthful files pinch a multi-line preamble nary longer request pre-processing, and ON_ERROR set_null turns unparseable values into NULLs alternatively of aborting the full load. Both astatine once:

Two header lines skipped, oops softly became NULL, and the NOTICE tells you really galore values were affected truthful the nonaccomplishment isn’t silent. This joins ON_ERROR disregard from PostgreSQL 17, which dropped the full statement alternatively of conscionable the bad column.

Fourth: COPY TO yet accepts a partitioned table directly. Previously you had to constitute COPY (SELECT * FROM t) TO ..., since COPY t TO only publication the 1 narration you named. As a bonus, this made logical replication’s first array sync faster too.

• 𝗗 COPY
• 𝗖 7dadd38, 4c0390a, bc2f348, 2a525cc, 4bea91f
• 𝗔 Joe Conway, Jian He, Andrew Dunstan, Shinya Kato, Fujii Masao, Kirill Reshke, Ajin Cherian

Range subtraction pinch gaps

#

The - usability connected ranges has ever had an awkward restriction: it errors retired if the consequence would person a spread successful the middle, because a azygous scope can’t correspond 2 disjoint pieces. The caller range_minus_multi() returns a set of ranges instead:

On PostgreSQL 18, '[1,20)'::int4range - '[5,10)'::int4range raises consequence of scope quality would not beryllium contiguous. There’s a multirange type too, which collapses the pieces into 1 value:

Useful anyplace you compute availability: subtract booked slots from opening hours and get backmost the gaps.

• 𝗗 Range Functions
• 𝗖 5eed8ce
• 𝗔 Paul A. Jungwirth

More jsonpath drawstring methods

#

jsonpath gained 8 drawstring methods that reflector their SQL counterparts: ltrim(), rtrim(), btrim(), lower(), upper(), initcap(), replace(), and split_part(). They chain, truthful you tin cleanable up values wrong the way look alternatively of unnesting first:

All 8 are immutable, for illustration the SQL functions they mirror, which intends they’re usable successful look indexes.

• 𝗗 JSON Functions
• 𝗖 bd4f879
• 𝗔 Florents Tselai, David E. Wheeler

base64url and base32hex

#

encode() and decode() learned 2 much alphabets. base64url is the URL-safe version from RFC 4648: - and _ alternatively of + and /, nary padding, truthful it’s safe successful URLs and JWTs without post-processing:

base32hex is the much absorbing one: dissimilar mean base32, its alphabet (0-9, A-V) preserves the benignant bid of the bytes it encodes, which makes it a compact, sortable encoding for things for illustration UUIDs. One caveat the docs are definitive about: that ordering only holds nether a byte-wise collation. Sort the encoded matter pinch a natural-language collation and the guarantee is gone, truthful usage COLLATE "C" erstwhile you trust connected it.

• 𝗗 Binary String Functions
• 𝗖 497c117, e752a2c, e1d9171
• 𝗔 Andrey Borodin, Aleksander Alekseev, Florents Tselai

Other notable changes

#

• CHECK constraints tin beryllium un-enforced. ALTER TABLE ... ALTER CONSTRAINT ... NOT ENFORCED now covers CHECK, not conscionable overseas keys, truthful you tin parkland 1 while loading awkward data. Re-enforcing re-validates the full array and fails connected immoderate violating row.
• GRANT ... GRANTED BY records a different domiciled arsenic the grantor, which matters because revoking depends connected who granted. GRANT SELECT ON study TO intern GRANTED BY leader records intern=r/boss, but leader must already clasp the privilege WITH GRANT OPTION.
• error_on_null() returns its statement aliases raises, the assertion coalesce() can’t express: error_on_null(NULL::int) fails pinch null worth not allowed.
• WAIT FOR blocks until a standby has replayed to a fixed LSN: WAIT FOR LSN '0/1000000' WITH (TIMEOUT '100ms'). That’s read-your-writes connected replicas. The default standby_replay mode is recovery-only, truthful connected a superior it fails pinch betterment is not successful progress.
• CHECKPOINT takes options. In CHECKPOINT (MODE SPREAD, FLUSH_UNLOGGED), SPREAD throttles the checkpoint for illustration a scheduled 1 alternatively of flushing arsenic accelerated arsenic imaginable (FAST is the default).
• bytea ↔ uuid casts activity straight now, nary much encode()/replace() gymnastics. Also tid_block() and tid_offset(), to propulsion the page number and statement pointer retired of a ctid.
• Roles and databases arsenic DDL. pg_get_role_ddl(), pg_get_database_ddl(), and pg_get_tablespace_ddl() reconstruct a meaning the measurement pg_get_viewdef() does for views, truthful recovering 1 nary longer intends pg_dumpall --roles-only. Every property is spelled out, antagonistic ones for illustration NOSUPERUSER included; passwords ne'er are.
• random(min, max) for dates and timestamps. The bounded random() from PostgreSQL 17 gained date, timestamp, and timestamptz versions, truthful trial information is simply a one-liner.
• oid8, a 64-bit unsigned identifier type. Mostly plumbing for early catalog work, but usable: '18446744073709551615'::oid8 useful wherever bigint overflows.
• Publications sewage 2 caller shapes: FOR ALL TABLES EXCEPT (TABLE a, TABLE b) and FOR ALL SEQUENCES.
• Logical replication replicates series values, via CREATE SUBSCRIPTION, REFRESH PUBLICATION, aliases the caller REFRESH SEQUENCES. It besides nary longer needs wal_level = logical up front: replica is enough, decoding switches connected erstwhile thing needs it, and the read-only effective_wal_level tells you what’s successful force.
• log_min_messages tin beryllium group per process type: SET log_min_messages = 'warning, autovacuum:debug1'. A bare default level is required, different it fails pinch Default log level was not defined.
• Polish and Esperanto stemmers for full-text search, positive an updated Dutch one; the aged Dutch is still disposable arsenic dutch_porter.
• Unicode 17. unicode_version() now reports 17.0, and astir 4,800 codification points that PostgreSQL 18 considered unassigned are assigned, which matters if you validate pinch unicode_assigned(). icu_unicode_version() didn’t move, truthful ICU and the builtin supplier tin disagree astir a brand-new character.
• List-valued settings tin beryllium emptied pinch NULL. SET search_path = NULL now intends “empty list”, which antecedently had nary pronunciation astatine all.

And connected the capacity and operations side:

• Foreign cardinal checks sewage a accelerated path, worthy up to 2× amended insert capacity connected tables pinch overseas keys per the beta 1 announcement.
• Reads now support the visibility map. On-access pruning sets the all-visible spot successful sequential, TID range, sample and bitmap heap scans and the heap broadside of scale scans, not conscionable successful VACUUM and COPY ... FREEZE. On a freshly loaded 256-page table, a plain SELECT count(*) marks 221 pages all-visible wherever PostgreSQL 18 leaves each 1 unset.
• LISTEN/NOTIFY scales to galore channels. A shared transmission representation intends NOTIFY wakes only the backends listening connected that channel, alternatively than astir of them.
• More incremental sorts. Append and MergeAppend now see definitive incremental sorts, which mostly helps walled tables pinch an ORDER BY that’s partially satisfied by an index.
• Server-side SNI. A caller pg_hosts.conf, located by the hosts_file setting, maps hostnames to certificate/key pairs, truthful 1 server tin coming a different TLS certificate per requested hostname.
• New monitoring views. pg_stat_lock reports waits and hold clip per fastener type; pg_stat_recovery exposes replay progress, region state, and whether promotion was triggered. Many existing views gained a stats_reset column, and pg_stat_progress_vacuum/pg_stat_progress_analyze gained started_by, truthful you tin show an autovacuum from a manual run.
• CREATE SUBSCRIPTION ... SERVER takes relationship specifications from a postgres_fdw overseas server and personification mapping alternatively of an inline relationship string, keeping credentials retired of the subscription.
• postgres_fdw pushes down more. Array comparisons successful prepared statements now push down, and the caller import_stats action has ANALYZE import distant statistic alternatively of dragging rows crossed the wire. Off by default, and spelled restore_stats successful beta 3.
• PL/Python tin constitute arena triggers, which antecedently required PL/pgSQL aliases C.

Incompatibilities worthy knowing

#

PostgreSQL 19 changes a fistful of defaults and removes immoderate aged behavior. The afloat database is successful the migration section; these are the ones astir apt to astonishment you:

• JIT is disconnected by default. The costs exemplary deciding erstwhile to JIT was unreliable, and a bad determination is expensive. If your analytical queries use from JIT, you now person to move it connected deliberately.
• TOAST compression defaults to lz4 erstwhile the server is built pinch --with-lz4; different it stays connected pglz. Existing information isn’t recompressed, only recently stored values.
• max_locks_per_transaction doubled to 128 — not a capacity increase. The fastener table’s shared representation accounting changed (see the hidden gems below), truthful allocation is deterministic and you deed the limit sooner for a fixed setting. The norm of thumb from the commit: if you had tuned this, double your value.
• log_lock_waits is connected by default, truthful waits exceeding deadlock_timeout get logged unless you move it off.
• standard_conforming_strings is forced on and escape_string_warning is gone. Dumps taken by a pre-19 pg_dump pinch it disconnected won’t load into a 19 server; re-dump pinch the newer tool.
• RADIUS authentication is removed. It only ever worked complete UDP, which can’t beryllium secured. There’s nary drop-in replacement, truthful scheme a migration to LDAP, OAuth, aliases certificates.
• MD5 password authentication warns connected each success. md5_password_warnings isn’t caller (18 added it, to pass erstwhile a password was set); 19 reuses it for logins, the adjacent nudge toward scram-sha-256, the challenge-response strategy (RFC 7677) that replaces it. Related: password_expiration_warning_threshold (default 7 days) warns earlier a password expires.
• json_array() complete an quiet subquery returns [], not NULL.
• The built-in GiST inet_ops is now the default opclass for inet/cidr, displacing btree_gist’s gist_inet_ops and gist_cidr_ops, which could miss rows they should person returned. pg_upgrade refuses a cluster pinch an scale connected the aged opclasses, and reindexing won’t help: driblet and recreate them connected inet_ops. It likewise refuses MULE_INTERNAL encoding (removed) and carriage returns aliases statement feeds successful database, role, aliases tablespace names (now disallowed).
• pg_stat_subscription_stats.sync_error_count is renamed to sync_table_error_count, since series sync errors are counted separately now. The BUFFERPIN hold arena type is renamed to BUFFER.

#

A drawback container of customer and inferior improvements:

• psql tin show booleans nevertheless you like: \pset display_true yes and \pset display_false no, alternatively of the terse t/f.
• Two caller punctual escapes. %S shows the existent search_path (it needs an 18-or-later server), and %i shows whether you’re connected a basking standby — a bully defender against penning to the incorrect host.
• \dRp+, \dRs+, and \dX+ show comments for publications, subscriptions, and extended statistics, positive a ample batch of tab-completion improvements, including 1 for FOR PORTION OF.
• vacuumdb --dry-run prints the commands it would tally alternatively of moving them. Also, --analyze-only and --analyze-in-stages nary longer skip walled tables.
• pgbench --continue-on-error keeps a tally going aft SQL errors alternatively of aborting the client, which makes benchmarking workloads pinch expected conflicts acold little annoying.
• pg_test_timing reports nanoseconds alternatively of microseconds, and adds a array of nonstop timings alongside the histogram (with an optional --cutoff). The caller timing_clock_source mounting tin prime the TSC straight connected x86, making EXPLAIN (ANALYZE, TIMING) cheaper.
• pg_waldump and pg_verifybackup publication WAL from tar archives, truthful you nary longer person to extract a backup to inspect it.
• pg_upgrade is overmuch faster pinch galore ample objects, the pathological lawsuit that made immoderate upgrades return hours. It besides handles non-default tablespaces stored wrong PGDATA alternatively of erroring out.
• pg_dump tin dump restorable extended statistics, truthful a restored database doesn’t commencement retired pinch nary for its CREATE STATISTICS objects.
• “Grease mode” ran during the beta, and past stops. libpq gained protocol parameters that deliberately workout unknown-parameter handling, to fume retired proxies and drivers that mishandle protocol extensions. It was progressive during the 19 beta and will not beryllium sent arsenic of the release, though the parameter reservations enactment for early use. If you support a relationship pooler aliases a wire-protocol driver, this is the merchandise to trial against.

#

Everything supra comes from the merchandise notes. Those notes are deliberately curated: astir 3,400 commits landed betwixt PostgreSQL 18.0 and the 19 unchangeable branch, and a merchandise statement that listed each of them would beryllium useless. But a fewer of the changes that didn’t make the trim are still interesting, truthful present are immoderate worthy knowing about:

• You tin yet find retired who killed your backend. When a convention is terminated by pg_terminate_backend() aliases an outer SIGTERM, the server log now adds DETAIL: Signal sent by PID 142, UID 999. It’s an errdetail_log(), truthful it goes to the log alternatively than the client, and it needs SA_SIGINFO (Linux, FreeBSD, astir modern Unixes). A follow-up reworked awesome handling to walk a pg_signal_info struct into each handler, alternatively of stashing the sender successful globals. 𝗖 55890a9, 3e2a149 • 𝗔 Jakub Wartak, Andrew Dunstan

• A quiet rewrite of shared representation allocation. The merchandise notes grounds the user-visible part, the caller ShmemRequestStruct() API. Behind it, Heikki Linnakangas converted fundamentally each subsystem: the buffer manager, AIO, SLRUs, lwlock.c, predicate.c, pg_stat_statements. The fudge factors came retired excessively — a 10% “safety margin” successful the fastener manager’s hash array estimates, a bogus 1 successful predicate.c — which is precisely why max_locks_per_transaction’s default had to double. The perpetrate spells it out: the allocation became deterministic, “but it besides intends that you often deed 1 of the limits sooner than before”. 𝗖 283e823, a4b6139, 9b5acad, 3e854d2, 79534f9

• EXPLAIN tells you what a Result node replaced. A scheme proven quiet astatine readying clip utilized to illness to a bare Result pinch One-Time Filter: false, losing each trace of the narration it stood successful for. Now it says Replaces: Scan connected w, which makes “why is my array missing from this plan” answerable. 𝗖 f2bae51

• CI moved disconnected Cirrus CI onto GitHub Actions. Not a database change, but it’s the infrastructure each early spot is tested on. Cirrus support was removed outright alternatively than kept successful parallel. 𝗖 9c12606, 68c8a36 • 𝗔 Andres Freund

• Set-returning functions are nary longer allowed successful a model OVER clause. An SRF location contradicts the rule that a model usability doesn’t alteration the statement count, and drew 2 bug reports (#17502 and #19535). Rather than specify the semantics, 19 makes it an error; put the SRF successful a LATERAL FROM clause instead. Worth flagging because it’s a difficult correction connected codification that antecedently “worked”. 𝗖 0c15b71 • 𝗔 Tom Lane

• The multixact members offset is 64-bit. Widening MultiXactOffset lifts the 2^32 headdress connected full multixact members and removes members-space wraparound, on pinch the emergency anti-wraparound freezing that came from exhausting it; multixact IDs themselves are still capped astatine 2^31. The on-disk format changed, truthful pg_upgrade rewrites the pg_multixact files. Related: the wraparound informing period moved from 40 cardinal to 100 cardinal transactions. 𝗖 bd8d9c9, 48f11bf • 𝗔 Maxim Orlov, Nathan Bossart

• SIMD keeps spreading. COPY FROM parsing for matter and CSV, hex_encode()/hex_decode(), page checksums (AVX2), and CRC32C connected ARM each moved to vector instructions. No API changes; bulk loading and checksumming conscionable get faster. 𝗖 e0a3a3f, ec8719c, 5e13b0f, fbc57f2

• AIX support is back, aft being dropped successful PostgreSQL 17. IBM stepped up pinch buildfarm animals; xlc and 32-bit builds did not travel back, truthful it’s gcc-only and 64-bit-only. Meanwhile the minimum C type moved from C99 to C11, Visual Studio 2019 is now the level connected Windows, and MSVC tin build for AArch64. 𝗖 4a1b05c, ecae097, f5e0186, 8fd9bb1, a516b3f

Final thoughts

#

PostgreSQL 19 is simply a large release, and unusually front-loaded pinch things you tin see. A fewer themes guidelines out:

• Query language: spot chart queries are the headline, and a genuinely caller measurement to query PostgreSQL. FOR PORTION OF, ON CONFLICT DO SELECT, and IGNORE NULLS are smaller but each region a well-known workaround.
• Operations: REPACK CONCURRENTLY and autovacuum prioritization some onslaught the aforesaid problem, which is that attraction utilized to mean downtime.
• Planner: eager aggregation and NOT IN anti-joins are existent wins connected existent queries, and pg_plan_advice is simply a notable philosophical displacement for a task that spent 20 years saying nary to hints.
• Observability: EXPLAIN (ANALYZE, IO) yet makes the asynchronous I/O added successful 18 visible, and Memoize estimates fto you cheque the planner’s reasoning alternatively than conscionable its conclusion.

The defaults changed much than accustomed too. JIT off, lz4 TOAST compression, log_lock_waits on, max_locks_per_transaction doubled, RADIUS gone: publication the migration notes earlier you upgrade, not after.

P.S. Curious really we grip clip bid information astatine scale? VictoriaMetrics is simply a purpose-built database for metrics, logs, and traces. Browse the rest of our blog for heavy dives into retention engines, query performance, and observability astatine scale.

• postgresql
• postgres
• sql
• databases