PostgreSQL 19 new features that change your monitoring queries (tested on RC 1)

I build HeapDeck, an iPhone and iPad app for Postgres on call. You won't need it here: the checks below run in psql, and they apply to any script or dashboard that reads pg_stat_*.

PostgreSQL 19 is due November 19. I ran my app's live test matrix against RC 1 ahead of it: 1,208 scenarios across PostgreSQL 13 to 19. Two needed a rewrite, both because of one server change. The change that worries me most for other people's dashboards produces no error.

An alert that goes quiet

PostgreSQL 19 moves buffer pin waits from the wait_event_type BufferPin to Buffer, and the event itself becomes BufferCleanup. A query that filters on the old name still runs and returns no rows.

You can see it in a few minutes. RC 2 is due November 12, so check the current tag first:

docker run -d --rm --name pg19 -e POSTGRES_PASSWORD=x postgres:19rc1
docker exec -it pg19 psql -U postgres

Session A holds a pin with an open cursor:

CREATE TABLE b AS SELECT g AS id FROM generate_series(1, 100) g;
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM b;
FETCH 1 FROM c;

Session B waits for it:

VACUUM (FREEZE) b;

Session C looks at the wait, then counts it the way an old alert would:

SELECT wait_event_type, wait_event, state, left(query, 24) AS query
FROM pg_stat_activity
WHERE query LIKE 'VACUUM%';

SELECT count(*) AS waiting_on_pins
FROM pg_stat_activity
WHERE wait_event_type = 'BufferPin';

On PostgreSQL 18.6:

 wait_event_type | wait_event | state  |       query
-----------------+------------+--------+--------------------
 BufferPin       | BufferPin  | active | VACUUM (FREEZE) b;

 waiting_on_pins
-----------------
               1

On PostgreSQL 19 RC 1, the same session waits on the same pin:

 wait_event_type |  wait_event   | state  |       query
-----------------+---------------+--------+--------------------
 Buffer          | BufferCleanup | active | VACUUM (FREEZE) b;

 waiting_on_pins
-----------------
               0

Changing the filter to Buffer catches more than you had before. The docs list BufferExclusive, BufferShared and BufferShareExclusive under the same type, and those wait on buffer locks. This filter keeps the old meaning on both versions:

WHERE wait_event_type = 'BufferPin' OR wait_event = 'BufferCleanup'

Four errors you will see

These fail out loud, so you will find them on the first run:

ERROR:  column "sync_error_count" does not exist
ERROR:  unrecognized configuration parameter "io_workers"
ERROR:  unrecognized configuration parameter "escape_string_warning"
ERROR:  non-standard string literals are not supported

On 18	On 19 RC 1
pg_stat_subscription_stats.sync_error_count	sync_seq_error_count and sync_table_error_count
io_workers	io_min_workers (2 in the Docker image) and io_max_workers (8)
escape_string_warning	removed
SET standard_conforming_strings = off	refused with SQLSTATE 0A000

The last row cost me those two rewrites: my SQL classifier's fuzz tests ran in both modes, and 19 leaves one. To find all of these in your own repo:

grep -rnE "BufferPin|sync_error_count|escape_string_warning|io_workers|standard_conforming_strings" .

Two new warnings at login

PostgreSQL 19 can send a warning after authentication, before your first query:

WARNING:  authenticated with an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release of PostgreSQL.

WARNING:  role password will expire soon
DETAIL:  The password for role "expuser" will expire in 1 day.

password_expiration_warning_threshold sets the window, and it reads 7d on RC 1. To reproduce these, put an md5 or scram-sha-256 line for a test role at the top of pg_hba.conf and connect over TCP. The Docker image trusts local connections by default, and trust skips both.

HeapDeck's connection opens with both warnings, over plain TCP and over TLS. If your driver wrapper or health check treats any server message at login as a failure, test this case.

REPACK, and a mistake it exposed in my classifier

REPACK is new. It rewrites a table to reclaim disk space, and with USING INDEX it also reorders the rows. The CLUSTER page now calls CLUSTER equivalent to REPACK with a USING INDEX clause.

HeapDeck's SQL console classifies each statement before it runs. Version 1.2.7 had never seen REPACK. It fell through to my rule for anything unrecognized, and that rule treats a statement as destructive. The rule kept it safe, but the message didn't say why REPACK is risky.

Reading the docs exposed an older mistake. I had CLUSTER as plain DDL, one class below VACUUM FULL, while it holds ACCESS EXCLUSIVE for the whole rewrite. In 1.2.8 both ask you to type the table name and say why.

If you try REPACK, check two things in the docs first:

• REPACK with no table processes every table and materialized view you hold MAINTAIN on.
• REPACK (CONCURRENTLY) t skips the long lock and refuses to run inside a transaction block. The docs warn: "After the repacking transaction commits, the table will appear empty to concurrent transactions, if they are using a snapshot taken before repacking started."

The new keywords ignore, lsn, repack, respect and wait are all unreserved, so a column with one of those names keeps working. If you maintain an SQL tokenizer, add them.

New system views

My app reads none of these yet:

• pg_stat_autovacuum_scores: one row per table in the current database, with the score autovacuum uses to prioritize it.
• pg_stat_lock: one row per lock type, with cluster-wide lock statistics.
• pg_stat_recovery: at most one row, with the recovery state of the startup process.
• pg_stat_progress_repack: one row per backend running REPACK, CLUSTER or VACUUM FULL. pg_stat_progress_cluster stays for backwards compatibility.

What stayed the same

My drift script reads the columns, settings, functions and extension versions the app depends on, on each major version. 180 rows match on all seven versions from 13 to 19. Seven rows differ between 18 and 19:

pg_replication_slots.slotsync_skip_reason   new column
pg_stat_all_indexes.stats_reset             new column
pg_stat_all_tables.stats_reset              new column
pg_stat_user_indexes.stats_reset            new column
pg_stat_user_tables.stats_reset             new column
pg_stat_statements 1.12 -> 1.13             two rows, one per version

New columns leave a query alone when it names its columns. SELECT * with positional decoding is where they bite. pg_stat_statements 1.13 adds generic_plan_calls and custom_plan_calls, so code that branches on the extension version needs a branch for 1.13 or an "and newer" rule.

This diff covers what one app reads. The release notes cover the rest.

Where this leaves my app

Version 1.2.8 with PostgreSQL 19 support came out on November 2. I'll run the matrix again on the final image after November 19.

HeapDeck shows the wait label as the server reports it, so this one reads Buffer · BufferCleanup. A row turns red for a Lock wait and no other.

Have you run your own dashboards against 19 yet? Tell me what broke in the comments.

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission. HeapDeck is an independent client and is not affiliated with, endorsed by, or sponsored by the PostgreSQL Community Association of Canada, PostgreSQL Global Development Group, PostgreSQL Project, PostgreSQL Core Team, or PostgreSQL Community.