Tag Archives: database

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.

Databases and Finding Useful Information

Yesterday, I received a bug report about certain details not being displayed in an application. The report said that this data should be there because it was configured properly in another application but wasn’t, hence the problem. I checked the user’s configuration and found out that the report was accurate. I replicated the user’s steps, looking for clues, and personally verified that we have an issue to fix. Why isn’t it showing? The question nagged me into performing more tests, trying out several steps that may be able to correct the problem but to no avail.

Eventually, I thought about logging on to the database to see what’s happening. Remembering what tables the particular feature is using, I looked for data that’s out of place or different from the usual behavior. I discovered missing values in one specific field and considered adding something to it and see if that changes anything. It did. Display of the reported details returned and I was ecstatic to have found the culprit, now ready for a quick fix, with less investigation time.

For software testers, it helps to know how to create and use database queries to find useful hints. They help us understand why some features behave as they do, especially when these applications transform dynamic information from one source to another.