I find it difficult to setup PostgreSQL without having a single point of failure and introducing downtime for things such as upgrades.
We aren't doing life changing stuff, true, but a downtime in our system can result in employees (often at the bottom of the economic ladder) not being able to get to work. That gnaws at me. I guess the better alternative is to go for a hosted approach, but that isn't without its own complications and challenges.
Having a master with a read replica and then making the read replica the master in case the master goes down seems to be a very well known methodology. You can use a virtual ip with both servers behind it (keepalived) or a 0 ttl DNS solution (consul). Is you case more complex than that? With master to master replication I feel there are couple of gotchas and you need to design your data in a way to avoid conflict as much as you can. A solution like bdr seems to be an off-the-shelf one that is proven.
I haven't really upgraded postgres in a rapid fashion, usually just keep with whatever version comes with the OS package manager forever.. or whatever docker image I start it with. I guess the same approach for HA can be used to upgrade one at a time, am assuming postgres doesn't break backwards compatibility very often.
You can either automate the failover or have a system to SMS you when it happens. I like to do it manually personally since I have seen the automated failover fail more times than the manual procedure.
I have probably managed over 30 postgres instances in my services that have grown up to 1/2 PB of data and I've never had a single issue with it... postgres, redis, rabbitmq, consul are technologies that you RTFM, set it up, and it just works!
I wouldn't trust a technology that isn't established and widely adopted which is how I see cockroachdb a the moment.
>You can either automate the failover or have a system to SMS you when it happens.
I don't want to be writing code to make things do automatic failover. I'm not a database engineer. I want a database where its standard setup does this stuff for me.
(I would find it really interesting if there was a tightly-integrated "distribution" of Postgresql plus related services that had a setup process as straight-forward as CockroachDB and had automatic read-replicas and failover.)
Compared to all that, CRDB is just a binary on each server pointing to each other. Operational simplicity matters and that's where CRDB is a great alternative.
None of that is automatic and every step introduces problems compounded by the amount of tools used to handle each step. Because of this, the setup is intensive and usually custom to every environment. This is hard to test and be confident in and the source of much downtime or data loss.
Meanwhile CRDB handles all of this seamlessly. As long as you have a quorum of healthy nodes, your database is alive and working. Point a load balancer at all of the nodes and you will always have a database to contact with no other overhead. Postgres could get there, but it isn't anywhere there today.
We still use Postgresql, and I agree, it's setup and forget. But those aren't HA (and don't need to be).
Amazon does this with RDS, and so can you.
This really isn't very tough to achieve with replicas. And for upgrades you just add a new replica with the latest version and promote it to master and replace the remaining nodes one at a time.
They must at least have read replicas given the traffic for the site.
I feel like you need more hardware than that for a hello world kubernetes tutorial...
Bit outdated but probably still holds true.
It's pretty amazing what SQL Server can do...just wish it wasn't so expensive
One downsides I didn't like though is no incremental backups unless enterprise. There's PG dump but not sure if it'd work on a bunch of threads and stuff. If using MySQL there's the MyDumper project that looked interesting.
Then there's no full text search is a downside, but I know it seems people recommend running a separate search solution anyways so that can be a little slow to get started. Easier if you could just run it in one system in the beginning, and who knows maybe search isn't a bottleneck anyways depending on the implementation of the database or you want to be able to still full text search while debugging maybe.
Then no geospatial can be a downside for some types of businesses or use cases, say a place lookup, factoring it into a game matching algo, etc. I've been really interested in MongoDB lately though, but was playing with a few newer SQL based databases for what to use on my own project. Then I know some companies actually run multiple databases too, some stuff might be noSQL while other workloads are SQL databases too, but I know some rather just one a single database so only one source of truth. Then I know PostgreSQL has the idea of foreign data wrappers [0], so I guess then you could treat a MongoDB collection as a table. So maybe you had a users collection, you could join on it I think using this but never played with it. However this seems mostly Postgre itself, other Postgre compatible databases don't implement this as far as I can tell.
However I believe even MongoDB with it sharding can't handle GEO unless you separate that data in a unsharded collection, which is then placed on any replica set in the cluster. So maybe you could split geo results up by city level in different collections but then if you wanted to find near by things that could go into other cities based on the radius, you'd have to write your own logic. Unsure how larger sites like say Yelp handles GEO lookups.
Only big downside I see with MongoDB though is no joins, so say you had a social networking site and wanted to hide posts by users suspended from the results, you'd have to write your own logic to do so. I think it'd be so cool if you could like symlink a value from one document to another. I think OrientDB has something like that though. I was asking a friend who uses Mongo for advice, apparently in that case you'd just update every single post when you suspend that user - not sure if that's really efficient or not but I doubt a single user would have millions of posts... So probably less than 10K posts at the most maybe. However I guess with social feeds themselves, seems it recommend to fan out on write instead of fanning out on read, so in a way you can sometimes precompute things to help the database. All about making decisions and tradeoffs I guess.
You won't get that popular. And if you do, replacing database layer would be a party with cases of champagne.
The whole point of cdb is to enable things like Google scale. But all I ever see on it are random devs spinning up three nodes and being so proud.
Which is too bad because I'd love to see someone pushing its limits, and betting mission critical services on it that nothing else could handle.