pgDynamicPartitions is a script that allows you to maintain a rolling window of daily table partitions under PostgreSQL. When called daily via cron, it will create x empty partitions into the future and keep only n partitions from the past 30 days, for example, and drop any that are older. It can be used with multiple tables, and each can be treated differently by calling it with different parameters via cron.
|Tags||Perl postgres postgresql SQL Database|
Release Notes: This release is not a code change, but a change in the example table definitions. The way the example was doing the constraints before was causing query pruning not to work as expected. If your tables are partitioned with these new constraints, you can now do queries like "select * from foo where date(starttime) = ’2012-01-19′", and it will work as expected and only scan the partitions for the day 01-19. That assumes that ‘starttime’ is a timestamp column.