PostgreSQL VACUUM and Table Bloat

Performance / Guide 35 of 47 / Advanced

• OSUbuntu 26.04 LTS
• PostgreSQL18.6-0ubuntu0.26.04.1
• Test data400,000 rows, 2 core / 4 GB host
• TimeAbout 17 min

This is a direct consequence of MVCC: an UPDATE writes a new row version and leaves the old one for concurrent readers. Every PostgreSQL table works this way, and autovacuum exists to manage it.

This guide includes

Use this when a table has grown and deleting or updating rows does not shrink it. This matters because VACUUM makes space reusable without returning it - only a rewrite gives the disk back.

• updating a third of the rows, and counting the dead tuples that leaves behind
• vacuuming, and watching the size not change at all
• rewriting the table with VACUUM FULL to actually get the space back
• reading the settings that make this automatic, which are already on

Before you start

• The events table with 400,000 rows.

1. 1

   Update a third of the rows and count the dead ones

2. 2

   Vacuum, and watch the size not change

3. 3

   Rewrite the table to get the space back

4. 4

   Read the settings that make this automatic

Official sources

• PostgreSQL 18 Documentation - Using EXPLAIN
• PostgreSQL 18 Documentation - Index Types
• PostgreSQL 18 Documentation - Partial Indexes
• PostgreSQL 18 Documentation - Routine Vacuuming
• PostgreSQL 18 Documentation - pg_stat_statements