---
title: "Postgres index bloat and slow queries"
platform: "ChatGPT"
model: "GPT-5"
source: "https://chatgpt.com/c/demo-postgres"
exported: "2026-10-18T09:42:00Z"
messages: 4
words: 344
tags:
  - ai-export
  - chatgpt
  - gpt-5
---
# Postgres index bloat and slow queries

**Source:** ChatGPT · [chatgpt.com/c/demo-postgres](https://chatgpt.com/c/demo-postgres)
**Model:** GPT-5
**Exported:** 2026-10-18 17:42
**Messages:** 4 · **Words:** 344

---

## You <sub>2026-10-18 17:38</sub>

My analytics queries went from 300ms to 4s over the last three months. The table only grows by around 40k rows a day. What should I check first?

---

## Assistant <sub>2026-10-18 17:38</sub>

**Thought for 8s**

> A 13× slowdown on a table growing ~1.2M rows a month is too steep for data volume alone. The most likely cause is a plan change: an index that the planner no longer trusts, or statistics that stopped being representative.

Before you touch the query, measure *where* the time goes. A 13× slowdown on a table growing ~1.2M rows per month usually means the planner stopped using an index — not that the data got too big.

### 1. Confirm the plan changed

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', created_at) AS day, count(*)
FROM events
WHERE created_at > now() - interval '30 days'
GROUP BY 1;
```

If you see a `Seq Scan` where an `Index Scan` used to be, the index is either bloated or the statistics are stale.

### 2. Check bloat and statistics

| Signal | Healthy | What it means |
| --- | --- | --- |
| Dead tuples | < 5% | Above 20% the planner starts avoiding the index |
| Last analyze | < 1 day | Stale stats produce bad row estimates |
| Index size | ~ table size | Bloated indexes are read page by page |

> Rule of thumb: if `pg_stat_user_tables.n_dead_tup` is climbing faster than your inserts, autovacuum is losing the race.

### 3. Fix it in this order

- **ANALYZE** the table — free, instant, often enough.
- Raise the table's autovacuum frequency.
- Only then rebuild the index.

Useful references: the [PostgreSQL vacuuming guide](https://www.postgresql.org/docs/current/routine-vacuuming.html) and `pgstattuple` for real bloat numbers.

---

## You <sub>2026-10-18 17:41</sub>

Can I rebuild the index without locking writes?

---

## Assistant <sub>2026-10-18 17:41</sub>

Yes — `REINDEX CONCURRENTLY` builds a new index beside the old one and swaps it in. It takes longer and needs free disk space, but writes keep flowing.

```sql
REINDEX INDEX CONCURRENTLY events_created_at_idx;
```

Two caveats worth knowing before you run it:

1. It cannot run inside a transaction block.
2. If it fails, it leaves an `INVALID` index behind — drop it and retry.

For a 40k/day table this should finish in under a minute. Schedule it for a low-traffic window anyway, and watch `pg_stat_progress_create_index`.

---

*Generated with Threadmark · 2026-10-23 22:42*