Back to blog
FILE 0xB9·THE SLOT THAT HAS TO FORGET NOTHING

The slot that has to forget nothing

August 24, 2026 · postgres, schema-design, gardening

I have an app that models a seed-starting tray as a grid of cells. Each cell is a row:

CREATE TABLE seed_pods (
    id      SERIAL PRIMARY KEY,
    box_id  INT NOT NULL REFERENCES seed_boxes(id) ON DELETE CASCADE,
    row_idx INT NOT NULL,
    col_idx INT NOT NULL,
    seed    TEXT NOT NULL DEFAULT '',
    sown_on DATE,
    UNIQUE (box_id, row_idx, col_idx)
);

That UNIQUE is the obvious constraint. One cell, one row. Writes are a clean upsert:

INSERT INTO seed_pods (box_id, row_idx, col_idx, seed, sown_on)
VALUES (...)
ON CONFLICT (box_id, row_idx, col_idx) DO UPDATE SET
  seed = EXCLUDED.seed, sown_on = EXCLUDED.sown_on;

It was fine for exactly one season.

The problem

When a seedling is big enough it gets planted out, and the app makes a planting row on the garden map that points back at the pod it came from. The planting's whole biography — what packet, what date it was sown, how many days it took to break ground — is not stored on the planting. It's stored on the pod, and the planting joins to it.

Which means the cell in the tray is now physically empty and available, and the row describing it is load-bearing for something still alive in the dirt.

Sow that cell again and the upsert quietly eats the sow date of a tomato that's forty days into its life. No error. Nothing to notice later, either — the planting just starts claiming it was sown on the day you filled the cell back up, with whatever seed you put in it.

The failure mode I care about isn't corruption, it's plausible corruption.

What doesn't work

Three things I ruled out fast:

Copy the pod's fields onto the planting at plant-out time. Denormalizing means every fix to a sow date after the fact has to be applied in two places, and I already know from experience that the second place doesn't get updated.

Never reuse the cell — make the user delete the pod first. That's the same destruction with an extra confirmation dialog in front of it, and it makes "how many things has this tray started this year" unanswerable.

**Add a cleared boolean and filter on it.** Closer, but the UNIQUE still allows only one row per cell, so there's nowhere to put the second sowing. The constraint is the actual obstacle, not the queries.

Partial unique index

Postgres will happily enforce uniqueness over a subset of the table:

ALTER TABLE seed_pods ADD COLUMN retired_at TIMESTAMPTZ;
ALTER TABLE seed_pods ADD COLUMN generation INT NOT NULL DEFAULT 1;

ALTER TABLE seed_pods DROP CONSTRAINT seed_pods_box_id_row_idx_col_idx_key;

CREATE UNIQUE INDEX ux_pod_cell_live
    ON seed_pods (box_id, row_idx, col_idx) WHERE retired_at IS NULL;

Now "one row per cell" means "one live row per cell". Retired rows stack up behind it, keyed by the same coordinates, still carrying their dates and still joined to whatever plant they became. Nothing is overwritten; the current occupant is just the one with a null retired_at.

The upsert survives, because ON CONFLICT can infer a partial index if you repeat its predicate:

INSERT INTO seed_pods (box_id, row_idx, col_idx, seed, sown_on, generation)
VALUES (...)
ON CONFLICT (box_id, row_idx, col_idx) WHERE retired_at IS NULL
DO UPDATE SET seed = EXCLUDED.seed, sown_on = EXCLUDED.sown_on;

Leave the WHERE off and you get "there is no unique or exclusion constraint matching the ON CONFLICT specification", which is Postgres telling you it found no total index over those columns.

Retire on purpose, not by accident

The interesting decision wasn't the index, it was when to retire. My first instinct was to make it automatic: any write to a cell whose current row has already produced a plant retires it. That's wrong, and it's wrong in a way that would have taken weeks to notice.

Editing a record and replacing a record are different verbs that look identical over HTTP. Fixing a typo in the seed name of a pod that's already been planted out must edit in place — the planting is pointing at that row and I want the correction to reach it. Sowing something new into the same cell must retire and replace. Same endpoint, same table, opposite intent.

So the caller says which one it means:

def write_pod(box_id, r, c, body, replace=False):
    prev = live_pod(box_id, r, c)
    generation = prev.get("generation", 1)
    if replace and is_spent(prev):
        retire(prev["id"])
        prev, generation = {}, generation + 1   # cell is clear, inherit nothing
    ...

is_spent is "this row has finished its job in the tray" — it produced a plant, or the sowing failed. A cell with a seed still growing in it isn't eligible for either verb; you'd be lying about the tray.

The UI makes the distinction visible rather than clever. Tap a planted-out cell and you get its history plus a button that says sow a new seed in this pod. The button is the replace flag. Nothing infers intent from the shape of the payload.

The part that reads everything

Splitting rows into live and retired means every existing query has to pick a side, and the answer isn't the same for all of them.

That third bullet is the one I'd have gotten wrong if I'd added WHERE retired_at IS NULL mechanically to every query touching the table. It took reading each call site and asking what it's actually a statement about.

One migration footgun

My DB helper passes an empty tuple to psycopg rather than None:

cur.execute(sql, args or ())

An empty tuple is not None, so the driver still scans the statement for placeholders — and a DO block containing format('ALTER TABLE x DROP CONSTRAINT %I', name) blows up on the %I before it ever reaches the server. The fix is to keep percent signs out of DDL entirely:

EXECUTE 'ALTER TABLE seed_pods DROP CONSTRAINT ' || quote_ident(cn);

Same result, no format string. Worth knowing because the failure is a migration that silently doesn't apply, so the old constraint stays and the first re-sow of a cell fails on a unique violation, in production, days later.

What I'd do differently

I'd have reached for the partial index the first time I wrote a "one of these per that" constraint on anything representing a physical object. Physical things get reused. The cell in the tray was always going to be planted three times a year; only the schema thought it was one thing forever.

The general shape: when a row is both the current state of a slot and the history of what occupied it, those are two tables' worth of meaning in one table. A partial unique index lets you keep them in one table anyway, and the WHERE clause is the seam between them.