Performance Anti-Patterns and When SQLite Has Reached the Wrong Workload

Learning outcomes

Performance work is complete only when it can say do not tune SQLite here. A slow system may spend its time in N+1 application loops, duplicate indexes, JSON traversal, long transactions, blocked writers, remote filesystems, serialization, or simply a workload that wants a client/server or analytical engine. This final lesson turns measurements into a prioritized engineering report.

Anti-pattern 1: N+1 queries

An N+1 pattern fetches a parent list, then runs one additional query for every parent. The individual statements can each be “fast” while the application performs hundreds of prepare/step/map cycles.

-- First querySELECT device_id, device_code FROM device WHERE status='active';-- Then the application repeats this once per returned device:SELECT occurred_at, severity, note_textFROM maintenance_noteWHERE device_id=?ORDER BY occurred_at DESCLIMIT 1;

Measure statement count per user operation, not only statement duration. A window query, correlated subquery, join/CTE, or explicit batched IN request may reduce round trips/driver overhead depending on the exact result shape.

WITH ranked AS (  SELECT n.*,         row_number() OVER (           PARTITION BY device_id ORDER BY occurred_at DESC, note_id DESC         ) AS rn  FROM maintenance_note AS n)SELECT d.device_id, d.device_code,       r.occurred_at, r.severity, r.note_textFROM device AS dLEFT JOIN ranked AS r  ON r.device_id=d.device_id AND r.rn=1WHERE d.status='active';

Anti-pattern 2: missing, redundant, or oversized indexes

A missing index can force a large scan; too many overlapping indexes increase every INSERT/UPDATE/DELETE and consume file/cache space. Chapter 10 taught planner evidence. A performance review should list every persistent index beside the queries that justify it.

SELECT name, tbl_name, sqlFROM sqlite_schemaWHERE type='index'ORDER BY tbl_name, name;PRAGMA index_list('maintenance_note');PRAGMA index_xinfo('idx_note_device_time');EXPLAIN QUERY PLANSELECT note_id, occurred_at, severityFROM maintenance_noteWHERE device_id=?ORDER BY occurred_at DESC LIMIT 25;

Anti-pattern 3: SELECT * and unnecessary work

SELECT * is not automatically slow, but it can force SQLite and the driver to read, decode, allocate, and copy columns the caller never uses—especially wide TEXT/BLOB/JSON payloads. It can also prevent a smaller covering index from satisfying the query.

-- UI list only needs these columns:SELECT note_id, occurred_at, severityFROM maintenance_noteWHERE device_id=?ORDER BY occurred_at DESCLIMIT 50;-- Compare with SELECT * only if the UI genuinely needs every column.-- Capture plan, bytes/row if relevant, and end-to-end mapping time.

Likewise, an ORDER BY or DISTINCT that exists only because “the old query had it” can create a temporary b-tree. Use EXPLAIN QUERY PLAN to find USE TEMP B-TREE, then decide whether the order is semantically required and whether an index can support it.

Anti-pattern 4: huge transactions and chatty transactions

Both extremes can hurt. A transaction that pauses for user input or HTTP calls keeps database resources occupied for no database reason. A huge batch can monopolize the single writer and make rollback/retry expensive. Thousands of tiny commits can spend most time on durability boundaries.

Anti-pattern 5: unbounded JSON traversal

JSON is useful for flexible metadata, but repeatedly traversing a large JSON document for a field used in hot predicates hides schema and indexability. If a path becomes operationally important, promote it to a real/generated column or an expression index with validated query matching—as Chapter 13 demonstrated.

EXPLAIN QUERY PLANSELECT device_idFROM device_profileWHERE json_extract(metadata, '$.protocol')='modbus';-- If this is a hot stable property, compare against:-- 1) indexed generated column-- 2) expression index with exact expression matching-- 3) promoted ordinary relational column-- Measure write/storage cost too.

Anti-pattern 6: long readers and write contention

WAL improves reader/writer overlap, but a long-lived read transaction can pin an old WAL end mark and prevent checkpoints from completing. Multiple writers still serialize. Diagnose with transaction lifetime and checkpoint/busy evidence, not by repeatedly increasing busy_timeout.

For each SQLITE_BUSY incident:  timestamp  operation + transaction start time  connection/process identity  BEGIN mode  journal_mode + synchronous  busy timeout/retry policy  competing writer duration  long reader duration (WAL)  WAL size/checkpoint result  final success/failure + retry count

Storage placement can dominate SQL

SQLite is an embedded library coordinating a database file through its VFS and filesystem locking primitives. High-latency or semantically unsuitable network filesystems can turn ordinary page/journal operations into slow or unreliable behavior. Do not compensate with giant caches or timeouts until the deployment filesystem is explicitly supported and tested.

When another database is the optimization

SQLite remains excellent for local application state, edge/desktop/mobile storage, caches, configuration, embedded services, test fixtures, and many moderate workloads. Reassessment becomes reasonable when the product requires many concurrent writers, centralized remote clients, server-managed authorization, replicas/failover, or analytics that are naturally columnar/distributed.

Mini performance review: FieldNotes

Workload: FieldNotes local/edge databaseEvidence collected:  [ ] top 10 operations by total DB time, not only average latency  [ ] statement count per operation (find N+1/chatty loops)  [ ] EXPLAIN QUERY PLAN for hot queries  [ ] index inventory + write rate  [ ] transaction duration + commits/sec + busy/retries  [ ] WAL size/checkpoint + long-reader duration  [ ] database size/page_count/freelist_count  [ ] JSON hot-path queries  [ ] sqlite_version/source_id/compile_options + PRAGMAs  [ ] host filesystem/storage contextPrioritize each finding:  P0 correctness/durability risk  P1 high total latency/throughput bottleneck  P2 measurable efficiency/storage issue  P3 speculative idea -- DO NOT implement without evidence

Example prioritized tuning report

Chapter synthesis

Bridge to security and deployment

Performance settings are inseparable from reliability and deployment. Chapter 19 moves to trust boundaries: SQL injection, file permissions, encryption reality, trusted_schema, extension loading, local versus network filesystems, and the final decision about when SQLite is—and is not—the right database.

Authoritative references

• SQLite PRAGMA reference
• SQLite EXPLAIN QUERY PLAN
• SQLite WAL
• SQLite temporary files
• SQLite appropriate uses