Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 33 ยท Performance

PRAGMAs and Tuning

Learn the SQLite PRAGMAs that matter: journal_mode WAL, synchronous, cache_size, page_size, busy_timeout, mmap_size, foreign_keys, and what each one trades off.

A PRAGMA is SQLite's way of asking or changing the behavior of the database engine itself, as opposed to the data inside it. PRAGMA foreign_keys = ON switches on constraint checking, PRAGMA user_version reads a number stored in the file header, PRAGMA table_info('t') describes a table. They are not standard SQL; every PRAGMA is SQLite-specific.

Many PRAGMAs are performance and safety dials. The right setting depends on what you value: raw speed, durability after a power cut, many concurrent readers, or low memory. This lesson explains the handful you will actually use, what each one trades away, and which settings last in the file versus only for the current connection. The settings that need two processes (WAL, locking, busy timeouts) are shown as read-only code, because the in-browser engine has a single connection and no real file on disk. Reading and setting the simpler ones works live.

Two kinds of PRAGMA

Stored in the file journal_mode WAL, page_size, auto_vacuum, user_version.
Per connection synchronous, cache_size, foreign_keys, busy_timeout.

Per-connection settings must be repeated every time you connect.

1 / 2

The PRAGMA syntax

A PRAGMA with no value reads the setting; with = value it sets it (and some print the new value). Many also have a function form you can use inside SELECT, written pragma_name(...), which is handy for joining PRAGMA results with other queries.

SQL Reading the defaults

Press Run (or Ctrl + Enter) to see the result here.

On the site's engine you see delete, 2, -2000, 4096, 0, 0, 0, 0, 0 and 0. Those are SQLite's built-in defaults, which a new connection to a file database also starts with (a file database that is already in WAL mode reports wal instead). The numbers mean:

PRAGMA Default Meaning
journal_mode delete How changes are made atomic: a rollback journal file that is deleted after each commit
synchronous 2 (FULL) How careful SQLite is about flushing data to disk
cache_size -2000 Page cache size; negative means kilobytes (about 2 MB), positive means pages
page_size 4096 Bytes per database page
busy_timeout 0 Milliseconds to wait for a lock before failing (0 = fail immediately)
foreign_keys 0 (off) Whether foreign keys are enforced
temp_store 0 Where temp tables and sort spills go (0 = compile-time default, 1 = file, 2 = memory)
mmap_size 0 Bytes of the file to read through memory mapping (0 = off)

Setting a value and reading it back

SQL Setting PRAGMAs

Press Run (or Ctrl + Enter) to see the result here.

Foreign keys go from 0 to 1, synchronous becomes 1 (NORMAL), the cache becomes 64,000 KB (about 64 MB), temp storage becomes 2 (memory) and user_version is now 3. Settings such as synchronous accept either the words (OFF, NORMAL, FULL, EXTRA) or numbers 0 to 3. Remember that all of these except user_version live only as long as the connection: open a new connection and you are back to the defaults.

journal_mode and WAL

SQLite must make every commit atomic: after a crash, either the whole transaction is there or none of it. The journal mode decides how.

  • DELETE (default): before changing a page, SQLite copies the original to a separate rollback journal file. On commit it deletes the journal. Readers and a writer block each other during the write.
  • WAL (write-ahead log): changes are appended to a second file, the -wal file, and the main file is updated later in the background (a checkpoint). Readers keep reading the old data while a writer works, so readers do not block the writer and the writer does not block readers. Commits are usually faster too, because they are one sequential append.
  • Others (TRUNCATE, PERSIST, MEMORY, OFF) are niche; MEMORY and OFF give up crash safety.
SQL
PRAGMA journal_mode = WAL;    -- returns: wal
PRAGMA journal_mode;          -- returns: wal (on any later connection too)

Unlike most PRAGMAs, journal_mode = WAL is stored in the database file, so it only has to be set once and every later connection uses it. The trade-offs of WAL are the reason it is not the default: it needs shared memory between processes on the same machine (so it does not work on network file systems), the database is now several files (app.db, app.db-wal, app.db-shm), which must be copied together if you copy by hand, and a very long-running reader can stop the WAL file from being recycled so it keeps growing. Concurrency, Locking and WAL goes deeper.

synchronous: how much to flush

SQLite calls fsync (ask the operating system to push data onto the physical disk) at certain moments in a commit. synchronous decides how many of them:

Value Behavior Risk
OFF (0) No flushing; hand data to the OS and carry on An OS crash or power loss can corrupt the database
NORMAL (1) Fewer flushes In WAL mode: no corruption, but the last few commits may vanish after a power loss. In other modes: a small risk
FULL (2, default) Flush at every critical step Commits survive power loss
EXTRA (3) Also flush the directory after deleting the journal Extra safety for odd file systems

A good, common pairing is journal_mode = WAL with synchronous = NORMAL: the database stays consistent, commits are noticeably faster, and you only risk the very latest transactions in a power failure (not an application crash). If losing a committed order is unacceptable, stay on FULL. OFF is for data you can rebuild, like a cache or a one-off import you will re-run if it fails.

cache_size, mmap_size and temp_store

  • cache_size is how many pages SQLite keeps in memory per connection. A bigger cache means fewer disk reads for repeated queries. A negative number is in kilobytes, so -64000 is about 64 MB. The default of roughly 2 MB is deliberately small; for a database that is read heavily on a server, raising it is one of the cheapest wins. It is per connection, so many connections multiply the memory.
  • mmap_size lets SQLite read the file through the operating system's memory mapping instead of read() calls. Setting it to a large value such as 268435456 (256 MB) can speed up read-heavy workloads. The cost is that an I/O error on the file may surface as a crash of the process instead of an error return, so many applications leave it at 0.
  • temp_store chooses where temporary tables, indexes and sort spill files live. MEMORY (2) is faster but uses RAM; on a memory-constrained device, leave it alone.

page_size

The database file is a series of fixed-size pages, 4096 bytes by default. You can only choose the page size before the first table is created (or later by running VACUUM, which rebuilds the file). Larger pages (8192, 16384) can help with big rows and sequential scans, smaller ones with small random updates. Most applications never change it.

SQL page_size must come first

Press Run (or Ctrl + Enter) to see the result here.

The page size reads 8192 because it was set before any table existed. page_count is 2 (one page for the schema and one for the notes table), so the database occupies 16,384 bytes. freelist_count shows pages that are free inside the file after deletes; those pages are reused but do not shrink the file until a VACUUM.

busy_timeout

If another connection holds a lock you need, SQLite by default gives up at once with SQLITE_BUSY ("database is locked"). PRAGMA busy_timeout = 5000 instead retries for up to 5 seconds before failing. Almost every application with more than one connection should set it. Most drivers also have their own timeout option that does the same thing, so check which one you are using. Like synchronous, it is per connection.

SQL
PRAGMA busy_timeout = 5000;   -- wait up to 5 s for a lock

foreign_keys

Foreign key enforcement is off by default for historical reasons and must be turned on for every connection (see Foreign Keys). It cannot be changed in the middle of a transaction.

A typical startup block

Many applications open a connection and run something like this, then keep it open:

SQL
PRAGMA journal_mode = WAL;     -- persistent, once per database
PRAGMA synchronous = NORMAL;   -- per connection
PRAGMA foreign_keys = ON;      -- per connection
PRAGMA busy_timeout = 5000;    -- per connection
PRAGMA cache_size = -32000;    -- per connection, ~32 MB

Treat that as a starting point and not as a rule. The biggest speed-up for writing is usually not a PRAGMA at all: put many INSERTs inside one transaction (Transactions). Without it each statement is its own commit with its own flush to disk, which is the slowest thing SQLite can do. Measure your real workload before and after any tuning change, and change one setting at a time.

Housekeeping PRAGMAs

A few PRAGMAs do not tune anything but are constantly useful:

SQL Inspection and health PRAGMAs

Press Run (or Ctrl + Enter) to see the result here.

integrity_check and quick_check both print ok for a healthy database (the first examines more, the second is faster); on a damaged file they list the problems. pragma_table_info describes columns and pragma_index_list lists indexes. Both can be filtered and joined like any table. user_version and application_id are two free integers in the file header: Altering Schema Safely shows how to use user_version for schema migrations.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which of these PRAGMAs is stored in the database file and so applies to every later connection?

    WAL mode is recorded in the file header. The others are per-connection and must be set again each time you connect.
  2. 2. journal_mode = WAL is combined with synchronous = NORMAL. What is the main risk?

    In WAL mode NORMAL keeps the database consistent. It only skips some flushes, so the newest commits can be lost if the machine loses power.
  3. 3. What does a negative cache_size such as -64000 mean?

    A negative value is interpreted as a size in kilobytes; a positive value is a number of pages.
  4. 4. When can you change page_size the cheap way?

    The page size is fixed when the file is first written. Afterwards you change it by setting the PRAGMA and running VACUUM to rebuild the file.
  5. 5. What does PRAGMA busy_timeout = 5000 do?

    It is a per-connection wait for locks, not a query time limit.

Summary

  • PRAGMAs read or change engine settings; some live in the file (journal_mode = WAL, page_size, user_version) and the rest are per connection.
  • journal_mode = WAL lets readers and a writer work together; it needs a local file system and adds -wal and -shm files.
  • synchronous trades durability for speed: FULL (default) is safest, NORMAL plus WAL is a popular balance, OFF risks corruption.
  • cache_size, mmap_size and temp_store spend memory to save disk reads; busy_timeout waits for locks; foreign_keys must be switched on each connection.
  • Batching writes in one transaction beats most PRAGMAs; measure before and after any change.

Next: Full-Text Search with FTS5 shows how to search large amounts of text quickly.