I have a table for storing events which is partitioned on a
timestamptz column by month using
PARTITION BY RANGE.
There are 5 partitions at the moment, containing a span of a month each, starting from
FOR VALUES FROM ('2018-01-01') TO ('2018-02-01') and ending with
FOR VALUES FROM ('2018-05-01') TO ('2018-06-01').
Most data is entered in a linear and predictable fashion. However, events are consumed by apps who report them, and I do have to allow for a past event to be entered at any time - which could have a timestamp earlier than
2018-01-01, or even a future event (for example a charge that is expected to occur some time in the future).
I was planning on creating one partition for past events that would span much longer than a month since there is no expectation of having too many of those.
I'm not sure what would be the best approach for future events where no partition exists for yet.
Is there a way to get the min/max values I could store within the existing partitions? If not, I could create a reference table that would store these values but I prefer to not have to maintain that.
Should I create a trigger to check for each row being inserted (seems expensive)? Should I capture errors on inserts and deal with those one at a time?