Postgres: How to find where database size growth is coming from

All we need is an easy explanation of the problem, so here it is.

We have a PostgreSQL database that has grown significantly in size recently, from about 340GB to 571GB over the past couple of months, and we are not tracking any significant change in user behavior over that time. Our primary DBA has made a couple of recommendations, with his chief recommendation being to export the entire database and then re-import it, which from his tests on a second server cloned from our primary requires about 3 hours of downtime, and gets the size down to only 300GB.

My two main areas of concern would be finding out where this significant growth is coming from (using du -h I can at least see it’s in the /data directory with no significant growth in tablespace or pg_wal), and understanding just how importing and exporting the database can get us almost 300GB of space recovery without actually losing any production data.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The first thing I’d do is change into the data directory and run

du -sk *

This will show you in which of the subdirectories a lot of disk space is used. You can drill down by descending deeper and repeating the command.

Typically, the increase in disk usage comes from one of two causes:

  1. WAL in pg_wal cannot be removed. This could be because the archiver has a problem (look at pg_stat_archiver) or you have a stale replication slot (look at pg_replication_slots).

  2. Some tables or indexes are bloated.

    If you created a copy of the database with pg_dump/restore, you are halfway to the solution. Run something like this on both databases:

    SELECT oid::regclass AS object, relkind, pg_relation_size(oid) AS size
    FROM pg_class
    ORDER BY size DESC;
    

    Compare the output on both sides an watch for tables and indexes that are considerably larger on the original database.

    Fix the bloat by examining the possible causes. Once you have done that, get rid of the bload with VACUUM (FULL) (attention, this requires down time).

Method 2

Ultimately, we used the following to determine the issue:

We did an export and import onto a test database server so we had a copy of the database at it’s full size, and a copy of the database at the smaller, post import size.

We then ran the following query to identify the largest tables:

SELECT schema_name as table_schema, relname as table_name, pg_size_pretty(pg_relation_size(relid)) as data_size FROM pg_catalog.pg_stat_all_tables ORDER BY pg_relation_size(relid) DESC;

This clearly showed that on the main system the table pg_catalog.pg_largeobject was just over 200GB while on the test system following the export and import it was 0 bytes.

We are now working on a plan to better manage the growth of pg_largeobject.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply