Hacker News Reader

Comment by dangoodmanUT | Hacker News Reader

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.

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.

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

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

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.

> 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

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.

The default with the build in sqlite in macos. Means Apple engineers made this choice.

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.

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.

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.

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

[1]: https://www.sqlite.org/flextypegood.html

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.

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.

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

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)

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

Reply on news.ycombinator.com