Show HN: My Go SQLite driver did poorly on a benchmark, so I fixed it (github.com)

> Sqinn-Go is a Go (Golang) library for accessing SQLite databases without cgo. It uses Sqinn https://github.com/cvilsmeier/sqinn under the hood. It starts Sqinn as a child process (os/exec) and communicates with Sqinn over stdin/stdout/stderr. The Sqinn child process then does the SQLite work.

> If you want SQLite but do not want cgo, Sqinn-Go can be a solution.

This seems pretty clear to me about what's happening. It makes an OS call to a third-party executable (sqinn), then pipes the result from stdout back into the Go code. The advantage is you don't have to compile the C code alongside your Go code.

Honestly, I don't really know how much more clear the author could be. I guess if you don't know what stdin, stdout, and stderr are it might be confusing? It's hard to imagine a programmer who is interested in this library but isn't immediately familiar with those concepts.

So, my bindings wrap a WASM build of SQLite. The WASM module exports functions (like sqlite3_exec) for Go to call, and imports Go functions (like go_full_pathname) for WASM to call.

To call these functions you need to get “function pointers” to them by “name”, and this is actually a very slow process, orders of magnitude slower than actually calling the function.

So I used to setup all the function pointers at instantiation time, and then reuse them for every call.

But this is not supported, and fails, when used reentrantly: Go calls sqlite3_step, which in turn calls some Go code (e.g. to implement a virtual table) which then calls sqlite3_step again (the virtual table reads data from another table). Only happens when the same function is called: stack pointers are kept in a structure that gets reused, so the stack gets corrupted.

The simple fix is to never reuse these structures, but that makes everything significantly slower. So I needed to cache them, but not allow concurrent reuse.

The caching I was already doing (hash based) was not good enough. So I switched to a 4x wider PLRU bit cache. Hit rates are now over 99%, the cache is itself fast, and lazily filling it improves startup times.

Fixing that hot path surfaced others, also recent features. Doing badly on a benchmark is a great way to improve!

PS: wazero is a great piece of software, tested and fuzzed to death. They delayed releasing their next compiler engine for 3 months because of a stack corruption bug that only happened rarely in fuzzing (because of an interaction with signal handlers used by the Go fuzzer). They were all over my bug, because the result was also stack corruption, on a compiler that had been tested to death and passed. But my bug was due to API misuse, not any wazero fault.

  1. Go+SQLite user: No CGO
  2. Go user
  3. Wanting to use the library
  4. Someone following a link from HN
  5. ...

Heads up about the modernc library, it has been stuck on an old version of sqlite for several months [1]. It seems like maintainer time is the limiting factor [2]. There has been a call to arms on that issue page, the maintainer is looking for help, but it looks like not much has arrived. It seems like it might trace back to blockers in the C-to-Go compiler.

It's a major undertaking and a very impressive piece of work, but I'm not surprised it's a struggle when big roadblocks get hit. I hope they find a way to progress, but I'm very relieved to be seeing some CGo-free alternatives like ncruces/go-sqlite3 emerging. I'm going to give it a try for sure and see if I can live with the compromises.

Squinn-go looks very compelling too, but I don't like that it requires the squinn binary to already be installed on a user's machine, I think that gives with one hand and takes with the other: sure, I get to avoid CGo, but I also lose the turnkey, single-command install, static build benefits Go brings out of the box.

Seconding the point about nitty gritty, I'd read it for sure too!

  [1]: https://gitlab.com/cznic/sqlite/-/issues/154
  [2]: https://gitlab.com/cznic/sqlite/-/issues/164

The commits implementing the new function cache are these two: https://github.com/ncruces/go-sqlite3/compare/0b093b7c...986...

Creating good, representative, benchmarks is hard. I use speedtest1 [1] but this tests WASM/SQLite and my VFSes, not the Go driver that wraps it.

I already forked this benchmark and will run it regularly, but I may also adapt it into a Go benchmark (easier to run, benchstat compatible, etc).

[1] https://sqlite.org/src/file/test/speedtest1.c

https://zig.news/kristoff/building-sqlite-with-cgo-for-every...

If you were using an ORM, usually the ORM would expect to be sitting on top of the lower-level database/sql. (It's turtles all the way down!)

Dropping the database/sql compatibility requirement allows for more direct (and possibly faster) interaction with the database. This approach can unlock database-specific features and potentially improve performance, but it clearly deviates from the standard API envisioned in database/sql. You might also end up losing the ability to use an ORM, but usually you don't really want to mix ORM and non-ORM in the same codebase anyway.

However, adhering to database/sql also brings significant benefits, such as simplifing switching between different database systems with only small code alterations. While schema changes might still be necessary, they are generally external to the core codebase.

This is github specific, not part of commonmark AFAIK

But you can try to use my build scripts, and tweak my code, to build SQLite+sqlite-vss+Faiss into one large WASM. If you use my patches/imports/exports/config for SQLite, my binding will gladly take your WASM blob, without modification to the Go code.

All you need to do is set this global variable (and not import package embed, which embeds my WASM and sets this variable for you at init): https://pkg.go.dev/github.com/ncruces/go-sqlite3#pkg-variabl...

Ping me on GitHub if you need help.

Vector search is on my meters long todo list, but I'd rather do it in Go if at all possible.