Back to articles

Tag archive

#sqlite

A
Nov 15, 2026

A JSON column is a schema decision: measuring 50,000 orders in SQLite and D1

Same 50,000 orders as two tables and as a JSON column: 1.32x the bytes, 2.9x slower totals, 1,090x slower "which orders contain this SKU", 11-41x slower edits. Plus the failures with no error message: a text price filters in 150,000 of 150,000 rows, max() over string prices returns 997 while 3,907 exists, and one malformed document kills the whole aggregate.

Nov 15, 202614 min read0 reactions1 comments
T
Nov 15, 2026

The won that disappears at the boundary: measuring money rounding in Python, V8, SQLite and D1

Truncating 10% of a price loses a won on half of all prices. Rounding per line vs per invoice differs on 63% of 10-line carts. Half-even vs half-up disagrees on 5% of Korean VAT amounts. Measured over a few million prices on Python 3.12, V8, SQLite 3.46.1 and Cloudflare D1 — and the float drift everyone blames turns out to be 1e-11 won.

Nov 15, 202613 min read0 reactions1 comments
F
Nov 15, 2026

Five indexes, five different answers: what a two-character search query costs in SQLite and D1

The same 6,178 rows answered five different ways by five indexes, and two of them returned zero with no error. Measured on SQLite 3.46.1: 4.1 VM steps per row for LIKE '%q%', why a prefix LIKE ignores your index, 34 B vs 111 B per row for the two FTS5 indexes, 68.5 ms for a two-character query, and the D1 limit that turns 22 ms into 45 queries per second.

Nov 15, 202617 min read0 reactions0 comments
O
Nov 14, 2026

One writer at a time: SQLite and D1 concurrency, busy locks, and schema changes that ship without downtime

A single-writer database changes your whole design: BEGIN IMMEDIATE instead of BEGIN, one statement instead of read-modify-write, batch() as one transaction and one queue slot, retries with jitter that restart from the top, session bookmarks so a replica shows the row you just wrote, and schema changes in five deploys (add, dual-write, backfill, switch reads, clean up) with verification that gates the switch.

Nov 14, 202619 min read0 reactions0 comments
O
Nov 15, 2026

One day is not 24 hours: date arithmetic that breaks quietly, measured on SQLite and D1

The same 6,480 rows returned 4,320 in one timestamp format and 0 in another. Measured on SQLite 3.46.1 and D1: a 23-hour and a 25-hour day, 60 local minutes that do not exist in 2026, a 24h window that reports 2,283 / 2,160 / 540 rows for one dataset, 20 of 48 month-ends that clamp, a +1mo/-1mo round trip that loses three days, and a business-day formula wrong on 49.7% of spans.

Nov 15, 202617 min read0 reactions0 comments