Thread: table partitioning

Hi all,

I'm checking out table partitioning and wondering how to handle it
dynamically.

For example, if I want to partition by year & month, do I need to create
all of the partitions (& rules) before hand or can I handle all of that
another way (a pl/pgsql function? something else?)

Any pointers would be most handy, thanks!

--
Postgresql & php tutorials
http://www.designmagick.com/

Chris,

I'm using a cron job which creates a new partition table every night at midnight. After I create the table, I update the rule on the parent table so that new records go into the new partition table. Then I update the constraints on the tables so that constraint exclusion works. I'm doing it this way because you'll find if you have multiple rules on the parent table it might not return the proper result for the number of affected rows (might not be an issue for you). If you don't need to do it like this, just create a script that creates all the tables ahead of time. for year-month shouldn't be too many.

Gene




--
Eugene Hart

Hi Gene,

> I'm using a cron job which creates a new partition table every night at
> midnight. After I create the table, I update the rule on the parent
> table so that new records go into the new partition table. Then I update
> the constraints on the tables so that constraint exclusion works. I'm
> doing it this way because you'll find if you have multiple rules on the
> parent table it might not return the proper result for the number of
> affected rows (might not be an issue for you). If you don't need to do
> it like this, just create a script that creates all the tables ahead of
> time. for year-month shouldn't be too many.

Thanks for the info. I think I'll have to use a similar method - it's
for a web-app with quite non-static data.

--
Postgresql & php tutorials
http://www.designmagick.com/