On Tue, Oct 8, 2013 at 8:23 AM, Grzegorz Tańczyk <goliatus@xxxxxxxxxx> 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.
