How far has postgres come w.r.t. setting up a cluster with automatic fail-over and recovery? I didn't see the author address this aspect of Rethink that has a lot going for it.
Aphyr's "call me maybe" series shows just how hard it is to get automatic failover and recovery working just right. If you're not ready to become a full-time expert on the topic, and pay for more servers for the same load, then simple replication and manual promotion can result in less downtime and less dataloss. I've seen multiple small startups have cassandra clusters fail, because they did not maintain them properly. I've seen them mysteriously lose data in elasticsearch clusters, for the same reason.
That said, GitLab has recently shown that even simple replication can be screwed up. I'd say, the lesson is, the most effective thing is to keep it simple (not "easy"), and avoid disaster caused by mundane mistakes.
I'll probably try it out on a service where I'm writing simultaneously to two different databases, one local and one distributed. And then log any issues where I see an inconsistency. I won't be relying on it for a while for anything critical.
Do you have any queries iterating over the entire table?
Yes. It's currently using 80GB of disk, so regarding disk space I can scale for years. I have tiering system where older data gets stored in Google cloud storage, and grabbed on demand. So regarding space, there isn't an issue scaling up. It's also trivial to increase disk space on live VMs on GCE.
> So you scale the database by switching to a stronger machine?
That's my plan. We're at about 50% overall load as I write this, and here's the usage on the database server: "load average: 0.09, 0.16, 0.17". We're collecting historical Prometheus and other monitoring about load and queries, etc. By the time we need 32 cpus (the GCE limit), either the GCE limit will be raised, the company will be dead (no way in hell), or the company will be wildly successful. So in the only case in which I need to scale out, it will be an exciting new problem that I can hire a team to help with, and we'll benefit from understanding exactly what the problem is we need to solve.
I have wasted an enormous amount of time until now due to premature worry about scalability, fueled by my naive assumptions about how quickly SageMathCloud would grow.