Maximum Partitioning PostgreSQL I have 400,000 records I need to partition. The records will increase 100,000 per year and those new records might need to have 1000 new partitions added.

Here are some of my concerns:


How many partitions are too many
Is having small partitions bad (could have less than 150 records per partition)
Large partitions will be 10,000 or more records
partitions are created by field, not a date
new records could be added daily to the larger partitions but the smaller partitions might be monthly
possibly need to separate partitioned data legally (some cases but not all)


Example:

Let's say 10,000 out of 100,000 will need to be broken into partitions of 100 records, so 1000 partitions will be created and 9 additional partitions will be created with 10,000 records each, for a total of 1009 partitions.

Questions:


is this approach going to work
is this the best approach, if not what are some other ideas


Notes: 


Most records will fall into the larger partitions
You need one partition for that many records. Not 1000. Certainly not 1000/year. This is not a problem that requires partitioning. It looks to me like you've decided on the solution before fully stating and analysing the problem.

Reading between the lines, it sounds like you're implementing a mulit-tenant system and have already decided that partitioning is the way to do that. Right?

If so: wrong approach. Start with a single table. Partition if/when you need to for performance and maintenance reasons. With a DB of this scale it is very unlikely that you will ever need to, it's tiny.


  How many partitions are too many


Because the constraint exclusion code isn't super smart, try to stick to low partition counts. I prefer tens or hundreds at most.


  Is having small partitions bad (could have less than 150 records per partition)


Yes, it's very wasteful in terms of planning and execution time.


  possibly need to separate partitioned data legally (some cases but not all)


What's the difference between a partition and a single table with a composite key? I've never seen a legal or regulatory code that goes down to the level of actually specifying database structure, other than maybe PCI, and not in this way.

Details please.


  is this the best approach, if not what are some other ideas


Use one table, a composite key, and some composite indexes. If useful/necessary, use partial indexes for sub-ranges.