DEV Community: Rasik

Your Database MCP Is Lying to You

Rasik — Sat, 25 Jul 2026 20:26:41 +0000

Here's a conversation I've had more than once.

Me: How many orders use express delivery?

Agent: (runs `SELECT count() FROM orders WHERE details ->> 'express_delivery' = 'true'`)*

Agent: None. No orders in the table use express delivery.

The real answer was 2,317.

The agent didn't hallucinate. It ran a real query against a real database and reported the real result. The query was just wrong — and this is the part that matters — nothing anywhere in the stack was capable of noticing.

Why it lies

Ask your MCP server to describe the table and it tells you the truth, as far as it goes:

 column_name | data_type
-------------+-----------
 id          | uuid
 details     | jsonb

jsonb. That's the entire contract. That column could hold {} in every row or a nine-level fulfillment tree with 400 distinct paths, and information_schema genuinely cannot tell you which — because as far as Postgres's type system is concerned, jsonb is one scalar type. The structure lives in the value, not the type. Nobody ever told the catalog.

So the model does the only thing available to it: pattern-matches a plausible key name out of the column name and whatever's floating around the conversation. It writes express_delivery. The data says:

fulfillment.shippingV2.expressDelivery

Snake case or camel case. Flat or nested. Singular or plural. Three independent coin flips, and it needed to win all three.

Why nothing catches it

If a wrong guess threw an error, none of this would matter. The agent would see the error, adjust, try again. That loop works fine — it's most of what agents do all day.

But -> on a missing key returns NULL. NULL = 'true' evaluates to NULL. NULL filters the row out. So a wrong guess returns zero rows, which is a pixel-perfect impostor of "no data matches."

That's the whole failure. Not a bad model, not a bad prompt — a severed feedback loop. Every wrong guess looks identical to a correct query on an empty result set, so the model has no signal to self-correct. It reports its guess as a finding, with the serene confidence of a weather presenter. And then somebody puts the number in a deck.

Worth sitting with: you have exactly the same problem. You just have Slack history and a colleague named Dave to compensate. The agent has neither. (Dave, in my experience, has also left the company.)

The fix that doesn't work

The obvious move is to hand the model some rows and let it work the shape out:

SELECT details FROM orders LIMIT 20;

This is worse than it looks. Twenty 40KB documents is tens of thousands of tokens spent on a bad inference — the model sees whichever paths happened to occur in those particular twenty rows, with no frequency information, no type consistency check, and no concept of what it missed. Optional fields look mandatory. Rare fields don't exist at all. And you've burned half your context window getting there.

You're shipping the data to the reasoning. The data is four orders of magnitude bigger than the answer.

The fix that works

Make the database do the aggregation and hand the model the conclusion.

jsonb isn't text — it's a parsed binary structure, and Postgres can iterate it without re-parsing anything. Three primitives are all you need: jsonb_each, jsonb_array_elements, and jsonb_typeof. Wire them into a recursive CTE and the entire walk runs server-side:

WITH RECURSIVE walk AS (
    SELECT s.rid, e.key AS path, e.value, 1 AS depth
    FROM sample s, LATERAL jsonb_each(s.details) e
    WHERE jsonb_typeof(s.details) = 'object'
    UNION ALL
    SELECT w.rid, w.path || child.suffix, child.value, w.depth + 1
    FROM walk w
    CROSS JOIN LATERAL (
        SELECT '.' || e.key AS suffix, e.value
        FROM jsonb_each(w.value) e WHERE jsonb_typeof(w.value) = 'object'
      UNION ALL
        SELECT '[]', el.value
        FROM jsonb_array_elements(w.value) el WHERE jsonb_typeof(w.value) = 'array'
    ) child
    WHERE w.depth < 8
)
SELECT path,
       array_agg(DISTINCT jsonb_typeof(value)) AS types,
       count(DISTINCT rid)                     AS occurrences
FROM walk GROUP BY path;

That CROSS JOIN LATERAL in the recursive term isn't stylistic. SQL allows exactly one self-reference in a recursive term, and objects and arrays are two different descent rules — unioning them inside a lateral subquery is what makes the whole thing legal.

What comes back is one row per distinct path instead of one row per document:

                  path                   |   types   | occurrences
-----------------------------------------+-----------+-------------
 fulfillment.shippingV2.enabled          | {boolean} |         973
 fulfillment.shippingV2.expressDelivery  | {boolean} |           3
 lineItems[].sku                         | {string}  |         998
 status                                  | {string}  |        1000

Four lines. A few hundred tokens. The mystery from the top of this post solves itself on sight.

Three things turn that from clever into practical:

Sample by page, not by row. TABLESAMPLE SYSTEM — or system_rows if the contrib extension is installed — reads random pages instead of scanning, so cost scales with your sample rather than your table. ORDER BY random() is a full scan wearing a trench coat; it reads all 52 million rows to hand you a thousand.

Stop on convergence. Profile in batches, count newly-discovered paths per batch, stop when a few consecutive batches turn up nothing new. Production JSON is emitted by a finite amount of application code, so the discovery curve is steep — most tables plateau by row 300–500.

Report your uncertainty. Sampling will miss the key that appears in 3 rows out of 52 million, and the consumer needs to know that:

"sampling": {
  "method": "system_rows",
  "rows_scanned": 1500,
  "estimated_total_rows": 52000000,
  "converged": true,
  "missing_mass": 0.004,
  "note": "frequency_pct is computed over the sample, not the whole table"
}

missing_mass there is Good–Turing: singleton paths divided by documents sampled. 0.004 means roughly one document in 250 carries a path you haven't seen — comfortably fine to write queries against. 0.20 means the table is heterogeneous and your map isn't trustworthy yet. A profiler that reports "complete" after touching 0.003% of a table is worse than no profiler at all, because it launders a known unknown into an unknown unknown.

Wire it in as a tool

Expose that as an MCP tool and the loop closes:

mcp.NewTool("profile_json_columns",
    mcp.WithDescription("Infer json/jsonb structure (key paths, types, frequency, examples); block-samples to convergence."),
    mcp.WithString("schema", mcp.Required()),
    mcp.WithString("table", mcp.Required()),
    mcp.WithString("column"),
)

The model calls it, gets the real path map, and writes details #>> '{fulfillment,shippingV2,expressDelivery}' on the first attempt instead of the fourth. Database does the aggregation, model gets the conclusion, context window survives.

The bug report nobody asked for

Profile a column and you get one for free. When a single path comes back as "types": ["number", "string"], you have both {"amount": 1999} and {"amount": "19.99"} sitting in production — two writers, two conventions, one storing integer cents and one storing dollar strings. A latent cast error stacked on a latent 100x pricing bug, discovered by asking an entirely unrelated question.

Frequency reads as a design review too. A path at 100% isn't optional data; it's a column in witness protection, and ALTER TABLE ... GENERATED ALWAYS AS will let it out. A path under 1% is usually somebody's abandoned experiment.

The short version

Your agent isn't lying on purpose. It's answering from an empty catalog, and jsonb fails silently, so nothing in the loop can distinguish a wrong query from no data. Fix the catalog problem and the lying stops:

Walk the JSON server-side. Recursive CTE over jsonb_each / jsonb_array_elements. Gigabytes stay where they are; kilobytes come back.
Sample by page. TABLESAMPLE SYSTEM makes cost a function of the sample, not the table.
Converge, and admit what you missed. Watch new-path discovery, estimate missing mass, never claim completeness you didn't verify.

The same argument holds with no AI anywhere in the picture, incidentally. This is just what \d+ for jsonb would look like if Postgres shipped one.

Want the full treatment? I wrote a considerably longer version that goes into the storage layer, why the query planner is equally blind (contsel returns a hardcoded 0.001 for every containment query, regardless of your data), the four non-obvious details hiding in that recursive CTE, why ctid isn't unique across partitions and will silently corrupt any dedupe built on it, and the statistics behind convergence:

The Column No LLM Could See

Implementation and MCP wiring: github.com/rasikraj01/psql-json-profiling-mcp

Either way: go find out what's actually in that column. I promise it's weirder than you think.

The Column No LLM Could See

Rasik — Sat, 25 Jul 2026 20:22:56 +0000

1. The number that was wrong

It started with a question I didn't think was hard.

Me: How many orders use express delivery?

Agent: (runs `SELECT count() FROM orders WHERE details ->> 'express_delivery' = 'true'`)*

Agent: None. No orders in the table use express delivery.

I would have believed it. That's the part I keep coming back to. I had no particular reason to doubt it, the query looked like a query I'd have written, and "we launched express delivery and nobody's using it" is an entirely plausible fact about a business.

I only checked because I happened to be looking at a support ticket from someone using express delivery.

SELECT count(*) FROM orders
WHERE details #>> '{fulfillment,shippingV2,expressDelivery}' = 'true';
-- 2317

Two thousand three hundred and seventeen. So the agent hadn't hallucinated — it ran a real query against a real database and faithfully reported the real result. The query was just wrong, and nothing in the entire stack had been capable of noticing.

That's the thread I pulled on. This is where it went.

2. Nobody knows what's in there

First question: how was the agent supposed to know the right path? So I asked the database the same thing the agent had.

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders';

 column_name | data_type
-------------+-----------
 id          | uuid
 details     | jsonb

jsonb. That's it. That's the entire contract.

That column could hold {} in every row, or a nine-level fulfillment tree with 400 distinct paths, and the catalog genuinely cannot tell you which — because as far as the Postgres type system is concerned, jsonb is a single scalar type. The structure lives in the value, not the type. Nobody ever told the catalog anything, because there was never a place to write it down.

So the agent did the only thing available to it: pattern-matched a plausible key name out of the column name and the surrounding conversation. It guessed express_delivery. The data said fulfillment.shippingV2.expressDelivery. Snake case or camel case, flat or nested, singular or plural — three independent coin flips, and it needed all three.

But losing the coin flips isn't the interesting part. This is:

SELECT * FROM orders WHERE details ->> 'express_delivery' = 'true';
-- 0 rows

Not an error. Not a type mismatch. Zero rows.

-> on a missing key returns NULL. NULL = 'true' evaluates to NULL. NULL filters the row out. So every wrong guess produces a result set that is pixel-identical to a correct query that found nothing.

I sat with that for a while, because it reframes the whole problem. The agent wasn't lying. It had no feedback loop. A wrong query and a correct-but-empty query are indistinguishable from the inside, so there was no error to react to, no signal to self-correct on. It reported its guess as a finding with the serene confidence of a weather presenter, and it would have kept doing that forever.

And — uncomfortably — so would I have. I make the same class of guess. I just have Slack history and a colleague named Dave to compensate, and the agent has neither. (Dave, in the finest tradition of the field, has since left the company.)

2.1 The planner can't see it either

Somewhere in here I got curious about whether anything in the system knew the shape of that column. Postgres runs ANALYZE, so surely it collected something?

SELECT attname, n_distinct, most_common_vals IS NOT NULL AS has_mcv
FROM pg_stats WHERE tablename = 'orders' AND attname = 'details';

 attname | n_distinct | has_mcv
---------+------------+---------
 details |      -0.91 | t

Statistics! Except — look at what they're statistics of. Postgres computed those over whole jsonb blobs, because jsonb happens to have an equality operator and a sort order, so ANALYZE dutifully answered the question "how often does this exact 40KB document appear," which is a question no human being has ever asked. Per-path statistics: none. Nothing anywhere knew that status is "delivered" in 91% of rows and "refunded" in 2%.

Which explains something I'd been vaguely annoyed by for months:

EXPLAIN SELECT * FROM orders WHERE details @> '{"status":"refunded"}';

 Seq Scan on orders  (cost=0.00..6120.00 rows=620 width=744)
   Filter: (details @> '{"status": "refunded"}'::jsonb)

620 rows out of 620,000. Exactly 0.1%. And I do mean exactly — that's not an estimate, it's a constant. The selectivity function behind @> is contsel, and contsel returns DEFAULT_CONTAIN_SEL = 0.001 unconditionally, every single time. It's right there in selfuncs.c, no data-dependent branch anywhere in it.

So the planner gives the same answer for status = 'delivered' at 91% of rows as it does for a key appearing twice in the whole table. Feed that fiction into a join and it picks a nested loop where it needed a hash join, and your p99 goes somewhere you'll be writing an incident report about.

At this point the shape of the problem had changed for me. This wasn't an agent problem. Three separate consumers of that column — me, the query planner, and the LLM — were all flying blind, for exactly the same reason, and none of us had noticed because none of us had anything to be wrong against. Nobody wrote the schema down, so everybody guesses. We'd built a small, quiet consensus of ignorance and been running it in production for two years.

The obvious move was to write the schema down. Sample some rows, walk the JSON, emit a map of every key path with its types and how often each one shows up.

That operation needs a name, and the one I've settled on is JSON profiling — schema inference over a schema-on-read column. Basically the \d+ that Postgres would ship for jsonb if jsonb had feelings.

It sounds like an afternoon. Reader, it was not an afternoon.

3. Afternoon one, in which I nearly take out staging

The first version was twenty minutes of work, which should have been my first clue. Nothing that matters is twenty minutes of work.

SELECT details FROM orders ORDER BY random() LIMIT 1000;

Unmarshal in Go, recurse over map[string]any, accumulate paths into a set. I wrote it, pointed it at a staging replica of the 52-million-row table, and went to make coffee.

It was still running when I got back. That was the first hint that I had not thought about this correctly. Three distinct things were wrong, and any one of them alone would have been disqualifying.

ORDER BY random() is a full scan wearing a trench coat. There is no index on Earth that satisfies a random ordering. Postgres reads every row, assigns a random number to each, sorts all 52 million of them, hands you the top 1,000, and throws away 99.998% of the work it just did. I asked for a sample and paid for a census.

I was detoasting the entire column for fun. jsonb values above roughly 2KB get pushed out-of-line into the TOAST relation, compressed — pglz by default, lz4 if you've set default_toast_compression on PG14+. Reading the column means fetching every chunk from the TOAST table and decompressing it. So selecting details for 1,000 rows of 40KB payloads is ~40MB of decompression, then ~40MB across the wire, then ~40MB of encoding/json allocations in my process — to produce a few kilobytes of path names.

That ratio is what actually stopped me. I was moving the data to the code, and the data was four orders of magnitude bigger than the answer.

And the walk itself was the slowest possible walk. Go's encoding/json into map[string]any allocates an interface{} per node and a map per object. Pointed at deeply nested order documents, the unmarshalling dominated everything else and pushed the process into GC pressure it had no business being in.

Three problems, one root cause, and once I saw it I couldn't unsee it: the walk was happening in the wrong place.

4. Moving the walk into the database

Here's the thing I'd been forgetting about jsonb: it is not text.

It's a parsed binary structure — a header, an array of JEntry offset/type words, then the values, with object keys pre-sorted by length then bytewise so that key lookup is a binary search. It's all laid out in jsonb.h if you enjoy reading header files, and I do. The operative consequence is that Postgres can iterate that structure without parsing anything, and it exposes exactly the three primitives a tree walk needs:

Function	Yields
`jsonb_each(jsonb)`	`(key text, value jsonb)` for each object member
`jsonb_array_elements(jsonb)`	`value jsonb` per array element
`jsonb_typeof(jsonb)`	`object`, `array`, `string`, `number`, `boolean`, `null`

Which means the whole traversal can be a WITH RECURSIVE CTE, and the only thing that ever crosses the wire is a GROUP BY aggregate. One row per distinct path — not one row per document.

If there's one idea in this entire post, it's that one. Cost stops being a function of payload size (gigabytes) and becomes a function of distinct path count (hundreds). Everything after this section is refinement.

So I wrote it:

WITH RECURSIVE walk AS (
    SELECT s.rid, e.key AS path, e.value, 1 AS depth
    FROM sample s, LATERAL jsonb_each(s.details) e
    WHERE jsonb_typeof(s.details) = 'object'

    UNION ALL

    SELECT w.rid, w.path || '.' || e.key, e.value, w.depth + 1
    FROM walk w, LATERAL jsonb_each(w.value) e
    WHERE jsonb_typeof(w.value) = 'object'

    UNION ALL

    SELECT w.rid, w.path || '[]', el.value, w.depth + 1
    FROM walk w, LATERAL jsonb_array_elements(w.value) el
    WHERE jsonb_typeof(w.value) = 'array'
)
SELECT path, array_agg(DISTINCT jsonb_typeof(value)), count(*)
FROM walk GROUP BY path;

And Postgres said no.

ERROR:  recursive reference to query "walk" must not appear more than once

4.1 One self-reference

This is a real constraint, not a Postgres quirk — the SQL standard permits exactly one reference to the recursive CTE inside its own recursive term. And I needed two, because descending into an object and descending into an array are two different rules.

I spent an evening arguing with that error message before the shape landed. You don't need two branches that each reference walk. You need one branch that references walk, joined against a subquery that produces children by either rule:

WITH RECURSIVE walk AS (
    -- Seed: top-level object keys
    SELECT s.rid, e.key AS path, e.value AS value, 1 AS depth
    FROM sample s, LATERAL jsonb_each(s.details) e
    WHERE jsonb_typeof(s.details) = 'object'

    UNION ALL

    -- Seed: top-level array elements
    SELECT s.rid, '[]', el.value, 1
    FROM sample s, LATERAL jsonb_array_elements(s.details) el
    WHERE jsonb_typeof(s.details) = 'array'

    UNION ALL

    -- Recursive term: exactly one reference to walk
    SELECT w.rid, w.path || child.suffix, child.value, w.depth + 1
    FROM walk w
    CROSS JOIN LATERAL (
        SELECT '.' || e.key AS suffix, e.value
        FROM jsonb_each(w.value) e
        WHERE jsonb_typeof(w.value) = 'object'
      UNION ALL
        SELECT '[]', el.value
        FROM jsonb_array_elements(w.value) el
        WHERE jsonb_typeof(w.value) = 'array'
    ) child
    WHERE w.depth < 8
)
SELECT
    path,
    array_agg(DISTINCT jsonb_typeof(value))                  AS types,
    count(DISTINCT rid)                                      AS occurrences,
    (array_agg(DISTINCT left(value::text, 80))
        FILTER (WHERE jsonb_typeof(value) NOT IN ('object','array')))[1:3] AS examples
FROM walk
GROUP BY path;

The CROSS JOIN LATERAL unions both descent rules into a single relation, so walk appears once and both paths still fire. That trick is what makes the entire approach viable, and I'd love to tell you I derived it from the standard.

4.2 Three more things I got wrong first

The query compiled. It also gave me wrong answers three times before it gave me right ones.

Occurrence counts were absurd. lineItems[].sku was showing up in 12,000 "documents" out of a 1,000-row sample. Of course it was — a path inside an array produces one walk row per element, and an order with 12 line items yields 12 rows. Frequency only means anything per-document, so: count(DISTINCT rid), never count(*). Obvious in retrospect. It always is.

Path cardinality exploded. My first version kept array indices, so lineItems[0].sku and lineItems[47].sku were different paths and a table with big arrays produced thousands of useless distinct entries. Collapsing every index to [] fixes it and has a nice second property: [] is the notation that maps straight back onto SQL, via jsonb_array_elements or details @? '$.lineItems[*].sku'.

One document ate all the memory. No depth limit, one pathological deeply-self-nesting blob, and the CTE ran until it hit the memory limit and took the connection with it. depth < 8 is a circuit breaker, and the same reasoning applies to array fan-out per node and to total distinct paths per column. Any unbounded recursion over user-supplied data is a loaded gun.

With those fixed, this came back:

                  path                   |   types   | occurrences |   examples
-----------------------------------------+-----------+-------------+---------------
 fulfillment.shippingV2.enabled          | {boolean} |         973 | {true}
 fulfillment.shippingV2.expressDelivery  | {boolean} |           3 | {true}
 lineItems[].sku                         | {string}  |         998 | {"SKU-2481"}
 status                                  | {string}  |        1000 | {"delivered"}

Four lines. A few hundred bytes. And the mystery from §1 solves itself on sight — there's the path, spelled exactly the way the agent didn't guess it.

That was the good moment. It lasted until I timed the thing.

5. Still reading 52 million rows

Moving the walk server-side had fixed the transfer problem completely. It had done precisely nothing about ORDER BY random() still touching every row in the table.

Which is where I found TABLESAMPLE, sitting in the SQL:2003 standard and in Postgres since 9.5, waiting for somebody to notice it.

Method	Granularity	Cost	Notes
`TABLESAMPLE BERNOULLI (p)`	Row	Full scan	Statistically pristine, no cheaper than a seq scan
`TABLESAMPLE SYSTEM (p)`	Page	~`p%` of pages	Random block reads. Cheap. Clustered.
`TABLESAMPLE SYSTEM_ROWS (n)`	Page, exact `n` rows	~`n/rows_per_page` pages	contrib `tsm_system_rows`
`ORDER BY random()`	Row	Full scan + sort	The trench coat

SYSTEM is the one that changes the economics: it picks pages at random and returns every row on each chosen page, so read cost scales with the sample rather than the table.

5.1 The thing that nearly stopped me

I almost didn't use it, because page-level sampling is clustered sampling and I could hear a statistics lecturer in my head. Rows sharing a page are physically adjacent, which means they correlate — same insert batch, same merchant, same time window. For estimating a mean, that inflates variance badly and the lecturer would be right to glare.

What unstuck me was noticing I wasn't estimating a mean. I was discovering the set of distinct key paths, which is a coverage problem, and 500 rows drawn from 25 scattered pages cover the schema space nearly as well as 500 independent rows would. The clustering costs almost nothing when the question is "does this path exist anywhere."

That distinction is worth internalizing generally: SYSTEM is right for this question and wrong for plenty of others. Know which one you're asking before you reach for it.

If tsm_system_rows happens to be installed, it's strictly better — it takes a row count directly and reads pages until satisfied, so batch sizes are exact rather than approximate:

CREATE EXTENSION IF NOT EXISTS tsm_system_rows;
SELECT * FROM orders TABLESAMPLE system_rows(500);

One rule I set immediately: the profiler uses that extension if present and never, ever installs it. CREATE EXTENSION wants superuser, and a read-only diagnostic tool has no business acquiring superuser. If you catch yourself typing that, go touch grass.

5.2 Choosing at runtime

Which method to use depends on table size, and happily the planner already estimated that for free:

SELECT reltuples::bigint AS est, relkind::text
FROM pg_class
WHERE oid = (quote_ident($1) || '.' || quote_ident($2))::regclass;

Small trap in there, with a version number attached: reltuples is -1 when the relation has never been analyzed on PG14+. Before that it was 0, which is maddeningly ambiguous with "the table is genuinely empty." So -1 has to mean unknown, which is a different situation from small, and routes differently:

func chooseSamplingMethod(ctx context.Context, d db.Querier, fullScan bool, estTotal int64, canSample bool) string {
    if fullScan || !canSample || (estTotal > 0 && estTotal <= fullScanThreshold) {
        return "full_scan"
    }
    if info, err := db.CheckExtension(ctx, d, "tsm_system_rows"); err == nil && info.Installed {
        return "system_rows"
    }
    if estTotal > 0 {
        return "system_pct"
    }
    return "random" // never analyzed: no basis for a percentage
}

Below roughly a thousand rows there's no point sampling at all — scanning everything is cheaper than the machinery and gives an exact answer instead of an estimate. Just say which one you did in the output.

5.3 The partition footgun

Then I pointed it at a partitioned table and got results that were quietly, subtly wrong.

TABLESAMPLE needs a physical relation. Ordinary tables (relkind='r') and materialized views ('m') are fine. Views and foreign tables have no ctid and get rejected outright, loudly, which is the good kind of failure.

Partitioned tables ('p') are the bad kind. The parent has no storage of its own — and, the part that got me, ctid is not unique across partitions. I was using ctid to track which rows I'd already sampled, so two rows in different partitions sharing a ctid meant my exclusion list was silently dropping rows I had never actually seen. No error. Just fewer rows than I thought, and no way to tell.

func sampleable(relkind string) bool {
    return relkind == "r" || relkind == "m"
}

Detect it up front, fall back to a synthetic row_number() row id with a full scan, move on.

5.4 Batches that re-read each other

One more, from the same family. Independent batches are independent draws, which means batch three is perfectly entitled to re-read batch one's pages. On a small table that's not a hypothetical — it's the default outcome.

It corrupts two things at once. Your rows_scanned count, obviously. But worse, and I didn't spot this for a while: a batch full of already-seen rows discovers zero new paths, which looks exactly like the convergence signal I was about to build the entire stopping rule on. My tooling was about to lie to me in the same way the agent had. There's a lesson in there somewhere.

Carry the sampled row ids forward and exclude them:

WHERE details IS NOT NULL
  AND NOT (ctid = ANY($2::text[]::tid[]))

The text[] → tid[] double cast looks silly and is, in practice, how you round-trip ctid values through a driver with no native tid array type. Sometimes the pragmatic answer is the ugly one.

6. How many rows is enough?

Sampling worked. Which immediately raised the question I'd been avoiding: how many rows should it read?

Any fixed number is wrong in both directions simultaneously. 5,000 rows is wasteful on a table with 12 paths and insufficient on a table with 800. So don't fix the number — watch the signal. Profile in batches, count newly-discovered paths per batch, stop after N consecutive batches turn up nothing new.

zeroStreak := 0
for {
    rows, err := d.QueryRows(ctx, q, batchLimit, seenRids)
    if err != nil {
        return nil, fmt.Errorf("sample %s.%s: %w", schemaName, tableName, err)
    }
    report.Batches++

    batchRows, newPaths, batchRids := mergeBatch(aggs, rows)
    report.RowsScanned += batchRows
    report.NewPathsLastBatch = newPaths
    seenRids = append(seenRids, batchRids...)

    if newPaths == 0 {
        zeroStreak++
    } else {
        zeroStreak = 0
    }
    if zeroStreak >= opts.ConvergenceBatches {
        report.Converged = true
        break
    }
    if report.RowsScanned >= opts.MaxRows {
        break // budget hit before convergence
    }
}

It converged much faster than I expected, and the reason is structural rather than lucky. Production JSON isn't arbitrary — it's emitted by application code, a serializer or a checkout handler or a webhook consumer, so the universe of possible paths is bounded by a finite amount of source, and the common paths get written by every code path that touches the table. It's the coupon collector's problem with a brutally non-uniform distribution: a few hundred rows catch everything above ~1% frequency, and the long tail is optional keys, deprecated fields, and the ghosts of feature flags past. Most tables I've pointed this at plateau by row 300–500.

6.1 The 1953 paper

Fast convergence answered "when do I stop." It didn't answer the question that actually worried me, which was "what did I miss?"

Because sampling will miss the key that appears in 3 rows out of 52 million. That's not a bug, that's the deal I signed when I stopped doing full scans. But shipping a tool that quietly pretends otherwise felt like recreating the original problem in a new location.

It turns out there's a proper estimator for exactly this, it's called Good–Turing missing mass, and it is older than every other piece of technology mentioned in this post combined. Good, 1953 — the same I. J. Good who spent the war at Bletchley with Turing, now quietly solving my jsonb problem from eight decades away. (Gale and Sampson later wrote the version you can actually read.)

Let f₁ be the number of paths seen in exactly one sampled document, and N the number of documents sampled. Then:

P(next document contains an unseen path) ≈ f₁ / N

That's the whole thing. And you already have per-path occurrence counts sitting in memory, so it costs a loop:

// singletons: paths observed in exactly one sampled document.
func missingMass(paths []PathInfo, rowsScanned int) float64 {
    if rowsScanned == 0 {
        return 0
    }
    singletons := 0
    for _, p := range paths {
        if p.Occurrences == 1 {
            singletons++
        }
    }
    return float64(singletons) / float64(rowsScanned)
}

Reading it: 0.004 means roughly one document in 250 carries a path you haven't seen, which is comfortably good enough to write queries against. 0.20 means you sampled a heterogeneous table and your map isn't trustworthy yet, and you should either raise the budget or stop trusting the output. Of everything I bolted onto the naive version, this was the highest-value addition — it turns "did I sample enough?" from a vibe into a number.

Which meant the output could finally be honest about itself:

"sampling": {
  "method": "system_rows",
  "rows_scanned": 1500,
  "estimated_total_rows": 52000000,
  "batches": 3,
  "new_paths_last_batch": 0,
  "converged": true,
  "missing_mass": 0.004,
  "note": "frequency_pct is approximate (computed over the sample, not the whole table)"
}

Every consumer — human or model — needs to know whether it converged, whether the budget cut it short, whether a cap fired. A profiler that reports "complete" after touching 0.003% of a table is worse than no profiler at all, because it launders a known unknown into an unknown unknown. And when you genuinely need certainty, full_scan is still there and you pay full price for it with your eyes open.

7. What the map turned out to be good for

I'd built this to stop an agent from guessing. It immediately started earning its keep in ways I hadn't planned.

Here's the output shape:

{
  "schema": "public",
  "table": "orders",
  "columns": [{
    "column": "details",
    "data_type": "jsonb",
    "paths": [
      {
        "path": "fulfillment.shippingV2.expressDelivery",
        "types": ["boolean"],
        "occurrences": 3,
        "frequency_pct": 0.3,
        "examples": ["true"]
      },
      {
        "path": "lineItems[].sku",
        "types": ["string"],
        "occurrences": 998,
        "frequency_pct": 99.8,
        "examples": ["\"SKU-2481\"", "\"SKU-0977\""]
      }
    ]
  }]
}

The path notation isn't decorative — it maps mechanically onto SQL, which is the whole reason for choosing it:

Path	Query
`fulfillment.shippingV2.expressDelivery`	`details #>> '{fulfillment,shippingV2,expressDelivery}'`
`fulfillment.shippingV2.expressDelivery`	`details @> '{"fulfillment":{"shippingV2":{"expressDelivery":true}}}'` (GIN-indexable)
`lineItems[].sku`	`jsonb_array_elements(details->'lineItems') ->> 'sku'`
`lineItems[].sku`	`details @? '$.lineItems[*].sku'` (PG12+)
`lineItems[].sku`	`JSON_TABLE(...)` (PG17+)

7.1 The bug I wasn't looking for

The first real profile I ran on production came back with a path typed ["number", "string"], and I had to read it twice.

Both {"amount": 1999} and {"amount": "19.99"} were in that column. Two writers, two conventions — one storing integer cents, one storing dollars as a string. Every consumer downstream was carrying a latent cast error and a latent 100x pricing bug, stacked on top of each other, and it had been there long enough that nobody could remember which one came first.

I hadn't asked "is my data consistent." I got the answer anyway. Same signal shows up for ["boolean", "null"], and for the path that's sometimes an object and sometimes an array — the timeless "we turned a scalar into a list and backfilled absolutely nothing" migration.

7.2 Frequency reads as a design review

~100% — this was never optional data. It's a column that hasn't been promoted yet, and it knows it.
40–90% — genuinely optional. This is jsonb being used correctly. Leave it alone.
<1% — a dead field, someone's abandoned experiment, or a bug writing keys it shouldn't. Grep the codebase; nine times out of ten it's deletable.

7.3 Index selection stops being astrology

Cardinality and access shape happen to be exactly the inputs the index decision always needed:

-- Broad @> / ? / @? over many unpredictable paths → jsonb_ops (default)
CREATE INDEX orders_details_gin ON orders USING gin (details);

-- Only @> containment, want a smaller/faster index → jsonb_path_ops
-- Indexes hashes of full paths: ~half the size, but no ? / ?| / ?& support.
CREATE INDEX orders_details_gin ON orders USING gin (details jsonb_path_ops);

-- One hot path, high selectivity → an expression B-tree beats GIN every time
CREATE INDEX orders_status ON orders ((details ->> 'status'));

And that third one closes a loop from §2.1 in a way I found genuinely satisfying: ANALYZE collects real statistics on expression indexes. Create it, analyze, and details->>'status' = 'delivered' suddenly gets an MCV-backed estimate instead of contsel's eternal 0.001. The planner starts picking join strategies like it can see again — because now, for that one path, it can.

7.4 Promote, freeze, and test the shape

A path sitting at 100% frequency with a stable type isn't schema-less data. It's a column in witness protection. On PG12+, generated columns extract it with zero application changes — typed, indexable, always consistent:

ALTER TABLE orders ADD COLUMN status text
    GENERATED ALWAYS AS (details ->> 'status') STORED;
CREATE INDEX ON orders (status);

Profiling tells you what the shape is today; a constraint keeps it that way tomorrow:

ALTER TABLE orders ADD CONSTRAINT details_has_status
    CHECK (details ? 'status') NOT VALID;
-- NOT VALID: enforce on new rows now, backfill and VALIDATE CONSTRAINT later

For a real contract rather than a spot check, pg_jsonschema puts full JSON Schema validation inside a CHECK.

And the profile itself is a snapshot of your implicit schema, which means it can be diffed. Store it, re-profile in CI, compare. A path that changed type, a required path slipping below 100%, a new path nobody mentioned in code review — all of it becomes a visible diff today instead of an incident three weeks from now. Cheap insurance. Take it.

8. Back to the agent

Which brings this back around to the thing that started it.

An LLM writing SQL against your database has strictly less context than your greenest new hire. No tribal knowledge, no Slack archive, no "just ask Dave" — Dave, as established, is gone. All it has is the catalog, and on jsonb the catalog is an empty page. So it guesses, gets zero rows, and reports zero rows as a finding, and the failure is invisible to the model and to whoever reads its output.

Wiring the profiler in as an MCP tool closes that loop:

s.AddTool(
    mcp.NewTool("profile_json_columns",
        mcp.WithDescription("Infer json/jsonb structure (key paths, types, frequency, examples); block-samples to convergence."),
        mcp.WithString("schema", mcp.Required()),
        mcp.WithString("table", mcp.Required()),
        mcp.WithString("column"),
        mcp.WithNumber("max_rows"),
        mcp.WithBoolean("full_scan"),
    ),
    func(ctx context.Context, req mcp.CallToolRequest) (*mcp.CallToolResult, error) {
        prof, err := jsonprofile.ProfileJSONColumns(ctx, d, schemaName, tableName, opts)
        if err != nil {
            return mcp.NewToolResultError(err.Error()), nil
        }
        return jsonResult(prof)
    },
)

The model calls it, receives the real path map, and writes the correct expression on the first attempt instead of the fourth.

The token math points the same direction, which I hadn't anticipated but should have. The alternative — dumping 20 raw documents into context and letting the model infer — costs tens of thousands of tokens to infer badly: it sees whichever paths happened to occur in those particular 20 rows, with no frequency information and no concept of what it missed. Optional fields look mandatory, rare fields don't exist. A profile of the same table is a few hundred tokens, covers 1,500 rows, and arrives with explicit frequency and convergence metadata attached. The database does the aggregation; the model gets the conclusion. That's the correct division of labor, and it's the same division of labor that fixed the performance problem back in §4.

Strip the AI out entirely and the argument survives, incidentally. This is just what \d+ for jsonb would look like, if Postgres shipped one.

9. Notes from production, or: scars

Things that bit me after the part of the project where I told people it was finished.

Bound everything. Max depth (8), array elements walked per node (50), distinct paths per column (2000), examples per path (3), example string length (80 chars). One pathological document should degrade your output, never your database. And when a cap fires, report truncated: true — silent truncation is just lying with extra steps, and this entire post is about that.

Cast json to jsonb before walking. The older json type is stored as raw text and re-parsed on every function call, so walking it is dramatically slower — and it faithfully preserves duplicate keys and whitespace you never wanted. One details::jsonb fixes both.

Read-only by default. This runs against production, so act like it. BEGIN READ ONLY plus a statement_timeout: the tool physically cannot write, and cannot pin a snapshot forever when somebody inevitably points it at the 400M-row table.

Verify identifiers against the catalog, then quote them. Schema and table names get interpolated into query text — you cannot parameterize an identifier, that's just SQL. So resolve them through information_schema first, so only catalog-verified strings reach the builder, then quote_ident on the way in anyway. Both. Not either.

Build the query once. Query text is fully determined by schema, table, column set, and sampling method, none of which change between batches. Hoist it out of the loop and let the server reuse the plan.

10. What I'd tell you if we only had a minute

jsonb doesn't delete your schema. It stops writing it down, and hands every future reader the job of reconstructing it from memory — and memory, as we've established, left the company and does not answer LinkedIn messages. JSON profiling writes it back down.

Three ideas, and everything else in this post is detail:

Walk server-side. A recursive CTE over jsonb_each / jsonb_array_elements keeps gigabytes of traversal where the data already lives and sends back kilobytes. One self-reference, so union your descent rules inside a LATERAL.
Sample by page. TABLESAMPLE SYSTEM / system_rows makes read cost a function of the sample, not the table — and clustered sampling is fine when your question is coverage rather than estimation.
Stop on convergence, and report your uncertainty. Watch new-path discovery, estimate missing mass with f₁/N, and never claim a completeness you didn't verify.

I eventually packaged all of it — profiler, sampling logic, convergence loop, tests, MCP wiring — into a small Go server so my agents would stop guessing at my columns: github.com/rasikraj01/psql-json-profiling-mcp. If you'd rather build your own, everything you need is on this page.

Either way: go find out what's actually in that column. Mine had a pricing bug in it. Yours is weirder than you think.