SQLITE_TOOBIGERRORTier 1 — Safe✅ HIGH confidence

string or blob too big

Category: Resource LimitVersions: All SQLite versions

What this means

SQLITE_TOOBIG (result code 18) is returned when a string or BLOB value exceeds the compile-time or runtime maximum allowed size. The default maximum is 1 billion bytes (1 GB) per value, but the sqlite3_limit() API or the SQLITE_MAX_LENGTH compile option can lower this. SQLite is not intended as a store for multi-gigabyte binary objects.

Why it happens

1Inserting a BLOB or TEXT value larger than SQLITE_MAX_LENGTH (default 1,000,000,000 bytes)
2A SQL expression produces an intermediate string result that exceeds the limit
3The application lowered the per-connection limit via sqlite3_limit(SQLITE_LIMIT_LENGTH) and then tried to insert a value that previously fit

How to reproduce

A BLOB larger than the configured limit is inserted.

trigger — this will ERROR

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE t (data BLOB)')

# Default limit is 1 GB; lower it for demonstration
conn.execute('SELECT sqlite_compileoption_used("MAX_LENGTH")')

# Generate a 2-byte-over-limit blob (in practice use actual limit)
big = b'x' * (1_000_000_001)  # > 1 GB
conn.execute('INSERT INTO t VALUES (?)', (big,))  # triggers SQLITE_TOOBIG

sqlite3.OperationalError: string or blob too big

Fix 1: Store large objects on the filesystem and store only the path in SQLite

When the value genuinely needs to be large (images, documents, videos).

fix

import sqlite3, pathlib

# Store the file on disk
pathlib.Path('/data/uploads/file.bin').write_bytes(large_data)

# Store only the reference in SQLite
conn = sqlite3.connect('app.db')
conn.execute('CREATE TABLE files (id INTEGER PRIMARY KEY, path TEXT)')
conn.execute("INSERT INTO files (path) VALUES ('/data/uploads/file.bin')")
conn.commit()

Why this works

SQLite is optimised for structured relational data. Large binary objects are better served by a filesystem or object store, with SQLite holding metadata and file paths. This also avoids page fragmentation from large overflow pages.

What not to do

✗

Raise SQLITE_MAX_LENGTH to multi-gigabyte values

Why it's wrong: Very large values cause severe page fragmentation and slow all operations on the table. SQLite's B-tree structure is not efficient for row sizes that exceed a page.

Version notes

All versionsThe default SQLITE_MAX_LENGTH compile-time limit is 1,000,000,000 bytes. The absolute maximum is 2,147,483,647 bytes (2 GiB - 1).

Sources

📚 Official docs: https://www.sqlite.org/rescode.html#toobig

🔧 Source ref: sqlite3.h — SQLITE_TOOBIG = 18

📖 Further reading: SQLite limits documentation

Confidence assessment

✅ HIGH confidence

Stable. Limits are well documented and consistent across builds unless explicitly overridden at compile time.