6 ms·

How do you cluster in postgresql? Serious question, my prefer noSQL is cassandra and clustering is pretty easy. I ask this question every year and postgresql h

How do you cluster in postgresql? Serious question, my prefer noSQL is cassandra and clustering is pretty easy. I ask this question every year and postgresql have not deliver this. If there is any, there are hardly any documentation on it.

icebraining 12y ago
Postgres-XL, Postgres-XC, pg_shard...
agentultra 12y ago
> In 9.4, Logical Decoding supplies a new API for reading, filtering and manipulating the PostgreSQL replication stream. This interface is the foundation for new replication tools, such as Bi-Directional Replication, which supports the creation of multi-master PostgreSQL clusters. Other improvements in the replication system, such as replication slots and time-delayed replicas, improve management and utility of replica servers. See also, ye manual: http://www.postgresql.org/docs/9.4/interactive/high-availability.html http://www.postgresql.org/docs/9.4/interactive/high-availabi...
- klaruz 12y ago
  I think the problem is clustering is still very much a duct-tape situation in postgresql with no real clear consensus on how to build out a cluster. Postgres-XL looks great for scale out, but you need 4 independent types of servers. Even with all those moving parts, it doesn't provide availability. If you want fail-over, you need pacemaker for the data nodes with traditional sync replication, and something like VRRP for your balancer, and something else to failover the coordinator. Several of these pieces can be tricky to set up in a cloud provider. BDR looks nice, but it looks like there could be lots of gotchas for consistency in there. Maybe it is a magic bullet though... I don't know much about it yet. Contrast with something like rethinkdb, mysql-galera, cassandra, etc, you start up enough nodes for quorum, tell them about each other, and you're pretty much done. The clients can handle the balancing, or you can use a pooler/balancer. In my perfect world, I'd install postgresql-awesome-cluster-edition on 3 nodes, add the 3 IPs (or turn on multicast discovery, if my env can support it), and away we go for read scalability and availability. I do this today for mysql-galera, and other than the fact it's mysql, it's awesome. For writes, if you add 4 or more nodes, there should be some sort of shard system like XL has. That said, postgresql is still clearly the best SQL and even noSQL single node server out there, it's a really great piece of software.
  - gthank 12y ago
    If you honestly believe that all you have to do is stand up a bunch of instances of Mongo/Cassandra/whatever and you instantly get acceptable HA, then you need to read the [Jepsen series](https://aphyr.com/tags/jepsen https://aphyr.com/tags/jepsen)
    - internetisthesh 12y ago
      Elasticsearch can run with Zookeeper. Zookeeper is pretty solid. Also, MSSQL with AlwaysOn is something which seems very robust too.
    - nemo44x 12y ago
      It depends on what you consider "acceptable HA". There are many instances where I'm not trying to protect from a network partition (single data center, monitored batch data loads, etc) and don't have a requirement for that level of tolerance. However, you're right in that it's important to know that nearly every distributed system has edge cases where things might not appear as you thought. Elasticsearch has a section on their Website detailing their resiliency efforts. I wish every company was as transparent about what they're doing on that front so we can all plan and consider expectations better.
    - threeseed 12y ago
      Setting up Mongo/Cassandra for HA is still several orders of magnitude less work than with PostgreSQL. And there are plenty of options for dealing with the issues presented in those series.
- threeseed 12y ago
  That's an API not a solution. I do think (for once) PostgreSQL is addressing it's core weakness and by version 10 will likely have horizontal scalability locked down. The new API is a really positive step.
throwaway90446 12y ago
Just do vnode-based consistent hashing on the client side.
- nemo44x 12y ago
  How do I add and remove capacity then?
  - bonif 12y ago
    You don't need to (and if you ever do, you will have or recruit the know-how) http://www.ovh.com/us/dedicated-servers/big-data/ http://www.ovh.com/us/dedicated-servers/big-data/
    - nemo44x 12y ago
      That is entirely wrong. It might suit your use case (having a bunch of monkeys running around fixing a hack job) but I'd rather use a tool which can expand and reduce capacity when I want, which is quite often. It saves me money on cloud hosting to only provision what I need when I need it and be able to expand and contract it at will, easily. Your solution fails at this.
      - bonif 12y ago
        This is not my solution (and I don't know much about monkeys, sorry). This is a cost-effective way to be always maxed out (expanded as you say) with fixed price, this can scale well beyond the needs of almost every business. And , as I said, if you happen to be the next Facebook, Uber, Airbnb or whatever, you will acquire the know-how to scale.
angrybits 12y ago
Because multi-master clustering is extremely hard to do when you need transactional semantics. For those that need it, scaling up is by far the easiest path. Most of us will never exceed the growth rate of server capacity over time, so you can just keep going up. HA using stand-bys and failover is well documented, and quite sufficient I might add. If you don't need transactional semantics and you do need globally distributed multi-master key/value storage, I would not switch from Cassandra to PG, you already have a good solution for that. If you don't need transactional semantics and you also do not need globally distributed multi-master key/value storage, use whatever you want, it doesn't really matter.
norkakn 12y ago
Clustering is really, really hard. Cassandra is one of the better ones out there, but you have to deal with its data model and weird consistency promises (which however weird you think they are, are weirder) The correct way to cluster also changes dramatically depending on your use case. Sure there are things like RAC that promise to make it just work, but those don't scale more than a few nodes. Mongo is kind of the worst in this - it clusters in one weird way, has bad tooling, and subtly destroys your data at scale. The general philosophy with postgres is to do it right, or not do it. There are ways to do specific kinds of clustering, but all of them (just like mongo, oracle, etc) have a lot of nuances to them. If you have a natural shard key, use a bunch of schemas and table inheritance, and eat the downtime during re-shards. Check out citus as well. They have their issues, but they can help you hook up what you need.
- threeseed 12y ago
  > Cassandra is one of the better ones out there I take it you haven't actually used Cassandra much in the last few years. It's data model is almost identical to a typical relational one and it's consistency promises are quite clear: http://www.datastax.com/documentation/cql/3.0/cql/aboutCQL.html http://www.datastax.com/documentation/cql/3.0/cql/aboutCQL.h... And I've scaled Cassandra clusters from 1 to 100 nodes in hours with no issues. It really is quite simple. Likewise have had no issues with MongoDB replica sets. It is definitely not "really, really hard". > postgres is to do it right, or not do it What a pathetic cop out. PostgreSQL has been around for decades they've had plenty of time to have a proven, stable solution implemented.
  - dmpk2k 12y ago
    It's quite simple until you write code that has to deal with AP (as in CAP), and the limitations that come with it. I have, and what a pain that was.
  - norkakn 12y ago
    Cassandra avoids some of the really visible issues by being AP instead of CP. Hbase hits them, but dodges a bit by only having row level consistency. They are solving very different problems. The lack of vector clocks in Cassandra can lead to some very non-intuitive (possible wrong) behavior - check out their counter implementation for some rage on that. It's pretty well made though, and I think C*, Hbase and Postgres all have great uses (along with Redis, and a lot of others) Mongo tends to get things subtly wrong in ways that corrupt data, or that don't scale, and it gives up both A and C.
mslot 12y ago
Depending on your exact requirements, you can use pg_shard [1] for real-time inserts/update/delete, or CitusDB [2] for scaling out selects, or combine them. [1] https://github.com/citusdata/pg_shard https://github.com/citusdata/pg_shard [2] http://www.citusdata.com/ http://www.citusdata.com/ Disclaimer: I work for Citus Data.

icebraining 12y ago

Postgres-XL, Postgres-XC, pg_shard...

agentultra 12y ago

> In 9.4, Logical Decoding supplies a new API for reading, filtering and manipulating the PostgreSQL replication stream. This interface is the foundation for new replication tools, such as Bi-Directional Replication, which supports the creation of multi-master PostgreSQL clusters. Other improvements in the replication system, such as replication slots and time-delayed replicas, improve management and utility of replica servers. See also, ye manual: http://www.postgresql.org/docs/9.4/interactive/high-availability.html http://www.postgresql.org/docs/9.4/interactive/high-availabi...

klaruz 12y ago
I think the problem is clustering is still very much a duct-tape situation in postgresql with no real clear consensus on how to build out a cluster. Postgres-XL looks great for scale out, but you need 4 independent types of servers. Even with all those moving parts, it doesn't provide availability. If you want fail-over, you need pacemaker for the data nodes with traditional sync replication, and something like VRRP for your balancer, and something else to failover the coordinator. Several of these pieces can be tricky to set up in a cloud provider. BDR looks nice, but it looks like there could be lots of gotchas for consistency in there. Maybe it is a magic bullet though... I don't know much about it yet. Contrast with something like rethinkdb, mysql-galera, cassandra, etc, you start up enough nodes for quorum, tell them about each other, and you're pretty much done. The clients can handle the balancing, or you can use a pooler/balancer. In my perfect world, I'd install postgresql-awesome-cluster-edition on 3 nodes, add the 3 IPs (or turn on multicast discovery, if my env can support it), and away we go for read scalability and availability. I do this today for mysql-galera, and other than the fact it's mysql, it's awesome. For writes, if you add 4 or more nodes, there should be some sort of shard system like XL has. That said, postgresql is still clearly the best SQL and even noSQL single node server out there, it's a really great piece of software.
- gthank 12y ago
  If you honestly believe that all you have to do is stand up a bunch of instances of Mongo/Cassandra/whatever and you instantly get acceptable HA, then you need to read the [Jepsen series](https://aphyr.com/tags/jepsen https://aphyr.com/tags/jepsen)
  - internetisthesh 12y ago
    Elasticsearch can run with Zookeeper. Zookeeper is pretty solid. Also, MSSQL with AlwaysOn is something which seems very robust too.
  - nemo44x 12y ago
    It depends on what you consider "acceptable HA". There are many instances where I'm not trying to protect from a network partition (single data center, monitored batch data loads, etc) and don't have a requirement for that level of tolerance. However, you're right in that it's important to know that nearly every distributed system has edge cases where things might not appear as you thought. Elasticsearch has a section on their Website detailing their resiliency efforts. I wish every company was as transparent about what they're doing on that front so we can all plan and consider expectations better.
  - threeseed 12y ago
    Setting up Mongo/Cassandra for HA is still several orders of magnitude less work than with PostgreSQL. And there are plenty of options for dealing with the issues presented in those series.
threeseed 12y ago
That's an API not a solution. I do think (for once) PostgreSQL is addressing it's core weakness and by version 10 will likely have horizontal scalability locked down. The new API is a really positive step.

throwaway90446 12y ago

Just do vnode-based consistent hashing on the client side.

nemo44x 12y ago
How do I add and remove capacity then?
- bonif 12y ago
  You don't need to (and if you ever do, you will have or recruit the know-how) http://www.ovh.com/us/dedicated-servers/big-data/ http://www.ovh.com/us/dedicated-servers/big-data/
  - nemo44x 12y ago
    That is entirely wrong. It might suit your use case (having a bunch of monkeys running around fixing a hack job) but I'd rather use a tool which can expand and reduce capacity when I want, which is quite often. It saves me money on cloud hosting to only provision what I need when I need it and be able to expand and contract it at will, easily. Your solution fails at this.
    - bonif 12y ago
      This is not my solution (and I don't know much about monkeys, sorry). This is a cost-effective way to be always maxed out (expanded as you say) with fixed price, this can scale well beyond the needs of almost every business. And , as I said, if you happen to be the next Facebook, Uber, Airbnb or whatever, you will acquire the know-how to scale.

angrybits 12y ago

Because multi-master clustering is extremely hard to do when you need transactional semantics. For those that need it, scaling up is by far the easiest path. Most of us will never exceed the growth rate of server capacity over time, so you can just keep going up. HA using stand-bys and failover is well documented, and quite sufficient I might add. If you don't need transactional semantics and you do need globally distributed multi-master key/value storage, I would not switch from Cassandra to PG, you already have a good solution for that. If you don't need transactional semantics and you also do not need globally distributed multi-master key/value storage, use whatever you want, it doesn't really matter.

norkakn 12y ago

Clustering is really, really hard. Cassandra is one of the better ones out there, but you have to deal with its data model and weird consistency promises (which however weird you think they are, are weirder) The correct way to cluster also changes dramatically depending on your use case. Sure there are things like RAC that promise to make it just work, but those don't scale more than a few nodes. Mongo is kind of the worst in this - it clusters in one weird way, has bad tooling, and subtly destroys your data at scale. The general philosophy with postgres is to do it right, or not do it. There are ways to do specific kinds of clustering, but all of them (just like mongo, oracle, etc) have a lot of nuances to them. If you have a natural shard key, use a bunch of schemas and table inheritance, and eat the downtime during re-shards. Check out citus as well. They have their issues, but they can help you hook up what you need.

threeseed 12y ago
> Cassandra is one of the better ones out there I take it you haven't actually used Cassandra much in the last few years. It's data model is almost identical to a typical relational one and it's consistency promises are quite clear: http://www.datastax.com/documentation/cql/3.0/cql/aboutCQL.html http://www.datastax.com/documentation/cql/3.0/cql/aboutCQL.h... And I've scaled Cassandra clusters from 1 to 100 nodes in hours with no issues. It really is quite simple. Likewise have had no issues with MongoDB replica sets. It is definitely not "really, really hard". > postgres is to do it right, or not do it What a pathetic cop out. PostgreSQL has been around for decades they've had plenty of time to have a proven, stable solution implemented.
- dmpk2k 12y ago
  It's quite simple until you write code that has to deal with AP (as in CAP), and the limitations that come with it. I have, and what a pain that was.
- norkakn 12y ago
  Cassandra avoids some of the really visible issues by being AP instead of CP. Hbase hits them, but dodges a bit by only having row level consistency. They are solving very different problems. The lack of vector clocks in Cassandra can lead to some very non-intuitive (possible wrong) behavior - check out their counter implementation for some rage on that. It's pretty well made though, and I think C*, Hbase and Postgres all have great uses (along with Redis, and a lot of others) Mongo tends to get things subtly wrong in ways that corrupt data, or that don't scale, and it gives up both A and C.

mslot 12y ago

Depending on your exact requirements, you can use pg_shard [1] for real-time inserts/update/delete, or CitusDB [2] for scaling out selects, or combine them. [1] https://github.com/citusdata/pg_shard https://github.com/citusdata/pg_shard [2] http://www.citusdata.com/ http://www.citusdata.com/ Disclaimer: I work for Citus Data.