PostgreSQL 19: part 5.2 (DBA1 and DBA2) of CommitFest 2026-03

We’re continuing our review of the March CommitFest for PostgreSQL 19. This second article in the series covers the changes related to our DBA1 and DBA2 courses.

As a reminder, here’s the plan for our coverage of the final PostgreSQL 19 CommitFest:

Part 5.1 (QPT)

Part 5.2 (DBA1, DBA2) -- you are here 🙂

Part 5.3 (DBA3, DBS)

Part 5.4 (SQL, DEV1, DEV2)

For the earlier CommitFests, see: 2025-07, 2025-09, 2025-11, 2026-01.

Here’s what’s covered:

• REPACK: rewriting a table to reclaim disk space
• Autovacuum: table processing order
• Autovacuum: parallel index vacuuming
• Enhanced transaction ID wraparound warnings
• Checkpoint reason in the completion log message
• Less WAL and on-access visibility map updates
• More code paths use read streams
• pg_stat_lock: statistics on long lock waits
• pg_upgrade: faster large object transfer, part 2
• oid2name: file paths relative to PGDATA
• psql: comments on internal queries
• log_min_messages per backend type
• Stats reset time for pg_stat_database_conflicts and pg_statio_all_sequences

REPACK: rewriting a table to reclaim disk space

commit: ac58465e061, 28d534e2ae0

The CLUSTER and VACUUM FULL commands deal with table bloat. They rewrite a table and its indexes into new files, leaving out dead tuples, and return the freed space to the operating system. The difference is that CLUSTER also physically reorders the rows according to the specified index.

The new REPACK command combines the functionality of VACUUM FULL and CLUSTER under a clearer name:

REPACK bookings; -- replaces VACUUM FULL
REPACK bookings USING INDEX bookings_pkey; -- replaces CLUSTER

In this form, REPACK, just like CLUSTER and VACUUM FULL, holds an exclusive lock for the whole operation. On a busy production system, that’s usually unacceptable.

The second commit adds the CONCURRENTLY option to REPACK, which lets you rebuild a table without blocking the applications that write to it. In this mode, the command creates new files with a copy of the table and its indexes, and uses logical decoding to capture the changes made while it’s running. At the end, it applies those changes and takes a brief exclusive lock to swap the old files for the new ones.

REPACK(concurrently) bookings;

REPACK(concurrently) has some serious limitations. For example, the command holds the database horizon for its entire duration, which prevents [auto]vacuum from removing dead tuples in other tables. It can’t process a partitioned table as a whole, though it does work on individual partitions. The table needs a primary key or a unique index on NOT NULL columns to serve as its replica identity. And you can’t repack system catalog tables or TOAST tables.

Some of these limitations may be lifted in future versions. For now, be sure to read up on them in the documentation before using it.

Autovacuum: table processing order

commit: d7965d65fc5, 87f61f0c828

An autovacuum worker has to process every relation in the database that needs vacuuming or analyzing. There are five criteria that put a relation on the list, and they haven’t changed:

• The age of the oldest transaction ID
• The age of the oldest multixact ID
• The number of dead tuples
• The number of inserted tuples
• The number of tuples modified since the last analyze

But in what order should the worker process the relations on this list? Until now, the order was essentially arbitrary. The worker read pg_class, evaluated the criteria, and built a list of tables and materialized views to process. Then it read pg_class a second time to add the TOAST tables that needed processing. Finally, it processed the relations in the order they had been added to the list.

PostgreSQL 19 introduces a simple prioritization system for processing tables. For every relation, the autovacuum worker calculates a separate score for each criterion above. The highest of the five becomes the relation’s overall score.

To keep the math simple, let’s disable the parameters that specify a fraction of the table’s rows (autovacuum_*_scale_factor). Then only the autovacuum_*_threshold parameters will decide whether a relation gets processed. We’ll also lengthen the delay between autovacuum runs so we have time to look at the numbers:

ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0;
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0;
ALTER SYSTEM SET autovacuum_vacuum_insert_scale_factor = 0;

ALTER SYSTEM SET autovacuum_naptime = '1h';
SELECT pg_reload_conf();

Now let’s create a table and insert 2000 rows into it.

CREATE TABLE test1 (id int);
INSERT INTO test1 SELECT g.x FROM generate_series(1,2000) AS g(x);

The new pg_stat_autovacuum_scores view shows the overall score and the individual score for each criterion. The last three columns are Boolean flags indicating whether the table needs vacuuming, analyzing, or freezing:

SELECT relname,
       round(score) score,
       round(xid_score) xid_score,
       round(mxid_score) mxid_score,
       round(vacuum_score) vacuum_score,
       round(vacuum_insert_score) vacuum_insert_score,
       round(analyze_score) analyze_score,
       do_vacuum,
       do_analyze,
       for_wraparound
FROM pg_stat_autovacuum_scores
WHERE schemaname = 'public' AND relname ~ 'test'
\gx

-[ RECORD 1 ]-------+------
relname             | test1
score               | 40
xid_score           | 0
mxid_score          | 0
vacuum_score        | 0
vacuum_insert_score | 2
analyze_score       | 40
do_vacuum           | t
do_analyze          | t
for_wraparound      | f

The table meets two criteria:

• vacuum_insert_score = 2
  2000 rows is twice autovacuum_vacuum_insert_threshold (1000).
• analyze_score = 40
  2000 rows is 40 times autovacuum_analyze_threshold (50).

The overall score (the score column) is the highest of these, so it’s 40.

In another table, let’s insert 1000 rows and immediately delete half of them, leaving 500 dead tuples:

CREATE TABLE test2 (id int);
INSERT INTO test2 SELECT g.x FROM generate_series(1,1000) AS g(x);
DELETE FROM test2 WHERE id > 500;

Let’s compare the vacuum and analyze scores for both tables:

SELECT relname,
       round(score) score,
       round(vacuum_score) vacuum_score,
       round(vacuum_insert_score) vacuum_insert_score,
       round(analyze_score) analyze_score
FROM pg_stat_autovacuum_scores
WHERE schemaname = 'public' AND relname ~ 'test'
ORDER BY score DESC;

 relname | score | vacuum_score | vacuum_insert_score | analyze_score
---------+-------+--------------+---------------------+---------------
 test1   |    40 |            0 |                   2 |            40
 test2   |    30 |           10 |                   1 |            30
(2 rows)

The second table scores 10 for dead tuples and 30 for analyze, so its overall score is lower than the first table’s.

But what if we want tables with lots of dead tuples to take priority? That’s what the new autovacuum_vacuum_score_weight parameter is for. It sets a weight for this criterion:

ALTER SYSTEM SET autovacuum_vacuum_score_weight = 10;
SELECT pg_reload_conf();

Now vacuum_score is multiplied by 10, so test2 gets processed before test1:

SELECT relname,
       round(score) score,
       round(vacuum_score) vacuum_score,
       round(vacuum_insert_score) vacuum_insert_score,
       round(analyze_score) analyze_score
FROM pg_stat_autovacuum_scores
WHERE schemaname = 'public' AND relname ~ 'test'
ORDER BY score DESC;

 relname | score | vacuum_score | vacuum_insert_score | analyze_score
---------+-------+--------------+---------------------+---------------
 test2   |   100 |          100 |                   1 |            30
 test1   |    40 |            0 |                   2 |            40
(2 rows)

Each of the five criteria has its own weight, and they all default to 1:

\dconfig+ autovacuum_*_weight

                           List of configuration parameters
                Parameter                 | Value | Type | Context | Access privileges
------------------------------------------+-------+------+---------+-------------------
 autovacuum_analyze_score_weight          | 1     | real | sighup  |
 autovacuum_freeze_score_weight           | 1     | real | sighup  |
 autovacuum_multixact_freeze_score_weight | 1     | real | sighup  |
 autovacuum_vacuum_insert_score_weight    | 1     | real | sighup  |
 autovacuum_vacuum_score_weight           | 10    | real | sighup  |
(5 rows)

To disable the new prioritization system, just set all five parameters to 0.

Autovacuum: parallel index vacuuming

commit: 1ff3180ca01

Manual VACUUM(PARALLEL N) has been able to process a table’s indexes in parallel since version 13, but autovacuum always used a single process per table, no matter how many indexes the table had. Now autovacuum can launch parallel workers to process the indexes of a single table.

The new autovacuum_max_parallel_workers configuration parameter sets the maximum number of parallel workers per autovacuum worker:

\dconfig+ autovacuum_max_parallel_workers

                        List of configuration parameters
            Parameter            | Value |  Type   | Context | Access privileges
---------------------------------+-------+---------+---------+-------------------
 autovacuum_max_parallel_workers | 0     | integer | sighup  |
(1 row)

By default, parallel autovacuum is off, so the behavior matches previous versions.

You can also set the number of index vacuuming workers per table:

ALTER TABLE flights SET (autovacuum_parallel_workers = 2);

Enhanced transaction ID wraparound warnings

commit: e646450e609, 48f11bfa06c

The warning about approaching transaction ID wraparound used to kick in 40 million transactions before the limit. At today’s transaction rates, that can be too late, so the warning now appears 100 million transactions ahead. In addition to the number of remaining transactions, it now also reports the percentage of transaction IDs still available:

WARNING:  database "mydb" must be vacuumed within 99985967 transactions
DETAIL:  Approximately 4.66% of transaction IDs are available for use.

The idea is that a small remaining percentage signals urgency to the DBA more clearly than a transaction count that looks reassuringly large.

Checkpoint reason in the completion log message

commit: 5b93a5987bd

Previously, the reason for a checkpoint (time-based, manual, or triggered by WAL growth) was logged only when the checkpoint started, not when it completed. That made it harder to tell why a given checkpoint had run. Now the completion message includes the reason too:

CHECKPOINT;
\! tail -2 logfile

2026-09-10 13:30:05.951 MSK [22469] LOG:  checkpoint starting: fast force wait
2026-09-10 13:30:05.965 MSK [22469] LOG:  checkpoint complete: fast force wait: wrote 0 buffers (0.0%), wrote 0 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.015 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=2403 kB; lsn=F/2F962CF0, redo lsn=F/2F962C90

Less WAL and on-access visibility map updates

commit: a881cc9c7e8, b46e1e54d07, 378a216187a

Vacuum no longer writes a separate WAL record noting that a page has been marked all-visible/all-frozen in the visibility map. Instead, the change is recorded in the WAL record for the heap page itself. This reduces WAL volume for bulk operations on tables.

This also makes it possible to update the visibility map on access, not only during vacuum (and COPY FREEZE). What does “on access” mean? Whenever a page is accessed, whether by a read or a write, page pruning may occur. Page pruning removes tuple versions on the page that are no longer needed. In PostgreSQL 19, pruning can also update the visibility map, setting the all-visible bit if only tuples visible to all transactions remain on the page. So ordinary read queries can now set the all-visible bit for a page as they prune it.

The benefit is that the visibility map no longer has to wait for vacuum: regular table operations can update it page by page. And vacuum will have less work to do if some pages are already marked in the map.

More code paths use read streams

commit: ae58189a4d5, 213f0079b34, 4c910f3bbe9, d841ca2d149, 6c228755add, bfa3c4f106b

The read stream interface, introduced back in version 17, keeps spreading to more subsystems:

• The pgstattuple extension: the pgstattuple_approx, pgstatindex, and pgstathashindex functions
• bloom indexes: vacuum cleanup, bulk delete, and bitmap scans
• Vacuum cleanup of GIN indexes
• Bulk delete for hash indexes

pg_stat_lock: statistics on long lock waits

commit: 4019f725f5d

The pg_locks view only shows a snapshot of current locks. Once a lock is released, the only trace of it may be in the server log, and only if the wait lasted longer than deadlock_timeout and log_lock_waits is enabled (in PostgreSQL 19, it’s on by default).

The new pg_stat_lock view accumulates statistics on lock waits that last longer than deadlock_timeout, aggregated by lock type.

Here’s an example. First, let’s reset the lock statistics:

SELECT pg_stat_reset_shared('lock');

In the first session, we lock a row in the table:

1=# BEGIN;
1=*# SELECT * FROM bookings WHERE book_ref = '2EW1SQ' FOR UPDATE;

 book_ref |           book_date           | total_amount
----------+-------------------------------+--------------
 2EW1SQ   | 2025-09-01 03:00:12.557744+03 |      8125.00
(1 row)

In the second session, we try to lock the same row:

2=# SELECT * FROM bookings WHERE book_ref = '2EW1SQ' FOR UPDATE;

While the command is waiting for the row to be released, we take our time switching back to the first session and committing the transaction:

1=*# COMMIT;

The second session can now proceed:

2=# SELECT * FROM bookings WHERE book_ref = '2EW1SQ' FOR UPDATE;

 book_ref |           book_date           | total_amount
----------+-------------------------------+--------------
 2EW1SQ   | 2025-09-01 03:00:12.557744+03 |      8125.00
(1 row)

And pg_stat_lock now has a record of the lock wait:

SELECT locktype, waits, wait_time, fastpath_exceeded
FROM pg_stat_lock;

     locktype     | waits | wait_time | fastpath_exceeded
------------------+-------+-----------+-------------------
 relation         |     0 |         0 |                 0
 extend           |     0 |         0 |                 0
 frozenid         |     0 |         0 |                 0
 page             |     0 |         0 |                 0
 tuple            |     0 |         0 |                 0
 transactionid    |     1 | 10060.469 |                 0
 virtualxid       |     0 |         0 |                 0
 spectoken        |     0 |         0 |                 0
 object           |     0 |         0 |                 0
 userlock         |     0 |         0 |                 0
 advisory         |     0 |         0 |                 0
 applytransaction |     0 |         0 |                 0
(12 rows)

Statistics cover all databases in the cluster and are grouped by locktype, which has the same values as the locktype column in pg_locks. The waits and wait_time (in milliseconds) columns only count long lock waits (longer than deadlock_timeout). The fastpath_exceeded column shows how many times a lock couldn’t be acquired via the fast path. If this number is high, consider increasing max_locks_per_transaction.

pg_upgrade: faster large object transfer, part 2

commit: b33f7536128

In our review of the first CommitFest, we described how pg_upgrade now transfers large object metadata (pg_largeobject_metadata) with the faster COPY command instead of individual INSERT commands. However, this optimization only applied when upgrading from version 12 or later.

Now that restriction is gone: COPY is used for pg_largeobject_metadata regardless of the old server’s version.

oid2name: file paths relative to PGDATA

commit: 3c5ec35dea2

In extended mode, the oid2name utility now shows file paths relative to PGDATA:

$ oid2name -d demo -t bookings --extended

From database "demo":
  Filenode  Table Name    Oid    Schema  Tablespace              Path
---------------------------------------------------------------------
     17258    bookings  17258  bookings  pg_default  base/16384/17258

You can get the same path with the pg_relation_filepath function:

$ psql -d demo -c "SELECT pg_relation_filepath('bookings')"

 pg_relation_filepath
----------------------
 base/16384/17258
(1 row)

psql: comments on internal queries

commit: 41d69e6dcca

psql runs internal queries against the system catalog to get the information it displays. In the server log or in script output, these queries used to be hard to tell apart from user queries. Now each internal query is wrapped in descriptive comments.

\set ECHO_HIDDEN on

\sf now

/**** INTERNAL QUERY ****/
/* Get function's OID */
SELECT 'now'::pg_catalog.regproc::pg_catalog.oid
/************************/

/**** INTERNAL QUERY ****/
/* Get function's definition */
SELECT pg_catalog.pg_get_functiondef(1299)
/************************/

CREATE OR REPLACE FUNCTION pg_catalog.now()
 RETURNS timestamp with time zone
 LANGUAGE internal
 STABLE PARALLEL SAFE STRICT
AS $function$now$function$

log_min_messages per backend type

commit: 38e0190ced7

When debugging background processes, it helps to get more detailed logs, for example by changing log_min_messages from WARNING to DEBUG1. But then the server log gets flooded with messages from all processes, especially client backends.

In PostgreSQL 19, log_min_messages lets you set a separate message level for each backend type. Let’s set DEBUG5 for the checkpointer and keep WARNING for everything else:

ALTER SYSTEM SET log_min_messages = 'warning, checkpointer:debug5';
SELECT pg_reload_conf();

Now, after a checkpoint, debug messages show up in the server log:

CHECKPOINT;
\! tail logfile | grep DEBUG

2026-09-10 14:28:56.991 MSK [22469] DEBUG:  checkpointer updated shared memory configuration values
2026-09-10 14:29:05.484 MSK [22469] DEBUG:  performing replication slot checkpoint
2026-09-10 14:29:05.555 MSK [22469] DEBUG:  snapshot of 0+0 running transaction ids (lsn F/2F962E08 oldest xid 1383 latest complete 1382 next xid 1383)
2026-09-10 14:29:05.561 MSK [22469] DEBUG:  attempting to remove WAL segments older than log file 000000000000000F0000002E
2026-09-10 14:29:05.562 MSK [22469] DEBUG:  SlruScanDirectory invoking callback on pg_subtrans/0000

Stats reset time for pg_stat_database_conflicts and pg_statio_all_sequences

commit: 723619eaa3a, 8fe315f18d4

The pg_stat_database_conflicts and pg_statio_all_sequences views didn’t have a stats_reset column showing when the counters were last reset, unlike the other statistics views. Now they do.

Similar work was done earlier in this release cycle for the pg_stat_{all|user}_{tables|indexes} and pg_stat_user_functions views.

That’s all for now. Next time, we’ll look at the changes related to the DBA3 and DBS courses.