On 10/30/2009 12:43 PM, Merlin Moncure wrote:
On Thu, Oct 29, 2009 at 11:40 AM, Steve Crawford
<scrawford@xxxxxxxxxxxxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
