Maximum Partitioning PostgreSQL
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.