7

Please correct me if I am wrong but I guess handling more requests and load by adding more machines or balancing the load between multiple servers is horizontal scalling. So, if I add more servers, how do I distribute the database? Do I create one database to hold the user records with multiple servers? Or do I split the database too? What about database integrity? How to synchronize it? Or else what do I do? I am a newbie and really confused but eager to learn. I would like to use postgres for my project and would like to know some basic things before I start. I was thinking of using two small ec2 instances. But I got confused about the database. How do I go about creating the database. Do I need to go through sharding for this? What would be the best approach for horizontal scaling in accordance to postgres. I would really appreciate if you could explain it to me. Thank you!

Edit:

How do I load balance using multiple machines and manage the database?

I have an app where users can upload videos and it will be converted to mp4 using Elastic Transcoder. Users about 10k. So, how do I load balance using multiple machines and manage the database? What I want to do is load balancing for the performance. And I read in many post that adding more machine can leverage it. So I though of horizontal scaling. But since horizontal scaling is scary, how do I load balance and manage my database?

Benjamin Smith Max
  • 213
  • 3
  • 6

1 Answers1

9

First rule of horizontal scaling of a database is to avoid it. At all cost. You should consider it only when no server you can possibly buy can handle your data. And there are servers which can handle enormous amounts of data today.

Horizontal scaling of a database will give you:

  • at least an order of magnitude more complicated system:

    • even in a simplest case of sharding you'd have to manage more servers, distribute load evenly, manage schema updates and shared tables in all nodes, make your frontend connect to correct server etc.;
    • if you use replication instead, then you'd have to manage replication delay, failing queries on a replica, secure transfer of data between primary and replicas, replicas going out of sync, updates have to be run against master etc.;

  • at least an order of magnitude more bugs, direct consequence of above complications;

  • at least an order of magnitude harder to debug bugs, direct consequence of above complications;

  • at least 2 times higher probability of hardware failure - you'd have at least 2 servers, probability of any of them failing is higher than one specific failing;

  • probably an order of magnitude worse performance than a single server for the same price, as better storage options like battery backed drive controller or SSDs, partitioning your data, smart placement of WAL files, bigger cache etc. would be far more important than buying another, slow server; remember that databases are mostly bottlenecked by storage.

You can scale horizontally clients connecting to a database (frontends are mostly bottlenecked by processor, so this makes sense), you should use replication for failover (so called warm standby server), you can partition your data within the same database (which would get you most of benefits of sharding with far less drawbacks). Just don't do horizontal scaling before you're of a comparable size to Google, Twitter or Facebook. So no 2 small EC2 instances, no.

Tometzky
  • 529
  • 2
  • 6