On Mon, 29 Apr 2019 at 17:13, Shai Cantor <shaicantor@xxxxxxxxx> wrote: > Will the db hold 135000 (45000 * 3 months) partitions under the assumption that I query only 1 partition? > Should I model it differently in terms of schema, partitions etc.? Which PG version? Before PG11 the query planner pruned unneeded partition by looking at each partition and determining if the partition constraint could not match the base quals on the query. PG11 improved this by adding a smarter and faster algorithm to get rid of non-matching partitions, however, this really only speeds things up a little as it only really allows the planner to skip generating paths for these partitions, certain meta-data is still loaded, which is not really slow per partition, but it is slow if you have thousands of partitions. PG11 is also pretty bad at planning UPDATE/DELETEs to partitioned tables with a large number of partitions. You'll at best suffer from high planning times to plan these queries and at worst suffer out of memory errors with that many partitions. The yet to be released PG12 improves both of these deficiencies providing you can eliminate the majority of partitions during query planning. PG12 also improves the performance of INSERT into a partitioned table, wherein PG11 a lock was obtained on every partition, with PG12 we only grab a lock on a partition the first time the INSERT command inserts a row into it. If you're just INSERTing 1 row per command into a partitioned table with many partitions then this makes a pretty big difference. Depending on the types of query you're running it's likely not a good idea to go above 100 or so partitions with PG11. You might get away with more if you're running a more data-warehouse type load, i.e fewer but longer running queries, but for a more OLTP type workload, with more queries and lower latencies, then you may struggle to cope with a dozen. I'd recommend you do workload simulations with whatever number you choose and ensure performance is to the level you require before getting in too deep with your design. If your go-live date is near the end of the year or beyond, then it might be a good idea to start testing with PG12 right away. The release date for that will likely be around the middle of October. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
