3 ms·

I have a question that wasn't answered on the blog, and might be silly for me to ask. Is SQLite running in WAL mode or not? I usually found that WAL mode usuall

I have a question that wasn't answered on the blog, and might be silly for me to ask. Is SQLite running in WAL mode or not? I usually found that WAL mode usually allows better performance even in read-heavy workloads as it doesn't need to exclusively lock the database each time. Otherwise, the post is a nice reminder for SQLite users that foresee a database switch to use the newly-introduced strict mode.

miguelgrinberg 4y ago
SQLite isn't running in WAL mode. This application isn't just read-heavy, it is ready-only for users, so I wouldn't think this will make a difference. The writes occur while everybody is sleeping, from a cron job that runs once a day to upload the traffic for the previous 24 hs.
dgb23 4y ago
Yes, WAL mode can have a drastic impact. Also this is apparently benchmarking Postgres and SQLite _with Python on top_. Some of the overhead here might not have much to do with either SQLite or Postgres at all. Just because one uses an ORM doesn't mean the same code is running and especially considering how sluggish Python is, I'm not convinced by any of this either way outside of this being an improvement for the very specific use-case presented. But we don't know _why_ that is.
- miguelgrinberg 4y ago
  I explain this in the post. The goal is to optimize the application for users. I really don't care if Postgres is faster than SQLite or viceversa, I just want users to have a snappier application. The experiment was to swap out the database while leaving everything else the same to see if that affects the overall user experience in a positive or negative way. Also note that I'm not saying Postgres is faster the SQLite in absolute terms. That happened to be the case for this particular application and how it was deployed. If you perform a similar test with a different app or stack you will likely get different results. A main point I'm trying to make in the article is that people should do their own benchmarking instead of making decisions on stuff you read online.

miguelgrinberg 4y ago

SQLite isn't running in WAL mode. This application isn't just read-heavy, it is ready-only for users, so I wouldn't think this will make a difference. The writes occur while everybody is sleeping, from a cron job that runs once a day to upload the traffic for the previous 24 hs.

dgb23 4y ago

Yes, WAL mode can have a drastic impact. Also this is apparently benchmarking Postgres and SQLite _with Python on top_. Some of the overhead here might not have much to do with either SQLite or Postgres at all. Just because one uses an ORM doesn't mean the same code is running and especially considering how sluggish Python is, I'm not convinced by any of this either way outside of this being an improvement for the very specific use-case presented. But we don't know _why_ that is.

miguelgrinberg 4y ago
I explain this in the post. The goal is to optimize the application for users. I really don't care if Postgres is faster than SQLite or viceversa, I just want users to have a snappier application. The experiment was to swap out the database while leaving everything else the same to see if that affects the overall user experience in a positive or negative way. Also note that I'm not saying Postgres is faster the SQLite in absolute terms. That happened to be the case for this particular application and how it was deployed. If you perform a similar test with a different app or stack you will likely get different results. A main point I'm trying to make in the article is that people should do their own benchmarking instead of making decisions on stuff you read online.