As part of a new and fairly large project I have a need to partition a few postgres tables and have a rolling daily window. That is.. I want to organize data by a timestamp storing each day in its own partition and maintain 90 days of historical data. Doing this is possible in Postgresql but it’s not pretty or very clean to set it up. To simplify the process I wrote this perl script that (when run daily) will pre-create a certain number of empty partitions into the future and remove the oldest partitions from your window.
I’m often ask what it is I do for a living… and being lazy I usually just say ‘computer stuff’. In an effort to provide a little more context to anyone who may be interested this is one in a series of postings where I’ll cover some aspect of what it is I do.
In my current role I spend part of the time doing development projects. (aka programming) I’m not a hard core developer though.. it’s not my full time occupation nor do I want it to be. I work mostly with perl and php when necessary, mysql and occasionally PostgreSQL or Oracle all under various flavors of linux. (debian is my favorite). Usually these development tasks are related to some sort of management automation for a global VoIP network but sometimes they involve making complex things easier to understand. Part of that involves automating the collection of large amounts of data and then presenting in a meaningful way so that problems and long term trends can be identified. What follows are some examples of the sorts of things I mean.