Skip to content
Tutorials Center
My learning

Topics

Lesson 43 of 90 ยท Indexes

Index Maintenance and Bloat

Keep PostgreSQL indexes healthy: find unused and duplicate indexes, rebuild with REINDEX CONCURRENTLY, understand index bloat, fillfactor and HOT updates.

An index is not a "create it once and forget it" feature. Every index you add makes writes slower, takes disk space, gives VACUUM more to do and competes for memory. Over the life of a database, some indexes stop being useful (the query that needed them was rewritten), some turn out to be copies of each other, and some grow bloated because of heavy updates and deletes.

This lesson is the maintenance routine for PostgreSQL indexes: how to see which indexes are used, how to find the ones you can drop, how to rebuild a bloated index without blocking your application, and how two storage settings (fillfactor and HOT updates) decide how much maintenance you need in the first place.

What runs live here. The server tools in this lesson (pg_stat_user_indexes, REINDEX CONCURRENTLY, pgstattuple) exist only in a real PostgreSQL server, so they appear as read-only code blocks with the kind of output you would see. The two TryIt editors use SQLite's catalog functions to do the same thinking (spot duplicate indexes, check which index a query uses). The ideas carry over; the catalog names do not.

The index maintenance loop

Measure Read usage counters and index sizes.
Remove Drop unused and duplicate indexes.
Rebuild Rebuild bloated or invalid indexes.
Tune Use fillfactor so updates stay cheap.

Do the loop a few times a year, and always before adding "just one more" index.

1 / 2

Listing the indexes you have

Before you measure anything, find out what exists. psql has shortcuts, and the catalogs give the same facts in SQL (PostgreSQL 18 output):

SQL
\d hamlet            -- the table, with an "Indexes:" section at the bottom
\di                  -- every index in the search path, with its table
\di+ idx_h_name      -- adds persistence, access method, size and description

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'hamlet' ORDER BY indexname;
--  idx_h_name   | CREATE INDEX idx_h_name ON public.hamlet USING btree (village_name)
--  idx_h_pop    | CREATE UNIQUE INDEX idx_h_pop ON public.hamlet USING btree (population) WHERE (population > 0)
--  village_pkey | CREATE UNIQUE INDEX village_pkey ON public.hamlet USING btree (id)

SELECT i.relname AS index, pg_size_pretty(pg_relation_size(i.oid)) AS size,
       ix.indisunique AS uniq, ix.indisprimary AS pk
FROM pg_index ix JOIN pg_class i ON i.oid = ix.indexrelid
WHERE ix.indrelid = 'hamlet'::regclass ORDER BY 1;
--  idx_h_name   | 16 kB      | f | f
--  idx_h_pop    | 8192 bytes | t | f
--  village_pkey | 16 kB      | t | t

pg_indexes.indexdef is a complete CREATE INDEX statement, which makes it handy for copying an index to another database and for spotting partial or expression indexes at a glance. Drop one with DROP INDEX idx_h_pop; (or DROP INDEX IF EXISTS; add CONCURRENTLY on a busy table, as described in the sections below). An index that backs a primary key or unique constraint cannot be dropped directly: PostgreSQL answers "cannot drop index village_pkey because constraint village_pkey on table hamlet requires it", and you drop the constraint with ALTER TABLE ... DROP CONSTRAINT instead.

Measure: which indexes are used?

PostgreSQL counts how often each index is scanned. The view pg_stat_user_indexes has one row per index in your own tables:

SQL
SELECT schemaname, relname AS table_name, indexrelname AS index_name,
       idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 5;
TEXT
 schemaname | table_name |        index_name         | idx_scan | idx_tup_read | idx_tup_fetch |  size
------------+------------+---------------------------+----------+--------------+---------------+--------
 public     | events     | events_user_id_created_idx|  1840221 |      9120331 |       9120331 | 2104 MB
 public     | events     | events_payload_gin        |    20311 |       88120  |             0 | 1650 MB
 public     | events     | events_created_at_idx     |        0 |            0 |             0 |  812 MB
 public     | orders     | orders_pkey               |  5530044 |      5530044 |       5530044 |  214 MB
 public     | orders     | orders_status_idx         |       37 |         2410 |          2399 |  190 MB

idx_scan is the number of index scans started. In the example, events_created_at_idx was never scanned and takes 812 MB. That is a candidate for removal.

Before you trust a zero, check three things:

  • How long have the counters been collecting? They reset when someone calls pg_stat_reset() and after a crash recovery. Look at stats_reset in pg_stat_database for your database. A zero after two hours means nothing; a zero after three months of normal traffic means a lot. PostgreSQL 16 and later also record last_idx_scan, the time of the most recent scan, which is even easier to read.
  • Did the workload include rare jobs? A month-end report may be the only user of an index. Counters that cover no month end would miss it.
  • Which server did you ask? Statistics are kept per server. If read-only reports run on a replica, ask the replica too, because the primary will show zero for indexes only replicas use.

Indexes you must not drop just because they are unused

An index with idx_scan = 0 can still be doing a job. A unique index or primary key enforces a rule on every insert, even if no query ever searches it. An index that backs a foreign key lookup is rarely scanned directly, but when you delete a parent row, PostgreSQL needs it to find child rows quickly. So filter out the constraint-backed ones:

SQL
SELECT s.relname AS table_name, s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes AS s
JOIN pg_index AS i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique        -- keep unique and primary key indexes
ORDER BY pg_relation_size(s.indexrelid) DESC;

Review each result by hand. Ask whether the index supports a foreign key, a rare report or an ON CONFLICT target before you remove it.

Dropping without blocking

A plain DROP INDEX takes a strong lock on the table for a moment, and that moment can queue behind a long-running query and block everything after it. The safe form is:

SQL
DROP INDEX CONCURRENTLY events_created_at_idx;

It waits for running transactions in a gentler way, and it cannot run inside a transaction block (so no BEGIN before it). Take a note of the original CREATE INDEX statement first; recreating a dropped index on a big table takes a long time.

Find duplicate and redundant indexes

Two kinds of waste are common, especially after years of migrations written by different people:

  1. Exact duplicates. Two indexes on the same columns, with the same operator class, uniqueness and WHERE clause. One of them is pure cost.
  2. Prefix-redundant indexes. An index on (customer_id) next to one on (customer_id, created_at). The B-tree on both columns can answer every query the single-column one can (see B-tree indexes), so the shorter one is usually unnecessary, unless it is unique or much smaller matters for your workload.

In PostgreSQL you find exact duplicates by grouping the catalog pg_index on its definition columns. A standard recipe:

SQL
SELECT indrelid::regclass AS table_name,
       array_agg(indexrelid::regclass) AS duplicate_indexes
FROM pg_index
GROUP BY indrelid, indkey, indclass, indexprs::text, indpred::text, indisunique
HAVING count(*) > 1;

SQLite keeps the same information in a different shape, so you can practice the idea live. This example builds three indexes on one table, then uses the catalog functions pragma_index_list and pragma_index_info to print each index's column list:

SQL Spot duplicate and prefix indexes

Press Run (or Ctrl + Enter) to see the result here.

The result lists orders_customer_a twice (it is an exact duplicate of orders_customer_b, and a prefix of orders_customer_date), orders_customer_b the same way, and nothing for orders_total. For a pair of exact duplicates you drop only one; for a prefix you decide whether the shorter index earns its keep. In PostgreSQL the same decision uses pg_index and pg_get_indexdef() to compare definitions.

See which index a query really uses

Statistics tell you what happened; EXPLAIN tells you what will happen. Before dropping an index you suspect is unused, run the queries that matter and check the plan. In PostgreSQL that is EXPLAIN (see EXPLAIN and EXPLAIN ANALYZE); SQLite's EXPLAIN QUERY PLAN gives a smaller but similar answer:

SQL Which index does each query pick?

Press Run (or Ctrl + Enter) to see the result here.

Read the three plan lines. The first two use orders_customer_date, so a separate customer_id index would have been unnecessary. The third needs orders_created, because created_at is the second column of the composite index and so it cannot help a query that does not filter by customer_id first. (PostgreSQL 18 added "skip scans" that can sometimes use a composite index this way, but only when the first column has few distinct values.) Keep an index only when some plan actually needs it.

Rebuild: REINDEX and REINDEX CONCURRENTLY

Indexes can need a rebuild for two reasons: they are bloated (more space than their live entries need), or they are invalid or damaged (a failed concurrent build, or corruption after a hardware or software bug).

SQL
-- Blocks writes to the table while it runs:
REINDEX INDEX orders_customer_date;

-- Online version (PostgreSQL 12+):
REINDEX INDEX CONCURRENTLY orders_customer_date;
REINDEX TABLE CONCURRENTLY orders;

Plain REINDEX is fast but takes locks that stop writes to the table (and reads that need the index) until it finishes. REINDEX CONCURRENTLY builds a fresh copy of the index next to the old one, lets writes continue, then swaps the new index in. It takes longer, needs room for both copies at once, and holds only a lock that allows normal reads and writes.

Two cautions:

  • If a concurrent rebuild is interrupted, it can leave behind an invalid index, often with a name ending in _ccnew or _ccold. These show up as indisvalid = false in pg_index and INVALID in psql's \d output. Drop them (DROP INDEX CONCURRENTLY) and run the rebuild again. An invalid index is still maintained on every write, so leaving it around is pure cost.
  • REINDEX CONCURRENTLY cannot run inside a transaction block, and it cannot rebuild an index that backs an exclusion constraint.

The same rule applies when you create a new index on a live table: use CREATE INDEX CONCURRENTLY, and check for INVALID afterwards.

Bloat: why indexes grow

PostgreSQL never updates a row in place (the reason is explained in MVCC). An update writes a new row version, and a B-tree index must usually get a new entry that points to it. Old entries stay until VACUUM removes them. That works well, but three side effects add up:

  • Space is reused but rarely returned. VACUUM marks index pages as empty and reusable. The file does not shrink, so an index that once held 50 million entries keeps its size on disk even after most entries are gone.
  • Pages become sparse. Deleting rows scattered across a key range leaves many half-empty pages. B-trees merge pages only when a page becomes completely empty, so sparse pages can persist.
  • Churn on one side of the tree. Indexing an ever-increasing key (timestamps, sequence IDs) while deleting old rows leaves the left side of the tree empty.

PostgreSQL 13 and later reduce the damage: B-tree deduplication stores repeated keys compactly, and bottom-up deletion removes dead entries before a page has to split.

To measure bloat accurately you can use the pgstattuple extension, which reads the index:

SQL
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT avg_leaf_density, leaf_fragmentation, leaf_pages, empty_pages, deleted_pages
FROM pgstatindex('orders_customer_date');
TEXT
 avg_leaf_density | leaf_fragmentation | leaf_pages | empty_pages | deleted_pages
------------------+--------------------+------------+-------------+---------------
             41.2 |               37.5 |     120331 |         812 |         14022

A freshly built B-tree has avg_leaf_density near 90 (the default fillfactor). Density in the 40s means the index uses more than twice the space it needs; rebuilding it would roughly halve its size. Do not rebuild on a schedule "just in case": a rebuilt index starts dense, then slowly refills with new entries, and the first inserts can cause page splits. Rebuild when the numbers (and the size on disk) justify it.

Fillfactor: leaving room on purpose

Fillfactor is the percentage of each page that PostgreSQL fills when it writes new data. The rest is kept free for later changes.

  • B-tree indexes default to fillfactor = 90.
  • Tables default to fillfactor = 100: pages are filled completely.

You set it per object:

SQL
ALTER TABLE orders SET (fillfactor = 85);
ALTER INDEX orders_customer_date SET (fillfactor = 80);
REINDEX INDEX CONCURRENTLY orders_customer_date;   -- applies the new setting

For tables, the new fillfactor affects only pages written afterwards. Existing pages change when the table is rewritten (for example by VACUUM FULL, which locks the table, or a rebuild tool such as pg_repack). For indexes, a rebuild applies the setting.

A lower fillfactor makes the object bigger, so only use it where the free space pays for itself, which brings us to HOT updates.

HOT updates: updates that skip the indexes

A normal UPDATE creates a new row version and then adds an entry to every index on the table, even indexes whose columns did not change. That is expensive and a main source of index bloat.

A HOT (heap-only tuple) update avoids it. If both conditions hold, PostgreSQL writes the new version on the same table page and does not touch any index:

  1. The update changes no indexed column. (PostgreSQL 16 relaxed this slightly: columns that are only covered by summarizing indexes such as BRIN do not block HOT.)
  2. The same page has free space for the new version.

The old index entry still points at the page, and PostgreSQL follows a short chain inside the page to the current version. Cleaning up such chains is cheap and can even happen during ordinary reads.

You can check how well this works for a table:

SQL
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
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC;
TEXT
  relname  | n_tup_upd | n_tup_hot_upd | hot_pct
-----------+-----------+---------------+---------
 sessions  |  48201933 |      47800121 |    99.2
 orders    |   9120044 |       1802233 |    19.8

sessions updates a column nobody indexes (like last_seen_at), so nearly every update is HOT. orders has a low percentage. If its hot updates are frequent in your workload, two fixes help: remove an index on a column that changes often (for example an index on status that you rarely use), and lower the table's fillfactor so each page keeps some room.

This is the practical link between the topics of this lesson: an index on a frequently updated column makes every update touch all indexes, grows them all, and increases bloat. Index columns that are read often and written rarely.

A maintenance checklist. Once a quarter: list the biggest indexes, check idx_scan against a long stats window, remove duplicates, look for INVALID indexes, check HOT percentages on update-heavy tables, and measure bloat only on indexes that look oversized. Use the concurrent forms of every command.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. An index shows idx_scan = 0 in pg_stat_user_indexes. What should you check before dropping it?

    Counters reset at stats reset and crash recovery, unique and foreign key indexes do work without being scanned, and monthly reports may be missing from a short window. Replicas keep their own counters too.
  2. 2. Why is REINDEX INDEX CONCURRENTLY usually preferred on a busy production table?

    The concurrent form is slower and needs room for both copies, but it only takes a lock that still allows normal reads and writes. Plain REINDEX blocks writes while it runs.
  3. 3. What is a HOT update?

    HOT (heap-only tuple) needs no indexed column to change and free space in the same page. Then no index has to be touched, which keeps indexes small and updates cheap.
  4. 4. You find two indexes: one on (customer_id) and one on (customer_id, created_at). Both are non-unique. What is usually true?

    A B-tree on (a, b) can serve conditions on a alone because a is its leading column. Review the exceptions (uniqueness, a much smaller index for a hot query) before dropping the shorter one.
  5. 5. After REINDEX CONCURRENTLY is cancelled, psql shows an index named orders_x_ccnew marked INVALID. What do you do?

    An invalid index is not used by queries but is still maintained on every write. Drop it and retry the rebuild.

Summary

  • pg_stat_user_indexes shows how often each index is scanned; trust a zero only after a long stats window, and never drop constraint-backed indexes just for being unscanned.
  • Duplicate indexes (same definition) are pure cost; prefix-redundant indexes need a judgment call.
  • Use DROP INDEX CONCURRENTLY, CREATE INDEX CONCURRENTLY and REINDEX ... CONCURRENTLY on live tables, and clean up INVALID leftovers.
  • Index bloat comes from updates and deletes; measure it with pgstatindex before rebuilding.
  • Fillfactor reserves free space in pages; lower values help update-heavy tables and cost disk space.
  • HOT updates skip index maintenance when no indexed column changes and the page has room, so avoid indexing columns that change constantly.

Next, the Transactions chapter begins with Transactions and ACID.