---
title: "SQLite for Production Use"
description: "Reference for running SQLite in production — WAL mode, connection pooling strategies, backup approaches, VACUUM, performance tuning (page size, cache size, mmap), Litestream replication, and Cloudflare D1 as managed SQLite."
url: https://agent-zone.ai/knowledge/databases/sqlite-operations/
section: knowledge
date: 2026-02-22
categories: ["databases"]
tags: ["sqlite","wal-mode","litestream","d1","backup","performance-tuning","connection-pooling","mmap"]
skills: ["sqlite-administration","database-performance-tuning","backup-strategy","edge-databases"]
tools: ["sqlite3","litestream","wrangler","litefs"]
levels: ["intermediate"]
word_count: 712
formats:
  json: https://agent-zone.ai/knowledge/databases/sqlite-operations/index.json
  html: https://agent-zone.ai/knowledge/databases/sqlite-operations/?format=html
  api: https://api.agent-zone.ai/api/v1/knowledge/search?q=SQLite+for+Production+Use
---


# SQLite for Production Use

SQLite is not a toy database. It handles more read traffic than any other database engine in the world -- every Android phone, iOS device, and major web browser runs SQLite. The question is whether your workload fits its concurrency model: single-writer, multiple-reader. If it does, SQLite eliminates an entire class of operational overhead with no server process, no network protocol, and no connection authentication.

## WAL Mode

Write-Ahead Logging (WAL) mode is the single most important configuration for production SQLite. In the default rollback journal mode, writers block readers and readers block writers. WAL removes this limitation.

```sql
PRAGMA journal_mode=WAL;
```

WAL creates two additional files: `database.db-wal` (the write-ahead log) and `database.db-shm` (shared memory). Writes append to the WAL instead of modifying the database file. Readers see a consistent snapshot as of when their transaction started. Periodically, the WAL is checkpointed back to the main database file.

```sql
PRAGMA wal_autocheckpoint=1000;       -- checkpoint at 1000 pages (default)
PRAGMA wal_checkpoint(TRUNCATE);      -- manual checkpoint, resets WAL to zero length
```

**Caveat:** WAL does not work over network filesystems (NFS, SMB). The shared memory file requires OS-level primitives that network filesystems do not support. Move the database to local storage.

## Connection Pooling for Single-Writer

SQLite allows only one writer at a time. The recommended pattern is a two-pool approach -- one dedicated write connection serialized with an application-level lock, and multiple read connections:

```python
import sqlite3
from contextlib import contextmanager
from threading import Lock

class SQLitePool:
    def __init__(self, db_path, read_pool_size=4):
        self._write_lock = Lock()
        self._write_conn = sqlite3.connect(db_path)
        self._write_conn.execute("PRAGMA journal_mode=WAL")
        self._write_conn.execute("PRAGMA busy_timeout=5000")
        self._write_conn.execute("PRAGMA synchronous=NORMAL")

        self._read_connections = []
        for _ in range(read_pool_size):
            conn = sqlite3.connect(db_path)
            conn.execute("PRAGMA query_only=ON")
            self._read_connections.append(conn)

    @contextmanager
    def write(self):
        with self._write_lock:
            try:
                yield self._write_conn
                self._write_conn.commit()
            except Exception:
                self._write_conn.rollback()
                raise
```

Always set a busy timeout so writes wait instead of immediately failing with SQLITE_BUSY:

```sql
PRAGMA busy_timeout=5000;  -- wait up to 5 seconds with exponential backoff
```

## Performance Tuning

**Page size:** default is 4096 bytes. Increase to 8192 or 16384 for databases with large rows. Must be set before creating the database or applied with VACUUM:

```sql
PRAGMA page_size=8192;
VACUUM;
```

**Cache size:** the page cache keeps frequently accessed pages in memory. Increase for read-heavy workloads:

```sql
PRAGMA cache_size=-65536;  -- 64MB (negative = kilobytes, positive = pages)
```

**Memory-mapped I/O:** maps the database file into process memory, bypassing user-space page cache. Best for read-heavy workloads on databases that fit in RAM:

```sql
PRAGMA mmap_size=268435456;  -- 256MB
```

**Synchronous mode:** in WAL mode, `NORMAL` provides full durability guarantees. Do not use `OFF` in production.

```sql
PRAGMA synchronous=NORMAL;
```

## VACUUM and Maintenance

SQLite does not reclaim space from deleted rows automatically. A maintenance script:

```bash
#!/bin/bash
DB_PATH="/var/lib/myapp/production.db"

sqlite3 "$DB_PATH" <<'EOF'
PRAGMA integrity_check;
ANALYZE;
PRAGMA wal_checkpoint(TRUNCATE);
PRAGMA optimize;
EOF

sqlite3 "$DB_PATH" "VACUUM;"
```

Full VACUUM requires temporary space equal to the database size and holds an exclusive lock. For large databases (10GB+), use incremental auto-vacuum instead:

```sql
PRAGMA auto_vacuum=INCREMENTAL;
PRAGMA incremental_vacuum(100);  -- reclaim up to 100 pages
```

## Backup Strategies

With WAL mode, you cannot simply copy the `.db` file. Use SQLite's Online Backup API:

```bash
sqlite3 production.db ".backup /backups/production-$(date +%Y%m%d).db"
```

### Litestream for Continuous Replication

Litestream streams WAL changes to S3-compatible storage for near-real-time backup:

```yaml
# /etc/litestream.yml
dbs:
  - path: /var/lib/myapp/production.db
    replicas:
      - type: s3
        bucket: my-backups
        path: production
        region: us-east-1
        retention: 168h
        sync-interval: 1s
```

```bash
# Run as a wrapper -- Litestream starts, then launches your app
litestream replicate -config /etc/litestream.yml -exec "myapp serve"

# Restore from S3
litestream restore -config /etc/litestream.yml /var/lib/myapp/production.db
```

On Kubernetes, run Litestream as an init container (restore on startup) and a sidecar (continuous replication). LiteFS from Fly.io provides an alternative using FUSE for multi-node read replicas.

## Cloudflare D1: Managed SQLite

D1 runs SQLite at Cloudflare's edge with automatic replication and zero infrastructure management:

```typescript
export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const results = await env.DB.prepare(
      "SELECT * FROM articles WHERE category = ?"
    ).bind("databases").all();

    await env.DB.prepare(
      "INSERT INTO page_views (article_id, timestamp) VALUES (?, ?)"
    ).bind(articleId, Date.now()).run();

    return Response.json(results);
  }
};
```

D1 handles WAL mode, replication, and backups automatically. The tradeoff is vendor lock-in to Cloudflare Workers. Excellent for read-heavy edge workloads.

## When SQLite Is the Wrong Choice

Choose a client-server database when multiple application servers need simultaneous write access, you need fine-grained user authentication, write throughput exceeds thousands per second, or you need mature built-in replication with automatic failover. For everything else -- single-server applications, edge deployments, embedded systems, CLI tools, read-heavy web applications -- SQLite eliminates operational complexity that provides no value.