sqlite3 CLI: .schema, EXPLAIN QUERY PLAN, ANALYZE

The sqlite3 CLI is the smallest honest interface to a production database. It speaks the same SQL as your app, it opens the same file, and it will not hide a missing index behind an ORM log line. On a VPS you should be able to SSH in, open the file read-only, and answer three questions: what is the schema, how will this query run, and are the statistics current.

Install the CLI from the distro package, not from a random static binary, so it matches the library your app links. On Ubuntu that is the sqlite3 package. Then always pass the absolute path you set in APP_DB.

.schema, .tables, and headers

.schema prints the CREATE statements SQLite stored, including indexes and the internal sqlite_sequence table for AUTOINCREMENT. .tables lists names. .headers on and .mode column make interactive SELECT readable. .once writes the next query to a file so you can capture a plan without a terminal mess.

sqlite3 /var/lib/myapp/data/app.db
.tables
.schema events
.headers on
.mode column
SELECT COUNT(*) FROM events;

Open read-only when you are only looking. A forgotten write in a shell session is a real incident.

sqlite3 -readonly /var/lib/myapp/data/app.db ".schema"

.schema with a table name filters to that object. Without a name it dumps everything, which is the fastest way to see a stray index or a leftover events_new from a failed migration. sqlite_master is the table behind .schema; a SELECT from sqlite_master WHERE type = 'index' is useful when you want names only.

EXPLAIN QUERY PLAN before you add hardware

When a page is slow, do not add RAM first. Ask the planner. EXPLAIN QUERY PLAN shows whether SQLite will use an index, scan the whole table, or run a covering index. It does not run the query. EXPLAIN (without QUERY PLAN) shows the virtual machine opcodes, which is more than you want on a first pass.

EXPLAIN QUERY PLAN
SELECT id, kind, created_at
FROM events
WHERE account_id = 42
ORDER BY created_at DESC
LIMIT 20;

A healthy answer looks like SEARCH events USING INDEX events_account_created (account_id=?). A bad answer looks like SCAN events. SCAN is acceptable on a 200-row table. SCAN on a 20 million row table is the outage.

Read the plan like a checklist:

• SEARCH plus USING INDEX plus the equality column you expected: good
• SCAN plus USE TEMP B-TREE FOR ORDER BY: you filter or sort on a column the index does not cover
• COVERING INDEX: the index holds every column the query needs, so SQLite will not visit the table
• LIST SUBQUERY or CORRELATED: you may want a JOIN rewrite

The planner uses statistics from ANALYZE. Stale stats after a bulk load will make a good index look unused. When the plan is wrong and the index exists, run ANALYZE before you invent a new index.

ANALYZE and sqlite_stat1

ANALYZE samples tables and indexes and writes sqlite_stat1 (and sometimes sqlite_stat4). The planner reads those rows to estimate selectivity. After a migration that backfills a column, or after deleting a large fraction of rows, ANALYZE is mandatory. It is cheap on typical VPS databases.

ANALYZE;
SELECT * FROM sqlite_stat1;

You should see one row per index, with a stat string that starts with the table row count. If sqlite_stat1 is empty, the planner is guessing. PRAGMA optimize is a lighter alternative the app can run on shutdown; it decides whether ANALYZE is worth it. For a weekly maintenance window, ANALYZE is simpler.

Do not run VACUUM because a plan is slow. VACUUM rewrites the whole file, rebuilds indexes, and needs extra disk. It is a compaction tool, not a tuner. EXPLAIN QUERY PLAN, then a matching index, then ANALYZE, in that order.

Keep a short runbook next to the unit file: how to open read-only, how to print the schema, how to paste a slow query into EXPLAIN QUERY PLAN, how to run ANALYZE. That runbook is worth more than a dashboard you will not look at during an incident.

Open the live file read-only, print the schema, explain the plan, then ANALYZE. You can spin up an Ubuntu 24.04 instance on Netbay in under 60 seconds and follow along — netbayhosts.in.