SQLite, again (updated)

calendar Jun 21, 2021 · 2 min read  en  · sql sqlite  ·
Share on: linkedin copy

SQLite is slowing becoming a new standard. It is fast, and has a growing number of features.

Useful settings

  1. PRAGMA journal_mode = wal;
  2. PRAGMA foreign_keys = true; Query, set, or clear the enforcement of foreign key constraints.
  3. PRAGMA busy_timeout=....; In milliseconds, the busy_timeout is associated with each connection to the database and as such you need to set the timeout for each connection.
  4. pragma synchronous = OFF; I call this "scissor mode" referring to running with a scissor in hands. This directive disables the call to fsync and it is very dangerous if a power outrange happens on the host. It delegates to filesystem the "fsync". Some database reach full speed disabling fsync call, but your data are not guaranteed to be stored in safe place until operating system flush data (which will eventually occur...).
  5. NUMERIC type is special.... https://news.ycombinator.com/item?id=28069694 From the documentation: "A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal . . . If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT." So NUMERIC is kind of like TEXT except when the text is a pure number.
  6. SQLite ".expert" can help you to find out missed indexes:
    For most non-trivial SQL databases, the key to performance is creating the right SQL indexes. In this context "the right SQL indexes" means those that cause the queries that an application needs to optimize run fast. The ".expert" command can assist with this by proposing indexes that might assist with specific queries, were they present in the database.

Relevant reading