When NOT to Use Indexes

The hidden cost: every write touches every index

An index isn't a free lookup table that sits quietly until queried — it's a live structure the database must keep in sync with the table at all times. Every INSERT adds an entry to every index on that table. Every UPDATE to an indexed column removes the old entry and inserts a new one. Every DELETE removes an entry from every index. A table with one index pays one extra write per row change; a table with eight indexes pays eight.

1. Low-selectivity columns

An index on a boolean column like is_active, or a status column with only three or four possible values, rarely helps. If true matches 80% of the table, the optimizer will almost always prefer a table scan over the index for that filter — covered in detail in Index Scan vs Table Scan — which means the index sits there being maintained on every write while never being chosen for a read.

-- Rarely worth it on its own: only 3 distinct values, one covers 80% of rows
CREATE INDEX idx_orders_status ON orders (status);

-- Usually more useful: status becomes selective once paired with customer_id
CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);

2. Small tables

If a table's entire contents fit in one or a few storage pages — a lookup table of 40 countries, a table of 12 subscription plans — a full scan already reads almost nothing. The B-tree traversal an index performs (see how a B-tree lookup works) has its own fixed overhead, and on a table this small that overhead can exceed the cost of just reading everything directly.

3. Redundant and overlapping indexes

A composite index already serves any query filtering on its leftmost columns (the leftmost-prefix rule from Indexes Explained With Real Examples). A separate single-column index on that same leftmost column adds nothing for reads, but still pays the full write cost of its own maintenance.

CREATE INDEX idx_orders_cust_status_date
  ON orders (customer_id, status, order_date);

-- Redundant: idx_orders_cust_status_date already serves
-- any query filtering on customer_id alone
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

4. High-churn columns

Indexing a column that changes on nearly every write — a last_seen_at timestamp updated on every user request, or a live view_count — means the index entry for that row is being torn down and rebuilt constantly. Unless queries genuinely need to filter or sort on that exact column, indexing it turns a routine update into extra B-tree maintenance work on every single change.

5. Write-heavy tables with few reads

Some tables exist mostly to record events — audit logs, raw ingestion tables, message queues — and are rarely queried by anything other than a recent time window or a primary key. Indexing every column "just in case" on a table like this adds write overhead to a workload that is, by nature, almost entirely writes, for reads that may never happen.

Before/after: what redundant indexes cost writes

Same orders table, same single-row INSERT statement — the only difference is how many indexes exist on the table.

INSERT INTO orders
  (customer_id, status, total, order_date)
VALUES (4471, 'pending', 89.00, now());

Indexes updated: orders_pkey,
  idx_orders_cust_status_date

INSERT INTO orders
  (customer_id, status, total, order_date)
VALUES (4471, 'pending', 89.00, now());

Indexes updated: orders_pkey,
  idx_orders_cust_status_date,
  idx_orders_customer_id (redundant),
  idx_orders_status (low selectivity),
  + 4 more single-column indexes

~7.3x slower per insert six extra indexes, most of them redundant or low-selectivity, added directly to every single write