The aggregation page groups rows over a date window. It was very slow. On 7 August, two days after we moved to PostgreSQL, I measured it again.

It pulled down 18.6 MB decoded. I still couldn't click anything for about 55 seconds. It made 41 label requests: batches of names sent to an API, most of them an LLM call to decide what those names meant. One of three cold loads failed with an HTTP 500 error on the resume fetch itself.

The before numbers, from July on Firestore, were the same shape. This page was not the only reason we moved, but it was the urgent one, and it did not notice.

What the page does

The page groups the entries inside every resume by one field and counts them over a date window. That is a group-by over a nested list. Firestore Standard, which is what we were running, has aggregation queries (count(), sum(), average()), but they return one value for a whole query, not one row per group. A per-group breakdown means one aggregation per group, which means you need the list of groups first, which is most of what you were trying to work out.

So we built it the way Firestore makes easy. Load the collection, aggregate in the browser.

The download itself was not this page's fault. The search box lives in the shared layout and pulls the resume list a manager is allowed to see, so in July a full page load for a manager cost 18.27 MB decoded, 2.66 MB over the wire, on any page in the app. Colleagues without manager access only ever get their own rows, and an in-app click could hit a five minute cache. A hard load paid in full. The aggregation page inherited that download, then built the grouped result in the browser, resolving labels as it went. Ask for ten years of history and you waited almost a minute, across 31 requests.

None of that was a mistake at the time. When the app was one person reading their own resume, it cost nothing worth measuring. Once the whole list is in the browser, using it there feels free, and it keeps feeling free long after it stops being cheap.

Why the migration changed nothing

We had changed the database. We hadn't changed the page. It was still fetching every resume and doing the work itself. The browser doesn't know which database it is talking to.

Two tasks of about 28 seconds are the part I got wrong for a while. I went for the charts first, because a page with doughnuts looks like a rendering problem. They have ten slices each and the data behind them is memoized, so there was nothing there.

Then I assumed the pairwise clustering loop, because it is the only obviously quadratic thing on the page. Replayed locally, that loop costs tens of milliseconds. The biggest local block lands the moment the payload does, and it is nearly the same at three years as at ten. Parsing the JSON takes under a tenth of a second and the aggregation build isn't much more, so it is neither of those. I never got a 28 second task to show up on my machine. Past that I stopped looking, because guessing is how the clustering theory got started.

Charts, then clustering. Both times I went for what looked slow instead of the payload. The fix was to stop shipping 18 MB into the browser.

The migration did not make the page fast. It made the page fixable.

Could Firestore have done it?

If the fix was a server-side read model, why not build it on Firestore and skip the migration?

Firestore comes in two editions, and the answer is different for each. We were on Standard.

On either one we could have built the same derived model: on every resume save, fan the nested list out into its own collection with startDate, groupId and documentId, then query by date window. That is a normal Firestore pattern and it would have removed the corpus download.

Standard stops there. Its aggregation queries can't group by a field, so "how many rows in this group" is either many document reads or a counter collection you keep correct yourself. There is no join, so fields that live on a related row either get copied onto every child, with write amplification and stale copies, or you batch-get them. We were doing the second one, at roughly 79,000 reads against the related collection in one monitoring window. And you pay per document: reading 2,000 child rows is 2,000 reads, on every page load, for every manager.

Enterprise is a different product. Its Pipeline operations reached general availability on 20 April 2026, about two months before we decided, and they bring grouped aggregation and joins through correlated subqueries. That is most of what Standard was missing, and most of what the page needed. Enterprise probably could have done this, but we didn't evaluate it. Switching editions is not a setting you flip, it is an export and import into a new instance, but it would likely still have been less work than moving to PostgreSQL. I didn't run the comparison that would let me say more. If I made this decision again, Enterprise is the first thing I would put on the table.

Against Standard the rest of the choice holds up. Vector search would have needed a second store or a pile of denormalised filter fields. Full-text search meant Enterprise or Algolia, a hosted search service. Uniqueness we had "solved" with a reservation collection, a small distributed protocol with orphan cleanup, where PostgreSQL has UNIQUE. We picked PostgreSQL because tsvector, pgvector and UNIQUE have been boring for years, and because we could keep Firebase Auth.

Firestore is better at two things. The nested resume body is a document, and scale-to-zero is real money. We kept the first, since bodies still live in jsonb. We took the second knowing what it costs: an always-on Cloud SQL instance bills whether anyone opens the app or not. I haven't compared the two bills side by side. For an internal tool with steady weekday load and no anonymous traffic, a predictable instance cost was a trade we were happy to make.

The name clustering didn't go away. PostgreSQL doesn't know that "Acme" and "acme B.V." are the same name either. All we did was move that work: the canonical label gets resolved once, when a resume is saved, and stored on the row. The read never clusters anything. That wasn't why the page was slow. It just means the browser has one less job.

The shred is a real cost, and it lands on every save. On Firestore Standard we would have paid it too, because there is no way to aggregate across an array inside a document without flattening it into its own collection first. Enterprise can flatten at read time with unnest, which is the same work moved to the other end, paid on every read instead of once per save. We pay once per save. When we measured a worst-case save on the emulators, regenerating embeddings and short links and re-shredding the derived rows, it came to 15 operations on SQL Connect against 106 document writes on Firestore, because Firestore charges for each of the 48 embeddings it deletes and rewrites. Writes are where SQL was supposed to lose, and it didn't.

What actually fixed it

A derived table, one row per item in a nested list, shredded out of the resume on every save:

type Span
  @table(key: ["id"])
  @index(fields: ["groupId", "startDate"], order: [ASC, DESC]) {
  id: String!
  document: Document! @ref @index # @ref does NOT auto-index the FK column
  group: Group @ref @index
  labelRaw: String
  startDate: Date @index
  endDate: Date
  personId: String
  tags: [String] @index(type: GIN)
}

And a server endpoint that queries it with the user's date window. The overlap condition is the whole feature: a row counts if it started before the window closed and had not ended when the window opened, with an open-ended row treated as still running.

query WindowSnapshot($start: Date!, $end: Date!) {
  spans(
    where: {
      _and: [
        { labelRaw: { isNull: false } }
        { startDate: { isNull: false } }
        { startDate: { le: $end } }
        { _or: [{ endDate: { ge: $start } }, { endDate: { isNull: true } }] }
      ]
    }
    limit: 100000
  ) {
    labelRaw
    personId
    startDate
    endDate
    document {
      id
      isArchived
    }
    group {
      label
    }
  }
}

The canonical label comes back on each row, so the response is the snapshot. The page hook takes it and returns early. No corpus download, no aggregation build, no clustering, no label calls.

We chose Firebase SQL Connect (formerly Firebase Data Connect, renamed April 2026, CLI still dataconnect) mostly because of what it let us avoid. It is managed Cloud SQL underneath with a GraphQL layer on top and Firebase Auth wired in natively, so auth.uid is available inside every operation. Our sessions, middleware and access rules were all built on Firebase Auth, and that was the one part of the system that was not broken. Moving data and auth together would have been two hard migrations in one project.

The swap itself was small. The UI never spoke to Firestore. Everything went through one interface, so we wrote a SQL implementation of it and ported the few places that reached for the Firestore admin SDK directly.

The numbers

Measured on 18 September, production against production, same app, same hosting, compression on both sides.

One run each, no throttling. Trust the shape, not the exact numbers.

The three-year row is the cold load, three years being the default window, so it carries the cost of booting the page as well as fetching data. That is why it is the slowest row in the after column while being the smallest window. The other four rows are filter changes on a page that is already open, so read down that group and not across the whole table.

The ten-year row is 15 times faster, but the useful column is the curve. Take the filter changes on their own. Before, more years meant more waiting. One year was 10 seconds. Ten years was 56. After, those same four windows all finish in about three seconds, and not even in size order. The data grows but the time stops caring.

The rest, same two runs:

• Main thread at ten years: about 55.7s blocked, down to 542ms.
• Label calls per filter change: 10 to 16, down to zero.
• A second page built the same way, a grid assembled from the corpus in the browser, is server-rendered now: DOMContentLoaded 14.4s down to 1.3s, while holding more rows than before.
• The search box corpus: 18.27 MB decoded, down to 1.63 MB. We didn't remove the preloader. We gave it twelve fields per resume instead of every field of every resume. The cold-load HTTP 500 from August went with it, since nothing asks for the fat payload any more.
• Eight full page loads in one manager session: about 146 MB of decoded JSON (roughly 21 MB over the wire), down to 1.63 MB decoded, fetched once. These were hard loads, not in-app clicks.

Vector search should be better on an HNSW index than on the Firestore scan we replaced, but I haven't measured it on either side in production. And the aggregation page picked up a DOMContentLoaded regression (2.5s to 3.7s) that I still can't explain. TTFB is 62ms, so the server is not the problem. It sits between first byte and hydration, which is part of what that three-year cold load is carrying: the page spends longer getting ready to ask for data than it spends fetching it.

What I would do differently

Plan the derived tables, the server-side reads and the indexes as part of the migration, in the same pull requests, measured together. Not as a follow-up ticket that everyone assumes someone else will handle.

And trust rendered output over row counts. Four bugs got past us because the counts matched. Firestore stores vectors as a native VectorValue, so Array.isArray() returns false and our length check silently skipped all 6,500 embeddings while the import reported success. upsertMany validates against the full input type, so partial writes need the generated _update mutation instead. The old aggregation had two years of business rules in it (skip rows without a parseable start date, exclude archived resumes, cluster by the raw label rather than by the related id) and none of them live in the data. And toISOString().slice(0, 10) converts to UTC first, so a date entered as midnight in Amsterdam on 1 May was stored as 30 April. Row counts matched on every one of those.

The part that is not finished

The aggregation page answers in one query now. The grid page went from fourteen seconds to one. So when I re-measured everything, I expected to be done.

/overview, for a manager, still downloads the whole visible resume corpus, because the metric tiles are still counted in the browser. That corpus is 22.98 MB decoded, 3.24 MB over the wire today. It was 18.27 MB decoded in July. The resume count grew by under ten percent in that window. The payload grew 26%, because the bodies got richer, not just more numerous. The one call I hadn't fixed is now more expensive than it was before the migration started, and /overview is quietly the slowest page in the app at 6.5 seconds.

It is the same bug this article is about, on the page I look at most often, and I found it only because I measured instead of assuming I was finished. The server-side query that would fix it has been possible since the day we cut over. Nobody has written that query. That includes me.

Takeaway

A database migration does not make anything faster. It changes which queries you are allowed to write. If the slow thing is your read model, and it usually is, the read model is the actual work.