Re: Querying a time range across multiple partitions

On 9/8/2014 1:40 PM, Andreas Brandl wrote:
> can you explain that further? In the end, that argument sounds like it would always be more efficient to use a single table and its index instead, rather than partitioning it (log(N) < c*log(N/c) for any c > 1, if I'm not totally lost today).

it indeed would.

good reasons for partitioning include...

* efficient date based bulk deletion (we have a very large table that
has 6 months retention, so we partition by week and delete the oldest
week when a new week starts... dropping a partition is far faster than
deleting 20 million records by date)

* needing to put data across several tablespaces - I haven't had to do
this.

* more efficient vacuuming - really really large tables, like 100 GB,
take a LONG time to vacuum. sane sized partitions will vacuum in less
time, and since older time-based partitions aren't typically updated,
they can be frozen.

--
john r pierce 37N 122W
somewhere on the middle of the left coast