On 10/30/2009 12:43 PM, Merlin Moncure wrote:
> On Thu, Oct 29, 2009 at 11:40 AM, Steve Crawford
> <scrawford@pinpointresearch.com> wrote:
>> Use a parent table and 20 child tables. Create a new child every day and
>> drop the 20-day-old table. Table drops are far faster and lower-impact than
>> delete-from a 120-million row table. Index-bloat is limited to one-day of
>> inserts and will be eliminated in 20-days.
[...]
>> Read up on it here:
>> http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html
>
> From a performance point of view, this is going to be the best option.
> It might push some complexity though into his queries to invoke
> constraint exclusion or deal directly with the child partitions.
Seeking to understand.... is the use of partitions and constraint-exclusion
pretty much a hack to get around poor performance, which really ought
to be done invisibly and automatically by a DBMS?
Much as indexes per se are, in the SQL/Codd worldview?
Or, is there more to it?
I appreciate the "Simple Matter Of Programming" problem.
Thanks,
Jeremy