The update counter fillfactor was waiting for

Two numbers that were never the same number

Every guide to fillfactor says roughly the same thing. Leave space on each page so an updated row can put its new version next to its old one; when it can, the update is a heap-only tuple and the indexes do not have to be touched; when it cannot, the row moves, every index on the table gets a new entry, and the old entry waits for vacuum. Lower the setting on a heavily updated table and the write amplification falls.

The advice is sound. Until PostgreSQL 16, checking whether it had worked was not straightforward, because the server counted two things and the thing you wanted was neither of them. It counted updates, and it counted the subset of updates that were heap-only. The arithmetic everybody does is to subtract one from the other and call the remainder “updates that moved”, which is how the number gets into dashboards and how it gets into post-incident write-ups.

That remainder is wrong, and it is wrong in the direction that makes fillfactor look less effective than it is. PostgreSQL 16 added a column that counts the thing directly: updates whose new row version could not be placed on the page holding the old one. The Monitoring section of the PostgreSQL 16 release notes records it in a single line naming the column.

Why the subtraction does not hold

An update fails to be heap-only for either of two reasons, and only one of them involves the row moving.

The first is space. There is no room on the page for the new version, so it goes on another page, every index must learn the new location, and the page the row came from now holds a dead tuple. This is the case fillfactor exists to prevent, and it is the case the new column counts.

The second is indexed columns. If the update changes a column that any index covers, the update cannot be heap-only no matter how much room the page has, because the index entry has to change by definition. The new row version can still land on the same page. The write is cheaper than a move: one page dirtied rather than two, no extra page to fetch, and the index work confined to the indexes that actually cover the changed column.

Subtracting heap-only updates from all updates lumps both cases together. On a table with several secondary indexes and an application that touches indexed columns routinely, most of the remainder is the second case, and tuning fillfactor will not shift it at all. Somebody lowers the setting, rebuilds the table over a weekend, watches the derived number refuse to move, and concludes that fillfactor tuning is folklore.

The fixture

Rows that are small enough to leave real free space at a moderate fillfactor, and a secondary index so that one kind of update can be forced without the other.

CREATE TABLE invoice_line (id integer PRIMARY KEY, sku text, note text) WITH (fillfactor = 60);
INSERT INTO invoice_line SELECT g, 'sku-' || g, repeat('.', 40) FROM generate_series(1, 5000) AS g;
CREATE INDEX invoice_line_sku_idx ON invoice_line (sku);
VACUUM ANALYZE invoice_line;

CREATE TABLE
INSERT 0 5000
CREATE INDEX
VACUUM

On 15 the column does not exist, and the server will not pretend otherwise:

SELECT n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
FROM pg_stat_user_tables WHERE relname = 'invoice_line';

ERROR:  column "n_tup_newpage_upd" does not exist
LINE 1: SELECT n_tup_upd, n_tup_hot_upd, n_tup_newpage_upd
                                         ^

So 15 gets the same workload and the subtraction everybody does, written out so the assumption inside it is visible. An update that changes an indexed column cannot be heap-only, which makes the remainder equal to the whole batch:

SELECT pg_sleep(2);
SELECT pg_stat_reset_single_table_counters('invoice_line'::regclass);
SELECT pg_sleep(2);
UPDATE invoice_line SET sku = 'sk0-' || id WHERE id <= 1000;
SELECT pg_sleep(2);
SELECT n_tup_upd,
       n_tup_hot_upd,
       n_tup_upd - n_tup_hot_upd AS assumed_to_have_moved
FROM pg_stat_user_tables WHERE relname = 'invoice_line';

 pg_sleep 
----------
 
(1 row)

 pg_stat_reset_single_table_counters 
-------------------------------------
 
(1 row)

 pg_sleep 
----------
 
(1 row)

UPDATE 1000
 pg_sleep 
----------
 
(1 row)

 n_tup_upd | n_tup_hot_upd | assumed_to_have_moved 
-----------+---------------+-----------------------
      1000 |             0 |                  1000
(1 row)

On 16, the two cases separate

First, an update that changes an indexed column and leaves the row the same size. It cannot be heap-only, and at this fillfactor there is room on the page, so it should not need to move.

SELECT pg_sleep(2);
SELECT pg_stat_reset_single_table_counters('invoice_line'::regclass);
SELECT pg_sleep(2);
UPDATE invoice_line SET sku = 'sk0-' || id WHERE id <= 1000;
SELECT pg_sleep(2);
SELECT n_tup_upd, n_tup_hot_upd,
       n_tup_upd - n_tup_hot_upd AS the_old_inference,
       n_tup_newpage_upd AS actually_moved
FROM pg_stat_user_tables WHERE relname = 'invoice_line';

 pg_sleep 
----------
 
(1 row)

 pg_stat_reset_single_table_counters 
-------------------------------------
 
(1 row)

 pg_sleep 
----------
 
(1 row)

UPDATE 1000
 pg_sleep 
----------
 
(1 row)

 n_tup_upd | n_tup_hot_upd | the_old_inference | actually_moved 
-----------+---------------+-------------------+----------------
      1000 |             0 |              1000 |            323
(1 row)

The old inference and the measurement disagree, and they disagree by a lot. Every one of those updates was non-heap-only, so the subtraction attributes all of them to page pressure. The measured column says that most of them stayed where they were. On 15 there was no way to see that, and no way to know that lowering fillfactor further would have bought nothing.

Now the other case. Grow the rows so that the new version genuinely cannot fit beside the old one.

UPDATE invoice_line SET note = repeat('#', 800) WHERE id <= 1000;
SELECT pg_sleep(2);
SELECT n_tup_upd, n_tup_hot_upd,
       n_tup_upd - n_tup_hot_upd AS the_old_inference,
       n_tup_newpage_upd AS actually_moved
FROM pg_stat_user_tables WHERE relname = 'invoice_line';

UPDATE 1000
 pg_sleep 
----------
 
(1 row)

 n_tup_upd | n_tup_hot_upd | the_old_inference | actually_moved 
-----------+---------------+-------------------+----------------
      2000 |           107 |              1893 |           1216
(1 row)

The second batch drives the measured column up, which is the behaviour the tuning advice predicts and which nothing before 16 could confirm.

What to do with the column

The value is a monotonically increasing count, so the useful form is a rate, and the useful comparison is against the update rate on the same table. A table where most updates move is a table paying index maintenance and vacuum work on nearly every write, and it is a candidate for a lower fillfactor, for fewer indexes, or for a schema change that stops rows growing after insert.

Three patterns are worth recognising in the ratio.

• A high move rate on a table whose rows never change size. This is page pressure pure and simple, and fillfactor is the lever.
• A high move rate that appears suddenly on a table that was fine last month. Usually a column started being filled in that used to be null or short, so rows grew past the free space that was sized for them.
• A low move rate alongside a large gap between updates and heap-only updates. This is the indexed-column case, and no amount of fillfactor tuning touches it. The lever here is the index list.

The third of those is the one that was previously invisible, and it is the reason the column is worth collecting even on a fleet where nobody is currently tuning anything.

Reading it beside the counters you already have

The column earns its place when it is read next to two others in the same row rather than on its own.

The first is the dead tuple count. Every update that moves a row leaves a dead version behind on the page it came from, so a high move rate and a high dead tuple count describe the same event from two sides. What that pairing tells you is whether vacuum is keeping up: a table with a high move rate whose dead count stays flat has a healthy cadence, and the same table with a dead count that climbs between vacuums is where bloat is being created faster than it is being reclaimed.

The second is the table’s size over time. A move does not just dirty a second page; it eventually leaves free space on the first one that only vacuum can reclaim and that only a future insert or update can use. On a table whose access pattern never returns to old pages, that space is effectively lost, and the table grows in a way no row count explains.

There is a partitioned-table wrinkle worth knowing. Statistics live on the partition, so a partitioned table’s move rate is the sum over its partitions, and the interesting case is almost always a single partition rather than the whole set. A time-partitioned table where updates land in the newest partition will show all of its moves there and none anywhere else, which is correct and also means a parent-level roll-up tells you nothing useful.

One more use is worth mentioning because it is the reverse of the usual direction. If you are considering raising fillfactor on a table to save space, this column is the evidence for whether you can. A table with no moves at its current setting has free space it is not using, and reclaiming it is safe. A table with moves already is a table where raising the setting will make the rate worse, and the counter will tell you by how much.

The measurement has a floor you cannot tune away

Free space is per page and rows are not uniform. Even on a table with generous free space and rows that do not grow, some updates land on a page that happens to be full, so the counter never reaches zero. Treat a small residue as normal and watch the shape of the number rather than its absolute value.

It is also worth being clear about what the column does not say. It counts row versions that moved; it does not say where they went, how much space the old versions are now occupying, or whether vacuum has caught up. A table with a high move rate and a healthy vacuum cadence is fine. The same table with autovacuum falling behind is where bloat comes from, and the two signals have to be read together. The dead tuple count beside it in the same view is the other half, and the failsafe that abandons cost limits is what the server does when that half has been ignored for long enough.

What it costs to observe

Nothing. The counter is incremented in the same place the update already decides where to put the new row version, it is reported in the same statistics flush as the counters beside it, and it needs no setting, no extension and no restart. It resets when the table’s counters reset, which means a scheduled reset costs you the history here exactly as it does everywhere else.

The one thing to change in a collection layer is the arithmetic. If a dashboard computes moved updates by subtraction, the derived number should be replaced rather than kept alongside, because the two will disagree and the subtraction is the one that is wrong. On a fleet with clusters on both sides of 16 the honest arrangement is to collect the measured column where it exists and to collect nothing where it does not, rather than to fill the gap with an estimate that reads like a measurement.