Any relational database worth its salt has partitioning for a reason. 1. Maintenance. You will need to delete data at some point.(cleanup)...Partitions are the only way to do it effectively. 2. Performance. Partitioning offer a way to query smaller slices of data automatically (i.e the query optimizer will choose the partition for you) ...very large tables are a no-no in any relational database.(sheer size has limitations) On Fri, Oct 30, 2009 at 11:57 AM, Jeremy Harris <jgh@xxxxxxxxxxx> wrote: > 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 > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
