agentsclimarketplace

Sqlite optimization

Skill jrajasekera/jr-agent-skills/skills/sqlite-optimization

Optimize SQLite database performance through configuration, schema design, indexing, and query tuning. Use when users ask to improve SQLite speed, reduce latency, optimize queries, configure PRAGMAs, fix slow queries, handle concurrency, optimize writes/inserts, or tune SQLite for production. Triggers on mentions of SQLite performance, slow queries, PRAGMA settings, WAL mode, indexing strategies, bulk inserts, or database maintenance (VACUUM, ANALYZE).From its SKILL.md

Install
npx -y skills add jrajasekera/jr-agent-skills --skill sqlite-optimization

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

2 things to look at

  • no licenseNo license file was found in the repository. Code published without one is not open source by default, so using it at work is a question for whoever answers licensing questions where you are.
  • 1 stars1 stars. Stars are a popularity signal and not a quality one, but at this level it is likely that nobody has read this closely except its author, and you would be relying on your own review.

SKILL.md

5.3 KB, as published. Nobody here has run it

SQLite Performance Optimization

Quick Start: Baseline Configuration

Set these PRAGMAs on every connection for most applications:

PRAGMA journal_mode = WAL;          -- Non-blocking concurrent reads/writes
PRAGMA synchronous = NORMAL;        -- Fast commits, safe for WAL mode
PRAGMA foreign_keys = ON;           -- Data integrity
PRAGMA cache_size = -64000;         -- 64MB cache (negative = KB)
PRAGMA temp_store = MEMORY;         -- In-memory temp tables/sorting
PRAGMA busy_timeout = 5000;         -- 5s retry on lock contention
PRAGMA mmap_size = 2147483648;      -- 2GB memory-mapped I/O (benchmark first)

Run periodically:

PRAGMA optimize;  -- Update query planner statistics

Optimization Workflow

1. Diagnose with EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN SELECT ...;

Key indicators:

  • SCAN TABLE → Full table scan (O(N)) — needs index or query rewrite
  • SEARCH TABLE USING INDEX → Index lookup (O(log N)) — good
  • COVERING INDEX → Index-only scan, no table lookup — optimal
  • USE TEMP B-TREE → Sorting not covered by index — consider index with ORDER BY columns

2. Schema Design

Use INTEGER PRIMARY KEY for rowid alias:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,  -- Alias for rowid, no extra index
    email TEXT NOT NULL,
    created_at TEXT
);

Choose appropriate types:

  • INTEGER for IDs, counters, booleans
  • REAL for floats
  • TEXT for strings
  • Avoid large BLOBs in hot tables; use separate table or external files

WITHOUT ROWID for junction tables:

CREATE TABLE user_roles (
    user_id INTEGER,
    role_id INTEGER,
    PRIMARY KEY (user_id, role_id)
) WITHOUT ROWID;  -- Clustered index, no rowid overhead

3. Indexing Strategy

Create indexes for real query patterns:

-- For: WHERE user_id = ? AND created_at >= ? ORDER BY created_at DESC
CREATE INDEX idx_orders_user_created 
ON orders (user_id, created_at DESC);

Covering indexes eliminate table lookups:

-- For: SELECT user_id, total FROM orders WHERE user_id = ?
CREATE INDEX idx_orders_cover 
ON orders (user_id, created_at DESC, total);

Partial indexes for subsets:

CREATE INDEX idx_active_users 
ON users (email) WHERE status = 'active';

Expression indexes:

CREATE INDEX idx_users_lower_email 
ON users (lower(email));

Avoid indexing:

  • Small tables (< hundreds of rows)
  • Low-cardinality columns alone (booleans)
  • Columns rarely in WHERE/JOIN/ORDER BY

4. Write Optimization

Always batch writes in transactions:

BEGIN TRANSACTION;
INSERT INTO logs (...) VALUES (...);
-- ... many more inserts
COMMIT;

Throughput jumps from ~50 inserts/sec (autocommit) to 50,000+ inserts/sec.

Use prepared statements:

stmt = conn.prepare("INSERT INTO logs (ts, level, msg) VALUES (?, ?, ?)")
for row in data:
    stmt.execute(row)

Optimal batch size: 1,000–10,000 rows balances throughput vs memory.

5. Concurrency Configuration

WAL mode enables concurrent readers + one writer:

  • Readers don't block writers
  • Writers don't block readers
  • Uses .wal and .shm files alongside main DB

Handle contention:

PRAGMA busy_timeout = 5000;  -- Retry for 5 seconds before SQLITE_BUSY

Architecture pattern: Single writer thread/process + many reader connections.

6. Maintenance

VACUUM after large deletes:

VACUUM;  -- Rebuilds and defragments DB file
  • Run during maintenance windows (blocking operation)
  • Requires free disk space ≈ DB size

Auto-vacuum for incremental reclaim:

PRAGMA auto_vacuum = INCREMENTAL;
VACUUM;  -- Apply change
-- Then periodically:
PRAGMA incremental_vacuum;

Keep statistics fresh:

PRAGMA optimize;  -- Smart, selective ANALYZE
-- Or full:
ANALYZE;

Reference Files

Common Anti-Patterns

ProblemSolution
SELECT *List only needed columns
Loop with single insertsBatch in transaction
Correlated subquery per rowRewrite as JOIN
Index on every columnIndex only queried columns
No busy_timeoutSet appropriate timeout
Rollback journal modeSwitch to WAL
Never running ANALYZERun PRAGMA optimize periodically

Environment-Specific Notes

Mobile (iOS/Android):

  • WAL + synchronous = NORMAL
  • Background maintenance jobs for VACUUM
  • Local storage only (not SD card)

Server/Desktop:

  • Centralize writes in single thread if possible
  • Set PRAGMAs immediately after connection open
  • Consider connection pooling with writer serialization

Keep looking

Skills are one crate of 608,475. Ordering is by how many stacks a row turns up in, so the top of any crate is what has actually been picked rather than what has the most stars.