Creating new partitions

When you have live data coming in, you can expect that you'll one day need to add new partitions. The example here is only good for 2004 data. Once 2005 starts, inserts into the orders table with an orderdate from that year are going to fail.

You need to consider two things when the active partition is about to move forward into the next value. The first is whether your trigger function will support it. In cases where that's a static block of code, with a series of hard-coded comparisons, you may need to update it. The second thing is that the partition needs to be created and all its attributes properly set.

Scheduled creation

The simplest way to deal with creating the new partitions is to write a program that adds them, then make sure you're always at least one partition ahead of what's needed. On UNIX derived systems this is typically scripted into a cron job that executes more frequently than the partition changes, for example, runs...