# Why is my table bloated when autovacuum runs?

> **Quick answer:** Measure before you tune. The dead row counters are estimates
> and can read backwards, so decompose the size first, then read `backend_xmin`
> and the `xmin` of every replication slot. A held horizon and a cancelled
> autovacuum both look like "autovacuum is not keeping up" and neither is fixed
> by a scale factor.

## What was measured, and where

Every number on this page was run, and nearly all of it came from one container,
so you can tell which claims are about PostgreSQL and which are about a
particular table.

| | |
|---|---|
| server | PostgreSQL 18.6, image `postgres:18.6` |
| container | `dbg-pgbloat`, Docker on an 8 vCPU host |
| settings | defaults, except `wal_level = logical` so logical slots could be tested |
| standby | `dbg-pgbloat-r`, same image, streaming from that primary, `hot_standby_feedback = on` |
| date | 2026-09-20 |

Some measurements needed settings no default server has, and they are labelled
where they appear. They ran in a second container, `dbg-pgwrap`, same PostgreSQL
18.6 image, same day, 2026-09-20, with `autovacuum_naptime = 5s` so a run could
be watched, and `autovacuum_freeze_max_age = 100000` so wraparound could be
reached in a minute rather than in months.

One re-run is labelled `dbg-pgbloat2`, a third container on the same PostgreSQL
18.6 image on the same day, 2026-09-20. It is the count of rows released when
the logical slot was dropped, in the replication slot section below.

## What is the problem?

PostgreSQL never updates a row in place. An `UPDATE` writes a new version and
marks the old one dead; a `DELETE` just marks it dead. **Vacuum** removes them.

Three different things get called bloat, and confusing them is why tuning often
changes nothing:

1. **Dead rows vacuum is not allowed to remove.** Something is holding the
   cleanup horizon, and no vacuum setting overrides that.
2. **Free space inside the file.** Vacuum made it reusable and left it there.
   If the table has reached a steady state this is not a problem to fix.
3. **Space that is not in the heap.** Indexes and TOAST are usually most of a
   large table, and the usual diagnostic query does not look at them.

Sort out which one you have before changing anything.

### Vacuum does sometimes shrink the file

The common shorthand is that vacuum never returns space to the operating system.
That is nearly always true. The exception matters because it makes disk usage
look non-deterministic.

The PostgreSQL 18 documentation states the exception directly:

> However, it will not return the space to the operating system, except in the
> special case where one or more pages at the end of a table become entirely
> free and an exclusive table lock can be easily obtained.

That sentence is from [Routine Vacuuming in the PostgreSQL 18
documentation](https://www.postgresql.org/docs/18/routine-vacuuming.html), read
2026-09-20.

Deleting the newest rows is one way to trigger the exception. Three 400,000 row
tables, identical except for what was deleted and one storage setting, PostgreSQL
18.6 in container `dbg-pgbloat`, 2026-09-20, in bytes as `pg_relation_size`
reported them:

| table | what was deleted | heap before | heap after plain `VACUUM` |
|---|---|---|---|
| `t_tail` | the newest 200,000 rows | 99,303,424 | 49,651,712 |
| `t_mid` | every second row | 99,303,424 | 99,303,424 |
| `t_notrunc`, `vacuum_truncate = off` | the newest 200,000 rows | 99,303,424 | 99,303,424 |

Half the file went back to the operating system in the first row and none did in
the second, from the same command on the same server. The third row is the same
deletion as the first with truncation turned off at the table level, which shows
that the difference was truncation rather than luck.

The index did not move in any of the three: 9,003,008 bytes before and after,
same tables, same PostgreSQL 18.6 container, 2026-09-20. Vacuum removes index
entries; it does not give index space back.

## How do I confirm it?

In this order. Each step is cheaper and less certain than the one after it, and
the common mistake is to stop at step two and start tuning.

### 1. Decompose the size

Before anything else, find out what the space is:

<!-- sql-check: postgres -->
```sql
SELECT c.relname,
       pg_size_pretty(pg_relation_size(c.oid))  AS heap,
       pg_size_pretty(pg_indexes_size(c.oid))   AS indexes,
       pg_size_pretty(coalesce(pg_total_relation_size(c.reltoastrelid), 0)) AS toast,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 20;
```

A 20,000 row table with one incompressible
text column per row, PostgreSQL 18.6 in container `dbg-pgbloat`, 2026-09-20,
measured 1,048,576 bytes of heap, 466,944 bytes of indexes and 165,724,160 bytes
of TOAST. The heap is under one percent of it. Deleting half the rows and
running `VACUUM` left all three numbers unchanged on that same server and day,
and `pg_stat_user_tables` reported `n_dead_tup` of 0 throughout.

If the heap is small and the total is large, no amount of autovacuum tuning is
the answer, and the question becomes which index or which TOAST table.

### 2. Use the counters to choose where to look, not to conclude

<!-- sql-check: postgres -->
```sql
SELECT relname,
       n_live_tup,
       n_dead_tup,
       n_mod_since_analyze,
       n_ins_since_vacuum,
       last_vacuum,
       last_autovacuum,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
```

The PostgreSQL 18 documentation describes `n_live_tup` as:

> Estimated number of live rows

That is from [The Cumulative Statistics System in the PostgreSQL 18
documentation](https://www.postgresql.org/docs/18/monitoring-stats.html), read
2026-09-20. The word that matters is "estimated".

On PostgreSQL 18.6 in
container `dbg-pgbloat`, 2026-09-20, two tables named `ca` and `cb` were each
built with 400,000 rows and each had its newest 200,000 rows deleted and then a
plain `VACUUM` run, the only difference being where the two statements ran. Both
tables were polled every 500 ms for eight seconds afterwards on that same
PostgreSQL 18.6 container on 2026-09-20, and both were read with `pgstattuple`
at the end:

| table | `DELETE` and `VACUUM` | `pg_stat_user_tables` | `pgstattuple` |
|---|---|---|---|
| `ca` | same session | 0 live, 200,000 dead | 200,000 live, 0 dead |
| `cb` | separate sessions | 198,413 live, 0 dead | 200,000 live, 0 dead |

Both files measured 23,412,736 bytes on that same PostgreSQL 18.6 container,
`dbg-pgbloat`, on 2026-09-20. Physically they are the same table twice.

In the `ca` row, `pg_stat_user_tables` reported zero live rows and two hundred
thousand dead ones for a table that had just been vacuumed clean. The reading was
stable: every one of the seventeen samples across eight seconds gave the same
answer. That is the reading the usual advice interprets as
"vacuum runs and cannot win".

What decides it is the session, not timing. A backend accumulates its statistics
and flushes them, so when the `DELETE` and the `VACUUM` run in one session the
delete's tally can be applied after the vacuum's, leaving the counters
describing a table state that no longer exists. Run them from separate sessions
and the order comes out right. The obvious explanation, that the counters are
briefly inconsistent while the two statements race each other, is wrong: polling
every 500 ms on that PostgreSQL 18.6 container, `dbg-pgbloat`, on 2026-09-20
showed the reading stable rather than transient.

Treat these counters as a way to pick which table to investigate and never as
evidence about that table.

### 3. Measure the table itself

<!-- sql-check: skip needs the pgstattuple extension, which the check server does not install -->
```sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT tuple_count,
       dead_tuple_count,
       round(dead_tuple_percent::numeric, 2) AS dead_tuple_percent,
       round(free_percent::numeric, 2)       AS free_percent
FROM pgstattuple('orders');
```

It reads every page, so it is the slow and correct answer. Run it on the tables
step 2 pointed at, off-peak or on a replica.

Free space is what it separates out. A 200,000 row table with every second row
deleted and vacuumed, PostgreSQL 18.6 in container `dbg-pgbloat`, 2026-09-20,
reported 0 dead tuples and 44.58 percent free, with the heap still at 7,249,920
bytes. Nothing is wrong with that table and nothing needs tuning, because the
space will be reused.

For indexes, `pgstatindex` answers the same question. The primary key of
that table, same server and day, reported an `avg_leaf_density` of 45.10
with its size unchanged at 4,513,792 bytes.

### 4. Watch the vacuum that is running

<!-- sql-check: postgres -->
```sql
SELECT p.pid,
       p.phase,
       p.heap_blks_scanned,
       p.heap_blks_total,
       p.dead_tuple_bytes,
       p.max_dead_tuple_bytes,
       left(a.query, 40) AS query
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid);
```

For runs that have finished, set `log_autovacuum_min_duration` and read
the log. At `0` it logs every autovacuum. A cancelled autovacuum is logged as an
`ERROR` whatever this is set to, which is how the cancellation below is caught.

### 5. Find what is holding the horizon

This step is often skipped, and it decides whether tuning can help at all. Vacuum
may remove a dead row only when no transaction could still need it. Whatever sets
that limit blocks cleanup in other tables too, not only its own: a session's
snapshot holds back every table in its database.

**`xact_start` is not the field.** Age alone is not either. Three sessions, all
open, all reported as `idle in transaction` with the same transaction age,
PostgreSQL 18.6 in container `dbg-pgbloat`, 2026-09-20:

| session | isolation | statements run so far | `backend_xmin` |
|---|---|---|---|
| A | read committed | `BEGIN` only | null |
| B | read committed | `BEGIN`, one `SELECT` | null |
| C | repeatable read | `BEGIN`, one `SELECT` | 782 |

With all three open, `VACUUM` removed none of the 50,000 dead rows and reported
`removable cutoff: 782`. Closing only session C and repeating the same command
on the same server removed all 50,000, while A and B stayed open. The field that
predicted it was `backend_xmin`, not `xact_start` and not the state string.

So read the horizon itself:

<!-- sql-check: postgres -->
```sql
SELECT pid,
       state,
       backend_xmin,
       age(backend_xmin)   AS xmin_age,
       now() - xact_start  AS xact_age,
       left(query, 60)     AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;
```

A long `active` query holds the horizon exactly as an idle transaction does. An
`idle in transaction` session with a null `backend_xmin` is a lock problem and a
connection problem, but it is not this problem.

### 6. Read the slots by their xmin, not their active flag

<!-- sql-check: postgres -->
```sql
SELECT slot_name,
       slot_type,
       active,
       xmin,
       age(xmin)          AS xmin_age,
       catalog_xmin,
       age(catalog_xmin)  AS catalog_xmin_age,
       restart_lsn,
       wal_status
FROM pg_replication_slots
ORDER BY greatest(age(xmin), age(catalog_xmin)) DESC NULLS LAST;
```

The PostgreSQL 18 documentation names those two fields for this:

> Drop any old replication slots. Use `pg_replication_slots` to find slots where
> `age(xmin)` or `age(catalog_xmin)` is large.

That is from [Routine Vacuuming in the PostgreSQL 18
documentation](https://www.postgresql.org/docs/18/routine-vacuuming.html), read
2026-09-20.

"Inactive slot" is not a diagnosis on its own. Three inactive slots gave three
different answers, PostgreSQL 18.6 in container `dbg-pgbloat`, 2026-09-20:

| inactive slot | `xmin` | `catalog_xmin` | what it blocked |
|---|---|---|---|
| physical, created and never used | null | null | nothing: 50,000 dead rows removed normally |
| logical, created and never read | null | 756 | 2,000 dead rows in `pg_class`, none in the user table |
| physical, left behind by a standby that had used `hot_standby_feedback` | 831 | null | 20,000 dead rows in the user table |

The logical slot pinned the removable cutoff at 756 for `pg_class` and dropping
it released almost all of those rows immediately, same server and day.

The shortfall looks like rows left behind, but it is not. Re-run on PostgreSQL 18.6 in container
`dbg-pgbloat2`, 2026-09-20, with the slot holding 2,000 dead rows in `pg_class`:
the vacuum after the drop removed 1,997 and reported none still dead, and a
third vacuum with nothing else changed removed none. Three rows are unaccounted
for, and this page does not know where they went: a vacuum that had left work
behind would have found it on the third pass, and the third pass found nothing.
Expect a catalog vacuum to remove slightly fewer rows than were dead when you
counted them, and do not read the difference as a held horizon. The third slot
held the user table's cutoff at 831 with the standby shut down and no walsender
connected, and dropping it released all 20,000.

Check the fields rather than the active flag: a slot whose `xmin` or `catalog_xmin` is
set holds a horizon whether or not anything is connected to it, and a slot with
both null does not.

### 7. Check the standbys

A replica running long reports can hold the primary's horizon. The PostgreSQL 18
documentation says so about `hot_standby_feedback`:

> If you do this, you should note that this will delay cleanup of dead rows on
> the primary, which may result in undesirable table bloat.

That sentence is from [Hot Standby in the PostgreSQL 18
documentation](https://www.postgresql.org/docs/18/hot-standby.html), read
2026-09-20.

Where it shows up on the primary depends on whether the standby uses a slot. One
standby, one repeatable read query held open on it, `hot_standby_feedback = on`
throughout, PostgreSQL 18.6 in containers
`dbg-pgbloat` and `dbg-pgbloat-r`, 2026-09-20:

| standby configuration | `pg_stat_replication.backend_xmin` | `pg_replication_slots.xmin` | dead rows vacuum could remove |
|---|---|---|---|
| streaming through slot `standby1` | null | 830 | 0 of 50,000 |
| streaming with no slot | 831 | not applicable | 50,000 of 50,000, and 0 of 10,000 newer ones |

Read both:

<!-- sql-check: postgres -->
```sql
SELECT application_name,
       state,
       backend_xmin,
       age(backend_xmin) AS xmin_age
FROM pg_stat_replication
ORDER BY age(backend_xmin) DESC NULLS LAST;
```

Prepared transactions are the last holder, and they survive restarts:

<!-- sql-check: postgres -->
```sql
SELECT gid, prepared, owner, database, age(transaction) AS xid_age
FROM pg_prepared_xacts
ORDER BY prepared;
```

## Autovacuum runs and does not finish

Everything above is about vacuum not being allowed to remove rows. This section
is about vacuum not getting to them.

### It is being cancelled

Autovacuum takes a `SHARE UPDATE EXCLUSIVE` lock, and unless it is running to
prevent wraparound it yields to anything that conflicts. The PostgreSQL 18 documentation states the consequence:

> Regularly running commands that acquire locks conflicting with a `SHARE UPDATE
> EXCLUSIVE` lock (e.g., ANALYZE) can effectively prevent autovacuums from ever
> completing.

That is from [Routine Vacuuming in the PostgreSQL 18
documentation](https://www.postgresql.org/docs/18/routine-vacuuming.html), read
2026-09-20.

What that looks like on the server, measured in container `dbg-pgwrap`,
PostgreSQL 18.6 with `autovacuum_naptime = 5s`, 2026-09-20: a 300,000 row table
with 150,000 dead rows, autovacuum running, and one
`LOCK TABLE ... IN ACCESS EXCLUSIVE MODE` from another session. The lock was
granted at once. On that same `dbg-pgwrap` container, PostgreSQL 18.6,
2026-09-20, `pg_stat_progress_vacuum` was sampled just before taking the lock
and read 376 of 12,500 blocks scanned, while the log line below names block 596,
which is where it had reached by the time it was killed. The two numbers are a
before and an after rather than a disagreement:

```
ERROR:  canceling autovacuum task
CONTEXT:  while scanning block 596 of relation "public.churn"
	automatic vacuum of table "postgres.public.churn"
```

Afterwards, on that same server and day, `pg_stat_all_tables` reported
`n_dead_tup` of 150,000, `autovacuum_count` of 0 and `last_autovacuum` null.

That combination is misleading. A null `last_autovacuum` usually gets read as
never triggered, so lower the scale factor, and here autovacuum had been
triggered, had started, and had been thrown off. Only the log distinguishes
them, and the cancellation is logged as an `ERROR` at the default settings.

### It is not triggered when you think

The update and delete path in PostgreSQL 18 is a capped threshold, not an
uncapped one:

<!-- sql-check: pg 18+ -->
```sql
SELECT c.relname,
       c.reltuples::bigint AS estimated_rows,
       least(current_setting('autovacuum_vacuum_max_threshold')::bigint,
             current_setting('autovacuum_vacuum_threshold')::bigint
               + current_setting('autovacuum_vacuum_scale_factor')::numeric * c.reltuples
       )::bigint AS dead_rows_before_autovacuum
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
ORDER BY c.reltuples DESC
LIMIT 20;
```

Read out of a default PostgreSQL 18.6 server in container `dbg-pgbloat` on
2026-09-20: `autovacuum_vacuum_threshold` 50, `autovacuum_vacuum_scale_factor`
0.2, `autovacuum_vacuum_max_threshold` 100000000. The cap only changes the
answer once the threshold and the scale factor together would exceed it. A 100,000,000 row table is
below that line and waits for 20,000,050 dead rows, which is the threshold of 50
on top of the scale factor's 20,000,000. A 1,000,000,000 row table is above it
and waits for 100,000,000 rather than the 200,000,050 the threshold and scale
factor would otherwise give between them, and that is the difference the cap
makes. `reltuples` is itself an estimate, so treat the trigger point as
approximate in both directions.

There are two other ways in, and neither counts dead rows.

**Inserts trigger vacuum too.** `autovacuum_vacuum_insert_threshold` is 1000 and
`autovacuum_vacuum_insert_scale_factor` is 0.2 on a default PostgreSQL 18.6
server, read from container `dbg-pgbloat` on 2026-09-20. A table that had 5,000
rows inserted and nothing updated or deleted was autovacuumed within 25 seconds
in container `dbg-pgwrap`, same PostgreSQL 18.6 and day, with `n_dead_tup` of 0
the whole time.

**Freezing is a separate job with its own trigger.** In container `dbg-pgwrap`,
PostgreSQL 18.6 with `autovacuum_freeze_max_age` set to 100000, 2026-09-20, a
table created with `autovacuum_enabled = off` reached a `relfrozenxid` age of
150,002 and was vacuumed anyway:

```
LOG:  automatic aggressive vacuum to prevent wraparound of table "postgres.public.frozen_off": index scans: 0
```

Its age went to 1 on that same server and day. Aggressive vacuum activity on a
table nobody deletes from is this, not a cleanup problem:

<!-- sql-check: postgres -->
```sql
SELECT c.relname,
       age(c.relfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;
```

### It is throttled, or there are not enough workers

Autovacuum pauses to stay out of the way, governed by
`autovacuum_vacuum_cost_limit` and `autovacuum_vacuum_cost_delay`. Raising the
cost limit lets each run do more work before it pauses. Do it deliberately and
watch the input and output load, because the throttle exists for a reason.

Throttling is one cause of a slow run and not the only one. Lock cancellation,
index cleanup, aggressive freezing and the rate the workload creates dead rows
all bound it too, which is why the log is worth more than the setting.

The worker limit is per cluster, not per database. The PostgreSQL 18
documentation says:

> A maximum of autovacuum_max_workers worker processes are allowed to run at the
> same time.

That sentence is from [Routine Vacuuming in the PostgreSQL 18
documentation](https://www.postgresql.org/docs/18/routine-vacuuming.html), read
2026-09-20.

On a default PostgreSQL 18.6 server in container `dbg-pgbloat`, read 2026-09-20,
`autovacuum_max_workers` is 3 and `autovacuum_worker_slots` is 16. The second
one is new in PostgreSQL 18 and is the pool the first draws from, so raising
`autovacuum_max_workers` above `autovacuum_worker_slots` does not get you more
workers. A large table count is not by itself evidence of a backlog: eligibility,
worker saturation and the durations in the log are.

<!-- sql-check: postgres -->
```sql
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('autovacuum_vacuum_cost_limit', 'autovacuum_vacuum_cost_delay',
               'autovacuum_max_workers', 'autovacuum_naptime',
               'log_autovacuum_min_duration')
ORDER BY name;
```

## When it is not a vacuum problem at all

### The workload creates more dead rows than it needs to

An update that changes no indexed column and finds room on its own page is a
**HOT update**: it leaves no new index entry and its dead version can be cleaned
up without touching an index. Two tables, identical columns, identical indexes,
identical `fillfactor = 70`, 20,000 rows each, 200,000 updates each, PostgreSQL
18.6 in container `dbg-pgbloat`, 2026-09-20. The only difference is whether the
updated column has an index on it:

| table | column updated | HOT updates | heap after | indexes before | indexes after |
|---|---|---|---|---|---|
| `hot_unindexed_col` | unindexed | 62,384 of 200,000 | 10,010,624 | 622,592 | 2,981,888 |
| `hot_indexed_col` | indexed | 0 of 200,000 | 10,010,624 | 622,592 | 4,595,712 |

The heap came out the same. The index side did not, on that same PostgreSQL
18.6 container on 2026-09-20: 2,981,888 bytes against 4,595,712 bytes.
Dropping an index nothing queries, or not updating a column that is indexed, is
a durable fix where a lower scale factor is a workaround.

`fillfactor` is the other half of it. On that same PostgreSQL 18.6 container on
2026-09-20, a table packed at the default 100 and then updated 200,000 times
recorded 0 HOT updates, against 62,384 for the identical workload at
`fillfactor = 70`. Free space on the page is what makes a HOT update possible,
which is also why the first pass over a freshly loaded table is the worst case.

<!-- sql-check: postgres -->
```sql
SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(n_tup_hot_upd * 100.0 / 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
LIMIT 20;
```

### Autovacuum cannot see some of your tables

**Temporary tables.** The PostgreSQL 18 documentation states it:

> Temporary tables cannot be accessed by autovacuum. Therefore, appropriate
> vacuum and analyze operations should be performed via session SQL commands.

That is from [Routine Vacuuming in the PostgreSQL 18
documentation](https://www.postgresql.org/docs/18/routine-vacuuming.html), read
2026-09-20.

Measured on PostgreSQL 18.6 in container `dbg-pgbloat`, 2026-09-20: a temporary
table with 100,000 rows and half of them deleted still reported 50,000 dead rows
and a null `last_autovacuum` several minutes later, holding 3,629,056 bytes. A
session that churns temporary tables has to vacuum them itself.

**Partitioned parents, for `ANALYZE`.** The documentation again:

> Unfortunately, this means that autovacuum does not run `ANALYZE` on
> partitioned tables, and this can cause suboptimal plans for queries that
> reference partitioned table statistics.

That sentence is from [Routine Vacuuming in the PostgreSQL 18
documentation](https://www.postgresql.org/docs/18/routine-vacuuming.html), read
2026-09-20.

On PostgreSQL 18.6 in container `dbg-pgbloat`, 2026-09-20, both partitions of a
two-partition table were autovacuumed and autoanalyzed within three naptimes,
and the parent had a null `last_autoanalyze` and a `reltuples` of -1, meaning it
had never been analyzed. The partitions are vacuumed; the parent's statistics are
your job.

## How do I get the space back?

Match the remedy to what the steps above told you.

| what you found | what to do |
|---|---|
| dead rows with a horizon holder | remove the holder. Nothing else helps. |
| dead rows, no holder, autovacuum cancelled in the log | stop the conflicting command, or schedule it |
| dead rows, no holder, threshold not reached | lower the scale factor on that table |
| free space, table at steady state | nothing. It will be reused. |
| heap capacity you need returned to the disk | `VACUUM FULL` or `CLUSTER` in a window, or `pg_repack` |
| index bloat with a healthy heap | `REINDEX CONCURRENTLY` that index |
| the whole table is emptied periodically | `TRUNCATE` instead of `DELETE` |

That last row is the documentation's own advice:

> If you have a table whose entire contents are deleted on a periodic basis,
> consider doing it with `TRUNCATE` rather than using `DELETE` followed by
> `VACUUM`.

That sentence is from [Routine Vacuuming in the PostgreSQL 18
documentation](https://www.postgresql.org/docs/18/routine-vacuuming.html), read
2026-09-20.

### What VACUUM FULL costs

<!-- sql-check: skip rewrites a fixture table under an exclusive lock -->
```sql
VACUUM FULL orders;
```

It rewrites the table and rebuilds every index, under an `ACCESS EXCLUSIVE` lock
for the whole operation. One table, PostgreSQL 18.6 in container `dbg-pgbloat`,
2026-09-20:

| | heap | indexes | total | index relfilenode |
|---|---|---|---|---|
| before | 10,010,624 | 4,595,712 | 14,647,296 | 16470 |
| after `VACUUM FULL` | 1,277,952 | 622,592 | 1,908,736 | 16477 |

The changed relfilenode is the index rebuild, and the new copy exists alongside
the old one until the command finishes, which is where the disk requirement
comes from. On a large table that is both an outage and a free-space
requirement, which is why it is a poor emergency response.

Plain `VACUUM` takes a much weaker lock. On that same PostgreSQL 18.6 container on
2026-09-20, a vacuum of a 2,000,000 row table held only a
`ShareUpdateExclusiveLock`, and while it ran another session read all 2,000,000
rows and updated one of them. `pg_repack` reaches the `VACUUM FULL` result
through that weaker lock, taking a brief exclusive lock only at setup and at the
swap, at the cost of an extension and disk room for the second copy.

## Why does AI-generated code cause this?

The pattern is the long-lived transaction. An agent asked to process records in
batches will open one transaction, loop over everything, and commit at the end,
because that is atomic and correct. It is also a transaction that holds a
snapshot for the length of the job.

The related pattern is opening a transaction and then waiting on something
outside the database: an HTTP call, a queue, a rate limiter. The transaction
lasts as long as the slowest external thing.

The third is a generated schema that indexes every column. Measured above on
PostgreSQL 18.6 in container `dbg-pgbloat`, 2026-09-20, updating an indexed
column instead of an unindexed one took the HOT update count from 62,384 to 0
over 200,000 updates. An index nobody queries still costs that on every write.

None of the three shows up in development, where the job finishes in a second
and the table has a thousand rows.

## How do I stop it coming back?

**Set `idle_in_transaction_session_timeout`** so an abandoned transaction ends
itself:

<!-- sql-check: skip changes a role the fixture shares -->
```sql
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';
```

**Read the log, and set `log_autovacuum_min_duration`.** A cancelled autovacuum
and an autovacuum that never ran look identical in `pg_stat_user_tables`, which
was measured above; the log's `canceling autovacuum task` line tells them apart.

**Monitor `backend_xmin` age and slot `xmin` age**, not dead tuple counts. Those
two predict bloat before it appears, and they are not estimates.

**Tune autovacuum per table on your largest tables.** The global defaults are
chosen for small tables and are not a statement about what yours need. 0.01 is a
starting point to validate against your churn rate, not a cure.

**Partition tables that age out.** Dropping a partition removes the rows and the
file together, with no row-by-row delete, though it takes an `ACCESS EXCLUSIVE`
lock on the parent table.

**Drop replication slots whose `xmin` or `catalog_xmin` is set and whose
consumer is gone.** Check what consumes a slot before dropping it: a subscriber
that comes back to a dropped slot has to be rebuilt.

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent the dead and live
row counts per table as a time series rather than a single reading, along with
the last autovacuum time, the table and index sizes, and the insert, update and
delete counts driving them. A single reading tells you there are dead rows. A
series tells you whether they have been climbing since Tuesday and when
autovacuum last completed.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [What is table bloat in PostgreSQL and how to fix it](https://www.dbgorilla.com/learn/postgres/what-is-table-bloat-in-postgresql-and-how-to-fix-it/)
- [Why is autovacuum not keeping up?](https://www.dbgorilla.com/learn/postgres/why-is-autovacuum-not-keeping-up/)
- [Why is DELETE so much slower than TRUNCATE in PostgreSQL?](https://www.dbgorilla.com/learn/postgres/why-is-delete-so-much-slower-than-truncate/)