VACUUM remains one of PostgreSQL's most consequential maintenance operations, and successive releases have reshaped both it and autovacuum to cope with heavy write loads. One such change lets VACUUM skip the index pass and work only on heap tuples, trading index maintenance for time. The behavior is governed by the INDEX_CLEANUP ON|OFF|AUTO option, added in PostgreSQL 12 and refined in PostgreSQL 14 so that the skip decision can be made automatically when appropriate. A relation storage parameter provides the same control at table level.

Line pointers and tuple lifetime

Dead tuples surface as confusing monitoring data because of how PostgreSQL stores rows. Pages hold tuples and are 8kB unless built otherwise. Tuples are not addressed by physical offset inside a page; instead, an array of line pointers at the start of the page, just after the page header, points to them. Each tuple carries a flag describing its purpose and lifetime:

/*
 * lp_flags has these possible states.  An UNUSED line pointer is available
 * for immediate re-use, the other states are not.
 */
#define LP_UNUSED       0       /* unused (should always have lp_len=0) */
#define LP_NORMAL       1       /* used (should always have lp_len>0) */
#define LP_REDIRECT     2       /* HOT redirect (should have lp_len=0) */
#define LP_DEAD         3       /* dead, may or may not have storage */

LP_NORMAL means the line pointer is in use and references a valid tuple. LP_UNUSED means the slot can be reused immediately by a new tuple. LP_DEAD is subtler: the pointer may no longer reference a usable tuple — say, no space is occupied — yet it is still required and cannot be reused right away. LP_REDIRECT marks the head of a Heap Only Tuple (HOT) chain.

The pointer array grows from the front of the page up to MaxHeapTuplesPerPage, a platform-dependent limit, while tuples are written from the end, leaving reserved space. A line pointer stores its flags in the lp_flag member.

Why dead tuples reappear

The anomalies that prompted a recent support question trace back to the skip-index optimization interacting with manual VACUUM (ANALYZE). Since PostgreSQL 14, (auto)VACUUM can decide on its own to skip indexes; the source code applies this formula:

/*
 * Threshold that controls whether we bypass index vacuuming and heap
 * vacuuming as an optimization
 */
#define BYPASS_THRESHOLD_PAGES  0.02    /* i.e. 2% of rel_pages */

[...]

threshold = (double) vacrel->rel_pages * BYPASS_THRESHOLD_PAGES;
   bypass = (vacrel->lpdead_item_pages dead_items) < (32L * 1024L * 1024L)));

Put plainly: when no more than 2 percent of tuples are LP_DEAD and TID storage stays under 32 MB, (auto)VACUUM skips the indexes. Skipping saves work proportional to index count and size, but it prevents VACUUM from killing dead heap tuples outright, because index entries still reference them. Only the line pointer must remain. VACUUM therefore marks those pointers LP_DEAD — in use, considered dead, likely to be freed soon. This happens during ordinary VACUUM runs too, but in the second phase of a run those pointers are ultimately set to LP_UNUSED.

The metric discrepancy arises when VACUUM is paired with ANALYZE, or when ANALYZE runs some time after a VACUUM that skipped index cleanup. ANALYZE still counts LP_DEAD-marked tuples as dead rows, so they can appear to return in system metrics.

Reproducing the effect

A small test table demonstrates the behavior. Create the table and populate it:

CREATE TABLE test(id bigint primary key);

INSERT INTO test SELECT t.id FROM generate_series(1, 100000) AS t(id);

autovacuum runs within autovacuum_naptime, so wait for it to complete its initial pass:

=# \x
=# SELECT last_autovacuum FROM pg_stat_user_tables WHERE relname = 'test';
─[ RECORD 1 ]───┬──────────────────────────────
last_autovacuum │ 2025-04-24 15:08:06.541069+02

The statistics in pg_stat_user_tables now read:

=# SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'test';
─[ RECORD 1 ]──────
n_live_tup │ 100000
n_dead_tup │ 0

Next, delete a batch of tuples, keeping the count below the 2 percent threshold. Because autovacuum_vacuum_scale_factor defaults to 0.2, autovacuum will not trigger again:

=# DELETE FROM test WHERE id BETWEEN 1000 AND 1999;
DELETE 1000

=# SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'test';
─[ RECORD 1 ]─────
n_live_tup │ 99000
n_dead_tup │ 1000

The table now shows 1000 dead tuples. Run an explicit VACUUM; with the primary key on the id column, the index should be skipped. VERBOSE prints the decisive line:

=# VACUUM (VERBOSE) test;
[... many lines of output ... ]
index scan bypassed: 5 pages from table (1.13% of total) have 1000 dead item identifiers
[...]

Check the statistics again:

SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'test';
─[ RECORD 1 ]─────
n_live_tup │ 98885
n_dead_tup │ 0

That matches expectations. Now run ANALYZE to refresh optimizer statistics:

=# ANALYZE (VERBOSE) test;
INFO:  analyzing "bernd.test"
INFO:  "test": scanned 443 of 443 pages, containing 99000 live rows and 1000 dead rows; 30000 rows in sample, 99000 estimated total rows
ANALYZE
Time: 26,105 ms

=# SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'test';
─[ RECORD 1 ]─────
n_live_tup │ 99000
n_dead_tup │ 1000

Nothing else changed, yet the dead tuples show up again. ANALYZE (VERBOSE) also reports 1000 dead tuples. VACUUM could not free them because indexes still address the line pointers in the pages. ANALYZE recognizes them nonetheless, since they matter for page-level data distribution and thus for cost estimation. Once the dead tuple count crosses the index cleanup threshold, VACUUM frees those line pointers for reuse.

Controlling the optimization

Since PostgreSQL 14, VACUUM and autovacuum may skip index cleanup when dead tuples fall below the threshold, deferring a costly operation so work can be batched. The side effect is statistics that look like resurrected dead tuples in views such as pg_stat_user_tables. DBAs who find this suboptimal can force index cleanup with INDEX_CLEANUP ON on a manual VACUUM, or disable the optimization at table level:

=# ALTER TABLE test SET (VACUUM_INDEX_CLEANUP = ON);
ALTER TABLE

Review such a change carefully. Disabling the optimization makes VACUUM perform work it could otherwise skip, which can hurt on very large tables under sustained write traffic.