Cut Index Bloat with PostgreSQL Partial Indexes

The problem: indexing rows you never query

On a high‑throughput events table, 95 % of rows are archived after a few days. A regular B‑tree on status still stores entries for every row, so each INSERT, UPDATE, or DELETE pays the full index‑maintenance cost. The index grows to millions of entries that the planner never touches because the application only ever asks for status = 'active'.

Why partial indexes solve it

A partial index adds a WHERE predicate to the index definition. Only rows that satisfy the predicate are entered into the B‑tree. This yields three concrete benefits:

• Smaller index — fewer index tuples mean less disk I/O and memory pressure.
• Lower write amplification — rows that don’t match the predicate skip index maintenance entirely.
• Targeted uniqueness — a partial unique index can enforce “one active subscription per user” without triggers.

The planner will use the partial index only when the query’s WHERE clause logically implies the index predicate. If the predicate is status = 'active', a query filtering on status = 'active' AND created_at > now() - interval '1 day' can use it; a query that only filters on created_at cannot.

Worked example: indexing only active orders

Assume a table orders with 1 million rows, 90 % archived (status = 'archived'). We want fast lookups for active orders.

-- 1. Create the table (run as a role with CREATE privilege on the schema)
CREATE TABLE orders (
    id          bigserial PRIMARY KEY,
    user_id     bigint NOT NULL,
    status      text   NOT NULL CHECK (status IN ('active','archived')),
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- 2. Populate with test data (run in psql or any client)
INSERT INTO orders (user_id, status, created_at)
SELECT
    (random()*10000)::bigint,
    CASE WHEN random() < 0.9 THEN 'archived' ELSE 'active' END,
    now() - (random()*interval '365 days')
FROM generate_series(1, 1000000);

-- 3. Build a partial index on active rows only
CREATE INDEX idx_orders_active_user ON orders (user_id)
WHERE status = 'active';

-- 4. Verify planner usage
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'active' AND user_id = 42;

Expected check: The EXPLAIN ANALYZE output should show an Index Scan using idx_orders_active_user with a low row estimate. Run pg_stat_clear_snapshot(); then execute the query a few times and query pg_stat_user_indexes — idx_scan on idx_orders_active_user should be > 0 while a comparable full index on user_id stays at 0.

Trade‑offs and limitations

• Immutable predicate required — the WHERE clause cannot reference volatile functions, CURRENT_DATE, or subqueries. CREATE INDEX … WHERE random() < 0.1 fails with “functions in index predicate must be marked IMMUTABLE”.
• No INCLUDE columns — PostgreSQL 11+ covering indexes (INCLUDE) cannot be combined with a partial predicate. All columns needed for index‑only scans must be key columns.
• Statistics matter — if the planner underestimates selectivity, it may ignore the partial index. Run ANALYZE orders; or create extended statistics with CREATE STATISTICS s_orders_status_user ON status, user_id FROM orders; to improve estimates.
• Dropping the predicate column — you must drop the partial index before ALTER TABLE orders DROP COLUMN status;; otherwise the command errors.

Actionable closing checklist

1. Identify a high‑cardinality column filtered by a stable, low‑selectivity value (e.g., status = 'active').
2. Create a partial index with CREATE INDEX … WHERE <predicate> using a role that has CREATE on the schema.
3. Run EXPLAIN ANALYZE on the target query; confirm an Index Scan on the new index.
4. Monitor pg_stat_user_indexes.idx_scan after a representative workload; drop the index if it stays at zero.
5. Schedule regular ANALYZE or extended statistics to keep planner estimates accurate.

Partial indexes are a mature, low‑risk lever (available since PostgreSQL 7.2) that can shrink index size by an order of magnitude and cut write latency on skewed workloads — provided the predicate is immutable and the planner’s statistics reflect reality.