The Complete Guide to PostgreSQL Indexes: The Index Lifecycle from Design to Retirement

• Introduction
• 1. The Index as a Lifecycle
• 2. Design — Which Columns, in What Order
  • Covering Indexes and INCLUDE
• 3. Choosing a Method — Which of the Six
• 4. Partial Indexes and Expression Indexes
• 5. Creation — CONCURRENTLY and Recovering from Failure
  • When It Fails
  • The Exception for Partitioned Tables
• 6. Verification — Is It Actually Being Used
• 7. Operations — Handling a Bloated Index
• 8. Retirement — Dropping an Index Reversibly
• Quiz: Test Your Understanding
• Closing
• Sources
• Further Reading

Introduction

Most writing about indexes falls into one of two camps. One explains data structures — what a B-tree actually is and how it is laid out on disk. The other is optimizer talk, the kind of piece that walks through "why won't my index get used, even though I built it." This blog already has examples of both. The Complete Guide to Advanced PostgreSQL Indexing leans toward the first camp, and Why the Index You Built Isn't Being Used leans toward the second.

This piece takes a third angle. It treats the index as an operational asset with a lifecycle, the same way you would treat a server, a queue, or a cron job. An index gets designed, gets created, gets verified, ages in production as the data and the workload shift underneath it, and eventually gets dropped. In practice, the incidents that actually wake someone up rarely come from "what data structure is this." They come from "when and how do you create it, and when and how do you drop it" — moments like building an index on a live table and accidentally blocking writes for several minutes, or a dozen indexes nobody ever queries, each one quietly charging a fee on every single INSERT, UPDATE, and DELETE that touches the table.

The reference engine throughout is PostgreSQL 18. Every default parameter value, lock level, and EXPLAIN behavior quoted here was read directly from the PostgreSQL 18 documentation, and anywhere the behavior differs across versions, that difference is called out explicitly rather than glossed over. MySQL's indexes stand on an entirely different premise — the clustered index, where the table itself is physically organized as an index on the primary key — so this piece deliberately does not mix the two engines together. Readers coming from MySQL should treat every claim here as PostgreSQL-specific.

1. The Index as a Lifecycle

The moment you create an index, the database starts paying three ongoing costs, and it keeps paying them for as long as that index exists.

• Write cost — every INSERT, UPDATE, and DELETE on the table has to update the index entry too, on top of the base table write. Ten indexes on a table means ten extra updates for every single row change.
• Space cost — an index takes up disk and buffer-cache space just like table data does. Every page an index holds in cache is a page of table data pushed out, which means other queries now have to go to disk more often than they otherwise would.
• Maintenance cost — VACUUM has to clean indexes as well as the table itself. More indexes means longer VACUUM runs, and a VACUUM that takes longer competes for I/O with the rest of the workload for that much longer.

The read benefit of an index is easy to see — a query gets faster, and everyone notices. These three costs are not; they show up as a slow, cumulative drag rather than a single visible event. That asymmetry is exactly why indexes only ever accumulate on a typical production database and almost never shrink on their own — nobody feels the pain of removing one, so nobody does. The lifecycle view is an attempt to correct that asymmetry deliberately, by managing, for every index that exists, both "why was this built" and "when will this be dropped," as a single ongoing concern rather than a one-time decision made at creation time.

The lifecycle breaks into six stages: design, method selection, creation, verification, operations, and retirement. The sections below follow that order exactly, and each one is meant to stand on its own as a reference you can jump back into.

2. Design — Which Columns, in What Order

In a composite index, column order changes performance not by a multiple but by an order of magnitude. The rule for choosing that order is simple.

Put columns used in equality conditions first, and columns used in range conditions after. A B-tree index is sorted in order starting from its leading column, so once a range condition appears, every column after it can no longer be used to narrow the search — it survives only as a filter.

-- A frequently executed query
SELECT id, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

-- Good order: equality (tenant_id, status), then range/sort (created_at)
CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

-- Bad order: a leading range column means the equality conditions behind it
-- can no longer narrow the index scan
CREATE INDEX idx_orders_created_tenant_status
  ON orders (created_at DESC, tenant_id, status);

To get the sort itself onto the index, the ORDER BY direction has to be reflected in the index definition. In the example above, defining the index with created_at DESC makes the sort operation disappear entirely. PostgreSQL can read an index backward, so for a single-column sort the direction rarely matters much — but for a composite sort (a ASC, b DESC), the index definition has to match exactly for the sort to be skipped.

Covering Indexes and INCLUDE

An execution style that reads only the index and never visits the table is called an Index Only Scan. It's possible when every column the query needs is already in the index. For columns you need in the result but never search on, INCLUDE is the better place to put them.

CREATE INDEX idx_orders_cover
  ON orders (tenant_id, status)
  INCLUDE (total_amount, created_at);

The PostgreSQL documentation is explicit about INCLUDE-listed non-key columns: they "cannot be used in an index scan search qualification, and they are not considered when checking for uniqueness or exclusion constraints." In exchange, their values can be read straight out of the index entry without a second lookup, which is exactly what makes an Index Only Scan possible in the first place. INCLUDE is only supported on B-tree, GiST, and SP-GiST, and a B-tree index that carries non-key columns has deduplication turned off for the whole index, which is a real trade-off if the key columns themselves are highly repetitive.

One caveat, and it trips people up constantly. For an Index Only Scan to actually skip the table, the visibility map has to mark the relevant page as all-visible, meaning every row on that page is guaranteed visible to every transaction without a further check. On a table that was just updated and hasn't been vacuumed yet, the plan can say Index Only Scan and still report a large Heap Fetches count, because the visibility map hasn't caught up. Always check that number in the plan rather than trusting the scan's name alone.

3. Choosing a Method — Which of the Six

PostgreSQL ships six index methods: btree, hash, gist, spgist, gin, and brin — omit the USING clause and you get B-tree. The decision criteria matter more than the data-structure explanation, so here's a table.

Two extra B-tree capabilities called out in the documentation are worth remembering, because they come up constantly in practice and people often assume otherwise. First, B-tree handles anchored patterns like LIKE 'foo%', where the wildcard sits at the end. It cannot handle LIKE '%foo', where the wildcard sits at the front, because a leading wildcard breaks the sorted-prefix property the index relies on. Second, only B-tree can hand back data already in sorted order, which is exactly why it is the only method that can make an ORDER BY disappear from a plan.

BRIN is widely misunderstood, and the misunderstanding usually runs in the "too good to be true" direction. Because it stores only a summary — a minimum and a maximum value — per contiguous range of blocks rather than an entry per row, its index size is extremely small, often a tiny fraction of the equivalent B-tree. But as the documentation stresses, it only pays off when the column's values correlate well with physical row order on disk. It is excellent for created_at on a log table that only ever appends new rows in time order, where physical order and value order are nearly the same thing, and it is essentially useless on a status column that gets updated in place in random order, where that correlation never holds.

4. Partial Indexes and Expression Indexes

Two tools for keeping an index small — and, in practice, the most underrated features in the toolbox.

A partial index restricts which rows get indexed with a WHERE clause. If only 1% of all rows are ever queried, the index only needs to cover that 1%.

-- A queue table where only unprocessed jobs matter
CREATE INDEX idx_jobs_pending
  ON jobs (created_at)
  WHERE status = 'PENDING';

-- A uniqueness constraint that excludes soft-deleted rows
CREATE UNIQUE INDEX idx_users_email_active
  ON users (lower(email))
  WHERE deleted_at IS NULL;

The second example combines a partial index with an expression index, and together they form the standard way to express "email must be unique, but only among the still-alive rows" in a schema that uses soft deletes instead of hard deletes. Checking this in application code — a SELECT to confirm nothing already has that email, followed by an INSERT — leaves a concurrency hole that two simultaneous requests can slip through together; a unique index makes the database itself guarantee it atomically, with no window for a race.

An expression index builds the index on the result of an expression instead of on a plain column value. The catch, and it is an unforgiving one, is that the expression written in the query has to match the expression baked into the index exactly, character for character as far as the planner is concerned. Build the index on lower(email) and the query needs to filter on lower(email) = ... for the planner to recognize the match; something that looks equivalent to a human, like email ILIKE ..., cannot use that index at all.

Expression indexes also affect statistics in a way that is easy to miss. PostgreSQL collects separate statistics for expression indexes, so simply creating one has the side effect of making row-count estimates for that expression more accurate, even in plans that end up not using the index itself. If you want the statistics without paying for the index, CREATE STATISTICS can build expression statistics on their own, decoupled from any index.

5. Creation — CONCURRENTLY and Recovering from Failure

This is where the most operational incidents happen.

Warning: a plain CREATE INDEX blocks writes to the target table until it finishes. The PostgreSQL documentation states that a plain CREATE INDEX "locks out writes (but not reads) on the table until it's done." Reads are allowed; writes wait. On a table with hundreds of millions of rows, this command can freeze the service's write path for tens of minutes. Any index built on a live table must use CONCURRENTLY.

-- This is the only form to use against a live table
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

CONCURRENTLY avoids taking the lock that blocks inserts, updates, and deletes, which is the whole point of using it on a table that is serving live traffic. The trade-off is real, though: per the documentation, it performs two full table scans in two separate transactions, and before each scan it waits for every existing transaction that could modify or use the index to finish first. After the second scan, it waits again — this time for every transaction holding a snapshot from before that scan to finish. So a single long-running transaction can stall the entire index build indefinitely, potentially for hours, with no error and no obvious sign of what is blocking it. Make it a habit to check pg_stat_activity for long-running transactions before you kick off an index build, not after you notice it has been running too long.

Also, CREATE INDEX CONCURRENTLY cannot run inside a transaction block, by design — PostgreSQL will simply reject it. If your migration tool wraps every migration in a single transaction, as many do by default, this command will fail there every time. You need to find that specific tool's option for disabling the wrapping transaction for this one migration.

When It Fails

If something goes wrong partway through — a deadlock, a uniqueness violation discovered mid-build, an operator killing the wrong session — the command fails but leaves an invalid index behind rather than cleaning up after itself the way a normal CREATE INDEX would. In the documentation's words, this index "will be ignored for querying purposes because it might be incomplete," yet it keeps paying update overhead on every write forever, exactly as if it were a fully valid index. That is the worst possible state a database object can be in: all of the cost, none of the benefit, and nothing about the schema tells you it happened.

-- Find invalid indexes
SELECT c.relname AS index_name, i.indisvalid, i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

-- Recovery: drop and rebuild
DROP INDEX CONCURRENTLY idx_orders_tenant_status_created;
-- or reindex in place
REINDEX INDEX CONCURRENTLY idx_orders_tenant_status_created;

Make running that query part of your process before you report an index build as done. Invalid indexes stay behind quietly.

The Exception for Partitioned Tables

The documentation is blunt: "concurrent build is not currently supported for indexes on partitioned tables." There is a recommended workaround. Build the index CONCURRENTLY on each partition individually, and finally build it non-concurrently on the parent partitioned table — this shortens the write-blocking window. When you build the index on the parent, if a correctly named index already exists on each partition, it gets attached rather than rebuilt.

6. Verification — Is It Actually Being Used

There are two levels to confirming an index you built is actually being used.

At the query level, look at EXPLAIN. In PostgreSQL 18, buffer information is included automatically whenever you use ANALYZE. The documentation states it plainly: "Buffers information is automatically included when ANALYZE is used." On 17 and earlier, you had to add BUFFERS explicitly.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount
FROM orders
WHERE tenant_id = 42 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

 Limit  (cost=0.56..42.18 rows=50 width=20)
        (actual time=0.041..0.180 rows=50 loops=1)
   Buffers: shared hit=54
   ->  Index Only Scan Backward using idx_orders_cover on orders
         (cost=0.56..8321.44 rows=10004 width=20)
         (actual time=0.039..0.171 rows=50 loops=1)
         Index Cond: ((tenant_id = 42) AND (status = 'PAID'::text))
         Heap Fetches: 0
         Buffers: shared hit=54
 Planning Time: 0.212 ms
 Execution Time: 0.221 ms

There are three things to check here: whether the condition landed in Index Cond (if it's sitting in Filter instead, the index did not narrow anything), whether Heap Fetches is zero, and how far the rows estimate is from the actual value.

Warning: EXPLAIN ANALYZE actually executes the statement. As the documentation states, the output of a SELECT is discarded, but every other side effect still happens. When analyzing INSERT, UPDATE, DELETE, or MERGE, wrap it in BEGIN / EXPLAIN ANALYZE ... / ROLLBACK.

At the workload level, look at the statistics views. In pg_stat_user_indexes, idx_scan is the number of scans that started on that index, and last_idx_scan is the timestamp of the most recent one.

SELECT s.schemaname, s.relname, s.indexrelname,
       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 NOT i.indisunique
ORDER BY s.idx_scan ASC, pg_relation_size(s.indexrelid) DESC;

Two things to watch when reading these numbers. First, the statistics are cumulative since the last reset — if you don't know when that was, a zero doesn't tell you anything. Second, an index that only backs a monthly batch job or a quarterly close report will read as zero over a week of observation. Watch for at least one full business cycle.

7. Operations — Handling a Bloated Index

Under PostgreSQL's MVCC, an update never modifies a row in place — it creates a new row version and leaves the old one for VACUUM to reclaim later, and the index has to add a new entry to match that new version. VACUUM cleans up the dead entries once they are no longer visible to any transaction, but the space it reclaims inside an index page doesn't always come back in a form that later insertions can actually reuse efficiently. Over time, an index gradually grows larger than the logical amount of data it represents. That's index bloat, and it is a one-way ratchet unless something actively reverses it.

The symptom is quiet, which is exactly what makes it dangerous. Queries don't suddenly get slow on some obvious date — they get slow gradually, a few milliseconds at a time, until months later someone notices the whole system feels sluggish. Less of the index fits in cache as it bloats, so more of every scan has to go to disk, and scans read more pages than the row count alone would suggest.

The fix is rebuilding the index from scratch, which throws away the bloat and starts clean.

Warning: running REINDEX without CONCURRENTLY takes an ACCESS EXCLUSIVE lock on the target table. That lock conflicts with every other lock mode, so it blocks reads too. VACUUM FULL and CLUSTER carry the same risk and should not be run casually on a live system for the same reason. The PostgreSQL documentation itself recommends that "administrators should try to avoid VACUUM FULL" in favor of routine VACUUM. The safe alternative is REINDEX INDEX CONCURRENTLY. It takes only a SHARE UPDATE EXCLUSIVE lock, so it blocks neither reads nor writes.

-- Safe: blocks neither reads nor writes
REINDEX INDEX CONCURRENTLY idx_orders_tenant_status_created;

-- Rebuild every index on a table concurrently
REINDEX TABLE CONCURRENTLY orders;

REINDEX ... CONCURRENTLY can also leave an invalid index behind on failure. If you see a leftover index with _ccnew appended to its name, clean it up.

When you're deciding on a rebuild cadence, look at the update pattern. A near append-only table barely bloats. A status table or a queue table that repeatedly updates the same rows bloats fast. Pairing a queue table with a partial index sharply cuts its exposure to bloat.

8. Retirement — Dropping an Index Reversibly

An unused index should be dropped, because every index that isn't earning its keep is pure cost with no offsetting benefit. But because "unused" can turn out to be a wrong call — based on incomplete observation, a rare but critical query, or a constraint nobody remembered — the process should stay reversible at every step rather than being a single irreversible command.

Start by finding duplicate indexes, which are often the easiest wins. If both (a) and (a, b) exist on the same table, the first is usually unnecessary, because a B-tree composite index can serve any condition that only touches its leading column just as well as a dedicated single-column index would. The exceptions are when (a) is itself a unique index enforcing a constraint, or when (a) alone — being smaller — is enough to support an Index Only Scan that (a, b) would serve less efficiently.

The retirement process has three steps.

Step 1 — Nominate candidates. Use the query from Section 6 to pull indexes where idx_scan is zero or extremely low. Exclude indexes backing unique constraints and the referencing-side indexes for foreign keys. If a child table with a foreign key has no index, deleting a parent row triggers a full scan.

Step 2 — Test by disabling. Before dropping anything, make the optimizer ignore it first. PostgreSQL has no official command to switch an index off, and while there is a known trick of editing the system catalog directly to mark it invalid, editing the catalog directly is risky. The safer path is turning off enable_indexscan and enable_bitmapscan for a single session and comparing the plan and runtime of your representative queries. Both parameters default to on.

-- Exclude index paths for just this session, to measure the worst case
SET enable_indexscan = off;
SET enable_bitmapscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET enable_indexscan;
RESET enable_bitmapscan;

Step 3 — Drop it. Drop it concurrently too.

DROP INDEX CONCURRENTLY idx_orders_legacy_status;

And always commit a recreation script alongside the drop. The rollback for dropping an index is recreating it, and recreating it takes time. Trying to reconstruct the definition from memory during an incident is where mistakes happen. Capture the definition ahead of time with pg_get_indexdef().

SELECT indexrelid::regclass AS index_name,
       pg_get_indexdef(indexrelid) AS definition
FROM pg_index
WHERE indrelid = 'orders'::regclass;

Quiz: Test Your Understanding

Index Only Scan using idx_orders_cover on orders
  (actual time=0.05..820.4 rows=48000 loops=1)
  Index Cond: (tenant_id = 42)
  Heap Fetches: 47912
  Buffers: shared hit=1204 read=46180

SELECT pid, state, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_alive
  ON users (lower(email))
  WHERE deleted_at IS NULL;

CREATE INDEX idx_a ON events (tenant_id);
CREATE INDEX idx_b ON events (tenant_id, occurred_at);

Closing

Most index problems come not from a lack of knowledge but from a lack of process. Plenty of engineers know what a B-tree is and can draw one on a whiteboard; far fewer teams have an actual written process for using CONCURRENTLY when building an index on a live table, checking for long-running transactions before they start, and querying for invalid indexes afterward as a routine last step rather than something they only think to do after an incident.

Three rules alone, made into team policy and enforced in code review, eliminate most of these incidents. First: creating, dropping, or rebuilding an index on any live table always uses CONCURRENTLY, with no exceptions carved out for "just this once." Second: whenever you create an index, record why you built it and which query it exists to serve, right there in the migration file's comments, so the next person doesn't have to guess. Third: query idx_scan on a fixed cadence — once a quarter is enough for most teams — and actually review the retirement candidates it surfaces instead of letting the list grow forever.

You can run every piece of SQL in this piece yourself in this site's Postgres Playground.

Sources

• PostgreSQL 18, CREATE INDEX: https://www.postgresql.org/docs/18/sql-createindex.html (retrieved 2026-08-15)
• PostgreSQL 18, Index Types: https://www.postgresql.org/docs/18/indexes-types.html (retrieved 2026-08-15)
• PostgreSQL 18, EXPLAIN: https://www.postgresql.org/docs/18/sql-explain.html (retrieved 2026-08-15)
• PostgreSQL 18, Explicit Locking: https://www.postgresql.org/docs/18/explicit-locking.html (retrieved 2026-08-15)
• PostgreSQL 18, Routine Vacuuming: https://www.postgresql.org/docs/18/routine-vacuuming.html (retrieved 2026-08-15)
• PostgreSQL 18, The Statistics Collector: https://www.postgresql.org/docs/18/monitoring-stats.html (retrieved 2026-08-15)
• PostgreSQL 18, Query Planning: https://www.postgresql.org/docs/18/runtime-config-query.html (retrieved 2026-08-15)
• PostgreSQL 18, Client Connection Defaults: https://www.postgresql.org/docs/18/runtime-config-client.html (retrieved 2026-08-15)

Further Reading

• Next: The Complete Guide to SQL Execution Plans — how the optimizer chooses an index path
• The Complete Guide to Advanced PostgreSQL Indexing — the internal structure of GIN, GiST, and BRIN
• Why the Index You Built Isn't Being Used — the cases where the optimizer refuses an index
• PostgreSQL VACUUM and MVCC Internals — how bloat happens
• Postgres Playground — run this piece's SQL yourself
• SQL Playground — experiment with query syntax