Reducing PostgreSQL Test Database File Size

Some time during the past week I thought about learning how to reduce the overall file size of postgreSQL test databases. It came as a minor annoyance over a 500 MB database backup file, which always took a while to restore when testing our apps locally. Apparently I was bothered enough that I decided to look over all the data to see whether there was anything unnecessary that I can purge.

I ran DELETE commands in places where data did not seem needed:

    DELETE FROM <database_table> WHERE <condition>;

But that alone, I found out, does not decrease the size of the database, let alone the size of the affected table. What did the trick was running two other commands after the initial delete instruction –

    VACUUM FULL VERBOSE ANALYZE <database_table>;
    REINDEX TABLE <database_table>;

– to which I was able to minimize our test database to 166 MB, at least 60% smaller than it was before. It turned out there were still tons of logs in there that we didn’t really want. The database restore process is now faster, plus I also learned more about our tables as I went through each of them.