SQLite’s perceived limits are often about practical performance, not hard ceilings.
Let’s see how a typical SQLite setup handles a moderately sized dataset, say 10 million rows in a table with a few indexes.
-- Create a table with some data types
CREATE TABLE events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
timestamp DATETIME NOT NULL,
event_type TEXT NOT NULL,
user_id INTEGER,
payload BLOB
);
-- Create indexes for common queries
CREATE INDEX idx_events_timestamp ON events (timestamp);
CREATE INDEX idx_events_event_type ON events (event_type);
CREATE INDEX idx_events_user_id ON events (user_id);
-- Populate with 10 million rows (this will take a while)
-- For demonstration, imagine this is already done.
-- INSERT INTO events (timestamp, event_type, user_id, payload) VALUES (datetime('now', '-1 day', '+' || (RANDOM() % 86400) || ' seconds'), 'login', (RANDOM() % 1000000), randomblob(100));
Now, let’s query it. A simple SELECT COUNT(*) might be surprisingly fast if the event_type index is used. But a query involving a LIKE operator on event_type without a matching index, or a JOIN across multiple large tables, starts to show SQLite’s strain.
The core problem isn’t usually a hard limit on the number of rows or the database file size (which is 281 TB in theory). It’s about how SQLite’s architecture, designed for simplicity and embedded use, handles concurrency, complex queries, and large data volumes efficiently. Each connection effectively gets a lock on the entire database file for writes, and read operations can block writes. This serialization is a killer for high-throughput applications.
The main levers you control in SQLite are schema design, indexing, and query optimization. You can use EXPLAIN QUERY PLAN to understand how SQLite executes your queries. For instance, if you see a SCAN TABLE where you expect an SEARCH USING INDEX, your indexing strategy needs work.
EXPLAIN QUERY PLAN SELECT * FROM events WHERE event_type = 'login' AND timestamp BETWEEN '2023-10-27 00:00:00' AND '2023-10-27 23:59:59';
The output of this EXPLAIN QUERY PLAN will tell you if it’s using an index or doing a full table scan. If it’s scanning, you need to add or refine indexes. For example, a composite index on (event_type, timestamp) would likely be more effective for that specific query.
DROP INDEX IF EXISTS idx_events_event_type;
CREATE INDEX idx_events_event_type_timestamp ON events (event_type, timestamp);
The most surprising true thing about SQLite’s performance is how much it relies on the operating system’s file system caching. When your dataset fits entirely within the OS cache, SQLite can feel incredibly fast. But once it has to hit disk regularly, performance degrades rapidly, and the simple locking mechanism becomes a bottleneck. This means that even if your database file is only 5GB, if your RAM is only 4GB, you’re going to feel the pain on disk I/O.
When SQLite hits its practical limits for your use case, you’ll start seeing slow query times, increased application latency, and eventually, write contention errors or timeouts under load.
The next step in understanding is how to effectively migrate that data to a more robust system like PostgreSQL, which handles concurrency, indexing, and query complexity at a different scale.