Indexes in practice

Indexes in practice is the craft of choosing, building and pruning indexes on a live production database: measuring which query really needs help, picking the narrowest index that fixes it, adding it without blocking writes, and later removing the ones that only cost you. If B+trees are new to you, read Database indexes first; this article assumes you know how lookups, range scans and clustered versus heap tables work.

Picture it first: the prep shelf by the grill

A cloud kitchen runs dinner service out of a big walk-in fridge. When paneer tikka orders back up, a good head chef doesn’t reorganise the fridge. They watch the pass to see which dishes run late, follow one cook to see why (a trip to the back of the fridge for every order), and put a small shelf by the grill holding just tonight’s marinated paneer, built during service without stopping the line. Every Sunday they clear away shelves nobody used, because each one must be restocked whenever a delivery arrives.

Map it back:

• Watching the pass is pg_stat_statements: which queries cost the most in total.
• Following one cook is EXPLAIN (ANALYZE, BUFFERS): why this query is slow.
• The small shelf is a partial or expression index: only the rows and the shape the hot query needs.
• Building during service is CREATE INDEX CONCURRENTLY or online DDL.
• Restocking is the write cost of every index, and the Sunday clear-out is dropping unused ones.

Measure first: the tuning loop

Most bad indexes come from guessing. A loop that works:

1. Find the expensive query. Rank by total time (calls × mean), not the slowest single run. A 4 ms query at 2,000 calls/s costs 4 ms × 2,000 = 8 seconds of database time every second; a 30-second daily report barely registers.
2. Explain it with real numbers. EXPLAIN (ANALYZE, BUFFERS) runs the query and reports, per plan node, rows produced, rows discarded and pages (buffers) touched. Look for a big “Rows Removed by Filter”, a large Sort, or buffers far above the rows returned (query execution plans covers the details).
3. Form one hypothesis. “This query reads 900,000 pages to return 100 rows; an index matching its WHERE and ORDER BY should cut that to about 100.”
4. Build safely and verify both sides: re-run the same EXPLAIN, and check the write path too (insert latency, HOT-update ratio, WAL volume).

-- Top statements by total execution time.
-- pg_stat_statements must be in shared_preload_libraries (a restart is needed).
SELECT calls,
       round(total_exec_time / 1000) AS total_s,
       round(mean_exec_time, 1)      AS mean_ms,
       shared_blks_read,
       left(query, 70)               AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

A worked example: orders stuck in “pending”

A food-delivery platform keeps 50 million orders in PostgreSQL 17. A dispatcher job runs every 5 seconds to find orders a restaurant hasn’t accepted within 2 minutes and nudge the restaurant again. At the lunch peak about 50 orders arrive per second and a restaurant takes about 80 seconds on average to accept one, so by Little’s law about 50 × 80 = 4,000 orders are pending at any moment. The table has a primary key and indexes on customer_id, restaurant_id and delivery_partner_id.

CREATE TABLE orders (
  id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id         bigint      NOT NULL,
  restaurant_id       bigint      NOT NULL,
  delivery_partner_id bigint,
  status              text        NOT NULL,  -- pending, accepted, preparing, picked_up, delivered, cancelled
  total_paise         integer     NOT NULL,
  eta_minutes         smallint,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);

-- The dispatcher's query
SELECT id, restaurant_id, created_at
FROM orders
WHERE status = 'pending'
  AND created_at < now() - interval '2 minutes'
ORDER BY created_at
LIMIT 100;

It tops pg_stat_statements: 17,280 calls a day (one every 5 seconds) × 4.1 s ≈ 70,800 s, about 19.7 hours of database time per day. The plan shows why:

EXPLAIN (ANALYZE, BUFFERS) ...   -- PostgreSQL 17, before (worker detail trimmed)
Limit  (cost=1236721.38..1236733.05 rows=100 width=24) (actual time=4086.311..4102.477 rows=100 loops=1)
  Buffers: shared hit=2304 read=906784
  ->  Gather Merge  (cost=1236721.38..1236749.40 rows=240 width=24) (actual time=4086.309..4102.462 rows=100 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=2304 read=906784
        ->  Sort  (cost=1235721.36..1235721.61 rows=100 width=24) (actual time=4071.902..4071.915 rows=80 loops=3)
              Sort Key: created_at
              Sort Method: quicksort  Memory: 30kB
              Buffers: shared hit=2304 read=906784
              ->  Parallel Seq Scan on orders  (cost=0.00..1235718.33 rows=100 width=24) (actual time=9.114..4071.334 rows=80 loops=3)
                    Filter: ((status = 'pending'::text) AND (created_at < (now() - '00:02:00'::interval)))
                    Rows Removed by Filter: 16666586
                    Buffers: shared hit=2304 read=906784
Planning Time: 0.233 ms
Execution Time: 4102.611 ms

Read it bottom-up. The parallel sequential scan touched 2,304 + 906,784 = 909,088 pages, about 7 GB. Three processes (two workers plus the leader, hence loops=3) each discarded about 16.7 million rows to keep 80, so 240 useful rows came out of 50 million. The Sort is cheap; the scan is the problem.

Hypothesis: the query only ever wants pending rows, ordered by created_at. A partial index on created_at covering only pending rows holds about 4,000 live entries, already in the order the query needs.

CREATE INDEX CONCURRENTLY orders_pending_created_idx
    ON orders (created_at)
    WHERE status = 'pending';

EXPLAIN (ANALYZE, BUFFERS) ...   -- after
Limit  (cost=0.29..36.42 rows=100 width=24) (actual time=0.028..0.371 rows=100 loops=1)
  Buffers: shared hit=118
  ->  Index Scan using orders_pending_created_idx on orders  (cost=0.29..87.01 rows=240 width=24) (actual time=0.027..0.358 rows=100 loops=1)
        Index Cond: (created_at < (now() - '00:02:00'::interval))
        Buffers: shared hit=118
Planning Time: 0.191 ms
Execution Time: 0.392 ms

The status test vanished because the index predicate guarantees it, and there is no Sort because the index returns rows in created_at order. 909,088 → 118 buffers is about 7,700× fewer pages; 4,102 ms → 0.39 ms is about 10,000× less time. The daily cost falls from 19.7 hours to 17,280 × 0.39 ms ≈ 7 seconds. (On PostgreSQL 18, EXPLAIN ANALYZE includes buffer counts by default and prints row counts with decimals, such as rows=100.00.)

Now verify the write side, because this fix has a catch. Before, no index referenced status, so a status change could be a HOT update that writes no index entries (if the page had room). Now status sits in an index predicate, which counts as an indexed column: every status change becomes a non-HOT update and adds an entry to each of the other four indexes. Each order changes status four more times (accepted, preparing, picked up, delivered): 50 orders/s × 4 = 200 updates/s, × 4 indexes = 800 extra index entries per second. That is affordable here, but the same mistake on a table with ten indexes and thousands of updates a second becomes its biggest write cost.

Compare n_tup_hot_upd (below) before and after. If the write budget can’t absorb it, keep status out of every index on orders: a small pending_orders table (insert when an order is placed, delete when it is accepted) gives the dispatcher the same tiny index without costing the big table its HOT updates.

Partial indexes: index only the rows you query

A partial index has a WHERE clause and contains entries only for rows that satisfy it. It shines when queries target a small, stable subset of a big table:

• A hot state: pending orders, unbilled invoices, unread notifications.
• Soft deletes: WHERE deleted_at IS NULL, so lookups skip years of deleted rows.
• Uniqueness within a subset: at most one active hold per seat, however many cancelled ones exist.

-- At most one active hold per seat; cancelled and expired holds don't count.
CREATE UNIQUE INDEX seat_holds_one_active
    ON seat_holds (show_id, seat_no)
    WHERE status = 'active';

The rules that trip people up:

• The planner must prove, at planning time, that the query implies the predicate. It manages simple inequalities (x < 1 implies x < 2); otherwise the predicate must appear in the query essentially as written.
• So keep the constant in the SQL text. WHERE status = 'pending' can use the index; WHERE status = $1 cannot, because the planner can’t prove $1 is always 'pending'. Watch for ORMs that parameterize every literal.
• Predicate columns block HOT updates, exactly like key columns, as the worked example showed.
• Don’t fake partitioning with dozens of non-overlapping partial indexes, as the PostgreSQL documentation warns; use table partitioning.

The size difference is dramatic. A plain B-tree entry on a timestamptz costs about 20 bytes (an 8-byte header, the 8-byte key and a 4-byte slot), and PostgreSQL fills new B-tree leaves to 90% by default:

• Index on (created_at), all 50M rows: 50M × 20 B = 1.0 GB, ≈ 1.1 GB with free space.
• Index on (status, created_at): the short text key and alignment padding push an entry to about 36 bytes, so 50M × 36 B ≈ 1.8 GB, ≈ 2 GB.
• Partial index, live entries only: 4,000 × 20 B = 80 KB, ≈ 100 KB with page overhead.

There is churn, though. Every order is born pending, so the partial index still takes one insert per order (50/s), and each entry goes dead when its order moves on. Dead entries linger until vacuum or an opportunistic cleanup removes them: with 10 minutes between vacuums, at most 50 × 600 × 20 B ≈ 0.6 MB, still about 1,800× smaller than the full index.

Expression indexes and the hidden-function trap

An index helps only when the query compares the indexed value itself. Wrap the column in a function, a cast or arithmetic, and the order the index provides no longer matches the question.

-- The index is on email; the query asks about lower(email). Full scan.
CREATE INDEX users_email_idx ON users (email);
SELECT id FROM users WHERE lower(email) = 'asha@example.in';

-- Fix A: index the exact expression the query uses.
CREATE INDEX CONCURRENTLY users_email_lower_idx ON users (lower(email));

-- The cast hides created_at from its index ...
SELECT count(*) FROM orders WHERE created_at::date = DATE '2026-10-27';
-- ... Fix B: rewrite as a range on the bare column. Same rows, index-friendly.
SELECT count(*) FROM orders
WHERE created_at >= '2026-10-27' AND created_at < '2026-10-28';

Prefer the rewrite: it reuses an index you already pay for. Otherwise an expression index works, at a price: PostgreSQL computes the expression on every insert and non-HOT update, queries must use exactly the same expression, and its columns now count as indexed for HOT. A unique expression index doubles as a rule: UNIQUE on lower(email) stops Asha@x.in and asha@x.in from coexisting. MySQL supports the same idea as functional key parts from 8.0.13: CREATE INDEX users_email_lower ON users ((LOWER(email)));.

Implicit casts: a function you didn’t write

The sneakiest version is a type mismatch, because the query looks right:

-- MySQL. phone is VARCHAR(15) and indexed.
SELECT * FROM users WHERE phone = 9876543210;    -- number: index can't be used
SELECT * FROM users WHERE phone = '9876543210';  -- string: index lookup

MySQL’s documentation is explicit: comparing a string column with a number can’t use the column’s index, because many strings convert to the same number ('1', ' 1' and '1a' all equal 1). The usual culprits are codes stored as text but sent as integers: phone numbers, PIN codes, external order IDs from a JSON payload.

GIN and BRIN: indexes for other shapes

Substring search with trigrams

A B-tree handles LIKE 'pan%' as a range scan, but LIKE '%paneer%' has no starting point in a sorted list. The pg_trgm extension splits text into trigrams, overlapping three-character pieces (“cat” becomes “ c”, “ ca”, “cat” and “at ”), and its GIN operator class indexes them. A pattern search then only checks rows that contain all of the pattern’s trigrams, so LIKE, ILIKE and regular expressions can use an index with wildcards anywhere.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY dishes_name_trgm_idx ON dishes USING GIN (name gin_trgm_ops);
SELECT id, name FROM dishes WHERE name ILIKE '%paneer%';  -- can use the trigram index

You pay in size and write cost: every insert adds an entry per trigram. For ranking and typo tolerance at scale, use a search engine (see how search engines work).

JSONB and arrays

GIN is an inverted index: one entry per element inside a value. That fits JSONB documents and arrays.

-- Containment queries on restaurant attributes
CREATE INDEX CONCURRENTLY restaurants_attrs_idx ON restaurants USING GIN (attrs jsonb_path_ops);
SELECT id FROM restaurants WHERE attrs @> '{"cuisine": "south_indian", "veg_only": true}';

The default jsonb_ops class supports containment (@>), key existence (?, ?|, ?&) and jsonpath matches (@?, @@); jsonb_path_ops drops the key-existence operators. Either way the operator must apply to the indexed column itself: attrs -> 'tags' ? 'jain' needs a GIN index on the expression (attrs -> 'tags').

GIN’s weakness is writes: one row can produce dozens of entries. PostgreSQL parks new entries in a pending list and merges them in bulk later, which makes inserts cheaper; but searches must scan the list too, and the insert that overflows gin_pending_list_limit pays for the cleanup.

BRIN for append-only time series

Take rider GPS pings: 2 billion rows of about 100 bytes, appended in time order. At about 80 rows per 8 KB page that is 25 million pages, 200 GB. A BRIN index stores only the minimum and maximum recorded_at for each block range, 128 pages (1 MB of table) by default.

CREATE INDEX CONCURRENTLY rider_pings_time_brin ON rider_pings USING BRIN (recorded_at);

• BRIN entries: 25M pages ÷ 128 ≈ 195,000 ranges; at roughly 32 bytes each, about 6 MB.
• A B-tree on recorded_at: 2B × 20 B = 40 GB, about 44 GB with free space. That is about 7,000 times bigger.
• One hour of data: 2B rows a year is about 228,000 rows an hour, or about 2,850 pages. BRIN finds about 23 matching ranges and reads about 2,900 table pages; the B-tree reads about 630 leaf pages plus the same 2,850 table pages. Similar I/O for a 7,000× smaller index.

The catch: BRIN only works while physical order tracks the column. Late-arriving data, backfills or rows that move widen every range’s min–max, and the index degrades towards a full scan. It is lossy, so every row in a matching range is rechecked, and ranges filled since the last summarization aren’t indexed until vacuum runs, autosummarize is on, or you call brin_summarize_new_values(). In its favour, BRIN is a summarizing index, so it doesn’t block HOT updates.

Special index types compared

Building indexes on live tables

A plain CREATE INDEX in PostgreSQL scans the table once while blocking every INSERT, UPDATE and DELETE on it; reads continue. On a large table that can mean hours of failed writes.

CREATE INDEX CONCURRENTLY keeps writes flowing by doing more work:

1. In one transaction, it adds the index to the catalog marked invalid.
2. It waits for transactions that have modified the table to finish, then scans the table and builds the index (a second transaction).
3. It waits again, then scans a second time to add rows that changed during the first scan (a third transaction).
4. It waits for every transaction with an older snapshot to finish, then marks the index valid.

What you pay: more total I/O and CPU, a much longer run, and a dependency on long-running transactions, since every wait step blocks until they finish. It can’t run inside a transaction block, so migration tools that wrap migrations in transactions must run it separately. Only one concurrent build runs per table at a time; for a partitioned table, build each partition’s index concurrently, then create the parent index, a metadata-only step.

If anything fails part-way (a deadlock, or a duplicate key while building a unique index), the command stops but leaves an INVALID index behind. Queries ignore it, yet every write still maintains it. Find and drop such leftovers, then retry:

SELECT indexrelid::regclass AS invalid_index FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY orders_pending_created_idx;   -- then run CREATE INDEX CONCURRENTLY again

MySQL: online DDL and gh-ost

In InnoDB, adding a secondary index is an in-place operation that permits concurrent reads and writes. Ask for that explicitly, so MySQL refuses rather than quietly falling back to a blocking method:

ALTER TABLE orders
  ADD INDEX idx_restaurant_created (restaurant_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

Two operational catches remain. The statement still needs an exclusive metadata lock, briefly while it runs and always at the end, so one idle transaction holding a lock on the table can stall it, and new queries on the table then queue behind the waiting ALTER. And on replicas the DDL starts only after it has finished on the source, so a two-hour build becomes two hours of replication lag.

For the biggest tables, many teams use gh-ost, GitHub’s online schema migration tool. It creates an empty ghost table with the new schema, copies rows across in small chunks, replays ongoing changes from the binary log (no triggers), and swaps the tables at the end. You gain control: it truly pauses or throttles when the primary is busy, and the cut-over can wait until someone is watching. You pay by copying the whole table, far more I/O and temporary disk than an in-place build.

gh-ost --host=replica-01 --database=food --table=orders \
  --alter="ADD INDEX idx_restaurant_created (restaurant_id, created_at)" \
  --postpone-cut-over-flag-file=/var/lib/gh-ost/orders.postpone \
  --execute

The broader playbook (expand and contract, backfills, rollbacks) is in online schema and data migrations.

Pruning: unused, duplicate and bloated indexes

Unused indexes

PostgreSQL counts how often each index is scanned:

SELECT s.relname      AS table_name,
       s.indexrelname AS index_name,
       s.idx_scan,
       s.last_idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique     -- unique indexes enforce constraints even if never scanned
ORDER BY pg_relation_size(s.indexrelid) DESC;

Before dropping anything on the strength of idx_scan = 0, check four things:

• Replicas keep their own statistics. Each server counts the scans it runs. An index used only by reporting queries on a read replica shows zero on the primary, yet dropping it on the primary removes it from a physical replica too. Run the query on every replica.
• Counters reset after a crash, a point-in-time recovery or a start from a base backup. Make sure your window covers month-end and quarterly jobs.
• Constraints don’t scan. Primary-key and unique indexes protect correctness with zero scans.
• Drop gently. Use DROP INDEX CONCURRENTLY. In MySQL, first make the index invisible (ALTER TABLE orders ALTER INDEX idx_x INVISIBLE): the optimizer ignores it but writes still maintain it, so switching it back is instant if something slows down. MySQL’s sys.schema_unused_indexes view lists candidates, and is only meaningful after the server has run a representative workload.

Duplicate and redundant indexes

• Exact duplicates: the same columns in the same order. The classic is an explicit index on a column that already has a UNIQUE constraint, which created its own index.
• Left-prefix redundancy: an index on (customer_id) when (customer_id, created_at) exists, since the composite serves both queries (see composite index design). Keep the narrow one only if it is unique or measurably faster for a very hot query.
• MySQL’s sys.schema_redundant_indexes names the dominant index for each redundant one and even generates the DROP statement.

Bloat and REINDEX CONCURRENTLY

Indexes on heavily updated or deleted data accumulate dead space. Vacuum makes dead entries’ space reusable, but an index that ballooned during a burst of churn rarely shrinks by itself. The pgstattuple extension’s pgstatindex() reports avg_leaf_density and leaf_fragmentation; a large index with a low density is a candidate for a rebuild. Since PostgreSQL 12, REINDEX INDEX CONCURRENTLY builds a fresh copy alongside the old one, swaps them and drops the old, without blocking writes. Like a concurrent build, a failure leaves an invalid new index to drop. The table-side story is in vacuum and bloat.

Write amplification and HOT updates

PostgreSQL’s MVCC never overwrites a row in place: an UPDATE writes a new row version. If that version lands somewhere new, every index on the table needs a new entry pointing at it, including indexes whose columns didn’t change. The heap-only tuple (HOT) optimization avoids those index writes when two conditions hold:

1. The update changes no column referenced by any index (key columns, INCLUDE columns, expression and predicate columns). Summarizing indexes such as BRIN don’t count.
2. The new version fits on the same heap page as the old one.

The indexes keep pointing at the original version, and the page links old to new. Put numbers on the difference for a busy table: say 8,000 updates/s and four full indexes. HOT: 8,000 heap-page writes. Non-HOT: the same 8,000 heap writes plus 8,000 × 4 = 32,000 index entries per second, each with its own WAL record, and four indexes that bloat until vacuum catches up.

Three levers keep updates HOT:

• Don’t index columns that churn: updated_at, last_seen_at, counters, and (as the worked example showed) predicate columns of partial indexes.
• Leave room on the page. Tables default to fillfactor 100, packing pages full. ALTER TABLE orders SET (fillfactor = 90); keeps 10% of each newly written page free for updated versions; you pay with a table about 11% bigger (100 ÷ 90).
• Measure the ratio.

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;

InnoDB works differently: it updates rows in place, keeps old versions in undo logs, and its secondary indexes point at the primary key, so changing a non-indexed column leaves them alone. The general lesson still holds: every index makes some writes more expensive.

Random UUIDs vs sequential keys

A UUIDv4 has 122 random bits, so each new key lands at a random position in the primary-key B-tree. That has three costs:

• The whole index is the working set. Every leaf page is equally likely to receive the next insert. Once the index outgrows memory, inserts turn into random reads and writes.
• Splits everywhere. A full leaf in the middle of the tree splits into two half-full pages. InnoDB’s documentation puts pages filled by random inserts anywhere from half to 15/16 full, against about 15/16 for sequential ones, so the same keys need more pages and more cache.
• InnoDB pays twice. The primary key is the table, so the rows themselves scatter. And every secondary index stores the 16-byte key (36 bytes if kept as text) instead of an 8-byte bigint.

RFC 9562, the 2024 UUID standard, names this directly: random UUIDs have poor database index locality, and the effect on B-trees “can be dramatic”. Its fix is UUIDv7: the most significant 48 bits are a Unix timestamp in milliseconds, and the remaining 74 bits (apart from the version and variant fields) are random or a counter. Keys generated later sort later, so inserts append at the right edge of the tree like a sequence, yet any service can mint them without coordination.

PostgreSQL 18 generates them natively with uuidv7(); elsewhere a library does the job. What you pay: the creation time is readable from the ID, so don’t use one where the timestamp is sensitive or the ID must be unguessable; and at very high insert rates every writer competes for the same rightmost leaf, a hotspot a random key never has. A bigint sequence is still the smallest and fastest key on one database; UUIDv7 earns its 16 bytes when IDs are minted across many services or shards (see design a unique ID generator).

“One more index”: war stories

These are illustrative composites of incidents common in production, not accounts of a specific company. The numbers are typical, not measured.

The boolean index. A users table gets an index on is_active, which is true for 98% of rows. Queries for active users ignore it (a sequential scan is cheaper for 98% of the table), queries for inactive users are rare, and every signup pays to maintain it. The fix: drop it, and if inactive users matter, add a partial index WHERE NOT is_active.

Fourteen indexes. Over three years, each team adds “just one index” for its own report. Peak insert latency creeps up, WAL grows and replicas lag. A 30-day idx_scan audit across the primary and every replica finds six unused indexes and two left-prefix duplicates; removing them one at a time brings insert latency back down.

The index the planner “ignores”. A dashboard filters on DATE(created_at) = CURDATE() and scans 80 million rows every refresh, despite an index on created_at. Rewriting it as a half-open range on the bare column takes it from seconds to milliseconds.

The index that broke HOT. An analytics job wants “rows changed since my last sync”, so someone indexes updated_at. Now every update changes an indexed column: the HOT ratio collapses from about 90% to nearly zero, every index bloats and WAL doubles. The fix: stream changes out with change data capture instead of polling an index.

Cheat sheet: a production index checklist

How to say it in the interview

“I don’t add indexes by instinct. I find the top queries by total time in pg_stat_statements, read EXPLAIN (ANALYZE, BUFFERS) to see how many pages they touch, and add the narrowest index that matches the WHERE and ORDER BY: often a partial index, like one on created_at for just the pending orders. I build it with CREATE INDEX CONCURRENTLY so writes keep flowing, then verify both sides: the plan, and insert latency and the HOT-update ratio, because indexing a frequently updated column makes those updates non-HOT, so each one writes to every index. I prune unused indexes regularly, checking replicas first, and for keys I use a sequence or UUIDv7 rather than random UUIDs, which scatter inserts across the whole B-tree.”

How do you add an index to a 500 GB table without downtime? In PostgreSQL, CREATE INDEX CONCURRENTLY: two table scans with waits in between, slower overall, but writes continue. Run it outside a transaction, make sure no long transaction is open, and check for an INVALID index if it fails. In MySQL, an in-place ALTER TABLE with LOCK=NONE, or gh-ost when you need throttling and a controlled cut-over; either way, plan for replica lag.

Why did writes slow down after you added one index? Every index adds work to every insert. Worse, in PostgreSQL, indexing a column that your updates change (even as a partial index’s predicate column) turns those HOT updates into non-HOT ones, each writing a new entry into every index. Compare n_tup_hot_upd before and after.

How do you know an index is safe to drop? idx_scan = 0 over a window that covers monthly jobs, checked on the primary and every replica, and it isn’t enforcing a primary key or unique constraint. Drop it concurrently, or make it invisible first in MySQL so you can bring it back instantly.

UUID or bigint for the primary key? On one database, a bigint sequence: 8 bytes, ordered, cheap. If IDs are minted by many services or shards, UUIDv7: time-ordered, so inserts append like a sequence, at the cost of 16 bytes and a visible timestamp. Avoid UUIDv4 as a clustered or primary key in a B-tree: random inserts touch every page.

Where to go next

• Database indexes: the fundamentals this article builds on, from B+tree height to clustered tables.
• Composite index design: column order, covering indexes and index-only scans.
• Query execution plans: every node type and statistic in the plans above.
• Index scan vs full table scan: why the planner sometimes, correctly, ignores your index.
• Vacuum and bloat: where dead tuples and dead index entries go.
• Online schema and data migrations: the wider playbook for changing live tables.
• MVCC: why PostgreSQL writes new row versions, the root of the HOT story.

References and further reading

1. PostgreSQL documentation: CREATE INDEXpostgresql.orgBuilding indexes concurrently (an invalid catalog entry, two table scans, waits, no transaction blocks, INVALID leftovers on failure), plus the B-tree fillfactor and BRIN pages_per_range defaults.
2. PostgreSQL documentation: Partial indexespostgresql.orgIndexing only a hot subset, the rule that the query must imply the predicate at planning time, parameterized queries, partial unique indexes, and not faking partitioning.
3. PostgreSQL documentation: Indexes on expressionspostgresql.orgIndexing lower(col), unique expression indexes, and the cost of computing the expression on every insert and non-HOT update.
4. PostgreSQL documentation: Heap-only tuples (HOT)postgresql.orgThe two conditions for a HOT update, the exception for summarizing indexes such as BRIN, and lowering fillfactor to make HOT more likely.
5. PostgreSQL documentation: BRIN indexespostgresql.orgBlock-range summaries, lossy bitmap scans, the pages_per_range size trade-off, and how new ranges get summarized.
6. PostgreSQL documentation: pg_trgmpostgresql.orgHow strings are split into trigrams, and the GIN and GiST operator classes that make LIKE, ILIKE and regular-expression searches indexable.
7. PostgreSQL documentation: The cumulative statistics systempostgresql.orgpg_stat_user_indexes.idx_scan and last_idx_scan, n_tup_hot_upd, per-server collection, and when counters are reset.
8. MySQL 8.0 Reference Manual: Online DDL operationsdev.mysql.comAdding and dropping InnoDB secondary indexes in place while concurrent reads and writes continue.
9. gh-ost: GitHub’s online schema migration tool for MySQLgithub.comTriggerless migrations driven by the binary log, with a ghost table, true throttling and pausing, and a postponable cut-over.
10. RFC 9562: Universally Unique IDentifiers (UUIDs)rfc-editor.orgWhy random UUIDs give poor database index locality, and the UUIDv7 layout with a 48-bit Unix millisecond timestamp in the most significant bits.