SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
1–10 of 87 posts
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#2[dead]
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#3I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and I think it's great that it's getting hyped because it truly is a capable database, but after trying myself I think I'd never reach for it in production because it lacks a lot of power that a database like Postgres has, and some of that power is actually relevant to a real production setting:
- Column definitions aren't able to be changed with something like `alter column` after creation. To change a column definition you have to manually update the underlying schema using the `writable_schema` pragma. If you mess this up you can be left with a corrupt database.
- Column types are pretty limited. This isn't too much of an issue in practice since you can handle this somewhat in application code, but it can still be a bit annoying at times.
- You have limited options for dealing with schema migrations. You basically either copy the migrations to the server and run it there (manually or with something like Ansible), or you run the migrations in your application on startup. Ideally you'd perform your schema migrations separately from your application, and having to somehow copy/get the migrations to your server to then run the migration is a bit clunky.
All 3 of these are handled in a more powerful (and not local-only) database, and so I don't get why someone would choose SQLite except for prototyping (or places like the browser or phone apps) where performance concerns aren't really relevant.
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#4Personally, I think the better way to tackle sqlite_busy is to have a single writer managed at the application level. That effectively eliminates sqlite_busy in the context of a single process.
> To ensure write operations don't suffer from disk synchronization bottlenecks, pair WAL mode with the following pragma
Only do this if you are prepared to sacrifice durability (i.e can afford to lose transactions).
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#5I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and…
I previously had a golang based crawler doing 5 concurrent process writing into the same sqlite wal, it caused the sqlite to get corrupted, and i finally decided to move to postgres instead.
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#6I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and…
I would only recommend sqlite if you know what you are doing (and/or prepared to learn it inside out). It's more of a build your own database primitive (often you'll have multiple sqlite databases for different things). Which can be incredibly rewarding and deliver amazing performance outcomes, simple ops, etc.
I see the migration argument come up a lot. But, in practice with sqlite you'll be using projections where you have a source of truth database (event log) and project off it into disposable/expendable sqlite databases. So schema changes are often just delete and rebuild the projection.
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#7I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and…
I’ve never once had to change a column definition. Sure in theory that option is available. Better option is to just add a new column with the correct definition then copy over existing data in the old column.
I don’t think that’s really a positive or negative.
And the point about migrations ideally being separate is really just your own opinion. I prefer having the database definition in the same source tree as the application, ideally just a .sql file in the project.
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#8I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and…
I also treat articles about production optimisation with a bit of caution when they don't include any numbers to back up the claims.
If you're saying "do this, get that", you should be able explain how to measure and reproduce that result.
The answer to why someone might choose SQLite in production could be latency but if it is then prove it's worth the trade-offs.
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#9I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and…
> Ideally you'd perform your schema migrations separately from your application
Why is that the ideal? With SQLite your database is 1:1 connected to your application (meaning there is no other application using that database), it doesn't make sense to move the app to a new version but not the database or vice versa. Running migrations on startup of the app is ideal.
Migrations are a bit more difficult to write for SQLite than they need to be (DROP column only being added recently...), though. I usually iterate a few times to get the column definitions just right so that I don't have to change them later.
As you say column types are limited (and enforcement lax) but in practice it's a non-issue because you convert the data to application-specific types when reading from db (and enforce by writing only right data types) anyway.
Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
#10As someone really tempted to use SQLite in production, the one thing I keep bumping against is how to have a nice GUI to interact with the running database. With our current prod databases, I can connect dbeaver and the like to them and nicely browse the data, query, or even do the occasional fix. Seems like this would be much more of a head scratcher if the database is just a file on the same VPS the app runs on.