SQLite Performance Tips for Web Applications

SQLite is increasingly used in production web applications, but its performance characteristics are different from client-server databases. Here are the optimization techniques I've learned running SQLite in production at DailyWatch.

Essential PRAGMAs

These should be set every time you open a connection:

$db = new PDO('sqlite:data/videos.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Critical PRAGMAs for web applications
$db->exec('PRAGMA journal_mode = WAL');         // Write-Ahead Logging
$db->exec('PRAGMA synchronous = NORMAL');       // Safe balance of speed/durability
$db->exec('PRAGMA cache_size = -20000');        // 20MB page cache
$db->exec('PRAGMA busy_timeout = 5000');        // Wait 5s for locks
$db->exec('PRAGMA foreign_keys = ON');          // Enforce FK constraints
$db->exec('PRAGMA temp_store = MEMORY');        // Temp tables in RAM
$db->exec('PRAGMA mmap_size = 268435456');      // 256MB memory-mapped I/O

WAL Mode: The Single Most Important Setting

WAL (Write-Ahead Logging) mode is the difference between "SQLite can't handle concurrent access" and "SQLite handles concurrent access just fine."

Without WAL: writers block readers, readers block writers. One cron job writing data means all web requests wait.

With WAL: readers and writers operate concurrently. Your cron job can insert data while web requests read without contention.

PRAGMA journal_mode = WAL;
-- This only needs to be set once; it persists across connections

Index Strategy

SQLite's query planner is simpler than PostgreSQL's. You need to be explicit about indexes:

-- Good: composite index matching your query patterns
CREATE INDEX idx_videos_cat_fetched
    ON videos(category_id, fetched_at DESC);

-- Good: covering index for frequently accessed columns
CREATE INDEX idx_videos_listing
    ON videos(category_id, fetched_at DESC)
    INCLUDE (title, thumbnail_url, channel_title, view_count);

-- Check if your indexes are being used
EXPLAIN QUERY PLAN
    SELECT title, thumbnail_url FROM videos
    WHERE category_id = 10
    ORDER BY fetched_at DESC LIMIT 20;

Batch Writes in Transactions

Individual INSERTs in SQLite are slow because each one triggers a filesystem sync. Wrapping them in a transaction makes batch inserts 50-100x faster:

// Slow: 500ms for 100 inserts
foreach ($videos as $video) {
    $stmt->execute([...]);
}

// Fast: 5ms for 100 inserts
$db->beginTransaction();
foreach ($videos as $video) {
    $stmt->execute([...]);
}
$db->commit();

Query Optimization

Avoid the N+1 problem. Instead of fetching videos then looping to get regions:

// Bad: N+1 queries
$videos = $db->query('SELECT * FROM videos LIMIT 20')->fetchAll();
foreach ($videos as &$video) {
    $video['regions'] = $db->query(
        "SELECT region FROM video_regions WHERE video_id = '{$video['video_id']}'"
    )->fetchAll();
}

// Good: single query with GROUP_CONCAT
$videos = $db->query('
    SELECT v.*, GROUP_CONCAT(vr.region) as regions
    FROM videos v
    LEFT JOIN video_regions vr ON v.video_id = vr.video_id
    GROUP BY v.video_id
    ORDER BY v.fetched_at DESC
    LIMIT 20
')->fetchAll();

Connection Handling

SQLite doesn't have a connection pool because there's no server. But you should still reuse connections within a request:

class Database {
    private static ?PDO $instance = null;

    public static function get(): PDO {
        if (self::$instance === null) {
            self::$instance = new PDO('sqlite:data/videos.db');
            self::$instance->exec('PRAGMA journal_mode = WAL');
            self::$instance->exec('PRAGMA synchronous = NORMAL');
            self::$instance->exec('PRAGMA cache_size = -20000');
            self::$instance->exec('PRAGMA busy_timeout = 5000');
        }
        return self::$instance;
    }
}

Database Maintenance

Run periodic maintenance to keep performance stable:

// Weekly maintenance (run via cron)
$db->exec('PRAGMA optimize');    // Update query planner statistics
$db->exec('PRAGMA wal_checkpoint(TRUNCATE)'); // Clean WAL file

// Monthly
$db->exec('VACUUM');             // Rebuild database file (reclaim space)
$db->exec('ANALYZE');            // Update all index statistics

Benchmarks from Production

Real numbers from dailywatch.video with ~15,000 video records:

Query	Time
SELECT by ID	0.1ms
Category listing (20 results)	0.3ms
FTS5 search	2-5ms
Home page aggregation	1.2ms
Full table count	0.5ms

These are after applying all the optimizations above. Before optimization, the same queries ranged from 5-50ms.