Skip site navigation (1) Skip section navigation (2)

Re: database size growing continously

From: Anj Adu <fotographs(at)gmail(dot)com>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: Jeremy Harris <jgh(at)wizmail(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: database size growing continously
Date: 2009-10-30 20:11:06
Message-ID: f2fd819a0910301311p3dabfe74jf20df1c5018da441@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Database are designed to handle very large tables..but effectiveness
is always at question. A full table scan on a partitioned table is
always preferable to a FTS on a super large table. The nature of the
query will of-course dictate performance..but you run into definite
limitations with very large tables.

On Fri, Oct 30, 2009 at 1:01 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:
> On Fri, Oct 30, 2009 at 12:53 PM, Anj Adu <fotographs(at)gmail(dot)com> wrote:
>> 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.
>
> This is true and it's unavoidably a manual process. The database will
> not know what segments of the data you intend to load and unload en
> masse.
>
>> 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)
>
> This I dispute. Databases are designed to be scalable and very large
> tables should perform just as well as smaller tables.
>
> Where partitions win for performance is when you know something about
> how your data is accessed and you can optimize the access by
> partitioning along the same keys. For example if you're doing a
> sequential scan of just one partition or doing a merge join of two
> equivalently partitioned tables and the partitions can be sorted in
> memory.
>
> However in these cases it is possible the database will become more
> intelligent and be able to achieve the same performance gains
> automatically. Bitmap index scans should perform comparably to the
> sequential scan of individual partitions for example.
>
> --
> greg
>

In response to

pgsql-performance by date

Next:From: Jeremy HarrisDate: 2009-10-30 20:18:45
Subject: Re: database size growing continously
Previous:From: Greg StarkDate: 2009-10-30 20:01:31
Subject: Re: database size growing continously

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group