PostgreSQL 10 High Performance - Third Edition

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.