# What is table bloat in PostgreSQL and how do I fix it?

> **Quick answer:** Bloat is dead row versions left behind by `UPDATE` and
> `DELETE` under PostgreSQL's MVCC. Autovacuum reclaims that space for **reuse
> inside the table**, the file usually stays the same size.  To shrink a bloated
> file you need `VACUUM FULL` (an `ACCESS EXCLUSIVE` rewrite) or an online tool
> like `pg_repack`.

## Why bloat happens: MVCC

PostgreSQL never updates a row in place.  Under **MVCC** (multi-version concurrency
control):

- An `UPDATE` writes a **new** row version and marks the old one dead.
- A `DELETE` marks the row dead.

Dead tuples are not removed immediately, they linger so concurrent transactions
can still see the version they started with.  On a heavily updated or deleted
table, dead tuples pile up faster than they are cleaned, and the table grows with
dead space.  That is bloat: disk and cache spent on rows nobody can see.

## How do I measure it?

Quick ratio from the stats view:

```sql
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup, 0), 3) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
```

For a precise measurement, the `pgstattuple` extension reports actual
`dead_tuple_percent` and `free_percent`:

```sql
SELECT * FROM pgstattuple('orders');
```

A high dead-tuple ratio on a large table is your signal.

## The key surprise: VACUUM does not shrink the file

This trips people up constantly:

- **Autovacuum / plain `VACUUM`** removes dead tuples and marks the space
  **reusable within the table**. The file usually stays the same size on disk;
  future inserts and updates reuse the freed space.  One exception: vacuum will
  truncate fully empty pages at the *end* of the table and return those to the
  OS (unless `vacuum_truncate` is off).  It runs online (`SHARE UPDATE EXCLUSIVE`, reads and writes continue).
- **`VACUUM FULL`** rewrites the whole table into a fresh file and **does** return
  space to the OS, but it holds an **`ACCESS EXCLUSIVE`** lock for the entire
  rewrite, blocking all reads and writes.  On a big table that is an outage.

So for ongoing health you want autovacuum keeping up (freed space gets reused).  You
only need `VACUUM FULL` when a table bloated badly and you must reclaim disk, and
even then, `pg_repack` does the same rewrite **online**, holding `ACCESS EXCLUSIVE`
only briefly at setup and at the final swap (not for the whole copy).

Indexes bloat too; rebuild them with `REINDEX` (use `REINDEX CONCURRENTLY`,
available since PostgreSQL 12, to avoid the strong lock).

## How do I prevent bloat?

- **Keep autovacuum ahead of write volume.** The defaults are conservative for a
  busy table; lower `autovacuum_vacuum_scale_factor` / raise the cost limits for
  hot tables so cleanup keeps pace.
- **Watch `n_dead_tup` and `last_autovacuum`** on your most-updated tables.
- **Batch large deletes/updates** so a single statement does not create a huge
  burst of dead tuples autovacuum then struggles to catch up on.

## Why does this catch aI-generated code out?

Your AI coding agent (Claude Code, Cursor) writes the `UPDATE` or the bulk
`DELETE` that is correct for the task, with no view of how often it runs or how the
table is vacuumed.  A nightly "update every row's `status`" job is fine in
isolation and a bloat machine in production.  Whether autovacuum keeps up is an
operational property of your write volume and settings, invisible in the source.

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor)
the real bloat picture.`n_dead_tup` ratios, last-autovacuum times, which tables
are growing dead space, so it can flag a write pattern that outruns autovacuum
and explain whether you need tuning, a plain vacuum, or a `pg_repack`. It surfaces
and explains; it does not run maintenance against production.
[Get started free →](https://app.dbgorilla.com/signup)

## FAQ

**What causes table bloat?**
MVCC: `UPDATE` writes a new row version and marks the old dead; `DELETE` marks the
row dead.  Dead tuples accumulate until vacuum reclaims them.

**Does VACUUM return disk space to the OS?**
Mostly no, plain `VACUUM`/autovacuum marks space reusable inside the table.  The
one exception is truncation: it returns fully empty pages at the *end* of the
table to the OS (unless `vacuum_truncate` is off).  Space trapped in the middle of
the file needs `VACUUM FULL` or `pg_repack`.

**What is the difference between VACUUM and VACUUM FULL?**
`VACUUM` reclaims dead tuples for reuse, online. `VACUUM FULL` rewrites the table
and returns space to the OS but holds an `ACCESS EXCLUSIVE` lock the whole time.

**How do I measure bloat?**
`n_dead_tup` vs `n_live_tup` in `pg_stat_user_tables` for a quick ratio, or
`pgstattuple` for exact `dead_tuple_percent` / `free_percent`.

## Related

- [Why did my PostgreSQL query suddenly get slow?](https://www.dbgorilla.com/learn/postgres/why-did-my-postgres-query-suddenly-get-slow/)
- [How to fix idle in transaction connections in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-fix-idle-in-transaction-connections/)
- [Which ALTER TABLE statements lock a PostgreSQL table?](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-table/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)