I Was Fetching Full Post Bodies Just to Count Words

I noticed the writing archive was slow the way you notice a bus is late — after you've already been standing at the stop for a while, getting quietly more annoyed. The page was loading in about three seconds on a fresh request. Not on slow connectivity, not during a traffic spike. Just consistently, every time, three seconds. For a list of blog posts.

Not a video. Not a stock ticker. Thirteen titles, their descriptions, and how many minutes each one takes to read.

Something was wrong.

The portfolio's /writing page is a Next.js server-rendered route backed by Neon PostgreSQL. Posts live in Payload CMS, which stores content as Lexical rich text — a JSONB tree in the database. The archive fetches a page of posts, renders the list, and according to the code, caches it for sixty seconds.

Or so I thought.

My first suspicion was the GitHub integration. I'd wired up an API connection on the work page to pull recent commits. Maybe something was bleeding over into the writing page somehow?

It wasn't. I traced the data dependencies: the GitHub calls are completely isolated to the work page, running in their own Promise.all(). The writing archive has zero GitHub dependency.

So I read the query.

The getPosts function selected a column called content.

This is the full post body. The entire Lexical JSONB tree for every post — paragraphs, headings, code blocks, emphasis — serialized to the database when the post was saved. A 1,200-word essay is a meaningful chunk of JSON.

And the archive page, which displays nothing but titles and "6 min read" labels, was fetching all of it.

Why? Because there's a countWords function that walks the tree:

typescript
function extractText(node: LexicalNode): string {
  if (node.text) return node.text
  return (node.children ?? []).map(extractText).join(' ')
}

function countWords(content: LexicalContent): number {
  return (extractText(content.root).match(/\S+/g) ?? []).length
}

It recursively walks every node, collects text, counts whitespace-separated tokens. For every post, on every request.

I also found why the revalidate = 60 ISR setting wasn't helping: the page reads searchParams — even an empty one — which forces Next.js into fully dynamic rendering regardless of any cache hints. The live response headers confirmed it: x-vercel-cache: MISS, cache-control: no-cache. Every request was fully dynamic, hitting Neon fresh each time.

So the full cost was paid on every load: transfer every post body from Neon, walk every tree, serve HTML.

Three seconds starts making sense.

The Fix Is Not the Query

My first instinct was "optimize the query." Add an index somewhere. Restructure the SQL.

But the real problem isn't that the query is slow. The problem is that the archive is asking for something it doesn't need — the full post body — in order to compute something it could simply store.

Word count is a derived value. It comes from the post content. But content only changes when a post is saved. Which means computing word count at read time — on every archive request — is the wrong moment to do that work.

The right moment is write time: once, when the post is saved in the CMS.

I added a wordCount field to the Posts collection in Payload with a beforeChange hook:

typescript
{
  name: 'wordCount',
  type: 'number',
  defaultValue: 0,
  admin: { readOnly: true },
  hooks: {
    beforeChange: [({ data }) => countWords(data?.content)],
  },
}

When a post saves, the hook computes word count from the Lexical content and persists the result. One computation, stored. Every subsequent archive load reads an integer instead of a document.

Then I generated a database migration. Payload's migration system produces the UP and DOWN SQL automatically from schema changes — including adding a parallel version_word_count column to _posts_v, the versions table — and it ran in 450ms on the production database.

Then the backfill script: populate word_count for the 15 posts that existed before the field was added. The field hook only fires on future saves, so existing posts needed a one-time pass:

plaintext
Post #31 "The :not() Specificity Trap…": 1,247 words
Post #30 "Gradual Typing Won…": 975 words
Post #6 "I Escaped Windows…": 1,264 words
...
15 / 15 backfilled.

I verified the numbers against what the old recursive walk had been producing. Identical algorithm, different moment in time. They matched.

With word_count populated, I dropped content from the archive query and added word_count::int:

sql
SELECT id, title, slug, published_at, word_count::int,
       meta_description
FROM posts
WHERE published_at IS NOT NULL
ORDER BY published_at DESC

The ::int cast is important. PostgreSQL's numeric type comes back as a string in the Node.js database driver. The application expected a real number. Without the cast, the word counts would have been "1247" instead of 1247, and the downstream formatting would have broken silently.

I also wrapped the archive data fetches in unstable_cache with tag-based revalidation. The CMS already fires a webhook to /api/revalidate when posts change. I added revalidateTag('writing', 'max') to that endpoint so the cache gets busted on post updates, and the page stops being fully dynamic.

One small surprise: revalidateTag('writing') with a single argument threw a TypeScript error — "Expected 2 arguments, but got 1." The function signature changed in a newer Next.js release to require a second cache profile parameter. It's the kind of thing you don't notice until the compiler tells you.

The Part Worth Being Honest About

The migration was where I held my breath.

Payload's generator did what it should — column added, rollback ready — and it ran in 450ms without incident. But "ALTER TABLE on a production database" is always a small moment of prayer regardless.

The backfill also needed to update _posts_v, not just posts. Otherwise the admin sidebar would show 0 word count for all existing posts until each one was manually re-saved. A small thing, easy to miss, worth getting right.

What I didn't do well: I spent time tracing the GitHub dependency before I read the actual query. In hindsight, looking at what the SQL selects should have been the first thing. The column name content in a list query is a flag. "Why does a list need the full content?" is the question I should have asked immediately.

The performance problem was sitting in plain sight, one keyword into the select clause. I just had to read the right file first.

On Denormalization Getting a Bad Name

"Avoid duplicating data" is practically the first rule of relational database design. And for most data, it's correct — duplication creates update anomalies and makes keeping things consistent a real headache.

But word_count isn't arbitrary duplication. It's a derived value that changes only when the source changes, and changing the source is an explicit, hookable event with a clear boundary. The duplication is controlled and intentional.

This pattern has a name: denormalization as a performance optimization. It's not a shortcut or a hack. It's the recognition that "compute once, read many" is the right trade when the read-to-write ratio is high enough — which, for a blog archive, is essentially infinite. Nobody is editing posts in the same second someone is loading the archive.

The word count field is now a stored fact, not a recomputed result. It's wrong the same way a price tag is wrong if the price changes and nobody updates the label. But in a CMS, updating the label is the hook's job, and hooks run on every save.

It's also worth noting what this didn't require: no schema redesign, no caching infrastructure invented from scratch, no new database. Just a field, a hook, a migration, and a backfill. The CMS already had the place to put the computation; I just hadn't used it.

The archive page now reads integers instead of documents.

The hook computes word count once at save time; every reader after that pays the cost of one integer field. The recursive tree walk that was running on every request is now a thing that runs only when someone hits "publish."

I could have gotten there faster if I'd looked at the query before forming any theories about what was slow. The bottleneck wasn't somewhere subtle. It was hiding behind a column name, exactly where the data was flowing, one line into the select statement.

Next time a page is slow, I'm reading the SELECT clause before I touch anything else.