Hacker News Reader

Comment by k_sze | Hacker News Reader

Bye bye MongoDB.

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.

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

> 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.

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.

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)

Elasticsearch can run with Zookeeper. Zookeeper is pretty solid. Also, MSSQL with AlwaysOn is something which seems very robust too.

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.

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.

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.

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

How do I add and remove capacity then?

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/

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.

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.

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.

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.

> 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.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.

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.

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.

> Mongo is kind of the worst in this - it clusters in one weird way, has bad tooling, and subtly destroys your data at scale.

I'm genuinely interested. How does Mongo corrupts data? Thanks.

The most infuriating way is that it will roll back its oplog on an election, and sometimes throw away confirmed writes.

Mongos does weird magic as well when it gets confused, and will confirm writes to the wrong shards during "interesting" situations.

This is more than worrysome. I googled for it and found this http://docs.mongodb.org/v2.6/core/replica-set-rollbacks/

Is this behaviour what you are referring to?

> A rollback reverts write operations on a former primary when the member rejoins its replica set after a failover. A rollback is necessary only if the primary had accepted write operations that the secondaries had not successfully replicated before the primary stepped down. When the primary rejoins the set as a secondary, it reverts, or “rolls back,” its write operations to maintain database consistency with the other members.

Yeah, it's worse than it sounds though. If you write to 2/3 nodes, and the third gets elected, you can roll back majority confirmed writes. I think it did even worse things when near capacity, but those are harder to pin down.

So the "majority" write concern is not safe. However it seems that the number of nodes that must confirm a write can be configured http://docs.mongodb.org/manual/core/replica-set-write-concer... From what I read there it seems that if you want to get a confirmation from all the nodes and one gets offline you'll be waiting forever (but there is wtimeout). I'm afraid that any db cluster with any technology has similar problems.

> If you have a natural shard key, use a bunch of schemas and table inheritance,

They are working on improving FDW API to help make foreign tables available as inheritance children. I think It's a step forward...

This plus zookeeper to manage shard transitions would be awesome.

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 [2] http://www.citusdata.com/

Disclaimer: I work for Citus Data.

Reply on news.ycombinator.com