"Simon Riggs" <simon@2ndquadrant.com> writes:
> If we partition on invoice_date only, there is an implication that
> people will search for invoices on date range only too, otherwise why
> not just partition on invoice_id. This still works with the compound key
> approach.
Well there are practical problems with partitioning on invoice_id. It's
convenient to have a predictable partition definition that can be calculated
in advance.
I suspect what people did with paper invoices is look at the last invoice for
a period and note the invoice_id down to check all future invoice_ids against.
Essentially partitioning on two separate equivalent partition keys.
We could do the same sort of thing since we're looking at constraints, there's
nothing stopping the partitions from having two separate but effectively
equivalent constraints on them. I'm not sure how to describe "partition based
on this rule for dates but note the range of invoice_ids covering a partition
and generate a constraint for that as well"
But if we could find a way to represent that it would make a lot of common use
cases much more convenient to use.
>> (But that sounds rather like pie in the sky, actually. Which other
>> databases can do that, and how do they do it?)
>
> Oracle does it, by building a big index. Few people use it.
The people that use it are the people stuck by dogmatic rules about "every
table must have a primary key" or "every logical constraint must be protected
by a database constraint". Ie, database shops run by the CYA principle.
But if a database feature is hurting you more than it's helping you then
you're not doing yourself any favours by using it. The database is a tool to
make your life easier, not something to flog yourself with to prove how good
your database design skills are.
Oracle calls these "global" indexes and imho they defeat the whole purpose
behind partitioning your data in the first place.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com