pg_crdt: Journal

Session 1: 2026-11-04, research kickoff

• Created pg_crdt/. The user wrote the brief in prompt.md. They called it a brain dump, to be rethought from first principles.
• User decisions:
  • Implement in PL/pgSQL only: no plv8, PL/Rust, C or WASM.
  • Namespace each CRDT type the way pg_permissions namespaces its models, so new types can be added without collisions.
• Three parallel research tracks: runtimes.md, prior-art.md and sql-integration.md. Combined in architecture.md.
• Things that changed my mind:
  • pg_permissions does not use a schema per model. It uses one schema with a prefix per model (rbac_, fga_, cedar_). pg_crdt should do the same: one crdt schema.
  • Prefixes must name the algorithm variant (awset_, lww_), not the family (set_, register_). Otherwise the second register variant has nowhere to go.
  • pg_tle base types can have PL/pgSQL I/O functions. They are still not worth it: no send/recv, no opclass without superuser, and the I/O functions can’t be patched in upgrades. jsonb with a {t, v} envelope wins.
  • A single-statement update ... set s = merge(s, $1) is safe under READ COMMITTED. Client-side read-merge-write is not.
  • Supabase deprecated plv8 on PG17, and Automerge JS is WASM-only. Even without the user’s decision, reusing JS libraries was a dead end.
  • Documents: store-and-relay of opaque updates first. A native Fugue table is an experiment. Eg-walker stays a proposal.
• Next: user decisions listed at the end of architecture.md, then specs plus the pn_ vertical slice.

Decisions after review (same day)

• One crdt schema, prefixes named after the specific algorithm (pn_, awset_, lww_), not the family.
• No generic crdt.merge dispatcher for now, because it would be slower. It may come later.
• Still open: whether server-readable collaborative text is in scope, and domain vs plain jsonb.
• Server-readable where possible, so values can be indexed. Collaborative text therefore means a native sequence CRDT (Fugue-style) in tables, not an opaque Yjs/Automerge log. Opaque types (doclog_) are deferred, not ruled out.
• A domain per type (crdt.pn, crdt.awset, …), as part of the extension’s value. Known risk: dropping the extension cascades to columns of that type. Must be documented. Also to test: whether a plain jsonb value needs an explicit cast to call domain-typed functions.
• OrioleDB (orioledb.md): helps with bloat, vacuum and full-page writes, but a changed value is still fully rewritten and WAL-logged. Verdict: storage-agnostic, OrioleDB as an optional accelerator. The delta table plus merge aggregate remains the real fix.

Session 2: 2026-11-04, the PN-Counter slice

• Built the first vertical slice, test-first: pg_crdt--0.0.1.sql with the PN-Counter (crdt.pn domain, pn_empty, pn_increment, pn_decrement, pn_value, pn_merge, pn_merge_agg, pn_is_valid, pn_apply_delta, current_replica_id), pg_crdt.control, a test runner copied and simplified from pg_validate’s, ten spec files, a benchmark script and benchmarks/pn-counter.md.
• Tests: 165 assertions in 10 files, all passing on Postgres 16.4 (local), and 16.15, 17.11 and 18.6 in Docker with pg_tle and the control file installed (test/docker.sh), so the pg_tle and create extension paths run as a non-superuser there. Property tests: commutativity, associativity, idempotence and identity over 1,000 random triples each; 60 convergence trials with shuffled, duplicated and stale delivery; an independent per-key max as the oracle for merge and for the aggregate.
• Concurrency, proved with dblink: a second session’s update ... set s = pn_merge(s, $1) waits on the row lock and merges into the first session’s committed version; 600 concurrent single-statement transactions from two sessions lose nothing. The negative control (read in one statement, write the merged value in another) does lose an update, so the test can see one.
• The RLS path: pn_apply_delta(state, delta) is a validating merge, not SECURITY DEFINER itself. The app wraps it in its own SECURITY DEFINER function and denies clients UPDATE on the column. The replica id comes from the JWT sub, else the crdt.replica_id setting. A delta may only touch the caller’s id or <id>:<device>, may not lower a count, and must pass the full validator.
• Open questions, settled (test/spec/40_open_questions.sql):
  • Does a plain jsonb value need an explicit cast to call a crdt.pn function? No. Columns, PL/pgSQL variables and literals all coerce implicitly, and the coercion runs the CHECK once per value.
  • Does the domain CHECK run on every function return? Yes, whenever a crdt.pn is made from jsonb: PL/pgSQL, inlinable SQL and SQL-standard bodies alike. Counted with a spy constraint: pn_merge and pn_increment once per call, pn_merge_agg once per merge, pn_empty() once per call. Not for a crdt.pn passed through unchanged (pn_merge(x, null), a crdt.pn column as an argument). It also runs on a null literal cast to the domain.
  • Generated column and expression index on pn_value? Both work (stored generated column, expression index used by the planner, CHECK constraint, reindex under search_path = pg_catalog, pg_temp). Postgres 18’s virtual generated columns reject user-defined functions (“not yet supported”), so stored only.
• Things that surprised me:
  • The first pn_merge passed every property test and was quadratic: a -> 'p' -> e.key inside the sub-select over b’s entries detoasts a and copies its map per entry. A full merge of two 10,000-replica states took 1.85 s; extracting the maps once made it 13 ms. One-entry deltas hid it. Correctness tests can’t see this; benchmarks have to be part of the spec.
  • SQL vs PL/pgSQL makes no difference for merge: the same statement costs the same in both, from 10 to 10,000 replicas. The cost is building jsonb.
  • jsonb || re-sorts every key of the map; jsonb_set on an existing key does not. pn_merge now uses jsonb_set when exactly one entry changes (a replica’s own delta), which halves the delta merge at 1,000+ replicas. The jsonb_set loop, ruled out in the architecture, is the fastest for one-entry deltas and quadratic for full merges.
  • A full per-entry CHECK on the domain costs about 4 ms at 10,000 replicas and runs on every merge result, more than the merge. So the domain checks only the envelope (about 10 us, with @> and jsonb - text[], which do not walk the nested maps; value -> 'p' and jsonpath keyvalue() both copy them). pn_is_valid is the full validator, enforced by pn_apply_delta. This is safe for convergence: merge keeps the larger entry under jsonb’s total order, a semilattice for any JSON values, and a property test with junk entries (strings, nulls, objects) holds the laws. First version of the validator had a hole: jsonb_typeof(state -> 'n') is null when n is missing, a null CHECK passes, so a state without n was accepted. Coalesce, or operators that cannot return null.
  • WAL crossover at about 100 replicas. Below it, a state-column update logs less WAL than a delta insert (128 B vs 334 B at 10 replicas): the row is inline and Postgres logs only the changed bytes of a same-page update. Above about 2 kB the state is TOASTed and every write logs all of it: 9.9 kB at 1,000 replicas, 92 kB at 10,000 (28 and 264 times a delta insert). So the state column is right for small counters, and the delta table plus compaction for counters with many writers.
  • create schema if not exists crdt fails inside create extension when the control file or pg_tle has already made the schema (pg_validate had found the same). Use a DO block.
• Next: compaction for the delta table (merge into a snapshot row, delete folded deltas), then awset_ with the naive OR-set as its oracle.

Session 3: 2026-11-04, generic identity

The user reviewed the slice. Guiding principle: pg_crdt is generic Postgres and must work with any tool, not only Supabase. Identity must not assume UUIDs, auth.uid(), Supabase roles, or that a JWT is present at all.

• Stale deltas are a no-op, not an error. Session 2 rejected a delta that lowered a count (22023), to surface a replica that lost its state. But a lower total also looks exactly like a retry or a replayed older delta, and those must succeed: max-merge already ignores them. The check is gone. Lost-state recovery is now documentation: a writer reads its own entries back first, or uses a fresh device id; otherwise its next increments are swallowed until they pass the old total. Two writers sharing one key do the same.
• Replica ids are generic text, with : reserved. An id is any non-empty text without : (emails, integers as text, role names; no UUID assumption). An entry key is an id or <id>:<device>; the owner is the part before the first :, compared exactly, so abc can write abc and abc:phone but not abcd or ab. A caller identity with : is rejected (22023, like other malformed input), and so are malformed keys (abc:, :x) in pn_increment, pn_decrement and pn_apply_delta. pn_merge stays permissive: it must accept whatever another replica sends.
• Identity is pluggable. pn_apply_delta(state, delta, replica text default crdt.current_replica_id()). The app’s SECURITY DEFINER wrapper passes the identity it trusts: session_user for direct SQL clients (not current_user, which is the definer), its own users table, a JWT claim, or auth.uid()::text on Supabase. A null replica is 42501. With the replica an argument, pn_apply_delta is pure, so it is now IMMUTABLE.
• The default resolver reads a JWT claim from request.jwt.claims, which is the PostgREST convention rather than a Supabase one; the claim name comes from crdt.replica_id_claim, default sub. Then the crdt.replica_id setting, which app servers set with SET LOCAL. The same order as pg_permissions’ subject. Trust model, stated in the README and pinned by a test: any role can SET these settings, so a direct-SQL client can claim any id through the default; such deployments pass session_user.
• Docs and tests are generic. The README leads with the wrapper pattern and two identity sources (PostgREST JWT, app server or session_user), mentioning Supabase as one example. Spec 30 creates its own roles instead of relying on authenticated; only the grant check in 02_security uses the PostgREST-convention roles, and only where they exist.

Session 4: 2026-11-04, user documentation

• User docs in docs/ and an Astro/Starlight site in site/. Copied from pg_validate’s site, plus pg_permissions’ code tabs. App examples use node-postgres first, then SQL. There is no client library.
• Pages: overview, install, quickstart, four guides (replicas and identity, safe updates, security, storage and performance), the PN-counter page, and a reference (shared, pn, errors, limits). Plus STYLE.md and CHANGELOG.md.
• Every SQL and JS example was run against a throwaway cluster. The install paths were run in Docker (pg_tle on PG18, control file on PG16).
• Found while writing the docs:
  • drop extension leaves the empty crdt schema behind.
  • A pn_empty() column default blocks drop extension even after the column is altered to jsonb.
  • The old README’s error table missed several SQLSTATEs.
• Added a hand-written compaction recipe for delta tables: delete ... returning folded into a snapshot upsert in one statement. Compaction is not built in yet.
• The production URL is undecided. The placeholder is https://pg-crdt.vercel.app (site/astro.config.mjs, research/navigation.json).

Session 5: 2026-11-04 to 11-05 (overnight), collaborative text

The user asked for a way to store text documents, the most common CRDT use case (a blog post body edited by several people). They suggested either a CRDT text column or tables in the crdt schema with a link to a document, and left it to run overnight. Brief: research/text.md.

• Plan: one algorithm, two storage shapes, measured against each other. The algorithm is YjsMod order (equal to FugueMax, no interleaving), with one JSON op format shared by three implementations:
  • a JS reference (experiments/text-ref), which also generates the fixtures and converts the automerge-paper editing trace (259,778 edits);
  • shape A, a crdt.yseq jsonb column (experiments/text-column);
  • shape B, extension-owned tables with a document id (experiments/text-table). All three must produce identical text, so the fixtures double as differential tests.
• Shape A: runs stored as int[] literals inside jsonb, which parse 4x faster than jsonb arrays. Runs are grouped in chunks of up to 128 (chosen by a sweep). Deleted characters keep no text. Merge became a linked-slot engine: at 100k chars with 1,000 edits per side, merge went from 2.4 s to 74 ms. Storage is 1.2 to 1.6x the plain text, but every keystroke rewrites the whole value: 114 kB of WAL per keystroke at 100 kB.
• Shape B: rows per run, and a server-local bigint order label with relabelling, so render is order by.
  • Fractional bytea keys were tried first. They reached 288 bytes on the trace.
  • Blocks hold prefix lengths for position lookup: 0.3 ms per insert at 100k, against 3.7 ms with a running sum.
  • Writes serialize per document on the document row lock.
  • Compaction merges adjacent runs (trace document: 149 to 26 bytes per char).
  • Replica ids are interned, which halves storage with UUID ids.
  • WAL is about 1 to 2 kB per keystroke at any size.
• Head-to-head (research/benchmarks/text.md): B is the default. Its cost per edit is flat from 1 kB to 1 MB. It is 10 to 60x cheaper in WAL per keystroke, about 3x faster for three-way saves, 8x faster for batched apply, and 2x the same-post throughput. The price is about 4x A’s storage and tables the extension owns. A suits small documents saved from a form, where the team wants a plain column.
• Things that surprised me:
  • My brief was wrong about set(). Diffing a save against the current text reverts whatever other editors did since the form was loaded. So “edits to different paragraphs both survive” was false. Shape B found it. The fix is a three-way set against the base the editor loaded: a token in B, the loaded state in A. It took two more review rounds to get right, first for a second save from the same base, then for ops emitted after the writer’s own later edits.
  • The op format was not safe against untrusted writers. A reviewer showed that one insert with an originRight that skips a character the writer had seen makes replicas diverge permanently (aYXbc against aXbcY). The JS reference had the same hole, so it was in the contract, not a porting bug. The fix is that every op carries the sender’s version vector, and apply checks rules V0 to V7. The rules: origins covered; seqs contiguous; delete targets covered; the vector causally closed; originLeft before originRight; and no item the sender had seen strictly between the origins. Byzantine fuzz over 3,000+ seeds found 0 divergences, and turning off any of V1 to V5 brings divergence back. Equivocation (the same id with different content) is outside the rules. A single Postgres server settles it with the primary key, so the first arrival wins.
  • The two adversarial reviews found the same classes of bug in both shapes: uncapped replica-key length, quadratic CPU from one op with thousands of tiny runs, a parked op that blocks a writer forever, and missing search_path pins. Correctness fuzzing missed all of them. Each review came with a repro script.
  • Pinning search_path on every PL/pgSQL function costs about 18% on ydoc_apply. We kept it, because internal functions must stay executable by API callers.
  • Shape A’s int[]-in-jsonb trick parses 4x faster than nested jsonb arrays, and halves the size.
• Next: the user reviews the names (ydoc_, yseq_), the promotion of ydoc_ into the extension, and the pg_dump config for extension-owned tables. Not built: tombstone GC beyond compaction, rich text and marks, cursors, undo, a client library (JS clients must convert UTF-16 offsets to code points), and Yjs wire compatibility.

Session 6: 2026-11-05, text document ids

The user asked that clients can create documents, so the public document id changes from a server-assigned bigint to client-choosable text: ydoc_create(id text default gen_random_uuid()::text) returns text, and every ydoc_ function takes doc text.

• Why text, not uuid. Same reason replica ids are text: the extension stays generic. Text accepts UUIDs, UUIDv7 and ULIDs, Yjs room names and app keys (post:123) without the extension choosing an identity scheme. The only rules are non-empty, at most 256 bytes, no control characters; ids compare byte for byte (collate "C"), so Post and post are two documents.
• Why the internal bigint stays. 45 columns in the internal tables name a document (items, blocks, deps, replicas, pending, …). Keeping a bigint there keeps rows and indexes small, and leaves every _ydoc_* internal untouched. ydoc_docs gets uid text unique; each public function resolves it once, in the same probe that takes the document row lock (_ydoc_lock returns the internal id), so a write does one lookup, not two. Latency of 1,000 inserts into a 100k document did not change (about 0.22 s before and after).
• Why 23505 and not an idempotent create. An idempotent create (on conflict do nothing, return the id) lets anyone squat on a predictable id: create post:123 first and the owner’s create silently succeeds against a document someone else already controls. So an existing id raises unique_violation with a hint, concurrent creates of one id give one success and one 23505 (the on conflict insert waits for the other transaction), and the app’s wrapper decides what to do: the owner’s retry gets its own post back, anyone else is refused. An id grants nothing, and existence is never proof of ownership; ownership lives in the app’s own table.
• Small things. The default is pg_catalog.gen_random_uuid()::text, qualified because defaults run in the caller’s context. The base token’s d now holds the public id. Unknown documents still raise P0002; the readers still return null (ydoc_version: {}).

Session 7: 2026-11-05, queued fixes and the user’s decisions

The user answered the eight open decisions and asked for three queued fixes, test first.

• Decisions. Names ydoc_ and yseq_ stay. yseq_ stays experimental. drop extension deleting the documents stays (and is documented). Public EXECUTE on the _ydoc_* internals is accepted, since the tables are the gate. The pinned search_path stays despite its 18%. The pg_dump config is confirmed. The docs URL stays a placeholder. Push and PR wait.
• ydoc_version on a missing document. It returned {}, which reads as “nothing yet”: a peer that asks ops_since with {} would get the whole history of a mistyped id. It now raises P0002 like the writers. The price is that it is PL/pgSQL, not an inlined SQL function. Readers of text (ydoc_value, ydoc_length, ydoc_base) still return null.
• The : rule. The JS reference refused : in any replica id, while the SQL accepted <id>:<device> keys, so the reference could not replay an op log that used device keys. We aligned the reference to SQL (the extension is the product, and device keys are in the docs). The useful part was the shared fixture: one JSON file of 22 keys (valid and invalid, the 256-byte edge in ASCII and two-byte text, newlines, non-BMP) that the JS test and a SQL spec both read, so the two rules cannot drift again without a test failing. The SQL side already passed; the JS side failed first, then passed after the one-line regex change. Two old JS tests had used a:b as an example of an invalid id and had to change.
• ydoc_rejected_purge. The rejection log keeps 100 rows per writer and document, and apps could only delete from it by hand with table privileges. ydoc_rejected_purge(doc, writer default null) returns the number of rows deleted, takes the document lock (so it does not race the call that decides a waiting op) and touches nothing else. It is SECURITY INVOKER like the rest, so a role without the table grants gets 42501.
• Tests: 471 assertions in 26 files (was 445 in 24), on the local Postgres 16 and on PG16/17/18 in Docker. Not done: ops_since delete versioning.

Session 8: 2026-11-05, a JavaScript client for text

Follow-up 1 of the handoff: apps had to hand-roll op generation, UTF-16 to code point conversion, version tracking and the sync loop. Now client/ (pg-crdt-client): plain ESM, JSDoc types, no dependencies, no build step, Node and browsers. Docs: docs/reference/client.md.

• API. openYdoc({transport, doc, replica, create}) loads the document (opsSince(doc, {})) and returns a client with value(), insert(index, text) and delete(index, length) in UTF-16 units, on('change' | 'rejected'), sync(), version(), pending and error. The transport is two functions the app writes, apply(doc, op) and opsSince(doc, vv), so nothing in the library knows about drivers or platforms. One example adapter, node-postgres, in docs/examples/; the docs also sketch a browser fetch one.
• One engine, not two. The client needed the reference CRDT, and copying it would have given two implementations to keep in step with the SQL. So ref.mjs moved to client/src/doc.js, and ref.mjs is now a one-line re-export. The only change was Buffer, which browsers lack: replica order is now a code point comparison (lone surrogates read as U+FFFD, exactly what Buffer.from does), and key length is a hand-written UTF-8 count. The proof that nothing changed: gen-fixtures.mjs and the trace converter regenerate all 71 fixtures and the three automerge-paper files byte for byte, and the SQL suite passes 471/471 with them.
• Batching, and the trap in it. Edits between syncs are merged with mergeOps into one op, which is both fewer round trips and fewer rows. A batch closes at a sync and whenever a remote change is integrated, because a merged op carries the first op’s v, and a later edit next to a foreign character would fail V1 against it. The trap was retries: if a sync sends abc (seqs 1 to 3), the server applies it and the response is lost, and the user then types d, merging again would send abcd as one run. The server sees a run that half overlaps ids it has, which is equivocation, and refuses it with 22023, and the client is dead. So a batch is frozen the first time it is handed to the transport and re-sent byte for byte until the server answers; ydoc_apply accepts an identical re-send as a no-op. A mutation test that merges into the frozen batch fails three tests.
• Change events without engine hooks. Items are never moved or revived, so the exact changes of a pull are a walk of the item list comparing “visible before” with “visible now”. “Before” needs no snapshot: an item is old if its integration counter is below the counter at the start, and a subclass records which items _markDeleted killed. The ranges are in UTF-16 units of the old text, sorted and non-overlapping, which is CodeMirror’s changes shape. Every fuzz step checks that a mirror string patched only by events equals value().
• Refusals are permanent, everything else is transient. An error whose code is a SQLSTATE of class 22, 42, 54 or P0 stops the client: after a refusal the local replica has characters the server never will, and V2 makes every later op of the key wait behind them forever. One rejected event, error set, and sync() rejects without calling the transport again. The client also refuses, locally, the text that would only fail on the server: a lone surrogate and U+0000.
• Tests. 25 Node tests (node:test, no packages): conversion, the ops sent, batching, both kinds of lost message, the 128-run cap, refusal, serialized syncs, reopening a key. The convergence fuzz runs 1,000 seeds of 2 to 5 clients over an in-memory server that validates with the reference’s untrusted path, with random delays, overlapping syncs and about 1,500 lost requests and responses: every client converges to the server’s text and version, no op ever waits. The integration test drives the docs’ node-postgres adapter unchanged, through a 30-line psql stand-in for client.query (so no pg dependency), on Postgres 16 locally and on 17 and 18 in Docker over TCP: two clients agree with ydoc_value, a lost response, a forged key (42501), and 8 seeds of three clients with concurrent syncs interleaved with ydoc_set saves and ydoc_compact.
• Things that surprised me:
  • Two of my own unit tests were wrong, not the client: “bob inserts > at the start” without pulling first is concurrent with alice’s text at the same origins, and the tie-break puts alice:a first. The fuzz had no opinion; it only checks agreement.
  • pending never happens for an honest client of one database: everything its v names came from that server or from itself. So the client counts it as delivered, and the case where a waiting op is refused later (it lands in ydoc_rejected) is invisible to it. Documented, not solved.
  • The engine is the reference, written for correctness: one object per character and an indexOf per integrated character. Fine for blog posts; the trace (105k characters) takes about 5 s to replay. A faster engine would now need the fixtures to keep it honest, which is the point of sharing one.
• Not built: persisting the queue across page reloads, presence and cursors, and a non-JS client.
• Adversarial review, six low-severity findings, all fixed with a regression test each (every test fails on the old code):
  • A throwing change listener escaped _emit: insert() threw after the edit was already applied and queued, and on a remote change it aborted sync() and skipped the later listeners. A throwing rejected listener ran before throw this._error and replaced the YdocRejectedError. Now _emit calls every listener in a try/catch and rethrows each error on a setTimeout, the way a DOM event listener’s error surfaces (uncaught exception in Node, window.onerror in a browser). I picked that over an 'error' event because it needs no new API and cannot be silenced by forgetting to subscribe.
  • openYdoc accepted replica keys such as a\u0000:1 and lone surrogates that Postgres cannot store, so the first insert would have been refused as a permanent error. It now runs checkStorable on the key, up front. isReplicaKey in doc.js is unchanged, so the shared fixture and the reference tests are not affected, and SQL cannot hold those keys anyway. The doc id is not checked the same way (not in the review; a \u0000 there fails at the first call).
  • Errors from opsSince were never classified, so a dropped document (P0002) or a revoked writer (42501) retried forever. A failed pull in sync() now goes through the same isRejection test and the same stop, with op: null on the event and on the error. openYdoc’s first load still rejects with the raw transport error, since there is no client to stop.
  • Two doc fixes: insert('') and delete(i, 0) still check the index (RangeError inside a surrogate pair), and a batch closes when a pull integrates new characters, not on any change from the server.

Session 9: 2026-11-10, vacuum, bloat and WAL

The benchmarks so far measured latency, storage after vacuum full and WAL from LSN differences. They did not measure what sustained edits leave behind or what the WAL is made of. This session measured both, for pn_, yseq_ and ydoc_, each next to a plain text column with the same edit stream: benchmarks/vacuum-wal.md, scripts in benchmarks/vacuum/. Postgres 16 only; 17 and 18 were skipped.

• Method. 100 documents of 1, 5, 20 and 100 kB. Keystrokes at random positions, one per transaction. First 20,000 of them with autovacuum off, then VACUUM and VACUUM FULL, measured with pgstattuple. Then fixed rates under default autovacuum for 3 and 10 minutes, sampled every 5 s. Then WAL through pg_waldump --stats=record: cold after a checkpoint, warm, and after VACUUM, with wal_compression off, pglz and lz4, and with checkpoints every 30 s against 5 min.
• The open item is closed: yseq_’s WAL is not full-page images. 96 to 100% is record payload, the new TOAST chunks of the whole value. FPIs of TOAST pages are small, because an FPI leaves out the page’s empty middle and new chunks mostly go to fresh pages. So wal_compression does nothing for yseq_. It was ydoc_ that turned out FPI-heavy: flat records of about 1 kB, plus FPIs that are 40 to 84% of its WAL after a checkpoint. Checkpoints every 30 s nearly double ydoc_’s WAL, and wal_compression cuts about 30%.
• The bloat is TOAST’s, and it is the same for a plain column. Every value over about 2 kB that is rewritten per edit leaves its old version in the TOAST table until VACUUM, which makes the space reusable and returns none of it. Plain text of 5 to 100 kB bloats like yseq_ (yseq_ is 1.1 to 1.6 times plain). Under default autovacuum a TOASTed column settles near writes/s x value size x 60 to 70 s. The 60 s is autovacuum_naptime, so 200 keystrokes/s on 100 documents of 5 kB holds 65 to 80 MB for 0.5 MB of data. pn_ follows the same rule above about 170 replicas: a 1,000-replica counter at 100 increments/s holds 85 MB.
• What changed my mind.
  • yseq_ crosses the TOAST threshold below 1 kB of text. A 1 kB document with a short history is a 2.4 kB value, so the cheap inline regime that plain text keeps up to 2 kB does not exist for yseq_.
  • yseq_ does not settle under random-position keystrokes, because each one splits a run and the state keeps growing: 336 MB after 10 minutes at 5 kB, against 72 MB for plain. Typing at the end of the document settles at 95 to 122 MB. But a single hot document still grows, because backspaces leave tombstones that break the run. Its steady state depends on how people type. Plain text’s does not.
  • At 100 kB and 200 keystrokes/s, default autovacuum does not keep up even for plain text: 3.9 GB in 10 minutes for 6.8 MB of data, three TOAST passes, still climbing. That is a cliff of Postgres’s defaults, not of pg_crdt, but it sits exactly where a collaborative editor would put a long document.
• What it confirmed. ydoc_ stays at 2 to 3 times its live data in every scenario, with 100% HOT on its internal tables. Its growth is live rows (the storage cost text.md reports), not dead ones. The pn_ delta table never bloats until compaction, and the documented recipe folds 20,000 rows in 0.5 s (reads 5.0 ms to 0.24 ms), though the btree indexes keep their pages afterwards.
• Bug in my own harness. Under set -e, a failing kill in an EXIT trap ends the shell before the cleanup after it. Two scripts left 18 throwaway clusters running (about 15 GB) until I noticed the disk filling. They were idle, so no numbers depend on them. Fixed with || true.
• Not measured: PG 17 and 18, tuned autovacuum, long-running transactions holding back VACUUM, ydoc_compact under sustained edits, and read latency on a bloated table.

Session 10: 2026-11-11, yseq_ moves into the extension

The user’s decision: keep the extension simple. The jsonb column type yseq_, an experiment since session 5, becomes part of the extension as the primary text type, labelled experimental, with its limits written down plainly. ydoc_ stays unchanged as the table-backed alternative.

• What moved. experiments/text-column/impl.sql (2,607 lines) is now the yseq_ section of pg_crdt--0.0.1.sql, placed before ydoc_, with its own grant loop (public EXECUTE on yseq_* and _yseq_*, as for ydoc_). The 12 spec files moved to test/spec/70_yseq_* to 81_yseq_* unchanged apart from the fixture path; the experiment’s runner and impl.sql are deleted, and NOTES.md stays as research notes that point at the new locations. The bench scripts that loaded impl.sql after the extension (bench/text-compare.sh, research/benchmarks/vacuum/, the experiment’s own benches) now load the extension only. No name collided: the internals were already _yseq_*, and the ydoc_ grant loop matches only ydoc names.
• The surprise: the file stopped installing. 03_install builds a scratch database with the cluster’s default encoding, SQL_ASCII, and the install failed there: yseq_ops_since had a U&'\FFFD' literal, and PL/pgSQL’s check at create function refuses a non-ASCII Unicode escape in a non-UTF8 database. The experiment only ever ran in UTF8 databases. It is chr(65533) now, and the plain-install database in 03_install is SQL_ASCII on purpose. That only moves the problem to run time: in LATIN1 or SQL_ASCII, yseq_ops_since, yseq_diff and yseq_merge raise 54000 on any state with deleted characters. Documented as “UTF8 databases only”; a fallback character is a one-line change if anyone needs it.
• Tests added. 02_security checks yseq_ like ydoc_ (client API list, public EXECUTE on the internals, usage on the domain, IMMUTABLE PARALLEL SAFE everywhere but VOLATILE yseq_new, pinned search_path, no EXECUTE). format() had to be allowed, since yseq_ uses it for error messages. 03_install gained a yseq column smoke test, and the drop checks: with the control file, drop extension fails with 2BP01 while a crdt.yseq column (with a generated column on yseq_value) exists, and cascade drops both columns and keeps the table and its rows; the same under pg_tle. 701 assertions in 38 files: 471 before, the 221 moved, and 9 new.
• Docs. The type page now starts with a choice between the column and the tables, and a limits section for the column with the measured numbers (WAL per keystroke 3.4 / 7.1 / 18.5 / 78.7 kB at 1 / 5 / 20 / 100 kB, TOAST churn, no tombstone GC, no equivocation detection, O(document) merge, apply and ops_since, the whole state as a three-way base, edits that return state). The guide builds the post twice: Part 1 with a column (wrappers around yseq_set and yseq_apply, a generated body_text with a GIN index), Part 2 the existing ydoc_ walk-through. New reference page docs/reference/yseq.md, whose examples run in order. The JS client still speaks only ydoc_; its page says how an app drives a column with yseq_apply and yseq_ops_since, and that the edits return states, so the op of a server-side edit is yseq_diff(old, new).
• A detail I had wrong in my head. yseq_ has no public function that merges ops: yseq_merge takes two states, and the trusted op path (_yseq_integrate) is internal. So a server catches up from a peer by merging states, and ops go only one way, to clients, through yseq_ops_since.
• Benchmarks at the bottom of each type page. docs/types/pn-counter.md and both parts of docs/types/collaborative-text.md (yseq_, ydoc_) now end with a Benchmarks section: a bottom line, three or four tables (size, latency, WAL, bloat, concurrency where measured) and a setup and caveats paragraph, with links to research/benchmarks/. The numbers are copied from text.md, vacuum-wal.md and pn-counter.md, not re-measured, so the docs only say what the reports say.
• Two runs disagree, and the pages show both. WAL per keystroke in text.md (an earlier run, older code) is higher than in vacuum-wal.md for the same edit: yseq_ 11,123 against 3,376 bytes at 1 kB and 122,390 against 78,733 at 100 kB, ydoc_ 861 against 1,952 (cold) at 1 kB. The pn_ WAL per write at 1,000 replicas is 9,941 bytes in pn-counter.md (an 8.9 kB state) and 13,700 in vacuum-wal.md (a 12 kB state). Neither is wrong; the pages label the source of each row.