Newshot

PostgreSQL 19: A hands-on tour with runnable browser examples

Original text(via Hacker News)

A release you can try at the prompt

PostgreSQL 19 brings useful new SQL alongside maintenance and replication improvements. Try IGNORE NULLS, get-or-create inserts, JSON export, JSONPath transformations, and range subtraction, then explore planner advice and real replication.

PostgreSQL 19.0 · released 19 November 2026. The stable release announcement introduces the version installed in this sandbox. Each feature links to its official documentation. The release notes remain the complete change log.

About this terminal. Experiments run on a real PostgreSQL 19 server as student, in a disposable guest. Maintenance tables live in blog_lab. Replication experiments start additional real servers on demand: a logical subscriber on 5433, and a physical standby on 5434, both fed by the primary on 5432. To fit the guest’s memory budget, only one of these extra instances runs at a time: each replication card stops the other and starts its own. Re-run that card before checking its current state again. Their first runs take longer because they initialize a cluster or take a base backup. Reset discards all three clusters and their data.

How to compare with 18. Sidenotes offer older SQL/workflows that you can run on 19, or a setting that disables a particular new optimization. These show the difference in expression or behavior. They do not turn PostgreSQL 19 into PostgreSQL 18. For timing claims, run the same workload on actual 18 and 19 servers with equal data, settings, cache conditions, and hardware.

Run the experiments in any order. Commands beginning with \! run a shell command from psql. The logical replication walkthrough shows its server setup and SQL directly; as-postgres runs server utilities as the Linux postgres account. Query and comparison examples state when to use another port or the administrative SQL role.

Start with the server you actually have

Before reading a release article, ask the server which release you are actually running. SELECT 1 is also a pleasantly boring connectivity test: a result beats a spinning status indicator.

The version returned below belongs to the sandbox, not to the release covered by the article. The SQL role is student; ownership of the prepared demo database gives you room to experiment without administrative credentials.

Look for: one row, a real server version, and the expected role. Keep that baseline when you compare an upgrade later.

From the shell, open the demo database (PGDATABASE=blog_lab):

Then run this at the SQL prompt:


        SELECT 1 AS terminal_is_alive,
       current_setting('server_version') AS actual_version,
       current_user AS sql_role;
      

Expected: One row with terminal_is_alive = 1, the installed server version, and student.

REPACK — rewrite and reclaim space

Deleting rows leaves reusable space behind. Regular vacuum cleans that space; PostgreSQL 19’s native REPACK rewrites the table and returns unused bytes to the OS. The card deletes middle rows, preserving occupied tail pages so ordinary vacuum cannot simply truncate them. Compare the file size after vacuum with its size after a real rewrite. The tiny table demonstrates the operation, not a storage benchmark. REPACK documentation.

The concurrent variant uses logical decoding to capture intervening writes and takes an access-exclusive lock for the final swap. After the card, run this example at the same psql prompt. REPACK requires the table's MAINTAIN privilege; student owns events, so no role change is needed.


        REPACK (CONCURRENTLY, ANALYZE) events;
      
Compare with 18: VACUUM (FULL, ANALYZE) events; performs the older blocking rewrite. For physical index ordering, compare CLUSTER events USING events_pkey; with REPACK events USING INDEX events_pkey;. Restore the same bloated table before each run. Use a second connection to try SELECT/INSERT while a larger rewrite runs, and inspect pg_locks: the concurrent form still locks briefly at the swap. PostgreSQL 18 has no native concurrent equivalent; the separately installed pg_repack extension is another workflow.

        DELETE FROM events WHERE id BETWEEN 301 AND 700;
      

        VACUUM (ANALYZE) events;
      

        SELECT pg_relation_size('events') AS bytes_before_repack;
      

        REPACK (ANALYZE) events;
      

        SELECT count(*) AS surviving_events,
       pg_relation_size('events') AS bytes_after_repack
FROM events
WHERE id <= 1000;
      

Expected: DELETE 400 on the first run, VACUUM, REPACK, then 600 original surviving events. Compare bytes before/after; sizes vary.

Autovacuum — inspect the real priority scores

Autovacuum now prioritizes tables using real scores and can parallelize index work. The card reads pg_stat_autovacuum_scores and the configured score weights. Use \x on to show the wide scores view as readable records, then \x off to return to a table for the two-column settings query. Scores change with workload and statistics, so zero after maintenance is a useful result too. Autovacuum scheduling, statistics views.

autovacuum_max_parallel_workers controls the server-wide allowance; autovacuum_parallel_workers is a per-table setting. The sandbox’s current parallel allowance is zero, so the card inspects priorities rather than launching parallel vacuum work. PostgreSQL 19 can also mark eligible pages all-visible during ordinary scans, improving future index-only scans.

Compare with 18: On an administrative connection, set autovacuum_max_parallel_workers = 0 with ALTER SYSTEM, then reload using SELECT pg_reload_conf(); to disable this new parallel index work. Restore the previous value afterward. Manual VACUUM (PARALLEL 0) versus VACUUM (PARALLEL 2) is also runnable on 18, but measures manual vacuum. There is no faithful switch back to PostgreSQL 18’s autovacuum scheduler; compare the scheduling on separate installations.

        SELECT *
FROM pg_stat_autovacuum_scores
WHERE relid IN ('events'::regclass, 'orders'::regclass);
      

        SELECT name, setting
FROM pg_settings
WHERE name LIKE 'autovacuum%score_weight'
   OR name = 'autovacuum_max_parallel_workers'
ORDER BY name;
      

Expected: Expanded records for per-table scores, then a two-column table of configured weights; recently vacuumed tables may score zero.

Plan advice — constrain one query, then release it

pg_plan_advice generates a description of planner choices and lets you constrain selected choices. pg_stash_advice stores advice by query identifier for automatic reuse. In this database setup has enabled the advice module for the student’s sessions; on another installation an administrator can use LOAD 'pg_plan_advice' or configure preloading. Plan advice, stored advice.

The first plan emits generated advice; the second requests SEQ_SCAN(orders). Look for matched feedback. Advice restricts the normal planner and can become stale when data changes.

Compare with 18: Run the settings, query, and resets in the same connection:

        SET enable_indexscan = off;
SET enable_bitmapscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT sum(amount)
FROM orders
WHERE customer_id = 1;
RESET enable_indexscan;
RESET enable_bitmapscan;
      

These broad settings discourage whole classes of paths. PostgreSQL 19’s advice can instead target a named table in a query. Our small table is likely to choose a sequential scan already; the interesting evidence is the advice and its feedback.

Inspect the generated plan advice.


        EXPLAIN (PLAN_ADVICE, COSTS OFF)
SELECT sum(amount)
FROM orders
WHERE customer_id = 1;
      

Apply session advice, inspect the plan, then reset the setting.


        SET pg_plan_advice.advice = 'SEQ_SCAN(orders)';
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT sum(amount)
FROM orders
WHERE customer_id = 1;
RESET pg_plan_advice.advice;
      

Expected: Generated Plan Advice, then Supplied Plan Advice with matched feedback and an Aggregate plan.

Logical replication — see what moves between two servers

Follow one row and one counter from source to destination. The publisher on 5432 owns the source data. The subscriber on 5433 is a separate PostgreSQL process with its own data directory. Both databases are named pg19_repl; the port identifies which one you are changing.

ObjectPublisher · 5432Subscriber · 5433
messagesWrite a rowThe publication copies the row
scratchWrite a rowExcluded from the publication; remains empty
ticket_numbersConsume 50 valuesExplicit refresh copies the sequence state

The commands below show the entire setup. Run them in order, or use Run all steps to enter the same visible commands. \! runs a shell command from psql. as-postgres runs the following command as the Linux postgres account; initdb initializes the cluster and pg_ctl starts it. \connect database role host port chooses the SQL connection. Administrative SQL here explicitly uses the SQL role postgres. Local trust authentication is for this disposable guest.

The standby from experiment 06 is stopped first to fit the guest's memory budget. The subscriber uses small buffers and synchronous I/O for this runtime. Its log is /tmp/pg19-subscriber.log. Conditional setup preserves existing objects on repeat runs; each repeat advances the publisher counter again.

What is new in 19? FOR ALL TABLES EXCEPT (TABLE scratch) excludes a table, and ALL SEQUENCES includes sequence state. Initial subscription creation copies sequence values; later publisher calls to nextval can leave the subscriber behind. REFRESH SEQUENCES requests another copy. We wait for the initial copy before advancing the publisher so it cannot mask the before/after comparison. The visible polling loops have a deadline because synchronization is asynchronous. Definitions must match on both servers. Sequence synchronization, publication syntax.

The subscription receives data over loopback TCP; psql uses local Unix sockets for administration. Compare wal_level = replica with effective_wal_level = logical on the publisher after creating the publication: PostgreSQL 19 enables logical WAL when required without restarting this primary. WAL configuration.

Compare with 18: Publish the included table explicitly with FOR TABLE messages. The publisher needs wal_level = logical and a restart. Sequence state is not replicated: at cutover, stop publisher writes, read last_value and is_called, and apply setval('ticket_numbers', captured_value, captured_is_called) on the subscriber. In 19, repeat the steps below and compare the counter before and after REFRESH SEQUENCES.

1 · Make room for the subscriber. Stop the physical standby only if experiment 06 has started it.


        \! if as-postgres pg_ctl status -D /tmp/pg19-standby >/dev/null 2>&1; then as-postgres pg_ctl stop -D /tmp/pg19-standby -m fast -w; fi
      

2 · Initialize a separate cluster. initdb creates its data directory; the condition preserves it on repeat runs.


        \! if ! as-postgres test -f /tmp/pg19-subscriber/PG_VERSION; then as-postgres initdb -D /tmp/pg19-subscriber -U postgres --auth=trust --locale=C -E UTF8; fi
      

3 · Start that cluster on port 5433. pg_ctl waits for startup; the -o options choose the port, sockets, and small memory settings for this guest.


        \! as-postgres pg_ctl status -D /tmp/pg19-subscriber || as-postgres pg_ctl start -D /tmp/pg19-subscriber -l /tmp/pg19-subscriber.log -w -o "-p 5433 -h 127.0.0.1 -k /tmp -c dynamic_shared_memory_type=mmap -c shared_buffers=16MB -c max_connections=10 -c io_method=sync"
      

4 · Publisher, port 5432. Create the source database if absent. psql’s \gexec executes the CREATE DATABASE text only when the SELECT returns it.


        \connect postgres postgres /tmp 5432
SELECT 'CREATE DATABASE pg19_repl OWNER student'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'pg19_repl') \gexec
      

5 · Publisher schema and publication. messages is included, scratch is excluded, and ticket_numbers is explicitly published through ALL SEQUENCES. The existence checks make setup repeatable.


        \connect pg19_repl postgres /tmp 5432
CREATE TABLE IF NOT EXISTS messages (id integer PRIMARY KEY, body text);
CREATE TABLE IF NOT EXISTS scratch (id integer);
CREATE SEQUENCE IF NOT EXISTS ticket_numbers START 1;
SELECT 'CREATE PUBLICATION pg19_pub FOR ALL TABLES EXCEPT (TABLE scratch), ALL SEQUENCES'
WHERE NOT EXISTS (SELECT FROM pg_publication WHERE pubname = 'pg19_pub') \gexec
SELECT pubname, puballtables, puballsequences FROM pg_publication;
SHOW wal_level;
SHOW effective_wal_level;
      

6 · Subscriber, port 5433. Create the destination database and matching objects. Logical replication does not create their definitions.


        \connect postgres postgres /tmp 5433
SELECT 'CREATE DATABASE pg19_repl'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'pg19_repl') \gexec
\connect pg19_repl postgres /tmp 5433
CREATE TABLE IF NOT EXISTS messages (id integer PRIMARY KEY, body text);
CREATE TABLE IF NOT EXISTS scratch (id integer);
CREATE SEQUENCE IF NOT EXISTS ticket_numbers START 1;
      

7 · Subscriber. Subscribe to the publisher on 5432. The connection string is the source; the current psql connection is the destination.


        SELECT $sql$CREATE SUBSCRIPTION pg19_sub
  CONNECTION 'host=127.0.0.1 port=5432 user=postgres dbname=pg19_repl'
  PUBLICATION pg19_pub$sql$
WHERE NOT EXISTS (SELECT FROM pg_subscription WHERE subname = 'pg19_sub') \gexec
      

8 · Wait for the initial sequence copy before changing the publisher. This visible shell loop checks synchronization metadata, with up to 300 checks, sleeping 0.1 seconds between checks.


        \! ready=0; for attempt in $(seq 1 300); do if [ "$(psql -w -X -h /tmp -p 5433 -U postgres -d pg19_repl -At -c "SELECT count(*) FROM pg_subscription_rel WHERE srrelid = 'ticket_numbers'::regclass AND srsublsn IS NOT NULL")" = 1 ]; then ready=1; break; fi; sleep 0.1; done; test "$ready" = 1 && echo 'Initial sequence copy ready' || echo 'Initial sequence copy timed out' >&2
      

9 · Publisher. Write one included row and one excluded row. Advance the sequence 50 times so the change crosses a sequence WAL batch; record publisher_ticket.


        \connect pg19_repl postgres /tmp 5432
INSERT INTO messages VALUES (1, 'hello from the publisher')
ON CONFLICT (id) DO UPDATE SET body = EXCLUDED.body;
INSERT INTO scratch SELECT 1 WHERE NOT EXISTS (SELECT FROM scratch WHERE id = 1);
SELECT max(n) AS publisher_ticket
FROM (SELECT nextval('ticket_numbers') AS n FROM generate_series(1, 50)) q;
SELECT last_value AS publisher_ticket, is_called FROM ticket_numbers;
      

10 · Subscriber, before refresh. Its counter can lag even while table changes replicate. Compare subscriber_ticket with the publisher value above.


        \connect pg19_repl postgres /tmp 5433
SELECT last_value AS subscriber_ticket_before_refresh, is_called FROM ticket_numbers;
      

11 · Subscriber. Request sequence synchronization explicitly. ALTER SUBSCRIPTION schedules asynchronous work; it does not wait for the new value.


        ALTER SUBSCRIPTION pg19_sub REFRESH SEQUENCES;
      

12 · Wait for equal sequence values and the included row. The SQL and deadline are visible here; this loop only observes the servers.


        \! target=$(psql -w -X -h /tmp -p 5432 -U postgres -d pg19_repl -At -c 'SELECT last_value FROM ticket_numbers'); synced=0; for attempt in $(seq 1 300); do if [ "$(psql -w -X -h /tmp -p 5433 -U postgres -d pg19_repl -At -c "SELECT (last_value = $target AND is_called AND EXISTS (SELECT FROM messages WHERE id = 1 AND body = 'hello from the publisher'))::int FROM ticket_numbers")" = 1 ]; then synced=1; break; fi; sleep 0.1; done; test "$synced" = 1 && echo 'Sequence synchronization confirmed' || echo 'Sequence synchronization timed out' >&2
      

13 · Subscriber evidence. messages contains the published row, scratch stays empty, and subscriber_ticket matches publisher_ticket. Inspect the synchronization metadata, then return to blog_lab as student.


        SELECT * FROM messages;
SELECT count(*) AS excluded_scratch_rows FROM scratch;
SELECT last_value AS subscriber_ticket_after_refresh, is_called FROM ticket_numbers;
SELECT srrelid::regclass, srsubstate, srsublsn
FROM pg_subscription_rel ORDER BY 1;
\connect blog_lab student /tmp 5432
      

Expected: messages contains 1 | hello from the publisher; excluded_scratch_rows is 0; subscriber_ticket_after_refresh matches publisher_ticket. Configured WAL is replica; effective WAL is logical. A timeout means synchronization has not been confirmed. Values advance on repeat runs.

WAIT — trace a committed write to a physical standby

A write can commit on the primary before a standby can read it. This walkthrough follows an event with ID 2000 from the primary on 5432 to a physical standby on 5434. Unlike the logical subscriber in section 05, a physical standby starts from a backup of the entire cluster and replays WAL to keep that copy current.

Run the steps in order, or use Run all steps. Every server command and SQL statement is shown below. \! executes a shell command from psql; as-postgres runs server utilities as the Linux postgres account. SQL writes and reads use student, and each \connect identifies the destination port. The logical subscriber is stopped first to fit the guest’s memory budget. The backup and its replication slot are reused on repeat runs. Physical standby setup.

The read follows three stages: commit the write → capture its WAL position → wait for standby replay. Capture the LSN after the INSERT has committed. Waiting for WAL to arrive or be flushed does not establish that the rows have been applied. MODE 'standby_replay' waits for that application step; the following SELECT obtains a fresh snapshot. Keep WAIT outside an explicit transaction for this example. WAIT documentation.

Look for: success from WAIT, then is_standby = true, replay_reached_target = true, and the expected row. If WAIT times out, inspect /tmp/pg19-standby.log and retry the wait after resolving the delay. Do not treat a timeout as permission to perform the dependent read. If a failover changes the timeline, establish whether the captured LSN still refers to the expected history.

Compare with 18: Poll SELECT pg_last_wal_replay_lsn() >= :'target_lsn'::pg_lsn; on the standby with a short sleep and an overall deadline. Read only after it returns true. PostgreSQL 19’s WAIT performs that wait inside the server. Optional NO_THROW returns timeout or not in recovery as a status instead of an error; your application must handle that status before reading.

1 · Make room for the physical standby. Connect to the primary before stopping the logical subscriber from experiment 05; only one extra server fits in this guest.


        \connect blog_lab student /tmp 5432
\! if as-postgres pg_ctl status -D /tmp/pg19-subscriber >/dev/null 2>&1; then as-postgres pg_ctl stop -D /tmp/pg19-subscriber -m fast -w; fi
      

2 · Copy the primary cluster. pg_basebackup takes the initial physical backup from port 5432. -R configures standby mode and its upstream connection; -X stream includes WAL generated during the backup. -C -S creates a named replication slot that retains WAL for this standby. The condition reuses the backup on repeat runs.


        \! if ! as-postgres test -f /tmp/pg19-standby/PG_VERSION; then as-postgres pg_basebackup -h /tmp -p 5432 -U postgres -D /tmp/pg19-standby -R -X stream -c fast -C -S pg19_standby; fi
      

3 · Give the copied cluster its own port. It inherits the primary’s configuration, so override port 5432 with 5434. Empty preloaded libraries and disabled log collection keep this extra instance within the guest’s memory budget.


        \! as-postgres sh -c "printf '%s\n' 'port = 5434' \"shared_preload_libraries = ''\" 'logging_collector = off' >> /tmp/pg19-standby/postgresql.auto.conf"
      

4 · Start the standby. pg_ctl starts PostgreSQL from the copied data directory and waits until it accepts connections. A running standby is left running; startup messages go to /tmp/pg19-standby.log.


        \! as-postgres pg_ctl status -D /tmp/pg19-standby || as-postgres pg_ctl start -D /tmp/pg19-standby -l /tmp/pg19-standby.log -w
      

5 · Verify the destination. Connect to blog_lab on 5434 as student. is_standby must be true and port must be 5434. A physical backup includes the database and its tables; no CREATE TABLE is needed here.


        \connect blog_lab student /tmp 5434
SELECT pg_is_in_recovery() AS is_standby, current_setting('port') AS port;
      

6 · Commit a write on the primary. Switch to 5432, enable psql autocommit, and insert an event with ID 2000. The next command runs after this INSERT commits. ON CONFLICT makes the demonstration repeatable.


        \connect blog_lab student /tmp 5432
\set AUTOCOMMIT on
INSERT INTO events VALUES (2000, 'replica-visible write')
ON CONFLICT (id) DO UPDATE SET payload = EXCLUDED.payload;
      

7 · Capture the WAL position after commit. An LSN is a position in the write-ahead log. This position is at or beyond our transaction’s commit record. psql’s \gset saves it as target_lsn; \echo shows the value. The client variable survives switching servers.


        SELECT pg_current_wal_insert_lsn() AS target_lsn \gset
\echo Target WAL position: :target_lsn
      

8 · Wait on the standby for replay. Switch to 5434 and substitute the saved LSN with :'target_lsn'. standby_replay waits until WAL has been applied to the database. TIMEOUT limits the wait to 30 seconds; an error stops Run all steps before the read. A caught-up standby returns success immediately.


        \connect blog_lab student /tmp 5434
WAIT FOR LSN :'target_lsn'
WITH (MODE 'standby_replay', TIMEOUT '30s');
      

9 · Read after WAIT succeeds. This separate SELECT takes a fresh snapshot on the standby. Expect is_standby and replay_reached_target to be true, plus the event with ID 2000 and payload replica-visible write. Finally return to the primary for the next experiment.


        SELECT pg_is_in_recovery() AS is_standby,
       pg_last_wal_replay_lsn() >= :'target_lsn'::pg_lsn AS replay_reached_target,
       id, payload
FROM events WHERE id = 2000;
\connect blog_lab student /tmp 5432
      

Expected: On port 5434: is_standby = true; WAIT returns success; the separate read shows replay_reached_target = true and the event with ID 2000 and payload replica-visible write. Target LSN values vary. A timeout means the standby has not replayed the target yet.

EXPLAIN IO — see the work behind the wait

EXPLAIN (ANALYZE, IO) adds asynchronous I/O evidence alongside buffers. Worker I/O can now grow and shrink its worker pool using io_min_workers, io_max_workers, io_worker_idle_timeout, and io_worker_launch_interval. EXPLAIN options, I/O settings.

The sandbox is configured with sync I/O. The card demonstrates the new EXPLAIN syntax; it cannot measure an asynchronous worker pool here. Tiny warm tables also make poor storage benchmarks.

Compare with 18: Run the query once with EXPLAIN (ANALYZE, BUFFERS) and once with the new IO option to inspect the extra reporting. On a native server configured for worker I/O, setting the 19 minimum and maximum worker counts to the same number approximates 18’s fixed worker-count policy; it does not restore the old implementation. Keep a separate actual 18 baseline for throughput comparisons.

        SHOW io_method;
      

        EXPLAIN (ANALYZE, IO, BUFFERS, COSTS OFF)
SELECT count(*)
FROM events
WHERE length(payload) > 100;
      

Expected: The guest uses sync I/O. EXPLAIN accepts IO and reports buffers; asynchronous activity may be absent or zero.

Foreign keys — faster inserts, same contract

Most application schemas have relationships. Here, a customer can have zero or many orders, and each order can reference zero or one customer because customer_id is nullable. Every non-NULL customer reference must exist, so foreign-key checking is on the hot path for an ordinary order insert.

The Beta 1 announcement reported up to roughly twice the insert performance for workloads involving foreign-key checks. That is a workload-specific result, not a promise that every INSERT becomes twice as fast.

Insert a demo order, then join it to the customer. The ON CONFLICT clause makes the experiment repeatable. An insert referencing a nonexistent customer would still be rejected: faster checking preserves the constraint's job.

Look for: order 1001 attached to Ada. To evaluate the upgrade, replay realistic batches with the same relationships, transaction sizes, and contention. Do not turn off constraints to make the comparison look better.


        INSERT INTO orders VALUES (1001, 1, 42)
ON CONFLICT (id) DO NOTHING;
      

        SELECT o.id, c.name, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id = 1001;
      

Expected: INSERT 0 1 on the first run, then 1001 | Ada | 42.

IGNORE NULLS — carry the last known value forward

lag, lead, first_value, last_value, and nth_value now support IGNORE NULLS. Here, last_value carries the most recent known reading through gaps. The explicit frame ends at the current row so no future observation leaks backward. Window functions.

Compare with 18: A running maximum is wrong when readings decrease. Run this older two-window workaround on the same data and compare its result with the card:

        -- PostgreSQL 18 approach: group readings, then fill each group.
WITH islands AS (
  SELECT tick, reading,
         count(reading) OVER (ORDER BY tick ROWS UNBOUNDED PRECEDING) AS grp
  FROM readings
)
SELECT tick, reading, max(reading) OVER (PARTITION BY grp) AS carried
FROM islands ORDER BY tick;
      

Each non-null value starts a new group. The results match; the 19 expression needs one window rather than the grouping workaround.


        -- PostgreSQL 19 approach: carry the latest non-null reading forward.
SELECT tick, reading,
       last_value(reading) IGNORE NULLS OVER (
         ORDER BY tick
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS carried
FROM readings ORDER BY tick;
      

Expected: Carried values 10, 10, 7, 7, while the original missing readings remain NULL.

Get or create — return the conflict without updating it

ON CONFLICT DO SELECT ... RETURNING implements get-or-create without a dummy update. Run it twice: the second call returns the existing ID and tuple xmin. Optional FOR UPDATE / FOR SHARE can lock the conflicting row. INSERT documentation.

Compare with 18: Replace DO SELECT with DO UPDATE SET handle = EXCLUDED.handle and repeat. It returns the ID, but also creates a new row version and can fire UPDATE triggers. Inspect xmin before/after; under autocommit it changes. DO NOTHING RETURNING returns no row on conflict. The common single-statement CTE fallback can miss a concurrently inserted row under READ COMMITTED; an insert-then-select retry loop is another 18 approach. Sequence gaps can occur even on the new conflict path because defaults are evaluated before resolving the conflict.

        INSERT INTO handles(handle) VALUES ('ada')
ON CONFLICT (handle) DO SELECT
RETURNING id, handle, xmin;
      

Expected: Both calls return the same id and xmin for ada; the second call selects the existing row.

COPY TO JSON — export objects directly

COPY TO now emits JSON directly, either one object per row or a single array with FORCE_ARRAY. SQL NULL becomes JSON null. COPY formats.

Compare with 18: Use SQL functions to produce objects or an array:

        -- PostgreSQL 18: one JSON object per row.
SELECT row_to_json(c)
FROM (SELECT id, name FROM customers ORDER BY id) c;

-- PostgreSQL 18: one JSON array.
SELECT json_agg(c ORDER BY id)
FROM (SELECT id, name FROM customers) c;
      

For a clean output stream in the shell:


        psql -X -At -d blog_lab \
  -c 'SELECT row_to_json(c) FROM customers c ORDER BY id'
      

Compare parsed JSON rather than whitespace. The aggregate constructs an array in memory; COPY provides a direct export path.


        COPY (SELECT id, name FROM customers ORDER BY id)
TO STDOUT (FORMAT JSON);
      

        COPY (SELECT id, name FROM customers ORDER BY id)
TO STDOUT (FORMAT JSON, FORCE_ARRAY);
      

Expected: Two JSON objects, then a JSON array containing Ada and Grace.

COPY FROM — keep a row with one malformed field

The prepared CSV contains two preamble/header lines and 2,oops. HEADER 2 skips both lines; ON_ERROR set_null keeps row 2 with its invalid integer replaced by NULL. The card uses client-side \copy so student does not need server-file privileges. COPY error handling.

Compare with 18: Remove the first preamble line in a CSV preprocessing step, use HEADER, and choose ON_ERROR ignore: the bad row is discarded rather than preserved. To keep it on 18, import text into a staging table and convert with:

        CASE
  WHEN pg_input_is_valid(reading, 'integer')
  THEN reading::integer
END
      

Inspect the three rows afterward. set_null handles input conversion failures; CHECK, NOT NULL, foreign-key, or malformed-CSV errors can still abort the import.


        \! cat /home/student/pg19-import.csv
      

        TRUNCATE imported_readings;
      

        \copy imported_readings FROM /home/student/pg19-import.csv WITH (FORMAT CSV, HEADER 2, ON_ERROR set_null)
      

        SELECT id, reading,
       reading IS NULL AS repaired
FROM imported_readings
ORDER BY id;
      

Expected: COPY 3. Row 2 survives with a NULL reading; rows 1 and 3 retain 42 and 7.

COPY a partitioned parent — include every partition

COPY partitioned_parent TO ... now exports its child rows directly. Both partitions in the card contribute a row. COPY relation behavior.

Compare with 18: Use the older query wrapper:

        COPY (SELECT * FROM export_events ORDER BY id)
TO STDOUT (FORMAT CSV, HEADER);
      

It also gives a deterministic order; plain table COPY makes no ordering promise.


        COPY export_events TO STDOUT (FORMAT CSV, HEADER);
      

Expected: The id,label header and both 1,low and 11,high (row order is unspecified).

JSONPath — transform strings inside the path

JSONPath adds lower, upper, initcap, replace, split_part, ltrim, rtrim, and btrim. The card transforms every email into a lowercase handle directly in the path. JSONPath methods.

Compare with 18: Extract to SQL strings and aggregate the result back to JSON:

        SELECT jsonb_agg(split_part(lower(email), '@', 1) ORDER BY ord)
FROM jsonb_array_elements_text(
  '["[email protected]","[email protected]"]'::jsonb
) WITH ORDINALITY AS e(email, ord);
      

This preserves the input order and matches the string-array example. Mixed-type arrays require explicit handling; JSONPath lax/strict behavior can differ from extracting every element as text.


        SELECT jsonb_path_query_array(
  '["[email protected]","[email protected]"]'::jsonb,
  '$[*].lower().split_part("@", 1)'
) AS handles_from_json;
      

Expected: The JSON array ["ada", "grace"].

Range subtraction — return both remaining pieces

range_minus_multi returns each non-empty remaining range as a row. multirange_minus_multi returns a non-empty multirange result, or no rows if nothing remains. Useful for availability minus booked time. Range functions.

Compare with 18: Subtract multiranges, then expand the result into rows:

        SELECT unnest(
  int4multirange('[0,10)'::int4range)
  - int4multirange('[3,7)'::int4range)
);
      

This gives the same two pieces. Direct range subtraction with - cannot represent a result split into two disjoint ranges.


        SELECT remaining
FROM range_minus_multi(
  '[0,10)'::int4range,
  '[3,7)'::int4range
) AS remaining
ORDER BY remaining;
      

Expected: Two rows: [0,3) and [7,10).

Random dates and timestamps — generate realistic fixtures

random(min, max) now accepts dates, timestamps, and timestamptz bounds. The card generates five fixtures and checks the date bounds. Random functions.

Compare with 18: Generate a date with date '2026-01-01' + floor(random() 365)::int, or a timestamp with timestamp '2026-01-01' + random() interval '1 day'. Keep the same bounds; output samples will differ. These are fixture generators, not cryptographic randomness.

        WITH samples AS (
  SELECT random(date '2026-01-01', date '2026-12-31') AS d,
         random(
           timestamp '2026-01-01',
           timestamp '2026-01-02'
         ) AS ts,
         random(
           timestamptz '2026-01-01 00:00+00',
           timestamptz '2026-01-02 00:00+00'
         ) AS tz
  FROM generate_series(1,5)
)
SELECT d, ts, tz,
       d BETWEEN date '2026-01-01' AND date '2026-12-31' AS within_bounds
FROM samples;
      

Expected: Five varying samples; all within_bounds values are true.

Binary values — UUID casts and URL-friendly encodings

This section demonstrates two PostgreSQL 19 additions:

  • UUID conversion: a UUID can be cast directly to bytea, PostgreSQL’s raw byte type, and back. The first query checks its size and verifies that converting it back gives the original UUID.
  • Text encoding: base64url and base32hex turn raw bytes into printable strings. The second query starts with the text Hi, converts it to UTF-8 bytes, and encodes those bytes in both formats: SGk in Base64URL and 91KG==== in Base32hex. The trailing = characters are padding, not extra data. Encoding changes how bytes are represented; decoding restores the bytes.

base64url uses - and _ in place of the + and / characters in standard Base64. base32hex uses digits 0–9 followed by letters A–V; with bytewise comparison, this alphabet supports preserving byte ordering. The small example shows the encodings, rather than testing their ordering. Binary encodings, UUID type.

Compare with 18: For UUID bytes use decode(replace(u::text, '-', ''), 'hex'). For a URL token use rtrim(translate(encode(bytes, 'base64'), '+/', '-_'), '='), removing embedded line breaks for longer byte strings. Compare with the native 19 encoder on identical bytes.

1 · Convert a UUID to bytes and back. The sample UUID is named u. u::bytea converts it to raw bytes; octet_length counts them. Casting those bytes back to uuid and comparing with u checks that the original value survives. Expect uuid_bytes = 16 and round_trip = true.


        WITH value AS (
  SELECT '123e4567-e89b-12d3-a456-426614174000'::uuid AS u
)
SELECT octet_length(u::bytea) AS uuid_bytes,
       (u::bytea)::uuid = u AS round_trip
FROM value;
      

2 · Turn bytes into text tokens. convert_to('Hi', 'UTF8') converts the familiar text Hi to its two UTF-8 bytes. Both encode calls represent those same bytes as text: base64url uses an alphabet suitable for URL tokens, while base32hex uses digits 0–9 and letters A–V. Compare the two representations; this example does not use the UUID above.


        SELECT encode(convert_to('Hi', 'UTF8'), 'base64url') AS url_token,
       encode(convert_to('Hi', 'UTF8'), 'base32hex') AS ordered_encoding;
      

Expected: First result: uuid_bytes = 16 and round_trip = true. Second result: Hi becomes url_token = SGk and ordered_encoding = 91KG====. Both encode the same two UTF-8 bytes; the equals signs are Base32hex padding.

Catalog identities — database names and physical tuple addresses

regdatabase resolves a database name to its OID and displays the name again. oid8 is an unsigned 64-bit type; tid_block and tid_offset split a physical tuple identifier without parsing its text. Identifier types, system functions.

Compare with 18: Look up the OID using SELECT oid FROM pg_database WHERE datname = current_database();. Use numeric(20,0) with a bounds CHECK if you need to model unsigned 64-bit values. A ctid can change after updates or rewrites: use it to inspect storage, not as a permanent business identifier.

        SELECT current_database()::regdatabase::oid AS database_oid,
       '18446744073709551615'::oid8 AS unsigned_max;
      

        SELECT id, ctid,
       tid_block(ctid) AS block_number,
       tid_offset(ctid) AS tuple_offset
FROM customers
ORDER BY id;
      

Expected: The database OID, the full unsigned 64-bit maximum, and block/offset parts of each ctid.

CHECK constraints — switch enforcement explicitly

Version 19 lets ALTER TABLE ... ALTER CONSTRAINT toggle CHECK enforcement; this previously applied to foreign keys. The card temporarily disables the rule, inserts a negative price, cleans it up, and restores enforcement. Constraint alteration.

Compare with 18: Drop and recreate the CHECK. ADD CONSTRAINT ... NOT VALID is a different operation: it skips validating old rows while still enforcing the rule for new writes. Version 19 also allows changing a virtual generated column’s expression when it has a CHECK constraint; extended statistics can now include virtual generated columns.

        TRUNCATE prices;
      

        ALTER TABLE prices ALTER CONSTRAINT positive_price NOT ENFORCED;
      

        INSERT INTO prices VALUES (-10);
      

        SELECT amount FROM prices;
      

        DELETE FROM prices WHERE amount <= 0;
      

        ALTER TABLE prices ALTER CONSTRAINT positive_price ENFORCED;
      

        SELECT conname, conenforced
FROM pg_constraint
WHERE conrelid = 'prices'::regclass;
      

Expected: A negative amount is accepted while enforcement is off; after cleaning it up, positive_price is enforced again.

NOT IN — find candidates that are not blocked

Find candidates that are not on a blocked list. The prepared candidate_ids table contains IDs 1–1000; blocked_ids contains 1–500. The condition NOT IN excludes those blocked IDs, leaving 501–1000: 500 rows.

PostgreSQL 19 can turn this exclusion into an anti join: keep a candidate only when there is no matching blocked row. Here, both ID columns are primary keys, so neither can contain NULL. That lets the planner use this optimization; its choice of join method still depends on costs. Planner changes.

The first command shows how the query executes. The second shows how many rows it returns.

1 · Inspect how PostgreSQL finds the unblocked IDs. EXPLAIN ANALYZE executes the SELECT and shows its plan. Look for an Anti Join: it keeps candidate rows that have no matching blocked ID. The exact join method depends on planner costs.


        EXPLAIN (ANALYZE, COSTS OFF)
SELECT id
FROM candidate_ids
WHERE id NOT IN (SELECT id FROM blocked_ids);
      

2 · Count the result. This runs the same exclusion without EXPLAIN and returns the number of unblocked IDs. Expect unblocked = 500, corresponding to IDs 501–1000.


        SELECT count(*) AS unblocked
FROM candidate_ids
WHERE id NOT IN (SELECT id FROM blocked_ids);
      

Expected: First: an execution plan, potentially using an Anti Join. Second: unblocked = 500. Both statements exclude the blocked IDs 1–500.

On PostgreSQL 18, a common way to express the same exclusion is NOT EXISTS. Read it as “keep this candidate if no blocked row has the same ID”:


        -- PostgreSQL 18 approach: exclude candidates with a matching blocked ID.
SELECT c.id
FROM candidate_ids c
WHERE NOT EXISTS (
  SELECT 1
  FROM blocked_ids b
  WHERE b.id = c.id
);
      

For these primary-key IDs, this returns the same IDs 501–1000. To compare plans, prefix it with EXPLAIN (ANALYZE, COSTS OFF).

If the blocked list can contain NULL, NOT IN and NOT EXISTS can produce different results. The example avoids that difference through its primary-key constraints. Running the older query on 19 compares SQL forms; use an actual PostgreSQL 18 server to compare planner versions.

Eager aggregation — aggregate before the join

Version 19 can aggregate eligible rows before joining, reducing intermediate row volume. The card runs the same aggregate with enable_eager_aggregate on by default, then off in one session. Look for a Partial HashAggregate below the join: hundreds of orders become two customer totals before the join. With eager aggregation off, the join processes the orders before aggregating. Plan choices still depend on statistics and data. Optimizer changes.

Compare with 18: The second command disables this particular new optimization on 19. Run SET, EXPLAIN, and RESET in one connection, as shown below. This is a useful controlled comparison, but every other 19 planner improvement still applies.

Inspect the plan with eager aggregation enabled.


        EXPLAIN (ANALYZE, COSTS OFF)
SELECT c.name, sum(o.amount)
FROM customers c
JOIN orders o ON o.customer_id=c.id
GROUP BY c.name;
      

Disable eager aggregation for the comparison, then reset it.


        SET enable_eager_aggregate = off;
EXPLAIN (ANALYZE, COSTS OFF)
SELECT c.name, sum(o.amount)
FROM customers c
JOIN orders o ON o.customer_id=c.id
GROUP BY c.name;
RESET enable_eager_aggregate;
      

Expected: Two plans for the same result. A small dataset may produce the same shape; the second disables eager aggregation.

Defaults and diagnostics — inspect what changed

Release 19 disables JIT by default, enables lock-wait logging, changes the default TOAST compressor to LZ4 where supported, and lets wal_compression = on select the best available compression method. max_locks_per_transaction changes from 64 to 128 along with a lock allocation change. Ordinary strings always use standard-conforming behavior; the old off mode is removed. The card distinguishes each configured setting from its boot default. This sandbox was built without LZ4 support, so its TOAST default remains pglz. Migration notes.

The new pg_stat_lock and pg_stat_recovery views add operational evidence. A recovery view on the primary may have no interesting activity; after starting the standby, query it there as well. Statistics views.

Compare with 18: For a representative analytical query, compare SET jit = on with SET jit = off in the same native installation; use EXPLAIN (ANALYZE, BUFFERS) and reset afterward. This build may lack LLVM JIT. For newly written TOAST values, compare SET default_toast_compression = 'pglz' with lz4 if supported. Changing the setting does not recompress existing rows. String literals needing escapes should use explicit E'...' syntax.

        SELECT name, setting, boot_val, source
FROM pg_settings
WHERE name IN (
  'jit',
  'log_lock_waits',
  'default_toast_compression',
  'wal_compression',
  'max_locks_per_transaction',
  'standard_conforming_strings',
  'password_expiration_warning_threshold',
  'md5_password_warnings'
)
ORDER BY name;
      

        SELECT * FROM pg_stat_lock LIMIT 5;
      

        SELECT * FROM pg_stat_recovery;
      

Expected: Configured and built-in defaults plus the new lock/recovery views; recovery counters may be idle on the primary.

What did not make the cut

Earlier beta headlines are not a reliable feature list. SQL/PGQ property graphs were reverted in the Beta 4 announcement. Online checksum toggling is also absent: pg_checksums still requires a cleanly stopped server.

PostgreSQL 19 rejects shorthand GROUP BY ALL without grouping elements. The current SELECT and ALTER TABLE syntax pages also omit the previously advertised inferred GROUP BY ALL, partition SPLIT/MERGE, and FOR PORTION OF DML. Those are excluded from this tour. PostgreSQL 18’s temporal constraints remain available; they are not new 19 DML.

The card reads the current checksum setting and returns editorial removal labels. These labels are not server capability detection. Use the final release documentation when choosing application dependencies.


        SHOW data_checksums;
      

        SELECT feature, status
FROM (VALUES
  ('online checksum toggling', 'not available in PostgreSQL 19'),
  ('SQL/PGQ property graphs', 'not available in PostgreSQL 19')
) AS release_watch(feature, status);
      

Expected: Current checksum setting, followed by two explicitly labelled editorial rows.

More changes worth knowing

The runnable cards cover the major SQL and operational additions. The rest of the release matters in specific workloads; use the linked documentation to choose your next experiment.

  • More planner and executor work: additional LEFT JOIN to anti-join transformations, Memoize on eligible anti joins, incremental sorts under Append/MergeAppend, and simplification of null/boolean expressions. Faster foreign-key checks, row deformation, sorting, UTF-8 case folding, selective NOTIFY wakeups, SIMD COPY parsing, streaming index cleanup, and parallel TID range scans round out the performance work. Full optimizer/performance list.
  • Replication controls: publication exclusions, sequence refresh, subscriber connection parameters via a foreign server, per-subscription receiver timeouts, and retained conflict-detection information (retain_dead_tuples, bounded by max_retention_duration). Review retention overhead and new conflict/error counters. CREATE SUBSCRIPTION, ALTER SUBSCRIPTION.
  • Observability: statistics-reset timestamps on more relation/function/conflict views; vacuum/analyze initiators; autovacuum scores; sequence synchronization errors; slot-sync skip reasons; WAL full-page-image bytes; extension installation locations; and dynamic shared-memory allocations. EXPLAIN also reports Memoize estimates. Statistics, EXPLAIN.
  • Logging and authentication: separate autoanalyze duration logging, per-process log levels, more COPY/WAL wait events, earlier wraparound warnings, expiring-password warnings, and warnings after MD5 authentication. RADIUS authentication is removed. Server SNI uses pg_hosts.conf; OAuth has custom validator options and improved client hooks/CA configuration. Logging, authentication, libpq.
  • Smaller SQL conveniences: error_on_null(value) raises on NULL rather than silently passing it through; IS JSON accepts domains over supported base types; Polish/Esperanto stemmers; PL/Python event triggers; forward foreign-key references inside CREATE SCHEMA; CHECKPOINT options; aggregate support functions; and GRANTED BY for privilege changes. Function additions, CHECKPOINT, GRANT.
  • psql 19: show the search path with %S, standby status with %i, customize true/false display, inspect comments on publications/subscriptions/statistics, and use an explicit service file. The client version matters separately from the server version. Try \pset display_true yes and \pset display_false no, then SELECT true, false;. psql reference.
  • Administration and extensions: dump/restore extended planner statistics; vacuumdb --dry-run; partitioned-table analysis improvements; tar-WAL support in backup verification/WAL inspection; upgrade improvements for large objects/tablespaces; pgbench --continue-on-error; generic/custom plan counters in pg_stat_statements; foreign statistics import in postgres_fdw; and buffer mapping/dirty-marking tools in pg_buffercache. pg_dump, vacuumdb, postgres_fdw, pg_stat_statements, pg_buffercache.

For extension authors there are new planner hooks, index-handler/API changes, C11 requirements, additional platform support, and shared-memory registration improvements. See the source-code changes.

The text above was extracted automatically from the source page and may miss images, tables or code blocks. The original is authoritative.

Published by Hacker News
This site only summarises and aggregates; copyright stays with the original author. Headlines and summaries follow the source — where they differ, the original wins.
Read the original ↗