Re:Cloud SQL for PostgreSQL now generally available
by Erwin on Thursday April 19, 2018 @11:58AM
(#16876957)
Amazon's Aurora Postgres database does a similar thing: your master in one zone replicates to a disk that is in all the other zones. Unlike normal Postgres RDS instance it also auto-scales storage to what you use.
Amazon claims better scaling then ordinary Postgres for this.
Re:Cloud SQL for PostgreSQL now generally available
by brazzledazzle on Wednesday April 18, 2018 @08:42PM
(#16872516)
Just speculating but it’s possible the block level is faster because it’s replicated over a dedicated and optimized SAN rather than (potentially) contending with normal network traffic. I assume the database state would only be crash consistent though.
Intuition tells me that you might get better performance if you let the DB itself do the replication but I can't really justify that without real review of what happens.
The postgres docs (https://www.postgresql.org/docs/10/static/different-replicat...) say that the WAL solution has no "Master server overhead" in contrast to the File System Replication solution, but it's not explained and I'm not sure what is meant by that.
I guess with a block device based solution, recovery takes longer, because failover entails you have to actually mount the block device (as no 2 machines can mount it rw at the same time), and then start the DB (or in a more basic implementation, just boot the entire second machine as part of failover), while with WAL streaming both postgres instances would already be running? Wo failover would be faster with WAL streaming?
I would be great if somebody from GCP could elaborate what the tradeoffs here are, how long failover takes, and whether we can expect similar performance and behaviour as with WAL shipping.