Performance impact of hundreds of partitions

From: Leonardo F <m_lists(at)yahoo(dot)it>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Performance impact of hundreds of partitions
Date: 2010-04-21 10:45:36
Message-ID: 644679.46887.qm@web29018.mail.ird.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

increasing shared_buffers has improved *a lot* the number of inserts/second,
so my "problem" [1] is fixed.

But now I'm worried because of the sentence (Tom Lane):

"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 did a (very quick) test with 3000 empty partitions, and it took 0.5 secs to do
the planning for a simple select with a very simple where condition.
With 300 partitions, planning takes about 30ms.

That's fine for my case, as I don't expect more than 300 partitions; and I could
actually wait for .5 secs more if that helps with such large tables, and I won't
be doing joins.

So: the "scaling" problem would be more evident in case joins were taken into
account? Or there's something else I didn't get?

[1] http://archives.postgresql.org/pgsql-general/2010-04/msg00611.php

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A B 2010-04-21 11:35:08 Re: Database viewpoint of subject - Sending e-mails from database table with Cronjob
Previous Message Leonardo F 2010-04-21 10:36:16 Re: performance problems inserting random vals in index