Postgres jsonb vs Relations in Payload: The Schema Decision We Make Before the First Migration

Every Payload schema review we do for a new client starts with the same argument, usually already three Slack threads deep: half the team wants everything embedded as jsonb because it is flexible, the other half wants everything relational because it feels proper. Both halves are wrong in a specific, predictable way — and the bill arrives around 1M rows, when the admin list view that used to load in 200ms starts taking 8 seconds and somebody opens a query plan for the first time. This is the framework we run before the first migration, the index layer that keeps it honest in production, and the pg_stat_statements review that settles arguments with data instead of taste.

What Payload actually writes to Postgres

Since Payload 3.0 moved to Drizzle-backed Postgres, the mapping is more transparent than most teams realise. Every collection gets a real table. Scalar fields become real columns. Arrays, blocks, groups, and collapsible substructures become child tables (not jsonb, despite what people assume) unless you reach for a custom field or a `json` field type — which is where actual jsonb columns appear. Relationship fields become foreign-key columns plus a join table for `hasMany`. Localized and versioned content multiplies all of this into `_locales` and `_versions` sibling tables.

The practical consequence: the jsonb-vs-relations decision in Payload is really about three field types — `json` fields (true jsonb), `array`/`blocks` (child tables you query through Payload, not SQL), and `relationship` (foreign keys). Teams that treat these as interchangeable end up with schemas where the same conceptual data lives in three different storage shapes, and the admin UI performance profile becomes a lottery.

The two wrong defaults

Default one: embed everything. A product spec sheet becomes a `json` field with forty keys. Flexible, zero migrations, fast to write. Then the Head of Content asks to filter the admin list by `specs.material`, and you discover the query Payload generates does a sequential scan over jsonb extraction across 800,000 rows. We have watched this exact filter take 9.2 seconds on a collection that rendered its detail pages in 90ms — because rendering reads one row by primary key, and filtering reads all of them.

Default two: relate everything. A `tags` relationship with `hasMany: true` for values that are only ever rendered as a label next to the title. Now every list query joins a `_rels` table, every write touches two tables, and the admin UI fires a depth-populated query per row to resolve tag names. On one archive-heavy build we inherited, a 50-row admin list was issuing 51 queries because of an over-related taxonomy that should have been a `select` field with 12 options.

The decision framework we run before the first migration

1. Render-together test — is this data always read with the parent document, in the same shape, on the same screen? If yes, embed it (json or array). Spec sheets, SEO meta, address snapshots on orders.

2. Filter-on test — will anyone filter, sort, or build access control on this value in the admin UI or the Local API? If yes, it needs to be a real column or a relationship. Categories, authors, statuses, tenants.

3. Cardinality test — does the value repeat across documents? 12 fixed options is a `select`. 4,000 shared values is a relationship. 40 unique keys per document is jsonb.

4. Write-shape test — is this written once at creation (snapshot → embed) or updated independently of the parent (inventory, pricing → relate or external table)?

5. Admin-UI test — does an editor need a column, filter, or search box for this in the Payload admin? Admin filters are the first thing that seq-scans at scale; treat every admin-filterable field as an index candidate from day one.

One collection, modeled three ways

Here is the same `articles` collection shaped three ways — the shape we would refuse, the shape we would ship, and the hybrid we reach for when a value is both rendered and filtered.

// ❌ What we refuse: filterable data buried in jsonb
export const ArticlesBad: CollectionConfig = {
  slug: 'articles',
  fields: [
    { name: 'title', type: 'text', required: true },
    {
      name: 'meta',
      type: 'json', // true jsonb column
      // { category: 'engineering', authorId: 42, readingTime: 8 }
      // Filtering by meta->>'category' seq-scans at scale. Don't.
    },
  ],
}

// ✅ What we ship: filter-on data is relational, render-together data is embedded
export const Articles: CollectionConfig = {
  slug: 'articles',
  fields: [
    { name: 'title', type: 'text', required: true },
    {
      name: 'category',
      type: 'relationship',
      relationTo: 'categories',
      hasMany: false,
      index: true, // Payload emits the B-tree for you — use it
      admin: { position: 'sidebar' },
    },
    {
      name: 'author',
      type: 'relationship',
      relationTo: 'users',
      index: true,
    },
    {
      name: 'seo',
      type: 'group', // child table, rendered with the doc, never filtered
      fields: [
        { name: 'metaTitle', type: 'text' },
        { name: 'metaDescription', type: 'textarea' },
        { name: 'ogImage', type: 'upload', relationTo: 'media' },
      ],
    },
    {
      name: 'structuredData',
      type: 'json', // jsonb is fine here: written on publish, read whole, never queried
    },
  ],
}

The hybrid case deserves its own mention: sometimes a value is genuinely both — say, a denormalized `categorySlug` you render on the card without a join *and* filter on in the admin. We duplicate it deliberately: keep the relationship as the source of truth, and maintain a plain indexed `text` column via a `beforeChange` hook. It costs one hook and one column; it saves a join on every list render.

// Hybrid: denormalized lookup column kept in sync by a hook
const syncCategorySlug: CollectionBeforeChangeHook = async ({ data, req }) => {
  if (data?.category && typeof data.category === 'number') {
    const category = await req.payload.findByID({
      collection: 'categories',
      id: data.category,
      depth: 0,
    })
    data.categorySlug = category.slug // indexed text column, no join at render
  }
  return data
}

// field config:
// {
//   name: 'categorySlug',
//   type: 'text',
//   index: true,
//   admin: { readOnly: true, position: 'sidebar' },
// }

The index layer: where Payload's defaults stop

Payload's `index: true` gives you a B-tree, which is exactly right for foreign keys, slugs, statuses, and timestamps. It does nothing for jsonb. If you have a legitimate jsonb field that occasionally gets queried — a feature-flag bag, a third-party sync payload — the index that matters is a GIN index with `jsonb_path_ops`, and you add it in a hand-written migration, not in the field config. The Postgres jsonb indexing docs are clear on the trade-off: `jsonb_path_ops` is smaller and faster for containment queries, which is what `@>` filters generate.

-- migration: keep jsonb queries off the seq-scan path
-- 1) GIN for the rare-but-real containment query on a json field
CREATE INDEX CONCURRENTLY articles_structured_data_gin
  ON articles USING GIN (structured_data jsonb_path_ops);

-- 2) Partial index for the admin's hottest filter:
--    editors filter published articles by category 95% of the time
CREATE INDEX CONCURRENTLY articles_published_category_idx
  ON articles (category_id, updated_at DESC)
  WHERE _status = 'published';

-- 3) Verify the planner actually uses it
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title FROM articles
WHERE _status = 'published' AND category_id = 17
ORDER BY updated_at DESC LIMIT 50;

The receipts from the last three schemas we tuned this way: an admin list filter on a 1.1M-row collection went from p95 8.4s to 210ms with one partial index; a Local API query feeding a Next.js category page went from 380ms to 22ms after we moved a jsonb-filtered attribute into a real column; and the GIN index above costs roughly 8–12% extra write time on that table — a tax we pay happily on a publish-once-read-forever field, and one we would refuse on a hot write path.

When versioning and localization change the math

Two Payload features quietly multiply your row counts, and both punish over-relational schemas. Versions duplicate every document into `_versions` tables on every save — a 100k-article archive with 15 drafts per article is 1.6M rows before anyone filters anything. Localization splits localized fields into `_locales` tables, so a relationship that was one join is now two. Our rule of thumb: on versioned + localized collections, be *more* willing to embed render-together data and *less* willing to add decorative relationships, because every relationship's join tax is paid per locale per version. And never put a relationship inside a localized group unless an editor genuinely needs a different relation per locale — the query shape Payload generates for that is the single ugliest plan we see in client audits.

The same render-together vs filter-on logic applies one level up, when you are deciding between blocks and separate collections — we wrote that decision up separately in Payload Blocks vs Collections: The Content Architecture Decision That Breaks at 1,000 Articles.

The pg_stat_statements review we run at launch and at month three

Taste loses arguments; `pg_stat_statements` wins them. On every Payload-on-Postgres launch we enable the extension and schedule two reviews: one in launch week, one at month three when real editorial behaviour has accumulated. The query is boring and that is the point.

-- The five queries eating your database, normalized
SELECT
  calls,
  round(mean_exec_time::numeric, 1) AS mean_ms,
  round(total_exec_time::numeric / 1000, 1) AS total_s,
  left(query, 140) AS query_preview
FROM pg_stat_statements
WHERE query ILIKE '%articles%'
ORDER BY total_exec_time DESC
LIMIT 5;

What we do with the top five rows: anything with high `calls` and low `mean_ms` is a hot path — check its indexes and its `depth` setting in the Local API call. Anything with low calls and high `mean_ms` is usually an admin filter or a report — partial-index territory. Anything showing `jsonb` operators in the preview goes straight onto the 'should this be a column?' list. This review has never taken more than two hours and has never failed to find at least one 10x win.

What we would not do

We would not do schema gymnastics before traffic. A 5,000-row collection does not need a denormalized lookup column or a partial index — it needs `index: true` on the two fields the admin filters by, and a calendar reminder to run the month-three review. And we would not reach for a query-planner model or an ML index advisor as a substitute for the three indexes above; the Payload database docs and `EXPLAIN ANALYZE` get you 95% of the way with tools you already have. Cleverness is a liability at the schema layer — boring columns, boring B-trees, one GIN where it earns its keep.

What we ship by default on every Payload + Postgres project that matches this shape: `index: true` on every admin-filterable relationship, a hand-written migration for any jsonb field that will ever be queried, the denormalized-slug hook wherever a list view renders related labels, and the pg_stat_statements review booked before launch day. That combination has kept every admin list we have shipped under 300ms at seven-figure row counts — without a single emergency index added in production.

If your schema is already past the point where a migration is scary, See how we build on Payload CMS — schema design and production hardening are the core of that practice.

Designing a Payload schema that has to survive 1M+ documents? Send us the schema — we will tell you where we would draw the line.