Re: Many thousands of partitions

On Tue, Oct 8, 2013 at 8:23 AM, Grzegorz Tańczyk <goliatus(at)polzone(dot)pl>wrote:

> Hello,
>
> I have question regarding one of caveats from docs:
> http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html
>
>
If you are using Postgresql 8.3 then you should consider upgrading to 9.3
instead.

> "Partitioning using these techniques will work well with up to perhaps a
> hundred partitions; don't try to use many thousands of partitions."
>
> What's the alternative? Nested partitioning could do the trick?
>

Nested partitioning will have the same problems, if not more. The query
planner might come up with suboptimal plans depending on how many nested
partitions there are.

> I have milions of rows(numbers, timestamps and text(<4kb), which are
> frequently updated and there are also frequent inserts. Partitioning was my
> first thought about solution of this problem. I want to avoid long lasting
> locks, index rebuild problems and neverending vacuum.
> Write performance may be low if at the same time I will have no problem
> selecting single rows using primary key(bigint).Partitioning seems to be
> the solution, but I'm sure I will end up with several thousands of
> automatically generated partitions.
>

I can speak from painful experience: just recently we had a project where a
development team went ahead and partitioned about 900 tables, resulting in
almost 80 thousand tables . It was almost comical that every single query
went from sub-second to tens of seconds, and a pg_dump of an *empty*
database would take longer than an hour. This was on sandbox servers so the
hardware was not production grade, but it was an excellent way to get the
point across that too many partitions can crush performance.