Re: Declarative partitioning grammar

"Markus Schiltknecht" <markus(at)bluegap(dot)ch> 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!