Index Selection, Write Amplification, Deduplication, Reindexing, and Portfolio Audits

Learning outcomes

After four lessons, ServiceHub can create many kinds of indexes. The production skill is deciding which ones deserve to exist. Every secondary index is another physical relation that consumes storage, cache, WAL, checkpoint/vacuum attention, and write work. It can also reduce Heap-Only Tuple (HOT) opportunities when updated columns participate in indexes. An index portfolio therefore needs an evidence-backed keep/drop/add/rebuild log—not a collection of “maybe useful” structures.

1. Create an intentionally over-indexed portfolio

DROP TABLE IF EXISTS app.ch10_portfolio;CREATE TABLE app.ch10_portfolio (    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    tenant_id integer NOT NULL,    external_id uuid NOT NULL,    status text NOT NULL,    category text NOT NULL,    updated_at timestamptz NOT NULL,    payload text NOT NULL);INSERT INTO app.ch10_portfolio(tenant_id, external_id, status, category, updated_at, payload)SELECT (g % 50)+1,       md5(g::text)::uuid,       CASE g % 5 WHEN 0 THEN 'done' WHEN 1 THEN 'queued' ELSE 'active' END,       'cat-' || (g % 20),       timestamptz '2026-01-01' + g * interval '10 seconds',       repeat(md5((g*17)::text), 3)FROM generate_series(1,200000) AS g;CREATE INDEX ch10_pf_status_idx ON app.ch10_portfolio(status);CREATE INDEX ch10_pf_status_tenant_idx ON app.ch10_portfolio(status, tenant_id);CREATE INDEX ch10_pf_tenant_status_idx ON app.ch10_portfolio(tenant_id, status);CREATE INDEX ch10_pf_category_idx ON app.ch10_portfolio(category);CREATE INDEX ch10_pf_updated_idx ON app.ch10_portfolio(updated_at);CREATE INDEX ch10_pf_external_btree_idx ON app.ch10_portfolio(external_id);ANALYZE app.ch10_portfolio;

Some of these indexes may overlap, but “same first column” is not proof of redundancy. Ordering, included columns, predicates, operator classes, uniqueness, and actual workload determine whether one can substitute for another.

2. Build a safe inventory before judging usage

SELECT i.indexrelid::regclass AS index_name,       am.amname AS access_method,       i.indisunique,       i.indisvalid,       i.indisready,       pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,       s.idx_scan,       s.idx_tup_read,       s.idx_tup_fetch,       pg_get_indexdef(i.indexrelid) AS definitionFROM pg_index AS iJOIN pg_class AS c ON c.oid = i.indexrelidJOIN pg_am AS am ON am.oid = c.relamLEFT JOIN pg_stat_user_indexes AS s ON s.indexrelid = i.indexrelidWHERE i.indrelid = 'app.ch10_portfolio'::regclassORDER BY pg_relation_size(i.indexrelid) DESC;

Usage counters are cumulative since their statistics reset context and are not a complete workload trace. A zero scan count can mean “unused,” “stats were reset yesterday,” “reads happen on a replica,” or “the index exists primarily for a constraint.”

SELECT conname, contype, conindid::regclass AS backing_indexFROM pg_constraintWHERE conrelid = 'app.ch10_portfolio'::regclass  AND conindid <> 0;

3. Every index is write amplification

When one heap row is inserted, PostgreSQL must insert corresponding entries into every relevant index. Updates can touch multiple indexes and can prevent HOT optimization if indexed columns change. More indexes therefore change write latency, WAL volume, dirty-page pressure, vacuum work, and storage.

BEGIN;EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)INSERT INTO app.ch10_portfolio    (tenant_id, external_id, status, category, updated_at, payload)SELECT 1,       md5(('new-'||g)::text)::uuid,       'active',       'cat-1',       clock_timestamp(),       repeat('x',96)FROM generate_series(1,5000) AS g;ROLLBACK;

EXPLAIN ANALYZE executed the INSERT. The transaction rollback makes this a repeatable lab. Record WAL records/bytes, buffers, and elapsed time as local observations; do not publish them as universal PostgreSQL overhead.

DROP INDEX app.ch10_pf_category_idx;DROP INDEX app.ch10_pf_status_idx;BEGIN;EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS)INSERT INTO app.ch10_portfolio    (tenant_id, external_id, status, category, updated_at, payload)SELECT 1,       md5(('new2-'||g)::text)::uuid,       'active',       'cat-1',       clock_timestamp(),       repeat('x',96)FROM generate_series(1,5000) AS g;ROLLBACK;

Run both tests under comparable cache/checkpoint conditions if you want meaningful local comparison. The exercise demonstrates the direction of cost, not a fixed percentage improvement.

4. B-tree deduplication: duplicates can share posting lists

PostgreSQL B-tree can deduplicate duplicate leaf keys by storing one key plus a posting list of heap TIDs. Deduplication is enabled by default where it is safe and is especially useful when many rows repeat the same indexed key, as with status or category. It is a physical compression of logically identical index keys, not SQL duplicate elimination.

CREATE INDEX ch10_pf_status_dedup_onON app.ch10_portfolio(status) WITH (deduplicate_items = on);CREATE INDEX ch10_pf_status_dedup_offON app.ch10_portfolio(status) WITH (deduplicate_items = off);SELECT relname,       pg_size_pretty(pg_relation_size(oid)) AS size,       reloptionsFROM pg_classWHERE oid IN (  'app.ch10_pf_status_dedup_on'::regclass,  'app.ch10_pf_status_dedup_off'::regclass);

Expect the deduplicated index often to be smaller on this duplicate-heavy dataset, but exact size depends on page packing, TIDs, build state, and server version. The lab uses a type/operator class where deduplication is safe.

5. Hash indexes: narrow equality tool, not generic replacement

PostgreSQL hash indexes are persistent and crash recoverable. They are single-column, cannot enforce uniqueness, and support only equality. They store hash values rather than the original key, so scans are lossy and heap rechecks are part of their semantics. For large equality-only lookups on long scalar values they can be compact, but B-tree usually has broader functionality and strong concurrency characteristics.

CREATE INDEX ch10_pf_external_hash_idxON app.ch10_portfolio USING hash(external_id);SELECT c.relname, am.amname,       pg_size_pretty(pg_relation_size(c.oid)) AS sizeFROM pg_class AS cJOIN pg_am AS am ON am.oid = c.relamWHERE c.oid IN (  'app.ch10_pf_external_btree_idx'::regclass,  'app.ch10_pf_external_hash_idx'::regclass);EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT idFROM app.ch10_portfolioWHERE external_id = md5('12345')::uuid;

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT external_idFROM app.ch10_portfolioWHERE external_id >= md5('10000')::uuidORDER BY external_idLIMIT 20;

The second query can use B-tree ordering/range semantics; the hash index cannot. Keep a hash index only when the actual equality workload and measured resource profile justify a second structure.

6. Redundant is not the same as similar

Consider (status, tenant_id) and (tenant_id, status). They contain the same columns but serve different leading-key access patterns and orderings. PostgreSQL 18 skip scan may make one somewhat useful for a later key, but that does not prove equivalence under your cardinalities. Conversely, a single-column status index can sometimes be redundant if every important status query is served better by the wider index—but only workload evidence can establish that.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT count(*) FROM app.ch10_portfolio WHERE tenant_id = 12;EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT count(*) FROM app.ch10_portfolio WHERE status = 'queued';EXPLAIN (ANALYZE, BUFFERS, SETTINGS)SELECT count(*) FROM app.ch10_portfolioWHERE tenant_id = 12 AND status = 'queued';

7. When REINDEX is evidence-based

REINDEX rebuilds an index. Appropriate reasons include corruption concerns documented by PostgreSQL diagnostics, severe/structurally inefficient index bloat, applying changed index storage parameters, or recovering an invalid concurrent-build artifact. It does not repair heap bloat and should not be a calendar ritual.

SELECT indexrelid::regclass AS index_name,       indisvalid, indisready, indisliveFROM pg_indexWHERE indrelid = 'app.ch10_portfolio'::regclass;SELECT pid, command, phase,       relid::regclass AS table_name,       index_relid::regclass AS index_name,       blocks_total, blocks_done,       tuples_total, tuples_doneFROM pg_stat_progress_create_index;

A normal REINDEX INDEX can block writers while it rebuilds. REINDEX INDEX CONCURRENTLY uses a multi-phase process with less write blocking, extra scans/waits, restrictions, and more total work. It cannot run inside a transaction block.

REINDEX INDEX CONCURRENTLY app.ch10_pf_external_btree_idx;

8. Produce a portfolio decision log

Do not finish an index review with a pile of SQL commands. Record the decision and evidence so the next operator understands why an index exists or disappeared.

9. Production judgment and cleanup

An index portfolio is healthy when each structure has a documented operator/workload/correctness purpose and its read benefit exceeds its write/storage/maintenance cost. Use plan evidence, statistics context, index size, constraint dependencies, WAL-safe experiments, and change-control logs. Reindex only for a diagnosed index problem; drop only after proving substitution and rollback safety.

DROP TABLE IF EXISTS app.ch10_portfolio;

Authoritative references

Index behavior depends on PostgreSQL major version, operator class, collation, statistics, data distribution, visibility, and workload. Use the documentation for the exact major you operate.

• PostgreSQL 18 — Indexes / Examining Usage
• PostgreSQL 18 — B-Tree Deduplication
• PostgreSQL 18 — Hash Indexes
• PostgreSQL 18 — REINDEX
• PostgreSQL 18 — Routine Reindexing
• PostgreSQL 18 — EXPLAIN