Hacker Newsnew | past | comments | ask | show | jobs | submitlogin
Show HN: My Go SQLite driver did poorly on a benchmark, so I fixed it (github.com/ncruces)
234 points by ncruces on Dec 15, 2023 | hide | past | favorite | 58 comments
https://github.com/ncruces/go-sqlite3 was doing poorly on this benchmark that was posted yesterday to HackerNews [1].

With the help of some pprof, I was able to trace it to a serious performance regression introduced two weeks ago, and come up with the fix (happy to field questions, if you're interested in the nitty gritty).

It's not the fastest driver around, but it's no longer the slowest: comfortably middle of the pack. It's based on a WASM build of SQLite, and thanks to https://wazero.io doesn't need CGO.

[1]: https://news.ycombinator.com/item?id=38626698




Hey, that's awesome. I think if I wanted a CGo-less solution, I would've probably picked the modernc variation, but honestly this looks very polished + the modernc version is known to be slow in some cases (though, the benchmarks actually made it look alright, realistically.)

One question I have is, is there a strategy to stop the performance from accidentally regressing in the future? Seems like it might not be a bad idea to include some benchmarks in the repository if you don't already.

Also, personally, I am interested in the nitty gritty of why it was slow and how you fixed it, so if you write it anywhere in this thread at least I will definitely read it through.


> I would've probably picked the modernc variation

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 nitty gritty is here, mostly: https://news.ycombinator.com/item?id=38652566

About my driver, the major compromise to be aware of is that WAL mode support is limited. No tiptoeing here: I call that caveat out in the front page.

My driver reimplements the entire SQLite OS Interface (VFS) in pure Go. This makes it very portable, but it's also a risk: it's definitely not as extensively tested as SQLite's. And the WASM sandboxing model makes shared memory a hard problem to solve, which prevents WAL mode from working.

I have some ideas for work arounds, but: they're hard to implement, and hard to "prove" correct. Idea 1 is a VFS for WAL where SQLite believes it's using memory journal mode, but the wrapper uses the standard WAL format to persist disk. It's a lot of work, but standard SQLite tools can recover a crashed DB. Idea 2 is for the WASM host to simulate shared memory through copying. This should work (there are explicit locks and memory barriers to use as copy points), but it's hard to "prove" correct (without bothering SQLite developers to death with details).

I had some hope that the work being done in libsql to virtualize the WAL would help, but their API for this is just terrible. They simply extracted and exposed internal SQLite stuff, but have no documentation, no usage examples, or even any public VFS implementation using these APIs. And I honestly don't trust their ability to maintain a quality fork. An API that's not dogfooded nor documented is not an API.


Thanks for the info, I think I'll be using this SQLite library next time this comes up, then. I do think the modernc library is really cool, but there are some aspects of it that I already disliked, so I'm more than willing to see if the web assembly approach is better (it looks surprisingly tractable.)


One thing I tried to make sure, to avoid the pitfall modernc is having, is to make sure building "the WASM BLOB" is easily reproducible with widely available tools:

https://github.com/ncruces/go-sqlite3/blob/main/.github/work...

I do apply some light patches to SQLite, but so far they've always cleanly applied, and I can produce a new release within hours of being notified of SQLite releases.


The nitty gritty is in another answer.

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


I use the Mattn library. I don't care as much about not having Cho in my program. Sqlite project uses c and they have rigourous testing for each release. With a driver written in go I couldn't be sure what is different. This could result in bugs or features that are not available.


Moreover, if it's about cross-compilation, why not using zig?

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


It's not just about cross compilation.

There's also debuggability and other aspects of development.

It's an anecdote, but I've had a better experience debugging issues while developing SQLite extensions and VFSes for this driver than I've had in other "cross runtime" / "bindings" work I've done before (with Cgo, JNI, P/Invoke).


I'm curious what issues you had with P/Invoke. I use SQLite's C API from C# using P/Invoke rather than using a high-level .NET library. I've implemented virtual table modules, VFSes, application-defined SQL functions, you name it. I even reach into SQLite and call their internal tokenizer directly. The Visual Studio debugger was key to getting this done productively. With C#'s high level of interoperability with C, I can't imagine a similar "SQLite transpiled to C#" project gaining traction in our ecosystem. I think C# stands out from the crowd on interop with C libraries.


Issue one: someone else was employed professionally to develope those bindings for you. :)

Once that's done correctly and at a high quality, your life is easier.

Developing those bindings is another story. But PInvoke is one of the best development stories, I'm not dinging on it.


No, I wrote my own bindings to the C API with P/Invoke. I _don't_ use any other high-level library in .NET. Previously I used C++/CLI, but I migrated it all to vanilla C# with P/Invoke. I have significant experience with the SQLite C API (which is why I don't bother with other people's bindings). With P/Invoke it was a pretty straightforward experience, and specifically the debugger worked great and was crucial to getting it working.

I really think that C# with P/Invoke and the Visual Studio debugger is a cut above the rest.


P/Invoke is similar to how many compiled languages used to bind to other compiled languages for many years, e.g. Modula-2, BASIC and Pascal dialects.

Somehow it became common to do it in more complex ways, and the return to AOT compilation is rediscovering it.

The way VS debugging allows for mixed language debugging is only comparable with Netbeans/Eclipse, but neither JNI nor Panama are as good as P/Invoke.


Oh, great! I assumed you meant the officially supported System.Data.SQLite bindings. Do you have a link or is this internal?


The meat of the P/Invoke code is in here: https://github.com/electroly/sqlnotebook/tree/master/src/Sql...

The parent directory includes code that uses it. I'm most proud of this SQLite virtual table module that proxies queries to remote ADO.NET connections, allowing you to write joins directly between local SQLite tables and remote SQL Server tables. https://github.com/electroly/sqlnotebook/blob/master/src/Sql...

This is exposed to the user as a "LINK" option on the "IMPORT DATABASE" statement: https://sqlnotebook.com/import-database-stmt.html

I've also got a generic virtual table module that lets me easily write table-valued functions in C#: https://github.com/electroly/sqlnotebook/blob/master/src/Sql...

I don't have too many table-valued functions yet but one example is the "LIST_XLS_WORKSHEETS" function: https://sqlnotebook.com/list-xls-worksheets-func.html

I have my own implementation of SQLite's grammar so I can embed it inside my larger grammar which adds things like DECLARE, IF, and WHILE: https://github.com/electroly/sqlnotebook/blob/master/src/Sql...

The goal is to provide various "supercharged" features to base SQLite by taking advantage of all the extension points I can. I wish some went further; in particular the virtual table API doesn't "push down" enough of the original query to allow the module to avoid doing N+1 queries in some cases.

Always happy to see people using SQLite in unique ways!


Oh I wanted to implement a virtual table to do the same thing in Go (access a remote DB, with where clause push down).

Will have a look at your code!


I would need to read more about that to understand how it could be a problem. You see, I've literally spend decades using C and I can never imagine how adding that what I understand to be huge dependencies like wazero.io and executing huge wasms binaries made from original C sources can be more debuggable than debugging a directly compiled native code -- I'm just disclosing my bias, and I want to be clear about it, I'm certainly not doubting your "better experience."

Edit: have you tried that zig workflow for cross-compiling?

Edit2: and I'm also aware that zig also uses wasm binary and its own wasm2c, written from scratch, for its own bootstrapping:

https://ziglang.org/news/goodbye-cpp/


I don't mean debugging the C bits (although having a stack trace and an exact line number when I cause SQLite to crash is rather nice).

I mean developing a complex wrapper/binding that involves Go calls C, which then calls back to Go, which again calls C, and putting breakpoints on the Go bits multiple layers deep, and being able to inspect the full stack of those Go bits.

Last I've tried with Cgo, stuff has a high probability of hanging your entire process (e.g. if the C library acquired some lock or something).

There's also the permanent fear that (bugs, API misuse…) leads to the C library corrupting your Go memory, which is much harder to do if the C bits run in a sandbox. SQLite is "imune" to bugs, but not API misuse.

Tbh, I haven't had a better experience with JNI, JNA, PInvoke…

And yes, I've looked at zig, even used it initially to build the WASM blob. I actually think it'd be a worthy endeavour to build a better Cgo driver. But having Cgo and WASM versions of this in the same repo is just too much effort at this point (I've considered it).


Nothing ever comes close to P/Invoke in ease of use* (while remaining practically zero-overhead if you don't need to marshal strings). JNI is on the opposite side of the spectrum and should never be even compared as it is at least ten times worse developer experiences (and equally higher overhead).

*in garbage-collected languages, otherwise it's pretty much like calling C from Rust.


I'm sorry. The debugging experience of trying to whip up PInvoke wrappers for the COM library for Kinect 2 that worked with Unity Engine's awful version of Mono was charring.

Also had “fun” building some Xamarin Android PInvoke-to-JNI bindings. I just died a little bit remembering this.


No language has good interop story with Android unless there's no interop and you are using Java/Kotlin.

As for the first argument - this really does not represent the average experience in the current day ecosystem. Kinect 2 was a thing 9 years ago and together with Unity it's pretty much an exotic scenario. Unity in general is really not a good example until they get Unity 6 out and move over to vanilla .NET.


To be clearer to those who don't read what's linked but reflexively downvote:

zig is not only the language, it's also a build system that compiles C (also in Go CGO use scenarios) better than the default C compilers, allowing order of magnitude easier cross compilation. It's described in the link I've already provided exactly by compiling SqLite for its use in Go!


You mentioned a compiler crash - I suppose that means the Go compiler? That's pretty interesting! Any more information about the crash? Did you file a bug report?


No, I meant in wazero, when using their AOT WASM-to-native compiler engine.

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.


That's annoying about wazero function not allowing reentrant calls. The docs do say it's not goroutine safe, which is easy to miss on its own, but nothing about this.


https://github.com/tetratelabs/wazero/pull/1853


Can someone explain what is the essential, practical difference between a library that implements package database/sql (such as mattn's) and one that does not (such as zombiezen or crawshaw) ?

Not using a standard interface (i.e. database/sql) sounds dodgy, but maybe there's really no problem.

Apologies if this is a stupid question.


The database/sql package serves as an intermediary abstraction layer (but still low-level) between the programmer's standardized API and the database. It's responsible for managing interactions with the database, such as communicating with a remote database server or accessing a local database like SQLite. Keep in mind that database/sql requires a compatible database driver (e.g., mattn/go-sqlite3) to facilitate this communication. In other programming environments, this concept is like ODBC or JDBC.

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 a great answer to the parent question, I'll just add that I (try) to provide both a database/sql driver, and a lower level direct mapping of the SQLite C API.

Pretty much the only thing the lower level wrapper does for you is simplify some of the memory management inherent to a C API (who allocates? who frees? for everything even something as trivial as an error message: GC+copying handles lightweight types like strings and errors, io.Closer is used for everything else).

At the same time, I also made a serious effort to make many SQLite features (like blob IO, nested transactions, JSON, time handling) easy to access for users of the database/sql driver.


Thank you for this explanation.

I've already decided Yes to SQLite (only) and No to ORMs, so maybe zombiezen is the way to go.


I guess my unexpressed actual question was whether the API is materially different for small-scale operation.


database/sql adds some overhead; in return, it gives you a connection pool, and tries not to have you worry about connections.

A lot of the interesting features of SQLite are connection oriented, and compared to other databases, connections are cheap (because it's all local). So the things database/sql offers are (relatively) less valuable, and it makes other things harder.

Yet (IMO) connection pools still offer some performance benefits, not having to worry about (re)preparing statements on new connections is still helpful, and many of the connection oriented things you might want to do can be exposed through database/sql with some imagination.

I have a lot of respect for crawshaw and zombiezen; I started from their PoV. But I've since come around to database/sql.

A lot of what is mentioned in [1] has been fixed in database/sql or can be fixed in drivers: parameter names now work in database/sql; their approach to statement caching isn't necessarily better, but can be added to a driver, or live in a compatible framework; panics vs. errors is contentious; savepoints can be made to work just fine [2]; and for contexts, I started with crawshaw and built an improved version of it into my driver, that is both low overhead, interacts well with the Go scheduler, and doesn't start additional goroutines.

Living in database/sql land gives you access to other amazing tooling like SQLC, just to name a single one. And it doesn't need to be either/or: drivers should support both high and low level APIs.

[1]: https://crawshaw.io/blog/go-and-sqlite

[2]: https://pkg.go.dev/github.com/ncruces/go-sqlite3/driver#exam...


I'm glad with community feedback and research you were able to bolster your driver!


Very cool! I’ve been looking at using SQLite-vss for fast vector sorting, but it requires CGO. I imagine if I do WASM compilation directly from C, I might be able to get around using CGO. Do you have any idea able the feasibility of that using your package? Thanks!


One problem with WASM is that, at this point (2023), the only easy way of building that, is to statically link everything C into one WASM blob (so you can't use my blob).

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.


How would you do it in Go? It is my understanding that FAISS is integrated into the SQL database itself (using embedding in the table), so writing it in Go wouldn't integrate it in the same way.

> But you can try to use my build scripts, and tweak my code, to build SQLite+sqlite-vss+Faiss into one large WASM

That makes perfect sense to me! I'm looking into your build scripts right now. I'm pretty proficient in Go, but haven't touched wasm or C much, so. I may reach out with more questions.


You need a virtual table (same as sqlite-vss) and a library that does cosine similarity indexing (Faiss here)… which I know little about.

My plan was to evaluate Go for cosine similarity indexing, and if I found something promising, port the virtual table.


The "[!IMPORTANT]" block in the README is really fancy. I had no idea you could do that.


There are more "Alert blocks" available https://docs.github.com/en/get-started/writing-on-github/get...

This is github specific, not part of commonmark AFAIK


They are becoming more and more widely used, such as in Obsidian with their "callouts": https://help.obsidian.md/Editing+and+formatting/Callouts


A bit of a tangent but for those who’d like to use SQLite for a backend, running it as a separate daemon could be an interesting choice, which would also remove Cgo from the build and maybe make things like separate background job processes easier to orchestrate. See [1], [2].

—-

1: https://github.com/tursodatabase/libsql/tree/main/libsql-ser...

2: https://news.ycombinator.com/item?id=38602175


Basically a throwaway comment that is somewhat off topic, but your current Github README is very "inside baseball." I think it would be worth a one-sentence introduction to what your library can do (allows an app to incorporate a database, and it also has compatibility with a very popular library called SQLite, etc), then to tout the benefits of not requiring CGO, etc.


I think when you create a website (write a document, write a README) it is important to imagine the top 3 reader/visitor personas. What do they want from the README (website)?

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


Agreed, although I think a brief one-line intro to what this thing does wouldn't really interrupt the flow for any of the above use-cases. Right now, the top of the README is a list of sub-libraries, listing the library first.

Personally, I would add ~2 sentences in the order I outlined, and then I would reorient the list (currently library-first) in a use-case first arrangement like you mentioned.

I mean, realistically my git repos are a mess, so who am I to talk? But this one looks promising enough such that it's worth offering a bit of feedback.


Thank you for the feedback! I tried to improve the copy a bit, but this is clearly not my thing…


Agree,

4. Someone following a link from HN

I'm often confused when clicking through from HN and can't figure out from the website or README what the project is doing.


Sometimes you want people who don’t get it to bounce.

Keeps the signal to noise ratio higher.


I'd like to read what the actual benefits of not linking to C sqlite implementation are. I can imagine using wazero.io for libraries, but I fail to understand _why_ would anybody prefer to run sqlite that way from their own go program, except if it's in cases where execution times are clearly not important (which explains to me the background story).


The major one is (cross) compilation and portability.

You can (e.g.) easily whip up a small CLI that embeds an SQLite database in the binary [1], cross compile this to all the platforms Go supports [2], copy one file, and be reasonably sure it'll run in any of them.

This is an example of embedding a database in a binary (with go:embed), and opening it for reading/writing in memory. You can then save the modified copy to disk using the backup API:

[1]: https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/memdb#e...

[2]: https://gist.github.com/asukakenji/f15ba7e588ac42795f421b48b...


I'm still missing some details to understand that story, as I don't believe that there is any platform that Go supports on which C sqlite would not run, more the opposite, the memory requirements of wazero.io way must be clearly higher than of the C sqlite library?

It seems to me it's not about would C sqlite run or not on that platform but that there is still some other convenience, and that's what I'd still like to learn, as I'm sure ncruces does solve some specific problem.


To compile C for a given platform, you need a C compiler for that platform. Which definitely exists but you may not have it at hand.

To cross compile from your platform A to that platform B, you need a cross compiler, which may be even harder to come by.

If you're working on a Go project, you already have a Go compiler that can do this (cross compilation) very easily, out of the box.

But if you add a C dependency, you also need a C cross compiler. SQLite is easy to configure, and very portable, but this still adds some friction.

Then if you want to setup CI, you need all this infra in CI. If you want to automate releases for a bunch of platforms, you need all of it in your release process.

I'm not saying it's a huge pain but it's enough that some projects avoid cgo as a matter of principle.


Many thanks for this good answer, that level of detail is exactly what I have been missing up to now.

I'm surely aware of the fact that C cross-compilation is often very clumsy implemented. But I also have to mention that there is a project that also tries to solve that too, while also creating a whole new language:

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


it's also worth noting that even on the same platform, compiling with CGO enabled creates a dynamic link against `glibc`, which creates a dependency on having the matching version in your deployment target.

We ran into this recently as our CI system is running a newer OS than we currently deploy to - and disabling CGO was an easy way to sidestep this requirement


Is this something inherent in CGO implementation? If your dependency happened because the compiled library depended on it, do you know that that is probably avoidable (I haven't tried your use case, so I could be wrong) by using zig as a compiler as it can "cross compile" for lower glibc version and also to musl, avoiding glibc altogether?


With zig I think you can avoid libc deps on Linux, at least, by statically linking musl instead.

Not sure SQLite is tested in this configuration, for the fans of “this didn't pass TH3 so we can't trust it at all.”


AFAIK: you also link to a different (lower!) glibc version from the one on which you do the compilation, probably solving the problem you had without having an excuse that you linked an "untested" library.


I’m not 100% sure why you would like to run SQLite in your go program this way (using WASM) but I think avoiding cgo in the go community is a common thing for all the reasons mentioned here [1] (or perhaps even more directly, because that article exists :-p).

—-

1: https://dave.cheney.net/2016/01/18/cgo-is-not-go


[flagged]


First part of the README that hasn't changed in 2 months:

> 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.




Consider applying for YC's Fall 2025 batch! Applications are open till Aug 4

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: