For a query like this, a partial index covering only unsynced rows would serve best. CREATE INDEX ON tbl (id) WHERE sync_done = FALSE; However, for a use case like this, other synchronization methods may be preferable to begin with: Have a look at LISTEN / NOTIFY. Or use a trigger in combination with dblink … Read more
I think they are sometimes used interchangeably, and if the transaction only involves one object then they are essentially the same, but MVCC is an extension of optimistic concurrency (or a version of it) that provides guarantees when more than one object is involved. Say that you have two objects, A and B, which must … Read more
rs.slaveOk() run in the mongo shell will allow you to read from secondaries. Here is a demonstration using the mongo shell under MongoDB 2.4.3: $ mongo –port 27017 MongoDB shell version: 2.4.3 connecting to: 127.0.0.1:27017/test replset:PRIMARY> db.foo.save({}) replset:PRIMARY> db.foo.find() { “_id” : ObjectId(“51bf5dbd473d5e80fc095b17”) } replset:PRIMARY> exit $ mongo –port 27018 MongoDB shell version: 2.4.3 connecting … Read more
I use following SQL queries to check status on Postgres v11 usually. On master: select * from pg_stat_replication; On replica (streaming replication in my case): select * from pg_stat_wal_receiver;
TL;DR: Logical replication sends row-by-row changes, physical replication sends disk block changes. Logical replication is better for some tasks, physical replication for others. Note that in PostgreSQL 12 (current at time of update) logical replication is stable and reliable, but quite limited. Use physical replication if you are asking this question. Streaming replication can be … Read more
Answering my own question is not the best thing to do, but, as I ultimately dropped the idea, I’d like to share on the rationale that worked in my case. I’d like to emphasize that this rationale might not apply to all cases, so it’s up to architect to decide. Generally, the first main point … Read more
This is the full step-by-step procedure to resync a master-slave replication from scratch: At the master: RESET MASTER; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; And copy the values of the result of the last command somewhere. Without closing the connection to the client (because it would release the read lock) issue the command … Read more