FWIW, this works for me with Python 3.12 from Homebrew, but not Python 3.12 from python.org. _sqlite3.cpython-312-darwin.so in Homebrew's Python appears to dynamically link /opt/homebrew/opt/sqlite/lib/libsqlite3.0.dylib, but the version in python.org's Python statically links the sqlite3 library.
EDIT: Python 3.9.6 from Xcode doesn't work either. It has _sqlite3.cpython-39-darwin.so which dynamically links /usr/lib/libsqlite3.dylib, but that dylib doesn't exist on my system, and I don't know enough about macOS internals to tell where it's coming from. The _sqlite3 so doesn't seem big enough to have it statically linked.
EDIT2: Xcode's Python works when launching via the real path instead of using the /usr/bin/python3 alias, I assume because /usr/bin is SIP-protected or something.
From the original forum post [0] announcing this improvement:
> But if you modify your application to start storing JSONB instead of text JSON, you might see a 3-times performance improvement, at least for the JSON-intensive operations. JSONB is also slightly smaller than text JSON in most cases (about 5% or 10% smaller) so you might also see a modest reduction in your database size if you use a lot of JSON.
I for one am excited about these improvements (specifically the disk use reduction) since we store a lot of JSON here at Notion Labs, and we’re increasing our use of SQLite.
We’ve used SQLite in our native apps (including desktop) for years, like you’d expect. We’re considering how we could use it in the browser in a few ways now that OPFS and the ecosystem there are stabilizing. We’re also looking at some use cases server side, but not one-db-per-tenant.
I don’t think SQLite’s single-writer model would mesh well with Notion’s collaborative features. I’m actually very curious if the one-db-per-tenant concept turns out to be a good idea or a fad. To me it seems like a small app can very happily fit all their users on a single Postgres instance with much less orchestration effort, and a large app demanding of its database would hit the single-write lock thing.
> Fix a couple of harmless compiler warnings that appeared in debug builds with GCC 16.
Some projects use -Werror, only ever test with older GCC, and builds fail with anything recent. SQLite on the other hand anticipates the new compiler warnings of GCC 3 major versions in the future, that's impressive!
Embarrasing question tbh but with all the cloud-native sqlite stuff like cloudflare d1 and fly LiteFS I'm seriously thinking of switching from postgres to sqlite.
Does anyone have a compare/contrast sort of thing between the two?
It is a lot less feature rich than Postgres so there are things you will miss. Nothing like the range of types, I do not think it has a transactional DDL which is nice to have for migrations, and there are various other things like exclusion constraints and the different index types.
On the other hand SQLite may do all you want and not having to run and configure a separate server is a huge deployment advantage.
For the love of all that is holy, if you do, only use STRICT tables. By default [0], SQLite will happily accept that not only can an INTEGER column store “1234” (helpfully silently casting it first), but “abcd” can also be stored in the column as-is.
I actually kinda like the fact that whatever data you write to the table will actually be written.
I semi-regularly fix a serious data loss bug that has been fixed with an alter table query. Maybe converting VARCHAR to TEXT or INT to BIGINT... of course it doesn't really "fix" your problem, because the data has already been lost/truncated.
What's a real world situation where completely the wrong type could be written to a column? Especially in modern software with good type safety checks/etc to ensure you don't have malicious data inserted into your database? If I ever did have that happen... at least the data hasn't been lost. You can run a simple script to clean up the "horrific" data.
For me, the biggest trade offs for sqlite are just that you need to think about how you're going to store and backup the database a lot more. Specifically in container orchestration environments like kubernetes, I think sqlite presents a couple of challenges. With MySQL, you can set up a replicated database server instance outside of the cluster that you just connect to over the network, and you can use standard MySQL tools like mysqldump to back them up. Kubernetes isn't ideal for stateful workloads so that tends to be one of the more sane solutions there.
With SQlite you need to set up a persistent volume to keep the database around between container restarts, and you need to think of a clever way to then back up that sqlite database to somewhere like S3, likely using an sqlite3 command with a VACUUM statement and then an `aws s3 cp` command, which requires AWS credentials. Overall, a lot of additional work and privileges on the application container, at least in container orchestration environments. In lieu of all that, maybe you trust your persistent volume provisioner enough to try to do an online snapshot, but that always sketches me out / I don't trust the backup enough to rely on it.
Of course you can use a public cloud sqlite service like Cloudflare D1, but I haven't used that solution enough to say if it would be flexible enough to work with, say, an on-prem application server, or if it only works with Cloudflare workers. I'm sure I could find that out in the documentation but I've exhausted my mental stamina for the day with leafing through documentation pages.
Backing up sqlite databases is straightforward. `.backup` is a command that you use in sqlite for this purpose. Since you already have a volume for the database, you can backup to that same volume (if offloading to S3/etc is too much work).
It sounds great until you need a centralized billing database and then you might want to just stick with postgres rather than run two kinds of database.
Does anyone have ideas on how to solve that? Not to mention complicating migrations. Unfortunately sqlite-based product docs seem to end right before getting to the hard stuff. Or perhaps I missed them.
I use SQLite/Litestream for https://extensionpay.com! Serves about 120m requests per month (most of those are cached and don't hit the db), but it's been great!
Using SQLite with Litestream helped me to launch the site quickly without having to pay for or configure/manage a db server, especially when I didn't know if the site would make any money and didn't have any personal experience with running production databases. Litestream streams to blackblaze b2 for literally $0 per month which is great. I already had a backblaze account for personal backups and it was easy to just add b2 storage. I've never had to restore from backup so far.
There's a pleasing operational simplicity in this setup — one $14 DigitalOcean droplet serves my entire app (single-threaded still!) and it's been easy to scale vertically by just upgrading the server to the next tier when I started pushing the limits of a droplet (or doing some obvious SQLite config optimizations). DigitalOcean's "premium" intel and amd droplets use NVMe drives which seem to be especially good with SQLite.
One downside of using SQLite is that there's just not as much community knowledge about using and tuning it for web applications. For example, I'm using it with SvelteKit and there's not much written online about deploying multi-threaded SvelteKit apps with SQLite. Also, not many example configs to learn from. By far the biggest performance improvement I found was turning on memory mapping for SQLite.
In my tests sqlite was around 10X faster than postgres.
That mean that a single cheap server is capable of going very very far in normal web workloads.
I think we often add complexity: virtualisation, cloud, separated db server, horizontal scaling when efficient and simple tech is able to go very very far.
Trying to store JSON-like data in a way that's both compact and fast to operate on directly is a challenge. IIUC this is is something SQLite has wanted to introduce for a while, but it took them some time to find a viable approach.
Readit News
Short version:
That prints "3.45.0" for me.If you have https://datasette.io/ installed you can then get a web UI for trying it out by running:
https://sqlite.org/fiddle
is always updated as part of the release process and is updated periodically between releases.
EDIT: Python 3.9.6 from Xcode doesn't work either. It has _sqlite3.cpython-39-darwin.so which dynamically links /usr/lib/libsqlite3.dylib, but that dylib doesn't exist on my system, and I don't know enough about macOS internals to tell where it's coming from. The _sqlite3 so doesn't seem big enough to have it statically linked.
EDIT2: Xcode's Python works when launching via the real path instead of using the /usr/bin/python3 alias, I assume because /usr/bin is SIP-protected or something.
> But if you modify your application to start storing JSONB instead of text JSON, you might see a 3-times performance improvement, at least for the JSON-intensive operations. JSONB is also slightly smaller than text JSON in most cases (about 5% or 10% smaller) so you might also see a modest reduction in your database size if you use a lot of JSON.
I for one am excited about these improvements (specifically the disk use reduction) since we store a lot of JSON here at Notion Labs, and we’re increasing our use of SQLite.
[0]: https://sqlite.org/forum/forumpost/fa6f64e3dc1a5d97
We’ve used SQLite in our native apps (including desktop) for years, like you’d expect. We’re considering how we could use it in the browser in a few ways now that OPFS and the ecosystem there are stabilizing. We’re also looking at some use cases server side, but not one-db-per-tenant.
I don’t think SQLite’s single-writer model would mesh well with Notion’s collaborative features. I’m actually very curious if the one-db-per-tenant concept turns out to be a good idea or a fad. To me it seems like a small app can very happily fit all their users on a single Postgres instance with much less orchestration effort, and a large app demanding of its database would hit the single-write lock thing.
If you want to know more, think about joining?? :) https://notion.so/careers or @jitl on Twitter
https://antonz.org/sqlite-3-45
Some projects use -Werror, only ever test with older GCC, and builds fail with anything recent. SQLite on the other hand anticipates the new compiler warnings of GCC 3 major versions in the future, that's impressive!
Does anyone have a compare/contrast sort of thing between the two?
https://www.sqlite.org/omitted.html
and the gotchas page it links to.
It is a lot less feature rich than Postgres so there are things you will miss. Nothing like the range of types, I do not think it has a transactional DDL which is nice to have for migrations, and there are various other things like exclusion constraints and the different index types.
On the other hand SQLite may do all you want and not having to run and configure a separate server is a huge deployment advantage.
There are other horrors in the link.
[0]: https://www.sqlite.org/quirks.html
I semi-regularly fix a serious data loss bug that has been fixed with an alter table query. Maybe converting VARCHAR to TEXT or INT to BIGINT... of course it doesn't really "fix" your problem, because the data has already been lost/truncated.
What's a real world situation where completely the wrong type could be written to a column? Especially in modern software with good type safety checks/etc to ensure you don't have malicious data inserted into your database? If I ever did have that happen... at least the data hasn't been lost. You can run a simple script to clean up the "horrific" data.
With SQlite you need to set up a persistent volume to keep the database around between container restarts, and you need to think of a clever way to then back up that sqlite database to somewhere like S3, likely using an sqlite3 command with a VACUUM statement and then an `aws s3 cp` command, which requires AWS credentials. Overall, a lot of additional work and privileges on the application container, at least in container orchestration environments. In lieu of all that, maybe you trust your persistent volume provisioner enough to try to do an online snapshot, but that always sketches me out / I don't trust the backup enough to rely on it.
Of course you can use a public cloud sqlite service like Cloudflare D1, but I haven't used that solution enough to say if it would be flexible enough to work with, say, an on-prem application server, or if it only works with Cloudflare workers. I'm sure I could find that out in the documentation but I've exhausted my mental stamina for the day with leafing through documentation pages.
Does anyone have ideas on how to solve that? Not to mention complicating migrations. Unfortunately sqlite-based product docs seem to end right before getting to the hard stuff. Or perhaps I missed them.
I use SQLite/Litestream for https://extensionpay.com! Serves about 120m requests per month (most of those are cached and don't hit the db), but it's been great!
I was convinced that SQLite could be a viable db option from this great post about it called Consider SQLite: https://blog.wesleyac.com/posts/consider-sqlite
Using SQLite with Litestream helped me to launch the site quickly without having to pay for or configure/manage a db server, especially when I didn't know if the site would make any money and didn't have any personal experience with running production databases. Litestream streams to blackblaze b2 for literally $0 per month which is great. I already had a backblaze account for personal backups and it was easy to just add b2 storage. I've never had to restore from backup so far.
There's a pleasing operational simplicity in this setup — one $14 DigitalOcean droplet serves my entire app (single-threaded still!) and it's been easy to scale vertically by just upgrading the server to the next tier when I started pushing the limits of a droplet (or doing some obvious SQLite config optimizations). DigitalOcean's "premium" intel and amd droplets use NVMe drives which seem to be especially good with SQLite.
One downside of using SQLite is that there's just not as much community knowledge about using and tuning it for web applications. For example, I'm using it with SvelteKit and there's not much written online about deploying multi-threaded SvelteKit apps with SQLite. Also, not many example configs to learn from. By far the biggest performance improvement I found was turning on memory mapping for SQLite.
Happy to answer any questions you might have!
That mean that a single cheap server is capable of going very very far in normal web workloads.
I think we often add complexity: virtualisation, cloud, separated db server, horizontal scaling when efficient and simple tech is able to go very very far.
JSONB has landed - https://news.ycombinator.com/item?id=38540421 - Dec 2023 (205 comments)