Proposal: Exact, Versioned Row Identity V2

Status: Proposed Target: Pre-1.0 / v0.83.0 DVM semantic fidelity work Decision: Replace hashed BIGINT row identities with canonical BYTEA Upgrade: Recreate every existing stream table; in-place V1/V2 migration is unsupported

1. Executive Decision

pg_trickle should replace __pgt_row_id BIGINT with an exact, versioned, canonical BYTEA encoding of the logical identity fields.

V2 should not hash the canonical identity into 64 bits. The canonical bytes are the identity. This removes session-dependent ::TEXT formatting and hash collisions. For types whose registry entries explicitly guarantee native-order preservation, it also lets B-tree probes retain source-key locality instead of deliberately randomizing it.

The heap storage contract becomes:

__pgt_row_id  BYTEA NOT NULL  -- complete canonical identity

For an identity whose maximum encoded length is statically proven to fit a B-tree tuple, pg_trickle indexes the complete identity directly:

CREATE [UNIQUE] INDEX ... USING btree (__pgt_row_id);

Only an unbounded identity uses a bounded expression index:

CREATE INDEX ... USING btree
   (pgtrickle.row_probe_v1(__pgt_row_id));

Matching through that expression index always rechecks the complete identity:

pgtrickle.row_probe_v1(st.__pgt_row_id) =
   pgtrickle.row_probe_v1(delta.__pgt_row_id)
AND st.__pgt_row_id = delta.__pgt_row_id

The probe is an index accelerator, not an identity. For ordinary identities it is exactly the full row ID. For unusually wide identities it is an ordered prefix plus a 128-bit digest. The full row ID comparison remains authoritative, so a probe collision can only add a candidate row to an index scan; it cannot merge, overwrite, or delete the wrong row. The probe is not stored in the heap or exposed as a table column.

There should be one V2 encoding for the extension. No per-table encoding option, no simultaneous BIGINT and BYTEA strategies, and no direct-integer side path are proposed. A signed integer already has a compact, order-preserving V2 byte encoding, so another strategy would add coordination and migration states without adding a capability.

typed logical fields
        |
        v
exact canonical encoding          --->  __pgt_row_id
        |
        v
statically bounded? -- yes ------>  __pgt_row_id B-tree
   |
   no
   v
bounded lookup probe              --->  expression B-tree

2. Why Replace the Hash

The current row-ID path already frames composite inputs with an encoding version, component count, NULL/value tags, and per-value lengths. Inputs such as (ab, c) and (a, bc) therefore hash different framed byte sequences. V2 does not need to fix delimiter ambiguity.

Four structural problems remain.

First, fields are converted to String values rather than encoded from typed PostgreSQL datums. Text output can depend on PostgreSQL settings and type output behavior.

Second, the final identity is only 64 bits. Different canonical inputs can hash to the same BIGINT. Because MERGE treats the hash as proof of identity, a collision can silently overwrite or delete the wrong logical row.

Third, a hash destroys potentially useful source-key ordering. MERGE probes become random B-tree accesses once the storage table is larger than shared_buffers, even when rows arrive in primary-key order and the source type has a byte encoding that can preserve its native B-tree order.

Fourth, the current SQL shape pays conversion and allocation costs on every row:

pgtrickle.pg_trickle_hash_multi(
    ARRAY[(expr1)::TEXT, (expr2)::TEXT, ...]
)

Changing only the hash input would fix the first and fourth problems while deliberately retaining the second and third. Since V2 already requires every stream table and CDC identity buffer to be rebuilt, preserving BIGINT does not avoid the expensive part of the migration. This is the correct point to adopt the durable representation rather than schedule another identity migration.

3. Required Invariants

V2 is governed by these invariants:

  1. Equality agreement. Values equal under the PostgreSQL equality semantics used by the maintained query encode identically.
  2. Injectivity modulo equality. Values that are not equal encode differently. The final row identity has no hash-collision failure mode.
  3. Tuple framing. Field count, order, type, NULL, and value boundaries are unambiguous. (1, 23) cannot encode like (12, 3).
  4. Determinism. Encoding is independent of DateStyle, TimeZone, bytea_output, locale formatting, process architecture, and database OIDs.
  5. Explicit ordering contract. Every supported type declares whether lexicographic payload order matches its PostgreSQL B-tree comparator. Native ordering is a performance property, not a prerequisite for exact identity. No locality claim is made for types marked non-order-preserving.
  6. Prefix freedom. No complete row identity is a byte prefix of another complete row identity.
  7. One implementation. Trigger CDC, WAL CDC, full refresh, differential refresh, IMMEDIATE mode, joins, aggregates, and stream-table propagation use the same encoder.
  8. Exact matching. The bounded probe may narrow candidates, but only equality of the complete __pgt_row_id establishes a match.
  9. Known version. Persisted V1 and V2 identity state must never be consumed together.

Correctness takes precedence over accepting every PostgreSQL type. A type with no proven encoder is rejected at stream-table creation instead of falling back to text or a hash.

4. Normative Wire Format

The implementation must land a short normative wire-format specification and golden vectors before it lands operator rewrites. The format must not be frozen until the type contracts and probe sizing gates in sections 6, 8, and 13 pass. Once V2 is released, its byte format is immutable. A semantic correction requires V3 and another rebuild.

This proposal defines the semantic constraints, not the final byte assignments. The separate wire document is a hard implementation prerequisite and must assign every tag value and width; define byte order and signed transforms for every integer; define count, exponent, terminator, escape, and nested framing rules; specify numeric normalization and all structural resource limits; specify the XXH3-128 seed and output-byte order; and include independently reproducible identity and probe vectors. No encoder, stored expression, or operator rewrite may merge before those vectors pass on every supported PostgreSQL major and CPU endianness.

Conceptually, an identity is:

VERSION | DOMAIN | FIELD_COUNT | FIELD... | TUPLE_END

Each field is:

TYPE_TAG | NULL_TAG

or:

TYPE_TAG | VALUE_TAG | PAYLOAD

The following rules are normative:

  • VERSION is part of every identity, not only catalog metadata.
  • DOMAIN separates scan keys, group keys, joins, set operations, windows, keyless rows, and synthetic identities.
  • TYPE_TAG values are stable and never reused.
  • fixed-width payloads have a fixed byte count implied by their type tag;
  • NULL_TAG sorts before VALUE_TAG; all NULLs of one field type encode identically;
  • variable-width payloads use an unsigned-byte-order-preserving escape-and-terminate scheme, not a length prefix;
  • nested tuples are framed recursively;
  • TUPLE_END makes complete identities prefix-free.

For arbitrary bytes, the base escaping is:

00      -> 00 FF
end     -> 00 00
01..FF  -> unchanged

This preserves unsigned byte order while making the end of a value explicit. Fixed identity schemas have the same header and type tags for every row, so those bytes do not disturb ordering within an index.

The row ID is opaque to users. The format is specified so pg_trickle can test and preserve it, not to create a user-facing serialization API.

5. Identity Domains and Composition

Domains make different identity meanings disjoint without relying on magic numbers or hoping that a hash does not produce a reserved value.

The initial domain registry should include at least:

Domain Input
SCAN_KEY fields from an eligible source identity index
KEYLESS_ROW all logical output fields
GROUP_KEY GROUP BY fields
JOIN_KEY ordered child identities
SET_KEY set-operation identity components
WINDOW_KEY partition/order identity components
SYNTHETIC a stable internal discriminator

Operators decide which logical values form an identity. The row-identity module alone decides how those values are encoded.

Pass-through operators preserve the child identity unchanged. Derived operators encode child identities as framed BYTEA fields. A join therefore has a stable left-then-right ordering and cannot confuse (left=A, right=BC) with (left=AB, right=C).

Synthetic identities use the SYNTHETIC domain and a registered discriminator, for example scalar_aggregate_singleton or lateral_inner_dummy. Hard-coded BIGINT sentinel values disappear. SQL NULL is never a valid row identity.

5.1 Source-key eligibility

“Primary or unique key” is not sufficient by itself. A SCAN_KEY may use only an index that covers every source row and whose uniqueness semantics agree with the encoder. The initial V2 release accepts:

  • a primary key; or
  • a non-partial, non-expression, immediate unique index whose key columns are all catalog-marked NOT NULL; or
  • the same form of unique index declared NULLS NOT DISTINCT.

A default NULLS DISTINCT unique index with any nullable key column is not a logical row identifier: PostgreSQL can store several rows whose encoded key is identical. Partial and expression indexes are not eligible in V2. A deferrable or otherwise non-immediate unique constraint is also ineligible because an IMMEDIATE maintenance path can observe duplicates that are legal until source transaction commit. If no eligible index exists, planning uses the existing keyless/multiset identity rules or rejects a mode that requires unique identity.

The selected index’s collation and equality operator family are part of validation. V2 initially accepts default operator classes whose equality contract is proven by the registry. A non-default operator class is rejected unless a registry entry explicitly proves agreement between its equality operator and canonical bytes. Catalog invalidation or DDL affecting any of these properties invalidates the resolved identity schema and requires revalidation.

6. Type Semantics

Type support is an explicit registry. Runtime lookup begins with PostgreSQL type OID, but encoder resolution is keyed by the complete semantic descriptor:

concrete type | typmod | collation | equality operator family |
nested type metadata | PostgreSQL major version

There is no generic ::TEXT, output-function, or typsend fallback. Every resolved registry entry declares:

semantic_family:          stable V2 type tag
equality_canonical:       required
default_nonnull_btree_order_preserving:  true | false
volatility:               immutable | stable
maximum_encoded_size:     fixed | typmod-bounded | unbounded
ddl_invalidations:        explicit list
supported_pg_majors:      explicit set

equality_canonical means PostgreSQL equality and byte equality agree in both directions. A type cannot ship without that proof. default_nonnull_btree_order_preserving means that, for non-NULL values using the default ascending B-tree operator class, the sign of lexicographic payload comparison agrees with the type’s PostgreSQL B-tree comparator. It does not claim PostgreSQL’s default NULL placement, descending-index order, non-default operator classes, or expression-index order. Only entries marked true support source-index locality claims. The registry stores these properties per concrete type OID, even where the tables below group types with identical policies.

The stable wire TYPE_TAG identifies a canonical semantic family, not a database object. Runtime OIDs and typmods never enter persisted bytes. Domains intentionally use their base family’s tag and erase domain identity. Enum values use the enum-family tag and label bytes, so two concrete enum types with the same label have the same field encoding; this is safe because row identities are relation-local and the validated identity schema supplies the concrete type contract. Set operations resolve a common PostgreSQL output type before encoding. Altering a concrete type or replacing one type with another invalidates that schema and requires rebuild even when the resulting bytes would coincide. PostgreSQL major-version upgrades revalidate every registry contract before V2 state is consumed; an unverified major is rejected rather than assumed byte-compatible.

6.1 Initial scalar encoders

Type family Canonical ordered payload
bool one byte, false before true
int2, int4, int8 sign bit flipped, then big-endian
oid unsigned big-endian
float4, float8 sortable IEEE transform after canonicalizing signed zero and NaN
numeric ordered class, normalized base-10000 exponent, normalized digits; negative magnitudes reversed
text, varchar escaped database-encoding bytes
bpchar trailing-space-normalized bytes, then escaped
bytea escaped raw bytes
uuid 16 network-order bytes
date sign-flipped internal day count
time unsigned internal microseconds
timestamp, timestamptz sign-flipped internal microseconds; timestamptz is UTC
timetz sign-flipped GMT-equivalent microseconds, then sign-flipped stored zone offset in seconds west of UTC
interval sign-flipped interval_cmp_value() result as big-endian int128
inet, cidr family, prefix length, and canonical address bytes; cidr host bits are zeroed
macaddr, macaddr8 network-order address bytes
bit, varbit bit length followed by escaped packed bits
enum escaped label bytes, never enum OIDs
domain encoded as its base type

The float transform must place PostgreSQL NaN after positive infinity and map all NaN payloads to one quiet-NaN representation: 0x7FC00000 for float4 and 0x7FF8000000000000 for float8, before the sortable transform. Numeric must encode 1.0 and 1.00 identically, and its class order is negative infinity, finite values, positive infinity, then NaN, matching PostgreSQL. Interval uses PostgreSQL’s 128-bit interval_cmp_value(), which treats one month as 30 days for comparison; therefore 1 month and 30 days encode identically. The comparison value must remain 128 bits through the sign transform and big-endian encoding because legal interval values can overflow int64. The physical (months, days, microseconds) layout is not a valid identity encoding.

Enum labels are portable across dump/restore but mutable through ALTER TYPE ... RENAME VALUE. The existing ALTER TYPE DDL hook must continue to mark every directly and transitively dependent stream table for reinitialization before it can consume changes encoded with the new label. fn_extra caches enum dispatch metadata but not enum labels. For each enum datum, the encoder resolves the value OID through PostgreSQL’s ENUMOID syscache and copies the current label while holding the returned tuple. PostgreSQL’s commit-time syscache invalidation, not a custom pre-commit generation counter, governs cross-backend label visibility. This is an in-memory syscache lookup rather than SPI or a catalog scan, and its cost is part of the encoder benchmark. V2 tests both DDL-state invalidation and syscache visibility explicitly, including a two-session rename after the encoder call site is warmed.

The initial registry records these ordering and catalog dependencies:

Type family Default non-NULL B-tree order preserving Volatility Maximum encoded size DDL invalidations
bool, integers, oid, floats, numeric yes immutable fixed or typmod-bounded; unconstrained numeric is unbounded none
text, varchar, bpchar only under C/POSIX immutable typmod-bounded or unbounded collation change
bytea, uuid, date/time types, macaddr, macaddr8 yes immutable fixed or unbounded as defined above none
interval yes, using the full 128-bit comparison value immutable fixed none
inet, cidr no immutable fixed none
bit, varbit no immutable typmod-bounded or unbounded none
enum no; PostgreSQL orders by enumsortorder, not label bytes stable bounded by PostgreSQL’s enum-label limit enum label/order DDL
domain inherited from base type inherited from base type inherited from base type domain or base-type DDL

The listed inet/cidr and bit-string payloads are exact identities but do not match PostgreSQL’s native ordering. inet/cidr compare common network bits, mask length, and full address in a different sequence. Bit strings compare packed bits before using length as a tie-breaker. Enum label bytes likewise do not reflect enumsortorder.

6.2 Structural encoders

Arrays, ranges, multiranges, jsonb, and composite values may be supported only through explicit structural encoders:

  • arrays include dimensions, lower bounds, element count, and framed elements;
  • ranges include empty/infinite flags, inclusivity, and canonicalized bounds;
  • multiranges encode canonical ordered ranges;
  • jsonb is traversed structurally so object key order and duplicate-key input do not alter identity;
  • composites include a stable field count and recurse into supported field types.

Nesting depth and total encoded size are bounded by explicit resource limits. Exceeding a resource limit raises a contextual error; it never switches to a different identity algorithm.

Structural encoders do not need to ship in the first implementation commit, but the initial support matrix must name which structural families ship; every other type remains rejected. Adding a new type tag later does not change existing V2 identities and therefore does not require V3. Each structural entry also declares the registry properties above. Default non-NULL B-tree order preservation is false until comparison against PostgreSQL’s B-tree support function has been demonstrated. Nested volatility is stable if any component is stable and otherwise immutable; maximum size is unbounded if any component is unbounded; DDL invalidations are the union of all component invalidations. Composite alteration invalidates resolved field metadata and all dependent stream tables.

6.3 Collation policy

Text identity uses database-encoding bytes. For deterministic collations this agrees with PostgreSQL equality, although byte order may differ from a locale’s sort order. The locality guarantee for text is therefore exact for C/POSIX collations. Other deterministic collations preserve equality agreement but are marked non-order-preserving and receive no source-index locality claim.

Non-deterministic collations are rejected in identity fields. Distinct byte strings can compare equal under those collations, and ICU sort keys are not a stable persisted identity across library upgrades. Rejection is preferable to silently changing row identity after an operating-system update.

6.4 Validation

CREATE STREAM TABLE and ALTER ... SET QUERY validate every possible identity field before creating storage or capture state. Errors name the expression, resolved type, and unsupported property. Upgrade preflight reports every existing stream table that cannot be represented by V2 before migration changes anything.

7. Typed SQL Entry Point

The encoder must receive PostgreSQL datums and their actual types. The existing TEXT[] hash functions cannot be reused.

Generated SQL should call a new function shaped like:

pgtrickle.encode_row_id_v2(domain, ROW(expr1, expr2, ...)) RETURNS bytea

The C/Rust implementation obtains the record TupleDesc, deforms the tuple directly, and dispatches through the type registry. It caches the resolved field encoders in fn_extra, keyed by the anonymous record typmod. Every invocation validates the current typmod and rebuilds the cache when it changes, following PostgreSQL’s record-function cache pattern. Catalog lookup, function lookup, and type classification must not occur per row when the typmod is unchanged.

The implementation should write directly into one growing scratch buffer and copy only the completed varlena result into PostgreSQL-owned memory. It must not construct Vec<String>, call display output functions, or allocate once per field.

Because the initial registry supports label-based enum encoding, encode_row_id_v2 is STABLE and PARALLEL SAFE. Enum output is catalog dependent, so declaring the encoder IMMUTABLE would be false even with DDL hooks and forced reinitialization. The probe index expression calls only row_probe_v1(bytea), which remains genuinely IMMUTABLE and PARALLEL SAFE; the row-ID encoder itself does not appear in that generated index expression. Existing V1 hash functions retain their old behavior during migration and are removed only after no V1 trigger or stored expression can reference them.

8. Bounded B-Tree Indexing

A full canonical identity can be wider than PostgreSQL permits in one B-tree index tuple, especially for keyless rows and composite text keys. Indexing the full BYTEA without a static size proof would turn valid data growth into runtime index failures. V2 indexes the complete BYTEA directly whenever the identity schema is statically proven to fit. Only an unbounded schema uses the bounded probe expression described below.

Probe version 1 includes a fixed prefix length P, but this proposal does not freeze its value. Benchmarks must compare at least P = 32, 64, 128, and 256 bytes before the probe format is frozen. For the selected value:

if row_id.length <= P:
    probe = row_id
else:
    probe = row_id[0..P] || xxh3_128(row_id)

Properties:

  • the probe is at most P + 16 bytes;
  • ordinary integer, UUID, and short composite identities are indexed in full;
  • because complete identities are prefix-free, non-overflow probe ordering is exactly full-identity byte ordering, which matches native PostgreSQL ordering only for registry entries that guarantee it;
  • overflow probes sort first by their first P canonical bytes; identities that share that complete prefix sort by digest, not by their remaining bytes;
  • the digest normally distributes identities with a large common prefix across distinct probe keys; this is a performance property, not a correctness claim;
  • digest collisions do not affect correctness because MERGE also compares the complete row ID.

row_probe_v1(bytea) is an IMMUTABLE, PARALLEL SAFE index expression, not a stored column or public value. The index always derives its key from the same __pgt_row_id that the executor rechecks, so callers cannot supply a mismatched probe.

The implementation must validate both the direct-index size proof and the selected P against the running cluster’s actual BTMaxItemSize, including varlena and index-tuple overhead. The bridge binary computes V2 index readiness for the running BLCKSZ; migration preflight and every V2 create/alter operation reject V2 use if the probe’s worst-case datum cannot fit, while existing V1 state continues to operate. Direct-versus-expression classification is repeated for each identity schema at create, alter, and migration preflight. Errors report the configured page size, computed maximum datum size, and selected probe version. A prefix length or direct bound safe for the default BLCKSZ is not assumed safe for every supported build.

The identity index should not INCLUDE the full row ID or arbitrary user columns; doing so would reintroduce the B-tree tuple-width failure. Covering indexes, if useful, are separate optional indexes and must never be required for correctness.

Static boundedness is computed from identity field types and typmods: sum the fixed header/framing cost and each field’s worst-case escaped size in the database encoding. Fixed-width types have known bounds; bounded varchar(n), bpchar(n), bit(n), varbit(n), and numeric(p,s) use typmod-derived bounds; unconstrained numeric, text, bytea, JSONB, array, range, unconstrained composite, or any other unbounded field makes the whole schema unbounded. Conservative classification is required: uncertainty means unbounded.

An identity schema whose maximum encoded index datum is statically within BTMaxItemSize uses a direct B-tree index on __pgt_row_id. That index is UNIQUE only when the identity semantics are unique; bounded keyless/multiset storage uses a non-unique direct index. Every unbounded schema uses a non-unique expression index on row_probe_v1(__pgt_row_id) plus an exact full-ID recheck. The implementation must not place a UNIQUE constraint on a probe or digest.

Probe format and identity format are versioned separately. Changing prefix length or digest algorithm requires rebuilding expression indexes, but not rewriting heap rows or rebuilding logical row identities from source data.

9. Storage and DML Changes

Every storage, delta, temporary, CDC, and cache relation that currently carries a BIGINT row identity moves to __pgt_row_id BYTEA NOT NULL. Transport relations carry only the full identity. Consumers apply row_probe_v1 when the target schema is unbounded.

Bounded schemas match on full row-ID equality and use the direct B-tree. For a bounded unique schema, the existing upsert shape becomes ON CONFLICT (__pgt_row_id). Bounded keyless storage uses the same full-ID predicate against a non-unique direct index. Unbounded identities use MERGE or equivalent exact DML against the non-unique expression index, with both probe-expression equality and full row-ID equality in every UPDATE, DELETE, and MERGE predicate.

Keyless semantics do not change. Identical logical rows intentionally have the same full identity and may occur more than once, so their direct or expression index remains non-unique and counted-delete logic remains authoritative.

For unique but unbounded identity schemas, the database index is intentionally non-unique because no bounded B-tree key can prove uniqueness of arbitrary-length values. Scheduled and manual refresh retain the existing transaction advisory lock keyed by pgt_id and catalog-row serialization.

Initial V2 does not add per-identity advisory locks. IMMEDIATE mode rejects a unique unbounded identity during planning because the expression index cannot enforce uniqueness. A later concurrency RFC may enable that path only after it proves fresh-snapshot behavior, old/new-key acquisition, deadlock handling, subtransaction ownership, and a cluster-wide lock-pool budget across concurrent sessions. A per-transaction setting alone is not a sufficient capacity bound. Bounded unique identities remain eligible for concurrent IMMEDIATE maintenance because the direct UNIQUE (__pgt_row_id) index is authoritative.

RowIdStrategy and RowIdSchema continue to decide which logical fields form an identity. They do not select a storage encoding. V2 has one encoder.

10. Shared CDC State

A source change buffer may feed several stream tables. V2 is therefore extension-wide, not a per-stream-table option. Every consumer of a source uses the same source identity bytes and probe rules.

Trigger CDC and WAL CDC both emit full V2 identities. A consumer derives the V2 probe only when matching an unbounded target. No hot-path lookup of downstream stream-table preferences is required because no such preference exists.

The V2 extension-upgrade DDL adds non-null row_identity_version and row_probe_version columns to pgtrickle.pgt_change_buffers and the corresponding version state for stream-table storage. Buffer registry metadata records both versions. Runtime readers refuse mismatches before consuming a row. The guard is enforced in code, not only by extension upgrade SQL, because a new shared library can be installed before ALTER EXTENSION ... UPDATE runs.

11. Compatibility Contract

__pgt_row_id remains visible but changes type from BIGINT to BYTEA. Its meaning is opaque implementation identity:

Applications may compare or display __pgt_row_id, but must not parse it, generate it, or use it as a durable business identifier.

The V2 bytes are stable for the life of V2, but pg_trickle reserves the right to introduce V3 through another explicit rebuild before 1.0 if correctness requires it. Diagnostics should display the value in hexadecimal.

This is a breaking change for logical-replication subscribers, DuckLake sinks, outbox consumers, dbt models, and user SQL that assumes BIGINT. That break is accepted. Every stream table must be replaced anyway, so preserving the old type would only retain its collision and locality limitations.

External consumers must update their schema and resnapshot the replaced stream tables. There is no automatic cast from old numeric IDs to V2 bytes because the numeric value does not contain the original logical identity.

After rebuild, a bounded unique stream table may use its direct UNIQUE (__pgt_row_id) index as replica identity. The column is catalog-marked NOT NULL, satisfying PostgreSQL’s replica-identity index requirements. A publication that carries UPDATE or DELETE for that table must include __pgt_row_id in its column list. Subscribers store it in a regular BYTEA column; no generated publisher-to-generated-subscriber mapping is involved.

Unbounded or keyless stream tables use REPLICA IDENTITY FULL, and publication column lists must include every column PostgreSQL requires for that identity mode. row_probe_v1 is only an index expression and is never part of a publication schema. Operators must account for the resulting full old-row WAL, network, and subscriber-apply cost on UPDATE and DELETE; it is included in the replication benchmark matrix.

11.1 Security and privacy

“Opaque” is an API contract, not a confidentiality claim. V2 bytes contain reversible encodings of source keys and, for keyless identities, may contain every logical output field. Pass-through identity can retain an upstream key that a later projection no longer displays. The ordered prefix in an overflow probe also reveals the corresponding prefix of the canonical value. Hexadecimal display does not anonymize any of this data.

The implementation and integration documentation must therefore enforce these rules:

  • default user-facing views and grants do not expose __pgt_row_id; reading the internal column requires an explicit privilege or ownership;
  • logs, errors, traces, and monitoring report identity length and a short diagnostic fingerprint by default, not complete bytes or reversible prefixes;
  • publication, outbox, DuckLake, and other sink configurations include the row ID only when their update/delete contract requires it or an administrator opts in after reviewing the data classification;
  • operators treat identity fields containing personal data, credentials, tenant-private keys, or other secrets with the same controls as the source columns; and
  • dump, support-bundle, and diagnostic documentation warns that internal identities can retain values omitted by a downstream projection.

12. Upgrade and Stream-Table Recreation

V1 and V2 state cannot coexist in one refresh graph. Because there are no production V1 databases to preserve, V2 does not implement an in-place migration or a V1 change replay protocol. This is an intentional pre-1.0 breaking change: existing stream-table state is discarded and rebuilt from the unchanged source tables.

The supported upgrade procedure is:

  1. pause the scheduler and export each stream table’s definition, query, schedule, refresh mode, and dependency order;
  2. record external publication, outbox, dbt, DuckLake, and replication consumers that depend on __pgt_row_id or stream-table storage;
  3. drop V1 stream tables in reverse dependency order using the supported SQL API; source tables and their data are not dropped;
  4. remove the now-unused V1 change-buffer state through the extension’s cleanup path, with no attempt to replay it into V2;
  5. install the V2 extension build and restart PostgreSQL when the shared library has changed, so no backend retains V1 extension code;
  6. recreate stream tables in dependency order with the recorded definitions; each table receives a fresh V2 initial refresh; and
  7. recreate or resnapshot external consumers after updating them for the BYTEA row-ID contract, then resume the scheduler.

Source writes should be paused while the V1 graph is being removed and the V2 graph is being recreated. If writes cannot be paused, the operator must recreate the graph using the normal source-capture setup before allowing writes and accept that the initial refresh defines the new V2 state; V2 does not promise to replay changes made during the recreation window.

The V2 preflight is deliberately small and fail-safe. It validates all identity types, typmods, collations, source-key eligibility, operator classes, and PostgreSQL-major registry contracts; detects any remaining V1 stream tables or buffers; checks that external consumers are acknowledged for resnapshot; and reports unsupported identities before changing state. If V1 state remains, the operation fails with instructions to drop and recreate the affected stream tables. It never partially converts a table, changes source data, or mixes V1 and V2 buffers in one graph.

There is no V1_ACTIVE, MIGRATING_TO_V2, or crash-resumable cutover state in V2. A failed recreation is repaired by dropping the incomplete V2 stream-table graph and running the documented recreation procedure again. The initial refresh is the authoritative V2 baseline. Older binaries must reject V2 catalog state; V2 binaries must reject leftover V1 state rather than guessing how to convert it.

13. Performance Requirements

The encoder and probe run on a hot path. Correctness does not excuse avoidable allocation or lookup overhead.

Implementation requirements:

  • resolve TupleDesc and encoder dispatch once per call site;
  • no per-field String, SQL text cast, catalog query, or fmgr lookup;
  • one reusable scratch buffer per call site;
  • one pass over input values to produce the full identity;
  • compute overflow digest from the completed bytes only when length exceeds P;
  • keep the common fixed-width path branch-light;
  • preserve exact wire bytes across optimization changes.

Benchmarks must cover single integer, UUID, composite integers, short and long text composites, arrays/JSONB when supported, and keyless wide rows. They must measure encoding throughput, allocations, CDC overhead, index size, buffer hits, WAL volume, encoded-ID storage, CDC-buffer growth, cached MERGE latency, and MERGE latency with indexes larger than shared_buffers. Probe benchmarks must compare 32, 64, 128, and 256-byte prefixes across short keys, ordered wide keys, random wide keys, and long identities with common prefixes.

For every applicable bounded and unbounded workload, the physical comparison set includes:

  • direct B-tree on the complete bounded __pgt_row_id;
  • B-tree expression index on row_probe_v1(__pgt_row_id);
  • stored generated probe plus B-tree, including heap and WAL cost; and
  • PostgreSQL’s built-in hash index on the complete __pgt_row_id.

The built-in hash index is a particularly relevant unbounded, non-unique baseline: it accepts wide input, stores a four-byte hash, and lets the executor recheck complete BYTEA equality, but cannot enforce uniqueness or preserve prefix locality. The matrix also retains V1 as an end-to-end baseline. A material regression on cached common-key workloads must be investigated before merge. The expected V2 benefit is removal of text conversion and substantially better out-of-cache index locality where registry metadata supports that claim, not merely a different microbenchmark score. V2 may regress cached integer keys, randomly distributed deltas, wide keyless rows, and overflow identities that require TOAST access; the release decision must measure rather than assume those tradeoffs.

The release gates are index size, WAL volume, encoded-ID storage size, CDC-buffer growth, cached integer-key latency, out-of-cache ordered-key latency, overflow-key latency, and fresh initial-refresh throughput. The probe prefix and physical index policy are frozen only after these results identify the best acceptable tradeoff.

14. Testing

14.1 Encoder tests

  • golden byte vectors for every supported type, domain, NULL state, and boundary;
  • identical vectors on little- and big-endian targets where CI permits;
  • setting-independence tests for DateStyle, TimeZone, bytea_output, and locale-sensitive output;
  • equality-agreement property tests for every registry entry that prove a IS NOT DISTINCT FROM b if and only if encode(a) = encode(b), including numeric scale, signed zero, NaN payloads, bpchar padding, timestamp zones, extreme and equivalent intervals, and JSONB key order;
  • for entries marked default_nonnull_btree_order_preserving, property tests comparing the sign of bytea comparison with the sign of PostgreSQL’s B-tree comparator for non-NULL values under the default ascending operator class;
  • prefix-freedom and tuple-framing property tests;
  • explicit rejection tests for unsupported types and non-deterministic collations.

14.2 Probe tests

  • inline probes equal the full identity;
  • overflow probes never exceed P + 16 bytes and fit the running cluster’s BTMaxItemSize with all tuple overhead included;
  • differences in the first P bytes retain byte order;
  • long common-prefix identities produce narrow candidate lookups;
  • a test-only probe constructor can inject equal digests for distinct full IDs, proving that forced probe collisions still match only the exact full row ID;
  • direct full-ID indexes are selected whenever the proven datum bound fits the running cluster’s BTMaxItemSize;
  • unbounded identities select a non-unique expression index and no stored probe column; and
  • no UNIQUE index is created where overflow is possible.

14.3 Cross-path tests

The same logical identity must produce byte-identical output through trigger CDC, WAL CDC, full refresh, differential refresh, IMMEDIATE mode, joins, aggregates, set operations, windows, and downstream stream tables.

Tests must include primary-key updates, NULL group keys, keyless duplicates, deep join composition, synthetic identities, and wide TOASTed values.

Concurrency tests cover bounded unique identities under concurrent IMMEDIATE writes and unbounded identities under scheduled/manual refresh. Every unbounded unique IMMEDIATE path must raise feature_not_supported during planning, before capture, storage mutation, or advisory-lock acquisition, at every transaction isolation level. Enum tests use two sessions: session A warms the encoder cache, session B renames an in-use label and commits, and session A calls the cached expression again. Session A must either resolve the new label or reject the affected stream state; it must never emit stale bytes. The test also proves that the existing DDL hook marks the full downstream DAG for reinitialization before further refresh.

Replication tests prove that bounded unique tables use the direct non-null row-ID index as replica identity, required publication column lists include the row ID, and subscribers use a regular BYTEA column. Unbounded and keyless tables test REPLICA IDENTITY FULL. No schema or publication contains a generated probe column.

14.4 Upgrade and recreation tests

  • preflight detects V1 stream tables or buffers and makes no partial changes;
  • source tables and source data remain untouched while the V1 graph is removed;
  • V1 stream tables drop in reverse dependency order and V2 stream tables are recreated in dependency order;
  • the recorded query, schedule, refresh mode, and dependency graph recreate successfully;
  • source writes are paused during the recreation window, and the first V2 refresh produces the exact expected multiset;
  • an incomplete recreation is detected and can be repaired by dropping the partial V2 graph and starting again;
  • replacing the shared library requires the documented PostgreSQL restart, and old binaries reject V2 catalog state;
  • unsupported-type preflight fails before any stream-table state changes; and
  • external publications, outbox consumers, dbt models, DuckLake sinks, and replication subscribers are identified and resnapshotted after the BYTEA row-ID change.

The final oracle is a fresh V2 build from unchanged source tables: recreated output must be exactly equal as a multiset to an independent full computation.

15. Implementation Plan

Stage 1: Resolve and freeze the contracts. Write the normative byte specification, domain and type-tag registries, golden vectors, and type validation. Prove the 128-bit interval encoding and per-type equality/order contracts, specify volatility and DDL invalidation, benchmark candidate probe prefixes and all four physical index forms, and check BTMaxItemSize before freezing identity V2 and probe V1.

Stage 2: Implement the encoder. Add the typed record entry point, cached dispatch, scalar encoders, structural encoders required by the existing test surface, synthetic domains, and probe helper.

Stage 3: Change storage and matching. Create one BYTEA row-ID column, direct full-ID indexes for bounded schemas, expression-probe indexes for unbounded schemas, and exact DML. Remove assumptions that row IDs are numeric or that every non-keyless identity has a unique full-width index.

Stage 4: Centralize producers. Move trigger CDC, WAL CDC, scan, refresh, join, aggregate, set, window, and synthetic identities to the shared encoder. Delete ::TEXT row-ID construction and hard-coded numeric sentinels.

Stage 5: Add version guards and recreation tooling. Persist identity/probe versions, reject leftover V1 state, document the restart requirement, provide preflight output and cleanup for V1 stream tables/buffers, and document the reverse-drop/dependency-order recreation procedure outside ALTER EXTENSION UPDATE.

Stage 6: Prove it. Run the full correctness matrix, recreation tests, and performance benchmarks. Update SQL reference, architecture, upgrade, replication, DuckLake, outbox, dbt, and release documentation.

Stages may be separate pull requests, but V2 must not be user-selectable until all stages are complete. There is no supported partially migrated mode.

16. Alternatives Rejected

Keep BIGINT and hash a better encoding

This fixes ambiguous input but retains collisions and random index locality. It also requires the same rebuild of persisted state. It spends the migration cost without reaching the durable design.

Store a 128-bit or 256-bit hash in BYTEA

Using a digest alone destroys source-key ordering and still treats digest equality as row equality. Wider probability is not needed when exact canonical bytes can be stored. The overflow probe also contains a digest, but only as a bounded index accelerator; it is never authoritative and exact matching is always preserved.

Index every complete BYTEA directly

This is the selected design for statically bounded identities. Applying it to an unbounded identity would fail for sufficiently wide B-tree entries. The expression probe is reserved for that unbounded case and guarantees indexability without making a digest authoritative.

Store a generated probe column

This duplicates ordinary short identities in the heap, increases WAL, complicates replica identity and publication column lists, and cannot map generated-to- generated in PostgreSQL 18 logical replication. An immutable expression index provides the same unbounded lookup key without adding a public table column.

Add a direct-integer strategy

The V2 integer payload is already fixed-width and order-preserving. A second strategy would complicate shared buffers, derived identities, metadata, and migration for little practical gain.

Make BYTEA opt-in per stream table

Shared source buffers and downstream DAGs need one identity representation. Mixed strategies expand the state machine and preserve V1 indefinitely. V2 is an extension-wide format change.

Fall back to text or hash for unsupported types

A silent fallback creates two identity contracts and reintroduces settings, ambiguity, or collisions exactly where correctness is hardest to observe. Explicit rejection is safer and allows support to grow one tested type tag at a time.

17. Acceptance Criteria

The proposal is complete when implementation can demonstrate all of the following:

  • no SQL-facing row-ID path casts identity fields to text;
  • no final row identity is a hash or numeric sentinel;
  • supported unequal logical identities have different full bytes;
  • all indexed matching verifies the full identity;
  • bounded identities use direct full-ID B-trees, while unbounded expression-probe indexes remain bounded for arbitrary valid input size;
  • the selected probe prefix fits the running cluster’s BTMaxItemSize and is justified by the required benchmark matrix;
  • the normative wire document defines every byte and structural limit and its golden identity/probe vectors pass before implementation merges;
  • each registry entry proves equality agreement and declares default non-NULL B-tree ordering, volatility, size, DDL-invalidation metadata, semantic resolution context, and supported PostgreSQL majors;
  • nullable NULLS DISTINCT, deferrable, partial, expression, and unregistered non-default-opclass indexes cannot become SCAN_KEY identities;
  • interval identities encode the complete 128-bit PostgreSQL comparison value;
  • enum label caching is invalidated by enum DDL and the encoder is not declared IMMUTABLE while label-based enum support is enabled;
  • enum invalidation is observed across backends, not only in the backend that ran the DDL;
  • unsupported types and collations fail before state is created;
  • V1/V2 mismatch cannot consume or mutate persisted state;
  • V1 stream tables and buffers are detected before any partial upgrade;
  • source tables remain untouched while the old stream-table graph is removed;
  • recreated V2 stream tables use reverse dependency order for removal and forward dependency order for creation;
  • source writes are paused during recreation, and a fresh initial refresh is the correctness baseline;
  • replacing the shared library requires a full PostgreSQL restart, and old binaries reject V2 catalog state;
  • external compatibility breaks and resnapshot steps are documented and acknowledged before consumers resume;
  • bounded unique replica identity uses the direct non-null row-ID index, and publication schemas include every required identity column;
  • default views, grants, diagnostics, publications, and integration docs treat canonical row IDs as reversible source data rather than anonymized hashes;
  • unique unbounded IMMEDIATE identities are rejected before mutation until a separate concurrency RFC proves correctness and cluster-wide lock capacity;
  • out-of-cache composite-key workloads show the intended locality benefit;
  • cached common-key workloads have no unexplained material regression;
  • all performance release gates in section 13 pass.

18. Recommendation

Adopt exact canonical BYTEA row identities plus a bounded, non-authoritative probe as pg_trickle’s V2 architecture before 1.0. Do not freeze or implement the wire specification until interval encoding, enum volatility, per-type ordering, physical-index benchmarks, and durable migration meet the gates above. Keep unbounded unique IMMEDIATE maintenance disabled until a separate concurrency RFC is proven; it is not a V2 release dependency.

This is intentionally a breaking, extension-wide rebuild. That cost buys a row identity the project can keep: exact rather than probabilistic, typed rather than formatted, capable of preserving native order where proven, bounded at the B-tree boundary, and shared consistently across CDC and DVM.

The full identity is the source of truth. The bounded probe is only an index. That separation avoids both failure modes that otherwise force another redesign: unbounded BYTEA index entries and hashes treated as proof of equality.