From: Scott Marlowe <scott.marlowe@gmail.com>
To: Pablo Alcaraz <pabloa@laotraesquina.com.ar>
Cc: Steven Flatt <steven.flatt@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: tables with 300+ partitions
Date: Wed, 31 Oct 2007 12:57:17 -0500
Message-ID: <dcc563d10710311057p793cc9fav570f6ae075375cd0@mail.gmail.com> (raw)
In-Reply-To: <47289EEE.50008@laotraesquina.com.ar>
References: <47277341.7070704@laotraesquina.com.ar>
<357fa7590710301400x3a05796dle6bae219a1a25fc@mail.gmail.com>
<47289EEE.50008@laotraesquina.com.ar>
On 10/31/07, Pablo Alcaraz <pabloa@laotraesquina.com.ar> wrote:
>
> Steven Flatt wrote:
>
> On 10/30/07, Pablo Alcaraz <pabloa@laotraesquina.com.ar> wrote:
> > I did some testing. I created a 300 partitioned empty table. Then, I
> > inserted some rows on it and the perfomance was SLOW too.
>
>
> Is the problem with inserting to the partitioned table or selecting from it?
> It sounds like inserting is the problem in which case I ask: how are you
> redirecting inserts to the appropriate partition? If you're using rules,
> then insert performance will quickly degrade with number of partitions as
> *every* rule needs to be evaluated for *every* row inserted to the base
> table. Using a trigger which you can modify according to some schedule is
> much faster, or better yet, use some application-level logic to insert
> directly to the desired partition.
>
> Steve I was a program inserting into the base table. The program ran in 200+
> threads and every thread insert data on it. Every thread inserts a row every
> 3 seconds aprox.(or they used to do it), but when I put more partitions the
> insert speed went to 1 insert every 2 minutes.
>
> The selects that need to evaluate all partitions were slow too, but I think
> I can wait for them. :D
>
> I wonder if the update are slow too. I do not know that.
>
> Do I need to do a trigger for insert only or I need a trigger to update and
> delete too?
You need a trigger for any update / delete / insert you don't want to
be really slow. Basically, if a rule is doing it now, you need a
trigger to do it to speed it up.
My experience has been that at 200 to 1000 partitions, the speed of
the smaller tables still makes selects faster than with one big table
for certain kinds of access. At some point, the cost of planning a
lookup against thousands of tables will be more than the savings of
looking in a really small table.
The nice thing about triggers is that you can use maths to figure out
the name of the table you'll be writing to instead of a static table
like most rules use. So, all you have to do is make sure the new
tables get added under the parent and poof, you're ready to go, no
need for a new trigger.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, pabloa@laotraesquina.com.ar, steven.flatt@gmail.com
Subject: Re: tables with 300+ partitions
In-Reply-To: <dcc563d10710311057p793cc9fav570f6ae075375cd0@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox