Every PostgreSQL release quietly improves how much you can see about what your database is doing. PostgreSQL 19 is no exception, and this time the improvements are unusually practical: fewer blind spots in the log, better visibility into WALand wait events for paths that used to look idle.
This is a two-part walk-through. Part 1 (this post) covers logging, WAL, and I/O. Part 2 covers WAIT FOR, the new system views, autovacuum prioritisation, and what is already queued for PostgreSQL v20.
For each feature, we call out what changes and what to check after upgrading.
1. Logging
log_lock_waits is now on by default
log_lock_waits has existed for a long time, but it has been switched off since it was introduced. PostgreSQL 19 flips the default to on.
When enabled, a message is logged whenever a session waits longer than deadlock_timeout (default 1 second) to acquire a lock. The check piggybacks on the deadlock detector, which is why the threshold is deadlock_timeout and not a separate setting of its own.
The reasoning is simple: if a session is stuck behind a lock for more than a second, that is almost always a problem worth a log entry. It is a cheap lock-contention detector, and now it is there without anyone having to remember to enable it.
— Check what your cluster will do after the upgrade
SHOW log_lock_waits;
SHOW deadlock_timeout;After upgrading: because lock waits are logged only once they exceed deadlock_timeout, that setting now also controls what reaches your log. Check your value. Then consider alerting on the “still waiting for lock” messages. On most clusters, these lines will be new.
Different log verbosity per process type
log_min_messages used to take a single level that applied to the whole server. In PostgreSQL 19, it accepts a list of type: level pairs, so you can turn up the detail for the one process you are investigating without drowning in debug output from everything else.
— DEBUG2 for the checkpointer, DEBUG1 for autovacuum, WARNING for everything else
ALTER SYSTEM SET log_min_messages = 'warning, checkpointer:debug2, autovacuum:debug1';
SELECT pg_reload_conf();A few rules worth knowing:
- One bare level is mandatory. It applies to every process type you do not list.
- There are fifteen process types:
archiver, autovacuum, backend, bgworker, bgwriter, checkpointer, ioworker, postmaster, slotsyncworker, startup, syslogger, walreceiver, walsender, walsummarizer and walwriter. - autovacuum covers both the launcher and the workers.
After upgrading: nothing breaks. A single level is still valid. But if you ship a config validator or a UI that checks log_min_messages, teach it the type: level list syntax, otherwise, it will reject valid settings.
2. WAL
wal_fpi_bytes: the same number in four places
Full-page images (FPIs) are a major driver of WAL volume, especially right after a checkpoint. Until now, you could see how many full-page images were written, but not how many bytes they accounted for. PostgreSQL 19 adds the bytes written as full-page images (a contribution from Shinya Kato), and exposes it at four levels:
| Level | Where | What it tells you |
|---|---|---|
| Cluster | pg_stat_wal.wal_fpi_bytes | Total since the last stats reset. This is what your metrics collector should poll. |
| Session | pg_stat_get_backend_wal(pid) | The same counter for one backend: which connection is producing the WAL? |
| Operation | VACUUM / ANALYZE log line | A new full page image bytes field in the WAL usage line of each run. |
| Query | EXPLAIN (ANALYZE, WAL) | A new fpi bytes value in the WAL line of the plan. |
A VACUUM log line now looks like this:
WAL usage: 2841 records, 1904 full page images, 15735621 bytes,
15242880 full page image bytes, 3 buffers full
In that example, almost all of the WAL generated (about 15.2 MB of 15.7 MB) is full-page images. That is a very different story from 1904 images and 2841 records, and it points you at checkpoint frequency and full_page_writes behaviour rather than at the workload itself.
— Cluster-wide FPI volume vs total WAL
SELECT wal_records, wal_fpi, wal_bytes, wal_fpi_bytes,
round(100.0 * wal_fpi_bytes / NULLIF(wal_bytes, 0), 1) AS fpi_pct
FROM pg_stat_wal;The real value here is the drill-down path: spot a high FPI ratio in the cluster counter, find the guilty session with pg_stat_get_backend_wal(pid), then confirm at query level with EXPLAIN (ANALYZE, WAL).
3. I/O
New wait events for COPY
There was a gap. COPY FROM / COPY TO On a file, pipe, or program did its reads and writes with no wait event at all. A backend stuck in a bulk load looked idle in pg_stat_activity, with wait_event set to NULL. (COPY over the wire protocol was already covered by ClientRead / ClientWrite.)
PostgreSQL 19 closes that gap with two new I/O wait events:
- CopyFromRead: waiting to read from a pipe, file, or program during COPY FROM.
- CopyToWrite: waiting to write to a pipe, file or program during COPY TO.
These cover the paths most commonly used by bulk loads and ETL pipelines.
SELECT pid, state, wait_event_type, wait_event, left(query, 60) AS query
FROM pg_stat_activity
WHERE wait_event IN ('CopyFromRead', 'CopyToWrite');If you have ever stared at a long-running COPY ... FROM PROGRAM wondering whether it was working or hung, this is the signal you were missing: the session is waiting on the program’s output, not idle.
Recap and what is next
Part 1 in one list:
log_lock_waitsis on by default; check deadlock_timeout first.log_min_messagestakes per-process-type levels.wal_fpi_bytesshows up inpg_stat_wal, per-backend WAL stats,VACUUM/ANALYZElogs andEXPLAIN (ANALYZE, WAL).- CopyFromRead / CopyToWrite end the “idle but actually copying” mystery.
In Part 2 we look at WAIT FOR (read your writes on async replicas), the new pg_stat_lock, pg_stat_recovery and pg_stat_autovacuum_scores views, autovacuum priorities, and a preview of what is already on master for PostgreSQL 20.