I've been thinking about the two optimizations mentioned at the end a bit more, so let me share my thoughts before I forget that:
On 8/18/22 23:10, Tomas Vondra wrote: > > ... > > And maybe we could then use the LSN to read the increment from the WAL > during decoding, instead of having to read it and WAL-log it during > commit. Essentially, we'd run a local XLogReader. Of course, we'd have > to be careful about checkpoints, not sure what to do about that. > I think logging just the LSN is workable. I was worried about dealing with checkpoints, because imagine you do nextval() on sequence that was last WAL-logged a couple checkpoints back. Then you wouldn't be able to read the LSN (when decoding), because the WAL might have been recycled. But that can't happen, because we always force WAL-logging the first time nextval() is called after a checkpoint. So we know the LSN is guaranteed to be available. Of course, this would not reduce the amount of WAL messages, because we'd still log all sequences touched by the transaction. We wouldn't need to read the state from disk, though, and we could ignore "old" stuff in decoding (with LSN lower than the last LSN we decoded). For frequently used sequences that seems like a win. > Another idea that just occurred to me is that if we end up having to > read the sequence state during commit, maybe we could at least optimize > it somehow. For example we might track LSN of the last logged state for > each sequence (in shared memory or something), and the other sessions > could just skip the WAL-log if their "local" LSN is <= than this LSN. > Tracking the last LSN for each sequence (in a SLRU or something) should work too, I guess. In principle this just moves the skipping of "old" increments from decoding to writing, so that we don't even have to write those into WAL. We don't even need persistence, nor to keep all the records, I think. If you don't find a record for a given sequence, assume it wasn't logged yet and just log it. Of course, it requires a bit of shared memory for each sequence, say ~32B. Not sure about the overhead, but I'd bet if you have many (~thousands) frequently used sequences, there'll be a lot of other overhead making this irrelevant. Of course, if we're doing the skipping when writing the WAL, maybe we should just read the sequence state - we'd do the I/O, but only in fraction of the transactions, and we wouldn't need to read old WAL in logical decoding. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company