9 ms·
Sensible SQLite Defaults
- darthShadow 2y agoFWIW, auto_vacuum set to INCREMENTAL doesn't do any vacuuming automatically; it just stores the information needed to vacuum the DB. From https://www.sqlite.org/pragma.html#pragma_auto_vacuum https://www.sqlite.org/pragma.html#pragma_auto_vacuum: > When the value of auto-vacuum is 2 or "incremental" then the additional information needed to do auto-vacuuming is stored in the database file but auto-vacuuming does not occur automatically at each commit as it does with auto_vacuum=full. In incremental mode, the separate incremental_vacuum pragma must be invoked to cause the auto-vacuum to occur.
- neverartful 2y agoUnfortunately, there doesn't seem to be a PRAGMA option for strict tables. Docs at following link say that 'STRICT' must be appended to end of CREATE TABLE statement to disable the flexible typing 'feature'. https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- giraffe_lady 2y agoI don't think there can be with sqlite's backwards compatibility model. The DB has to assume it may be accessed with multiple versions of the library or clients using different pragma settings. So one client disabling strict (or using a version predating that feature) and inserting a bunch of violating records would break the DB file when used by another client with strict enabled. With it part of table creation, which IIRC is read for each connection to create the schema, it's part of the DB file not the client configuration. If you open a DB using strict with a version that doesn't support it, it will fail but not cause problems for other users.
- neverartful 2y agoYep, that all makes perfect sense!
- alberth 2y agoI wish there was a branch of SQLite that cleaned up everything, set new sane behavior (and broke backward compatibility since it’s needed to achieve that). Eg strict mode by default, WAL2, BEGIN CONCURRENT, etc. Maybe HC-Tree will become that? https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html https://sqlite.org/hctree/doc/hctree/doc/hctree/index.html
- silvestrov 2y agoIt is time for version 4 of SQLite.
- ratorx 2y agoI 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 agoThat'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 agoRight, 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 agoA 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 agoAs 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.
- 2y ago
- chasil 2y agoI do not agree that WAL mode should be enabled by default. It can break things if used incorrectly. https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf “To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.” “It is not possible to change the page size after entering WAL mode.” “In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.” https://www.sqlite.org/lang_attach.html https://www.sqlite.org/lang_attach.html “SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.”
- masklinn 2y agoThese all sounds like load-specific / ancillary concerns. I don't think most people are in one of those cases, and most people will greatly benefit from WAL. WAL should absolutely be the default unless you have a good reason to not use it.
- chasil 2y agoYou may prefer non-acid characteristics in your databases in general. I do not. There may be specific uses, but standards should be followed when possible.
- giraffe_lady 2y ago"When possible" in this case being simply don't enable the WAL if your use requires transactions across multiple attached sqlite DBs.
- 2y ago
- masklinn 2y ago> Why?: Prevents "database is locked" errors by giving SQLite 5 seconds to wait for a locked resource before returning an error, useful for handling multiple concurrent accesses. Alongside that one of the most important things to do is to have strong read/write segregation: separate readonly and r/w pools, the latter having a single connection which is immediately set to BEGIN IMMEDIATE when it's borrowed out.
- skrebbel 2y agoWhat do you mean by "connection" in the context of SQLite?
- giraffe_lady 2y agoHow is the meaning not clear? If you're trying for a "there's no connections in sqlite" type gotcha that's not even true. SQLite itself uses the term internally and it shows up in the docs. https://www.sqlite.org/c3ref/open.html https://www.sqlite.org/c3ref/open.html https://www.sqlite.org/c3ref/sqlite3.html https://www.sqlite.org/c3ref/sqlite3.html
- elchief 2y agowhy is cache size negative?
- cogman10 2y agoAccording to the docs, it sets the cache_size based on kilobytes rather than pages. [1] [1] https://www.sqlite.org/pragma.html#pragma_cache_size https://www.sqlite.org/pragma.html#pragma_cache_size
- acuozzo 2y agoThis will probably get buried, but I'm wondering: Has any scripting language attempted to use SQLite as its memory model? You get GC, memory safety, atomicity guarantees, core dump functionality, and rich data structures for free. If configured to run in-memory, I reckon it wouldn't impose enough overhead to be problematic. It sounds crazy, but most scripting languages add this stuff anyway. With rqlite this can be extended to distributed systems programming as well.
- aziis98 2y agoI also think about this sometimes, maybe not as only memory model but a simple scripting language with tight integration with sqlite would be pretty cool. P.S. Actually if think I saw something similar somewhere in the past, if it comes to mind I'll send it
- Imustaskforhelp 2y agoyeah dude send it , I am thinking of writing a scripting language some years later and I have absolutely loved this idea.
- emseetech 2y agoThat's interesting. There would be overhead compared to directly managing memory. But it would be wild to be able to declare an array of structs that you could directly query. var people person[5] people[5] // { name: "Human Person", age: 39 } people.query("SELECT name FROM a WHERE age = 39") // "Human Person" If everything in memory was a table structure, you could do joins across variables too. Would it have to be a scripted language? Seems like you could do it as a compiled language. I love SQL so if I had the option to use it within in application I'd be hard pressed not to. But from what I've learned from the popularity of ORMs and NoSQL, most don't share that feeling.
- pixelesque 2y agoA very similar list (more aimed at high-performance) here: https://highperformancesqlite.com/articles/sqlite-recommended-pragmas https://highperformancesqlite.com/articles/sqlite-recommende...
- jmull 2y ago> PRAGMA busy_timeout = 5000; I would go higher than this by default. Probably the best default is something like "amount of time a human would be willing to wait for something they want before giving up" which, if you have to pick one value, is hopefully longer than 5 sec. Perhaps 30000. Now, there are also cases where fail-fast is best. However 5000 isn't good for those either. And that assumes a wider system that handles failures in quite a nice way, which doesn't happen without special effort, so doesn't make sense to target with a default value.
- kgeist 2y agoIn my tests, with all these settings, if you hammer SQLite with reads/writes, it won't be able do any WAL checkpoints (needs no active transactions), and the WAL file can quickly grow from, say, 8 MB to tens, hundreds of GB until your machine runs of out space (the docs mention this). So my default is to also keep track of the size of the WAL file, and if it grows beyond some threshold, temporarily suspend all reads/writes and checkpoint manually.
- killingtime74 2y agoHow do you do this, with custom code in a maintenance thread?
- kgeist 2y agoYes, a separate thread.
- reactboii 2y agohttps://phiresky.github.io/blog/2020/sqlite-performance-tuning/ https://phiresky.github.io/blog/2020/sqlite-performance-tuni... does a better job of explaining imo