Skip site navigation (1) Skip section navigation (2)

Re: database size growing continously

From: Jeremy Harris <jgh(at)wizmail(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: database size growing continously
Date: 2009-10-30 18:57:04
Message-ID: 4AEB3700.5080304@wizmail.org (view raw or flat)
Thread:
Lists: pgsql-performance
On 10/30/2009 12:43 PM, Merlin Moncure wrote:
> On Thu, Oct 29, 2009 at 11:40 AM, Steve Crawford
> <scrawford(at)pinpointresearch(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Jesper KroghDate: 2009-10-30 19:46:37
Subject: Re: Queryplan within FTS/GIN index -search.
Previous:From: Robert HaasDate: 2009-10-30 15:03:47
Subject: Re: sub-select in IN clause results in sequential scan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group