3 ms·

I think it would be really useful to have a “Why Not?” section for each option as well. Some of these feel like tradeoffs and setting them blindly without unde

by ratorx 2y ago

I think it would be really useful to have a “Why Not?” section for each option as well. Some of these feel like tradeoffs and setting them blindly without understanding the downsides seems incorrect.

TheRealPomax 2y ago
That's the essence of defaults: they're just better defaults, not an excuse to stop caring about what better settings to use once you have runtime performance metrics to guide whether they need changing and which direction to change them in.
- ratorx 2y ago
  Right, but they are not the official defaults. If you are changing the official defaults, then you might as well do it in a more principled way. If you are going to need to optimise with performance metrics anyway, then why not stick to just the official defaults (unless the official defaults are non-functional, is that the case?) I think anyone who is reading a blog post on “better defaults” is front loading some of the optimisation, so you could let them make a principled choice straight away for marginal extra cost.
  - nemothekid 2y ago
    A lot of these aren’t defaults because of backwards compatibility. IMO there is no reason to not use WAL mode, but it’s not default because it came later
    - bruce511 2y ago
      As long as all the processes are on the same machine the wal mode is a good idea. It's not good in the case where multiple machines are sharing the same database. Like say if you had a shared settings file which allowed multiple VMs to be set in one place. Obviously the same-machine situation is the most common. But you asked for a reason for when wal is not appropriate.
    - prirun 2y ago
      There is a list of 6 disadvantages of WAL mode on the SQLite site: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
  - TheRealPomax 2y ago
    There is some principle here: these are more sensible defaults (but still only defaults) for a very different purpose when compared to the defaults that make sense for "generic SQLite use" that has to keep those set to something that works across all versions. Having different, domain-specific defaults based on an assumption of "you're setting up a new project using the current version of SQLite" makes a whole lot of sense.
  - antisthenes 2y ago
    > If you are going to need to optimise with performance metrics anyway, then why not stick to just the official defaults (unless the official defaults are non-functional, is that the case?) Well, why don't you do the research and tell us? In my 10-15 years of dealing with official defaults of many programs is that they do work, but in 90% of cases they are overly-conservative.
btilly 2y ago
The note of several of them as being defaults for a web app kind of sets expectations for me. These are defaults for maybe you're writing a web app and want it to be a bit more like MySQL or Postgres. They aren't defaults for using SQLite as an in-memory cache on a complex piece of analysis. SQLite is an amazingly widely used piece of software. It's impossible for one set of defaults to be perfect for all use cases.
jotaen 2y ago
I agree. While it’s probably not possible to settle on defaults that work for each and every scenario, my personal preference is that factory defaults should tend to optimise for safety primarily. (Both operational safety, but also in regards to usage.) For example, OP suggests setting the `synchronous` pragma to `NORMAL`. This can be a performance gain, but it also comes at the cost of slightly decreased durability. So for that setting, I’d feel that `FULL` (the default) makes more sense as factory default for a database.
jccooper 2y ago
https://www.sqlite.org/pragma.html https://www.sqlite.org/pragma.html does a pretty good job on the tradeoffs. Something like WAL you'd want to look a bit deeper on... but need not go too far afield: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
scorpioxy 2y ago
I agree that they are tradeoffs and I find it strange to use a term like "sensible" to describe them. In my mind, it implies that if you're not using them you're not being sensible. And in my experience, people who can't explain that these are tradeoffs often use words like that[0]. Of course English is not my first language so that may be the reason why I think that. These may be useful settings for a certain kind of application under a certain workload. As usual, monitor your application and decide what is suitable for your situation. It is limiting to think in a binary way of something being sensible or not. [0] Like "Best Practices". Any else you can think of?

by ratorx 2y ago

TheRealPomax 2y ago

That's the essence of defaults: they're just better defaults, not an excuse to stop caring about what better settings to use once you have runtime performance metrics to guide whether they need changing and which direction to change them in.

ratorx 2y ago
Right, but they are not the official defaults. If you are changing the official defaults, then you might as well do it in a more principled way. If you are going to need to optimise with performance metrics anyway, then why not stick to just the official defaults (unless the official defaults are non-functional, is that the case?) I think anyone who is reading a blog post on “better defaults” is front loading some of the optimisation, so you could let them make a principled choice straight away for marginal extra cost.
- nemothekid 2y ago
  A lot of these aren’t defaults because of backwards compatibility. IMO there is no reason to not use WAL mode, but it’s not default because it came later
  - bruce511 2y ago
    As long as all the processes are on the same machine the wal mode is a good idea. It's not good in the case where multiple machines are sharing the same database. Like say if you had a shared settings file which allowed multiple VMs to be set in one place. Obviously the same-machine situation is the most common. But you asked for a reason for when wal is not appropriate.
  - prirun 2y ago
    There is a list of 6 disadvantages of WAL mode on the SQLite site: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- TheRealPomax 2y ago
  There is some principle here: these are more sensible defaults (but still only defaults) for a very different purpose when compared to the defaults that make sense for "generic SQLite use" that has to keep those set to something that works across all versions. Having different, domain-specific defaults based on an assumption of "you're setting up a new project using the current version of SQLite" makes a whole lot of sense.
- antisthenes 2y ago
  > If you are going to need to optimise with performance metrics anyway, then why not stick to just the official defaults (unless the official defaults are non-functional, is that the case?) Well, why don't you do the research and tell us? In my 10-15 years of dealing with official defaults of many programs is that they do work, but in 90% of cases they are overly-conservative.

btilly 2y ago

The note of several of them as being defaults for a web app kind of sets expectations for me. These are defaults for maybe you're writing a web app and want it to be a bit more like MySQL or Postgres. They aren't defaults for using SQLite as an in-memory cache on a complex piece of analysis. SQLite is an amazingly widely used piece of software. It's impossible for one set of defaults to be perfect for all use cases.

jotaen 2y ago

I agree. While it’s probably not possible to settle on defaults that work for each and every scenario, my personal preference is that factory defaults should tend to optimise for safety primarily. (Both operational safety, but also in regards to usage.) For example, OP suggests setting the `synchronous` pragma to `NORMAL`. This can be a performance gain, but it also comes at the cost of slightly decreased durability. So for that setting, I’d feel that `FULL` (the default) makes more sense as factory default for a database.

jccooper 2y ago

https://www.sqlite.org/pragma.html https://www.sqlite.org/pragma.html does a pretty good job on the tradeoffs. Something like WAL you'd want to look a bit deeper on... but need not go too far afield: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html

scorpioxy 2y ago

I agree that they are tradeoffs and I find it strange to use a term like "sensible" to describe them. In my mind, it implies that if you're not using them you're not being sensible. And in my experience, people who can't explain that these are tradeoffs often use words like that[0]. Of course English is not my first language so that may be the reason why I think that. These may be useful settings for a certain kind of application under a certain workload. As usual, monitor your application and decide what is suitable for your situation. It is limiting to think in a binary way of something being sensible or not. [0] Like "Best Practices". Any else you can think of?