sqlite VS ctlstore

Edit: Thanks to u/lbgdn, the cross compilation issue was resolved. I was able to bypass the CGO issue by using pure-Go SQLite driver.

Each call out to a C library from Go locks an OS thread rather than participating in the go routine scheduling. If stuff happens that makes those threads slow or have some issue, they just sit here. I had a server with C Kafka libraries and each time the network glitched the CPU would spike up wards and not decline. With pure go the scheduler is able to just swap out and ignore code that is stuck some where (unless it were in a busy loop).

With this SQLite implementation I can stick it in my server (as a background, near real time mirror of memory state to disk) without worrying that the main loop will get clogged or my real time path will be hurt.

I use glabarez’ wrapper which makes it have an API like other Go databases: https://github.com/glebarez/sqlite/ and hey have been very responsive to issues I raised.

I have been running load tests against it for a few weeks, and it is quite solid.

RTFM ``` import ( "gorm.io/driver/sqlite" // Sqlite driver based on GGO // "github.com/glebarez/sqlite" // Pure go SQLite driver, checkout https://github.com/glebarez/sqlite for details "gorm.io/gorm" )

We replicated our MySQL database to a SQLite edge at Segment in ctlstore: https://github.com/segmentio/ctlstore

We considered tailing binlogs directly but there's so much cruft and complexity involved trying to translate between types and such at that end, once you even just get passed properly parsing the binlogs and maintaining the replication connection. Then you have to deal with schema management across both systems too. Similar sets of problems using PostgreSQL as a source of truth.

In the end we decided just to wrap the whole thing up and abstract away the schema with a common set of types and a limited set of read APIs. Biggest missing piece I regret not getting in was support for secondary indexes.

> it is only practical for situations where the write rate (<100/s total) and data volumes (<10GB total) are low.

This comment from the GitHub project page is pretty important. Configuration data often sees slow change, and isn't huge so a custom approach seems viable. I wonder how close they are to that 100/s ceiling.

There's also an unmentioned transition to eventual consistency happening here:

> The implications of this decoupling is that the data at each instance is usually slightly out-of-date (by 1-2 seconds).

> The reader API provides a way to fetch an approximate staleness measurement that is accurate to within ~5 seconds.

That's could lead to more complex application logic or risk of confusing users with stale behavior. No free lunch here.

[1] https://segment.com/blog/separating-our-data-and-control-pla...

[2] https://github.com/segmentio/ctlstore

at segment we benchmarked https://github.com/segmentio/ctlstore against this driver. We saw about a 50% hit to read performance, so we didn't move forward with it, but the improvements in service build times were really appealing.