A bulk DELETE didn't make my Postgres table any smaller

For our database course project I had a events table on RDS PostgreSQL that I'd filled with about five million fake rows for load testing. When I was done, I ran a DELETE for everything older than a week, which was most of the table, and expected the storage graph in the console to drop.

It didn't move at all. The table was just as big as before.

It turns out that in PostgreSQL a DELETE doesn't remove rows from disk right away. Because of how Postgres handles concurrency (MVCC), deleted and updated rows stay in the table as dead tuples until they're sure no transaction still needs them. Cleaning them up is the job of VACUUM, and on RDS that normally happens automatically through autovacuum.

I checked what was going on with:

```sql

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum

FROM pg_stat_user_tables

WHERE relname = 'events';

```

Millions of dead tuples, and autovacuum hadn't finished with the table yet. A few minutes later last_autovacuum updated and n_dead_tup dropped to almost zero.

But the table size still didn't shrink, which confused me all over again. Regular VACUUM marks the space as reusable for new rows in that table. It usually doesn't give it back to the operating system (it can only trim completely empty pages at the very end of the table). So the file stays the same size, and new inserts just reuse the empty space inside it. To actually shrink the table you need VACUUM FULL, which rewrites the whole table and holds an exclusive lock while it does it. On a test table that's fine. On a table people are using, it basically means downtime, which is why tools like pg_repack exist.

Since this was a test database, I ran VACUUM FULL events; and watched the size drop.

What I'd do differently: for data I know I'm going to throw away in bulk, like old events, I'd use a partitioned table by date and drop old partitions instead of deleting rows. Dropping a partition removes the data immediately, with no dead tuples left behind. I'd read about partitioning in class, but this was the first time I understood why anyone bothers.