Backfill Data Pipeline#

Sequin's backfill pipeline reads existing Postgres table rows and emits them as ConsumerEvent messages, filling in data before (or independently of) the WAL replication stream. The entry point is TableReader (lib/sequin/runtime/table_reader.ex), orchestrated by TableReaderServer — a GenStateMachine that manages the backfill lifecycle. Cursor state is stored in Redis as a keyset cursor, enabling resumable pagination.

Fetch Pipeline#

The full-row backfill runs in two query stages:

1. PK-only scan — fetch_batch_pks/4 issues a SELECT of only the keyset cursor columns (PKs + optional sort column) to page through the table efficiently. The select_only_pk_and_cursor_columns flag bypasses safe_select_columns for this pass, selecting only what's needed for pagination 1.

2. Full-row fetch — fetch_batch/6 issues a SELECT for all safe columns, converts the Postgrex.Result to maps via Postgres.result_to_maps/1, then post-processes values through Postgres.load_rows/2.

Query construction happens in fetch_batch_query/3, which uses KeysetCursor helpers to build tuple-comparison WHERE (col1, col2) > ($1, $2) and ORDER BY clauses for efficient keyset pagination.

Safe Column Selection#

Postgres.safe_select_columns/1 builds the SELECT list by filtering every column through the private has_encoder?/1. A column is included if:

• Its type starts with "time" (all timestamp/timezone variants) 2
• Its pg_typtype is "c" (composite), "d" (domain), or "e" (enum) — included optimistically, assuming Postgrex has a matching encoder (see known gap below) 3
• Its base type (after stripping trailing []) appears in the @safe_types allowlist of ~40 known Postgrex-encodable types (int4, uuid, jsonb, vector, hstore, etc.)

Columns failing all three checks are silently excluded from the SELECT and will be absent from backfill row maps — logged at info level 4.

Known Gap: Composite Types#

Custom composite types pass the pg_typtype = "c" check and appear in the SELECT. However, if their Postgrex-decoded struct has no Jason.Encoder implementation, load_rows/2 silently returns nil for that column's value 5. The code comment acknowledges this: the actual base type isn't stored in Column, so the check is optimistic 6. This is the primary source of composite type columns being nulled out during backfill.

Postgrex Decoding vs. WAL Text Decoding#

This is the sharpest architectural divergence in Sequin's decoding layer. The two paths share no code and handle the same semantic values very differently:

load_rows/2 runs over a full row map after Postgrex binary decoding. It handles serialization concerns: hex-encoding bytea/bit, string-converting UUID binaries, formatting ranges/intervals — and returns nil for any value with no Jason.Encoder 12.

ValueCaster is invoked per field in data_tuple_to_fields/3 inside SlotProducer.Processor. It receives text-format column values from the WAL decoder and wraps each as a Field struct. JSON decode errors raise immediately with row IDs for debugging 11.

Key Source Files#