High-Performance SQLite Production PRAGMAs

Supanote Core Team @supanote · 1 min read

plain (sql) 1 day ago · 24 lines · 9 views

-- High-Performance Concurrent SQLite Configuration
-- Run immediately after opening database connection

-- 1. Enable Write-Ahead Logging (concurrent readers + writer)
PRAGMA journal_mode = WAL;

-- 2. Synchronous NORMAL is safe in WAL mode and drastically cuts fsync calls
PRAGMA synchronous = NORMAL;

-- 3. In-memory temporary tables and indices
PRAGMA temp_store = MEMORY;

-- 4. Set 64MB page cache (-64000 KB)
PRAGMA cache_size = -64000;

-- 5. Busy timeout to prevent immediate locked database failures (5 seconds)
PRAGMA busy_timeout = 5000;

-- 6. Enforce Foreign Key relational constraints
PRAGMA foreign_keys = ON;

-- 7. Memory-mapped I/O (up to 256MB)
PRAGMA mmap_size = 268435456;

1-- High-Performance Concurrent SQLite Configuration

2-- Run immediately after opening database connection

4-- 1. Enable Write-Ahead Logging (concurrent readers + writer)

5PRAGMA journal_mode = WAL;

7-- 2. Synchronous NORMAL is safe in WAL mode and drastically cuts fsync calls

8PRAGMA synchronous = NORMAL;

10-- 3. In-memory temporary tables and indices

11PRAGMA temp_store = MEMORY;

13-- 4. Set 64MB page cache (-64000 KB)

14PRAGMA cache_size = -64000;

16-- 5. Busy timeout to prevent immediate locked database failures (5 seconds)

17PRAGMA busy_timeout = 5000;

19-- 6. Enforce Foreign Key relational constraints

20PRAGMA foreign_keys = ON;

22-- 7. Memory-mapped I/O (up to 256MB)

23PRAGMA mmap_size = 268435456;

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note