Track Database Size and Bloat

Your PostgreSQL database is quietly getting fatter and slower — not because you're storing more useful data, but because dead rows and orphaned index entries are piling up. This is bloat, and it's one of the most common reasons queries that used to fly now crawl. If you've ever run SELECT count(*) on a table and watched it take seconds when it should take milliseconds, bloat is likely your culprit. In this lesson, you'll learn how to track database size and bloat using built-in PostgreSQL views and extensions, so you can pinpoint exactly which tables and indexes are wasting storage — and decide when to clean them up.

The Problem This Lesson Solves

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to let multiple transactions read and write the same table without blocking each other. When you UPDATE or DELETE a row, PostgreSQL doesn't overwrite the original — it marks it as dead and creates a new version. Over time, dead rows accumulate, and the table file grows far beyond what your live data actually needs. This is table bloat.

Indexes suffer too. Every index entry that points to a modified row becomes stale, and unless the index is rebuilt, those dead entries stay forever. Index bloat can make index scans slower than sequential scans because the index is physically larger and more fragmented.

At this point in your PostgreSQL journey, you've already learned about vacuuming in earlier lessons. But vacuuming alone doesn't reclaim disk space — it only makes dead rows reusable for future writes. The disk space is not returned to the operating system. So even after a VACUUM, your table files remain huge, and bloat keeps eating your storage and hurting performance.

The real question is: How do you know which of your tables and indexes are bloated, and by how much? The answer is tracking database size and bloat — measuring the actual disk usage versus the live data size, and identifying where the waste is. Once you know that, you can apply targeted cleanup: VACUUM FULL, REINDEX, or pg_repack.

Why it matters now: Storage is cheap, but performance is priceless. Bloated tables inflate I/O, cache pressure, and query planning time. Knowing how to track bloat is a critical skill for any PostgreSQL DBA or backend engineer.

Core Concept / Mental Model

Think of your table as a library with thousands of books on shelves. Each row is a book. When you update a book, you don't rewrite it in place — you place a new copy on a new shelf and mark the old one as "damaged." The damaged copy still occupies shelf space until a janitor (the VACUUM process) comes to remove it. But the janitor only marks the shelf as available for new books; they don't tear down the shelf or make the room smaller.

Bloat is the difference between the total shelf space (actual on-disk size) and the space occupied by books that people actually read (live rows).

In technical terms:

• Live rows: rows that are visible to the current snapshot — your real data.
• Dead rows: rows that are no longer visible to any transaction — old versions after UPDATE or DELETE.
• Dead tuples: PostgreSQL's term for those dead rows, tracked in pg_stat_user_tables.

To measure bloat, you need two numbers:

1. Total relation size — the sum of the table's heap file plus its indexes (and TOAST, if any). You can get this from pg_total_relation_size() or from catalog views.
2. Live row count and average row width — from statistics or by sampling.

Bloat ratio = (total size - estimated live size) / total size. A healthy table has a bloat ratio close to 0; a bloated table can have 50% or more wasted space.

PostgreSQL provides two main ways to measure this:

• pg_stat_user_tables (built-in) — gives you n_live_tup, n_dead_tup, n_tup_upd, n_tup_del, and more. It's free, but only counts rows, not bytes.
• pgstattuple extension (third-party, but bundled with PostgreSQL) — gives table_len, tuple_len, dead_tuple_len, free_space, and more. It's accurate but scans the whole table, so it's heavier.

Key insight: Tracking bloat is a two-step process. First, use cheap row counts to find suspicious tables. Then, use pgstattuple for precise byte-level measurements on those suspects.

How It Works Step by Step

Let's break down the process of tracking database size and bloat into logical steps.

Step 1: Get the overall database size first

Before diving into individual tables, know the total size of your database. This gives you a baseline and helps you decide whether bloat is a systemic problem or isolated.

SELECT pg_database_size('your_database_name');

That returns bytes. Use pg_size_pretty() to make it human-readable:

SELECT pg_size_pretty(pg_database_size('your_database_name'));

Step 2: Identify the largest tables by total size

Bloat is most harmful on large tables, so sort by total size first.

SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

This gives you the top 10 tables by total size (including indexes). Now you know where to focus.

Step 3: Check row statistics for dead rows

pg_stat_user_tables tracks activity since the last stats reset. Look for tables with a high n_dead_tup relative to n_live_tup. A dead ratio above 20% is a red flag.

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    CASE WHEN n_live_tup > 0 THEN round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2) ELSE 0 END AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Step 4: Measure exact bloat with pgstattuple

For the suspect tables, use the pgstattuple extension to get byte-level details.

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('your_table_name');

The key columns are:

• table_len — total physical length of the table file
• tuple_len — total length of live tuples
• dead_tuple_len — total length of dead tuples
• free_space — free space in the table (reusable, but doesn't shrink the file)

Simple bloat ratio: (table_len - tuple_len) / table_len. If it's high (say > 0.3), you have significant bloat.

Step 5: Check index bloat separately

Indexes can be bloated independent of the table. Use pgstatindex for a specific index.

SELECT * FROM pgstatindex('your_index_name');

Look at avg_leaf_density — a value below 80% indicates fragmentation and wasted space.

Hands-On Walkthrough

Let's do a practical exercise. We'll create a test table, insert rows, update/delete a lot, and then measure bloat.

Setup

-- Create a small test table
CREATE TABLE IF NOT EXISTS bloat_test (
    id integer PRIMARY KEY,
    payload text
);

-- Insert 100,000 rows
INSERT INTO bloat_test
SELECT generate_series(1,100000), repeat('a', 100);

Now simulate a typical OLTP workload: update every row 10 times and delete every other row.

-- Update each row 10 times
DO $$
BEGIN
    FOR i IN 1..10 LOOP
        UPDATE bloat_test SET payload = repeat(b, 100);
    END LOOP;
END$$;

-- Delete half the rows
DELETE FROM bloat_test WHERE id % 2 = 0;

Before running VACUUM, check the stats:

SELECT relname, n_live_tup, n_dead_tup, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
WHERE relname = 'bloat_test';

You'll see a huge number of dead tuples because the stats are recent (unless autovacuum ran). Now check the table size before any vacuum:

SELECT pg_size_pretty(pg_total_relation_size('bloat_test'));

Now run a regular VACUUM and check again:

VACUUM bloat_test;
SELECT pg_size_pretty(pg_total_relation_size('bloat_test'));

You'll get the same size! Regular VACUUM doesn't shrink the file. That's bloat. Now use pgstattuple:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('bloat_test');

Look at table_len vs tuple_len. The tuple_len will be small because you only have 50k rows left, but table_len is still large. Calculate the bloat ratio: (0.5MB - 0.05MB)/0.5MB = 90% — almost all the file is dead or free space.

Now run VACUUM FULL:

VACUUM FULL bloat_test;
SELECT pg_size_pretty(pg_total_relation_size('bloat_test'));

Voila! The size drops dramatically because VACUUM FULL rewrites the table, reclaiming disk space. But note: VACUUM FULL takes an exclusive lock, blocking reads and writes.

For indexes, create a btree index and bloat it similarly, then check with pgstatindex.

CREATE INDEX idx_bloat_test_payload ON bloat_test (payload);
-- Run the update loop again on the payload column
-- Check index size
SELECT pg_size_pretty(pg_indexes_size('bloat_test'));
-- Then REINDEX
REINDEX INDEX idx_bloat_test_payload;
SELECT pg_size_pretty(pg_indexes_size('bloat_test'));

Pro tip: Always run ANALYZE after VACUUM FULL or REINDEX to refresh statistics for the query planner.

Compare Options / When to Choose What

When you've identified bloat, you have several cleanup tools. Here's a comparison:

Choose pg_repack for busy production systems where downtime is unacceptable. pg_repack uses a log-based approach to rewrite the table in the background with minimal blocking.

When to use pgstattuple vs pg_stat_user_tables?

• pg_stat_user_tables is cheap and always available — use it for monitoring and quick triage.
• pgstattuple is accurate but expensive — use it for detailed analysis on suspect tables when you need byte-level precision.

For ongoing monitoring, set up a scheduled query that alerts when n_dead_tup ratio exceeds a threshold.

Troubleshooting & Edge Cases

Here are common issues you may face:

n_dead_tup is always zero

If n_dead_tup shows zero, it could be because autovacuum ran recently. Stats are reset on VACUUM, so dead tuples are cleaned and the counter resets. That doesn't mean bloat is gone — the file size is still large. Always also check total relation size and pgstattuple.

pgstattuple returns an error 'must be superuser'

This extension requires elevated privileges. In managed PostgreSQL (like RDS), you may not have superuser access. In that case, use pgstatstatements or rely on pg_stat_user_tables and system tables to estimate bloat. There are third-party scripts (e.g., pg_bloat_check) that use heuristics without pgstattuple.

Autovacuum doesn't shrink tables

Autovacuum runs regular VACUUM, which doesn't reclaim disk space. That's normal. The table file remains and will reuse free space for future writes. If you need to shrink it, schedule VACUUM FULL or pg_repack during low traffic.

Index bloat is invisible in pg_stat_user_tables

That view only tracks table-level rows. You need pgstatindex to see index bloat. For all indexes, use pg_stat_all_indexes to see scan counts, but not bloat. The only reliable way is pgstatindex or estimate with pg_index and pg_class.

VACUUM FULL is slow on huge tables

It rewrites the entire table, so it can take a long time and fill up disk space during operation. Always ensure you have enough free disk (at least the size of the table) before running it.

TOAST bloat

Large values stored in TOAST can also bloat. Check pg_stat_user_tables for n_tup_dead with reltoastrelid, and use pgstattuple on the toast table as well. Rare, but possible.

What You Learned & What's Next

You now understand the core idea of tracking database size and bloat in PostgreSQL. You can:

• Explain why bloat happens (MVCC dead rows) and why it hurts performance.
• Use pg_database_size() and pg_total_relation_size() to get quick size figures.
• Interpret pg_stat_user_tables to spot tables with many dead tuples.
• Use pgstattuple and pgstatindex for precise byte-level analysis.
• Choose the right cleanup tool — regular VACUUM, VACUUM FULL, REINDEX, or pg_repack — based on your uptime requirements.

This knowledge gives you a proactive approach to storage management. Instead of waiting for disk full alerts, you can monitor and clean bloat before it impacts your application.

Next in the PostgreSQL Tutorial track, you'll learn how to optimize query performance with indexing strategies — building on your understanding of bloat, you'll see how index design can reduce both query time and maintenance overhead. Keep your databases lean and fast!