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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.