We put a million agent runs in one Postgres and timed every screen

We filled one Postgres with a million runs and timed every operation an app or the Studio performs. Most were already constant. These were not:

Two rows were measured on 30,000 runs, not a million: at that size the old code already took over a minute.

Sending a message did not change: about 20 ms and 90–96 statements, before and after. The fixes took work off the read path without adding it to the write path.

The store was Postgres 16 with 1,000,005 runs, 8.2 million journal rows, 200,000 threads and 2 million messages: 5.3 GB. We built it with SQL from a real thread of five runs, copied until there were a million.

Then we ran each operation several times and recorded two numbers: the time, and how many statements it sent. The count matters as much as the time. An operation that sends one query per run is fast on a test database and slow on a real one. The count shows it first.

Most of the list was already flat: creating a thread was 1 query, listing threads 1.6 ms, reading a thread's messages 0.5 ms, reading a run 0.3 ms, a Studio page 0.8 ms. They are not in this post.

Symptom. The first run on a new thread got slower as the store grew: 72 ms at 1,000 runs, 7.3 s at 10,000, 70 s at 30,000. A run without a thread took a few milliseconds.

Cause. Before a run joins a thread, GNL checks who owns the thread, so one person cannot write into another's. The answer comes from the thread's owner record. A new thread has no record yet. So the check looked for any earlier run on that thread, by walking the whole run list page by page. A thread started without a signed-in user never got a record at all, so the walk was also how GNL knew it existed. And the walk itself grew quadratically: pages were read with LIMIT … OFFSET n, and Postgres builds every row before the offset. The page at row 15,000 took 132 ms; the first page 3 ms.

Fix. A new anonymous thread now writes a small marker. A store marks itself complete once every older thread has its record or its marker. In a complete store, no record and no marker means new: two point reads. Until a store is complete, the old walk stays, so a correct answer never depends on the upgrade.

Run lists also stopped counting rows. A page now starts right after the last run it returned, by its time and id, on the index that already existed. First run on a new thread at 30,000 runs: 70 s → 14 ms. Reading all 30,000 runs: 71 s → 432 ms.

Row-number paging hid a second bug. A run deleted between two pages moved every later row back by one, so the next page skipped a run. The new cursor fixed that on every store.

Symptom. Erasing one person with about 500 runs sent 425,966 queries and took 90 seconds. 404,674 of those queries read other people's thread owner records.

Cause. A run's person and thread lived only inside the run's input record. To find a person's runs, erasure walked every run. To find the threads they own, it opened the owner record of every thread in the store, 200,000 of them, one at a time.

First fix. A per-person index: one key for each run and thread the person owns, written before the record it points to. One erasure: 24,630 queries, 7–16 s, most of it the deletes themselves.

Final fix. We removed the index. Postgres and Sqlite now keep a run's person and thread as columns on its row, filled by the store in the same transaction as the record. A row whose columns are empty is read and decided from its record, every time. If a writer that does not know the columns rewrites a record, a database trigger sets the columns back to empty, so that row is decided from its record again.

On a 1,000,020-run copy, one erasure took 5.2 s and 21,112 statements, about what the index took, without the hole. The same columns made two other screens cheap: one person's runs 16 s → 1 ms, and deleting a thread with its runs 15 s → 45 ms.

Symptom. The approval inbox showed 167 waiting runs. To find them it read the whole run list and filtered: 23.3 s, 20,335 statements. The strict usage total read every run too: 17 s for one organization. Live updates were the worst. Whenever an organization existed, each 2-second tick did a full scan that took 22.8 s. With one Studio tab open, the database was 73% busy; with three, 94%.

Cause. All three asked one question: which runs are open, or changed? The run row had the answer, but no index covered it. So the only way to answer was to read every row.

Fix. A partial index holds only the runs that are not completed. Here that was 167 rows out of a million. The inbox reads it: 75 ms. The strict total reads the open runs plus one indexed count: 10 ms for an organization, 72 ms for the whole store, with the same totals as before. Live updates ask for the runs changed since the last tick, through the updated-at index: 1–3 ms per tick, the database 0.07–0.17% busy, with one or three tabs, idle or at 20 writes per second.

We tried a cheaper total first: the usage counter plus the open runs. It gave a different number, because the counter misses runs that finished without recording usage and keeps purged ones. So the total stays an exact count.

An older store needs a one-time step to write the thread records the new check relies on. Its first version held every run summary in memory: 1.4 GB at a million runs. It now reads one page at a time and keeps one small entry per thread: +97 MB and 141 s for a million runs.

It is one command, gnl upgrade. It lists each step and what it does, and runs all of them or only the ones you name. --dry-run writes nothing.

These are measured and not fixed yet:

• A message costs about 89 statements on Postgres. On a database across a network, each one is a round trip. We counted where they go; some could be merged. We have not done it yet.
• Memory reads the whole thread on every turn to keep its last few messages. One message took 25.4 ms on a thread of 10 messages, 32.0 ms at 1,000, 57.4 ms at 10,000.
• An older store is fast only after its upgrade. Placing a million older runs in the new columns took 5.8 minutes. Until then, lookups by person still read the older runs from their records.
• Live updates do not announce a deleted run. The next list fetch drops it.

Every slow screen had one shape: a question about a few runs, answered by reading all of them. A small test database hides it. At 1,000 runs, the new-thread check that took 70 s at 30,000 took 72 ms.

So count statements, at the size you expect to run. And when one is slow, do not make the walk faster. Store the answer where an index can reach it, write it in the same transaction as the data, and keep the slow path for data the new one cannot vouch for.