3 ms·

SQLite defaults have many, many weird choices that make unsuspecting users choose dangerous configurations. And it's not reasonable to expect them to read thro

SQLite defaults have many, many weird choices that make unsuspecting users choose dangerous configurations. And it's not reasonable to expect them to read through all the docs to figure out that the defaults are not safe. Defaults should be safe, tune for performance. Not the other way around.

jabwd 1y ago
Sqlite defaults in many ways perfectly fine, you get the foot guns when you need the performance. Read the article rather than commenting on HN because WAL is not default.
- btown 1y ago
  There's some nuance here. The compiler flags SQLITE_DEFAULT_SYNCHRONOUS and SQLITE_DEFAULT_WAL_SYNCHRONOUS are set to FULL by default, which does fsync on each commit. https://sqlite.org/compile.html https://sqlite.org/compile.html But there is a thing called NORMAL mode which, in WAL and non-WAL mode, does not fsync on each commit. In WAL mode, at least this doesn't cause corruption, but it can still lose data. https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous is very explicit that the thing called NORMAL does have these risks. But it's still called NORMAL, and I'd think that's something of a foot-slingshot for newcomers, if not a full-fledged footgun.
  - nutjob2 1y ago
    FULL can also lose data if you lose power or crash before the fsync. This is just a simple trade of losing slightly more data (possibly) in return for better performance. Fsync is relatively expensive. Recovery price is not going to differ much between the two settings. It's like 1 in 1000 loss for 999 in 1000 gain. Makes perfect sense to me.
- nh2 1y ago
  > Sqlite defaults in many ways perfectly fine In the sibling comment we are discussing how the default of sqlite is not durable, so it's only ACI, not ACID. https://news.ycombinator.com/item?id=45005866 https://news.ycombinator.com/item?id=45005866 So you do get the foot guns automatically. > rather than commenting on HN I appreciate the parent commenting on HN, because they seem to be ... right.
  - tobias3 1y ago
    The default with the build in sqlite in macos. Means Apple engineers made this choice.
- ehutch79 1y ago
  But I constantly read advice to change to WAL on this very site. No further nuance, just that if I do it’ll out perform a MySQL cluster.
  - lupusreal 1y ago
    That's why you should read the documentation and not comments from random bozos telling you to do a thing without you, or probably they, even knowing what it actually does. Read the documentation and learn how your tools work. Don't cargo cult.
conradev 1y ago
This is perhaps my favorite one. By default, SQLite doesn’t actually delete data: The default setting for secure_delete is determined by the SQLITE_SECURE_DELETE compile-time option and is normally off. The off setting for secure_delete improves performance by reducing the number of CPU cycles and the amount of disk I/O. https://www.sqlite.org/pragma.html#pragma_secure_delete https://www.sqlite.org/pragma.html#pragma_secure_delete
- rcxdude 1y ago
  That isn't much unlike most filesystems (in fact, on CoW filesystems, even with this setting the data is likely to still be hanging around on disk)
lupusreal 1y ago
I swear I think people choose WAL mode because they read something about it online, where that something obviously isn't the documentation. This behavior shouldn't be catching any engineer by surprise.
sgarland 1y ago
Yes, yes it does [0]. I fully understand the need for backwards-compatibility given the sheer number of SQLite installations, I find their attitude towards flexible types [1] appalling. At least they’ve added STRICT. Similarly, the fact that FKs aren’t actually enforced by default is a horrifying discovery if you’re new to it, but tbf a. MySQL until quite recently allowed CHECK constraints to be declared, which it then ignored b. The FK enforcement decision is for backwards-compatibility. [0]: https://www.sqlite.org/quirks.html https://www.sqlite.org/quirks.html [1]: https://www.sqlite.org/flextypegood.html https://www.sqlite.org/flextypegood.html
- sevensor 1y ago
  I disagree about flexible types. SQLite bills itself as a better fopen, not as a full on database engine. If you have data sitting outside your application, trusting it to be well typed is just going to cause trouble. If your database is the application, that’s a different story.
  - sgarland 1y ago
    I’d rather have the INSERT fail than to discover it later when I’m trying to do math with a string, but to each their own.
ectospheno 1y ago
The article just demonstrates that you should compile SQLite yourself for any real app. After reading the documentation.

jabwd 1y ago

Sqlite defaults in many ways perfectly fine, you get the foot guns when you need the performance. Read the article rather than commenting on HN because WAL is not default.

btown 1y ago
There's some nuance here. The compiler flags SQLITE_DEFAULT_SYNCHRONOUS and SQLITE_DEFAULT_WAL_SYNCHRONOUS are set to FULL by default, which does fsync on each commit. https://sqlite.org/compile.html https://sqlite.org/compile.html But there is a thing called NORMAL mode which, in WAL and non-WAL mode, does not fsync on each commit. In WAL mode, at least this doesn't cause corruption, but it can still lose data. https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous is very explicit that the thing called NORMAL does have these risks. But it's still called NORMAL, and I'd think that's something of a foot-slingshot for newcomers, if not a full-fledged footgun.
- nutjob2 1y ago
  FULL can also lose data if you lose power or crash before the fsync. This is just a simple trade of losing slightly more data (possibly) in return for better performance. Fsync is relatively expensive. Recovery price is not going to differ much between the two settings. It's like 1 in 1000 loss for 999 in 1000 gain. Makes perfect sense to me.
nh2 1y ago
> Sqlite defaults in many ways perfectly fine In the sibling comment we are discussing how the default of sqlite is not durable, so it's only ACI, not ACID. https://news.ycombinator.com/item?id=45005866 https://news.ycombinator.com/item?id=45005866 So you do get the foot guns automatically. > rather than commenting on HN I appreciate the parent commenting on HN, because they seem to be ... right.
- tobias3 1y ago
  The default with the build in sqlite in macos. Means Apple engineers made this choice.
ehutch79 1y ago
But I constantly read advice to change to WAL on this very site. No further nuance, just that if I do it’ll out perform a MySQL cluster.
- lupusreal 1y ago
  That's why you should read the documentation and not comments from random bozos telling you to do a thing without you, or probably they, even knowing what it actually does. Read the documentation and learn how your tools work. Don't cargo cult.

conradev 1y ago

This is perhaps my favorite one. By default, SQLite doesn’t actually delete data: The default setting for secure_delete is determined by the SQLITE_SECURE_DELETE compile-time option and is normally off. The off setting for secure_delete improves performance by reducing the number of CPU cycles and the amount of disk I/O. https://www.sqlite.org/pragma.html#pragma_secure_delete https://www.sqlite.org/pragma.html#pragma_secure_delete

rcxdude 1y ago
That isn't much unlike most filesystems (in fact, on CoW filesystems, even with this setting the data is likely to still be hanging around on disk)

lupusreal 1y ago

I swear I think people choose WAL mode because they read something about it online, where that something obviously isn't the documentation. This behavior shouldn't be catching any engineer by surprise.

sgarland 1y ago

Yes, yes it does [0]. I fully understand the need for backwards-compatibility given the sheer number of SQLite installations, I find their attitude towards flexible types [1] appalling. At least they’ve added STRICT. Similarly, the fact that FKs aren’t actually enforced by default is a horrifying discovery if you’re new to it, but tbf a. MySQL until quite recently allowed CHECK constraints to be declared, which it then ignored b. The FK enforcement decision is for backwards-compatibility. [0]: https://www.sqlite.org/quirks.html https://www.sqlite.org/quirks.html [1]: https://www.sqlite.org/flextypegood.html https://www.sqlite.org/flextypegood.html

sevensor 1y ago
I disagree about flexible types. SQLite bills itself as a better fopen, not as a full on database engine. If you have data sitting outside your application, trusting it to be well typed is just going to cause trouble. If your database is the application, that’s a different story.
- sgarland 1y ago
  I’d rather have the INSERT fail than to discover it later when I’m trying to do math with a string, but to each their own.

ectospheno 1y ago

The article just demonstrates that you should compile SQLite yourself for any real app. After reading the documentation.