Re: Performance impact of hundreds of partitions

From: Vick Khera <vivek(at)khera(dot)org>
To: Leonardo F <m_lists(at)yahoo(dot)it>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Performance impact of hundreds of partitions
Date: 2010-04-21 14:20:20
Message-ID: p2l2968dfd61004210720lfff6dc7eo1a17d73021595ca1@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Wed, Apr 21, 2010 at 6:45 AM, Leonardo F <m_lists(at)yahoo(dot)it> wrote:
> "The partitioning code isn't designed to scale beyond a few dozen partitions"
>
> Is it mainly a planning problem or an execution time problem?
>

I'll bet that is related to the planning and constraint exclusion
parts. I have a couple of tables split into 100 partitions, and they
work extremely well. However, I was able to alter my application such
that it almost always references the correct partition directly. The
only times it does not is when it requires a full scan of all
partitions. All inserts are direct to proper partition.

In my view, it is a big win to partition large tables such that each
partition holds no more than 5 million rows. This keeps the indexes
small, and the query engine can quite easily skip huge hunks of them
on many queries. Also, reindexes can be done pretty quickly and in my
case without seriously disrupting the application -- each table
reindexes in under 5 or 10 seconds. When this was all one table, a
reindex op would lock up the application for upwards of two hours.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Allan Kamau 2010-04-21 15:01:38 Re: Database viewpoint of subject - Sending e-mails from database table with Cronjob
Previous Message Josh Kupershmidt 2010-04-21 14:09:37 Re: How to terminate a query