Change Data Capture

[benbjohnson]

While Litestream performs physical replication of WAL frames, it would be useful to derive logical changesets from those physical pages. This could be done by using the Ptrmap pages to traverse up the b-tree to the root and from the root page number we can associate the changed page with a table & schema.

One tricky part would be determining how this information is relayed to the application. It could potentially be streamed via a unix socket and the application could have a client that connects to it.

The resulting stream could be a stream of JSON objects or perhaps the session extension format could be used.

[gedw99]

Tidb has cdc so adding in case it helps

https://github.com/pingcap/ticdc

[benbjohnson]

Thanks, @gedw99. I didn't realize PingCAP had a CDC.

[simonw]

This would be incredibly useful. I've built systems in the past that listen to the MySQL replication stream and turn that into events - having that capability for SQLite would be fantastic.

[freshteapot]

Seeing you mentioned the sessions extension, I got curious what is the state of its use in go on github.

• https://github.com/bvinc/go-sqlite-lite/blob/master/sqlite3/session.go
• https://github.com/crawshaw/sqlite/blob/master/session.go
• Issue request https://github.com/mattn/go-sqlite3/issues/825

[gedw99]

@freshteapot I asked if this is possible at https://gitlab.com/cznic/sqlite/-/issues/60

[freshteapot]

@gedw99 thank you for the tip! I wiill keep an eye on this project! sqlite without cgo would be very cool!

[gedw99]

@benbjohnson this one is a good primer: https://medium.com/event-driven-utopia/a-gentle-introduction-to-event-driven-change-data-capture-683297625f9b

[fujohnwang]

interested ;)