Index maintenance: count the work behind one logical update

Logical user payload, database page writes, write-ahead log bytes, filesystem writes, and device-internal writes describe different layers. Define a ratio using a stated denominator, such as logical changed payload bytes over a time interval, and a stated numerator, such as database-generated data and log bytes in that same interval. Include or exclude background cleanup explicitly. A page dirtied five times may be written once before eviction, so counting dirty events as if each were a complete physical page write overstates some workloads and misses other logging behavior.

Avoid adding overlapping counters. If a storage measurement already includes log writes, adding the separately measured log total double-counts them. Device-level amplification can include garbage collection inside flash storage that the database does not report. Conversely, a benchmark ending before dirty pages or deferred cleanup are flushed can hide later work. Compare equivalent durability, checkpoint, cache-warmth, and cleanup conditions. Ratios with a tiny logical-byte denominator can be large without implying an implementation bug; small updates naturally interact with page- and log-oriented mechanisms.

A logical row update can create a new physical row version. Indexes referencing physical versions may need new entries even when some indexed key values did not logically change. PostgreSQL's HOT optimization can avoid new entries in ordinary indexes under documented conditions, including suitable same-page space and restrictions on modified indexed columns. Included columns count as index dependencies for this decision. PostgreSQL 18 has a specific exception for summarizing indexes such as BRIN, so the blanket statement that any indexed-column update always forbids HOT is too broad.

Deleting a row does not always erase every old index entry immediately: active snapshots and recovery rules constrain reclamation. Vacuum, bottom-up index cleanup, deduplication where supported, and page recycling can reduce obsolete versions or avoid splits. These mechanisms affect different stages and do not guarantee the file instantly shrinks. A rebuild can produce a compact structure while temporarily consuming extra storage and I/O. Concurrent rebuild or construction has its own locking, validation, and failure behavior; selecting the concurrent option does not make the operation free of resource or availability consequences.

For each proposed index, identify the query family it accelerates and measure how often that family occurs. Compare the saved scan, sort, and row-fetch work with extra writes, storage, cache pressure, and recovery volume. An apparently redundant index may still enforce a required uniqueness constraint or serve a distinct ordering; removing it requires understanding those semantics. Conversely, several wide indexes added for rare reports can penalize every frequent update. Test representative bursts and skew, not only steady read-only queries after a convenient bulk load.

The queue lab illustrates an explicit maintenance-rate budget. Reset, set λ=4 and μ=3, predict one additional queued work unit per second, then Play or Step. Separately Reset, set λ=2 and μ=3, predict the restored four-unit initial backlog drains in four seconds, then Play or Step. The instrument has no indexes, WAL, vacuum, or storage device. Associate each work unit with a clearly stated synthetic maintenance quantity and explain why an ever-growing queue cannot be fixed simply by deferring the same required work indefinitely.