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