PostgreSQL, gimme back my disk space!

Published on Mar 24, 2014 by Vincent Roy

One of the biggest mistake that I’ve made when I bought my current MacBook Air is to cheap out on disk space (128GB). Ever since, I have been fighting the good fight against running out of space.

I started using Disk Inventory X early on, as recommended in this lifehacker post. Today as I was analyzing my disk to find bits to delete when I found that my data directory for PostgreSQL was taking around 13GB.

Finding your biggest relations

At this point I knew that my database was taking a lot of space, but I didn’t know which tables or indexes were taking the most space. Deleting files from the filesystem directly wouldn’t be wise (data corruption).

PostgreSQL has some great introspection tools, but I always have to google to find the exact queries. The one I turned to is the snippet below that that returns the biggest relations and their size.

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;

The biggest relations were data that were used from tests I had done in the past. Most rows in those tables were ripe to be deleted. I went ahead and deleted most rows from the biggest tables.

See also this wiki article for more disk usage related queries: http://wiki.postgresql.org/wiki/Disk_Usage

Give it back!

Deleting rows is insufficient to free up space on disk. I also had to run a VACUUM to give back the space to the operating system.

For the tables that I deleted rows from, I ran this:

VACUUM FULL table_name;

Read more about vacuuming for space recovery here

How much space can you really reclaim from this?

I’ll leave you with this: 10.1GB