Which SQLite size limits can a phone app storing chat history actually hit, and what error does each one raise?
answer
- compile-time maximums
- a billion bytes, row included
- 2000 columns
- host parameters: 999, then 32766
- page size times page count
basics
~20 sSQLite caps a string, a BLOB and a whole row at 1,000,000,000 bytes (SQLITE_TOOBIG), a table at 2000 columns, a statement at 32766 host parameters since 3.32.0, and the file at page size times max_page_count (SQLITE_FULL).
solid answer
~50 sSQLite's limits are compile-time maximums; `sqlite3_limit()` can lower one per connection but never raise it past the build's value. `SQLITE_MAX_LENGTH` defaults to 1,000,000,000 bytes and bounds one string or BLOB and also a whole row, because a row is encoded as one BLOB while it is written and read; crossing it gives `SQLITE_TOOBIG`, `string or blob too big`. `SQLITE_MAX_COLUMN` defaults to 2000. `SQLITE_MAX_VARIABLE_NUMBER` is 32766 since 3.32.0 and was 999 before, so a large multi-row `INSERT` or `IN` list fails with `too many SQL variables` on an older library. The file can grow to page size times `max_page_count`, at most 4294967294 pages, which is also the default since 3.45.0; past that an insert gets `SQLITE_FULL`, the same code as a full disk. On a phone the disk usually fills first, and large attachments belong in files.
code
sql · 4 linesPRAGMA page_size; -- 4096 on a default build
PRAGMA max_page_count; -- 4294967294 on 3.45.0 and later
PRAGMA max_page_count = 25600; -- this connection only: 25600 x 4096 bytes = 100 MiB
SELECT sqlite_version();go deeper
Recall that SQLite has fixed limits: a billion bytes per value and per row, 2000 columns, and a file size set by page size times page count.
Explain that limits are compile-time maximums sqlite3_limit can lower but never exceed, why a whole row counts against SQLITE_MAX_LENGTH, and which error each limit raises.
Show production judgment: size batches for the oldest library in the field, keep large media in files, and handle SQLITE_FULL and SQLITE_TOOBIG as expected, recoverable errors.
Weigh lowering limits on purpose: a capped max_page_count bounds a cache's footprint and tight limits blunt hostile SQL, at the price of new failure paths the app must handle.
## Limits are compile-time numbers Since about release 3.5.8, SQLite has had well-defined limits that its test suite exercises. Each one is a C preprocessor macro named `SQLITE_MAX_*`, fixed when the library is compiled. At run time, `sqlite3_limit(db, SQLITE_LIMIT_..., n)` sets a value for one connection, but only up to the compiled value: that macro is a hard upper bound. Two consequences follow: - **The numbers below are the source defaults.** A library someone else compiled may use different values, so ask the connection rather than assume. `sqlite3_limit()` called with a negative value changes nothing and returns the current setting; the `sqlite3` command-line tool prints them with `.limit`. - **Lowering a limit is a defence.** An app that runs SQL from an untrusted source can cap string length, column count or expression depth on that connection so a hostile statement cannot exhaust memory. ## The limits a chat app can reach | Limit | Default | What it bounds | What you see when you cross it | |---|---|---|---| | `SQLITE_MAX_LENGTH` | 1,000,000,000 bytes | one string or BLOB, and one whole row | `SQLITE_TOOBIG`, `string or blob too big` | | `SQLITE_MAX_COLUMN` | 2000 | columns in a table, index, view or result set; terms in `SET`, `GROUP BY` and `ORDER BY`; values in an `INSERT` | `too many columns on message`, or `too many columns in result set` | | `SQLITE_MAX_VARIABLE_NUMBER` | 32766 (999 before 3.32.0) | the largest host-parameter number in one statement | `too many SQL variables` | | `SQLITE_MAX_SQL_LENGTH` | 1,000,000,000 bytes | the text of one SQL statement | `SQLITE_TOOBIG`, `statement too long` | | `SQLITE_MAX_PAGE_COUNT` and `PRAGMA max_page_count` | 4294967294 pages (the default since 3.45.0) | the size of the database file, together with the page size | `SQLITE_FULL`, `database or disk is full` | A few others rarely matter on a phone: at most 64 tables in one join (fixed, not configurable), 10 attached databases by default (at most 125), an expression depth of 1000, 500 terms in a compound `SELECT`, and 1000 function arguments since 3.48.0. ## Why a row, not only a value, hits the length cap During parts of `INSERT` and `SELECT` processing, SQLite encodes the complete content of a row as a single BLOB. `SQLITE_MAX_LENGTH` therefore bounds the whole row, not just each column. Two 600 MB attachments in one row fail even though each is under a billion bytes. The implementation also cannot go past 2147483645 bytes for one value whatever the macro says, and some built-in functions such as `hex()` fail well before that. Size is not the only cost. Reading a BLOB column through an ordinary `SELECT` loads the whole value into memory unless you use incremental BLOB I/O (`sqlite3_blob_open()`). For a chat app: 1. Store photos, voice notes and videos as files, and keep the path, size and hash in the row. 2. If media must live in the database, split it into chunks in a child table. 3. Bind large values with `?` parameters instead of pasting them into SQL text, which keeps statements far below `SQLITE_MAX_SQL_LENGTH` and avoids escaping. ## Host parameters and older libraries Syncing a batch of messages often means a multi-row statement such as `INSERT INTO message(chat_id, sender, body) VALUES (?,?,?), (?,?,?), ...`, three parameters per row. On a library older than 3.32.0 the cap of 999 allows 333 rows per statement; on a current one, 32766 allows 10922. A large `IN (?, ?, ...)` list of message ids meets the same wall. The error is a plain `SQLITE_ERROR` with the message `too many SQL variables`, raised when the statement is prepared. Two fixes: - Size each batch for the oldest library the app runs on, or for the value `sqlite3_limit()` reports. - Reuse one prepared single-row `INSERT` inside one transaction, which has no parameter-count problem at all. ## How big the file can get The maximum database size is the page size times the maximum page count. At the default 4096-byte page and 4294967294 pages that is about 17.5 TB; with 65536-byte pages, about 281 TB. Before 3.45.0 the default page-count cap was 1073741824 pages, about 4.4 TB at 4096 bytes. `PRAGMA max_page_count = N` lowers the cap for the current connection; it is not stored in the file and cannot go below the file's current size. That makes it a cheap way to bound a cache database. When a write would grow the file past the cap, SQLite returns `SQLITE_FULL`, `database or disk is full`. That is the same code a full disk produces, and on a phone it is the one you will actually meet. ## What to do in an app - Treat `SQLITE_FULL` and `SQLITE_TOOBIG` as expected errors with a user-facing path, such as freeing space or skipping an oversized attachment, not as crashes. - Check limits on the library you actually link, not on the documentation's defaults. - Keep schemas far below 2000 columns; very wide tables also make statement preparation slow.
- Why does a SQLite library built by someone else sometimes allow more or fewer host parameters than 32766?Because 32766 is only the source default of `SQLITE_MAX_VARIABLE_NUMBER`; whoever compiles the library may choose another value, and your own code may have lowered it with `sqlite3_limit()`. Ask the connection with `sqlite3_limit(db, SQLITE_LIMIT_VARIABLE_NUMBER, -1)`, which returns the current value without changing it, and size batches from that.
- Can you raise SQLITE_MAX_LENGTH to store a 3 GB video in one SQLite BLOB?No. Whatever the macro says, the implementation supports a string or BLOB of at most 2147483645 bytes, and some built-in functions fail well before that. SQLite's documentation advises against raising the limit in security-sensitive applications. Store the video as a file and keep its path, size and hash in the row.
- SQLite has no practical size limits, so any BLOB will fit.
- SQLITE_MAX_LENGTH limits single values only, never a whole row.
- sqlite3_limit() can raise a limit above the compiled maximum.
- Every SQLite build allows 32766 host parameters in a statement.
- SQLITE_FULL always means the phone's storage is full.
- A SQLite row must fit inside a single database page.
Assumes SQLite 3.53; the host-parameter default rose from 999 to 32766 in 3.32.0 and the default page-count cap from 1073741824 to 4294967294 pages in 3.45.0, so an older bundled library stops sooner.