Lazy Snapshots - Mailing list pgsql-hackers

One of the problems with Hot Standby is that a long running query on the standby can conflict with VACUUMed rows on the primary, causing queries to be cancelled.

I've been looking at this problem for about a year now from various angles. Jeff Jane's recent thoughts on procarray scalability have led me down an interesting path, described here. Taken together, this has led me to rethink completely the strategy used for avoiding conflicts in the Hot Standby patch.

Currently, we take eager snapshots, meaning we take a snapshot at the start of each statement whether or not it is necessary. Snapshots exist to disambiguate the running state of recent transactions, so if a statement never sees data written by recent transactions then we will never actually use the snapshot.

Another way of doing this is to utilize lazy snapshots: do not take a snapshot when a statement starts and only take one at the point that we need one. No other changes to the MVCC mechanisms are proposed.

Is that possible?

The time the snapshot is taken is the time of the consistent viewpoint from which all data access during a statement is judged. Taking the snapshot later, at an undefined point in the future means that the consistent viewpoint is actually floating. When we execute the statement we won't actually know which viewpoint will be used to derive the answers to a query.

A floating, yet consistent viewpoint is in my opinion a good thing, since it includes a more recent database state in the answer to a query than we would otherwise have used. Consider the case where a very large table has a "select count(*)" executed on it. The scan begins at block 0 and continues through the table until the end, which for purposes of an example we will say takes 1 hour. Rows are added to the table at a constant rate of 100/sec and immediately committed. So by the time the scan has finished it will deliver an answer that is wrong by 360000. Using a lazy snapshot would give us an answer almost exactly correct, though of course Heisenbuggers may dispute the existence of a "correct" answer in this case.

So let's look at some theory details:

* Scan begins, no snapshot set. A row is inserted and transaction commits. Scan progresses until it sees a recent row. Scan takes snapshot; the row is now visible to it and progresses. Another row is inserted and transaction commits. When we later come to second new row, we already have a snapshot, so that row is invisible to us. Results of query are consistent to the point we took the snapshot, which happened when we saw the first row. Are the results consistent only to end of transaction that created that row? No, other transactions can also have committed after it and yet before we take snapshot. The recent transaction is the catalyst for us to take a snapshot, though the snapshot is not dependent upon the xid of the new row we have seen.

* Scan begins, no snapshot set. Ahead of scan a row that would have been visible at start of scan is deleted, commits and removed by VACUUM/HOT. The scan has no evidence that a removal has taken place, never sees contention and thus never takes a snapshot. This isn't a problem; the row removal created an implicit xmin for our scan. If we later took a snapshot the xmin of the snapshot would be equal or later than our previous implicit xmin and so MVCC would be working. This shows that it is wrong to presume that taking no snapshot at all means that the time consistent point on the scan was at the start of a statement, it may not be.

* We open a cursor, then start issuing updates where current of. Does the cursor need a snapshot? I don't think it does, since we have special visibility rules for rows produced by our own transaction and we do not need a snapshot to disambiguate them. ISTM there may be a corner case where we need cursors to take snapshots, but I haven't seen it yet.

Does that cover all the cases? Some main ones, but let's see if other problems emerge? Anyone?

OK, in theory it seems to work, so how will it work in practice and does that cause other difficulties?

* We will hold a new global variable LastGlobalXmin, which is maintained by GetSnapshotData(). We can access it atomically, without locks.

* In XidInMVCCSnapshot() if our snapshot is NULL then we update RecentXmin from LastGlobalXmin and test using that because we don't have a snapshot xmin. If this is sufficient to return false then that's all we do. Otherwise, we now get a full snapshot and then continue as normal. (There may be some API rework to allow this to happen, so I think I papered over a few difficulties here, but in broad terms, this appears to work).

Lazy snapshots mean that some things normally updated during snapshot taking will fall behind somewhat. This has a couple of effects that we can mitigate in various ways

* In TransactionIdIsInProgress() if xid < RecentXmin we update RecentXmin from globalxmin and retry the test.

* We probably need to do something with HOT page cleaning as well, but that is fairly subtle bit of tuning that I expect to see a range of viewpoints on. Various options exist from do-nothing through to re-check xmin prior to each cleaning check or somewhere in between.

I have no idea whether this idea is patented and I would appreciate some help in researching whether this idea is legally able to be implemented by PGDG, so I can remain untainted.

Benefits

* Scalability: The reduction in ProcArrayLock requests from snapshots will drop away considerably as a result of these changes. (It may prove feasible to provide an option to lightly partition the procarray to increase commit rate, but that would be later)

* Hot Standby: Implementing this will likely significantly reduce the number of queries cancelled during Hot Standby. This will be because many queries will not have snapshots at all and the queries that do will typically have much younger snapshots.

* Accuracy: More accurate answers to long database queries.

I will be removing various parts of code from Hot Standby patch while this is discussed. I'm not very available at moment, so my replies are likely to be considerably delayed.

Best Regards, Simon Riggs