14 ms·
Go and SQLite in the Cloud
- simscitizen 4y agoIt doesn't strike me as the best language for embedding SQLite given the need to constantly cross the Cgo boundary. But I'm sure it still works fine.
- iansinnott 4y agoAlthough not mentioned in the article, there is a CGo-free port of SQLite which can be used as an alternative to the usual driver: https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite
- Cwizard 4y agoNot relevant if you are interested in performance. This go only version is much slower than the cgo overhead. (At least it was a year ago, do your own benchmarks)
- Loic 4y agoIt works very well. We are running a search engine for chemical properties in Go+SQLite and the speed is simply incredible to the point people think it is a static website. Here is a page with quite some queries to render: https://www.chemeo.com/cid/58-801-8/Pentane https://www.chemeo.com/cid/58-801-8/Pentane
- markusw 4y agoHaha, I’ve heard that comment before as well. If it’s _too_ fast, it feels suspicious, like it’s not doing anything. I think we’ve collectively forgotten how fast dynamic websites can be on modern hardware!
- mappu 4y ago> A benchmark performed on go 1.15 showed 60ns of overhead for calls into C This concern is a bit overblown, the Cgo boundary is heavier than an ordinary function call but still a zillion times faster than doing disk I/O on the database.
- rtukpe 4y agoThis is nice, I've used Litestream for a personal project. I wonder how it compares to something like rqlite [1] with larger datasets [1] https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
- otoolep 4y agorqlite author here, happy to answer any questions. The rqlite FAQ[1] might be useful to you. [1] https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md
- fyresala 4y agoYou won't gain much by the combination of golang, sqlite and the cloud. You can't scale out and the cloud layer only makes sqlite slower. I can't see any reason not using RDS other than it's more expensive. I would only say this is a quick way to run up an application with a cheap VPS for a beginner.
- benbjohnson 4y agoLitestream/LiteFS author here. I agree that "cloud" is a bit ambiguous but Go & SQLite are quite powerful together and I don't think it's only for beginners. Both are fast and have low overhead. In addition to lower cost versus RDS, there's near-zero query latency which eliminates a lot of performance problems. You can comfortably run tens or hundreds of requests per second on minimal hardware (e.g. 256MB or 512MB instances). There's a lot of room for scaling up before you hit a performance ceiling.
- ehutch79 4y agoTens of requests a second?
- randomdata 4y agoHe's being generous. Indeed, your service will be lucky if it sees tens of requests per day.
- ehutch79 4y ago:-| Normally I'm on the side of 'do you actually get that much traffic?' but yeah, a dashboard view can generate dozens of requests alone, each with multiple queries.
- benbjohnson 4y agoA request can have a large range of queries within it so I was trying to account for that. If your requests are lightweight and mostly reads, you can do 1,000+ req/sec on a 256MB instance. YMMV.
- markusw 4y agoHey! I'm the author of the article. Saw a sudden influx of traffic from HN and found the post here. Happy to answer any questions. Although SQLite is so dead-simple-but-awesome that you probably don't have any. :D Anyway, hope you enjoy it and learn a thing or two. Markus
- longcommonname 4y agoWould you provide a summary of how you noticed the increased traffic and how you found this post?
- markusw 4y agoSaw the request spike, searched HN for "sqlite".
- yandrypozo 4y agoNice article, what is your opinion on pocketbase.io which match your title really well, it would be great a follow-up article for LiteFS and pocketbase :)
- markusw 4y agoI don’t know pocketbase, will have a look.
- nileshtrivedi 4y agoI'd like to avoid writing boilerplate CRUD code. Is there an equivalent of PostgREST but for SQLite? Essentially, a standard binary would read the schema metadata, and generate standard CRUD APIs: https://postgrest.org/en/stable/api.html https://postgrest.org/en/stable/api.html The APIs can even support authentication and authorization with the help of JWT tokens. SQLite may not have row-level security, but even a convention (eg: if a row has user_id column, the JWT must have the same user_id value to get access to a row) would go a long way.
- 0cf8612b2e1e 4y ago
- hackerbrother 4y agoFYI-- it appears your Mastodon link needs "op" changed to "io"!
- markusw 4y agoOoops, thanks! Deploy on the way with a fix. :D
- cube2222 4y agoIt's also a really nice combo for simple automation lambdas that need some state and you want an ergonomic DB without paying for full RDS. Go + Lambda + EFS + SQLite work great for that.
- davidjfelix 4y agoDo you limit concurrency to 1 with this setup?
- cube2222 4y agoYou can, though then you get errors when invoking the Lambda too much at the same time. Just letting it block on SQLite db opening should be good enough.
- davidjfelix 4y agothere aren't any issues with having multiple lambdas opening the same sqlite file?
- markusw 4y agoSQLite is designed for concurrent access to the same db from multiple processes. All relevant locking happens in the DB file itself.
- davidjfelix 4y agoMy concern is mostly around EFS consistency. SQLite concurrency locking is only as good as EFS file consistency. I wasn't sure if you had to work around any oddities of NFS. https://twitter.com/benbjohnson/status/1360592483969810443?lang=en https://twitter.com/benbjohnson/status/1360592483969810443?l...
- lormayna 4y agoIt's not relational, but MongoDB has a generous free tier. Or maybe you can use Aurora Serverless, it's in the free tier as well (but it's designed for different use cases).
- sakopov 4y agoI've become really interested in SQLite when I heard about Litestream. What kind of deployment strategies do folks use to deploy SQLite and Litestream for production use? Also, AFAIK writes can only be done via master instance, so how do you control read/writes from multiple app instances?
- markusw 4y agoHave a look at the followup article that touches on distributed SQLite with LiteFS. :)
- 0cf8612b2e1e 4y agoOnly had a chance to skim the LiteFS article, but one use case closer to my heart would be blue-green deployments against the same SQLite database for zero-downtime deployments. Conceptually, I think I could use LiteFS to the same effect as distributed, but would love to see a write-up or any gotchas likely to occur.
- markusw 4y agoI don’t think there’s too much difference to “regular” distributed SQLite, but I’ll have to think about that.
- siliconc0w 4y agoStill not convinced sqlite is a good default, you can also run mysql/postgres locally which allows you similarly eliminate network hops and gives you the option to separate out things down the road. I'm not sure how Sqlite handles concurrent readers/writers these days but you'll probably at least end up scaling concurrency even in the early stages for things like async tasks.
- markusw 4y agoIt’s not just extra hops: there’s no network! (In distributed SQLite for reads at least.) Great for read-heavy workloads, and no n+1 query problems. Concurrent readers is no problem. No concurrent writes right now.
- leg100 4y agoHow does one perform deployments with go+sqlite? With a client-server database such as postgres your app and database are on separate servers, and you can perform a blue-green, canary, etc, deployment of the app, spinning up new servers running the new version alongside the servers running the old version, before shutting down the servers running the old version. But with sqlite you'd have to perform a hot-upgrade, surely? i.e. shut down the old version and quickly fire up the new version, with a small window of downtime in between. Note: I see Litestream/LiteFS allows distributed deployment (both in beta).
- markusw 4y agoEssentially yes, with a single SQLite instance, you have a small window of downtime on every deploy. But depending on your setup, that window could be very small, and unnoticeable if you have something in front that just delays incoming requests (like fly.io does with a load balancer in front of that single instance). And yes, LiteFS just selects a new leader and does a rolling deploy (or whatever else you want).
- ngrilly 4y agoAnother, more hacky option, would be to teach the binary executable how to download a new version, and start it to replace itself, while staying in the same container. Nginx does this for example. Of course, that’s some additional complexity, but then it is possible to have zero downtime upgrades (by basically upgrading the executable in the container but not the container itself). It is also possible, and simpler to upgrade the container, if it’s possible for different containers to share the same volume, but I don’t think this is possible on Fly.io.
- LVB 4y agoThis is a good article that neatly covers a lot of the tips I've seen spread across various posts, talks, etc. Thanks! I'm curious what experience you or others have dealing specifically with the write concurrency elements of this setup, should you find it is actually an issue. I've occasionally seen mention of restructuring things to queue writes within the app (e.g. having a single writer goroutine fed with a channel), but I be interested in more details about when folks hit the point of needing to do that, and what they did (and did it help?)
- simonw 4y agoIf you turn on WAL mode SQLite will queue the writes for you, and since most writes complete in under 1ms you'll likely find that this isn't really worth worrying about at all. I did some trivial benchmarking around this recently: https://simonwillison.net/2022/Oct/23/datasette-gunicorn/ https://simonwillison.net/2022/Oct/23/datasette-gunicorn/
- dinosaurdynasty 4y agoWAL mode does have some slight decrease in durability by default. If you pull the power immediately after a commit the commit may be reverted when you come back up. But yeah most of the time it isn't an issue.
- markusw 4y agoThis is news to me. What’s your source for that? AFAIK, SQLite is fully and completely ACID also in WAL mode.
- dinosaurdynasty 4y agohttps://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in journal_mode=DELETE on an older filesystem. WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode.
- mlangenberg 4y agoI feel that I am spoiled with a great graphical user interface being available to explore MySQL databases in the form of Sequel Ace for macOS, to the point that I sometimes load an sqlite database in MySQL just to be able to browse through it with Sequel Ace. Any recommendations for a macOS GUI for sqlite that comes close to Sequel Ace? I have tried DB Browser for SQLite, but that feels a bit outdated to be honest.
- SergeAx 4y agoI was a big fan of SQL as a language since meeting it in MS Access 95, but today I strongly believe that it became obsolete and too clumsy. I am now in favor of something more strict and structured, like MongoDB fully JSON-powered queries. Unfortunately, Mongo currently is heading directly into corporate hell, and with their uncanny license there's no way FOSS community will touch anything related with 6 foot pole in a nearby future.