SQLite synchronous PRAGMA: Balance Safety and Speed (2026)

SQLite’s PRAGMA synchronous setting is the most common knob you’ll twist to balance data safety against write performance.

Imagine you’re writing a critical transaction to disk. SQLite, by default, takes a few steps to ensure that data actually makes it to the physical storage before it tells you it’s done. This is good for preventing data loss if your system crashes mid-write, but it can really slow things down if you’re doing a lot of writes. PRAGMA synchronous lets you dial this safety level up or down.

Let’s see this in action. We’ll create a temporary database and insert some data, measuring the time it takes under different synchronous settings.

import sqlite3
import time

def insert_data(db_path, num_records, synchronous_setting):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute(f"PRAGMA synchronous = {synchronous_setting}")
    cursor.execute("CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, value TEXT)")
    start_time = time.time()
    for i in range(num_records):
        cursor.execute("INSERT INTO items (value) VALUES (?)", (f"item_{i}",))
    conn.commit()
    end_time = time.time()
    conn.close()
    return end_time - start_time

db_file = ":memory:" # Use an in-memory database for speed, but results are illustrative
num_records = 10000

# Full: Highest safety, slowest
sync_full_time = insert_data(db_file, num_records, 3)
print(f"Synchronous = 3 (Full): {sync_full_time:.4f} seconds")

# Normal: Default, good balance
sync_normal_time = insert_data(db_file, num_records, 2)
print(f"Synchronous = 2 (Normal): {sync_normal_time:.4f} seconds")

# Off: Lowest safety, fastest
sync_off_time = insert_data(db_file, num_records, 0)
print(f"Synchronous = 0 (Off): {sync_off_time:.4f} seconds")

When you run this, you’ll observe a significant difference. synchronous = 3 will be the slowest, synchronous = 0 the fastest, and synchronous = 2 somewhere in between. The exact numbers will vary based on your hardware, but the trend is consistent: more synchronous means more waiting for the disk.

The core problem PRAGMA synchronous addresses is how SQLite interacts with the operating system’s file system and the underlying hardware to ensure data durability. When you write data, it goes through several layers: your application, SQLite’s WAL (Write-Ahead Log) or rollback journal, the OS buffer cache, and finally to the physical disk. A crash at any point can lead to data corruption or loss.

Here’s how the settings map to this process:

• synchronous = 3 (Full): This is the most aggressive setting. After a COMMIT, SQLite calls fsync() on the database file and the journal file (if used). fsync() is a system call that forces the operating system to flush all buffered data for that file to the physical disk and wait until the disk has confirmed it’s written. This guarantees that even if the OS crashes or the power goes out immediately after the COMMIT returns, your data is safely on disk. This is the safest but slowest option.

• synchronous = 2 (Normal): This is the default. After a COMMIT, SQLite calls fsync() on the journal file only. It doesn’t fsync() the main database file. The theory here is that the journal file is written first, and if a crash occurs, you can replay the journal to restore the database. However, if the OS crashes after the journal is flushed but before the main database file is updated from the journal (or if the OS itself loses data buffered for the main database file), you could still lose the transaction. This offers a good balance, as fsync() on the journal is faster than on both files.

• synchronous = 1 (Off): This setting is actually a bit of a misnomer. It doesn’t turn off synchronization entirely. Instead, after a COMMIT, SQLite waits for the write() operations to return, but it does not call fsync() at all. The data is written to the OS buffer cache, but it might not have hit the physical disk yet. If the OS crashes or power is lost before the OS flushes its buffers to disk, the data will be lost. This is the fastest option, but it significantly increases the risk of data loss.

• synchronous = 0 (No-op): This is the fastest but most dangerous. SQLite essentially relies on the OS to flush data to disk eventually. It doesn’t explicitly wait for any fsync() calls or even for writes to complete. If the OS crashes or power is lost, you’re very likely to lose recent writes. This is generally only suitable for temporary databases or scenarios where data loss is acceptable.

There’s another setting, synchronous = 4 (Fuller), which is similar to 3 but also calls fsync() on the WAL file in WAL mode.

The choice between these settings is a direct trade-off. If your application performs many small writes, like logging or frequent updates, and can tolerate the occasional loss of a few recent entries in the event of a system crash, setting synchronous = 0 or 1 can dramatically improve performance. If, however, you are writing critical financial data or any information that absolutely cannot be lost, synchronous = 3 is your best bet, even with the performance hit. For most general-purpose applications, synchronous = 2 (the default) provides a reasonable balance.

What people often overlook is how the PRAGMA synchronous setting interacts with the journaling mode. In rollback journal mode, synchronous = 2 only fsync()s the journal. In WAL (Write-Ahead Log) mode, synchronous = 2 fsync()s the WAL file, which is generally more efficient for concurrent readers and writers, but the durability guarantees are still tied to that WAL file fsync(). If you’re using WAL mode and need maximum durability, PRAGMA synchronous = 3 (or 4) is the way to go, ensuring both the WAL and the main database file are flushed.

After you’ve tuned PRAGMA synchronous, you might find yourself wanting to optimize how SQLite handles memory allocation for reads, which is controlled by PRAGMA cache_size.