json / jsonb: implementation plan

Written after differential testing against PostgreSQL 17 and reading the PostgreSQL, Datalevin, konserve and boring sources. Companion to backlog.md, which lists the defects this plan fixes.

The organising idea: decide what a value IS before deciding how to store it. The encoding (canonical text vs CBOR bytes) is a codec choice behind an interface; the value model is not, and it is what the correctness bugs are actually about.

---

1. The value model

PostgreSQL's own answer is JsonbValue (src/include/utils/jsonb.h), and the type tag carries the comment /* Influences sort order */ — the enum order is the ordering:

Null  <  String  <  Numeric  <  Bool  <  Array  <  Object

variant	notes
JSON null	a VALUE, not an absence. IS NULL on it is false; jsonb_typeof says null. Distinct from SQL NULL and from our :__null__ sentinel.
String	Unicode text. � rejected (22P05); raw control bytes must be escaped. Cap ~256 MB (JENTRY_OFFLENMASK).
Number	arbitrary-precision decimal carrying a display scale. Equality and ordering IGNORE scale; rendering HONOURS it. Never NaN/Infinity — PostgreSQL stringifies those.
Boolean	false < true.
Array	ordered, duplicates allowed.
Object	keys unique, last-wins at construction; stored order is length-first then bytewise over UTF-8.

Plus one wrinkle: a top-level scalar is physically a 1-element array with a rawScalar flag. We do not need to store that — "the top-level value is a scalar" is already in the model — but it leaks into observable behaviour and we must decide per case (§5).

Clojure realization

• Number → java.math.BigDecimal, uniformly. Never Long, never Double. Clojure's = and hash on BigDecimal are scale-INsensitive ((= 1.00M 1M) is true, hashes agree), which reproduces PostgreSQL's numeric_eq for free — and therefore DISTINCT and GROUP BY too. This only holds if the representation is uniform: (= 1 1M) is false, so mixing Long and BigDecimal silently breaks equality. The invariant is load-bearing, not stylistic.
• JSON null → its own sentinel, distinct from nil and :__null__.
• Object → map with String keys; PostgreSQL's key order is a pure function of the key set, so derive it at render rather than storing it.
• Array → vector. String → String. Boolean → Boolean.

Traps

• BigDecimal.hashCode() is scale-SENSITIVE (1M → 31, 1.00M →
  3102. while clojure.core/hash is not (both 31). The equality invariant survives only through clojure.core/hash; any java.util.HashMap/HashSet in the path breaks it silently.
• BigDecimal is strictly more permissive than PostgreSQL numeric (dscale ≤ 16383, integer digits ≤ 131072, else 22003). We would accept documents PostgreSQL rejects without an explicit range check.
• Datahike's compare-value is scale-blind. At :db.cardinality/many this collapses scale-distinct values. Verified that cardinality-one is unaffected — last write wins, scale survives, with and without :db/index — and every SQL scalar column is cardinality-one.

---

2. Equality, ordering, hashing

jsonb_eq is not a bespoke equality: it is compareJsonbContainers(...) == 0, i.e. the ordering function, which compares strings with varstr_cmp under the database collation.

It nevertheless reduces to bytewise, because varstr_cmp returns 0 iff the bytes are equal and the default collation is always deterministic (nondeterministic collations are barred from operator classes). That is what makes the following safe:

Pinning to C collation cannot change any equality-derived result. =, DISTINCT, GROUP BY, hash aggregation, @>, ?, key lookup and uniqueness are all unaffected. It changes exactly one thing: the SEQUENCE of ORDER BY / < / > / MIN / MAX over jsonb containing strings.

And jsonb is not collatable (typcollation = 0; ORDER BY j COLLATE "C" is an error), so a client cannot ask for anything else. Decision: pin to C, make it a documented knob. Reproducing glibc collation in Clojure is not achievable — java.text.Collator does not match it — and a wrong en_US is worse than a documented C.

CBOR canonical order is the same order. RFC 8949 deterministic encoding sorts map keys by the bytewise order of each key's ENCODED form, and the encoding head carries the length — so it is length-first then bytewise, which is lengthCompareJsonbString. Verified through boring's :archival profile: {"z" "aa" "b" "kind"} stores as b, z, aa, kind. A CBOR-stored document would already be in the order PostgreSQL renders, with no re-sort at output.

Reproduce the [] anomaly. An empty top-level array sorts below every scalar, because a missing else lets the length test overwrite the rawScalar test (jsonb_util.c:248-260, with the confession in the comment). Upstream has declared it unfixable since btree indexes depend on it. It is top-level only — [[]] > [null]. One branch in the comparator; cheaper to reproduce than to explain later.

---

3. Size caps

PostgreSQL has no configurable JSON size limit. No GUC exists; only hard ceilings (~256 MB per jsonb string, 1 GB per field). It makes large values workable via TOAST rather than forbidding them.

Practitioners add their own, and the idiom is a CHECK constraint — CHECK (pg_column_size(col) <= N) or CHECK (length(col::text) < N). GitLab tracks exactly this ("Enforce limit of 64KB for JSONB column"). Guidance converges on ~2 KB as where the cliff starts (2–10x slowdowns beyond, TOAST_TUPLE_THRESHOLD).

What pg_column_size actually means

toast_datum_size (detoast.c:601): for an externally stored value it returns VARATT_EXTERNAL_GET_EXTSIZE — the bytes stored in the TOAST table, after compression, not counting the 18-byte pointer (the source comments "should we?"). Inline values report their stored size, compressed if compressed.

So it is a physical storage measure, and this matters for us:

• The same document yields different numbers on different engines. A threshold tuned against PostgreSQL does not transfer to us unless we compress identically. Measured on PG 17: a 3 009-byte document reports 59 (compressed inline); a 72 000-byte incompressible one reports 72 004.
• Therefore octet_length(col::text) is the portable basis for a portable constraint, and pg_column_size is the right basis for "protect my storage". Both are in use; we should implement both and document the difference.

Our mechanism

Datahike already has it: :db/maxLength is per-attribute and works independently of the database-wide :max-string-length. Verified: a capped attribute rejects at 200 chars while an uncapped one in the same database accepts 200 000.

Decisions.

1. Database default stays unbounded (:max-string-length 0). A ceiling PostgreSQL does not have is a conformance divergence that breaks apps working in dev against PostgreSQL.
2. Offer the cap per column through the CHECK idiom, lowered to :db/maxLength at DDL. Users then write portable, standard PostgreSQL DDL that also runs on real PostgreSQL, and we enforce it natively instead of evaluating a predicate per row.
3. Units follow the representation. :db/maxLength counts characters, so it pairs with length(col::text). If jsonb moves to CBOR bytes, the cap should move to :max-bytes-length and pair with octet_length. This is a reason to settle the encoding before promising exact cap semantics.
4. Fix the error first. Today the cap fires as XX000 with a raw Clojure ExceptionInfo including its ex-data map. PostgreSQL's class for a size ceiling is 54000 program_limit_exceeded. Cheap fix, and it is what makes the knob usable.

---

4. Ordering the work, by blast radius

Tier 1 — pg-datahike only, no datahike change, no migration. This is where every correctness bug lives, so it is also the highest value.

1. Record the oracle: bb cross-engine --record for a jsonb .test file. The harness exists and has never been pointed at JSON.
2. Value model: uniform BigDecimal, JSON-null sentinel, numeric range check (22003).
3. Structural equality / hashing / ordering (C collation, [] anomaly). Must land with the canonical writer — equality is comparison of the canonical form, so changing one without the other silently returns zero rows.
4. The canonical writer (already written and verified 16/17 byte-identical against PG 17; the 17th was the unrelated backslash-literal bug).
5. Operator correctness: -> returning JSON null instead of dropping the row, ||, -, ?|/?&, jsonb_agg as a real aggregate.
6. Error surface: 42883 for unknown functions, 54000 for size caps, json rejecting operators it does not have.
7. Missing surface, cheapest first: #>/#>> (they parse already — only the # front-door check blocks them), then the json_* family.

Tier 2 — datahike-side, additive, no pg-datahike change.

8. konserve zstd-3 compression for server-managed stores. Konserve's own measurement on a 512-datom node: lz4-hc 1602 µs → 4767 B versus zstd-3 69 µs → 2507 B — 23x faster and half the size, so lz4-hc has no niche. Note this reduces disk and IO but not node size in memory, so it does not by itself fix the heavy-fragment concern.
9. Blob promotion, if measurement justifies it — see §6, still open.

Avoid anything that puts a physical storage decision into the logical schema. That is the only class of change that forces DDL changes and migrations.

---

5. Deliberate-decision list

Cases where PostgreSQL's behaviour is a wart and we must choose knowingly rather than by accident:

• '1'::jsonb -> 0 returns 1, while '1'::jsonb #> '{0}' returns SQL NULL. Two sibling operators disagree, because -> checks only JB_ROOT_IS_ARRAY and a raw scalar carries JB_FARRAY.
• '"a"'::jsonb ? 'a' is true; '[1,2,3]'::jsonb @> '3' is true.
• '[1,2]'::jsonb @> '[1,1,1]' is true — containment ignores multiplicity.
• jbvDatetime is a 7th variant, observable only through jsonpath .datetime(). Scoped out explicitly with the rest of jsonpath.

---

6. Still open: promotion

Large values are stored inline with no promotion and no compression. Datalevin promotes at 497 bytes into a separate giants DBI with a truncated 496-byte prefix in the index; PostgreSQL TOASTs when the tuple exceeds ~2 KB, greedily externalising the biggest attribute until the row fits, after trying compression first.

Two things are settled:

• Compression and promotion solve different problems. Compression reduces disk and IO; the node still decompresses to full size on fetch. Only promotion decouples node size from value size. PostgreSQL does both, compression first, because it is cheap and may avoid the move — a 3 KB document compressed to 59 bytes and never left the tuple.
• The threshold is backend-shaped, and the direction inverts. Promotion trades bytes for round trips. LMDB fetches cost ~1 µs, so promote aggressively; an S3 GET costs ~10–50 ms, where promotion adds a round trip per row and inline is usually better — you were fetching the whole node over the network anyway. konserve-s3 does not implement PMultiKeyEDNValueStore, so promoted blobs would be fetched strictly one at a time. The threshold therefore wants to be a store-declared property, in the same shape konserve already uses for -supports-multi-key?.

What is not settled is whether promotion should be transparent at the storage layer or surfaced as a value type. Transparency matches the PostgreSQL and Datalevin precedents and costs pg-datahike nothing, but it hides a real cliff and removes per-column control. Left open deliberately.

Mitigation that matters most either way: Datalevin's "keep e and aid in giant refs so projected reads need not load the value". Most scans filter on other columns and never need the blob; that removes the cliff for the common case on every backend.

---

6b. What we verified about CBOR (boring)

The encoding was left open deliberately (§1). Three things had to be true before it could be chosen; two are now measured and the third has a concrete API.

1. Key order coincides. See §2 — CBOR deterministic order IS PostgreSQL's jsonb key order.

2. The value model survives. This was the risk that could have sunk it, since the whole equality story rests on uniform BigDecimal carrying a display scale. Round-tripped through boring :archival:

1.00                     scale 2  -> scale 2
0.001000                 scale 6  -> scale 6
1e3                      scale -3 -> scale -3
9007199254740993         exact past 2^53
123456789012345678901234567890.12345   scale 5 -> scale 5

No loss, including negative scale.

3. Navigation exists. boring.core/encode-indexed seals an index onto the value and boring.nav turns lookups into jumps — field-offset, nth-offset, container-count, value-at, reduce-kv-at. With sorted keys (which :archival gives) a lookup can binary search. This is the analogue of PostgreSQL's JEntry offset array, and it is the actual performance gap: we store canonical TEXT, so every -> re-parses the whole document, where PostgreSQL never parses at all.

Constraints to design around: boring.nav categorically refuses a stringref document (a cursor holds an offset and cannot rebuild the string table), so encode-indexed forces :stringref false and the storage profile must be :archival, not the default :clojure.

What CBOR does NOT buy

• Not equality. PostgreSQL's numeric equality is scale-INsensitive while its rendering is scale-preserving, so byte equality is not jsonb equality whatever the encoding. The structural comparator is required either way.
• Not the text writer. SELECT to_json(x) must return PostgreSQL-identical TEXT — compact vs spaced punctuation, key order, numeric scale. That writer is a wire requirement and stays.

The argument that is actually decisive

Today the canonical TEXT is the stored form, which puts the text writer on the write path, the read path AND every operator at once. That is why one un-normalised emit! call could corrupt json_agg output rather than merely mis-render it. Under CBOR the text writer becomes output-only: a punctuation bug renders wrong, it does not store wrong.

A typed encoder also fails LOUDLY where a generic map-walker does not. PgRecord and PgArray are defrecords, so our writer serialised their internals as keys; boring's :encode-fallback default turns an unregistered type into an obvious tag-27 placeholder naming the type, and can be made to throw.

Still open

Only the demand side: we have never shown that extraction dominates a realistic query mix. Whole-document reads already measured at parity, and extraction at ~2.25 us/row and parse-bound — but not what fraction of a real workload that is. That benchmark is the remaining gate, and it is now the ONLY one.

---

7. Is a later value-type change survivable?

Yes, with one precondition.

Changing jsonb from :db.type/string to bytes or a blob type is a one-line change in types.clj for NEW databases. Existing databases cannot have an attribute's :db/valueType changed in place, so the migration is dump-and-reload.

That path works today. Verified through our own dump: json is emitted verbatim ({ "b":1, "a":2 }) and jsonb in canonical form, as portable SQL that replays into either engine. Reloading also re-normalises, so a canonical-form change migrates for free.

But real pg_dump does not work against us — it fails immediately on pg_catalog.pg_is_in_recovery(), which is unimplemented. Since pg_dump is the interop path most users would reach for, and since dump/restore fidelity is what makes every representation decision reversible, that is worth fixing early. It is a small function.