"Markus Schiltknecht" <[EMAIL PROTECTED]> writes: > Hi, > > Hans-Juergen Schoenig wrote: >>> What do you need so many partitions for? >> >> having so many tables is not funny but it can be the only reasonable choice. > > Well, what do you do with all those partitions?
In a previous life I had a database which had daily partitions. I assure you it was unquestionably the right decision. Each day's data wasn't just distinguished by the timestamp but actually was entirely separate from the previous day's data. Both the archiving strategy and the many reports which were ran all depended specifically on the day the data was collected on. Much like partial indexes the partition key effectively gives you a free index key element which can be combined with any other index without extra i/o. Often this lets you avoid a sort letting you perform some queries as OLTP queries with no startup cost which otherwise would have DSS style plans with sorts. Better yet it can be used even in a sequential scan which performs better than even a perfectly clustered index. Daily partitioned let us go from a weekly batch job which was threatening to overrun the maintenance window to a daily batch job which completed in minutes. As a result data warehouse reports could be delivered the same night instead of being hours, and in some cases days, delayed. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
