6

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?

  • 2
    This is a huge "it depends" and entire books have been written about it. Your question is likely going to be closed. – Philᵀᴹ Feb 22 '14 at 23:47
  • You might start by reading this: wiki.postgresql.org/wiki/… – GhostGambler Feb 23 '14 at 18:49
  • I addition to the correct "it depends" -- There is the hot standby feature available which is allowing to share the content via several cluster and at least doing read-only-access on the standby systems. Maybe a starting point for you. Maybe not -- it depends ;) – frlan Feb 23 '14 at 21:14
  • We need a lot more information: What sort of system are you designing? What does your system architecture look like? What performance characteristics does your system have to have? How many concurrent users do you expect? – Colin 't Hart Feb 24 '14 at 13:31
  • @Colin'tHart Hello, I have edited my question. Could you please help me. Thank you. – Benjamin Smith Max Feb 24 '14 at 13:45
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 slave, secure transfer of data between master and slaves, slaves 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 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.

| improve this answer | |
  • 4
    Could you please add some explanation, too? Your statements are far from being that clearly true... – dezso Feb 24 '14 at 12:02
  • Thank you for the answer. Ok, so no horizontally scaling for now. But how do I load balance using multiple machines and manage database? What I want to do is load balancing for the performance. And I heard adding more machine can leverage it. So could you please elaborate a little more, especially the last paragraph ('horizontally clients connecting' to a database and 'partition data within same database'). – Benjamin Smith Max Feb 24 '14 at 13:37
  • @dezso: I tried. I suppose my opinion is very unpopular though, as database horizontal scaling is all the rage today. I just know that many things can go wrong in a system scaled like this. – Tometzky Feb 24 '14 at 22:33

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