Table of Contents

1. Why Do We Need VACUUM? (The Problem)

Because of  MVCC, PostgreSQL does not directly overwrite a row when you run an UPDATE, and it does not immediately remove the old row data when you run a DELETE.

• An UPDATE creates a brand-new row and marks the old one as “dead”.
• A DELETE simply marks the existing row as “dead”.

Over time, these “dead rows” (also called dead tuples) accumulate. This is called table bloat. It wastes disk space and slows down your queries.

VACUUM is PostgreSQL’s garbage collector. It cleans up dead rows so your database stays fast and lean.

2. Setting Up a Test Table

Let’s create a playground table and insert 1,00,00,000 rows to see VACUUM in action.

-- Create a sample items table
postgres=# DROP TABLE IF EXISTS items;
DROP TABLE
postgres=# CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price INT
);
CREATE TABLE
postgres=#

-- Disable autovacuum for one test table, just for DEMO

postgres=# ALTER TABLE items SET (autovacuum_enabled = false);
ALTER TABLE
postgres=#

postgres=# SELECT relname, reloptions FROM pg_class WHERE relname = 'items';
 relname |         reloptions
---------+----------------------------
 items   | {autovacuum_enabled=false}
(1 row)

postgres=#

-- Insert 1,00,00,000 rows

postgres=# INSERT INTO items (name, price)
SELECT
    'Item ' || generate_series,
    (random() * 100)::INT
FROM generate_series(1, 10000000);
INSERT 0 10000000
postgres=#

-- Table physical path
postgres=# SELECT pg_relation_filepath('public.items');
 pg_relation_filepath
----------------------
 base/5/65690
(1 row)

postgres=#

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65690*
-rw-------. 1 postgres postgres 144K Sep 22 19:09 /pgData/pgsql17/data/base/5/65690_fsm
-rw-------. 1 postgres postgres 498M Sep 22 19:13 /pgData/pgsql17/data/base/5/65690
[postgres@pgdb01 5]$

3. Creating Table Bloat (Generating Dead Rows)

Now let’s update all 1,00,00,000 rows. This will generate 1,00,00,000 dead rows alongside the 1,00,00,000 new rows.

-- Update every row
postgres=# UPDATE items SET price = price + 10;
UPDATE 10000000
postgres=#

4. Inspecting Dead Rows & Table Size

Let’s check how many dead rows exist and how large the table has become.

-- Check dead row count

postgres=# SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'items';
 relname | n_live_tup | n_dead_tup
---------+------------+------------
 items   |   10000147 |   10000000
(1 row)

postgres=# 

-- Check physical disk size of the table

postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 1424 MB
(1 row)

postgres=#

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65690*
-rw-------. 1 postgres postgres 272K Sep 22 19:16 /pgData/pgsql17/data/base/5/65690_fsm
-rw-------. 1 postgres postgres 996M Sep 22 19:16 /pgData/pgsql17/data/base/5/65690
[postgres@pgdb01 5]$

5. Standard VACUUM (Reclaiming Space for Reuse)

A standard VACUUM marks the dead rows as free space so future INSERT or UPDATE operations can reuse it, but it won’t release free space to the OS

-- Run standard vacuum
postgres=# VACUUM VERBOSE items;
INFO:  vacuuming "postgres.public.items"
INFO:  finished vacuuming "postgres.public.items": index scans: 1
pages: 0 removed, 127389 remain, 127389 scanned (100.00% of total)
tuples: 10000000 removed, 10000000 remain, 0 are dead but not yet removable
removable cutoff: 2178, which was 0 XIDs old when operation ended
new relfrozenxid: 2176, which is 4 XIDs ahead of previous value
frozen: 48685 pages from table (38.22% of total) had 7643430 tuples frozen
index scan needed: 63695 pages from table (50.00% of total) had 10000000 dead item identifiers removed
index "items_pkey": pages: 54839 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 64.788 MB/s, avg write rate: 69.883 MB/s
buffer usage: 146023 hits, 227350 misses, 245229 dirtied
WAL usage: 455518 records, 245755 full page images, 1191493291 bytes
system usage: CPU: user: 10.29 s, system: 2.81 s, elapsed: 27.41 s
INFO:  vacuuming "postgres.pg_toast.pg_toast_65690"
INFO:  finished vacuuming "postgres.pg_toast.pg_toast_65690": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 2178, which was 0 XIDs old when operation ended
new relfrozenxid: 2178, which is 6 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 1.178 MB/s, avg write rate: 1.178 MB/s
buffer usage: 9 hits, 1 misses, 1 dirtied
WAL usage: 1 records, 1 full page images, 7245 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
postgres=#


-- Check dead rows again

postgres=# SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'items';
 relname | n_live_tup | n_dead_tup
---------+------------+------------
 items   |   10000000 |          0  <---- all dead rows deleted.
(1 row)

postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 1424 MB      <--------- free space won't release to the OS, We can only reuse the unused space.
(1 row)

postgres=#

Important Note on Standard VACUUM:

• n_dead_tup dropped to 0 (the garbage was cleaned).
• However, if you check pg_total_relation_size('items'), it will still show 1424 MB.
• Standard VACUUM does not shrink the file size back to the operating system. Instead, it keeps the empty slots ready inside PostgreSQL so new inserts won’t require growing the file again.
• Non-blocking: Standard VACUUM allows normal SELECT, INSERT, UPDATE, and DELETE queries to continue running while it works.

6. VACUUM FULL (Shrinking Disk Space to the OS)

If you deleted millions of rows and you must return the unused disk space back to your operating system, you need VACUUM FULL.

-- Check table size
postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 1424 MB
(1 row)

postgres=#

-- Run VACUUM FULL
postgres=# VACUUM FULL items;
VACUUM
postgres=#

-- Check table size again
postgres=# SELECT pg_size_pretty(pg_total_relation_size('items')) AS total_size;
 total_size
------------
 712 MB  <--- shrunk back down to original size!, free space released to the OS. 
(1 row)

postgres=#

-- VACUUM FULL rewrites the entire table into a new file on disk.

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65690*
-rw-------. 1 postgres postgres 0 Sep 22 19:23 /pgData/pgsql17/data/base/5/65690
[postgres@pgdb01 5]$

postgres=# SELECT pg_relation_filepath('public.items');
 pg_relation_filepath
----------------------
 base/5/65698
(1 row)

postgres=#

[postgres@pgdb01 5]$ ls -lrth /pgData/pgsql17/data/base/5/65698*
-rw-------. 1 postgres postgres 498M Sep 22 19:23 /pgData/pgsql17/data/base/5/65698
[postgres@pgdb01 5]$

65690 -- This file no more availble.

Warning: VACUUM FULL rewrites the entire table into a new file on disk. It places an EXCLUSIVE LOCK on the table, blocking all reads and writes until it finishes. Use with caution in production!

7. VACUUM ANALYZE (Optimizing Query Performance)

Whenever data changes significantly, PostgreSQL’s query optimizer needs updated statistics to choose the fastest query plan.

VACUUM ANALYZE cleans up dead rows and refreshes the planner’s statistics in a single command:

-- Clean dead rows + update query planner statistics
postgres=# VACUUM ANALYZE items;
VACUUM
postgres=#

Pro Tip: Always run VACUUM ANALYZE after large bulk imports or mass data updates.

8. Autovacuum: The Background Worker

You don’t need to manually run VACUUM all day. PostgreSQL includes a daemon called Autovacuum that triggers automatically when a table exceeds a dead-tuple threshold (by default, when ~20% of rows are modified).

-- It was disabled for DEMO, enable back to ture.
postgres=# ALTER TABLE items SET (autovacuum_enabled = true);
ALTER TABLE
postgres=#

--- Confirming default autovacuum settings

postgres=# SHOW autovacuum;
 autovacuum
------------
 on
(1 row)

postgres=# SHOW autovacuum_naptime;
 autovacuum_naptime
--------------------
 1min
(1 row)

postgres=# SHOW autovacuum_vacuum_threshold;
 autovacuum_vacuum_threshold
-----------------------------
 50
(1 row)

postgres=# SHOW autovacuum_vacuum_scale_factor;
 autovacuum_vacuum_scale_factor
--------------------------------
 0.2
(1 row)

postgres=#

-- Check when autovacuum last cleaned your tables
postgres=# SELECT
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'items';
 relname |         last_vacuum          | last_autovacuum |         last_analyze          | last_autoanalyze
---------+------------------------------+-----------------+-------------------------------+------------------
 items   | 2026-09-22 19:28:22.29634+08 |                 | 2026-09-22 19:28:22.992613+08 |
(1 row)

postgres=#

-- LOAD some data and check again autovacuum last cleaned status

postgres=# UPDATE items SET price = price + 20;
UPDATE 10000000
postgres=#

postgres=# SELECT
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'items';
 relname |         last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze
---------+------------------------------+-------------------------------+-------------------------------+-------------------------------
 items   | 2026-09-22 19:28:22.29634+08 | 2026-09-22 19:40:56.685873+08 | 2026-09-22 19:28:22.992613+08 | 2026-09-22 19:40:58.096743+08
(1 row)

postgres=#

Note: Autovacuum automatically performs VACUUM to clean up obsolete row versions and ANALYZE to maintain optimizer statistics. It does NOT performs VACUUM FULL.

9. Quick Summary Cheatsheet