1

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
Improve this question
3
  • 2
    These numbers look too small for partitioning to make sense. 400K records total is really not that much nowadays, and a partition with 10K records is super-tiny.
    – Daniel Vérité
    Mar 23, 2015 at 21:19
  • @DanielVérité I agree but I need to somehow separate the records, I was thinking partitioning would solve this but then again it might not
    – Phill Pafford
    Mar 23, 2015 at 23:07
  • why not just add an identifier. that's what I did. it never violates the regulations for my medical company clients. only plan partitions once there are 'millions of patients.' thought about separate schemas but it was just to much work to maintain when testing compared to merely adding an organization identifier.
    – Andrew Scott Evans
    Jan 17, 2021 at 3:42

1 Answer 1

Reset to default
7

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.

Improve this answer
1
  • I agree with everything you have stated, the only thing I really need is separation of data, each data set could be a legal entity which could (but not for all cases) require this hard separation. I thought about doing separate schemas for this but wanted to know more about partitions and if I could actually use them that small and in the numbers needed. The other requirement is the ability to search across any, all, some of the data sets, I thought partitioning might also solve this issue. Thanks for your insight on my question
    – Phill Pafford
    Mar 24, 2015 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.