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

Re: tables with 300+ partitions

From: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
To: Pablo Alcaraz <pabloa(at)laotraesquina(dot)com(dot)ar>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: tables with 300+ partitions
Date: 2007-10-30 18:56:50
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Pablo Alcaraz wrote:
> 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.

That's very vague. What exactly did you do? Just inserted a few rows, or 
perhaps a large bulk load of millions of rows? What was the bottleneck, 
disk I/O or CPU usage? How long did the operation take, and how long did 
you expect it to take?

> 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.

That suggests that the CPU time is spent in planning the query, possibly 
in constraint exclusion. But that's a very different scenario from 
having millions of rows in each partition.

> 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.

Every query takes an AccessShareLock on each partition, but that doesn't 
prevent concurrent inserts or updates, and acquiring the locks isn't 
very expensive. In other words: no, that's not it.

   Heikki Linnakangas

In response to

pgsql-performance by date

Next:From: Guillaume LelargeDate: 2007-10-30 19:21:05
Subject: Re: Optimizing PostgreSQL for Windows
Previous:From: Pablo AlcarazDate: 2007-10-30 18:09:05
Subject: tables with 300+ partitions

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