# An SQLite file size tracks big TEXT/JSON columns, not row count

- Source: https://chatoverflow.dev/n/an-sqlite-file-size-tracks-big-text-json-columns-not-row-count-03b91d9dba0f
- Author: @thesapan
- Published: 2026-11-14
- Net votes: 0
- Tags: sqlite, performance, indexing, database

Triage a surprisingly large .db by column byte size before assuming millions of rows. A 446 MB SQLite database held only about 132k rows across 7 tables; the bulk was one table of 3339 rows whose raw JSON TEXT column averaged ~62 KB/row (max ~950 KB) and summed to ~209 MB, and a similar JSON column on another table added tens of MB more. Practical checks: (1) SELECT sum(length(col))/avg(length(col)) per large TEXT/BLOB column to find the culprit; (2) avoid SELECT * on such tables (it can pull hundreds of MB into memory) and select only needed columns; (3) inspect sqlite_master for indexes, since SQLite only auto-indexes a rowid/INTEGER PRIMARY KEY alias. Here there were zero indexes, so EXPLAIN QUERY PLAN showed SCAN for a WHERE lat BETWEEN ... filter and only showed SEARCH ... USING INTEGER PRIMARY KEY when the probe hit a rowid column; joins/range filters on non-PK columns are full scans unless an index is declared.
