SQLite Forum

SQLite WAL Pages Checkpointed

SQLite WAL Pages Checkpointed

(1) By JasonS (jsmedley) on 2021-12-02 00:25:46 [link] [source]

I looked and as far as I can tell there is no way to know what pages have been checkpointed. Is that correct?

(2) By Richard Hipp (drh) on 2021-12-02 00:55:17 in reply to 1 [link] [source]

There is no API for that, no. SQLite knows internally, obviously, but it won't tell you. Why do you want to know? What difference does it make?

(3) By JasonS (jsmedley) on 2021-12-02 01:51:54 in reply to 2 [link] [source]

I would like to know when a page is changed so that instead of searching through all pages for a change I can replicate the pages that I know have changed. I currently monitor the WAL file to know which pages change between checkpoints and create a delta out of those pages. That seems to work well enough, just was hoping I could do something more native.

(4) By Simon Slavin (slavin) on 2021-12-02 12:58:03 in reply to 3 [source]

Did you know about the session extension


? It doesn't work the way your system does, but it's a supported part of SQLite, does the job efficiently, and doesn't require external access into a journal file.

(5) By JasonS (jsmedley) on 2021-12-02 13:13:09 in reply to 4 [link] [source]

I am familiar with that. I am currently supporting live replication through using the WAL. Apparently I'm checking for page changes in the most efficient manner already.

(6) By ddevienne on 2021-12-02 13:21:20 in reply to 5 [link] [source]

live replication through using the WAL

So your system is similar to Litestream?
Is yours open-source too? If not, will it be eventually?

Richard, you've mentioned in a radio interview not wanting to get
into that space, and knowing of other initiatives similar to Litestream,
if I recall correctly. There's obviously lots of interest in this area
so is there hope to one day seeing primitives (like a hook?) in SQLite Core
to better support these WAL-replication use cases in a more official way?

Thanks, --DD

(8) By JasonS (jsmedley) on 2021-12-02 21:01:28 in reply to 6 [link] [source]

Yes you can view it at https://github.com/jtsmedley/ipfs-sqlite-cli. I have based my work off of Litestream, but I am syncing to IPFS.

Also yes the primitive like a hook would be the wish. The hook would support more than just the WAL-replication type and could potentially include a list of all pages changed from that commit.

(9) By ddevienne on 2021-12-03 08:57:40 in reply to 8 [link] [source]

Thanks for sharing.

About the hook, well, maybe Richard will answer.
I have not had much luck when asking questions these past few months.

(7) By ddevienne on 2021-12-02 13:25:09 in reply to 4 [link] [source]

The Session Extension is more Logical replication,
while WAL-replication is more Physical replication.

There are PROs and CONs with both approaches.
The latter basically has no limitations, unlike the former.