My PostgreSQL Database Went from 139 GB to 29 GB. Here's Why.

Background

Recently I was migrating our databases from RDS to Snowflake Postgres . I was using pg_dump and pg_restore to take a dump of source database and restore into the new database.

I ran this command on source database to see how large it is.

SELECT pg_size_pretty(pg_database_size(current_database()));

And it showed 139 GB.

After restore I ran the same command on the target database and shocking result was 29 GB.

I started checking if there were any data loss during migration. But all tables and row counts showed the same numbers. I have ran migra tools and it showed exact match as well.

Then I started my investigation.

Analyzing postgres disk usage

The first thing I thought about was - dead tuples. Maybe there was an issue with the autovacuum worker configuration and it was not catching dead data.

I ran this command to check what is going on:

SELECT c.relname, s.n_live_tup, s.n_dead_tup,
       round(100.0 * s.n_dead_tup / nullif(s.n_live_tup,0), 1) AS dead_pct,
       s.last_autovacuum, s.last_autoanalyze,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
  FROM pg_stat_user_tables s JOIN pg_class c ON c.oid = s.relid
 ORDER BY s.n_dead_tup DESC LIMIT 20;

The results looked normal.

Then I checked autovacuum configuration:

SELECT
    name,
    setting,
    unit,
    source
FROM pg_settings
WHERE name LIKE 'autovacuum%'
ORDER BY name; 

The thresholds were set correctly.

No signs of significant dead tuple accumulation, so I moved on to investigating how much disk space was occupied by tables and indexes.

I also wanted to check how much unused space was left inside the actual table files. Even if autovacuum removes dead tuples, Postgres can keep the allocated disk space for future use.

For this, we can use pgstattuple_approx from the pgstattuple extension:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    relname,
    pg_size_pretty(pg_relation_size(relid)) AS heap_size,
    round((s).approx_free_percent::numeric, 1) AS free_pct,
    pg_size_pretty((s).approx_free_space::bigint) AS free_space
FROM pg_stat_user_tables
CROSS JOIN LATERAL pgstattuple_approx(relid) s
WHERE pg_relation_size(relid) > 500 * 1024 * 1024
ORDER BY (s).approx_free_space DESC;

This checks tables larger than 500 MB and shows:

• heap_size — the size of the table itself, excluding indexes.
• free_pct — estimated percentage of unused space inside the table.
• free_space — estimated amount of space that PostgreSQL can reuse for future writes.

A high free_pct indicates table bloat, even when there are almost no dead tuples.

In my case, the metrics were within a normal range, and there were no signs of bloat.

I was confused.

At that point, I didn't know much about index bloat, so I wasn't sure what to investigate next.

So I started googling to figure out the reason for the significant disk space consumption.

So, while digging deeper, I stumbled upon something called index bloat.

What is the index bloat?

This is when your index keeps growing after a significant number of write operations (mostly updates and deletes) and takes significantly more disk space than required.

For example, it can happen when you use a serial number as a PK. It works well when you keep adding new items, but when you start updating or deleting existing rows, some index entries become dead. PostgreSQL can clean them up and reuse the space, but the existing pages may stay on disk even if they are mostly empty.

B-tree page splits during inserts can also contribute to this. When PostgreSQL needs to insert a new index entry into a page that is already full, it creates another page and redistributes the entries between them. This can leave both pages partially filled, and if that free space isn't reused by future inserts, the index can end up occupying more disk space than necessary.

Analyzing index bloat

To inspect index bloat, PostgreSQL provides the pgstattuple extension. It includes the pgstatindex function, which lets us inspect the internal structure of B-tree indexes.

Install the extension:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Then run this query to inspect indexes larger than 100 MB:

SELECT
    c.relname AS index_name,
    pg_size_pretty(s.index_size) AS size,
    s.leaf_pages,
    s.deleted_pages,
    s.empty_pages,
    s.avg_leaf_density,
    round(
        100.0 * (s.deleted_pages + s.empty_pages) /
        nullif(s.index_size / current_setting('block_size')::bigint, 0),
        1
    ) AS unused_pages_pct
FROM pg_class c
JOIN pg_am am ON am.oid = c.relam
CROSS JOIN LATERAL pgstatindex(c.oid) s
WHERE c.relkind = 'i'
  AND c.relnamespace = 'public'::regnamespace
  AND am.amname = 'btree'
  AND pg_relation_size(c.oid) > 100 * 1024 * 1024
ORDER BY unused_pages_pct DESC;

The most useful values here are:

• deleted_pages — pages marked as deleted that PostgreSQL can reuse. A large number indicates heavy updates and deletes.
• empty_pages — empty pages that still occupy disk space.
• avg_leaf_density — how full the active leaf pages are, on average. Around 75–90% is generally reasonable, while values below 50% may indicate significant wasted space and potential index bloat.
• unused_pages_pct — the percentage of the index file occupied by deleted or empty pages.

One important thing: avg_leaf_density alone can be misleading. An index might have 80% leaf density but still contain thousands of deleted pages. These pages aren't included in the average leaf density.

A high percentage of unused pages is a good reason to investigate further, especially for large indexes. However, it doesn't necessarily mean the space is permanently wasted, since Postgres can reuse those pages for future inserts.

Fixing index bloat

Postgres has a solution to fix this issue using builtin command.

REINDEX rebuilds an index using the data stored in the index's table, replacing the old copy of the index.

You can rebuild an index for the table:

REINDEX TABLE logs;

Or for the index:

REINDEX INDEX idx_orders_customer_id;

Also you can rebuild an index without taking any locks that prevent concurrent inserts, updates, or deletes on the table:

REINDEX TABLE CONCURRENTLY my_broken_table;

Why did pg_dump and pg_restore remove index bloat?

So, why did my database shrink from 139 GB to 29 GB after migration?

Let’s take a look at how pg_dump and pg_restore work under the hood.

When you take a logical dump, PostgreSQL exports the actual table data and index definitions, not the physical pages where they are stored.

During a full restore into a new database, Postgres does:

1. Creates the tables.
2. Loads the data using COPY.
3. Creates the indexes from scratch using CREATE INDEX.

This means that all those partially empty and unused index pages from the old database are left behind. PostgreSQL builds fresh B-tree indexes based on the restored data, without carrying over the old index bloat.

You can read more about this in the official documentation:

• Populating a Database — explains why PostgreSQL loads data before creating indexes during a full dump and restore.
• REINDEX — explains how rebuilding indexes replaces bloated index structures with new ones.

Conclusion

In my situation, index bloat was the main reason for the huge size difference. The migration effectively rebuilt all the indexes, which reduced the disk space usage significantly.