Re: 2D partitioning of VLDB - sane or not?

"Zeugswetter Andreas ADI SD" <Andreas(dot)Zeugswetter(at)s-itsolutions(dot)at> writes:

> I'd say that that point currently is well below 2000 partitions for all
> common db systems.

I think it will depend heavily on the type of queries you're talking about.
Postgres's constraint_exclusion is a linear search and does quite a bit of
work for each constraint. So it isn't terribly efficient for more than 1,000
partitions or so.

*But* that only affects planning time. If your queries are always effectively
pruned to few partitions and you execute them thousands of times then you not
care about slow planning time.

And if the overall table is large enough and you're dropping and loading
partitions then you may still be benefiting from partitioning by keeping all
the loaded records together and allowing dropping a partition to be constant
time.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com