What is Table Bloat in PostgreSQL Database ?

 

PostgreSQL Table Bloat Explained in Detail

Table bloat is one of the most important PostgreSQL performance issues.

Because PostgreSQL uses MVCC, every UPDATE creates a new row version instead of modifying the row in place.

How Bloat Happens

Assume a table:

CREATE TABLE employees(

   id INT,

   name TEXT

);

Initial data:

id   name

1    John

---

First Update

UPDATE employees

SET name='David'

WHERE id=1;

Internally:

Tuple 1: John    -> dead

Tuple 2: David   -> active

Physical storage:

Page 1

John   (dead)

David  (live)

---

More Updates

UPDATE employees SET name='Mike' WHERE id=1;

UPDATE employees SET name='Tom' WHERE id=1;

UPDATE employees SET name='James' WHERE id=1;

Now:

John    dead

David   dead

Mike    dead

Tom     dead

James   live

Although only one row exists logically, five versions exist physically.

80% dead tuples

20% live tuples

This is table bloat.

---

Why Bloat Is Bad

1. More Disk Usage

Logical data:

1 GB

Physical table:

20 GB

A common production issue.

---

2. More I/O

Suppose query:

SELECT *

FROM orders

WHERE order_id=100;

PostgreSQL scans pages.

Without bloat:

1000 pages

With bloat:

5000 pages

More disk reads.

---

3. Index Bloat

Every UPDATE creates new index entries.

Example:

Table Size = 50 GB

Indexes = 100 GB

Sometimes indexes become larger than the table.

---

4. Vacuum Takes Longer

More pages:

More work for VACUUM

which further increases maintenance cost.

---

Typical Causes

Long Running Transactions

Check:

SELECT pid,

       usename,

       now()-xact_start AS txn_age

FROM pg_stat_activity

WHERE xact_start IS NOT NULL

ORDER BY txn_age DESC;

Example:

3 hours transaction

Autovacuum cannot remove versions visible to that transaction.

---

Idle In Transaction Sessions

BEGIN;

-- user goes to lunch

COMMIT;

Very common application problem.

Check:

SELECT *

FROM pg_stat_activity

WHERE state='idle in transaction';

---

Heavy UPDATE Workloads

Examples:

Banking systems

Order systems

E-commerce inventory

Stock exchange data

---

Autovacuum Not Keeping Up

Autovacuum running slower than updates.

Result:

Dead tuples accumulate faster than cleanup.

---

Find Dead Tuples

Most useful first query:

SELECT

    schemaname,

    relname,

    n_live_tup,

    n_dead_tup,

    ROUND(

        100.0 * n_dead_tup /

        GREATEST(n_live_tup + n_dead_tup,1),

        2

    ) AS dead_pct

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC;

Example:

table         live       dead      dead_pct

------------------------------------------------

orders        1M         500K      33%

payments      2M         50K       2%

The orders table needs attention.

---

Find Largest Tables

SELECT

    relname,

    pg_size_pretty(pg_total_relation_size(relid))

FROM pg_catalog.pg_statio_user_tables

ORDER BY pg_total_relation_size(relid) DESC;

Example:

orders           500 GB

transactions     300 GB

Large tables with heavy updates are prime suspects.

---

Estimate Bloat Quickly

Top Suspects

SELECT

    relname,

    n_live_tup,

    n_dead_tup,

    pg_size_pretty(pg_total_relation_size(relid))

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC

LIMIT 20;

---

Exact Table Bloat Analysis

Install extension:

CREATE EXTENSION pgstattuple;

Check table:

SELECT *

FROM pgstattuple('orders');

Example output:

table_len         100 GB

tuple_len         40 GB

dead_tuple_len    45 GB

free_space        10 GB

Meaning:

45% table occupied by dead tuples

This is highly bloated.

---

Check Index Bloat

Install:

CREATE EXTENSION pgstattuple;

Check index:

SELECT *

FROM pgstatindex('idx_orders_order_id');

Example:

avg_leaf_density = 42%

Healthy:

>70%

Bloated:

<50%

---

Solution 1: Improve Autovacuum

For heavily updated tables:

ALTER TABLE orders

SET (

    autovacuum_vacuum_scale_factor = 0.01,

    autovacuum_analyze_scale_factor = 0.01

);

Instead of default:

20%

vacuum starts near:

1%

changes.

---

Solution 2: Manual VACUUM

VACUUM VERBOSE orders;

Benefits:

✅ Cleans dead tuples

✅ Updates visibility map

✅ Improves future index-only scans

Limitation:

❌ Does NOT shrink physical table size.

---

Solution 3: VACUUM FULL

VACUUM FULL orders;

What it does:

Rewrites complete table

Removes all dead space

Compacts table

Example:

Before: 100 GB

After : 35 GB

---

Downside

Exclusive lock.

Reads blocked

Writes blocked

until completion.

Not usually used on busy production systems.

---

Solution 4: pg_repack (Preferred)

Install:

pg_repack

Very popular production tool.

pg_repack -d mydb -t orders

Benefits:

✅ Rebuilds table

✅ Removes bloat

✅ Minimal locking

✅ Online

Example:

500 GB → 150 GB

without major downtime.

Most production DBAs prefer pg_repack over VACUUM FULL.

---

Find Tables Nearing Wraparound

Frequently correlated with poor vacuuming.

SELECT

    relname,

    age(relfrozenxid)

FROM pg_class

WHERE relkind='r'

ORDER BY age(relfrozenxid) DESC;

Large ages indicate vacuum is struggling.

---

Real Production Bloat Checklist

Run these in order:

Step 1

SELECT

    relname,

    n_live_tup,

    n_dead_tup

FROM pg_stat_user_tables

ORDER BY n_dead_tup DESC;

Step 2

SELECT *

FROM pg_stat_activity

WHERE state='idle in transaction';

Step 3

SELECT *

FROM pg_stat_progress_vacuum;

Step 4

SELECT *

FROM pgstattuple('large_table');

Step 5

If bloat > 30-40%:

Tune autovacuum

or

Run pg_repack

---

Interview Answer

PostgreSQL table bloat occurs because MVCC keeps old row versions after UPDATE or DELETE. When VACUUM cannot remove these dead tuples fast enough, tables and indexes grow larger than the actual live data. Bloat increases storage, I/O, query latency, and maintenance cost. It can be identified using pg_stat_user_tables, pgstattuple, and index statistics, and is typically resolved through proper autovacuum tuning, manual vacuuming, or online reorganization tools such as pg_repack.