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

SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

micrologics.org

Post reply on HN
Print view View original

11–20 of 87 posts

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#11

Post by Hendrikto »

yladiz wrote: post #3

I'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…

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

No you don’t [0]. It is less convenient than being able to directly alter columns, but you do not need to mess around with writable schemas or risk corruption.

[0]: https://www.sqlite.org/lang_altertable.html#otheralter

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#12

Post by [deleted] »

michaellee8 wrote: post #5
yladiz wrote: post #3

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

[deleted]

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#13

Post by woodpanel »

graboid wrote: post #10

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

DBeaver can also open/manage SQLite databases. I use it daily (although on a tiny page).

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#14

Post by yladiz »

Hendrikto wrote: post #11
yladiz wrote: post #3

I'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…

> 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. No you don’t [0]. It is less convenient than being able to directly alter columns, but you do not need to mess around with writable schemas or risk corruption. [0]: https://www.sqlite.org/lang_altertable.html#otheralter

I’m a bit confused. That’s not a column definition change, because the original column is the same, you’re doing a data migration. That is one way you would solve this class of problems in SQLite, but it’s a bit annoying compared to a something like `alter column`.

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#15

Post by graboid »

woodpanel wrote: post #13
graboid wrote: post #10

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

DBeaver can also open/manage SQLite databases. I use it daily (although on a tiny page).

Ah, my point was more on how to connect to the live database on some remote server.

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#16

Post by yladiz »

yread wrote: post #9
yladiz wrote: post #3

I'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 SQ…

> Why is that the ideal? With SQLite your database is 1:1 connected to your application

I don't think this solves the issue though. To be fair, I was a bit loose with my wording and the principle is actually "don't make backwards breaking changes to your database schema" rather than "do your migrations separately", but if you do them separately it is a good way to enforce it. The issue you want to prevent is your application having bugs/issues in production necessitating a rollback, and your now rolled back application doing things that are incompatible with the current database version (or in a concurrent setting, that some applications may not be updated).

There's still the issue where you're copying over all of the migrations to your server too when you do it in the application, which is in my opinion something you are ideally able to avoid, but it's not a problem in practice until you have 1000s of migrations.

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#17

Post by stanac »

graboid wrote: post #10

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

If your app is a web app you could create a rudimental page with SQL input and table output and authorize only admins. It's risky, if someone gets access to admin account they can drop everything. I have something like this for logs, app reads log files directly and they are accessible only via private domain (tailscale), if I connect to public domain I can't access logs. Additionally you could enable only SELECT statements via web.

Another downside is that it will take time to make it look nice.

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#18

Post by yread »

yladiz wrote: post #16
yread wrote: post #9

Earlier quoted context omitted.

> 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 SQ…

> Why is that the ideal? With SQLite your database is 1:1 connected to your application I don't think this solves the issue though. To be fair, I was a bit loose with my wording and the principle is actually "don't make backwards breaking changes to your database schema" rather than "do your migrations separately", but if you do them separately it is a good way to enforce it. The issue you want to prevent is your app…

I don't see how having migrations out of the app enforces that.

For the rare case when you do rollback the safest thing to do is stop the app, downgrade the db (by running some sql if necessary) and app and rerun it. Not that different in postgres no?

Re: SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

#19

Post by stanac »

grebc wrote: post #7
yladiz wrote: post #3

I'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…

> Better option is to just add a new column with the correct definition

After that you won't be able to change column to NOT NULL. You would need migration to create new table with not null column, copy everything, drop old table and rename the new one.

Edit: unless the table is empty.

Post reply on HN