PostgreSQL 19 maintenance extensions

A check first, because it changes how to read this page: none of these four modules changed in PostgreSQL 19. pg_visibility, pg_surgery, pg_prewarm and pgrowlocks ship the same functions they did in 15 through 18. They belong in this section anyway because 19 changed the behaviour they inspect: ordinary table scans can now set visibility-map bits (previously only VACUUM and COPY ... FREEZE could), pg_stat_lock aggregates lock contention but cannot show which rows are locked, and the new freeze-driven vacuum scores raise the cost of the corruption cases pg_surgery exists for. These are the tools for “is the on-disk state what the database thinks it is”.

All four install the same way:

CREATE EXTENSION pg_visibility;   -- or pg_surgery, pg_prewarm, pgrowlocks

pg_prewarm’s autoprewarm worker additionally needs shared_preload_libraries = 'pg_prewarm' and a restart.

pg_visibility - is the visibility map telling the truth

The visibility map (VM) holds two bits per heap page: all-visible (every tuple on the page is visible to every transaction - index-only scans may skip the heap) and all-frozen (every tuple is frozen - vacuum need not touch the page until it is modified again). A third bit, PD_ALL_VISIBLE, lives in the page header and should agree with the VM’s all-visible bit; crash recovery, races during inspection, or corruption can make them disagree.

Most functions run as superuser or pg_stat_scan_tables; pg_truncate_visibility_map is superuser only. The practical loop for the 19 change: after a large reporting scan, pg_visibility_map_summary shows whether all-visible coverage rose - that is the difference between an index-only scan that stays index-only and one that silently fetches every heap page.

pgrowlocks - which rows are locked, not which lock type

pg_stat_lock (new in 19) answers “which lock type serialises my writers”. pgrowlocks answers the orthogonal question: “which rows in this table are locked right now, and by whom”:

SELECT * FROM pgrowlocks('accounts');
-- locked_row | locker | multi |  xids   |      modes       |  pids
-- (0,1)      |    609 | f     | {609}   | {"For Share"}    | {3161}
-- (0,3)      |    607 | f     | {607}   | {"For Update"}   | {3107}

It takes AccessShareLock and reads the table row by row, so on a large table it is slow, it blocks behind any ACCESS EXCLUSIVE lock, and its output is not a consistent snapshot - locks can appear or release mid-scan. It shows lock holders (For Key Share through Update, including multixact IDs), not waiters; for the waiter side join pg_locks to pg_blocking_pids. Reach for it when pg_stat_lock says row-level lock waits are hurting and you need to see which rows the fight is over.

pg_surgery - the last resort for corruption that blocks vacuum

Two functions, both deliberately unsafe:

The second case matters more in 19 than before: a table that cannot be vacuumed accumulates the highest possible freeze score, and once vacuum_failsafe_age is passed the vacuum that runs is the aggressive, non-cancellable kind. Freezing the corrupt tuple lets vacuum proceed - at the price the documentation states plainly: these functions can make a table inconsistent with its indexes, violate UNIQUE or FOREIGN KEY constraints, or make tuples visible that crash the server when read. Use them to get a damaged table into a state where pg_dump or REPACK can run, then fix the data properly - they are a bridge to a rebuild, not a repair.

pg_prewarm - cache warmup, unchanged

pg_prewarm loads relation data into cache before it is asked for:

• pg_prewarm(rel, mode, fork, first_block, last_block) - three modes: buffer (into the PostgreSQL buffer cache), read (into the OS cache, synchronous, works everywhere), prefetch (asynchronous OS prefetch where supported).
• Autoprewarm: with pg_prewarm in shared_preload_libraries, a background worker dumps the shared buffer contents to autoprewarm.blocks every pg_prewarm.autoprewarm_interval seconds (default 300, 0 means dump only at shutdown), and after a restart two workers reload those blocks. autoprewarm_start_worker() and autoprewarm_dump_now() run it on demand.

The honest caveat is in the documentation: prewarmed blocks get no protection from eviction, and prewarming more than fits will evict other data - which is why it is mostly a startup tool, warming the cache while the cache is empty anyway. In 19 it pairs naturally with EXPLAIN (ANALYZE, IO) and the elastic I/O worker pool: a prewarmed buffer pool is what makes the I/O numbers on the async I/O page reproducible in benchmarks.

What changed, and in which release

Where it breaks

• pg_visibility on a huge table - the functions that consult PD_ALL_VISIBLE read every data block; use the _map variants or the summary first.
• pgrowlocks mid-incident - it scans the table under AccessShareLock; on the contended table itself that can be the slowest possible way to look. pg_locks + pg_blocking_pids first.
• pg_surgery used as a fix - it makes broken pages consistent enough to work with, not correct; anything frozen or killed this way needs auditing afterwards.
• Prewarming past the cache size - blocks read early are evicted by blocks read later, so prewarming a relation larger than shared_buffers mostly warms the tail of it.

What this is not for

• Routine monitoring - these are point-in-time diagnostics; the always-on surface is the statistics views on the monitoring page.
• Fixing bloat - pg_visibility tells you the map’s state, not the table’s; bloat inspection is pgstattuple (which in 19 got faster streaming reads), and the fix is REPACK.
• Page-level forensics - for reading tuple headers and page internals directly, that is pageinspect, not these modules.