A partial index filters rows, not columns. An index on id WHERE status = 'open' stores identifiers for open rows; status decides membership without becoming the search key. That distinction matters when estimating maintenance: a column need not appear in the stored key to affect an update’s cost.

For a table dominated by closed orders, excluding that history can save substantial index space. It does not make closed rows free to write, guarantee faster queries, or remove the table’s vacuum work. Evaluate the smaller structure alongside the writes that keep it correct.

Open rows 100 and 200 contribute id keys while closed row 101 does not. Entering the open subset creates an entry; leaving changes visible membership before later cleanup. Changing an open row id still needs a new entry. Closed to archived changes status and blocks HOT despite unchanged membership. A note-only update may qualify only if no index depends on note and the same heap page has room. Source fixture counters progress from one update and one HOT update to two updates and one HOT update.
Membership answers which rows belong. Write cost also depends on changed keys, predicate-column dependencies and same-page HOT eligibility.

Separate membership from update cost

An inserted row outside the predicate needs no entry in this particular index. Predicate evaluation, table storage, other indexes, and ordinary transaction work remain. Moving a row into the subset creates an entry. Moving it out changes visible membership; obsolete row versions and index entries are reclaimed later, rather than necessarily disappearing physically at that moment.

Keeping membership unchanged is not enough to skip maintenance. An open order whose indexed identifier changes needs a new index entry. Even changing an unrelated note can require new entries when PostgreSQL cannot use a heap-only tuple, or HOT, update.

For PostgreSQL 18 B-tree indexes, HOT requires unchanged values in every column referenced by the table’s index definitions, including expressions and partial-index predicates, plus space for the new tuple on the same heap page. Changing status from closed to archived blocks HOT even though neither value satisfies this index. Changing only an unindexed note can qualify. Lower table fillfactor leaves room but is no guarantee; other indexes can independently block the optimization.

This rule is version- and index-method-sensitive. PostgreSQL 16 introduced an exception for summarizing indexes such as BRIN; those summaries may still need updating during HOT. That exception does not apply to the B-tree example here. Read the documentation for the deployed version before extrapolating update behavior.

Eligibility is separate from usefulness

The planner must prove that query conditions imply the partial predicate. An open-only index cannot by itself answer a request including closed orders. A generic plan for status = $1 lacks that proof; a custom plan can know the supplied value. Parameters elsewhere in a query with literal status = 'open' do not erase that fact.

An eligible index still competes on cost. A sequential scan can be sensible for a small table or broad result. Inspect estimates, actual rows, and buffers before blaming predicate spelling. A test that always demands one index name confuses a performance hypothesis with a correctness requirement.

When it breaks

A growing backlog can make the open subset large; frequent status changes can also erase expected write savings. Compare representative reads and transitions, rather than extrapolating from index size alone. Many overlapping partial indexes add planning and maintenance work. Review the whole index set before adding another slice or removing an apparently unused index, especially one enforcing uniqueness.

Check it quickly

Run both blocks in one fresh psql -X -v ON_ERROR_STOP=1 session on a disposable PostgreSQL 18 database with track_counts enabled. Historical review records report SQL checks on PostgreSQL 18.6 in socket-free single-user mode; the client/server connection path was not tested. The temporary table contains 20,000 rows, with 200 open; explicit names avoid guessing PostgreSQL’s generated index names.

BEGIN;
CREATE TEMP TABLE partial_cost_demo (
  id integer NOT NULL,
  status text NOT NULL,
  note integer NOT NULL
) WITH (fillfactor=50);
INSERT INTO partial_cost_demo
SELECT g, CASE WHEN g % 100 = 0 THEN 'open' ELSE 'closed' END, 0
FROM generate_series(1,20000) AS g;
CREATE INDEX cost_all_id ON partial_cost_demo (id);
CREATE INDEX cost_open_id ON partial_cost_demo (id) WHERE status='open';
ANALYZE partial_cost_demo;
SELECT pg_relation_size('cost_all_id') AS full_bytes,
       pg_relation_size('cost_open_id') AS partial_bytes;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM partial_cost_demo WHERE status='open' ORDER BY id LIMIT 5;
SELECT id FROM partial_cost_demo WHERE status='open' ORDER BY id LIMIT 5;

Expect identifiers 100 through 500 in steps of 100. The partial index should occupy fewer bytes, without an exact one-percent size promise: page overhead matters. Record the chosen plan rather than asserting its shape. The full index supplies a same-key size comparison and remains a competing access path.

UPDATE partial_cost_demo SET note=1 WHERE id=100;
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_xact_user_tables
WHERE relid='partial_cost_demo'::regclass;
UPDATE partial_cost_demo SET status='archived' WHERE id=101;
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_xact_user_tables
WHERE relid='partial_cost_demo'::regclass;
SELECT count(*) FROM partial_cost_demo WHERE status='open';
ROLLBACK;

The transaction-local counters update continuously: total updates progress from one to two. The note update may increase the HOT count; the status change should not, despite unchanged predicate membership. The isolated run recorded counters of (1, 1), then (2, 1), and 200 open rows. Index sizes were 466,944 and 16,384 bytes, respectively; the chosen index-only scan still made five heap fetches. These are fixture observations, not universal expectations. Rollback removed the fixture. This separates HOT eligibility from subset size; temporary-table timings do not establish production WAL savings or sustained write throughput.

Sources

Further reading