Re: database size growing continously

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux