The rule I carried around for local-first apps went like this: keep records in a plain file until there are a few hundred thousand of them, then move to SQLite. A JSON Lines file is easy to read, easy to diff, easy to move, and it needs no library. Why add a database before the file hurts?

I tested that rule and it did not hold up. At 10,000 records, a random lookup in an unindexed JSONL file was already about 126 times slower than the same lookup in SQLite on my test machine. The part of the rule I got right was narrower: for append-only data the file kept up fine.

So the useful question stopped being “how many rows?” and became “what does the application do with the rows?”

What I compared

I wrote the same deterministic records into both stores. Each record has four fields: an integer id, one of 20 category values, an integer value, and a short name.

	Plain JSONL	SQLite
Layout	UTF-8, one compact JSON object per line, no index	records(id INTEGER PRIMARY KEY, category, value, name) plus an index on category
Durability	flush and fsync on every write	WAL mode, synchronous=FULL
Library	Python’s json	Python’s sqlite3

The JSONL side deliberately has no sidecar index. A file with an index next to it is a different architecture, and I come back to it below.

I timed four operations at 10,000, 100,000 and 500,000 initial rows: look up one record by ID, count and sum one category, update one record, and append a batch of 1,000. Lookups used 7 random targets per size, aggregates 3 repeats, updates 5 targets, appends 9 batches. I report medians, and every individual timing is kept in the data.

The environment matters for every number below. It was Python 3.13.5, SQLite 3.46.1, Linux 6.18 on ext4, on an AMD EPYC host, run on 27 August 2026. I did not flush the OS page cache, so these are ordinary warm local reads. Treat the milliseconds as a snapshot from that machine.

One protocol detail came from a failed first attempt. An exploratory run showed me that a JSONL lookup stops when it finds its target, so its latency depends on where in the file the record sits. A single target would have been misleading, which is why the final run uses seven. The exploratory timings were only for debugging and are not in any result here.

SQLite was ahead at 10,000 rows

The chart puts all four operations on one logarithmic axis, so the append panel can be compared honestly against the others. The exact numbers:

Initial rows	Lookup (JSONL / SQLite)	Aggregate	Update	Append 1,000
10,000	7.085 / 0.056 ms	16.533 / 0.255 ms	20.395 / 1.771 ms	5.809 / 4.522 ms
100,000	51.604 / 0.068 ms	130.491 / 2.297 ms	172.379 / 1.124 ms	5.030 / 4.912 ms
500,000	345.602 / 0.079 ms	670.071 / 11.554 ms	828.586 / 1.386 ms	5.105 / 13.849 ms

At 10,000 rows, the SQLite medians were already about 126× lower for lookup, 65× lower for the aggregate and 11.5× lower for a single-record update. At 500,000 rows the lookup gap was about 4,400×. I would not quote that last number to anyone. It is what this workload did on this host, and a faster JSON parser, a different filesystem or a different file layout would move it. What stays put is the shape of the curves.

Why the gap opens so early

The multiplier is a snapshot. The mechanism is something I can state without a benchmark.

An unindexed JSONL file has no way to answer “where is record 346,000?” except by reading lines and parsing JSON until it gets there. My lookup medians grew from 7 ms to 52 ms to 346 ms as the file grew, which is what a scan does. SQLite answers the same question through the primary key, so its median stayed between 0.056 and 0.079 ms across a fifty-fold change in size. SQLite’s query planner documentation describes the table scan versus index search that explains this.

The aggregate follows the same logic. Counting one category out of 20 means parsing every line in JSONL. In SQLite the category index narrows the work.

The update is where the file format starts to cost you in a different way. JSON Lines defines one JSON value per line and says nothing about changing a record in place. My JSONL update rewrites the whole file to a temporary path, calls fsync, and atomically replaces the original. That is a correct way to do it, and it took 829 ms at 500,000 rows. SQLite did the equivalent indexed UPDATE plus a durable commit in about 1.4 ms.

I should be careful about what the update row proves. A log-structured design could append a change record instead of rewriting the file, and updates would get cheap. Reads would then have to merge those changes back in. That is a legitimate design. It is also the first step toward building a database around a text file.

Append was the counterexample

If I had stopped at lookup, aggregate and update, the story would read “SQLite wins everything.” Append would not let me tell it.

Appending 1,000 records took a median of 5.8 ms in JSONL and 4.5 ms in SQLite at 10,000 rows, 5.0 and 4.9 ms at 100,000, and 5.1 and 13.8 ms at 500,000. JSONL was ahead only at the largest size, and the raw timings are noisy. There is a 706 ms first JSONL append at 500,000 rows in the data. I left it in. fsync and commit costs on an ephemeral host jump around, and removing the ugly observation would have made the story neater and less true.

That noise is also why I did not claim an append advantage for JSONL. The exploratory run had tempted me toward “JSONL wins appends.” Locking the protocol and keeping every specified observation supported something narrower: for append-only work, JSONL stayed competitive, and the winner depends on the target storage and durability settings.

SQLite was smaller, which I did not expect

I assumed the database’s pages and index would make it bigger than a compact text file. For this schema it was about 70%, 66% and 65% of the JSONL size at 10,000, 100,000 and 500,000 rows. JSON repeats every field name and bracket on every line, and a table stores the column structure once.

That result is specific to this schema. Compressed JSONL, shorter keys, nested data or more SQLite indexes could reverse it.

The strongest objection

The fair complaint is that I gave JSONL no help. A sidecar index, a change log or a compaction step would make it much faster at lookup and update. I agree, and I did not test that.

I excluded it on purpose, because the index changes what you are comparing. Once you add one, you also own its consistency with the data file, the write ordering between the two, and what happens when the process dies between them. At that point the application is maintaining database machinery, and SQLite already ships that machinery with documented transaction and isolation behaviour and a write-ahead log.

I want to be clear about the limit of that argument. SQLite’s semantics come from its documentation. I did not inject crashes or run concurrent writers, so I have no measurement that SQLite recovers better than a careful journaled file, or handles multiple processes better. SQLite still serializes writers.

How I would decide now

I would choose SQLite as the default primary store when any of these are true:

• Looking up a record by key is on a path the user waits for.
• Filtering or aggregating happens repeatedly, not once in an offline job.
• Existing records get updated or deleted.
• Several related changes should succeed or fail together.
• You can already see a second index coming, or you are about to write a sidecar file to make the first one work.

I would keep JSONL when:

• The data is a log, an event stream or an export that is mostly appended and read top to bottom.
• Being able to open it in any text tool, grep it, or concatenate two files is worth more than query speed.
• It is small enough, or queried rarely enough, that the simpler format wins despite a few milliseconds.

The design in Building Durable Memory Around a Stateless Model leans on the second list: its observation log is append-only, and the version I actually run is two plain-text files.

I would stop and test again instead of choosing either when compressed size matters, when records are deeply nested or schemaless, when several processes write at once, when the app runs in a browser, or when JSONL comes with a real external index.

None of this gives a row count, and I do not think one exists. Row count amplifies the cost, but the cost comes from the operation.

What would change my mind

For the timings: a rerun on your own hardware that disagrees. Do that before trusting any number here. A verification run of the same script on Windows 11, with Python 3.12.8 and SQLite 3.53.2, agreed on direction. SQLite was faster for lookup, aggregate and update at every size, used fewer bytes, and left append without a clear winner. Its outputs were discarded and are not part of the evidence above.

For the recommendation: an indexed-file design that gives equivalent lookup, update and recovery with clearly less complexity than SQLite, or a real workload that turns out to be almost entirely sequential append. Either would move the boundary, and the second one would move it toward JSONL.

What I have not tested: Windows and macOS filesystems as the primary run, SQLite in the browser, compressed JSONL, crash injection, and concurrent writers.

Sources and reproducibility

• Companion benchmark and the locked run’s raw timings. The same script produced the numbers above. It uses only the standard library, and --sizes 10000 gives a one-second check on your machine.
• JSON Lines format
• SQLite query planning
• SQLite transactions and isolation
• SQLite write-ahead logging

Version boundary: timings are from 27 August 2026 on SQLite 3.46.1. The documentation links were read the same day.

More on Data engineering DuckLake on One Machine →