Enabling WAL in SQLite Tripled the Throughput of My Sync Service

SQLite does not enable WAL (Write-Ahead Logging) by default, which can significantly limit concurrent performance.

PlanTodo is a task management application. Recently, while building performance tests for its synchronization service, I found that simply enabling WAL increased throughput by 3x.

There are already excellent articles on SQLite performance optimization, such as Optimal SQLite settings for Django and Optimizing SQLite for servers. This article does not introduce any novel techniques. Its purpose is simply to help more developers understand SQLite’s performance potential and to share a real-world performance testing case study.

Performance Testing the PlanTodo Sync Service

The performance tests were divided into three scenarios:

1. oo_upload (one user, one device, upload only) — a single device continuously uploads data.
2. oo_download — download-only workload.
3. oo_cross — uploads and downloads interleaved.

The purpose of oo_upload and oo_download is to measure the upper performance limits of upload and download workloads independently, providing baselines for future optimizations.

oo_cross more closely resembles real-world usage: one device uploads several updates while another device is triggered to download them. This scenario can therefore be used to estimate how many users a server can realistically support.

If you don’t want to read the detailed benchmark results below, here’s a quick summary of the throughput improvements:

1. oo_upload: 18,027 → 61,682 (3.42×)
2. oo_download: 17,082 → 49,635 (2.90×)
3. oo_cross: 17,085 → 44,203 (2.58×)

Reads are generally faster than writes, so download performance should normally exceed upload performance. The fact that PlanTodo’s sync service shows the opposite suggests there is still substantial room for optimization.

The following results were collected before enabling WAL:

+ just -f services/sync/justfile headless_oo_upload --less-output
============================================================
Performance Summary for test_oo_upload
============================================================
Requests      : 18,027
Failures      : 0
Failure Rate  : 0.00%

Average RT    : 78.14 ms
P50           : 78 ms
P95           : 110 ms
P99           : 130 ms
Max           : 272.81 ms

Endpoints
------------------------------------------------------------
POST /v1/sync/delta
  Requests=18,011  Avg=78.1ms  P95=110ms  P99=130ms  Max=272.8ms

POST /v1/clients
  Requests=8  Avg=76.2ms  P95=110ms  P99=110ms  Max=109.9ms

GET /v1/sync/full
  Requests=8  Avg=57.2ms  P95=120ms  P99=120ms  Max=120.3ms

+ just -f services/sync/justfile headless_oo_download --less-output
============================================================
Performance Summary for test_oo_download
============================================================
Requests      : 17,082
Failures      : 0
Failure Rate  : 0.00%

Average RT    : 82.63 ms
P50           : 82 ms
P95           : 110 ms
P99           : 130 ms
Max           : 370.33 ms

Endpoints
------------------------------------------------------------
POST /v1/sync/delta
  Requests=16  Avg=172.6ms  P95=370ms  P99=370ms  Max=370.3ms

GET /v1/sync/delta?cursor=1780662404990&limit=100
  Requests=2,087  Avg=83.4ms  P95=110ms  P99=130ms  Max=316.1ms

GET /v1/sync/delta?cursor=1780662403978&limit=100
  Requests=2,102  Avg=83.3ms  P95=110ms  P99=130ms  Max=325.5ms

+ just -f services/sync/justfile headless_oo_cross --less-output
============================================================
Performance Summary for test_oo_cross
============================================================
Requests      : 17,085
Failures      : 0
Failure Rate  : 0.00%

Average RT    : 83.05 ms
P50           : 82 ms
P95           : 120 ms
P99           : 150 ms
Max           : 245.89 ms

Endpoints
------------------------------------------------------------
GET /v1/sync/delta?cursor=1780662760888&limit=100
  Requests=1  Avg=245.9ms  P95=250ms  P99=250ms  Max=245.9ms

GET /v1/sync/delta?cursor=1780662760900&limit=100
  Requests=1  Avg=245.4ms  P95=250ms  P99=250ms  Max=245.4ms

GET /v1/sync/delta?cursor=1780662758760&limit=100
  Requests=1  Avg=228.2ms  P95=230ms  P99=230ms  Max=228.2ms

The following results were collected after enabling WAL:

+ just -f services/sync/justfile headless_oo_upload --less-output
============================================================
Performance Summary for test_oo_upload
============================================================
Requests      : 61,682
Failures      : 0
Failure Rate  : 0.00%

Average RT    : 22.30 ms
P50           : 22 ms
P95           : 32 ms
P99           : 41 ms
Max           : 74.82 ms

Endpoints
------------------------------------------------------------
POST /v1/clients
  Requests=8  Avg=33.8ms  P95=48ms  P99=48ms  Max=48.1ms

POST /v1/sync/delta
  Requests=61,666  Avg=22.3ms  P95=32ms  P99=41ms  Max=74.8ms

GET /v1/sync/full
  Requests=8  Avg=20.7ms  P95=32ms  P99=32ms  Max=32.5ms

+ just -f services/sync/justfile headless_oo_download --less-output
============================================================
Performance Summary for test_oo_download
============================================================
Requests      : 49,635
Failures      : 0
Failure Rate  : 0.00%

Average RT    : 28.03 ms
P50           : 28 ms
P95           : 38 ms
P99           : 46 ms
Max           : 305.64 ms

Endpoints
------------------------------------------------------------
POST /v1/sync/delta
  Requests=16  Avg=127.0ms  P95=310ms  P99=310ms  Max=305.6ms

GET /v1/sync/delta?cursor=1780663066610&limit=100
  Requests=6,109  Avg=28.4ms  P95=38ms  P99=46ms  Max=246.0ms

GET /v1/sync/delta?cursor=1780663068640&limit=100
  Requests=6,064  Avg=28.3ms  P95=38ms  P99=46ms  Max=95.8ms

+ just -f services/sync/justfile headless_oo_cross --less-output
============================================================
Performance Summary for test_oo_cross
============================================================
Requests      : 44,203
Failures      : 0
Failure Rate  : 0.00%

Average RT    : 31.80 ms
P50           : 28 ms
P95           : 45 ms
P99           : 150 ms
Max           : 477.37 ms

Endpoints
------------------------------------------------------------
GET /v1/sync/delta?cursor=1780663421960&limit=100
  Requests=1  Avg=477.4ms  P95=480ms  P99=480ms  Max=477.4ms

GET /v1/sync/delta?cursor=1780663421963&limit=100
  Requests=1  Avg=475.9ms  P95=480ms  P99=480ms  Max=475.9ms

GET /v1/sync/delta?cursor=1780663421966&limit=100
  Requests=1  Avg=475.4ms  P95=480ms  P99=480ms  Max=475.4ms

Each benchmark ran for only three minutes, so the dataset is relatively small. Performance characteristics may differ significantly with larger datasets, which I plan to test in the future.

In the oo_cross benchmark, the service processed 44,203 requests in 3 minutes, or roughly 245 requests per second.

The test continuously repeated an upload-followed-by-download cycle, and client operations also occurred in upload/download pairs. Therefore, the system could handle approximately 122 devices per second (245 ÷ 2).

Even after accounting for additional overhead from multiple users and devices, sustaining 100 device requests per second should be realistic.

In real-world usage, users typically make changes every few minutes rather than continuously. Assuming one update every 10 minutes, a single sync server could theoretically support:

100 × 60 × 10 = 60,000 devices

under normal operating conditions.

How to Enable WAL

Regardless of which ORM you use, the principle is the same: execute the following statement once after establishing a database connection:

PRAGMA journal_mode=WAL;

After WAL is enabled, two additional files will appear alongside the SQLite database file:

.db-shm
.db-wal

Their presence is an easy way to verify that WAL mode has been successfully enabled.

SQLAlchemy

from sqlalchemy import create_engine, event

engine = create_engine(
    DATABASE_URL,
    echo=False,
    connect_args={
        "timeout": 5,
    },
)

if engine.dialect.name == "sqlite":
    @event.listens_for(engine, "connect")
    def set_sqlite_pragma(dbapi_connection, _):
        cursor = dbapi_connection.cursor()

        # cursor.execute("PRAGMA foreign_keys=ON")
        cursor.execute("PRAGMA journal_mode=WAL")
        cursor.execute("PRAGMA synchronous=NORMAL")
        cursor.execute("PRAGMA temp_store=MEMORY")
        cursor.execute("PRAGMA cache_size=2000")
        cursor.execute("PRAGMA mmap_size=134217728")

        cursor.close()

PlanTodo’s sync service is built with FastAPI and SQLAlchemy. Due to the nature of the synchronization workload, foreign key constraints are intentionally disabled.

Django

Add an init_command in your project's settings.py:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "db.sqlite3",
        "OPTIONS": {
            "init_command": (
                "PRAGMA foreign_keys = ON;"
                "PRAGMA journal_mode = WAL;"
                "PRAGMA synchronous = NORMAL;"
                "PRAGMA busy_timeout = 5000;"
                "PRAGMA temp_store = MEMORY;"
                "PRAGMA cache_size = 2000;"
                "PRAGMA mmap_size = 134217728;"
            ),
        },
    }
}

drift

In the database class, add the PRAGMA statements inside the beforeOpen callback of the migration strategy:

class PtdDatabase extends _$PtdDatabase {

  // Irrelevant code omitted

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      beforeOpen: (details) async {
        // Executed every time before the database becomes available
        await customStatement('PRAGMA journal_mode = WAL');
        await customStatement('PRAGMA synchronous = NORMAL');
        await customStatement('PRAGMA busy_timeout = 5000');
        await customStatement('PRAGMA temp_store = MEMORY');
        await customStatement('PRAGMA cache_size = -2000');
      },
    );
  }
}