search

medama-io / medama

Self-hostable, privacy-focused website analytics.
https://oss.medama.io
319 stars 5 forks source link

Refactor SQLite Implementation #121

Open gedw99 opened 4 weeks ago

gedw99 commented 4 weeks ago

It’s got wal2 support and so will have higher perf for multi writer

https://www.reddit.com/r/golang/comments/1esdeq5/sqlite_driver_ncrucesgosqlite3_v0180/

gedw99 commented 4 weeks ago

Not that this is not backwards compatible .

the ABI is different so best to test carefully on an existing project first .

ayuhito commented 4 weeks ago

@gedw99, thanks for the notification! I'm actually partially debating whether I should remove github.com/ncruces/go-sqlite3 in favour of the DuckDB SQLite extension, if there isn't a major change in binary size, since that would simplify our database implementation significantly with less drivers to worry about.

The performance of the SQLite implementation doesn't matter since it has barely any user metadata. Most of the heavy lifting and optimisations should be focused around our DuckDB implementations.

ayuhito commented 4 weeks ago

To be fair... I don't think it even matters if we just use plain old DuckDB for this and cut SQLite out of the entire equation for this. Since we only have one user row with a couple hundred website rows max to worry about, and I don't plan on adding a teams feature to the self-hosted product at least, the performance overhead of using an OLAP DB for this purpose would be negligible...

gedw99 commented 3 weeks ago

I also like aspects of Duckdb.

I also like SQLITE because I can easily do MASTER / MASTER replication with it using Marmot ( NATS Jetstream. Works on any cloud.