Index maintenance: bloat, REINDEX, and detecting unused ones

Capsule description

The previous capsules taught you to add indexes: composite, covering, partial, expression. This capsule teaches you the opposite discipline, one that's almost never taught: maintaining them.

Three realities few courses cover:

1. Each index slows down your INSERT/UPDATE/DELETE. Not a little — significantly. A table with 10 indexes can have writes 5-10x slower than the same table with no indexes.
2. Indexes age. They accumulate bloat (space wasted by updates and deletes that aren't immediately reused). An index that took 100MB six months ago may now take 400MB and be slower.
3. Indexes become obsolete. Your app's queries change (refactors, new features, migrations). An index that was optimal a year ago may never run today. Meanwhile, it slows down writes and consumes disk.

This capsule teaches you three maintenance skills:

• Detect unused indexes with pg_stat_user_indexes and remove them.
• Measure and understand index bloat, decide when it matters.
• Run REINDEX CONCURRENTLY to reorganize indexes without blocking writes.

And a critical mental framework: adding an index is a decision, maintaining an index is a decision. Both require evidence, not faith.

Concrete objective: you'll be able to do an index audit of any PostgreSQL database: identify candidates to remove, candidates to reindex, and quantify the write cost each one costs you.

Mental model: the reads vs writes balance

Each index is an explicit trade-off:

Benefit:                      Cost:
+ Queries with WHERE/ORDER    - Slower INSERT
  can use the index             (each new row must be written to each index)
+ Some COUNT/EXISTS become    - UPDATE of indexed columns
  Index Only Scan               (each change rewrites the index)
+ Some JOINs become an        - Slower DELETE
  Index Scan instead of a       (marks it as a dead tuple in each index)
  heavy Hash/Nested Loop      - Disk space
                              - shared_buffers space (cache pollution)
                              - Periodic REINDEX
                              - Larger backups

For an index to be worth it, the aggregate benefit of the read queries must exceed the aggregate cost of the write queries + maintenance.

Concrete example: an events table that receives 10,000 INSERTs per minute and 100 SELECTs per minute. Each index added:

• Slows down the 10,000 INSERTs by X% (small per index, multiplied by 10k = significant).
• Speeds up the 100 SELECTs by Y% (large per SELECT, but only 100/min).

If X * 10,000 > Y * 100, the index is negative for overall performance. Better not to have it.

Detecting unused indexes with pg_stat_user_indexes

PostgreSQL maintains usage statistics for each index. The pg_stat_user_indexes view tells you how many times each index has been used since the last statistics reset.

Basic query: never-used indexes

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'      -- not the PKs
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan is the number of times the index has been used in queries.

idx_scan = 0 means it was never used since the last statistics reset. If the statistics are recent and representative (at least 1-2 weeks of production traffic), you can consider removing those indexes.

Careful:

1. Don't remove PKs. Exclude _pkey with WHERE indexrelname NOT LIKE '%_pkey' — even though they sometimes show up with idx_scan = 0, they're required for integrity.
2. Don't remove UNIQUE indexes. They can have idx_scan = 0 but be enforcing a constraint. Exclude with pg_constraint.
3. Check the period. If the statistics were reset 2 days ago, you don't have enough evidence. Ideally at least 1-2 weeks with representative traffic.
4. Careful with seasonal tables. An index used only "on the last day of the month" can show idx_scan = 0 for 29 days.

Improved query: excluding PK and UNIQUE

SELECT
  s.schemaname,
  s.relname AS table_name,
  s.indexrelname AS index_name,
  s.idx_scan,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
  i.indisunique AS is_unique,
  i.indisprimary AS is_primary
FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisprimary
  AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

This gives you the indexes that are candidates for removal: unused, not PK, not UNIQUE.

When to reset statistics

SELECT pg_stat_reset();   -- resets EVERYTHING; careful in production

Consider a reset if:

• You just deployed major changes to the app and want to measure the new query pattern from scratch.
• You haven't reset in months and the data has so much historical dust that it's confusing.

Don't reset if:

• You're measuring decisions that depend on prior data.
• You're mid-audit — you need the accumulated data.

For a selective reset (indexes only), use pg_stat_reset_single_table_counters(oid) or pg_stat_reset_single_function_counters(oid).

"Almost" unused indexes: very low idx_scan

Sometimes it's interesting to see indexes with very low usage. Compare with size:

SELECT
  s.schemaname,
  s.relname AS table_name,
  s.indexrelname AS index_name,
  s.idx_scan,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
  pg_size_pretty(pg_relation_size(s.relid)) AS table_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan < 100
  AND NOT i.indisprimary
  AND NOT i.indisunique
  AND pg_relation_size(s.indexrelid) > 10 * 1024 * 1024  -- >10 MB
ORDER BY pg_relation_size(s.indexrelid) DESC;

Large indexes with very little usage are the most expensive relatively. Each one is MBs occupying disk and RAM with almost no return.

Index bloat: what it is and when it matters

PostgreSQL implements MVCC: each UPDATE creates a new version of the tuple, marks the old one as "dead". The same with DELETE. The dead tuples' space is freed with VACUUM, but the physical space isn't returned to the OS automatically — it stays as "reusable space" within the table's or index's file.

In B-tree indexes, the situation is similar: the leaf nodes have entries that get "marked deleted" but aren't compacted immediately. With many updates/deletes, the indexes accumulate bloat: half-empty pages, wasted space.

Symptoms of high bloat

• The index takes up much more on disk than "reasonable" for the data it indexes.
• Queries that were once fast slow down gradually with no obvious changes.
• EXPLAIN (ANALYZE, BUFFERS) shows many buffer hits in operations that should be few.
• The index size doesn't drop after massive DELETEs followed by a simple VACUUM.

How to measure bloat (approximate)

PostgreSQL doesn't have an official "bloat per index" view. There are well-accepted community queries. The most used is the wiki's:

-- Simplified "Index Bloat" query from the PostgreSQL wiki
-- For serious production, use the wiki's which is more complete
SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  ROUND(100 * (1 - (idx_blks_hit::float / NULLIF(idx_blks_hit + idx_blks_read, 0))), 2)
    AS cache_miss_pct
FROM pg_stat_user_indexes
JOIN pg_statio_user_indexes USING (indexrelid)
WHERE pg_relation_size(indexrelid) > 5 * 1024 * 1024
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

(The wiki's query — link in resources — computes the real estimated bloat by comparing real rows vs the expected index size. More precise.)

Heuristic for deciding on a REINDEX

In real production, don't obsess over bloat below 30%. PostgreSQL is reasonably efficient at reusing space. Only when there are extreme patterns (bulk loads + cyclic massive deletes) does bloat become critical.

REINDEX CONCURRENTLY

REINDEX rebuilds an index from scratch. It does two things:

1. Removes the bloat (compacts the reusable space).
2. Reorganizes the structure optimally (rebalances the tree).

Blocking version (do NOT use in production except in an emergency)

REINDEX INDEX index_name;

This blocks writes to the table while it runs. For large tables it can take minutes or hours. Every app that writes to the table waits. In production it's unacceptable.

Concurrent version (always use in production)

REINDEX INDEX CONCURRENTLY index_name;

This variant (PostgreSQL 12+):

• Doesn't block SELECT, INSERT, UPDATE, DELETE.
• Builds a new index in parallel.
• Once ready, an atomic swap (microseconds of blocking).
• If it fails halfway through, it leaves an invalid index (visible with pg_index.indisvalid = false) that you must clean up manually.

Typical case: REINDEX of a large index in production

-- 1. Check disk space (you need ~2x the index size during the process)
SELECT pg_size_pretty(pg_relation_size('idx_orders_customer_status'));
-- 280 MB → you need 280 MB free

-- 2. Run REINDEX CONCURRENTLY
REINDEX INDEX CONCURRENTLY idx_orders_customer_status;
-- Takes 2-10 minutes depending on size and load

-- 3. Check the result
SELECT pg_size_pretty(pg_relation_size('idx_orders_customer_status'));
-- 95 MB after reindex (removing bloat)

REINDEX of a whole table

REINDEX TABLE CONCURRENTLY orders;

Rebuilds all the table's indexes. Takes longer, but guarantees post-maintenance consistency.

If REINDEX fails

-- Detect invalid indexes
SELECT indexrelid::regclass, indrelid::regclass, indisvalid
FROM pg_index
WHERE NOT indisvalid;

-- Clean up (remove the failed index)
DROP INDEX CONCURRENTLY invalid_index_name;
-- Retry
REINDEX INDEX CONCURRENTLY index_name;

Recommended frequency

For typical apps: REINDEX is rarely necessary. PostgreSQL is efficient without your intervention. The cases where it is:

• After a bulk DELETE of >50% of the rows.
• After a massive data migration.
• When a critical query starts degrading and the plan shows no obvious cause.
• As scheduled annual maintenance on very large databases (>100GB).

Don't do a weekly or monthly REINDEX without reason. It's free work that adds nothing.

The cost of each index added: practical measurement

How much does each index cost in writes? You're going to measure it.

Setup

DROP TABLE IF EXISTS demo_writes;
CREATE TABLE demo_writes (
    id          BIGSERIAL PRIMARY KEY,
    col_a       INTEGER NOT NULL,
    col_b       TEXT NOT NULL,
    col_c       NUMERIC(10, 2) NOT NULL,
    col_d       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    col_e       TEXT
);

Measure INSERT with 0 indexes:

\timing on
INSERT INTO demo_writes (col_a, col_b, col_c, col_e)
SELECT (random() * 1000)::INTEGER, md5(random()::text), random() * 100, md5(random()::text)
FROM generate_series(1, 100000);
-- Time: 850 ms (example, varies by machine)

Add an index and measure again:

CREATE INDEX idx_w_col_a ON demo_writes(col_a);

INSERT INTO demo_writes (col_a, col_b, col_c, col_e)
SELECT (random() * 1000)::INTEGER, md5(random()::text), random() * 100, md5(random()::text)
FROM generate_series(1, 100000);
-- Time: 1100 ms (~30% slower)

Add more:

CREATE INDEX idx_w_col_b ON demo_writes(col_b);
CREATE INDEX idx_w_col_c ON demo_writes(col_c);
CREATE INDEX idx_w_col_d ON demo_writes(col_d);
CREATE INDEX idx_w_col_e ON demo_writes(col_e);

INSERT INTO demo_writes (col_a, col_b, col_c, col_e)
SELECT (random() * 1000)::INTEGER, md5(random()::text), random() * 100, md5(random()::text)
FROM generate_series(1, 100000);
-- Time: 2800 ms (~3.3x slower than with no indexes)

Typical observation: each index adds 15-30% to the INSERT. 5 indexes ≈ 2-4x slower. On write-heavy tables this is critical.

The UPDATE case

An UPDATE of a non-indexed column is relatively cheap (it doesn't touch any index).

An UPDATE of an indexed column is expensive: it updates the table + the affected index.

-- col_a is indexed
\timing on
UPDATE demo_writes SET col_a = col_a + 1 WHERE id < 100000;
-- Expensive: each updated row rewrites the index entry

-- is col_e indexed in an INCLUDE? If it's only INCLUDE, it also touches it.
UPDATE demo_writes SET col_e = md5(random()::text) WHERE id < 100000;
-- Expensive if col_e is in any index (key or INCLUDE)

Lesson: the INCLUDE isn't free. If the column in INCLUDE changes frequently, each UPDATE rewrites the covering index. For heavily-written mutable columns, avoid INCLUDE.

Why does this matter in real work?

1. Periodic audits remove invisible debt.

A typical 3+ year old database accumulates indexes nobody uses. Auditing pg_stat_user_indexes each quarter and removing the dead ones frees disk, improves writes, and reduces backup size. It's 1-2 hours of work with measurable impact.

2. Indexing decisions have a real, quantifiable cost.

"Let's add an index just in case" has a price. If your app does 10k INSERTs/min and each index slows that down 20%, the "just in case" costs 2k INSERTs/min of capacity. Knowing how to measure it changes the technical conversations.

3. REINDEX CONCURRENTLY is an SRE tool, not a DBA one.

When a critical endpoint starts degrading and the plan shows no obvious cause, REINDEX CONCURRENTLY can restore performance without downtime. Knowing it exists and how to use it is a differentiator in incidents.

4. The conversation with product/CTO becomes technical.

"Why can't we add 5 more indexes?" "Because each one adds 20% to the INSERT, and we have a bulk write endpoint that's critical for use case X. The cost of the 5 indexes exceeds the benefit of the queries they'd cover." That's a senior conversation, not a junior one.

Traps and common mistakes

Mistake 1 (conceptual): assuming idx_scan = 0 is always safe to remove

Symptom: you see an index with idx_scan = 0, you remove it, a week later a monthly endpoint fails because that index was used only in the monthly report.

Why it happens: idx_scan counts since the last reset. If it was 2 weeks ago and the index was used monthly, it looks "unused" without being so.

How to fix it: before removing, consider:

• When was the last stats reset? SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();
• Are there periodic queries (cron, reports) that might use the index only occasionally? Check pg_stat_statements (module 5).
• Consider "marking it as a candidate" (rename to idx_x_TOREMOVE) during an observation period before removing it definitively.

Mistake 2 (practical): a blocking DROP INDEX in production

Symptom: you run DROP INDEX name; on a heavily-used table, all the table's SELECT/UPDATE block until it finishes.

Why it happens: DROP INDEX by default requires an exclusive lock on the table.

How to fix it: use DROP INDEX CONCURRENTLY:

DROP INDEX CONCURRENTLY index_name;

Takes longer but doesn't block other operations.

Mistake 3 (conceptual): REINDEX as "preventive cleanup" without measurement

Symptom: you schedule a weekly REINDEX in cron "just in case".

Why it's sub-optimal: REINDEX consumes significant IO/CPU. If there was no real bloat, it's free work. On a large database it can affect the normal workload's performance during the REINDEX.

How to fix it: measure bloat before reindexing. Only reindex when the data justifies it (>30-50% bloat, index >100MB). For small tables, it's almost never worth it.

Mistake 4 (conceptual): counting idx_scan without a time context

Symptom: you compare two indexes: idx_a with idx_scan = 50, idx_b with idx_scan = 50000. You conclude that idx_a isn't used.

Why it's sometimes wrong: if idx_a covers a query that runs 50 times a day (an administrative query), 50 may be correct. If idx_b covers a query that runs 50,000 times a day (the main endpoint), also correct. Without the context of the rate, the numbers don't mean the same thing.

How to fix it: combine it with pg_stat_statements (module 5) to understand which queries use each index and how often those queries run.

Mistake 5 (practical): not monitoring space during REINDEX CONCURRENTLY

Symptom: REINDEX CONCURRENTLY fails halfway through due to lack of disk space.

Why it happens: during the reindex, two copies of the index exist (the old one and the new one being built). You need free space ≥ the index size.

How to fix it: check space beforehand:

SELECT pg_size_pretty(pg_relation_size('index_name'));
SELECT pg_size_pretty(pg_database_size(current_database()));
-- Compare with the filesystem's free space

If the space is tight, free some up first (remove unused indexes, old files) or schedule the REINDEX during a low-load window.

Mistake 6 (conceptual): assuming a lost index will be recreated automatically

Symptom: you remove an "unused" index, the next day an endpoint collapses because it was used at peak hour (the statistics didn't capture it in time).

Why it happens: PostgreSQL doesn't recreate indexes on its own. Once removed, it stays removed.

How to fix it:

• Before a DROP, a prolonged observation (at least 1-2 weeks) of clean stats.
• Ideally: disable it first (UPDATE pg_index SET indisready = false WHERE ...), monitor whether queries degrade, and only then remove it definitively.
• Have the CREATE INDEX documented/version-controlled in the schema migration, so you can recreate it fast if the worst happens.

Exercises

Exercise 1: audit of unused indexes

In your local database (or a toy one), run the unused-index detection query. List the candidates to remove.

SELECT
  s.schemaname,
  s.relname AS table_name,
  s.indexrelname AS index_name,
  s.idx_scan,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS size,
  i.indisunique,
  i.indisprimary
FROM pg_stat_user_indexes s
JOIN pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisprimary
  AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC;

schemaname | table_name | index_name       | idx_scan | size   | indisunique | indisprimary
-----------+------------+------------------+----------+--------+-------------+--------------
public     | orders     | idx_orders_old   | 0        | 45 MB  | f           | f
public     | books      | idx_books_legacy | 0        | 12 MB  | f           | f

Exercise 2: measure the cost of an index in INSERT

Create a table with 5 columns. Measure the time of INSERT of 100k rows with 0, 1, 3, 5 indexes. Report the times.

DROP TABLE IF EXISTS bench_idx;
CREATE TABLE bench_idx (
    id BIGSERIAL PRIMARY KEY,
    a INTEGER, b TEXT, c NUMERIC, d TIMESTAMPTZ DEFAULT NOW(), e TEXT
);

-- Case 0: no indexes
TRUNCATE bench_idx;
\timing on
INSERT INTO bench_idx (a, b, c, e)
SELECT (random()*1000)::int, md5(random()::text), random()*100, md5(random()::text)
FROM generate_series(1, 100000);

-- Case 1: 1 index
CREATE INDEX ON bench_idx(a);
TRUNCATE bench_idx;
INSERT INTO bench_idx (a, b, c, e)
SELECT (random()*1000)::int, md5(random()::text), random()*100, md5(random()::text)
FROM generate_series(1, 100000);

-- Case 3: 3 indexes
CREATE INDEX ON bench_idx(b);
CREATE INDEX ON bench_idx(c);
TRUNCATE bench_idx;
INSERT INTO bench_idx (a, b, c, e)
SELECT (random()*1000)::int, md5(random()::text), random()*100, md5(random()::text)
FROM generate_series(1, 100000);

-- Case 5: 5 indexes
CREATE INDEX ON bench_idx(d);
CREATE INDEX ON bench_idx(e);
TRUNCATE bench_idx;
INSERT INTO bench_idx (a, b, c, e)
SELECT (random()*1000)::int, md5(random()::text), random()*100, md5(random()::text)
FROM generate_series(1, 100000);

Exercise 3: measure an index's bloat

Take a table in your environment with enough UPDATEs/DELETEs. Measure the index size. After a REINDEX CONCURRENTLY, measure again. Compare.

-- Setup: table with a simulation of frequent updates
DROP TABLE IF EXISTS bloat_demo;
CREATE TABLE bloat_demo (id SERIAL PRIMARY KEY, value INTEGER);
INSERT INTO bloat_demo (value) SELECT g FROM generate_series(1, 200000) g;
CREATE INDEX idx_bloat_value ON bloat_demo(value);

-- Initial size
SELECT pg_size_pretty(pg_relation_size('idx_bloat_value'));
-- Example: 6 MB

-- We generate bloat with many updates
DO $$
BEGIN
  FOR i IN 1..10 LOOP
    UPDATE bloat_demo SET value = value + 1;
  END LOOP;
END $$;

-- Size after updates
SELECT pg_size_pretty(pg_relation_size('idx_bloat_value'));
-- Example: 35 MB (5x more due to accumulated bloat)

-- VACUUM doesn't compact the index physically
VACUUM bloat_demo;
SELECT pg_size_pretty(pg_relation_size('idx_bloat_value'));
-- Example: 35 MB (same; VACUUM marks reusable space but doesn't return it)

-- REINDEX does compact it
REINDEX INDEX CONCURRENTLY idx_bloat_value;
SELECT pg_size_pretty(pg_relation_size('idx_bloat_value'));
-- Example: 6 MB (back to the "clean" size)

Exercise 4: decide candidates to remove

You have this output from pg_stat_user_indexes:

Stats reset 4 weeks ago, representative traffic. Which ones to remove?

Exercise 5: plan a REINDEX in production

You have an index idx_orders_customer_status of 800 MB with an estimated bloat of 60%. The table receives 1,000 INSERTs/min during business hours (9-18h). Free disk space: 1 GB.

Design the plan for the REINDEX CONCURRENTLY: when you run it, what precautions you take, what you monitor.

REINDEX INDEX CONCURRENTLY idx_orders_customer_status;

Exercise 6: apply it to your environment

Take a real database (your test app's or a toy one). Run:

1. An audit of unused indexes.
2. Measure the size of the 5 largest indexes.
3. Identify at least one that's a candidate to remove.
4. Document your decision: remove, observe longer, or keep.

## Index audit: [database] — date: 2026-05-15

### Stats reset: 2026-04-01 (45 days ago, representative traffic)

### Unused indexes (idx_scan = 0):

| Index | Table | Size | Decision |
|-------|-------|------|----------|
| idx_orders_country | orders | 45 MB | Remove — the filter-by-country feature was removed in 2025 |
| idx_users_legacy_id | users | 12 MB | Remove — migration to UUID, old idx |
| idx_logs_severity_old | logs | 80 MB | Rename TOREMOVE — observe 2 more weeks, there's a monthly job that might use it |

### Top 5 largest indexes:

| Index | Size | idx_scan | Decision |
|-------|------|----------|----------|
| idx_events_user_created | 1.2 GB | 5,000,000 | Keep — intensive usage |
| idx_orders_status_created | 800 MB | 1,200,000 | Keep |
| idx_logs_severity_old | 80 MB | 0 | Rename (see above) |
| idx_orders_country | 45 MB | 0 | Remove |
| idx_users_legacy_id | 12 MB | 0 | Remove |

### Actions:

```sql
DROP INDEX CONCURRENTLY idx_orders_country;
DROP INDEX CONCURRENTLY idx_users_legacy_id;
ALTER INDEX idx_logs_severity_old RENAME TO idx_logs_severity_old_TOREMOVE;

</details>

---

## Summary and next step

In this capsule you learned:

- **Each index has a cost**: it slows down INSERT/UPDATE/DELETE 15-30% per index. 5 indexes ≈ 2-4x slower.
- `pg_stat_user_indexes` tells you how many times each index was used. `idx_scan = 0` after a representative period = a candidate to remove.
- **Bloat** accumulates with UPDATEs/DELETEs. `VACUUM` frees space for reuse but doesn't compact. `REINDEX CONCURRENTLY` does, without blocking writes.
- **REINDEX CONCURRENTLY** requires ~2x the index size on disk temporarily.
- **Exclude PKs and UNIQUE** when evaluating candidates to remove — they can have `idx_scan = 0` but be structural.
- **Always use CONCURRENTLY in production** for `DROP INDEX` and `REINDEX`.
- **Periodic audit** (every 1-3 months) removes invisible debt: dead indexes, high bloat, write cost above what's needed.

Before moving on, you should be able to:

- Run the unused-index audit with `pg_stat_user_indexes`.
- Quantify the cost of an index in INSERT with a simple benchmark.
- Identify candidates to remove and candidates to reindex.
- Plan a `REINDEX CONCURRENTLY` in production with precautions (space, timing, monitoring, plan B).

**Next capsule — Module project: Indexing the Bookstore.** The moment to apply everything you've learned has arrived. You'll receive five problematic queries over a simplified version of the Bookstore API. For each one: you capture the initial plan, diagnose what's missing, design the appropriate index (composite/covering/partial/expression as the case may be), validate the plan afterward, and document your decision in `INDICES.md`. It's the direct connection with the final project of module 8 and the deliverable you take away as the module's portfolio.

---

## Resources

1. [PostgreSQL Documentation — pg_stat_user_indexes](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-USER-INDEXES-VIEW) — the official reference for the view.
2. [PostgreSQL Documentation — REINDEX](https://www.postgresql.org/docs/16/sql-reindex.html) — full syntax, CONCURRENTLY options, troubleshooting.
3. [PostgreSQL Wiki — Index Maintenance](https://wiki.postgresql.org/wiki/Index_Maintenance) — the canonical collection of audit queries: unused indexes, bloat, duplicates.
4. [PostgreSQL Wiki — Show Database Bloat](https://wiki.postgresql.org/wiki/Show_database_bloat) — the standard query for estimating index and table bloat.
5. [Hubert "depesz" Lubaczewski — "Find unused indexes"](https://www.depesz.com/2010/07/06/find-unused-indexes/) — the classic post on auditing unused indexes.
6. [pgBadger](https://pgbadger.darold.net/) — a PostgreSQL log analysis tool that includes index usage reports.
7. [Bruce Momjian — "Indexing Mistakes"](https://momjian.us/main/writings/pgsql/index_mistakes.pdf) — a section on over-indexing and index maintenance.
8. [Tomas Vondra — "Reducing index bloat without LOCKs"](https://www.2ndquadrant.com/en/blog/reducing-index-bloat/) — a technical analysis of how REINDEX CONCURRENTLY works internally.

---

*Module 3 — Database Performance & Query Tuning Guide*