SQL pragma are statements (like SELECT … or CREATE TABLE …) that change the database behaviors or call a special functions. This post is a short list of SQLite pragma I use in my projects built on SQLite, to get better performance and more consistency.

TL;DR

When Opening the DB

PRAGMA journal_mode = wal; -- different implementation of the atomicity properties
PRAGMA synchronous = normal; -- synchronise less often to the filesystem
PRAGMA foreign_keys = on; -- check foreign key reference, slightly worst performance

and check user_version.

When Closing the DB

PRAGMA analysis_limit=400; -- make sure pragma optimize does not take too long
PRAGMA optimize; -- gather statistics to improve query optimization

Performance

Filesystem Interactions

The following pragma statements set the way SQLite deals with the file system.

journal_mode wal

By default, when applying changes, SQLite uses a rollback journal, which is basically a copy of the data before the changes. Changing the journal mode to “Write-Ahead Log” is known to bring significantly better performance in most cases. It also allows concurrent readers with one writer. To activate it, run:

PRAGMA journal_mode = wal;

Some less common use cases are incompatible with this journal mode, for instance having a database on a network filesystem. The full list of drawbacks is listed in the documentation.

synchronous normal

To ensure integrity of the database, one of the mechanism SQLite uses is the filesystem synchronisation operations. However, these synchronisations are quite costly. With WAL journal mode enabled, your database will still be consistent while synchronising less often:

PRAGMA synchronous = normal;

Compared to the default synchronous = full, commited transactions could be rolled back if there is a powerloss (although not if the application crashes).

Optimize

To execute statement as efficiently as possible, SQLite has a query planner, which tries to read the tables to provide good performance (for instance by evaluating the WHERE clauses that select the fewest rows first).

This query planner sometimes need to know whether a column has many values or only a few, repeated values (like a boolean column would). To know this, it can’t read the whole table, as that may prove as costly as running the part of the query that is being optimized, so it uses some statistics collected in internal tables.

Using optimize right before closing the database connexion collects the statistics for some table columns. These colums are chosen mainly based on the queries executed during the connexion: if a query had benefited from more accurate statistics, the corresponding columns are analyzed.

For instance:

PRAGMA analysis_limit=400;
PRAGMA optimize;

In the above example, pragam analysis_limit ensures that optimize won’t run for too long, by limiting the number of rows read.

Allow Using More Memory

These two pragma could result in better performance, depending on your hardware and software configuration.

  • Keep temporary storage in memory: PRAGMA temp_store = 2. However, setting this pragma does not guarantee that the temporary storage will be held in memory. Please refer to the documentation for other parameters that may change the final outcome.
  • Keep more of the database pages in memory: for instance, use 32 MiB of memory for this purpose with PRAGMA cache_size = -32000. Note that the OS cache is already keeping parts of the database file in RAM, so this might end up wasting memory.

Consistency

user_version

The database’s schema can evolve over time. Your application could start with a car table to represent a car but later on, you realize you want to represent bicycles, so you add a bicycle table.

To keep track of the different versions of the schema, some libraries maintain an internal table with a single row with a version number. It then performs migrations as needed. In our example, version 1 would have only the car table and version 2, also the bicycle table. The migration from version 1 to version 2 add the bicycle table.

SQLite offers the user_version pragma, to keep track of these version. It is an integer at a fixed offset in the database file. It is simpler and more efficient than maintaining a table with versions, in particular because the table has to be found in the database file while the integer is available right away.

The drawback is that this is not portable between database engines. If you only use SQLite though, it is almost always the best option. To use it, you can write some code to check the value of the user_version pragma value right after opening the database. If it is lower than expected, then atomically 1) run the necessary migrations and 2) increment the user_version pragma value. I wrote a library to ease this task in rust and here is an example in python.

foreign_keys on

This may come as a surprise for folks with experience with other database system, but SQLite does not enforce foreign key constraints by default. This means that a foreign key can point to a row that does not exist.

This can be fixed with pragma foreign_keys:

PRAGMA foreign_keys = ON;

This comes at a performance cost however, because more checks are performed when inserting values with foreign keys. The cost is usually negligible but your mileage may vary.

Note: pragma foreign_key_check can be used to check a particular table for violated foreign key constraint. This can be useful before enabling foreign_keys on a database with existing data.

Go deeper

The full list of supported pragma with detailed descriptions is available in the documentation.


Discussion:  reddit  twitter

This blog does not host comments, but you can contact me!