DEV Community: Nobody

Why a Google Photos Takeout scrambles your timeline, and how the dates get back in

Nobody — Fri, 25 Sep 2026 21:56:22 +0000

Originally published at getnobody.org.

If you have ever moved out of Google Photos with Google Takeout, you probably know the moment. You import the export into Apple Photos, Lightroom or a NAS, and a big part of your library lands on one day: the day you made the export. Screenshots from 2018, a WhatsApp picture from 2019, the edits you made in Google Photos, a lot of videos. All of it filed under last Tuesday.

Nothing is lost. Takeout does give you the dates. It just puts them somewhere your photo app never looks. I built Takeoutfix to put them back, and this post is what I learned about how Takeout stores metadata and why the timeline breaks.

What Takeout actually gives you

A Takeout of Google Photos is a set of zip files (often several, each up to 50 GB). Inside them is a folder per year, Photos from 2019 and so on, plus one folder per album. Next to almost every photo and video sits a JSON file. Here is one from the sample library on the site (a synthetic library I generated; no real photos):

{
  "title": "IMG_4410.jpg",
  "description": "Torre de Belém at closing time",
  "imageViews": "0",
  "creationTime": { "timestamp": "1555187469", "formatted": "Apr 13, 2019, 8:31:09 PM UTC" },
  "photoTakenTime": { "timestamp": "1555171512", "formatted": "Apr 13, 2019, 4:05:12 PM UTC" },
  "geoData": { "latitude": 38.6916, "longitude": -9.216, "altitude": 6, "latitudeSpan": 0, "longitudeSpan": 0 },
  "geoDataExif": { "latitude": 38.6916, "longitude": -9.216, "altitude": 6, "latitudeSpan": 0, "longitudeSpan": 0 },
  "photoLastModifiedTime": { "timestamp": "1555187469", "formatted": "Apr 13, 2019, 8:31:09 PM UTC" }
}

Three things in there matter.

photoTakenTime is when the photo was taken, as Unix seconds in a string. This is the date you want.
creationTime is when the file was uploaded to Google Photos. It looks like a date you could use, and it's often years off for anything you backed up late. I only fall back to it when there is no photoTakenTime, and the report says so for every file where that happened.
geoData holds the place, and 0, 0 means "no place", not a spot in the Gulf of Guinea. A caption you typed in Google Photos lives in description and nowhere else.

And then the part that causes the trouble: every file in the zip is stamped with the time the export was made. In the sample, that's 20 September 2026 for all 20 files.

Why the photo app picks export day

A photo app doesn't know Takeout exists. When it imports a file, it looks for a date inside the file (EXIF DateTimeOriginal in a JPEG or HEIC, the creation time in a video's header). If it finds one, the photo goes to the right day. If it doesn't, it falls back to the file's own modified date, which Takeout set to export day. The JSON next to the file is never opened.

(Chart: see the original post.)

Places and captions have the same problem. A place you added in Google Photos, or a caption you typed, exists only in the JSON. So even the photos that land on the right day can arrive without their place.

Here is the sample library on the site, before and after. Before, by the only date an importer can find for most of these files; after, by the capture date that Takeoutfix wrote back in:

(Chart: see the original post.)

The names keep changing

The obvious fix is "for every photo, open the JSON with the same name plus .json and copy the date in". That used to be close to true. It isn't any more, and this is where tools written before 2024 fall over.

Classic: IMG_1234.jpg and IMG_1234.jpg.json.
Since 2024: IMG_1234.jpg.supplemental-metadata.json. A tool that looks for IMG_1234.jpg.json finds nothing and leaves the photo undated.
Cut short: JSON names are kept to 51 characters, .json included (this is the limit people report in the issue threads of Takeout tools). With the longer suffix, far more names hit it, and the cut lands anywhere: .supplemental-metada.json, .suppl.json, .s.json, or just ..json. For a long file name the cut lands inside the name itself.
Duplicates: when two photos share a name, the second is IMG_1234(1).jpg, and its JSON is IMG_1234.jpg(1).json, with the counter moved after the extension.
Edits: IMG_1234-edited.jpg has no JSON of its own (in a German export it's -bearbeitet, in a French one -modifié). It has to borrow the original's.

(Chart: see the original post.)

So the matcher is a series of passes over each folder, from strict to loose: the name itself (with every supplemental-metadata variant and the (1) rule), then the title inside the JSON (which keeps the full, uncut name), then cut names by prefix, then edited copies, then Live Photo videos (IMG_3001.MP4 takes the date of IMG_3001.JPG), and last, album copies that borrow the JSON of the same photo in its year folder. Each pass uses maps keyed by name, so a folder of thousands of files is matched in one go rather than by comparing every file with every JSON. It never pairs a file with a JSON from another folder, except for album copies. When nothing matches, the scan says so and shows the names.

Takeout also splits big libraries over several zips, and a photo and its JSON can land in different parts. So every part is indexed before anything is matched.

Writing the dates back in

Once a file has its JSON, the date has to go inside it, in the place each format keeps it.

JPEG: EXIF DateTimeOriginal, plus OffsetTimeOriginal, GPS and the caption. I don't re-encode the picture. The EXIF block is replaced and every other byte of the file is copied as it was.
HEIC, PNG, WebP and a few others: these are written by ExifTool, Phil Harvey's Perl program, compiled to WebAssembly so it runs in the browser tab. On a 4.4 MB synthetic HEIC it took about 0.4 seconds per file in Chrome on a Mac, much of it ExifTool starting up again for each file.
MP4 and MOV: a video stores its creation time in three header boxes (mvhd, tkhd, mdhd) as seconds since 1 January 1904, in UTC. Those are fixed-size fields, so the date can be patched while the video streams through, without ever holding a large video in memory and without changing any box size (which would break the offsets that point into the video data). Place and caption go in through ExifTool for videos up to 64 MB.
Camera RAW: never rewritten. It gets an .xmp file next to it, which Lightroom and darktable read.

One detail I had to decide: EXIF's DateTimeOriginal has no time zone. It's a local time, "2019:04:13 17:05:12", and the JSON gives an instant in UTC. So I write the local time in a time zone you pick (your computer's by default) and add OffsetTimeOriginal, so apps that read the offset place the photo exactly. For photos taken while travelling, the local time will be in your home zone. The report says that too.

What a browser can't do

A web page can write files into a folder you choose (in Chrome and Edge on a computer), but it can't set their modified dates. Every fixed file gets today's date as its file date. That's why everything above is about dates inside the files: that is what Apple Photos, Lightroom and the NAS photo apps sort by. And a Live Photo comes out as a dated photo and a dated short video, not one merged Live Photo.

Nothing is uploaded at any point. The page reads the zips where they are, writes the fixed copy to your disk, and the server only ever sees a hash of the file list and two counts, to know whether you paid.

If you have a Takeout sitting in your Downloads folder, try Takeoutfix. The scan, the match report and 20 fixed files are free, so you can import those and check before paying. The whole library is $9, once, and re-running the same library with other options is free for 7 days.

How a PNG becomes a cut file: image tracing, step by step

Nobody — Fri, 25 Sep 2026 21:56:09 +0000

Originally published at getnobody.org.

A Cricut or a laser doesn't know what a pixel is. It follows paths: closed outlines, each one a loop the blade or beam can travel from start back to start. So the job of a tracer is to look at a grid of colored squares and write down where the edges are, in as few curves as it can without drifting from the picture.

I built one for Cutline, which turns a PNG or JPG into files for Cricut, Silhouette, Glowforge, LightBurn and vinyl plotters. It runs in the browser tab. Here is every step it takes, with real numbers from the sample badge on Cutline's home page: a 900 x 900 px drawing with paper grain, 40 dust specks and 10 pinholes, which is what a phone photo of a print looks like.

(Chart: see the original post.)

1. Quantize: 220 colors down to 5

The badge is drawn with five paints: paper, cream, ink, pine green and a mustard sun. But anti-aliased edges and paper grain mean the file actually holds 220 distinct RGB values. Vinyl comes in sheets of one color, so the first job is to decide which five colors the image "really" has, and which one each pixel belongs to.

I use k-means, with two changes that matter in a browser:

Cluster a histogram, not pixels. Every pixel drops into one of 32,768 bins (5 bits per channel), and k-means runs over the bins weighted by their counts. A 4096 px image has 16 million pixels but never more than 32,768 bins, so the clustering costs the same at any size.
Seed it the same way every time. The first center is the fullest bin, and each next one is the bin farthest from the centers picked so far, weighted by count. Random seeding would give a slightly different palette on every slider move, and the layers would flicker.

A light blur runs first so single grainy pixels don't vote for a color of their own. But the final layer color is the average of each cluster's unblurred pixels, because the blur shouldn't tint the vinyl.

On the badge this takes about 55 ms and finds paper, cream, ink, green and sun. Paper covers most of the image's border, so it becomes the background and gets dropped: nobody cuts the paper.

2. Masks and cleanup

Each remaining color becomes a mask: a grid of yes or no, one bit per pixel. Straight out of quantization, those masks are a mess:

(Chart: see the original post.)

A thousand pieces of sun would mean a thousand tiny cuts to weed by hand. So the cleanup finds connected regions (4-connected, so pixels touching only at a corner don't count as joined) and:

removes specks: any region smaller than the speck size slider. On the sample that is anything under 146 pixels at preview size.
fills pinholes: any hole that small, as long as it doesn't touch the image border.
stacks the layers for layered vinyl: each layer also covers every layer above it, so the colors overlap a little instead of meeting edge to edge. Vinyl never lands exactly where you put it, and an exact butt joint shows the shirt through a hairline gap.

That last promise is where I found a bug.

The hairline gap

My first cut looked right in the color view. In the cut view, the bottom layer of the badge had two thin crescents running along the inside of the black ring: holes in a layer that should have been one solid disc.

The cause was the anti-aliasing. Where black meets cream, the edge pixels are a blend, and k-means doesn't always give a blend to either neighbor: some of them land closer to a third color. They formed thin, disconnected slivers of that color along the ring, each smaller than the speck size, so cleanup deleted them. Deleted pixels belong to no layer. Stacking can only OR together what exists, so the gap survived into the file.

My first fix was to merge every small region into whichever color surrounds most of it, instead of deleting it. That closed the gaps, but it wrecked the outer edge of the badge, where the same kind of slivers sit between the ink and the paper. Some merged outward and some merged inward, and the smooth circle came out bumpy:

(Chart: see the original post.)

The fix that stuck is narrower. After specks are removed, take the union of every kept color and look at its holes. A real hole, like the inside of a letter O, is background, so it holds background pixels. A gap left by deleted slivers holds none. So any enclosed hole with no background pixel in it is a gap, and it goes to the bottom layer. The badge's bottom layer is now one disc with no holes, and its outline is back to 20 segments.

3. Outline: marching squares

Now each mask is clean, and the tracer needs its edges. The tracing library I use, imagetracerjs (public domain, about 40,000 downloads a week), does this with a version of marching squares.

It slides a 2 x 2 window over the mask. The four pixels under the window are each in or out, so every position is one of 16 cases, stored as a 4-bit number. Case 0 (all out) and case 15 (all in) mean no edge passes here. Every other case says exactly which way the edge runs through that corner. Starting from any edge corner and following the cases, you walk around the shape and come back to where you started, which gives a closed loop for free.

(Chart: see the original post.)

On the badge's outer circle that loop has 3,192 points, one per pixel corner along the edge. A blade following it would stutter through three thousand tiny right angles.

Before fitting, the library moves each point to the midpoint of its edge step. That turns the staircase into an 8-direction path: diagonals where the pixels step evenly, straight runs where they don't.

4. Fit: lines first, then curves

The last step turns thousands of points into a few segments, with a greedy recursive fit:

Cut the path into runs that only use two of the 8 directions.
Try a straight line from the start of the run to its end. If every point is within the line tolerance, keep it.
If not, find the point farthest from that line and fit a quadratic curve through the start, that point and the end. If every point is within the curve tolerance, keep the curve.
If not, split the run at the worst point and do both halves again.

The two tolerances are the whole trade-off between faithful and smooth, and in Cutline they are the Detail and Smoothing sliders. Here is what Smoothing does to the badge's outer circle:

(Chart: see the original post.)

Past a point, more smoothing starts to round off corners you wanted sharp, like the peaks of the mountains. That's why it's a slider and not a constant.

Potrace is the classic alternative. It finds the polygon with the fewest sides that stays within a pixel of the edge, then decides corner by corner whether to round it with a Bezier curve. It often makes nicer curves, but it is GPL, and I wanted a permissively licensed library I could ship in a paid product without thinking about it.

5. Make it a file a machine accepts

The fitted segments go through a few more rules before anything is written:

Every path is closed and ends exactly where it started, so the cutting app treats it as a shape and not a stray line.
Coordinates go into a fixed 1000-unit box, rounded to 2 decimals, so the same design gives the same file.
Holes stay with their shape: the inside of the ring is part of the ring's path, filled with the even-odd rule, so it cuts as a hole instead of a second piece.

The result for the badge: 4 layers, 6 shapes, 7 closed paths and 203 nodes, from 810,000 pixels. The whole preview trace takes about 170 ms on my laptop, which is fast enough to re-run on every slider move.

From there the output is just formatting. Cricut Design Space wants an SVG sized in inches with one group per color. Silhouette Studio's Basic Edition only reads DXF, which has no fills, so every outline becomes a polyline. A laser wants red hairline strokes for cutting and black fills for engraving. The tracing is the hard part; the file formats are bookkeeping.

If you have a logo or a drawing you'd like on vinyl, try it on Cutline. The preview and all the sliders are free, and the files are $2 per image, paid once.

How Fillable finds the blanks in a flat PDF form

Nobody — Fri, 25 Sep 2026 21:50:27 +0000

Originally published at getnobody.org.

A lot of forms still arrive as flat PDFs: an intake form, a volunteer application, an order sheet. Nothing on them can be typed into, so people print them, fill them in by pen and scan them back. The tools that add form fields mostly want a monthly plan for something you need once. Fillable is the one-off version: you drop in the PDF, it finds the fields, you fix what it got wrong and pay $4 once for that document.

Finding the fields is the interesting part, and it all runs in the browser. This post covers how it works.

(Chart: see the original post.)

What a PDF page actually contains

A PDF page doesn't hold "a text field here" or even "a line here". It holds a content stream: a list of drawing operators like "move to", "line to", "rectangle", "stroke", "fill" and "show this text". pdf.js exposes that list through page.getOperatorList(), so the first job is to replay it and write down every straight line and rectangle it draws.

Replaying it means keeping the graphics state the way a renderer does. Every cm operator changes the current transform matrix, q and Q save and restore it, and a form XObject carries its own matrix on top. A line drawn at (0, 0) inside three nested transforms could be anywhere on the page, so every point goes through the full matrix before I keep it. The line width matters too, and it can come from a graphics state dictionary rather than the w operator.

Form generators disagree about how to draw a line. Some stroke a path. Some fill a rectangle 0.5 points tall. Some draw a box as four separate strokes. So a thin filled rectangle counts as a line, and rectangles are also rebuilt from separate lines that meet at their corners. That second step is also what finds every cell of a ruled table.

Text comes from page.getTextContent(), one run per item with its position measured from the baseline. Two extra rules come from real forms: a run of three or more underscores is a writing line typed as text, and characters like ☐ are checkboxes typed as text.

From lines and boxes to fields

With geometry and text in PDF points, classification is a set of plain rules:

Checkboxes are near-squares between 6 and 20 points. Each one takes the text to its right as its name. Short options also take the question on their row, so "Smoker? Yes / No" becomes smoker_yes and smoker_no.
Boxes that are empty become text fields, or multiline fields when they are taller than 36 points. If a label is printed inside the box, the field goes in the space under it or beside it. A box that contains other boxes is a panel, not a field.
Table cells that are empty are named from their column header and row label.
Writing lines are split wherever text sits on them, so "Signature ____ Date ____" drawn as one long rule gives two fields. The label is the nearest text to the left, then below, then above.

The kind comes from the label. "Date of birth" gets a date field and "Signature" a digital signature field (signing one needs a reader that supports digital signatures, such as Acrobat Reader; switch it to text for a typed name). Number fields are only for amounts and quantities. My first version also made phone and zip fields numbers, until I remembered what a number field does to a zip code: "02134" is shown as "2,134.00".

Last, overlapping candidates are merged, everything is clamped to the page box, and names are made unique. The server clamps again when it writes the PDF, because it never trusts coordinates from the browser.

Scanned pages have no operators

A scan is one big image and no text. When a page is mostly image and has almost no text runs, Fillable switches paths: it renders the page at 2 pixels per point, converts it to grayscale and picks a black-and-white threshold with Otsu's method (the gray level that best separates two groups of pixels, ink and paper).

Then it looks for long dark runs along each row and each column, and stacks runs on neighbouring rows into lines with a thickness. Anything thicker than 4 points is a filled bar or a photo, not a rule. Rectangles come from the lines the same way as before, and a rectangle that is mostly ink inside is thrown away. The rest goes through the same classifier as a drawn form.

There's no text recognition, so fields on a scan start unnamed. In the editor you select one, type a name and press Enter to jump to the next.

A quarter of a degree

My first version worked on a perfectly straight test scan and fell apart on the next one, which was tilted by 0.25 degrees. That tilt is invisible to the eye. But at 2 pixels per point, a line that rises by tan(0.25°) climbs one pixel every 229 pixels. A 468 point rule is 936 pixels wide, so it steps up four times, and each step splits it into a separate run. The big boxes lost their edges and the table lost a column.

The fix is to straighten the page before looking for lines. To measure the tilt, I take the dark pixels and, for each candidate angle, count how many land on each row after rotating by that angle. At the right angle, all the pixels of each rule land on the same row, so the counts are very uneven. Summing the squared counts turns that into a score. Fillable tries every 0.05 degrees between -3 and 3, then every 0.01 degrees around the best angle. It rotates the bitmap, finds the lines, and maps each field's centre back to where it sits on the original page.

On a letter page, the whole line and box search, straightening included, takes about 45 ms in my tests. The chart shows the difference on my sample intake form, scanned at different tilts:

(Chart: see the original post.)

How well it does

These numbers are from one intake form I made for testing. It has 25 fields: writing lines, two boxes, five checkboxes, a comments box, a ruled table and a signature line. Real forms are messier, which is why every field can be checked and fixed before you pay.

Sample	Fields found	Extra fields	Right kind	Time in Chrome
Drawn PDF	25 of 25	0	25	11 ms
Scan, tilted 0.25°	25 of 25	3	22	101 ms

On the scan, the three extra fields are the table's header cells. Without text recognition they look like empty boxes. The three wrong kinds are the date and signature fields, which come out as plain text because there is no label to read.

What you get

When you download, the PDF goes to the server once, together with the field list. The server writes real AcroForm fields with pdf-lib and sends the file straight back. The PDF is never stored: the server keeps its SHA-256 hash, page count and field count, so a payment unlocks exactly that document. Date fields carry their format, checkboxes are real checkboxes, and every field keeps its name. Text fields and checkboxes work in Acrobat Reader, Preview and browsers; date formatting needs a reader that runs form scripts, and signature fields a reader that supports digital signatures.

Finding fields, editing and previewing are free. The fillable PDF is $4, once, for that document, with free re-downloads for 24 hours if you spot something to fix. There's no account and no monthly plan.

Try Fillable

How a Word file becomes a printed book: line breaks, widows and the KDP gutter

Nobody — Fri, 25 Sep 2026 21:49:59 +0000

Originally published at getnobody.org.

A Word document has no pages, not really. It has paragraphs, and Word decides where the pages fall while you type. A printed book is the opposite: every page is fixed, the left and right pages mirror each other, and the printer wants a PDF where nothing moves. Somebody has to decide where every line and every page ends.

I built Bookcut to make that decision for self-published authors. You drop in the .docx, it finds the chapters, and it sets the book for an Amazon KDP paperback and an EPUB. All the typesetting runs in your browser tab. This post walks through the three problems that took the most thought, with numbers from the first three chapters of Pride and Prejudice, which is the sample text on the site.

1. Breaking lines

Book text is justified: every full line ends exactly at the right edge, so the spaces between words have to stretch or shrink. My engine gives a word space a natural width of 0.25 em, lets it shrink to 0.2 em and stretch to 0.45 em. Past 0.45 em a line looks loose, with visible holes between words, and the engine reports it.

The obvious way to break lines is greedy: put as many words on the line as fit, then start the next one. It's fast and it never looks back, which is its problem. A good line early in the paragraph can force a terrible line three lines later.

The better way comes from Donald Knuth and Michael Plass, who worked it out for TeX. Look at every possible way to break the whole paragraph, give each line a cost that grows sharply with how far its spaces stretch or shrink, and pick the set of breaks with the lowest total. It sounds expensive, but the best way to end a line after a given word doesn't depend on what comes later, so it's a shortest-path search over break points. My version is a simplified one: it adds a cost for each hyphenated line, and a very large one for two hyphenated lines in a row.

I expected this to fix everything. So I measured it. I set every paragraph of the three chapters in the Classic design (EB Garamond at 11.5 pt), once with a greedy breaker and once with the total-fit breaker, and counted the lines whose spaces had to stretch past 0.45 em.

(Chart: see the original post.)

Two things surprised me.

On a narrow 5 x 8 page without hyphenation, the algorithm made no difference at all: 47 loose lines against 48. A line of that width holds about eight or nine words of Austen, and when the next word is "neighbourhood" there is simply no good place to break. Total fit can spread the pain around the paragraph, but it can't make a long word shorter.

Hyphenation did most of the work. Turning it on at 5 x 8 cut the loose lines from 48 to 11, and on top of that total fit beat greedy by 18 to 11. At 6 x 9 the wider line gives both methods more room, and the gap is small once hyphenation is on. So Bookcut hyphenates by default, with rules taken from how book typesetters usually do it: only words of 6 letters or more, at least 3 letters before the hyphen and 2 after, and never on the last word of a paragraph.

2. Widows and orphans

Once the lines exist, they have to be poured into pages. The rule every book designer knows: a paragraph should never leave its first line alone at the bottom of a page (an orphan), or its last line alone at the top of the next one (a widow). A heading also shouldn't sit at the bottom of a page with its text on the next.

(Chart: see the original post.)

My page breaker handles this with a flag on every line: "ending the page after me would strand a line". It fills a page as far as it can, and if the last line it placed carries that flag, it walks back until it finds a line where a break is safe. The walk back is limited to 6 lines. Past that the page would look visibly short, so the engine fills the page anyway and lists the widow in the preview, where the author can decide. Headings are flagged the same way and stay with at least two lines of their text. A scene break never ends a page either, because at the foot of a page a reader can't tell it's there.

Pages then get their furniture: running heads (the author on left pages, the title on right pages), page numbers, and none of either on chapter openers or blank pages.

3. The gutter depends on the page count

This is the part that surprised me most. A thick paperback curves into its spine, so the inside margin (the gutter) has to be wider the more pages the book has, or the text disappears into the fold. KDP publishes the minimum on its help page for trim size, bleed and margins:

(Chart: see the original post.)

Now the loop: the margins decide how wide the lines are, the line width decides how many lines there are, and the number of lines decides the page count, which decides the margins. A book that sets at 300 pages with a 0.5 in gutter might need 0.625 in, and with the narrower lines it now sets at 304 pages. That's fine, 304 is still in the same band. But it has to be checked.

Bookcut estimates the page count from the word count first, lays the book out with the margins for that estimate, and then looks at the real page count. If it landed in a different band, it lays the whole book out again with the new margins. Margins only ever get wider after the first correction, so the passes can't bounce between two bands forever, and there is a hard limit of 6 passes. Each design also has its own preferred margins, often wider than KDP's minimum for a short book, and the band only takes over once it asks for more than the design does. One of the tests sets a generated 99,000 word novel at 5 x 8 in, which comes out over 700 pages and ends with the 0.875 in gutter KDP asks for.

A few more rules come after that. The page count must be even and at least 24, so the engine adds blank pages at the end. Recto (right-hand) pages have the gutter on the left and verso pages on the right. Every page is exactly the trim size with no bleed and no crop marks. The fonts (four open-licensed book faces) are embedded in the PDF.

What the author sees

The preview in the browser draws each page as SVG from the same layout the PDF is written from, word by word at the same positions, so the pages you read are the pages KDP prints. Next to it is a check panel with the final page count, the gutter KDP asks for at that length and the one the book actually has, the smallest text size and the blank pages.

If you have a manuscript waiting, try Bookcut. Importing, fixing the outline and previewing every page are free. The print PDF and the EPUB are $12 for the book, once, and you can export again free for 30 days when you find a typo.

Why your SQLite WAL file never shrinks

Nobody — Fri, 25 Sep 2026 15:08:40 +0000

Originally published at getnobody.org.

If you run SQLite in WAL mode with a busy writer and a few readers, you may one day find a -wal file of several gigabytes next to a database that is much smaller. Autocheckpoint is on, nothing reports an error, and PRAGMA wal_checkpoint says it worked. I read the checkpoint code in SQLite 3.53.2, then built a small lab to watch it happen.

The short version: when read transactions overlap so that at least one is always open, the checkpoint can never finish and the writer can never go back to frame 1. In my runs the WAL grew by 11.9 MiB per second for as long as the overlap lasted, and when the readers went away it stayed at 357 MiB. A wal_checkpoint(TRUNCATE) every 5 seconds kept it under 60 MiB in 2 of 3 runs (119 MiB in the run where one call came back busy), at the price of stalling the writer for up to 1.8 s each time.

How WAL mode commits and checkpoints

In WAL mode a commit doesn't touch the database file. The writer appends the changed pages to the -wal file as frames (a 24-byte header plus one page), and a shared-memory index in the -shm file maps page numbers to the newest frame that holds them. Readers look there first and fall back to the database file.

A checkpoint copies frames back into the database file. Two counters in the shared index drive everything (wal.c L321-L401):

mxFrame is the last committed frame.
nBackfill is how far the checkpoint has copied.

When nBackfill reaches mxFrame, everything in the WAL is in the database, and the next writer can start again at frame 1 instead of appending. It overwrites the old file from the start. It doesn't truncate it.

By default SQLite runs a checkpoint after any commit that leaves the WAL at 1,000 frames or more (sqlite3WalDefaultHook, main.c L2470-L2481). That checkpoint is the PASSIVE kind: it never waits for anybody.

(Chart: see the original post.)

What a reader pins

A reader doesn't copy anything. When it starts a read transaction it pins one of four read marks (aReadMark[1..4], since WAL_NREADER is 5 and slot 0 is special) whose value is no greater than the mxFrame it saw. If a slot already holds that value it shares the slot with the readers already on it. Otherwise it writes its mxFrame into a slot it can lock exclusively, and if it can't get one, it settles for the largest older mark (walTryBeginRead, L3163-L3199). It holds a shared lock on that slot until the transaction ends.

Frames after its mark are newer than its snapshot. For pages changed in those frames, the reader still needs the old copy in the database file, so the checkpoint may not copy those frames over it until the reader finishes (L378-L381). Frames up to its mark are safe to copy.

If the WAL is fully copied when the reader starts, it takes slot 0 instead, which means "read the database file only, ignore the WAL" (walTryBeginRead, L3128-L3160). Remember that slot 0, it matters later.

Why the checkpoint stops at the oldest reader

walCheckpoint starts by assuming it can copy everything, then walks the four read slots (L2227-L2245):

mxSafeFrame = pWal->hdr.mxFrame;
mxPage = pWal->hdr.nPage;
for(i=1; i<WAL_NREADER; i++){
  u32 y = AtomicLoad(pInfo->aReadMark+i); SEH_INJECT_FAULT;
  if( mxSafeFrame>y ){
    assert( y<=pWal->hdr.mxFrame );
    rc = walBusyLock(pWal, xBusy, pBusyArg, WAL_READ_LOCK(i), 1);
    if( rc==SQLITE_OK ){
      u32 iMark = (i==1 ? mxSafeFrame : READMARK_NOT_USED);
      AtomicStore(pInfo->aReadMark+i, iMark); SEH_INJECT_FAULT;
      walUnlockExclusive(pWal, WAL_READ_LOCK(i), 1);
    }else if( rc==SQLITE_BUSY ){
      mxSafeFrame = y;
      xBusy = 0;
    }else{
      goto walcheckpoint_out;
    }
  }
}

If it can lock a slot exclusively, nobody is using that mark, so it resets it: slot 1 to mxSafeFrame, the others to READMARK_NOT_USED (0xffffffff, unused). If it can't, a live reader is there, and mxSafeFrame drops to that reader's mark. The copy loop then skips every frame after mxSafeFrame (L2306), and nBackfill only advances to it (L2331).

Then comes the line that makes this hard to notice (L2340-L2345):

if( rc==SQLITE_BUSY ){
  /* Reset the return code so as not to report a checkpoint failure
  ** just because there are active readers.  */
  rc = SQLITE_OK;
}

So PRAGMA wal_checkpoint(PASSIVE) returns busy = 0 even when it copied only part of the WAL. The signal is in the other two columns: log (frames in the WAL) keeps getting bigger than checkpointed.

Why the file grows, and why it stays big

Copying is only half of it. The WAL goes back to frame 1 in walRestartLog (L3869-L3909), which runs at the start of every write. It rewinds only if the writer's own snapshot is on slot 0 (pWal->readLock==0, so the WAL was fully copied when the writer started), and it can lock read slots 1 to 4 exclusively, meaning no reader is using the WAL.

Now put a steady writer next to readers that overlap. Some reader always holds a mark from a second or so ago. The checkpoint copies up to that mark and stops. nBackfill never catches up with mxFrame, so the writer is never on slot 0, so it never rewinds. Every commit appends. The -shm index grows with it, one 32 KiB block per 4,096 frames (HASHTABLE_NPAGE, L615).

The SQLite docs describe exactly this under checkpoint starvation: "if a database has many concurrent overlapping readers and there is always at least one active reader, then no checkpoints will be able to complete and hence the WAL file will grow without bound."

And when the overlap ends, the file keeps its size. A rewind moves the write position to frame 1, but, as the same page says, a checkpoint "does not normally truncate the WAL file (unless the journal_size_limit pragma is set)". The file only gets smaller when:

journal_size_limit is set, and the first commit after a rewind trims the file to that limit (L4210-L4221). The default in this build is -1, no limit.
someone runs wal_checkpoint(TRUNCATE), which truncates it to 0 bytes (L2363-L2381).
the last connection to the database closes, checkpoints and deletes the WAL.

The experiment

One writer process commits one row with a 1,000-byte blob per transaction, open loop at a fixed 2,000 commits per second, with synchronous=NORMAL. Reader processes each run BEGIN, a SELECT that takes the snapshot, hold it, look up 100 recent rows, COMMIT, then idle. A sampler records the file sizes every 100 ms. Seven scenarios, 30 s each, 3 runs of each, interleaved:

	Scenario
a	writer only
b	2 readers holding 0.4 s every 1.0 s: about 20% of the time nobody is reading
c	3 readers holding 1.0 s, idling 0.2 s, staggered: at least 2 are always open
d	c, plus `wal_checkpoint(TRUNCATE)` every 5 s with a 5 s busy timeout
e	c, plus `journal_size_limit` of 4 MiB
f	b, plus `journal_size_limit` of 4 MiB
g	c, with `wal_autocheckpoint=0` on the writer (the sampler's once-a-second probe still checkpoints, see below)

Here is what the readers actually did in 3 seconds of one run of b and one of c, straight from the logged open and close times:

(Chart: see the original post.)

And here is the WAL file:

(Chart: see the original post.)

With gaps (b), the WAL rewinds twice a second, once per gap, 60 times in 30 s, and peaks at 6.05 to 6.08 MiB. With overlap (c), it never rewinds once, and grows in a straight line at 11.91 MiB/s in all three runs. That is about 1.5 frames of 4,120 bytes per commit: the final checkpoint counted 90,873 frames for 60,000 commits. At the end the -shm file was 736 KiB, 23 index blocks.

Through all of c, every PASSIVE probe returned busy = 0, while log ran up to 2,751 frames ahead of checkpointed. That is a little under one second of writes, about one reader hold time.

After the readers leave

When the run ended I stopped the readers and ran one more PASSIVE checkpoint with nothing else open. It copied everything, returned 0 | 90873 | 90873, and the file stayed where it was:

(Chart: see the original post.)

journal_size_limit did nothing in e. Its trim runs on the first commit after a rewind, and under overlap there are no rewinds. In f, where there are, it held the file at 4.00 MiB, though the high-water mark still reached 6.01 MiB between trims. In one of the three e runs the file was already 4.00 MiB before that final checkpoint. Most likely the readers closed a moment before the writer's last commits during shutdown, so one commit found a fully copied WAL, rewound and was trimmed: the same mechanism, with the blocker gone.

What TRUNCATE costs

The non-passive modes behave differently (sqlite3WalCheckpoint, L4295-L4426). FULL, RESTART and TRUNCATE take the writer lock first, waiting with the busy handler. Because xBusy is set, walBusyLock then waits on each reader slot instead of giving up. RESTART and TRUNCATE also wait until no reader is using the WAL at all (L2358-L2362), and TRUNCATE then cuts the file to 0 bytes. Readers that start after the WAL is fully copied take slot 0, which doesn't hold the checkpoint up, and in my runs the readers' 100-row lookup was no slower in d than in c (226 against 224 µs median). The SQLite docs do warn that readers might block while it runs. The writer is blocked for sure, because the checkpoint holds its lock.

In d, 14 of 15 TRUNCATE calls reset the WAL and returned 0 | 0 | 0. They took 1.40 to 1.81 s (median 1.70 s) with readers that hold for 1.0 s. That is close to two hold times, and the code explains why: first the call waits for the readers pinned below mxFrame, but while it waits, readers that start see a WAL that isn't fully copied yet, so they take a real mark at the new mxFrame. The RESTART step then has to wait for those too. On all 14 calls, the checkpoint returned within 105 ms of the last close of a reader that had started before the copy finished.

Median of 3 runs	c: overlap	d: c + TRUNCATE every 5 s
WAL at 30 s	357.03 MiB	59.58 MiB
Writer time inside `execute()`, p50 / p99.9	38 / 2,653 µs	38 / 745 µs
Writer response time p99	0.50 ms	1,719 ms
Writer response time max	6.7 ms	1,814 ms
Commits more than 100 ms late (of 50,000)	0	16,442

"Response time" is when a commit finished minus when the open-loop schedule said it should start, so a stall counts in full. Time inside execute() hides it: only one commit per TRUNCATE actually waits, and the thousands queued behind it run fast once it lets go. If you measure the effect of a checkpoint job on your writer, measure response time.

The one TRUNCATE that failed came back in 1.68 ms with 1 | -1 | -1. That is the WAL_CKPT_LOCK check at the very start (L4336): if another checkpoint is already running, every mode returns busy without calling the busy handler. My probe wasn't running at that moment, so the other checkpoint was almost certainly the writer's own autocheckpoint, which under starvation runs after every commit. That call missed, the WAL grew for another 5 s, and that run's high-water mark was 118.86 MiB instead of about 60.

Two smaller results

Checkpointing on the writer's connection costs it something under starvation. Once the WAL is past 1,000 frames, the autocheckpoint runs after every commit and copies whatever it can. In g the writer's autocheckpoint was off, and the only checkpoint was the sampler's PASSIVE probe once a second from another connection. That dropped the writer's p50 from 38 to 32 µs and its p99.9 from 2,653 to 694 µs. The WAL grew exactly as fast, and the probe kept nBackfill as close as the autocheckpoint did (at most 2,743 frames behind, against 2,751 in c), since the checkpoint wasn't the thing holding the WAL down anyway.
Reads did not get slower, and the code says they shouldn't. A reader doesn't search the whole WAL index. walFindFrame walks hash blocks only from its mxFrame down to the block holding minFrame (L3568-L3569), and minFrame is nBackfill + 1 when the read transaction starts (L3253). So lookup cost depends on how far the checkpoint lags, not on the size of the WAL. Under starvation the lag stayed at most 2,751 frames, less than one 4,096-frame block, so a lookup searched 1 or 2 blocks. In c the median was 216 µs for transactions that started 5 to 12 s into the run and 218 µs for 22 to 30 s, at a WAL of up to 357 MiB. Reads would slow down only if nBackfill itself fell far behind, for example with autocheckpoint off and no other checkpointer. I didn't measure that.

What to do in your app

Keep read transactions short. The problem is overlap, and overlap comes from long snapshots: a cursor left open while you call an API, a BEGIN that a connection pool never closed. In Python, a SELECT whose rows you haven't fully fetched keeps its statement active, and an active statement keeps its read snapshot.
Watch for it. Poll PRAGMA wal_checkpoint(PASSIVE) and alert when log - checkpointed keeps rising, and watch the size of the -wal file. The busy column won't tell you.
Set journal_size_limit, for example PRAGMA journal_size_limit = 67108864 for 64 MiB. It doesn't stop starvation, but it gives back the disk the next time the WAL rewinds.
Run wal_checkpoint(TRUNCATE) or (RESTART) from a maintenance job with a busy timeout, when writes are quiet. Expect it to block the writer for up to about twice your longest read transaction. If it returns busy = 1, retry soon rather than waiting for the next period.
If you turn off autocheckpoint, checkpoint from another connection. In g, one PASSIVE checkpoint a second from a separate connection kept up as well as the autocheckpoint and took that work off the writer. With nothing checkpointing at all, nBackfill never reaches mxFrame, so the WAL never rewinds, gaps or not.

How I measured

Machine: Apple M5 Pro (18 cores, 48 GiB), macOS 26.6.2, APFS on the internal SSD.
Software: Python 3.14.4 and the SQLite it links, 3.53.2 (DEFAULT_WAL_AUTOCHECKPOINT=1000, DEFAULT_JOURNAL_SIZE_LIMIT=-1, from PRAGMA compile_options). The source links above are pinned to the version-3.53.2 tag, because that is what Python links here. 3.53.3 and 3.53.4 are out.
Runs: 7 scenarios × 3 runs × 30 s, interleaved. The first 5 s of each run are left out of the latency statistics. Tables show the median of 3 runs. The writer is open loop, so every run issued all 60,000 scheduled commits, and all of them completed with no write errors. That says nothing about stalls; the response times above do.
Caveats:
- The sampler's PASSIVE probe once a second is itself a checkpoint, and it runs in every scenario. Where autocheckpoint is on, it copies no more than the autocheckpoint would. In g it is the only checkpointer, so g means "one PASSIVE checkpoint a second from another connection", not "no checkpointing". --probe-every 0 turns it off.
- The first full pass had a probe colliding with the TRUNCATE calls and a maintenance call firing after the run had ended. I fixed both and reran everything. All numbers here are from the second pass.
- This is one machine and one filesystem. I did not run the Linux cross-check or a sweep of write rates.
- "MiB" is 2^20 bytes.

The scripts, the raw CSVs and how to rerun them are in the lab folder. python3 run.py runs the full matrix in about 12 minutes with nothing but the standard library.

Credit goes to D. Richard Hipp and the SQLite developers, who wrote and maintain the WAL code and whose WAL documentation already describes this failure and the "reader gaps" fix in plain words. The code is commented well enough that a stranger can follow a checkpoint line by line, which is how this post got written. The same page also documents this year's WAL-reset bug, a rare corruption case when two connections write or checkpoint at the same instant, which one of the SQLite developers, Dan, found and fixed in 3.51.3.

How Postgres 19 checks foreign keys without running SQL

Nobody — Thu, 24 Sep 2026 23:29:54 +0000

Originally published at getnobody.org.

Every time you insert a row into a table with a foreign key, Postgres has to prove the referenced row exists and stop anyone from deleting it until your transaction ends. Up to Postgres 18, it did that by running a tiny SQL query for every single row. Postgres 19 stops doing that. I read the new code, then measured it.

The short version: on a 1 million row bulk insert, the foreign key check costs about 44% less time in Postgres 19. A second optimization that was pulled from 19 before release would have helped much more, but only when the foreign key values arrive in order.

The old path: a query per row

A foreign key is enforced by an internal AFTER INSERT trigger, RI_FKey_check in ri_triggers.c. In Postgres 18 that trigger builds this query once, saves the plan, and runs it through SPI (the server's internal SQL interface) for every inserted row:

SELECT 1 FROM ONLY parent x WHERE id = $1 FOR KEY SHARE OF x

FOR KEY SHARE is the important part. It locks the parent row so nobody can delete it or change its key while your transaction is open, but it still lets other sessions update the parent's non-key columns.

The plan is cached, but every row still pays for SPI setup, executor startup and teardown, snapshot handling and the permission checks, just to do one index lookup.

(Chart: see the original post.)

The new path: probe the index directly

Amit Langote's commit 2da86c1ef9 adds a fast path. Before touching SPI, RI_FKey_check now asks two questions:

if (ri_fastpath_is_applicable(riinfo) &&
    ri_FastPathCheck(riinfo, fk_rel, newslot))
    return PointerGetDatum(NULL);

ri_fastpath_is_applicable says no for two cases: when the referenced table is partitioned (the probe would need to be routed to the right partition), and for temporal foreign keys (range containment needs more than a single lookup). Later fixes added more fallbacks: the referenced index has to be a btree, and its collation must match the column's.

If the answer is yes, ri_FastPathCheck does what the query used to do, by hand:

Opens the parent table and its unique index.
Takes a snapshot. In the shipped version this happens after the table lock is granted. Taking it earlier opened a window where a parent row committed while you waited for the lock would be invisible, and the check would fail for a key that exists (commit 1390182683).
Builds scan keys from the new row's values and probes the btree with index_getnext_slot.
Locks the row it found with table_tuple_lock(..., LockTupleKeyShare, ...) in ri_LockPKTuple. That's the same lock FOR KEY SHARE takes.
If the lock had to follow an update chain to reach the newest row version, recheck_matched_pk_tuple checks that the key still matches. That is the fast path's version of the recheck the executor does for SELECT ... FOR UPDATE.

It also switches to the parent table owner's user id for the lookup, the same way the SPI path does, so permissions and row-level security behave the same.

The benchmark

A parent table with 1 million integer primary keys, and a child table with a foreign key to it. Each run inserts 1 million child rows in one INSERT ... SELECT, after a TRUNCATE and a CHECKPOINT. The same insert into a table with no foreign key is the baseline. Six runs per version; the charts show the median.

(Chart: see the original post.)

With scattered keys, the foreign key check itself (the time above the no-foreign-key baseline) drops from 2.17 s in 18.6 to 1.21 s in 19. The whole insert is 1.66x faster.

The control: turn the fast path off

To check the gain really comes from the fast path, I ran the same test with a partitioned parent table. The fast path refuses partitioned parents, so 19 falls back to SPI:

(Chart: see the original post.)

Two things show up here. The gain disappears when the fast path is off, so the fast path is what made the difference. And a foreign key to a partitioned table is expensive either way: about 3.5x slower than the SPI path on a plain table, because every check has to find the right partition first.

The batching that was pulled

Three days after the fast path, a second commit went further. Instead of probing the index once per row, it buffered up to 64 rows and checked them together. For single-column keys it passed all 64 values as one array scan key (SK_SEARCHARRAY), so the btree sorts them and walks its leaf pages once instead of descending from the root 64 times. The commit reported about 2.9x in total over the old code.

That batching is in the 19 betas but not in 19.0. On September 10 it was removed from REL_19_STABLE. Buffered checks have to survive across trigger calls and be flushed at the right moment, and that state has to be right under nested triggers, subtransactions, deferred constraints and SET CONSTRAINTS. Miss one case and a buffered check never runs: the transaction commits a real foreign key violation without an error. The commit message says it plainly: with 19 close to release, there wasn't time to be sure every case was covered. Batching stays on master for a later release. That's a hard call to make that close to release, and it's the right one.

My first measurement disagreed with the 2.9x. With scattered keys, master (batching included) is only about 2% faster than 19. The array scan only saves work when the 64 values land on the same few leaf pages. My keys were deliberately spread over the whole index, so every value still needed its own leaf.

So I reran with the keys in order (parent_id = 1, 2, 3, ...), which is what you get when child rows are loaded in parent order. Now batching does what the commit said: 701 ms against 1,021 ms for 19, and 2.8x against 18.6, close to the reported 2.9x.

Keys	18.6	19	master (batching)
Scattered	2,499 ms	1,509 ms	1,473 ms
Sequential	1,965 ms	1,021 ms	701 ms
Scattered, partitioned parent	7,808 ms	7,395 ms	not run

What this means if you run Postgres

Bulk loads into tables with foreign keys get faster in 19 for free, as long as the referenced table isn't partitioned and the key has a btree index (primary keys and unique constraints do).
Foreign keys to partitioned tables don't get this. If you bulk-load into a table that references a partitioned table, the check still costs about 7 µs per row.
Load order matters more after batching lands. Once batching ships in a later release, loading child rows sorted by parent key could make the check nearly twice as cheap again.
Only the insert-side check changed. ON DELETE CASCADE, SET NULL and the other action triggers still go through SPI, as the original commit explains.

How I measured

Machine: Apple M-series laptop. Each server ran in Docker (a Linux arm64 VM) with 4 CPUs and 4 GB of memory, one server at a time.
Settings: shared_buffers = 1GB, max_wal_size = 8GB and checkpoint_timeout = 30min on every version. Everything else was left at the defaults.
Versions:
- 18.6 and 19beta3 are the official Docker images.
- "19" is REL_19_STABLE at 8fbbb338be, which already has batching removed, built from source with -O2.
- "master" is master at 4545cee, built the same way.
- The 19beta3 image still includes batching and measured 1,529 ms on scattered keys, close to both 19 and master.
Caveats:
- The source builds use a different compiler setup from the packaged images. That probably explains part of the small difference in the no-foreign-key baseline (332 ms against 299 ms).
- These are single-session numbers. Concurrent inserts and lock contention are a separate test.

All scripts, Dockerfiles and raw results are in the lab folder. Run ./run.sh <label> <image> and you get the same table.

Credit goes to Amit Langote, who wrote the fast path, the batching and roughly thirty follow-up commits to this code this year, and to everyone on pgsql-hackers who tested the betas hard enough to find the problems before release.

Why easing makes animation feel alive

Nobody — Thu, 24 Sep 2026 04:27:25 +0000

Originally published at getnobody.org.

Move a box from left to right at a constant speed and it looks mechanical, like a machine part. Let it start quickly and settle gently into place and it suddenly feels like an object with weight. The only thing that changed is the easing curve: how progress is spread out over time.

Progress versus time

Every animation has a progress value that goes from 0 (start) to 1 (end). Easing is a function that takes linear time and returns how far along the motion should be.

(Chart: see the original post.)

Curve	Formula (p = time from 0 to 1)	Feels like
Linear	`p`	A machine. Constant speed, abrupt start and stop
Ease in	`p³`	Something gathering speed, like falling
Ease out	`1 - (1 - p)³`	Something arriving and settling
Ease in-out	`4p³` for the first half, then `1 - (-2p + 2)³ / 2`	A deliberate move from A to B

Which one to use

Things entering the screen: ease out. They arrive fast and settle. This is the most useful curve by far, because the viewer's eye catches the movement early and the element is readable sooner.
Things leaving the screen: ease in. They start slowly and accelerate away, so they don't distract at the end.
Things moving from one place to another on screen: ease in-out. Both ends are gentle, so the move reads as intentional.
Linear: almost never for movement. It works for things that should feel mechanical or continuous: a progress bar filling, a clock hand, a line being drawn at a steady pace.

Timing matters as much as the curve

The same curve feels different depending on how long it lasts and how elements are staggered:

(Chart: see the original post.)

A few rules of thumb from building animated explainers:

Stagger entrances by 150 to 400 ms, in reading order. Everything appearing at once reads as a flash; one thing at a time reads as a story.
Keep small entrances short, around 300 to 600 ms. Longer feels sluggish.
Time motion to speech. In a narrated video, an element should appear just as the narrator mentions it.
Combine properties. A fade plus a small 20 to 40 pixel rise looks far more polished than a fade alone.

In code

const EASING = {
  linear: (p: number) => p,
  easeIn: (p: number) => p * p * p,
  easeOut: (p: number) => 1 - (1 - p) ** 3,
  easeInOut: (p: number) => (p < 0.5 ? 4 * p * p * p : 1 - (-2 * p + 2) ** 3 / 2),
};

// Where an element is at time t, for a move from `from` to `to`:
const value = from + (to - from) * EASING.easeOut(progress);

These are cubic curves. CSS offers similar built-in curves (ease-in, ease-out, ease-in-out) defined with cubic-bezier(), whose exact shapes differ slightly but follow the same idea.

Earlier in this series: how a video editor works and rendering MP4 video in the browser.

Nobody