From this question: in PostgreSQL REPMGR Master-Slave environment is it necessary to have same H/W config for HOT STANDBY setup?, I was reminded of my experience and disappointment with the hot-standby design.
Say you have millions of users mostly querying. Then 1000 will also create change transactions. It should be really cool to have not just one but 10 stand-by's load balancing all the queries through PgPool-II.
The problem I found was that WAL log processing requires a lot of resources on the stand-by to the point where whatever work the master has done to turn the transaction(s) into a WAL content, that seems insignificant compared to the work left just applying the WAL to the data files. I know this because I tried to run on a very insert-busy workload the stand-by on a lesser hardware, hoping that it would be enough just to "keep up", but it was not. I needed to use the same hardware configuration (I use AWS, so it's easy to try out different ones).
What I then did to catch up, despite the files having grown quite big, it was pretty fast to just use rsync on the data files themselves, and quickly I was back in sync and could continue with processing the WAL updates.
Sadly, you do not gain that much free server power by using one master and several secondaries, because all those secondaries will be quite busy handling the incoming WAL from the master, that they have very little spare resources left to handle a bunch of querying activity.
But this gave me an idea. Two ideas actually.
Use a shared device, also known as "cluster" filesystem, the point being, the slave operates on the same physical drive. But then that may cause contention with increased head-seek activity, which isn't really an issue these days with SSDs. Ultimately this pushes the issue down to the hardware where you have similar things that adding a stand-by will increase some of the load on the master and definitely clog up some significant amount of the stand-by resources just with keeping up. But the more low level seems way less CPU intensive than applying WAL.
Why not use some rsync-based replication from the actual data-files rather than the WAL? Perhaps such a stand-by would not be immediately ready to take over when the master goes down, but a combination of an rsync based delta applied to the stand-by plus the last few amounts of WAL should be able to bring a stand-by to a reliable state just like pure WAL shipping method.
This would seem particularly useful if most of the query activity is not so critical that it has to be up to the second of update from the master. I mean, you can always have a little lag with WAL-based replication, so your results from a standby might just be a few minutes behind of what you would get by querying the master. This means consistent transactions would have to be applied to the master anyway, and some querying is involved there.
Let's think of an airline fare finder use case with a booking feature. Most of the activity will be users finding their best itinerary comparing fares, conveniences, maybe even available seats. Then they start putting a reservation together, but we know that there will be attrition, where, say 50% of the reservation work will be abandoned. That means we would let the users build their reservation, and only when they are getting ready to pay would we begin moving the transaction to the master, then re-check all the preconditions (e.g., is that seat on 18C still available?) and lock the resources, ready to issue the reservation when the payment is confirmed, all in one transaction.
If the stand-by might be 30 seconds behind the master, they could be 3 minutes behind too, or 30 min. The less they can be behind, the more resources they have to spend on tracking the master. This can be tweaked for real world use cases. But clearly it would be beneficial to have a means of a light-weight replication which is good enough and doesn't take all the massive resources that a WAL replay based replication requires.
Am I way off-base?
Can I come up with a poor-man's implementation of that? For example, say I have 3 stand-by servers (the more stand-bys I have the more relevant overall this gets, as every stand-by replicates the heavy work of applying the WAL to the data files.) I would allow each stand-by to fall behind up to n minutes. Say 10. In the 8th minute I would take it down briefly, rsync the data files from the master, and start it back up. This process might take just 2 minutes out of 10. (I think these are somewhat realistic numbers). And my 3 stand-bys rotate doing that, so 2 of the 3 are always up while 1 of them might be briefly down.
This could be improved even with some OS and SAN support, all the way down to RAID mirrors. But practically, I could mount the same file system (e.g., BSD UFS) on the master with -o rw and on the stand-by with -o ro. Even without UFS having any "cluster" support, I can do that. I will have a few inconsistencies, but PostgreSQL is very tolerant of small inconsistencies and can fix them anyway. (I would not dare try anything like this with an Oracle system.)
Another tool I could think of using is BSD UFS snapshot support. I could make a snapshot that is consistent from the file system perspective. PostgreSQL writers could interface with that, write to a consistent checkpoint, issue a file system sync, and take an UFS snapshot. Now any standby who reads its data files from this snapshot would not only have file-system level consistency, but even database level consistency. So with this replication using a file system mounted from multiple PgSQL servers, replication should work well while consuming minimal amount of server resources and also not lead to too much actual disk IO contention (seeks).
And even the disk contention could possibly be resolved by letting a RAID mirror sync to the same snapshot point and then split it off, or do the read activity of the stand-by server querying like crazy only from that mirror, while batching changes from the RAID master so that they can be applied with very low overhead.
So is anyone working on a less costly replication approach? Or in what way are my ideas fundamentally flawed that it couldn't be done?
rsyncorscpdoes? Why should the one way of sequentially writing a file be more expensive than the other one? You say "writing out WAL is expensive". Now where exactly is the bottleneck? I believe that is the key to the answer.