Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Benchmarks for Golang SQLite Drivers

github.com

Post reply on HN
Print view View original

21–30 of 36 posts

Re: Benchmarks for Golang SQLite Drivers

#21

Post by Sammi »

mrklol wrote: post #19
h4kor wrote: post #17

Earlier quoted context omitted.

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?

Afaik the fix for that is to have multiple read only connections and one write only connection.

Yes by enabling the write ahead log feature: 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.

Re: Benchmarks for Golang SQLite Drivers

#22

Post by sgt »

Sammi wrote: post #21
mrklol wrote: post #19

Earlier quoted context omitted.

Afaik the fix for that is to have multiple read only connections and one write only connection.

Yes by enabling the write ahead log feature: 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.

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.

Re: Benchmarks for Golang SQLite Drivers

#23

Post by slyzmud »

Kimitri wrote: post #3

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

Re: Benchmarks for Golang SQLite Drivers

#24

Post by wild_egg »

sgt wrote: post #22
Sammi wrote: post #21

Earlier quoted context omitted.

Yes by enabling the write ahead log feature: 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.

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.

> "dealing with WAL"

What's there to deal with? You turn it on with a pragma and forget about it.

Re: Benchmarks for Golang SQLite Drivers

#27

Post by kitd »

Twirrim wrote: post #5
maxmcd wrote: post #4

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.

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.

Re: Benchmarks for Golang SQLite Drivers

#29

Post by sgt »

wild_egg wrote: post #24
sgt wrote: post #22

Earlier quoted context omitted.

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.

> "dealing with WAL" What's there to deal with? You turn it on with a pragma and forget about it.

Sure but once you have WAL logs, you suddenly have a more heavy weight setup. Backing it up you'll want to back up those WAL logs to achieve proper point in time recovery, and so on. My point is, you're now bolting on extra stuff on it to do things that Postgres can do (which can be pretty light weight). Not disrespecting SQLite, still one of my favorite DB's.

Re: Benchmarks for Golang SQLite Drivers

#30

Post by wild_egg »

sgt wrote: post #29
wild_egg wrote: post #24

Earlier quoted context omitted.

> "dealing with WAL" What's there to deal with? You turn it on with a pragma and forget about it.

Sure but once you have WAL logs, you suddenly have a more heavy weight setup. Backing it up you'll want to back up those WAL logs to achieve proper point in time recovery, and so on. My point is, you're now bolting on extra stuff on it to do things that Postgres can do (which can be pretty light weight). Not disrespecting SQLite, still one of my favorite DB's.

What? Why are you backing up the WAL?

    sqlite3 source_database.db ".backup backup_database.db"
Now the WAL content is rolled into your new backup file. Stick a timestamp in the backup file name and run this as a cron job every N minutes and you have all the recovery you need. Another one-liner to sync to S3 and you're all set.

Edit: And just to clarify, that command can be run on a live DB as it's being used by your app server. SQLite handles external concurrent readers just fine.

Post reply on HN