Suppose I have a lot of data (several GB worth) to store each day, going
back a few years. I am considering a nested partition schema, in which
the data for each day is stored in a separate child table, which
inherits from an empty table for each month, which inherits from an
empty table for each year. For example, if I had data going back three
years, I'd have one master table with the column definitions. Then I'd
have three year tables inherit from that master table. Then I'd have
twelve month tables inherit from each year table. Then I'd have the
appropriate number of day tables inherit from each month table.
MASTER
2011
01
01
...
31
...
12
...
2012
...
2013
01
...
12
01
...
31
Check constraints would be used on each day table to ensure that only
data for that year-month-day is stored there (performing a range check
on an integer or timestamp type, for example). Check constraints would
also be used on each month table to ensure that only data for that
year-month is stored in that month's child tables. And check
constraints would also be used on each year table to ensure that only
data for that year is stored in that year's child tables. Each day
table would have its day check constraint, plus inherited year and month
check constraints.
Now to the questions. Would constraint exclusion work in a tree fashion
to prune tables from a query plan? In other words, if I query data for
a specific day, would constraint exclusion first exclude all the years
but the relevant one, then exclude all the months of that year but the
relevant one, then exclude all the days of that month but the relevant
one, and finally perform a sequential or index scan of the right
partition? The idea is that by nesting the partitions in this manner
the number of exclusion checks is reduced. One can see that with a
decade of data stored, there would be approximately 3652 partitions.
The documentation discourages having this many partitions.
Thanks in advance for advice.
Regards,
Gabriel Sánchez-Martínez
PhD Candidate
Massachusetts Institute of Technology
