1

I am reading up on system design concepts, specifically DataBase replication and partitioning. I understand that partitioning is used to improve throughput, and/or when the amount of data is high i.e. basically to improve scalability. But at what point do I need partitioning?

Eg. If I have a single table in my OLTP DB which sees a bunch of read/writes, with multiple columns, and each row is 1 KByte and there are 1 million daily users writing and reading from that DB, so each day there is 1GBytes of data written, and say the data is persisted for 1 year, so at any point the DB has 1 GBytes * 365 = 365 GBytes worth of Data.

Is this amount small enough to just use an index to improve throughput and get away with it? Or would I need to partition this? If the no. of users goes from 1 million to say 1 billion, then that is 365 TBytes worth of Data, what happens in this case?

Basically, how do I decide if I should partition or not, is there any empirical rule that I can fit here?

I understand that this question is too broad, and there is no "one size fits all" given how different each application, each DB can be, but there must be some way to "understand" or know this, hence asking the question here.

I did read up some articles online, but what I see is partitioning is explained wrt generic concepts like improving scalability, etc in a distributed system. Is there a real example that has a "before partitioning" and "after partitioning" scenario with numbers? Or is partitioning just used by default in modern applications?

Improve this question
1
  • 1
    Optimization follows usually the same pattern: implement and run the system in production, measure its resource consumption, check it against the requirements, and when you are getting at risk not the meet them any more, try out different optimizations.
    – Doc Brown
    Commented May 11, 2021 at 6:18

1 Answer 1

Reset to default
3

SLA

Service Level Agreement.

Are you meeting them?

Hardware

Does your server have the capacity to store the database?

Does it have the (real and potential) capacity to store the expected (and even worst case) growth in data volume over the next 5 years?

If so then a single database with a non-partitioned table is meeting your needs.

Otherwise if you are getting close to failing on your SLA, or the Server will soon run out of space. Now is the time to partition.

Depending on what issue you are addressing will determine how you solve this:

  • If storage is the issue, partitioning/sharding the database is quiet likely the route to take.
    • Of note try archival, or data purging first. System usually accrete stuff that is no longer needed/needed online
  • If performance is the issue, partitioning/sharding might be a solution. Another solution is to use a hot copy of the database to allow reading from one, and writing to the other.
    • Similarly upgrading the underlying hardware should also be considered, a single machine is many times less costly than two machines when considering on going costs.
Improve this answer
3
  • Ah, that makes sense. I didn't even think of SLA being a factor, but makes sense that the provider will have to keep the "promise". So basically numbers don't matter as long as my current hardware supports a non partitioned db, and if it is close to not meeting my requirements, then I look into partitioning, after making sure that there is nothing else I can do in my current setup. Right?
    – Ufder
    Commented May 11, 2021 at 2:11
  • 2
    Pretty much. Look for the low hanging fruit. A Database configuration change. An index. Remove old/bad data. Transaction levels. Then look at Hardware upgrades: more/larger drives, RAID, network upgrade, more ram, faster processor. Remember that splitting the database over a network, internalises that network to the database, which makes the system slower, and more complex. The payoff must be worth it, chances are there are lower hanging pieces of fruit with a higher payoff to complexity trade.
    – Kain0_0
    Commented May 11, 2021 at 2:30
  • It sounds so obvious when I read it! It is as I expected, no definitive answer but needed this understanding, thanks!!
    – Ufder
    Commented May 11, 2021 at 2:57

Your Answer

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

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