Wednesday, July 27 2011
PostgreSQL, tricks, catalogs

Next month partitions

When you do partition your tables monthly, then comes the question of when to create next partitions. I tend to create them just the week before next month and I have some nice nagios scripts to alert me in case I've forgotten to do so. How to check that by hand in the end of a month?

Here's a catalog query to help you there:

=> select * 
->   from
->   (
(>   select 'previous parts' as schemaname, count(*)::text as tablename
(>     from pg_tables
(>    where schemaname not in ('pg_catalog','information_schema')
(>      and tablename like to_char(now(), '%YYYYMM')
(>   
(>   union
(>   
(>   select schemaname, substring(tablename,1,length(tablename)-6) || '201108' 
(>     from pg_tables
(>    where schemaname not in ('pg_catalog','information_schema')
(>      and tablename like to_char(now(), '%YYYYMM')
(>   
(>   except
(>   
(>   select schemaname, tablename 
(>     from pg_tables
(>    where schemaname not in ('pg_catalog','information_schema')
(>      and tablename like to_char(now() + interval '1 month', '%YYYYMM')
(>   ) as t
-> order by schemaname <> 'previous parts', schemaname;
   schemaname   |       tablename        
----------------+------------------------
 previous parts | 1
 central        | stats_entrantes_201108
(2 rows)

As you see, our partitions are named _YYYYMM so that's it's easy to match them in our queries, but I guess about everyone does about the same here. Then the to_char expressions only allow to not enter manually '%201108' in the query text. And there's a trick so that we display how many partitions we have this month, adding a line to the result...