From: Pablo Alcaraz <pabloa@laotraesquina.com.ar>
To: pgsql-performance@postgresql.org
Subject: tables with 300+ partitions
Date: Tue, 30 Oct 2007 14:09:05 -0400
Message-ID: <47277341.7070704@laotraesquina.com.ar> (raw)
Hi All!
I had a big big big table. I tried to divide it in 300 partitions with
30M rows each one. The problem was when I used the table to insert
information: the perfomance was LOW.
I did some testing. I created a 300 partitioned empty table. Then, I
inserted some rows on it and the perfomance was SLOW too.
SLOW = 1% perfomance compared with a non partitioned table. That is too
much.
Then, I did a 10 partitioned table version with 30M rows each one and I
inserted rows there. The performance was the same that the no
partitioned table version.
I suspect there is a lock problem there. I think every SQL command do a
lock to ALL the partitions so the perfomance with concurrent inserts and
updates are far worst than the no partitioned version.
The perfomace degrade with the number of partitions. And it degrade
fast: I have troubles with 40 partitions.
Am I right? is there a workaround? Can I replace the partitioned version
with another schema? any suggestion? I prefer to use something
transparent for the program because it uses EJB3 = deep changes and
testing on any change to the database layer.
Regards
Pablo Alcaraz
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: pabloa@laotraesquina.com.ar
Subject: Re: tables with 300+ partitions
In-Reply-To: <47277341.7070704@laotraesquina.com.ar>
* 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