6 ms·

Benchmarks for Golang SQLite Drivers

92 points by cvilsmeier 1y ago

_hyn3 1y ago
Excellent evaluation. From reading the code, it appears that the units for the numbers column is usually milliseconds (ms) It also looks like squinn is the clear leader for most but not all of the benchmarks. Even though it's "not scientific", is still very useful as a baseline - thanks for taking this effort and publishing your results! Also taking a look at monibot.io , looks cool
- cvilsmeier 1y ago
  Thank you.
Kimitri 1y ago
This is interesting and very timely for me. Just this week I was building a small Go system that uses SQLite. I needed to cross-compile it for FreeBSD on a Mac and ran into issues with CGO. The easiest fix seemed to be to switch from a CGO based library to a pure Go one.
- algo_lover 1y ago
  I hit the same issue, building on mac to deploy to linux. I added this build step before `scp`ing the binary to the server docker run --rm --platform=linux/amd64 \ -v "$PWD":/app -w /app \ golang:1.22-bullseye \ /bin/bash -c "apt update && apt install -y gcc sqlite3 libsqlite3-dev && \ CGO_ENABLED=1 GOOS=linux GOARCH=amd64 go build -o app-linux-amd64 ./cmd/main.go" Looking at the article, I should give modernc sqlite driver a try.
- slyzmud 1y ago
  I don't know for freebsd but at least for Linux I started using the zig toolchain and it's wonderful. https://zig.news/kristoff/building-sqlite-with-cgo-for-every-os-4cic https://zig.news/kristoff/building-sqlite-with-cgo-for-every...
maxmcd 1y ago
This library is wild https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn Sqlite over stdin, to a subprocess, and it's fast!
- Twirrim 1y ago
  It's wild to me that stdin/stdout is apparently significantly faster than using the API in so many cases. That's the kind of result that makes me wonder if there is something odd with the benchmarking.
  - kreelman 1y ago
    That's an interesting thought. I wonder. I wonder if the following things make the C driven version slower... - prepare the send buffers (sqlite side) - prepare the receive buffers (go side) - do the call - get the received data into go buffers of some kind - free up the send buffers (happens automatically) - free up the receive buffers (semi automatically in Go). When using stdin/stdout, the system looks after send/receive buffers. It's simply reading/writing them. No allocation is needed. The stream can be as big or as little as wanted/needed. The OS will look after the integrity of the streams and these are probably fairly well tested subsystems on most operating systems. stdin/stdout becomes a "library" for "fast data transfer". Pretty neat.
    - raggi 1y ago
      fwiw, the tailscale fork of Crawshaw’s library has a good number of allocation removals and other optimizations, but cgo is still expensive.
  - kitd 1y ago
    And presumably that implies there's OS context switching going on underneath. Still, I can see a few downsides. Though sqinn-go is pure Go, the forked process is pure C, so you'll need to either download a prebuilt one (Linux and Windows only atm), or build it yourself. This rather defeats the benefits of Go's killer feature of "single-binary distribution". Still, I agree it's wild it is so fast.
jitl 1y ago
For a project a while back, I needed to turn many-gigabyte Postgres CSV table dumps into SQLite databases. I turned to Go as its a great language for easy parallelism combined with enough memory layout control to get relatively good performance. I quickly ruled out using database/sql drivers as the indirection through interface types added a bunch of overhead and stymied my attempts for reasonable memory layout. For my use-case, I found the crawshaw driver performed the best, but I ended up forking it as well as the Golang standard library CSV parser as I found defensive copying & allocation was the largest bottleneck. I ended up cycling several very large arenas among a CSV parser thread that filled the arena with column bytes and several threads writing to different temporary sqlite databases. Then at the end I ATTACHED them together and copied them into one big file (idk exactly why this is faster, but my profiles showed most cpu time spent in sqlite doing query binding things so MOAR CORES). One notable optimization was exposing a way to bind borrowed bytes to query parameters without inducing a copy in either Golang caller code, or SQLite library code. The crawshaw driver upstream only exposes sqlite_bind_blob with SQLITE_TRANSIENT mode, which tells SQLite to copy the input to a private allocation before returning from the sqlite_bind* call. I added a version that passes SQLITE_STATIC, which means "trust me, I won't touch these bytes until the query is done, and I'll free them afterwards". This is safe in Rust who's "borrow" and "lifetime" concept models this perfectly, but I guess in Golang its dicey enough to not expose in your public package. Here's the relevant commit in my fork: https://github.com/crawshaw/sqlite/commit/82ad4f03528e8fdc6ae133be4bcd50469323d2c7 https://github.com/crawshaw/sqlite/commit/82ad4f03528e8fdc6a... I'm curious how OP's https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn would fare, I'm somewhat sus about copying 200GB to stdin but the benchmark results are pretty good so ¯\_(ツ)_/¯
- jitl 1y ago
  There may be an opportunity to switch from TRANSIENT to STATIC in sqinn but I didn't read deeply enough to follow what the current memory approach is. https://github.com/cvilsmeier/sqinn/blob/a88b3df6c89f0531e39485c33e6b5f3d0906eb10/lib/db.c#L116-L126 https://github.com/cvilsmeier/sqinn/blob/a88b3df6c89f0531e39...
  - cvilsmeier 1y ago
Mawr 1y ago
Nit: "For benchmarks I used the following libraries: <snip>". This is begging to be a table.
- kreelman 1y ago
  You could do the edit for him and create a pull request. I did that for a really small mistake in the README.md of sqinn. I see what you mean, there are some categories there (cGO based or not) that lend themselves to quick understanding via a table.
- dardeaup 1y ago
  I had the same thought. A table indeed!
- cvilsmeier 1y ago
  You're right. A table. I've included one.
evilmonkey19 1y ago
Personally I use SQLite in production environments and I don't regret it at all. I don't use Go (I develop in Python - Django mainly) and it has been the best decision ever: no management overhead, easy to backup, no need for difficult environments, etc. I feel like SQLite is undervalued. I do agree that in particular cases might not be the best, but more often than not I see that SQLite is more than enough database. Using Postgres or MySQL for the sake of being "production grade" is never a good idea. SQLite is also production grade. Watching at the statistics (look at sqinn) I would state that 90% of the internet could use SQLite without any issue and only benefits.
- h4kor 1y ago
  The main reason I use postgres instead of SQLite is that I have multiple processes accessing the database, often 1 web service for API/Website and a worker running in the background doing heavy tasks (e.g. image processing). Both need access to the database and SQLite will run into locking issues. How do you overcome this with SQLite and Django?
  - mrklol 1y ago
    Afaik the fix for that is to have multiple read only connections and one write only connection.
    - Sammi 1y ago
      Yes by enabling the write ahead log feature: https://sqlite.org/wal.html https://sqlite.org/wal.html It's on by default in many sqlite drivers because it really is the best default. But it isn't on by default in upstream sqlite even though it's been out for ages now.
      - sgt 1y ago
        Sure but if you're dealing with WAL logs, why not just go Postgres? Then you also get a port you can connect to from remote machines if you need.
vorgol 1y ago
I apologize for the side question, but what are people using for Golang SQlite cipher/encryption combination?
bbkane 1y ago
I've been using the modernc driver for a few years in https://github.com/bbkane/enventory https://github.com/bbkane/enventory . It's worked perfectly with no drama. Combined with https://sqlc.dev/ https://sqlc.dev/, I've been very happy writing (small) database applications in Go.

92 points by cvilsmeier 1y ago

Excellent evaluation. From reading the code, it appears that the units for the numbers column is usually milliseconds (ms) It also looks like squinn is the clear leader for most but not all of the benchmarks. Even though it's "not scientific", is still very useful as a baseline - thanks for taking this effort and publishing your results! Also taking a look at monibot.io , looks cool

cvilsmeier 1y ago
Thank you.

Kimitri 1y ago

This is interesting and very timely for me. Just this week I was building a small Go system that uses SQLite. I needed to cross-compile it for FreeBSD on a Mac and ran into issues with CGO. The easiest fix seemed to be to switch from a CGO based library to a pure Go one.

algo_lover 1y ago
I hit the same issue, building on mac to deploy to linux. I added this build step before `scp`ing the binary to the server docker run --rm --platform=linux/amd64 \ -v "$PWD":/app -w /app \ golang:1.22-bullseye \ /bin/bash -c "apt update && apt install -y gcc sqlite3 libsqlite3-dev && \ CGO_ENABLED=1 GOOS=linux GOARCH=amd64 go build -o app-linux-amd64 ./cmd/main.go" Looking at the article, I should give modernc sqlite driver a try.
slyzmud 1y ago
I don't know for freebsd but at least for Linux I started using the zig toolchain and it's wonderful. https://zig.news/kristoff/building-sqlite-with-cgo-for-every-os-4cic https://zig.news/kristoff/building-sqlite-with-cgo-for-every...

maxmcd 1y ago

This library is wild https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn Sqlite over stdin, to a subprocess, and it's fast!

Twirrim 1y ago
It's wild to me that stdin/stdout is apparently significantly faster than using the API in so many cases. That's the kind of result that makes me wonder if there is something odd with the benchmarking.
- kreelman 1y ago
  That's an interesting thought. I wonder. I wonder if the following things make the C driven version slower... - prepare the send buffers (sqlite side) - prepare the receive buffers (go side) - do the call - get the received data into go buffers of some kind - free up the send buffers (happens automatically) - free up the receive buffers (semi automatically in Go). When using stdin/stdout, the system looks after send/receive buffers. It's simply reading/writing them. No allocation is needed. The stream can be as big or as little as wanted/needed. The OS will look after the integrity of the streams and these are probably fairly well tested subsystems on most operating systems. stdin/stdout becomes a "library" for "fast data transfer". Pretty neat.
  - raggi 1y ago
    fwiw, the tailscale fork of Crawshaw’s library has a good number of allocation removals and other optimizations, but cgo is still expensive.
- kitd 1y ago
  And presumably that implies there's OS context switching going on underneath. Still, I can see a few downsides. Though sqinn-go is pure Go, the forked process is pure C, so you'll need to either download a prebuilt one (Linux and Windows only atm), or build it yourself. This rather defeats the benefits of Go's killer feature of "single-binary distribution". Still, I agree it's wild it is so fast.

jitl 1y ago

For a project a while back, I needed to turn many-gigabyte Postgres CSV table dumps into SQLite databases. I turned to Go as its a great language for easy parallelism combined with enough memory layout control to get relatively good performance. I quickly ruled out using database/sql drivers as the indirection through interface types added a bunch of overhead and stymied my attempts for reasonable memory layout. For my use-case, I found the crawshaw driver performed the best, but I ended up forking it as well as the Golang standard library CSV parser as I found defensive copying & allocation was the largest bottleneck. I ended up cycling several very large arenas among a CSV parser thread that filled the arena with column bytes and several threads writing to different temporary sqlite databases. Then at the end I ATTACHED them together and copied them into one big file (idk exactly why this is faster, but my profiles showed most cpu time spent in sqlite doing query binding things so MOAR CORES). One notable optimization was exposing a way to bind borrowed bytes to query parameters without inducing a copy in either Golang caller code, or SQLite library code. The crawshaw driver upstream only exposes sqlite_bind_blob with SQLITE_TRANSIENT mode, which tells SQLite to copy the input to a private allocation before returning from the sqlite_bind* call. I added a version that passes SQLITE_STATIC, which means "trust me, I won't touch these bytes until the query is done, and I'll free them afterwards". This is safe in Rust who's "borrow" and "lifetime" concept models this perfectly, but I guess in Golang its dicey enough to not expose in your public package. Here's the relevant commit in my fork: https://github.com/crawshaw/sqlite/commit/82ad4f03528e8fdc6ae133be4bcd50469323d2c7 https://github.com/crawshaw/sqlite/commit/82ad4f03528e8fdc6a... I'm curious how OP's https://github.com/cvilsmeier/sqinn https://github.com/cvilsmeier/sqinn would fare, I'm somewhat sus about copying 200GB to stdin but the benchmark results are pretty good so ¯\_(ツ)_/¯

jitl 1y ago
There may be an opportunity to switch from TRANSIENT to STATIC in sqinn but I didn't read deeply enough to follow what the current memory approach is. https://github.com/cvilsmeier/sqinn/blob/a88b3df6c89f0531e39485c33e6b5f3d0906eb10/lib/db.c#L116-L126 https://github.com/cvilsmeier/sqinn/blob/a88b3df6c89f0531e39...
- cvilsmeier 1y ago

Mawr 1y ago

Nit: "For benchmarks I used the following libraries: <snip>". This is begging to be a table.

kreelman 1y ago
You could do the edit for him and create a pull request. I did that for a really small mistake in the README.md of sqinn. I see what you mean, there are some categories there (cGO based or not) that lend themselves to quick understanding via a table.
dardeaup 1y ago
I had the same thought. A table indeed!
cvilsmeier 1y ago
You're right. A table. I've included one.

evilmonkey19 1y ago

Personally I use SQLite in production environments and I don't regret it at all. I don't use Go (I develop in Python - Django mainly) and it has been the best decision ever: no management overhead, easy to backup, no need for difficult environments, etc. I feel like SQLite is undervalued. I do agree that in particular cases might not be the best, but more often than not I see that SQLite is more than enough database. Using Postgres or MySQL for the sake of being "production grade" is never a good idea. SQLite is also production grade. Watching at the statistics (look at sqinn) I would state that 90% of the internet could use SQLite without any issue and only benefits.

h4kor 1y ago
The main reason I use postgres instead of SQLite is that I have multiple processes accessing the database, often 1 web service for API/Website and a worker running in the background doing heavy tasks (e.g. image processing). Both need access to the database and SQLite will run into locking issues. How do you overcome this with SQLite and Django?
- mrklol 1y ago
  Afaik the fix for that is to have multiple read only connections and one write only connection.
  - Sammi 1y ago
    Yes by enabling the write ahead log feature: https://sqlite.org/wal.html https://sqlite.org/wal.html It's on by default in many sqlite drivers because it really is the best default. But it isn't on by default in upstream sqlite even though it's been out for ages now.
    - sgt 1y ago
      Sure but if you're dealing with WAL logs, why not just go Postgres? Then you also get a port you can connect to from remote machines if you need.

vorgol 1y ago

I apologize for the side question, but what are people using for Golang SQlite cipher/encryption combination?

bbkane 1y ago

I've been using the modernc driver for a few years in https://github.com/bbkane/enventory https://github.com/bbkane/enventory . It's worked perfectly with no drama. Combined with https://sqlc.dev/ https://sqlc.dev/, I've been very happy writing (small) database applications in Go.