6 ms·

Scaling out Postgres, Part 1: Partitioning

60 points by craigkerstiens 14y ago

simple10 14y ago
Craig, thanks for sharing Peter's article. As a side note, we started on Heroku (on the cedar stack as well as actually working out of the Heroku office -- we're big fans). We're now hosting with Liquid Web on dedicated db boxes due to the large RAM and IO speed requirements of our app. We also initially wrote the app to use a mix of Postgres and MongoDB but ended up moving everything into Postgres.
- stoked 14y ago
  What were the reasons for moving away from MongoDB?
  - simple10 14y ago
    Our app does a lot of writing. We were on Mongo 1.8 which has a global write lock. So concurrency was a big problem under load. We also have a lot of users with networks of 200k+ people – not even including followers. This meant that we had to have arrays in the User collection with 200k+ IDs to perform a lot of our queries. The RAM needed for indexes wasn't really worth the cost and performance started to become a big concern. In the end, our data makes more sense in SQL with a cache layer (Redis in our case) rather than NoSQL. Our experience thus far in Mongo vs Postgres: go with what you know. We probably could have made Mongo work for us (especially Mongo 2.0+), but it was easier for us to work out the performance issues with Postgres. Plus, things made more sense in SQL. If you're using Mongo and need a cloud provider, I can recommend the people at MongoLab. They really went above and beyond helping us get up and running and are really cool people.
    - stoked 14y ago
      Thanks for the summary.
- jadoint 14y ago
  As someone who's currently looking for a new hosting provider, I'm curious to know what made you go with Liquid Web. My experiences with them have been so-so and have been thinking of moving to SoftLayer for possibly more reliable, albeit more expensive, hardware.
taligent 14y ago
Having to implement your own sharding in your application layer for a database with 3 tables is ridiculous. No wonder people are moving to products like Cassandra, HBase, MongoDB, CouchDB etc which automatically and transparently shard across as many nodes as you like.
- petrohi 14y ago
  It is not the number of tables that requires sharding, but the amount of data and associated workload. While automatic sharding definitely has its place, we wanted to be very explicit in our consistency tradeoffs.
  - taligent 14y ago
    Sure. But let's be realistic here the amount of data you would be dealing with is pretty insignificant compared to the bigger users of Cassandra and HBase or even MongoDB. But hey if you wanted to be explicit about consistency then I am sure you had your reasons. Just strikes me as all a bit odd. Then again I didn't realise PostgreSQL didn't have an automatic sharding feature.
    - j-kidd 14y ago
      PostgreSQL with sharding is called Postgres-XC.
      - taligent 14y ago
        Only tested with CentOS 5.4 and is at version 1.0 ? Seems odd for PostgreSQL not to have made cluster features more of a priority given the more towards cloud based deployments.
        mnutt 14y ago
        Postgres's replication is very good these days, and it works for many of the read-scaling use cases. Scaling writes is a harder problem and I don't think there's a automatic drop-in solution for any sql store that I know of. Mongo gets away with it because it's non-relational so you never have to worry about joining across servers.
rbranson 14y ago
Maybe I'm not understanding something, but there seems to be an implicit assumption that the message broker (RabbitMQ) is more reliable than the database. This seems to be a common fallacy.
- petrohi 14y ago
  Yes, I can see where you're coming from. Message queue in the article is more of an abstract service that provides certain guarantees and less of a specific RabbitMQ implementation. There are examples of message queues implementations on top of database--the resulting tradeoffs can be an interesting discussion.
bbromhead 14y ago
Glad to see you are having "good problems". Didn't like parallel queries using pgpool2?
- petrohi 14y ago
  We are planning to use pgpool2, but only for connection pooling. In our case the subset of postgres databases to execute a distributed query is dependent on the input parameters, current sharding configuration and cached routes. Cached routes are used to handle adding new shards without repartitioning--I will cover this in the second part. I guess we could use pgpool2 parallel queries, but this would likely require some adjustments in how they are implemented.

Craig, thanks for sharing Peter's article. As a side note, we started on Heroku (on the cedar stack as well as actually working out of the Heroku office -- we're big fans). We're now hosting with Liquid Web on dedicated db boxes due to the large RAM and IO speed requirements of our app. We also initially wrote the app to use a mix of Postgres and MongoDB but ended up moving everything into Postgres.

stoked 14y ago
What were the reasons for moving away from MongoDB?
- simple10 14y ago
  Our app does a lot of writing. We were on Mongo 1.8 which has a global write lock. So concurrency was a big problem under load. We also have a lot of users with networks of 200k+ people – not even including followers. This meant that we had to have arrays in the User collection with 200k+ IDs to perform a lot of our queries. The RAM needed for indexes wasn't really worth the cost and performance started to become a big concern. In the end, our data makes more sense in SQL with a cache layer (Redis in our case) rather than NoSQL. Our experience thus far in Mongo vs Postgres: go with what you know. We probably could have made Mongo work for us (especially Mongo 2.0+), but it was easier for us to work out the performance issues with Postgres. Plus, things made more sense in SQL. If you're using Mongo and need a cloud provider, I can recommend the people at MongoLab. They really went above and beyond helping us get up and running and are really cool people.
  - stoked 14y ago
    Thanks for the summary.
jadoint 14y ago
As someone who's currently looking for a new hosting provider, I'm curious to know what made you go with Liquid Web. My experiences with them have been so-so and have been thinking of moving to SoftLayer for possibly more reliable, albeit more expensive, hardware.

taligent 14y ago

Having to implement your own sharding in your application layer for a database with 3 tables is ridiculous. No wonder people are moving to products like Cassandra, HBase, MongoDB, CouchDB etc which automatically and transparently shard across as many nodes as you like.

petrohi 14y ago
It is not the number of tables that requires sharding, but the amount of data and associated workload. While automatic sharding definitely has its place, we wanted to be very explicit in our consistency tradeoffs.
- taligent 14y ago
  Sure. But let's be realistic here the amount of data you would be dealing with is pretty insignificant compared to the bigger users of Cassandra and HBase or even MongoDB. But hey if you wanted to be explicit about consistency then I am sure you had your reasons. Just strikes me as all a bit odd. Then again I didn't realise PostgreSQL didn't have an automatic sharding feature.
  - j-kidd 14y ago
    PostgreSQL with sharding is called Postgres-XC.
    - taligent 14y ago
      Only tested with CentOS 5.4 and is at version 1.0 ? Seems odd for PostgreSQL not to have made cluster features more of a priority given the more towards cloud based deployments.
      - mnutt 14y ago
        Postgres's replication is very good these days, and it works for many of the read-scaling use cases. Scaling writes is a harder problem and I don't think there's a automatic drop-in solution for any sql store that I know of. Mongo gets away with it because it's non-relational so you never have to worry about joining across servers.

rbranson 14y ago

Maybe I'm not understanding something, but there seems to be an implicit assumption that the message broker (RabbitMQ) is more reliable than the database. This seems to be a common fallacy.

petrohi 14y ago
Yes, I can see where you're coming from. Message queue in the article is more of an abstract service that provides certain guarantees and less of a specific RabbitMQ implementation. There are examples of message queues implementations on top of database--the resulting tradeoffs can be an interesting discussion.

bbromhead 14y ago

Glad to see you are having "good problems". Didn't like parallel queries using pgpool2?

petrohi 14y ago
We are planning to use pgpool2, but only for connection pooling. In our case the subset of postgres databases to execute a distributed query is dependent on the input parameters, current sharding configuration and cached routes. Cached routes are used to handle adding new shards without repartitioning--I will cover this in the second part. I guess we could use pgpool2 parallel queries, but this would likely require some adjustments in how they are implemented.