Make an index earn its place in a write-heavy table

Article · Measurement

A jobs table has a status column. Workers change it from pending to running to complete. Someone adds an index on status because a dashboard filters by status.

The dashboard query gets better. The workers now have more index work to do. Whether that trade is good depends on how often each operation runs and what the product needs.

I would ask for that comparison before keeping the index. “The query uses it” is evidence that it is usable. It does not tell us the cost of maintaining it.

Give the index a specific job

Write down the query it supports, how often it runs, and the latency it needs. A support report opened twice a day is different from a worker claim executed thousands of times per second.

Check the actual predicate and ordering. An index on status alone may be a poor match for a query that filters by tenant and orders by creation time. Data distribution matters too. A table where almost every row is complete behaves differently from an evenly divided test table.

Do not change three indexes and the query in the same experiment. Start with one candidate and a representative dataset.

Observe update work as well as reads

The existing HOT update experiment explains why changing an indexed column can change the work done by an update. PostgreSQL's HOT documentation also describes the page-space requirement and the exception for summarizing indexes.

For the practical decision, collect a read plan and a bounded update run with each layout. Here is a Rust SQLx query for current-transaction counters:

let counts: (i64, i64) = sqlx::query_as(
            "SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_xact_user_tables WHERE relid = 'jobs_index_trial'::regclass",
        ).fetch_one(&mut *tx).await?;

This is an observation excerpt. It observes the updates in the same transaction and uses the fixture's schema-qualified relation identity through regclass. The counters explain what happened to updates there. They do not directly measure elapsed time or prove an application speedup.

Keep the engine version, table contents, transaction pattern, and page layout settings with the result. A fresh table with generous free space may give a different HOT rate from a mature table.

Compare the whole workload

Measure the read that motivated the index and the writes that maintain it. Include tail latency when a slow claim or update harms users. Repeat the comparison and retain an unchanged control so ordinary variation is visible.

Then decide using the workload's frequency and requirements. A larger write cost may be acceptable for a critical read. A rare report may be better served by a different access path, a smaller reporting table, or a report that takes a little longer.

Partial indexes deserve the same care. They can reduce the indexed set, but changing a row into or out of that set still has maintenance implications. They are not free write performance.

I would keep the result beside the migration that adds the index. Include the query it exists for and the evidence used to choose it. That gives the next engineer a reason to preserve it, change it, or remove it when the workload changes. An index with a documented purpose is easier to manage than an index nobody dares to touch.

A small measured comparison

The complete Rust index trial creates ten thousand rows with an integer primary key, status equal to ID modulo one hundred, and a hundred-character payload. Fillfactor is seventy. Each sample uses a fresh fixture, runs VACUUM ANALYZE, and updates the first thousand statuses in one transaction.

The compared layouts differ by one ordinary B-tree status index. The supporting read counts rows with status three. The baseline plan uses a sequential scan; the indexed plan uses an index-only scan. Both find the expected hundred rows. The retained CI output includes the full plans and buffers.

These are observations from PostgreSQL 18.6 on one shared CI runner, not a production prediction. The order deliberately includes both layouts early and late. Statement timing includes the driver exchange; commit is measured separately. The large second commit sample remains visible instead of being discarded.

The status index adds 90,112 index bytes in this fresh layout and prevents HOT for the indexed-column changes. The baseline's 450 HOT updates also show why “no secondary index” does not imply every update becomes HOT: page space still matters. The small sample is evidence of this workload's trade, not an application throughput or tail-latency study.

Use the schema and retained observations to prepare your own representative comparison. A dashboard read can justify write work. The decision needs its frequency and service requirements, not just the most attractive plan or the smallest timing in this table.