# sift_table: a columnar table access method on the sift_col engine

`CREATE TABLE orders (...) USING sift_table` keeps the rows of the table in compressed column groups with
**native MVCC**: no heap copy, no heap visibility map, no heap checks. The table is a normal PostgreSQL table: `INSERT`, `COPY`, `UPDATE`,
`DELETE`, `SELECT ... FOR UPDATE`, `TRUNCATE`, `VACUUM [FULL]`, `ANALYZE`, `CLUSTER`, `ALTER TABLE` rewrites, btree and BM25 (`sift`)
indexes, unique constraints, foreign keys, triggers, `pg_dump`, `pg_basebackup`, `pg_upgrade`, physical replication and (with one
requirement, section 5) logical replication.

Names. Table access method `sift_table` (`pg_am.amname` is unique across index and table AMs: the BM25 index AM is `sift`, the column
index AM is `sift_col`). Handler `sift.table_handler`, SQL in `sql/pg_sift--3.0.sql` and `sql/pg_sift--1.2--3.0.sql`. C identifiers `sift_tbl_*`, files
`src/table/*.c/h` (`sift_table_*` is taken by core/compat.h macros). Test functions `sift._test_tbl_*`. This document is the contract of
the table AM; where it and the code disagree, fix one in the same commit.

Running example (used in tests too): `orders(id bigint, tenant_id int, placed_at timestamptz, status text, total numeric(12,2), items jsonb,
metrics jsonb)` where `items` is an array of `{sku, qty, price}` objects and `metrics` an object of floats, and `stores(id int, name text)`.

---

## 1. Decisions in one page

| area | decision |
|---|---|
| Storage | the page store of `core/store.h` (metapage, directory, free list, extents, generic WAL, P1-P6 crash protocol), reused unchanged except one generalization (`sift_store_removable` on a table). |
| TIDs | **synthetic and never reused**: a TID is `(vblock, offset)` with `offset` in 1..`MaxHeapTuplesPerPage` (291) so every index AM, TID bitmaps and GIN accept it. `vblock` comes from a counter in the metapage and never decreases; it is *not* a physical block number. A superblock (SB, 256 vblocks, 74,496 TIDs) is the engine's unit, so the engine's row directory returns the table's own TIDs. |
| Row versions | **write buffer + groups.** New rows go to a row-format *write buffer* (`SIFT_PT_WBUFDATA` pages, directory kind `SIFT_SEG_WBUF_*`) with `xmin`/`cmin` per row. Promotion (section 7) turns a write buffer whose rows are all final into a frozen **column group** (an engine immutable segment, same TIDs) and, under directory pressure, any full write buffer into an **unfrozen group** (per-row `xmin`/`cmin` in version vectors, format 4; VACUUM freezes it when its rows are final). COPY goes through the write buffer. VACUUM FULL and CLUSTER write frozen column segments directly. |
| Deletes, updates, locks | **tombstones**: one 32-byte entry (`tid`, deleter `xid`, `cid`, kind, successor TID) appended to one per-relation **tombstone segment**. A row version is visible iff its insert is visible and no non-aborted DELETE/UPDATE tombstone of it is visible. UPDATE = new row in the write buffer + UPDATE tombstone carrying the successor TID (the update chain `FIND_LAST_VERSION` and EvalPlanQual follow). An UPDATE that keeps every column the indexes read is HOT (`TU_None`): its tombstone carries `SIFT_TOMB_HOT` and no index gets an entry for the new version, the old version's entries lead to it (section 2.4). |
| Why tombstones | the deleter's `xid`/`cid` in an append-only log gives MVCC for every snapshot type with the PostgreSQL transaction machinery (subtransactions, savepoints, own-command visibility, aborts) and never rewrites a column group. |
| Lock modes | all four `FOR ...` modes are supported and behave as one exclusive row lock between different transactions (no MultiXact): `FOR SHARE`/`KEY SHARE` of two transactions on the same row serialize. Foreign keys to a `sift_table` parent therefore serialize concurrent children of the same parent row; correct, not fast. |
| TOAST | none (`relation_needs_toast_table` false): values are stored inline, external TOAST pointers are expanded at insert. A row larger than a write buffer page continues in overflow pages; one too large for the buffer's page table (`sift.table_wide_rows`, directory format 11) continues in tail pages (`SIFT_TBL_PAGE_OVFDATA \ | SIFT_TBL_PAGE_TAIL`) that are in the page chain only, take no vblock, and are read through the record's `aux` and `ovf_next` links. In a group a value is an engine leaf (compressed with the column, section 8); jsonb keeps its exact bytes next to the shredded paths of col-json. |
| WAL | **hybrid** (section 5): generic WAL for every page change (no preload needed; physical replication, PITR, `pg_basebackup`, `pg_rewind` work), plus one custom-rmgr *logical change record* only when the relation is published and `wal_level = logical` (needs `pg_sift` in `shared_preload_libraries` on the primary and every standby; without it DML on a published table fails with a clear error, never a silent gap). |
| Isolation | READ COMMITTED, REPEATABLE READ, SERIALIZABLE (relation-level SIREAD lock, conflict-in on every write, conflict-out on invisible concurrent versions: conservative, more false positives than heap). |
| Parallel | parallel sequential scan over write buffer pages and (engine) SB morsels, one frozen directory copy shared by all workers. Parallel DML is not offered by PostgreSQL for any AM. |
| Aggregates | the SiftAgg pushdown of col-plan runs over the table through the engine's `SiftColVisibility` hook (`sift_col_open_table`, section 12); write buffer rows are fed as in-memory batches (section 6). |
| Options | **settings, not reloptions** (PostgreSQL has no extensible table AM options: an unknown option in `CREATE TABLE ... WITH (...)` is an error): `sift.table_write_buffer_limit` (kB), `sift.table_inline_promote`, `sift.table_compression`, `sift.table_dead_rewrite_pct` (section 8; docs/USAGE.md, Maintenance and operations); heap reloptions PostgreSQL parses for every table (`autovacuum_*`, `vacuum_truncate`) apply. Per-column compression is the `column=policy` form of `sift.table_compression`. `sift_group_rows` and `sift_paths` do not exist: jsonb is stored as exact bytes (section 8). Kernels follow `sift.simd`. |
| Maintenance | VACUUM does everything (tombstone sweep, dead-row liveness, index cleanup, promotion, merge, stats); autovacuum triggers through the AM's `pgstat` counters (inserts alone trigger it: `autovacuum_vacuum_insert_threshold`); inline promotion when a write buffer fills (`sift.table_inline_promote`); without VACUUM a worker does the space work that needs no index (`sift.table_maintain`, section 7, `sift.table_auto_defrag`). |

## 2. Layout

### 2.1 Relation, directory, metapage

Main fork only (no FSM, no VM: `pg_visibility` and index-only scans see a table without a VM, which is correct: they take the AM path). Blocks 0 (meta) and 1 (directory) as in `core/store.h`; everything else comes from the free list / high-water mark via `sift_alloc_*`. The metapage carries `flags & SIFT_TBL_META_FLAG` (0x00020000) and reuses fields a table does not need:

| field | in a table |
|---|---|
| `sum_dl` | **next vblock** (the TID block counter). Reserving a range is part of the directory change that creates the segment, so a crash never hands out a range twice. |
| `ndocs` | rows of the immutable segments (statistics) |
| `k1`, `b`, `analyzer_hash` | 0 |

`table/table.h` has the one accessor (`SIFT_TBL_NEXT_VBLOCK`).

Directory entries (`SiftSegEntry`):

| kind | entry | meaning |
|---|---|---|
| `SIFT_SEG_IMMUTABLE` | `flags & SIFT_SEG_VERS` when some of its rows are not final (an **unfrozen group**), then `reserved` = the smallest xid among them (a group of 3.0.1 that is not frozen has `reserved` = its writer's xid, format 3); `sb_lo..sb_hi` = SBs with rows, `ndocs` = rows | an engine column segment, schema = table columns + shredded leaves; an unfrozen group keeps the `xmin` and `cmin` of its rows in the version section of the segment (section 7); the entry's `sum_dl` is not touched: the store totals it into the metapage, where a table keeps the next vblock |
| `SIFT_SEG_WBUF_OPEN` / `_SEALED` | `flags & SIFT_TBL_SEG_WBUF`, `sb_lo..sb_hi` = SBs of its vblock range | a write buffer; at most one active, sealed ones wait for maintenance |
| `SIFT_SEG_WBUF_OPEN` | `flags & SIFT_TBL_SEG_TOMB` | the one tombstone segment (swapped for a rewritten one by VACUUM) |

At most `SIFT_MAX_SEGMENTS` (128) entries: the policy keeps 1 open write buffer, 1 tombstone segment, a few sealed write buffers and the tiered immutable segments. An insert that cannot open a write buffer because 112 entries are in use runs maintenance and, if the write buffers cannot be promoted, fails with `ERRCODE_PROGRAM_LIMIT_EXCEEDED` (detail "Too many write buffers waiting for promotion"). Once the directory is half full every write buffer becomes an unfrozen group (section 7), so a load of any size (one command or single-row INSERTs, one writer or several, with savepoints) does not reach the limit; a transaction whose write buffers hold a deleted row VACUUM has not seen, or a speculative insertion in progress, does. The limit is the testing setting `sift.table_max_segments` (default and maximum 128), which the regression tests lower.

Write buffers and tombstone segments use the lane header (`SiftWbufHeaderData`, lane 0 only) and the chain `next` pointer at the start of every data page, because the store walks write buffer lane chains to mark referenced blocks; the orphan sweep, `sift_store_verify` and the free-list protocol serve a table unchanged.

### 2.2 TIDs

`TID = (vblock, off)`, `off` in 1..291; `key = vblock << 16 | off` is the engine's `SiftKey`; SB = `vblock >> 8`. A write buffer owns the vblock range `[vbase, vbase + vcap)` (one vblock per page of the segment, in the order the pages were added). A bulk segment is numbered densely (vblock `base + i / 291`, offset `i % 291 + 1`), the engine's dense-page case. A vblock is never reused, so an index entry need not be removed before a TID is recycled: a dangling entry resolves to "no such row" (the fetch returns false), and VACUUM removes index entries only to keep indexes small. TIDs beyond `RelationGetNumberOfBlocks` are normal (`PrefetchBuffer` past EOF is harmless); `pg_relation_size` is the physical size.

`TID -> storage`: directory (binary search by SB range, then the unit) for groups; for a write buffer the header page holds `page[i]` (physical block of vblock `vbase + i`).

### 2.3 Write buffer (row format, the write buffer)

Header page (`SIFT_PT_WBUFHEADER`): `SiftWbufHeaderData` (lane 0 = the chain) then `SiftTblWbufHdr { magic, flags, vbase, vcap, npages, nrows, page[vcap] }` (`vcap` <= 1024, set by `sift.table_write_buffer_limit`, default 4 MB = 512). Data page (`SIFT_PT_WBUFDATA`): `SiftTblPageHdr { next, nrows, flags (SEALED, OVERFLOW), vblock, ovf_next, slot[291] u16 }` then records forward; a record is `{u32 len; xmin; cmin; u16 flags; u16 natts-hint}` plus a MinimalTuple (as `heap_form_minimal_tuple` makes it, so `ALTER TABLE ADD COLUMN` with a default is handled by `heap_deform_tuple`'s missing-attribute rule). TID offset = slot number + 1; slots never move.

Appending: a backend keeps a *target page* (a vblock it owns for appends) in `rd_amcache`, locks it exclusively, appends and logs the page delta (generic WAL), at most one WAL record per call, or per page for `multi_insert`. When the page is full it claims the next page under the header page lock (header, then chain tail: never the reverse), checking `SEALED`. Concurrent inserters never share a page. Overflow records (a row over ~7.5 kB) take consecutive pages marked `OVERFLOW`, linked by `ovf_next`; the TID's slot holds the head and the total length.

### 2.4 Tombstone segment

Header page as above (`SiftTblTombHdr { magic, nentries, tail }`), chain of data pages of `SiftTblTomb[]` (32 bytes: `u64 key`, `u32 xid`, `u32 cid`, `u64 succ`, `u16 kind` (DELETE, UPDATE, LOCK), `u16 flags`, `u32 pad`). Entries are written once and never edited; whether one counts is decided by the status of its `xid` at read time. A backend caches the entries as a sorted array and extends the cache incrementally from `nentries` (append-only): a lookup is a binary search, and with no entry (`nentries = 0`, an insert-only table) a row check costs one comparison. VACUUM writes a new tombstone segment without the entries that no longer matter (section 7) and switches it in with `sift_dir_update` (old pages are released with a `safexid`, so readers of the old copy keep valid pages: P4).

**HOT chains.** An UPDATE entry with `flags & SIFT_TOMB_HOT` says the new version (`succ`) is a *member* of the chain of the version it replaced: no index has an entry for it, and an index entry of the first version (the *root*) stands for the whole chain. `index_fetch_tuple` and the bitmap scans follow the HOT links of a version that is not visible to the snapshot, up to the version that is (or the end of the chain), and hand the slot that member's TID; `all_dead` is true only when every version of the chain is dead for everybody. A page scan of a bitmap skips the members and reaches them from their roots. The decision is taken at the UPDATE, as the heap's: the new version has the same binary datums in every column of every index (key, included, expression and predicate columns, `RelationGetIndexAttrBitmap(INDEX_ATTR_BITMAP_HOT_BLOCKING)`), the table has no summarizing index, and no index of the table is a `sift_col` or `sift` (BM25) index, whose heap check decides the visibility of a page of TIDs itself and does not follow a chain. VACUUM reports a root to the indexes only when its whole chain is dead; the rewrite of the log keeps the entry of a root whose chain lives, moved to the first link that is not removable and with a frozen xid (a log entry read as an update every snapshot sees, holding no xid back), and drops the entries of the members. An index built over chains has one entry per chain, at the root's TID, with the columns of the newest version that counts (`ii_BrokenHotChain` when an older one counts too). A release before this one reads a HOT entry as a plain update: the rows reached by an index entry of a root are lost to it, so a table with HOT updates cannot go back.

DELETE/UPDATE/LOCK of one TID is serialized by a heavyweight `LockTuple(rel, tid, ExclusiveLock)` held while the caller checks the cache against the log and appends, as `heap_delete` does, so deadlock detection and `XactLockTableWait` work as for any heap table.

## 3. MVCC

A row version has (insert state, delete state):

- **insert state**: write buffer row: the record's `(xmin, cmin)`, plus the `DEAD` record flag (aborted speculative insertion, or removed by VACUUM); group row: its `xmin` and `cmin` from the version vectors of an unfrozen group (`sift_tbl_ver_state`, `sift_tbl_vers_rowver`), else always inserted (frozen).
- **delete state**: the non-LOCK tombstone entries for its TID, in log order; a row can have several (aborted ones, then a committed or in-progress one). LOCK entries never delete.

Visibility per snapshot type (`sift_tbl_visible`, one function for every caller: scan, fetch, bitmap, tuple_satisfies_snapshot, index_delete_tuples, analyze, index build, SiftAgg's hook):

| snapshot | row is visible when |
|---|---|
| MVCC | inserted: `xmin` is the current transaction with `cmin < curcid`, or not in the snapshot and committed (`XidInMVCCSnapshot`, `TransactionIdDidCommit`; a hint on the record flags it so a committed record costs no clog lookup) **and** no tombstone is *deleted-visible*: same rule on the tombstone's `xid`/`cid` (`cid < curcid` for the current transaction). |
| Self | as MVCC with every command of the current transaction counted as done |
| Any | always (aborted rows included, as `SnapshotAny` on a heap; deleted rows included) |
| Dirty | inserter in progress (not me): visible, `snapshot->xmin = xmin`, and for a speculative record `snapshot->speculativeToken`; aborted inserter: no. A tombstone of an in-progress transaction (not me): row visible, `snapshot->xmax = xid`; of me or committed: row deleted. The btree uniqueness check, `INSERT ... ON CONFLICT` and the foreign key checks rely on exactly this. |
| NonVacuumable | not deleted, or the deleter is not yet removable (`GlobalVisTestIsRemovableXid`); used by the planner's `get_actual_variable_range` |
| historic MVCC, toast | not used on this AM |

**Updates and chains.** `tuple_update` locks the TID (`LockTuple`), syncs the cache and evaluates the TID's tombstones:

- none live and the row visible to `snapshot`: insert the new version (write buffer, `xmin = me`), append `UPDATE{succ = new}`;
- a live tombstone of a transaction in progress: `TM_BeingModified` (`wait = false`) or release the tuple lock, `XactLockTableWait`, retry;
- committed UPDATE: `TM_Updated` with `tmfd->ctid = succ`, `xmax`, `cmax`; committed DELETE: `TM_Deleted`; by me with `cmax >= cid`: `TM_SelfModified`; REPEATABLE READ and above see the same results and the executor raises the serialization failure, as for heap.

`tuple_lock` with `TUPLE_LOCK_FLAG_FIND_LAST_VERSION` follows `succ` while the entry is a committed UPDATE and fetches the latest version into the slot (so READ COMMITTED's EvalPlanQual, the logical-replication apply worker and `ON CONFLICT DO UPDATE` work). `ctid` and `tableoid` work (`slot->tts_tid`). `xmin`, `xmax`, `cmin`, `cmax` are the row's stored versions, read by TID when asked (`sift_slot_getsysattr`): `xmin`/`cmin` of the write buffer record or of the unit's version section (a frozen row: `FrozenTransactionId`, command 0), `xmax`/`cmax` of the row's last tombstone, delete, update or lock, aborted or not as the heap keeps them (none: 0). A row locked by several transactions shows the last locker, not a MultiXactId. The logical-replication apply worker reads `xmin` for its conflict report.

**Speculative insertion** (`INSERT ... ON CONFLICT`): the record carries the token; `tuple_complete_speculative(succeeded = false)` sets its `DEAD` flag, so a killed insertion is never visible and other inserters waiting on the token proceed; `succeeded = true` clears the speculative flag (a row left speculative would keep its write buffer from being promoted).

**SERIALIZABLE.** Scans and fetches take `PredicateLockRelation`; every write calls `CheckForSerializableConflictIn(rel, NULL, InvalidBlockNumber)`; an invisible row or tombstone of a concurrent transaction calls `CheckForSerializableConflictOut`. Relation granularity is the price of having no heap pages: correct, with more false positives than heap.

**Unfrozen groups.** The rows of a group that is not frozen are as invisible as write buffer rows until their writer's snapshot rule says otherwise. The scan, index scan, bitmap scan, `tuple_fetch`, parallel scan, SiftAgg's hook and the analyze and index build paths read each row's `xmin` and `cmin` from the version vectors of its unit (`SiftTblVers`, built per directory snapshot, cached by generation) and apply the write buffer rule (`sift_tbl_visible`); a unit whose summary decides for all its rows is not read row by row (`sift_tbl_ver_state`: all visible, none, or per row), and `CheckForSerializableConflictOut` is called as for an invisible row. A row's unit is found from its superblock: a group's superblocks belong to it alone, because a write buffer is given whole superblocks of vblocks (`create_wbuf`).

- Aborted writers (rollback, savepoint rolled back, crash): VACUUM finds the rows whose xid aborted in the vectors, the index cleanup forgets them and their liveness bits are cleared (the next merge drops the rows; a group with nothing live left is dropped).
- A group stays unfrozen until its live rows are committed and older than the horizon; then VACUUM clears the entry's flag (`sift_tbl_freeze_groups`), the vectors are ignored and the next merge rewrites it frozen.
- Live-row counts are exact for a frozen group and count the committed or own-transaction rows for an unfrozen one (`unfrozen_live_rows`), as `relation_estimate_size` and ANALYZE read them.

**Freezing and wraparound.** Frozen group rows have no xid. Write buffer rows older than the horizon are promoted into frozen groups; the xids of an unfrozen group are frozen with it; tombstones and bulk-segment `xmin`s are removed or reset to 0 by VACUUM. `relation_vacuum` computes the oldest xid still stored (write buffer rows, tombstones, segment xmins, `xmin`s of in-flight work) and passes it to `vac_update_relstats` as `relfrozenxid`; MultiXacts are not used, `relminmxid` follows the cutoff. So anti-wraparound autovacuum works and ends.

**Rows and the snapshot of the scan.** A scan registers its snapshot (`MyProc->xmin`, so P4 protects the extents), copies the directory once and reads write buffer pages as it goes: a row appended after the scan began has a later `xmin` (or a later `cmin` in this transaction) and is invisible; a promotion that replaces a write buffer with a group publishes the same TIDs and rows, and the scan keeps its old copy, whose pages are not reused until the scan's snapshot is gone.

## 4. TableAmRoutine: every callback

PostgreSQL 17, 18 and 19; differences are marked. Every callback below is implemented and tested.

| callback | decision | status |
|---|---|---|
| `slot_callbacks` | `TTSOpsHeapTuple`: every row is built as a HeapTuple (`t_self` = its TID) and handed to the slot with `sift_tbl_store`, which moves it into the slot's own memory context (a scan runs in a per-tuple context that is reset under it). All system columns work (`xmin`, `xmax`, `cmin`, `cmax` are read by TID when asked). | done |
| `scan_begin` / `scan_end` / `scan_rescan` | walks write buffers (page by page, rows in slot order), then groups (engine units, in SB order). Snapshot registered, directory copied once. `SO_TYPE_SEQSCAN`, `SO_TYPE_ANALYZE`, `SO_TYPE_BITMAPSCAN`, `SO_TYPE_TIDRANGESCAN` honored; scan keys unsupported (the planner never passes them for a non-heap AM). | done |
| `scan_getnextslot` | both directions, as a heap: the cursor is on the row last returned, so a backward step gives the one before it, an end resets the position (forward restarts at the first row, backward at the last). Backward goes over the column groups from their last unit, then the write buffer pages from the last; the units of the range are listed on the first backward step and found again by superblock and place in it; no unit is decoded twice per step. | done |
| `scan_set_tidrange` / `scan_getnextslot_tidrange` | by SB/vblock range on the directory, then the filter `mintid <= tid <= maxtid`. | done |
| `parallelscan_estimate` / `_initialize` / `_reinitialize` | `ParallelBlockTableScanDescData`-like header + a frozen copy of the directory + an atomic unit counter (a write buffer page, then one superblock of the column groups per unit). | done (`t/008_tbl_promote.pl`) |
| `index_fetch_begin/reset/end` | per-fetch state caches the directory copy, the open segment readers, the last decoded unit and the tombstone cache; no buffer is held between calls. | done |
| `index_fetch_tuple` | TID -> storage (2.2), visibility (3), materialize into a virtual slot. `call_again` always false (a HOT chain is followed to the first version the snapshot sees, 2.4); `all_dead` true when the row is dead for every snapshot (no such row, final tombstone, aborted insert) so btree can kill the index entry. Honors Dirty, Self, Any, NonVacuumable. | done |
| `tuple_fetch_row_version` | as above with `slot->tts_tid`; used by triggers, RETURNING of old rows, MERGE, `WHERE CURRENT OF`. | done |
| `tuple_tid_valid` | `off` in 1..291 and `vblock < next_vblock`. | done |
| `tuple_get_latest_tid` | follows `succ` of committed UPDATE tombstones visible to the snapshot. | done |
| `tuple_satisfies_snapshot` | `sift_tbl_visible` on the slot's TID. | done |
| `index_delete_tuples` | per TID: no such row, or a deleter removable now: `knowndeletable`; conflict horizon = the largest deleter xid among them (else `InvalidTransactionId`, nothing deletable). | done |
| `tuple_insert` | write buffer append (2.3), `slot->tts_tid` set, `xmin = GetCurrentTransactionId()`, `cmin = cid`; `pgstat_count_heap_insert`; conflict-in; logical record (5). `options` `TABLE_INSERT_SKIP_FSM`/`FROZEN`/`NO_LOGICAL` accepted (FROZEN is valid only in a rewrite and writes a frozen row). | done |
| `tuple_insert_speculative` / `tuple_complete_speculative` | record with `SPEC` flag and token; completion kills or confirms (3). | done |
| `multi_insert` | one write buffer page fill per WAL record; above `sift_group_rows` rows (or a COPY of that size) writes a column segment directly with segment-level xmin (bulk path). | done (row at a time through the write buffer; no bulk column path) |
| `tuple_delete` | tombstone (3); `wait`, `crosscheck`, `changingPart` honored (a partition move records `tmfd->ctid` = the "moved partition" TID like heap: `ItemPointerSetMovedPartitions`). | done |
| `tuple_update` | new version + UPDATE tombstone (3); `*update_indexes = TU_All`, `*lockmode = LockTupleExclusive`. | done |
| `tuple_lock` | LOCK tombstone unless the transaction already holds one or deleted the row; `LockWaitPolicy` block/skip/error; `FIND_LAST_VERSION`; `TUPLE_LOCK_FLAG_LOCK_UPDATE_IN_PROGRESS`; fills the slot with the locked version. | done |
| `finish_bulk_insert` | seals the write buffer if the bulk filled it and nothing else is pending; flushes a partial bulk segment; nothing to `fsync` (the store handles `wal_level = minimal`, as heap through `smgrDoPendingSyncs`). | done |
| `relation_set_new_filelocator` | creates the fork, writes meta + directory + the empty tombstone segment (init fork for UNLOGGED, as `ambuildempty`), returns `*freezeXid = RecentXmin`, `*minmulti = GetOldestMultiXactId()`. | done |
| `relation_nontransactional_truncate` | rewrites meta/directory empty (TRUNCATE itself creates the new relfilenode; this callback is the `TRUNCATE` of a relation created in the transaction and of temp tables). | done |
| `relation_copy_data` | `ALTER TABLE SET TABLESPACE`, `SET LOGGED/UNLOGGED`: block-for-block copy of the fork (`RelationCopyStorage` through smgr, WAL-logged per page); the store is position independent, so the copy is exact. | done |
| `relation_copy_for_cluster` | `CLUSTER`, `VACUUM FULL`: reads the old table's stored versions (live rows kept; recently dead rows counted in `tups_recently_dead` and dropped, as are rows the rewriting transaction deleted), writes frozen column segments directly (section 7), sorted by the btree `OldIndex` for CLUSTER, new dense TIDs; reports `num_tuples`, `tups_vacuumed`, `*xid_cutoff = OldestXmin`. | done |
| `relation_vacuum` | section 7. | done |
| `scan_analyze_next_block` / `_next_tuple` | PG 17+: the `ReadStream` is only a budget (its buffers are released unread); the AM walks sampling units (write buffer pages, engine units chosen at random up to `300 * max statistics target` rows) and reports `liverows` so that `totalrows` equals the exact live count. | done |
| `index_build_range_scan` | `CREATE INDEX [CONCURRENTLY]`, `REINDEX`: iterates the SB range `[start_blockno, start_blockno + numblocks)` in vblock space (the parallel btree build splits it), `SnapshotAny` + own visibility for the concurrent build (rules of `heapam_index_build_range_scan`: in-progress inserters and deleters are waited for with `XactLockTableWait` when not concurrent, indexed anyway when concurrent), partial index predicates, expression indexes, `anyvisible`, progress reporting. | done |
| `index_validate_scan` | second pass of `CREATE INDEX CONCURRENTLY`: merge-join of the sorted index TIDs (`tuplesort`, `ValidateIndexState`) with a scan of the table at the validation snapshot, inserting the missing visible rows. | done |
| `relation_size` | physical size of the main fork (`RelationGetNumberOfBlocks * BLCKSZ`). | done |
| `relation_needs_toast_table` / `relation_toast_am` / `relation_fetch_toast_slice` | false / unused / unused (no TOAST table is ever created). | done |
| `relation_estimate_size` | pages = the larger of the physical blocks and twice the pages the rows take in a heap (a scan builds every row: 1.7 times the instructions of a heap scan, whatever the compressed size), tuples = group rows - tombstoned + write buffer rows, from the meta counters (not `reltuples`, which ANALYZE of a sampled table only estimates), `allvisfrac` = 0 (no VM). Widths as the heap rule. | done |
| `scan_bitmap_next_block` (17) / `scan_bitmap_next_tuple` (17, 18) | the TID bitmap is walked here (18: iterator inside the scan): for a page `(vblock, offsets...)` rows are fetched in TID order, exact pages recheck-free, lossy pages test every row of the vblock; `exact_pages`/`lossy_pages` counted for EXPLAIN. Serves btree, BM25 `sift`, GIN, BRIN indexes unchanged. | done |
| `scan_sample_next_block` / `_next_tuple` | `TABLESAMPLE SYSTEM` picks vblocks, `BERNOULLI` walks them; the visible rows of a vblock are read at once and the method's offsets pick among them. | done |

Planner: no custom path is needed for a plain scan (AM costs through `relation_estimate_size` and the scan cost model suffice once the page estimate stands for the work of building rows, not the compressed size, which a VACUUM that gives the free space back makes a tenth of it). SiftAgg and SiftScan paths (col-plan) come from their own hooks and take the table through the engine source of section 6.

## 5. WAL, replication and what works where (the decisive part)

**PostgreSQL facts this rests on** (PostgreSQL 17 and 18 sources: `access/xlog_internal.h`, `replication/logical/decode.c`; docs "Generic WAL Records" and "Custom WAL Resource Managers"):

1. Generic WAL records (`RM_GENERIC_ID`) are replayed by every server but *not* decoded: rows written only through generic WAL are invisible to logical replication.
2. A custom resource manager (`RegisterCustomRmgr`, id 128..255 reserved for development or registered on the PostgreSQL wiki) must be registered from `_PG_init` during `shared_preload_libraries` processing, on the primary **and on every server that replays its WAL** (a standby without it stops recovery with "resource manager not registered"). `RmgrData.rm_decode` (present in 17 and 18) is called by `LogicalDecodingProcessRecord` for the rmgr's records and may call `ReorderBufferQueueChange` with `REORDER_BUFFER_CHANGE_INSERT/UPDATE/DELETE`, a `RelFileLocator` and a heap-format tuple; the built-in `pgoutput` then publishes it like a heap change (maps the locator to the relation, `heap_deform_tuple`, applies the row filter and column list).
3. `pgoutput` takes UPDATE/DELETE keys from the old-tuple image; the executor checks replica identity (`CheckCmdReplicaIdentity`) for every AM, so a published `sift_table` needs one for UPDATE/DELETE like a heap table.

**Options compared.**

|  | generic WAL only | custom rmgr for everything | **hybrid (chosen)** |
|---|---|---|---|
| `shared_preload_libraries` | no | **yes, always, everywhere** | only to publish the table |
| physical standby, PITR, `pg_basebackup`, `pg_rewind` | yes | yes (all nodes preload) | yes (all nodes preload only if the logical record is ever emitted) |
| logical decoding / publications | **no, silently nothing** | yes | yes |
| `wal_consistency_checking` | masks as for sift today | needs `rm_mask` | as generic |
| WAL volume | page write buffers (generic computes a diff against a copy: about 2 pages copied per write) | logical records, smallest | generic + one logical record per row change when published |
| code | small | redo for every page operation: large | small + decode + record writer |
| silent data loss risk | **yes** (a publication is accepted and delivers no rows) | none | none: the AM refuses DML on a published table when the logical record cannot be written |

**Decision: hybrid.**

- Every page change is a generic WAL record (the existing store protocol P1-P6, so crash recovery needs nothing new).
- When `RelationBuildPublicationDesc` says the relation publishes an action and `XLogLogicalInfoActive()`, the DML also writes **one custom-rmgr
  record per change** (`RM_SIFT_TBL`, a *development* rmgr id until one is registered): `{op, relfilelocator, tuple image (heap tuple format,
  every TOAST value expanded), old key image for UPDATE/DELETE}`. `rm_redo` is a no-op (the page changes are in the generic records, so
  a standby needs the rmgr only to *skip* the record), `rm_decode` turns it into a reorder-buffer change, subtransaction and
  transaction ordering come from the record's xid. Old key image: the replica identity columns (`RelationGetIndexAttrBitmap`), all
  columns for `REPLICA IDENTITY FULL`; building it costs fetching the old row of a DELETE/UPDATE only when published.
- If the relation is published and the rmgr is not registered (`pg_sift` not preloaded) the DML raises `ERRCODE_FEATURE_NOT_SUPPORTED`:
  "sift_table tables can only be published when pg_sift is in shared_preload_libraries". The check is cached per relation (the
  publication descriptor is in the relcache and invalidated by publication DDL).
- `TRUNCATE` is decoded by the core (`XLOG_HEAP_TRUNCATE` is written by `ExecuteTruncateGuts` for any AM).
- Initial table synchronization (`COPY` of the table) uses the AM's scan; the subscriber applies with `table_tuple_insert/update/delete/lock`
  of its own `sift_table` or heap table.

**What works where.**

|  | status | notes |
|---|---|---|
| crash recovery | works | generic WAL replays pages; a half-written segment is an orphan swept by VACUUM, never referenced (P1, P3); the directory switch is atomic (P2). TAP test: `kill -9` during insert, promotion, vacuum, bulk load, then `sift.verify` on the table's BM25 index and a result comparison with a heap table. |
| physical standby (hot standby reads) | works | the same snapshot rules (`XidInMVCCSnapshot` with the standby's snapshot), `sift_store_check_page` turns a page recycled under a standby query into a serialization failure to retry (P5; `hot_standby_feedback` avoids it). No recovery conflicts are raised by generic WAL. |
| `pg_basebackup`, PITR, `pg_rewind` | works | block-level WAL, no extra requirement unless the logical record is on (then every node preloads). |
| `pg_upgrade` | works | files copied; BLCKSZ 8192; the format major/minor in the metapage is checked on open (a newer minor asks for `VACUUM FULL`). |
| `pg_dump` / restore | works | `COPY` of the live rows through the scan, `CREATE TABLE ... USING sift_table`, indexes and constraints recreated; the per-table options (section 8.1) are the rows of `sift.table_option_values`, which `pg_dump` writes with the database. `pg_dump --no-table-access-method` restores into heap. |
| logical replication, publisher | works with the hybrid record | requires preload; UPDATE/DELETE need a replica identity; partitioned tables and `publish_via_partition_root` as for heap. |
| logical replication, subscriber | works | the apply worker uses the AM callbacks. |
| `wal_level = minimal` | works | new relfilenodes skip WAL and are synced at commit by the core (`RelationNeedsWAL`). |
| UNLOGGED, TEMP | works | the init fork holds the empty layout; temp tables use local buffers. |
| logical decoding of the table by a *plugin* (test_decoding, wal2json) | works with the record, same preload requirement | a plugin sees ordinary changes. |

**Unsupported, with the error**: logical decoding of an unpreloaded published table (`0A000`, above); `BLCKSZ` other than 8192 (`ERRCODE_FEATURE_NOT_SUPPORTED` at table creation).

**Blocker check.** No decisive blocker for logical replication: it works through the hybrid record, at the stated cost
(preload everywhere the logical record is replayed, double WAL for published tables). The cheapest way out for a deployment that
cannot preload is to publish a *heap* table (or a view over it) kept in sync by `INSERT INTO ... SELECT` or a trigger, or to
replicate the heap staging table of an ingest pipeline; the AM refuses to publish silently-incomplete data.

---

## 6. Reading: scans, indexes, the engine, SiftAgg

- **Sequential/bitmap/TID/index scans** are the AM callbacks above. A scan owns a `SiftTblScan` (directory copy, write buffer page cursor, group reader, tombstone cache). The slot of a `sift_table` (`table/slot.c`, a virtual slot) holds a group row as a reference only: the group reader, its unit (a token that changes with every unit) and the row's position in the unit's vectors.
  - `slot_getsomeattrs` receives the number of leading columns the query reads; only those leaf columns are decoded, one vector per column and unit on the first row that needs it (`fill_attr`); `count(*)` decodes nothing. Decoded by-reference values belong to the slot (its memory context, freed when cleared).
  - A slot whose unit is no longer the reader's current one (another row was fetched, a cursor moved on) finds its row again by TID through the table's fetch scan. Rows of row-format segments are copied into the slot as heap tuples.
  - TID fetches (index scans, bitmap, UPDATE) keep the decoded units and TID lists of up to 256 superblocks (64 MB) in a backend-wide fetch scan (valid while the directory and the backend's xmin stay; pages unpinned after every fill; the oldest are dropped past 64 MB per table, `SIFT_TBL_FETCH_BYTES`: a miss re-reads the superblock's TIDs, about 600,000 instructions; with 32 slots and 8 MB the 40,000 updates of `tbl_update_keys` missed on most fetches of a table of 1,000,000 rows, 3,538 M instructions against 2,381 M with 256 slots and 64 MB). A fetch that finds a tombstone of the current transaction that deletes the row for the snapshot answers invisible without reading the row, and the first fetch after a begin does not sync the tombstones twice: the uniqueness check of a key updated 20 times in one transaction went from 441,000 to 137,000 instructions an UPDATE (heap 111,000; 600,000-row bench case `tbl_update_keys` 3,076 to 950 ms, heap 705 ms). A caller that needs a row's version, not its values (row locks, uniqueness checks, `index_delete_tuples`), asks without decoding (`sift_tbl_fetch_lazy`: the unit's live-row list answers). `xmin`, `xmax`, `cmin`, `cmax` are read by TID from the same fetch and the tombstones.
- **BM25 `sift` and btree on a `sift_table`**: `ambuild` runs `table_index_build_scan`; `aminsert` receives the synthetic TID from `tuple_insert`; scans return TIDs and the bitmap/index scan nodes call `table_index_fetch_tuple` / `scan_bitmap_next_*`. The sift custom scans and `count(*)` fast paths ask `sift_vis_*` (src/core/visibility.c), which reads the heap's visibility map and calls `heap_hot_search_buffer`; for a `sift_table` the AM provides `sift_tbl_filter_page` (visibility of the candidate offsets of one vblock) and `sift_tbl_nblocks` (the vblock count), and `sift_vis_begin` branches on the table AM.
- **SiftAgg over the table** (`src/table/src.c`, the table provider of the seam `src/agg/colsrc.h`, docs/COL-PLAN.md): the leaves are the table's columns in layout order. The groups are read by the engine, one `sift_col_open_table` source per column layout the segments were written for (leaves of an older layout are matched by attribute number; a column the segment lacks reads as the catalog default: null or the constant of `ADD COLUMN .. DEFAULT`), with the table's visibility as the engine's `SiftColVisibility` hook: `sb_mask` is all-ones for a superblock with no tombstone and no write buffer row, else zero, and `filter_page` is `sift_tbl_filter_page` (insert state, tombstones).
  - Write buffer rows are encoded as one in-memory batch per superblock (`sift_tbl_wbuf_batch`, the promotion's row conversion, fed with `sift_col_add_batches` when a morsel begins) and read like a stored unit; the sources' units are merged in ascending superblock. Aggregates therefore see exactly the rows a sequential scan sees (`tbl_agg` and TAP test 008 compare the two plans in every state: write buffer, promoted, tombstones, own and other transactions' changes, rolled back, added columns).
  - `numeric(p<=18, s>=0)` leaves are the engine's scaled integers (`SiftSrcLeaf.scale`, `sift_col_numeric_key`), `char(n)` is a dictionary leaf of the padded values; leaves stored as exact images (`SIFT_COLS_VAR`: other types, wider numerics) are marked lossy: no query uses them. A column group keeps an odd NaN float apart as an exception: a query that reads such a column of a unit holding one raises 0A000 with the hint to set `sift.enable_agg` off (as the index does). A string of any length and a float `-0` are stored exactly in their leaf.
- **Parallel scans** (the aggregate engine, the table's parallel sequential scan): every participant reads only the state it captured when it opened (directory, write buffer list, tombstone log, versions) and never asks the backend's table cache again while it scans, because an invalidation (a VACUUM sends one) replaces the cache entry; a table scan holds a private `SiftTblScanState` (tombstone log, the directory's write buffers, the versions of its unfrozen groups, the snapshot) and every page read of it (`sift_tbl_filter_page_vers`, `sift_tbl_load_vblock_in`, the group scan's `vis_*`) takes that state, not the `Relation`; the workers of an aggregate scan build theirs from the directory and write buffers the leader exports (`view_size`/`view_export` of the provider, as for a `sift_col` index); an index-fetch scan (the heap check of a BM25 or column index) copies the write buffers and versions of the cache entry at its first page and follows the table only when asked for a page past them. What an item is (a range of superblocks, a page of the leader's write buffers) is fixed by the leader and numbers the same rows for every participant. A participant that opens later may see a newer table (promoted buffers, a rewritten log); that is the same snapshot's answer as long as it is self-consistent. When the leader's description names storage the participant cannot rebuild from the table as it is (the write buffer pages of the leader's list, the tombstones that hide their rows), the participant reads that storage by the leader's directory: the sequential scan reads the log of the generation that directory names (`sift_tomb_sync_at`), whose blocks the snapshot keeps. Tests: `agg_par_table`, `tbl_par_scan`, `agg_par_directory` (isolation).

## 7. Maintenance

**Promotion** (`sift_tbl_promote`, `src/table/promote.c`): a write buffer is sealed, closed to appends (page `SEALED` flags under page locks, directory
kind `SEALED`; `create_wbuf` does it when the segment fills, VACUUM closes the active one once it has 8 pages), and becomes a column
segment when every row is *final* (`row_fate`): `xmin` committed and older than the horizon (kept, frozen), or flagged `DEAD` by VACUUM
(dropped); a row whose insert is aborted or not yet old enough keeps the segment as it is (retried by the next promotion). A row with a
tombstone is promoted as a normal row (its tombstone applies by TID wherever the row lives). The segment is written with the engine writer
(`sift_colw_*`, one unit per superblock of the vblock range, TIDs unchanged), and `sift_dir_update` swaps it for the write buffer entry; the write buffer
pages are released with a `safexid` (reusable once no snapshot can read them: the second VACUUM, or the next allocation, recycles them;
the new table of a rewrite is private, so its extents are reusable at once). The inline promotion takes the maintenance lock only if it is free.

**Bulk load into groups** (`sift.table_bulk_groups`, on by default since 3.3; `src/table/load/`). `COPY`, `CREATE TABLE AS` and `REFRESH MATERIALIZED VIEW` pass a bulk insert state. With the setting on, the rows of such a command skip the write buffer: each takes the next TID of a run of superblocks the command reserved (8 for each group the run holds, under the lock of the write buffers); the rows of a superblock become one unit of a column group, published in the directory when the run is full and at the end of the command (`finish_bulk_insert`; the transaction's pre-commit is the last chance).

- The group carries the writer's `xmin` and `cmin` in version vectors (frozen under `COPY ... FREEZE` and the refresh of a materialized view): it is the unfrozen group of the write path above, with no new format; a 3.0 library refuses the table as it refuses one with unfrozen groups (`scripts/old-lib-check.sh`).
- The rows wait in the backend (at most a write buffer's bytes per unit) until their unit is written, so nothing may read them mid-command. The table qualifies only if nothing does: no unique or exclusion index (its check reads the rows already inserted), no trigger of any kind (a foreign key is one), not published (its changes are logged row by row), and no default, generated column, `CHECK`, domain, index expression or predicate that calls a user-written function (a volatile one sees the rows its own command inserted before it, which are not on disk yet; the built-in ones cannot read the table). Any other table keeps the write buffers (`sift._test_tbl_bulk_refusal` of the testing build names why).
- The groups of a run are written by a parallel context when the run is 16 MB of rows or more: the backend and up to `max_parallel_maintenance_workers` workers (3 at most) take a group each, so a run holds a group for each of them (8 units each) while the groups fit `maintenance_work_mem` (a group is 8 write buffers of rows: 2 groups at the default of 64 MB, 3 from 96 MB); a run that ends the command is cut in groups of the same size, two units at least. The rows wait as heap-format records, and the transposition into the batch, the encoding of the units, the dictionaries, the pre-aggregates and zstd run in whoever takes the group; the backend keeps the parse, the form of the record and the directory. A transaction block, `COPY ... FREEZE` and the partitions of a partitioned table take the same path (a partition has a load of its own, so its rows wait in a buffer of its own: up to the groups of `maintenance_work_mem` for each partition being loaded); a worker that dies, is cancelled or times out ends the command with the error and leaves groups nobody published (swept as orphans); `tbl_load_workers` kills, cancels and times out the participants and checks the file and the standby. `max_parallel_maintenance_workers = 0` (or a temporary table, a command already in parallel mode, a COMMIT-time flush) writes the groups in the backend, as before, with the same TIDs and the segments of 8 units. 1,000,000 orders (9 columns): the backend's instructions 22.6 G to 17.8 G (19.3 G at the default `maintenance_work_mem`), the cluster's 22.6 G to 23.9 G (the record is formed and read once more), WAL unchanged.
- An index entry of the table (btree, BM25) is added as the row is and points at a TID that no group holds until its unit is written: a fetch of such a TID finds no row, as for any uncommitted row.
- A group is published like a promotion's: when the directory is full or past `sift.table_backpressure` the command waits for the maintenance lock and merges first, then settles as a closing writer does. A failed command or dead backend leaves groups of an aborted writer (dropped by VACUUM, as for promotions) and written units nobody published (swept as orphans).
- 200,000 rows of seven columns: the WAL and time are in the changelog.

*Rows of a running transaction* cannot be final. While the directory is more than half full (`nsegs > sift.table_max_segments / 2`) a sealed write buffer is promoted whatever its rows are: the final rows are frozen (xmin 0), the others keep their `xmin` and `cmin` in the group's **version vectors** of format 4. Only a write buffer with a row VACUUM flagged `DEAD` or a speculative insertion in progress waits (the error of section 2.1 is then the end of the line). Version note: 3.0.1 promoted only the write buffers that one command of one transaction wrote, as a tagged group, so a transaction was limited to about 500 MB by that error.

The promotion is part of `sift_tbl_settle`, which an insert runs when it needs a new write buffer and which takes the maintenance lock only if it is free; it also freezes the unfrozen groups whose live rows are committed and older than the horizon, and under pressure merges the smallest groups so the directory keeps shrinking while the load goes on. A 10M-row COPY in one transaction holds about 180 MB in the backend whatever its size, and a loop of 10M single-row INSERTs in one transaction ends with a handful of unfrozen groups.

*Version vectors* (`src/col/ver.[ch]`, `src/col/seg/seg.c`, `src/col/seg_write/seg_write.c`). A segment with rows that are not final is written as column format 4, with the directory entry flag `SIFT_SEG_VERS` (`reserved` = the smallest xid among them) and a section after the superblock directory of the DOCMAP section:

- a header (magic, version, the unit count, the smallest and largest xid);
- one record per unit (the range of its xids and the highest cmin, a flag for a unit that has frozen rows, the location of its vectors);
- the vectors: the xmin and the cmin of each row of the unit in row order, each as a constant (one transaction, one command) or as a first value, a smallest step and bit-packed residuals, so that a run of single-row inserts (the same xmin, a cmin that goes up by one) takes no bits per row.

A unit all of whose rows are frozen has no vectors; a segment that has none is written in the format `sift.col_format_minor` says (3, as 3.0 writes it, or 4). A group written by 3.0.1 under pressure (one writer: the directory entry's `reserved` = xid, the segment header's `sum_dl` = cid, format 3) is read as constant vectors. The reader asks for the summary of a unit before its vectors: a unit whose xids are all visible, or all invisible, to the snapshot needs no per-row test (`sift_tbl_ver_state`). The metapage minor goes to 3 when the first such group is published (and is never lowered): a 3.0.x library refuses the table, which it would read as final, until `VACUUM FULL` rewrites it (new files have minor 2).

*Layout of a group* (`SiftTblLayout`): one leaf per live column in column order (typed: integers and `numeric(p<=18, s>=0)` as INT with the
scale, `float4/8` as F4/F8, text kinds and `uuid` as STR (a `uuid` is a dictionary of 16-byte images: its top-N reads the codes of the stored dictionary; a segment written before that holds it as VAR, read through the datum, and a rewrite makes it a dictionary); any other type, including `jsonb`, arrays and `bytea`, as VAR holding the exact datum
image), then the leaves of the declared jsonb paths (`sift.table_set_paths`, section 11), plus one `exceptions` VAR leaf. A value a typed leaf cannot hold exactly (numeric NaN/Infinity, a float non-canonical NaN)
is stored as NULL with the overflow flag in its leaf and its image in `exceptions`, so every value round-trips
exactly. A segment carries its own schema; after `ADD/DROP COLUMN` it is read by attribute number (missing: the default), segments of
another layout are read side by side and are not merged with the current ones. At most 127 columns (`CREATE TABLE` fails otherwise).

**Merge** (`sift_tbl_merge`, VACUUM: until it finds nothing, a worker until its budget): the tiered policy of `sift_merge_pick` over the segments of the current layout (fan-in 8) plus "mostly dead": a segment more than `sift.table_dead_rewrite_pct` percent dead (default 30; 100 never) is rewritten alone. A `sift_col` index uses 10 because a unit with dead rows is scanned instead of answered from its pre-aggregates; a table keeps its segments until a third is dead, so its file stays near 1.4 times its live rows. Dead rows are dropped by the engine's `sift_col_merge_segments`, which sizes its output runs to its input so that free space left by earlier merges is reused instead of extending the file. A segment of an older layout is not rewritten.

Unfrozen groups merge with everything: the output keeps the version vectors of the rows it holds (`sift_cbatch_merge` carries them) and is frozen outright when every row it keeps is committed and older than the horizon (the freeze callback of `sift_col_merge_segments`); the rows of an aborted writer are dropped not by the merge but by the VACUUM that cleared their liveness. Under directory pressure `merge_smallest` merges the smallest immutable segments of the current layout, unfrozen ones included.

**VACUUM (`relation_vacuum`)**, one pass, restartable:

1. Classify tombstone entries against `OldestXmin`: aborted -> drop; committed and removable -> its TID is **dead**; others kept.
2. Dead TIDs of write buffer rows get the `DEAD` record flag; dead TIDs of group rows clear the engine's liveness bit
   (`sift_cseg_bulkdelete` with a "TID in dead set" callback, WAL logged). Aborted write buffer inserts and the rows of aborted bulk segments are dead too.
3. `index_bulk_delete` on every index with the dead set, then `index_vacuum_cleanup`. The dead set is held a batch at a time, as the dead TID list of a heap
   VACUUM: the TIDs (6 bytes), the HOT roots that go on (6) and the runs of aborted rows (16) of a batch fill half of `maintenance_work_mem` (`autovacuum_work_mem` in an autovacuum worker),
   the lists double as they grow; when they are full the batch clears the liveness bits of its group rows (steps 2), every index goes through its TIDs (one pass for
   each batch, the `INFO` of `VACUUM VERBOSE` says how many), and the next batch starts, in the order of the write buffers, the log and the aborted groups. TIDs are
   never reused, so an interrupted VACUUM cannot expose a recycled TID (the log keeps the dead set of the group rows until the rewrite of step 5, after the last batch); only index bloat stays until the next one.
   The rows of an aborted writer's group are dead like those of an aborted write buffer: VACUUM finds them from the version vectors (the rows whose xid aborted, as key runs of the dead set that `dead_has` searches), clears their liveness bits after the index cleanup, and the next merge drops them (`sift_tbl_aborted_runs`; `sift_tbl_drop_dead_groups` for a group with nothing live left).
4. Close the open write buffer (8 pages or more), promote, freeze the unfrozen groups whose live rows are committed and older than the horizon, merge (the mostly dead segments included).
5. Rewrite the tombstone segment without dropped entries and without LOCK entries of finished transactions; swap it in (skipped when
   no entry would go).
6. Space: `sift_recycle` clears the free-list records whose releasing transaction no snapshot can see any more, the orphan sweep releases what a crash left, then the reusable free run at the end of the file is cut (`sift_store_truncate`, `core/store.h` P7) as for a heap.
   - Before the cut, the segment whose blocks end last in the file (column segment, tombstones, or the closed and sealed write buffer) is rewritten into the reusable free runs below it while they hold it (`compact_tail`, at most 16 steps; only with the cut enabled; a column segment of an older layout stays). A load seals its column segment above the write buffers it replaces, so without this the file stays the size of the load (1M orders: 163 MB to 14 MB; the dead rows of a rewritten segment go with it).
   - The cut happens when it is 1000 blocks or a 16th of the file, `vacuum_truncate` is on (heap reloption, or `VACUUM (TRUNCATE)`), and `AccessExclusiveLock` is free right now (`ConditionalLockRelation`, released as soon as the file is cut). Under it no backend holds a scan, directory copy or target page; the store sends a relcache invalidation before the lock goes, marking every backend's `SiftTblRel` invalid (target page, write buffer page tables and fetch scan are rebuilt on next use), and a standby replays the lock first, cancelling queries that hold the table there.
   - A lazy VACUUM is in no snapshot or horizon, so its own releases carry the next xid (`xid_safexid`) and are reused once another transaction has ended; under the lock every record is reusable (`sift_store_horizon`), so the cut takes the free run at the end whatever released it. On an idle database a second VACUUM right after the first finds the same blocks not yet reusable (3.1 measurement, 300,000 narrow rows: 2,907 of 2,994 blocks free after the first VACUUM, 16 reusable, the same after the second); one transaction that takes an xid (`SELECT txid_current()`) and a VACUUM cut the file to 103 blocks (824 kB). `sift.table_space` reports the free blocks a size benchmark must add.
   - Live segments in the middle of the file stop the cut, as live rows do for a heap; later writes reuse their space.
7. `vac_update_relstats` (relpages, reltuples = live rows, `relfrozenxid`), `pgstat_report_vacuum` (live rows; dead rows = deleted by a
   committed transaction but not removable yet), counted as a heap does: an in-progress insertion is not live yet, an in-progress deletion
   still is; group rows from the liveness bits less the recently deleted ones. `VACUUM VERBOSE` reports the rows removed, the rows that
   remain and the dead rows not yet removable, and the truncation. The counters of insert/update/delete that the AM reports keep
   autovacuum's thresholds meaningful (`pgstat_count_heap_insert/update/delete`).

`pg_repack` cannot rewrite a `sift_table` (it copies the rows into a heap table and swaps the files, which would replace the column segments with heap pages): the event trigger `sift_table_repack_guard` refuses its `repack.repack_trigger` on one, `pg_repack --only-indexes` is fine, and a run without `-t` stops at the first `sift_table`.

`VACUUM FULL` and `CLUSTER` are `relation_copy_for_cluster`: the kept versions are written straight into frozen column segments of the new table with dense TIDs (`sift_tbl_bulk_*`: no row-format pages, no promotion).

- VACUUM FULL re-keys the rows of the old units as they decode (no tuple is formed) when every column segment has the current layout, then adds the write buffer rows; otherwise (after `ALTER TABLE`) it reads the scan's tuples.
- CLUSTER sorts the scan's tuples by a btree index whatever the planner's `use_sort` says (a TID fetch decodes the unit of its superblock, so an index scan in random order would decode a unit per row) and fetches by index scan only for an index that cannot order a sort.
- A version deleted or updated by the rewriting transaction itself is dropped (the rewrite commits or aborts with it).

`VACUUM (INDEX_CLEANUP off)` skips step 3 (indexes keep dangling entries). `sift.table_space(tbl)` shows the file, the high-water mark, the free list and the virtual blocks of TIDs left (`vblocks_left`).

**`sift.table_maintain(tbl)`** does steps 2, 4 and 6 and nothing that needs the indexes: it promotes the write buffers, clears the liveness bits of group rows whose deletion is committed and older than the horizon (`GetOldestNonRemovableTransactionId`, as the inline promotion uses), merges and rewrites the mostly dead segments, recycles, sweeps, relocates the tail segments and cuts the free end. Tombstone entries stay: they are the dead set the next VACUUM hands to the indexes (a cleared bit with its tombstone is the state of a VACUUM stopped before step 3), so the file ends near the VACUUM's size plus the tombstone log, which only that VACUUM shortens; the rows of an aborted writer are left to it too (only the live bits say which they are). It takes `ShareUpdateExclusiveLock` and the maintenance lock only if free (`busy` in the result otherwise) and the file's lock as VACUUM does (`ConditionalLockRelation`, given up at once), never waits for a reader or writer, refuses a read-only transaction, a standby and a role without `MAINTAIN`, and writes only what VACUUM writes (liveness pages, directory switches, free list, truncation: same crash points).

*Background pass* (`sift.table_auto_defrag`, off by default for 3.2, needs `sift.async_maintenance`). A backend asks for a worker (`sift_maint_request_worker` as for the indexes; at most one request per `sift.maintenance_naptime` of a table, backing off after failures; one worker per table) when:

1. the reads of the table since the last pass, each paying for the share of its superblocks that hold a removable tombstone (their units are read from their rows, not from their pre-aggregates), add up to 38 reads of a table dirty throughout: a pass costs about 1,500 instructions a row of the table (a VACUUM of 300,000 rows takes 394 to 488 M, a maintenance step counted at half), a read about 20 to 113 for a row of a dirty superblock; the rows cancel, and neither a percent of dead rows nor an option decides (1% of deleted rows already make every superblock dirty, and a table nobody reads is left to autovacuum, which the deletes feed). Only tombstones older than every snapshot count, so a long transaction holding the horizon makes the pass wait instead of reading the table to remove nothing; the reads are the scans the statistics count (the setting `sift.table_auto_defrag_dead_pct` of 3.3, which no code has read since 4.0, is removed in 4.2); or
2. free blocks are at least `sift.table_auto_defrag_free_pct` percent of the file, worth the exclusive lock by VACUUM's rule (1000 blocks or a 16th of the file), and grown by a 32nd of it since the last request.

The backends that look are those that close a write buffer (every `sift.table_write_buffer_limit` of writes; not inside a VACUUM) and, at most once per `sift.maintenance_naptime` each, those that delete, update, lock or read the table (scan, index fetch, SiftAgg): a table of small writes or only deletes closes no write buffer for a long time, and nobody sees a table nobody uses. The check reads no page of a table never written.

- For (1) the worker runs the whole table VACUUM (`sift.table_auto_defrag_indexes`, on: without the index cleanup the tombstone log, which every scan reads and which holds the file above the live rows, cannot shrink; with it off the pass is `sift.table_maintain` and the tombstones outlive it until a VACUUM); for (2) `sift.table_maintain`. Both run in transactions of their own with no snapshot, at most 32 MB copied under the table lock.
- A pass rewrites at most `sift.table_auto_defrag_budget` MB in its merge steps (mostly dead segments first, the most dead first: what a rewrite frees per byte copied is its dead share), and passes again while the budget stopped the last one. Small groups next to the one a rewrite picks join it (`sift.table_auto_defrag_coalesce`).
- Foreground impact: a cost delay as autovacuum (page accesses charged against `vacuum_cost_limit`; `sift.table_auto_defrag_delay` is the sleep, after each page of a write buffer VACUUM judges and after each merge step; the table VACUUM of autovacuum is charged the same way); a lock of its own (`SIFT_WORKER_LOCK_BLKNO`) that no delete or update takes; a wait for a quiet moment when `sift.table_auto_defrag_max_active` clients run a statement (also between merges); and `sift.table_auto_defrag_wal_rate` kB of WAL a second.
- What remains is the cost of the new segments: every backend that reads a segment inflates and indexes its string dictionaries once (`col/dictcache.c`, keyed by the generation, which a rewrite changes), 100 ms for a column of 200,000 distinct values, and a pass writing many segments sends that to every client at once; the WAL rate bounds it. Measured (200,000 rows, 4 clients for 40 s, 10% threshold): about 9% of the throughput (966 against 1,058 TPS, same p99) and, in about half of the runs, stalls of 100 to 130 ms of every client at once that no lock, cost delay, truncation or index cleanup explain and no setting removes (250 to 355 ms before the page lock was told apart from the worker's).
- The request may come while the writer still works (its rows are not final, the end-of-file lock is not free), so the worker stays and passes again every fourth of `sift.maintenance_naptime` while a pass finds something to do (two in a row that do not end it, 20 at most).

Autovacuum is the other trigger and needs none of this: its dead-row threshold is 20% of the rows and its naptime a minute; the worker answers in `sift.maintenance_naptime` and also covers free space, which autovacuum's counters do not see.

**ANALYZE** (section 4): statistics columns as PostgreSQL computes them from the sample; jsonb shredded paths are not statistics targets.

---

## 8. Compression, options, defaults

- Compression is on by default (col-compress: algorithm per leaf, `auto` picks per chunk from FOR/PACK/dictionary and zstd/lz4 when the
  library is present); SIMD kernels are chosen at runtime from `sift.simd` (`SET sift.simd = 'scalar'` for the specification).
- Settings (GUCs; a table access method cannot have reloptions): `sift.table_write_buffer_limit` (kB, 2048-8192, default 4096: size of a write
  segment, whole superblocks of 2 MB, so 2 MB is the effective minimum; a row has to fit one segment), `sift.table_inline_promote` (default on), `sift.table_bulk_groups` (default on, section 7),
  `sift.table_compression` (the `compression` grammar of `sift_col`: `auto`, `none`, `lz4[:N]`, `zstd[:N]`, encodings; `'auto; col=zstd:9'` per column;
  taken from the session that promotes, autovacuum uses the server setting), `sift.table_dead_rewrite_pct` (1-100, default 30: section 7,
  merge). Segments are self-describing, so changing a setting affects only
  the segments written afterwards. `sift_group_rows` (rows per unit: one superblock) is not provided; the jsonb paths of a column are declared with `sift.table_set_paths` (section 11).
- `ALTER TABLE ... SET ACCESS METHOD sift_table` rewrites the table through `relation_copy_for_cluster`-style bulk writes.
- Types: every type, exactly (section 7, layout of a group): typed leaves for what the engine stores natively, opaque `SIFT_COLS_VAR` leaves
  for the rest, an `exceptions` leaf for the values a typed leaf would change.

### 8.1 Options of one table

A table access method has no reloptions before PostgreSQL 20 (`CREATE TABLE .. USING sift_table WITH (x = 1)` and `ALTER TABLE .. SET (x = 1)` refuse a name the access method does not know), so the settings that act on one table can be set for it with functions:

| function | |
|---|---|
| `sift.table_set_option(tbl regclass, name text, value text)` | sets the option; the value is checked as `SET` checks the setting of the same name (type, range, unit, the check of `compression` and `backpressure`), and the canonical text is stored |
| `sift.table_get_option(tbl, name)` | the value in force: the table's own, else the setting |
| `sift.table_reset_option(tbl, name DEFAULT NULL)` | takes one option (all when NULL) off the table |
| `sift.table_options(tbl DEFAULT NULL)` | one row for each option of each `sift_table` the caller may see: `name`, `value`, `source` (`table`, `setting` when the `sift.table_*` setting is not at its built-in default, `default`), the built-in `default_value` |

The name of an option is the name of its setting without `sift.table_`: `write_buffer_limit, wide_rows, inline_promote, async_promote, backpressure, hot_update, bulk_groups, dict_lookup, key_table, compression, dead_rewrite_pct, auto_defrag, auto_defrag_free_pct, auto_defrag_budget, auto_defrag_coalesce, auto_defrag_indexes`. The policies that bound the I/O and the load of the whole server (`auto_defrag_delay`, `auto_defrag_wal_rate`, `auto_defrag_max_active`) are not options of a table.

- **Precedence.** An option of the table beats the setting, whatever the setting's source (server, database, role, session `SET`), as a reloption beats the server default in PostgreSQL (`fillfactor`, `autovacuum_*`). To test a setting on a table that has the option, `sift.table_reset_option` the option first; `table_options.source` says which one is in force.
- **Who.** The owner of the table (or a member of the owning role, or a superuser) sets and resets, as for `ALTER TABLE`; no superuser is needed. A role with any privilege on the table reads. Nobody needs a privilege on `sift.table_option_values`.
- **When.** A statement running keeps the values it started with; the next statement of every session, and of a standby after it replays the change, uses the new ones. Segments are self-describing, so a change affects only the segments written afterwards. The setter takes a `SHARE UPDATE EXCLUSIVE` lock on the table (reads and writes go on; a VACUUM of the table waits for it).
- **Where they are kept.** In `sift.table_option_values (rel sift.tblref, name, value)`, an ordinary table of the extension that `pg_dump` writes with the database. `sift.tblref` is the OID of the table, written as its qualified name, so a restore, a `pg_upgrade` and a standby find the rows again; a name that is not in the database reads as no table and its row is skipped, so a restore that leaves a table out (`pg_dump -T`, an excluded schema) does not fail on its options. `DROP TABLE`, `DROP SCHEMA` and `DROP OWNED` delete the rows (an event trigger; with `event_triggers = off` a row can remain, unreadable, and the dump never writes it). `RENAME`, `SET SCHEMA`, `TRUNCATE`, `VACUUM FULL`, `CLUSTER`, `REINDEX` and a crash keep them (the OID does not change); `SET ACCESS METHOD` away from `sift_table` hides them and back restores them.
- **What does not carry them.** A dump of one table (`pg_dump -t`) does not write the table of options (it is data of the extension, as are the settings of an index in `WITH`); logical replication does not replicate it (neither does `ALTER TABLE .. SET`).
- **Cost.** The options are read once when a backend first builds the entry of the table in its cache (one probe of the primary key; 328,000 instructions the first time a backend opens the table of options, 4,000 to 8,000 for each table after that and each time the table's entry is rebuilt), and are a bit test and an array read after. A statement that has the entry costs the same as before (insert of one row 64,300 instructions, a grouped read of 20,000 rows 299,000, with and without options, with 1 and with 1,000 tables with options).
- **PostgreSQL 20.** When an access method can have reloptions, the names become `ALTER TABLE t SET (write_buffer_limit = '8MB')` and these functions stay as wrappers over that statement (same names, signatures and results); the update script of that release copies the rows into reloptions.

---

## 9. Limits

BLCKSZ 8192; PostgreSQL 17, 18 and 19; rows up to 1 GB (varlena limit).

- At most 127 columns: `CREATE TABLE` fails past it (error code `54000`), and so does an `ALTER TABLE` that adds a column or brings declared paths back past it, by the event trigger `sift_table_ddl_check`. `DROP COLUMN` is not checked, so that a table made too wide by 3.2, whose writes fail with the same error, can be narrowed.
- 128 directory entries: under directory pressure every write buffer becomes an unfrozen group whatever wrote it, and the groups merge, so a load of any size by any mix of commands, writers and savepoints is not bounded by them. What still waits is a write buffer that holds a deleted row VACUUM has not seen or a speculative insertion in progress; the insert that cannot open a write buffer then fails with the error of section 2.1.
- vblocks 2^32 (about 1.2 x 10^12 rows ever written, counting dead ones: a table that exhausts them is rewritten by `VACUUM FULL`).
- Unsupported: multixact-based shared row locks (they degrade to exclusive), speculative insertion through foreign tables (n/a), rewrite of a table with RLS-bypassing rewrites (n/a).

## 10. Tests

Run by `scripts/ci.sh` on PostgreSQL 17, 18 and 19; `SANITIZE=1` (ASan + UBSan) runs the same jobs.

- Regression: `test/sql/tbl_basic` (DDL, DML, rollback, savepoints, locks, unique and `ON CONFLICT`, VACUUM/ANALYZE, wide rows, TRUNCATE, CTAS, partitions, temp tables, `ALTER ... SET ACCESS METHOD`, `ADD/DROP COLUMN`), `tbl_bm25` (BM25 index agrees with the sequential scan through DML, VACUUM, REINDEX [CONCURRENTLY], CIC), `tbl_promote`, `tbl_space` (released blocks reused; the free end cut one VACUUM after it is freed, not with `vacuum_truncate` off), `tbl_dead` (live/dead counts; rewrite under `sift.table_dead_rewrite_pct`), `tbl_rewrite` (CLUSTER by btree sort and GiST scan, VACUUM FULL over two layouts), `tbl_sample`, `tbl_cap` (the limit lowered by `sift.table_max_segments`), `tbl_bigtx` (one transaction with more write buffers than directory entries, at a limit of 16; a heap twin must agree).
- **Differential suite** `tbl_diff`: the same statements on a heap table and a sift_table, EXCEPT ALL both ways plus index scans, across DML, rollbacks, MERGE, CTEs, aggregates, windows, locking, VACUUM [FULL], REINDEX, CLUSTER, rewrite to heap and back, TRUNCATE.
- Isolation: `tbl_bigtx` (rows invisible to every scan kind at READ COMMITTED, REPEATABLE READ and SERIALIZABLE until commit, gone after rollback, with VACUUM running) and `test/isolation/specs/tbl_concurrency.spec` (update/delete races at READ COMMITTED, REPEATABLE READ first-updater-wins, SERIALIZABLE write skew, FOR UPDATE / SHARE / NOWAIT / SKIP LOCKED, unique wait, `ON CONFLICT`).
- TAP: `test/t/006_tbl.pl` (crash recovery, hot standby, `wal_level = minimal` with both `wal_skip_threshold` settings, `pg_dump`/`pg_restore` plain and custom), `test/t/007_tbl_logical.pl` (publication with and without preload, 0A000 with the hint, replication to heap and sift_table subscribers, physical standby of the publisher), `test/t/008_tbl_promote.pl` (autovacuum promotion and reclaim, parallel workers, a reader across a promotion), `test/t/013_tbl_bigtx.pl` (standby replay with WAL consistency checking, crash mid-transaction, immediate stop, promotion, restored `pg_dump`).
- Benchmarks: `bench/tbl-bigcopy.sh` (COPY of 5M rows or more in one transaction with a primary key: time, peak backend memory, aggregate scan before and after VACUUM); `bench/ab-query.sh` (two builds on a heap table; the BM25 paths differ only by `sift_vis_nblocks` and the non-heap branch of the page filter).

## 11. Open decisions and risks

- Tombstone cache memory is O(live tombstones) per backend; the upgrade path is a per-SB tombstone index (pointer pages) so only the SBs a
  query touches are loaded. Mass deletes should be VACUUMed (autovacuum does) or avoided (TRUNCATE, partition drop).
- Shared row locks degrade to exclusive; implementing MultiXact would need a lock-owner set per row.
- Relation-level SSI locks; a per-SB granularity is possible once the engine reports the SBs a scan touched.
- Typed jsonb paths for a `sift_table`: `SELECT sift.table_set_paths('t', 'doc', 'doc->ch text, doc->w int4, doc->items[]{k int4, p float8}')` (the
  `paths` grammar of `sift_col`, the column named in each path; `NULL` or an empty spec drops the declaration). Without it a jsonb column is one opaque
  leaf of exact bytes and `SiftAgg` runs PostgreSQL's `jsonb_to_recordset` on every row. The declaration is a row of `sift.table_paths(relid, attname,
  spec)` (an extension configuration table, so `pg_dump` carries it; keyed by column name, so a renamed or dropped column has no paths); an event trigger
  clears it with the table. Promotion, merge and `VACUUM FULL` shred the declared column into leaves next to the exact bytes (no format change: a segment
  carries its schema), and the write buffer is shredded as rows are read. The planner uses the paths only when every segment it reads has them, so the
  plan stays the normal one until `VACUUM FULL` has rewritten the older segments (`sift.table_set_paths` says so, in a NOTICE, when the table has column groups), and a rewrite of the table that is not `VACUUM FULL` (`ALTER TABLE ...
  TYPE`) drops the leaves until the next one. A value of another JSON type than its path declares is kept exactly (overflow) and a query that reads
  that path keeps the normal plan while such a row exists; the `jsonb_typeof` forms still run (`filter_types`). Not done: ANALYZE gives the paths no statistics. The units keep the rollups of the elements of a list (docs/COL-PREAGG.md section 2) and a GROUP BY of its element leaves adds them up.
- The logical record doubles WAL for published tables; a custom rmgr for all page changes would remove the double write but demands
  preload everywhere.

## 12. Interfaces to the engine

The table AM is written against `src/col/col.h`, `col/enc.h`, `col/seg.h` of the column track and has no second implementation of
anything the engine does: group segments are written by `sift_colw_*` (and `sift_col_merge_segments`), read by `sift_col_open_table` /
`sift_col_begin/next/read`, cleared by `sift_cseg_bulkdelete`, with the table's visibility as the engine's `SiftColVisibility` hook.

| piece | status |
|---|---|
| segment writer `sift_colw_*`, `sift_col_merge_segments` | used by promotion and merge |
| `sift_col_open_table(rel, schema, dir, vis, heap_sbs, mcxt)`: a source over the groups of a table | used by scans, fetches, ANALYZE |
| `SIFT_COLS_VAR` (exact datum images, engine), `exceptions` leaf (table layout) | used |
| liveness `sift_cseg_bulkdelete` with a TID callback | VACUUM step 2 |
| `sift_colw_set_tag` (segment xmin of a bulk write) | removed with the write path of 3.1: the engine writer takes the `xmin`/`cmin` of every row from the batch (`sift_cbatch_add_ver`), and the direct column writes of a rewrite are frozen (no versions) |
| `sift_col_add_batches` (write buffer rows as in-memory units), the `SiftSrcProvider` seam of `agg/colsrc.h` | used by the table provider (`table/src.c`) |

---

## 13. Feature parity with `sift_col`

Both engines share the column format, the writer and the aggregate scan; what differs is which features the table's provider (`table/src.c`) offers the scan through the `SiftSrcProvider` seam (`agg/colsrc.h`) and which of the index's options have a counterpart. The table is compared with the callbacks each provider sets, so a feature added to one provider changes the table. `missing` means the plan or the scan degrades to the normal path (the note says how); `n/a` means the table has no such thing.

<!-- gen:parity -->

| Feature | `sift_col` | `sift_table` | Note |
|---|---|---|---|
| Aggregate pushdown (SiftAgg): filters, grouping, aggregates, top-N | works | works | the same executor over either source |
| Per-unit sums, counts, minimum and maximum | works | works | exact while the unit has no dead row and its pages are visible (a table: no write buffer row or tombstone in the superblock) |
| Group histograms (the cube) and calendar cells | works | works | a table writes the default cube and the cells of every date and timestamp column; an index takes `preagg` and `calendar_columns` |
| Minimum and maximum per group of the cube (`cube_minmax`) | works | works | a table writes them for the integer measures of its dims (not for calendar cells), as an index does by default; the setting `sift.col_format_minor = 6` writes none |
| Superunit summaries (`superunit`) | works | partial | whole-table aggregates read one summary for 16 units on an index; a table has no summaries (it takes the split and span of the key table, not `super`) |
| Key table of a segment (`key_table`) | works | works | a segment answers `WHERE key = x` from one record per key; a table writes it for its foreign key and `*_id` columns, within 3% of a segment, under `sift.table_key_table` (on); only a segment whose superblocks are all plain (no tombstone, write buffer row or uncommitted row) is answered whole |
| Row counts of a text dictionary (`count_columns`) | works | missing | `count(*)` of a prefix, a range or an equality of a text column of an index is read from two places of each segment's dictionary; a table writes no counts |
| Key lists and bitmaps of a leaf (`key_columns`) | works | works | a table writes them for its int2, int4 and date foreign key and `*_id` columns that are not unique (the default of an index), within 3% of a unit; `count(DISTINCT)` of a unit that has one reads no row |
| HyperLogLog sketch (`hll_columns`, `approx_count_distinct`) | works | missing | `sift.approx_count_distinct` is pushed down and reads the rows; no sketch of the units |
| Exceptions of a leaf that is NULL in nearly every row (soft-delete stamp) | works | missing | `IS NULL` / `IS NOT NULL` read the unit's NULL count either way |
| Rollups of the elements of a list (`rollup_leaves`, paths) | works | works | a table keeps the rollups of the lists of its declared paths (`sift.table_set_paths`), written with the units and read by the source of the index; rollups of the rows (`rollup_columns`) are an option of the index |
| Zone pruning of units by the filters of the statement | works | works | range, equality, `IN`, `IS NULL`, `OR` of those |
| Skip map for parallel morsels | works | missing | a worker takes ranges of superblocks and begins each, instead of skipping the pruned ones from one map |
| Parallel scan workers | works | works | partitions of a partitioned table too |
| Joins to the parent of a connected index answered from its found flag | works | n/a | a sift_col index keeps the flag that the parent row was found with each segment's stamp (docs/COL-CONNECT.md); a table has no connections |
| One directory for all the participants of a parallel scan | works | works | the workers open their sources on the directory and the end of the heap the leader read: the units of a key-cut segment are numbered by key range and the batches of the write buffer by superblock, so a promotion between two opens moves rows between items; a table numbers every unit by its superblock, and its workers also read the write buffers and the tombstone log of the leader's directory |
| Partitioned table pushdown | works | works | the partitions of one kind (all `sift_table`, or all with a `sift_col` index) are one source; partitions with their columns in another order keep the normal plan |
| Partition facts kept across statements, pages released between partitions | works | missing | the facts of a `sift_table` partition are read at each plan, and its source has no release between partitions |
| Planning without counting the sections of the units | works | n/a | the plan of a statement that prices no pre-aggregate answer does not read the cubes of the segments: an index counts which units hold a histogram, min and max or rollup when an aggregate is priced, a table keeps no such counts |
| Text tests on the encoded form (prefix zones, dictionary codes) | works | works | prefix zones skip groups, dictionary codes and encoded strings answer `=`, `<>`, ranges, `IN`, `NOT IN`, `LIKE 'p%'` (a column a group lacks stays with the executor) |
| Integer comparisons on encoded chunks | works | works | the table takes a filter when every segment layout holds the column as an integer leaf (agg/chunkfilter.c) |
| Decoding only the rows a filter keeps | works | works | a unit with few kept rows decodes them, and one the integer filters left few rows is narrowed to them |
| Decoding straight into the executor's vector | works | partial | a leaf that is not wide decodes into the executor's buffer in its own type (the read shared with `sift_col`); wide leaves (numeric over 18 digits, interval, enum) decode, then copy |
| Point directory and heap addresses of a key (`point_columns`, `point_tid_columns`) | works | n/a | a table has no index scan of its own: a btree serves point lookups |
| Rows of the values of a list (`paths` of a list of strings or numbers) | works | missing | the point directory of a list's values lists the rows that own each value, exactly; a table has no directory of the elements of a list |
| Units cut by a key (`key_cut`) | works | n/a | a table has no option surface; a lookup of a key reads the units of its superblocks |
| Sorted runs and run heads (`order_columns`) | works | works | a table writes the run of its timestamp columns where the run takes at most 3% of a unit (a column loaded in order: its rows are the run, and a unit keeps the first and last keys); `ORDER BY .. LIMIT 32` of such a column reads no unit |
| Run heads of a superblock, read without opening its unit | works | missing | a superblock of a table may hold write buffer rows and tombstones that its page masks do not show, so the unit is opened |
| Global order of a leaf (`order_array_columns`) | works | missing | the sorted `(key, heap address)` entries of every row of a segment; the top-N walks the zones of the units instead |
| Units read from their end (a key that follows the row order) | works | missing | ORDER BY key [DESC] LIMIT n over a delta key chunk reads 1,024 rows at a time from the end the sort wants; a sift_table has no chunk that says the order of its keys |
| Aggregates of a unit net of its dead rows | works | works | a VACUUM writes the sidecar of a unit that kept a dead row (a table: `sift_tbl_net_refresh`), valid while the unit has the same dead rows; the rows deleted since are read from the unit |
| Top-N from the zones of the units | works | works |  |
| Cache of the top-N walk | works | missing | the walk is made again by every statement |
| Top-N on raw uuid and bytea keys | works | works | a uuid column is a dictionary of its values; the top-N of uuid and bytea reads the stored codes |
| Sum and average of a numeric image read from the lanes of each row | works | missing | the sum adds an integer and a scale made from the lanes of a row, with no dictionary of numerics; a table reads the dictionary |
| Stored dictionary order (`dict_lookup`) | works | works | written for a dictionary of 1,024 strings or more under `sift.table_dict_lookup` (off by default); the top-N of the table reads its byte order |
| jsonb paths as leaves | works | works | `paths` on an index, `sift.table_set_paths` on a table |
| Shapes that read the heap row again (`set_recheck`) | works | missing | a table keeps the normal plan for them |
| Wide types (numeric over 18 digits, interval, enum) | works | works |  |
| LATERAL, EXISTS and shared CTE rewrites | works | works | the planner rewrites apply to a statement over a table |
| Statistics for the planner (zones, sizes, point and set cover) | works | partial | a table reports its rows, units and distinct counts, not the zones of its units |
| Automatic merge and defragmentation | works | works | `sift.col_auto_merge`, `sift.table_auto_defrag` |
| VACUUM, crash recovery, physical replication, upgrade | works | works | test/t col_* and tbl_* scripts |
| Differential tests against the normal plan | works | works | coldiff (`--table`), diff_fuzz |
| GPU kernels (`sift.gpu`) | works | missing | build option, off by default |
| Row counts of a range of an integer leaf (`order_array_columns`) | works | missing | `count(*)` of comparisons of one integer, date or timestamp column of an index is read from the entries between the bounds in the global order of each segment whose units are all exact; a table writes no global order |
| Row counts between two bounds of a text leaf (`order_array_columns`) | works | missing | `count(*)` of comparisons of one text column between two bounds is read from the ranks of the strings in the global order of each segment whose units are all exact; a table writes no global order |
| OFFSET and page filters in the global order (`order_array_columns`) | works | missing | an `OFFSET` of thousands is cut off the front of the order by counting the entries before a value, a segment that holds only the ends of its order says where they stop, and a condition on another column of a page is tested on the values of the units for the entries known to be exact; a table writes no global order |
| ORDER BY group, key from the second order (`order_array_group`) | works | missing | the top-N walks the table of groups of a second order group after group when the group column has no order of its own, and an `OFFSET` is cut by whole groups |
| A second scan of the same statement | works | missing | a nested loop that probes the aggregate scan again makes the source ready without opening it again; a table opens it again for each probe |
| Row counts of one element of a list (point directory of a segment) | works | missing | `count(*)` of an equality on an element of a text or integer array of an index is the length of the list of the value in the point directory of each segment whose units are all clean and all-visible; a table writes no point directory of elements |

<!-- /gen:parity -->