On Fri, 24 May 2019 at 22:00, David Rowley <david.row...@2ndquadrant.com> wrote: > I've attached the pg10 and pg11 patches with that updated... and also > the master one (unchanged) with the hopes that the CF bot picks that > one.
I got talking to Andres about this at PGCon after a use case of 250k partitions was brought to our attention. I was thinking about the best way to handle this on the long flight home and after studying the current docs I really feel that they fairly well describe what we've done so far implementing table partitioning, but they offer next to nothing on best practices on how to make the most of the feature. I've done some work on this today and what I've ended up with is an entirely new section to the partitioning docs about best practices which provides a bit of detail on how you might go about choosing the partition key. It gives an example of why LIST partitioning on a set of values that may grow significantly over time might be a bad idea. It talks about memory growth with more partitions and mentions that rel cache might become a problem even if queries are touching a small number of partitions per query, but a large number per session. The attached patch is aimed at master. PG11 will need the planner memory and performance part tweaked and for PG10 I'll do that plus remove the mention of PRIMARY KEY and UNIQUE constraints on the partitioned table. Does anyone see anything wrong with doing this? I don't think there should be an issue adding a section to the docs right at the end as it's not causing any resequencing. Or does anyone have any better ideas or better examples to give? or any comments? If it looks okay I can post version for PG11 and PG10 for review, but I'd like to get this in fairly soon. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
add_docs_about_best_partition_practices.patch
Description: Binary data