SQLite has a reputation for being slow and problematic when doing heavy writing. When SQLite was developed, computers were much less powerful, but a priority for the maintainers of SQLite is to preserve reverse compatibility. Consequently, the defaults are not optimal. SQLite provides a set of PRAGMA commands that can optimize performance and significantly improve the efficiency of database operations, especially when performing lots of writes. This article will give some great SQLite optimization tips using PRAGMA commands.

What is a SQLite PRAGMA?

PRAGMA is an SQL extension specific to SQLite that allows you to query or change the database's behavior. It is used to set or query the value of run-time parameters that affect the behavior of the database system. PRAGMA commands are used to control various settings and features of the SQLite database engine. Since modern computers are fast and have lots of RAM, some of these PRAGMA commands allow you to configure SQLite to take advantage of these resources or otherwise change the behavior to optimize performance for your particular circumstances.

Some PRAGMA settings can be set globally for the entire database, while others can be set on a per-session basis—the command's description will note when they are session-based.

Important PRAGMA commands

Following are some of the most important PRAGMA commands you can use to optimize SQLite performance. Keep in mind that these commands are not a one-size-fits-all solution. You should test them in your specific use case to see if they improve performance, as the results can vary depending on the nature of your application and the data you are working with.

1. Journal mode

The journaling mode determines how changes are written to the database file and controls the durability of transactions. The default journal mode is DELETE, which is the slowest mode but provides the best durability. However, it is highly recommended that the journal mode be set to WAL (Write-Ahead Logging) to improve write performance, which can make a database run tens or hundreds of times faster in multi-user scenarios. WAL mode allows multiple readers and one concurrent writer, resulting in much higher throughput and better performance than the default rollback journal mode.

Recommendation:

PRAGMA journal_mode = WAL;

journal_mode is persistent.

2. Busy timeout

The busy timeout is the number of milliseconds that SQLite will wait for a lock to go away before returning an error. The default is 0 ms, which means that SQLite will return an error immediately if it cannot obtain a lock. However, this makes it difficult to handle contention without errors. Increasing the busy timeout to a small number of seconds can help prevent database lock errors in multi-threaded applications. This helps avoid "database is locked" errors by allowing SQLite to wait longer for locked resources to become available.

Recommendation:

PRAGMA busy_timeout = 5000;

3. Synchronous

Synchronous mode controls how SQLite handles disk I/O operations. The default synchronous mode (when not in WAL mode) is FULL, which means that SQLite will wait for the data to be written to disk before continuing. This can be slow, especially for write-heavy applications. Changing the synchronous mode to NORMAL or OFF improves write performance. NORMAL mode is usually sufficient for most applications, as it ensures that data is written to disk at the end of each transaction but does not wait for the data to be fully synced to disk.

Recommendation:

PRAGMA synchronous = NORMAL;

synchronous must be set for each session, although it defaults to NORMAL when using the WAL journal mode.

If you are willing to risk losing data in the event of a power failure but want the maximum possible performance, you can set the synchronous mode to OFF, which will make SQLite faster by not waiting for data to be written to disk at all.

4. Cache size

Cache size controls the number of pages in the cache. The default cache size is 2000 pages, but you can increase this to improve read performance. The cache size is measured in the number of pages, where each page is typically 4,096 bytes by default (although this can be customized). Increasing the cache size can reduce disk I/O and improve read performance in read-heavy applications with large databases. Note that using a negative value sets the cache size in kilobytes—for example, -100000 will set the cache size to 100,000 kilobytes or about 100 megabytes.

Recommendation:

PRAGMA cache_size = 10000;

cache_size must be set for each session.

Based on the calculation of your page size and the amount of memory available, you can want to increase the cache size beyond 10,000 pages, but be careful not to set it too high, as this can lead to memory pressure and performance degradation.

5. Temp Store

Temp store controls whether temporary storage is in memory or on disk. The default temp store mode is DEFAULT, which means the C preprocessor macro SQLITE_TEMP_STORE is used to determine where temporary tables and indices are stored. To ensure temporary tables are stored in memory, set the temp store mode to MEMORY. This can improve performance when working with temporary tables.

Recommendation:

PRAGMA temp_store = MEMORY;

6. Foreign keys

By default, SQLite does not enforce foreign key constraints. To enable them, you need to set the foreign key constraint PRAGMA to ON. This can help ensure data integrity by enforcing referential integrity between tables.

Recommendation:

PRAGMA foreign_keys = ON;

foreign_keys must be set for each session.

Using foreign key constraints is more of a preference than a performance optimization, but it can help prevent data problems caused by poor programming or user errors.

7. MMap Size

MMap size controls the size of the memory-mapped I/O buffer. The default mmap_size is 0, which means that memory-mapped I/O is disabled. To enable memory-mapped I/O, set the mmap_size to a positive value. Memory-mapped I/O can improve read performance by mapping the database file into memory, reducing disk I/O.

Recommendation:

PRAGMA mmap_size = 268435456; --256MB

mmap_size must be set for each session.

This is especially relevant to read-heavy applications. Depending on your database and system, mmap_size may not make a big difference, but you can try different values to see if they improve performance in your situation.

Special PRAGMA Commands for Specific Cases

There are two PRAGMA commands that you probably won't need in most cases, but they can be useful in specific situations and can only be applied to a new database. Here are a few examples:

1. Page size

Page size controls the size of the database pages. The default page size is typically 4096 bytes, but you can change this to larger values like 8192, 16384, or 32768 bytes. Increasing the page size can improve read performance, especially for large databases with many rows.

Example:

PRAGMA page_size = 8192;

page_size persists and can only be set when the database is empty.

2. Auto vacuum

"Vacuum" is a process that rebuilds the database file, removing free pages and reordering data to optimize performance and reduce the size of a database. By default, SQLite does not automatically vacuum the database.

If you have a database that frequently grows and shrinks in size on a system with limited disk space, this may be a good option to enable. However, vacuuming can be slow and resource-intensive, so it is not recommended for databases that are frequently updated or if you have ample disk space.

Example:

PRAGMA auto_vacuum = INCREMENTAL;

auto_vacuum persists. It can only be changed from NONE to INCREMENTAL or FULL when the database is empty.

The default NONE setting is sufficient for most situations. A FULL setting can be used to vacuum the database fully, but this is even more resource-intensive and should rarely be used in production.

Conclusion

By considering the recommended settings discussed in this article and testing them in your specific use case, you can optimize SQLite performance and improve the efficiency of your database operations. You must keep in mind your specific situation and database, as these are not one-size-fits-all solutions. However, these PRAGMA commands can be a good starting point for improving SQLite performance in many applications.

If you'd like to use our suggested defaults, which are the best PRAGMA settings for many scenarios and are a good starting point, you can use the following commands:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 10000;
PRAGMA temp_store = MEMORY;
PRAGMA foreign_keys = ON;
PRAGMA mmap_size = 268435456;