PostgreSQL, gimme back my disk space!
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: