13 Apr 2011, 17:35

How Much Do My Date Partitions Grow Each Day? - Stupid PostgreSQL Tricks

Today I had to do some fast analysis on a data warehouse to see how much the database was growing each day. The PostgreSQL database was date partitioned in a fairly standard way: each partitioned table had tables with the naming convention “tablename_YYYY_MM_DD”, and these tables were located in a schema named “partitions”. There were several hundred tables that were partitioned this way. Here’s a quick one liner from bash that gets the answer:

echo “SELECT sum(pg_total_relation_size(schemaname || ‘.’ || tablename)) FROM pg_tables WHERE schemaname = ‘partitions’ AND tablename LIKE ‘%_2011_04_12’” | psql database_name