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

About taotetek

Sometimes stereotypical but never ironic. You can't stop the signal, Mal. All my opinions are my own, unless I stole them from you.
This entry was posted in postgresql and tagged . Bookmark the permalink.

Leave a Reply

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

You are commenting using your 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