How the Pileon SQL spatial store survives a crash mid-commit

A fast read path is only useful if the data is still there after the power goes out. The spatial store in Pileon SQL got its transaction layer in July 2026, after the query engine and before any more features, so that everything built later could assume committed data survives. This is how that layer works.

One record per committed transaction

Writes go to a log before they go to the store. The write-ahead log sits beside the store in a .uspt.wal file and holds one record for each committed transaction. A record is framed with a checksum and carries its operations: inserting points with their ids, inserting rows, creating a table or dropping one.

A crash can leave a half-written record at the end of the log. On start-up the log is replayed from the front, and replay stops at the first record that is torn or fails its checksum. Everything before that point is a whole transaction and is applied. Everything after it was never acknowledged to a client.

A checkpoint number that makes replay repeatable

Flushing the store to its files is the checkpoint. It takes the commit lock, writes the new state, swaps the root of the store so the new files are the durable ones, and only then truncates the log.

That order leaves one dangerous moment: the root has been swapped, and the log has not yet been truncated. A crash there would replay the log on top of a store that already contains those transactions. To make that harmless, the root records a checkpoint sequence number. Replay skips any record whose sequence number is at or below it, so replaying the log twice gives the same store as replaying it once.

The checkpoint number lives in the root header with a checksum of its own, outside the checksum that covers the main header. Files written before the field existed still read correctly.

Two locks, and what readers see

The commit lock

It serialises commits, schema changes and flushes, so there is only ever one writer applying changes.

The apply lock

This is a read-write lock. A reader holds the read side while it collects a snapshot of the store. A commit holds the write side while it applies its changes and publishes them, and so does the last phase of a flush.

The effect is atomic visibility. A reader sees a commit completely or not at all. The isolation level is read committed. Fixing this also removed an older race between publishing a segment and a reader collecting it, and a race with deleting stale segments.

A transaction is a buffered set of writes. Dropping it without committing is a rollback. Reads inside a transaction see its own buffered writes through an overlay, so a script can insert a row and select it back before committing. Schema changes commit immediately even inside a BEGIN, in the way MySQL does.

One process at a time

The store takes an exclusive lock file, .uspt.lock, using the standard library file lock. A second process that tries to open the same store gets a clear error and not a corrupted file. The operating system releases the lock if the holder crashes, so a dead process never leaves a store locked.

What fsync costs

Durability means the log reaches the disk before a commit is acknowledged, and that is slow. Measured on one machine:

The 760 per second figure is the speed of the disk's flush, not of the database. The practical answer is to load data in batches, which pay for a single fsync no matter how many rows they hold. The spatial benchmark and its accuracy checks did not change with the transaction layer.

Tests

Eleven tests cover this layer. They include replay after a crash, a torn record at the end of the log, repeating a checkpoint, atomic visibility while writes are running concurrently, primary key conflicts and the process lock.

The Pileon SQL page describes the formats and the supported SQL, and the post on block storage covers the layout this log protects.

Written by Caisson AI. Questions or corrections go to hello@caissonai.co.