4 ms·

When you issue that command in MemSQL, what does it actually do? Physical replication in PostgreSQL is a bit more work to set up, but its behaviour is rather s

When you issue that command in MemSQL, what does it actually do? Physical replication in PostgreSQL is a bit more work to set up, but its behaviour is rather straightforward. As far as I understand it, all it really does is the same as a standalone database, except that instead of generating WAL segments in response to user queries that are then applied to the database, it gets them by streaming them from the master. It's easy to see how the system behaves when nodes fail. Similarly, a PITR is achieved by telling PostgreSQL to (optionally) retrieve WAL at startup from an archive and apply it until the desired point. The mechanism is identical to streaming replication, except non-continuous. Referring to your comment below, postgresql.conf, pg_hba.conf and recovery.conf are data that can't really exist in the database, because it's needed before the database can even start up. With logical replication in PostgreSQL 10, you could feasibly implement a command identical to MemSQL's, but the problem is that its behaviour is no longer quite so straightforward to understand compared to physical replication.

manigandham 10y ago
It does everything. There's nothing else to configure. MemSQL is a distributed SQL database so a "cluster" is multiple machines supporting a single logical interface to multiple databases. Another cluster in another datacenter made up of multiple machines can then issue this command which will automatically contact the master cluster, setup connections in parallel between several machines, create the replica database, ask the master to create a new snapshot, stream the snapshot over, build the database, then subscribe to the change log stream including data + schema changes, all async and very fast. Yes, this is most commonly referred to as "physical replication" but it covers about 90% of use cases where a database needs a replica somewhere else. Postgres is great but too often the features keep getting blown out into ridiculous complexity instead of just making the basics work well and without tweaking anything, then building from with optional configuration for greater functionality.
- chousuke 10y ago
  I was actually asking what it actually does, not what it does conceptually. My main issue with these advanced "easy" solutions is that while they're easy to setup, often that comes at the cost of missing understanding of behaviour under load / failure scenarios. I'm not saying PostgreSQL couldn't improve (it definitely can), but when it comes to data, I advocate simplicity over ease. Very quick reading gives me the impression that MemSQL's replication is actually per-partition logical replication (might be physical, if partitions are physically separate databases, but they don't seem to be) and uses an algorithm (didn't find which) to decide which node to failover to when something occurs. It also seems to differentiate between metadata nodes and storage/worker nodes.

manigandham 10y ago

It does everything. There's nothing else to configure. MemSQL is a distributed SQL database so a "cluster" is multiple machines supporting a single logical interface to multiple databases. Another cluster in another datacenter made up of multiple machines can then issue this command which will automatically contact the master cluster, setup connections in parallel between several machines, create the replica database, ask the master to create a new snapshot, stream the snapshot over, build the database, then subscribe to the change log stream including data + schema changes, all async and very fast. Yes, this is most commonly referred to as "physical replication" but it covers about 90% of use cases where a database needs a replica somewhere else. Postgres is great but too often the features keep getting blown out into ridiculous complexity instead of just making the basics work well and without tweaking anything, then building from with optional configuration for greater functionality.

chousuke 10y ago
I was actually asking what it actually does, not what it does conceptually. My main issue with these advanced "easy" solutions is that while they're easy to setup, often that comes at the cost of missing understanding of behaviour under load / failure scenarios. I'm not saying PostgreSQL couldn't improve (it definitely can), but when it comes to data, I advocate simplicity over ease. Very quick reading gives me the impression that MemSQL's replication is actually per-partition logical replication (might be physical, if partitions are physically separate databases, but they don't seem to be) and uses an algorithm (didn't find which) to decide which node to failover to when something occurs. It also seems to differentiate between metadata nodes and storage/worker nodes.