11–20 of 36 posts
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…
Nit: "For benchmarks I used the following libraries: ". This is begging to be a table.
I see what you mean, there are some categories there (cGO based or not) that lend themselves to quick understanding via a table.
This library is wild https://github.com/cvilsmeier/sqinn Sqlite over stdin, to a subprocess, and it's fast!
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.
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.
This library is wild https://github.com/cvilsmeier/sqinn Sqlite over stdin, to a subprocess, and it's fast!
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.
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…
Wouldn’t duckdb be a good fit for this?
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.…
How do you overcome this with SQLite and Django?
Earlier quoted context omitted.
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.
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/…
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.…
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?
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.
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"