# Trigpoint — operator notes (extension 0.5)

A PostgreSQL 16, 17 or 18 index access method for pgvector's `vector` type: a per-point int8 PCA sketch with an
HNSW router over the sketches, a calibrated or exact lower bound that prunes before heap fetches, one
shared in-memory image per index, and a log of inserts and tombstones compacted by VACUUM. L2 (`<->`)
and cosine (`<=>`) operator classes. Version 0.5; `CHANGELOG.md` lists the releases.

## Install

From the release image or the .deb (`docker/Dockerfile.release`; `sh scripts/ci_release.sh` builds and tests both):

```
docker build -f docker/Dockerfile.release --build-arg PG_MAJOR=17 -t trigpoint/postgres:0.5-pg17 .   # pgvector's image + the extension
docker build -f docker/Dockerfile.release --build-arg PG_MAJOR=17 --target deb -o dist .            # dist/postgresql-17-trigpoint_0.5.0_<arch>.deb
dpkg -i dist/postgresql-17-trigpoint_0.5.0_*.deb          # on a host with postgresql-17 and postgresql-17-pgvector
```

From source: `make -C pgext && make -C pgext install` against `pg_config` of the target major (a C++17 compiler for the
core; `csrc/hnswlib` is cloned by the Dockerfile, or `git clone https://github.com/nmslib/hnswlib csrc/hnswlib`).

```
CREATE EXTENSION vector;
CREATE EXTENSION trigpoint;          -- or ALTER EXTENSION trigpoint UPDATE from 0.2 / 0.3 / 0.4
CREATE INDEX t_v_l2 ON t USING trigpoint (v vector_l2_ops) WITH (cells = 0);      -- L2, <->; cells = 0: automatic
CREATE INDEX t_v_cos ON t USING trigpoint (v vector_cosine_ops);                    -- or cosine, <=>
```

The build runs on maintenance workers (`max_parallel_maintenance_workers`) and needs `maintenance_work_mem`
large enough for the vectors in shared memory: about 6 bytes x rows x dimensions (30.1 GB at 5M x 1024), or the
build refuses. With `dynamic_shared_memory_type = posix` in a container, `/dev/shm` must hold that area during the
build and the image afterwards (compose: `shm_size`). An index over an empty table works (a trivial model)
and refits itself at the first REINDEX; TRUNCATE and create-then-load are fine.

PostgreSQL versions: 17 and 18 need nothing else. On 16 the cluster-wide image registry (the shared images of
`trigpoint.shared_image`) exists only with `shared_preload_libraries = 'trigpoint'`; without the preload the extension
works unchanged but every backend keeps a private copy of each image it opens (one image's worth of memory per
backend, slower first queries), `trigpoint_shared()` is empty and `trigpoint_shared_available()` returns false.
Build the image for a major with `PG_MAJOR=16|17|18` (`docker/Dockerfile.pg`); `sh scripts/ci_matrix.sh` runs the quick
suites on all three (16 both ways).

Reloptions: `cells` (0 = automatic), `levels` (e.g. `'32,96,256'`), `m` and `ef_construction` (router graph),
`sample`, `kmeans_iters`, `calibration_queries`, `seed`.

## Query-time GUCs

| GUC | default | meaning |
|---|---|---|
| `trigpoint.k` | 10 | neighbours computed by the first batch of a scan; a larger LIMIT, or a `WHERE` above the index, goes on in batches |
| `trigpoint.iterative_scan` | relaxed_order | how a scan goes on when the executor asks for more rows than the first batch: `off` repeats the search with k doubled over the same cells (0.4's behaviour); `relaxed_order` doubles k and the cells opened per batch, keeps the routed cell order, cascades the opened cells again under the larger tau and never fetches a heap row twice, so a later batch may return a row closer than one already returned; `strict_order` drops such rows, so the stream stays in distance order (with `trigpoint.exact = on` every prefix is the exact top-N) |
| `trigpoint.max_scan_tuples` / `trigpoint.max_probes` | 20000 / 0 | caps on an iterative scan: heap rows fetched in all, cells opened in all (0 = every cell); at a cap the scan stops and the query returns fewer rows than asked, with no error |
| `trigpoint.hits` / `trigpoint.probes` / `trigpoint.ef` | 200 / 32 / hits | router hits, cells opened, beam width |
| `trigpoint.delta` | 0.01 | miss probability of the calibrated bound |
| `trigpoint.exact` | off | certificate mode: exact bound over every cell (100 % recall, slower) |
| `trigpoint.shared_image` | on | share one image per index in dynamic shared memory; off = a private copy per backend |
| `trigpoint.on_shared_cap` | error | at `trigpoint.max_shared_mb`: `error` fails the query (53400), `private` reads a private copy in this backend instead (one image's memory per backend; a LOG line each time) |

## Maintenance GUCs

| GUC | default | meaning |
|---|---|---|
| `trigpoint.compact_frac` | 0.2 | VACUUM rewrites the image when log inserts + tombstones exceed this share of the live rows |
| `trigpoint.compact_reclaim` | on | after a compaction, move the new image into the pages the retired generation freed and truncate the relation after its last live page (off: the file never shrinks) |
| `trigpoint.compact_records` | 20000 | … or when the log holds more records than this (bounds a backend's replay at its first scan) |
| `trigpoint.delta_flat_max` | 50000 | appended positions a backend's router scores flat before it links them into a delta graph (0: link each one as it arrives) |
| `trigpoint.recalibrate_probes` | 64 | cells each calibration query of a compaction opens, in the router's order (0: every cell) |
| `trigpoint.recalibrate_queries` | 500 | calibration queries a compaction runs over the live rows (0 = keep the build's tables) |
| `trigpoint.max_shared_mb` | 0 | superuser; cap on the pinned images of the cluster, 0 = none; a load beyond it errors |
| `trigpoint.debug` | off | per-query statistics as NOTICEs; also enables the test-only page hooks |

## Monitoring and checking

- `trigpoint_info(regclass)`: the meta page (rows, metric, cells, levels, generation, log counters, build timings).
- `trigpoint_shared()`: one row per image in shared memory; `trigpoint_shared_total()`: images, loading, bytes, cap.
- `trigpoint_check(regclass, deep boolean)`: walks every page and the image; the last row is `summary: ok|CORRUPT, N problems`.
  A reader refuses anything the shallow check would flag, and a scan refuses a value a page corruption put out of range
  (a cell, a position, a graph link) with sqlstate XX002 instead of using it; the deep check also validates the arrays'
  contents and the model's floats (PCA rows, centroids, norms), and a compaction runs it before it reads the image.
  A corrupted model float the deep check does not catch gives a wrong answer without an error: PostgreSQL's data
  checksums (`initdb --data-checksums`, the default from 18) are the protection against bit rot in pages.
  `tests/test_pg_fuzz.py` mutates random bytes (1,000 per run) and asserts XX002 or an answer, never a crash. `REINDEX` rebuilds.
- `trigpoint_backend_memory()`: this backend's resident memory.

## Operations

- VACUUM tombstones deleted rows and, above the thresholds, rewrites the image over the live rows only
  (renumbered, router graph repaired, bound re-calibrated); the relation holds at most two generations plus the log.
- Crash recovery, UNLOGGED tables and hot standbys are tested (`tests/pg_crash.py`, `tests/pg_standby.py`); `statement_timeout`
  and `pg_cancel_backend` stop a build, an exact scan, a compaction VACUUM and a deep check within the timeout plus the
  current cell or page (`tests/test_pg_cancel.py`); a backend terminated while it loads a shared image publishes its
  whole entry or leaves none (`tests/test_pg_shared_cap.py`).
- Dumps: `pg_dump` stores the CREATE INDEX statement; the image is rebuilt on restore (`scripts/pg_upgrade_test.sh dump`
  checks the round trip: counts, validity, the checker, identical exact answers).
- On-disk format: page format 3 is read by every 0.x and 1.x release and is the same on every PostgreSQL major (fixed-width
  page structs, no PostgreSQL-version-dependent layout), so `pg_upgrade` carries a trigpoint index across majors without a
  REINDEX (`scripts/pg_upgrade_test.sh upgrade` runs 17 -> 18 in `docker/Dockerfile.upgrade`: same pages, checker clean,
  same answers). A reader refuses any other format with a REINDEX hint; a future bump ships an in-place migration or a
  documented REINDEX. `ALTER EXTENSION trigpoint UPDATE` changes the catalog only, one step at a time from 0.2
  (`tests/test_pg_upgrade.py`).
- `CREATE INDEX CONCURRENTLY` and `REINDEX INDEX CONCURRENTLY` work under concurrent inserts and deletes (the validate
  phase hands the rows inserted meanwhile to the log; `tests/test_pg_upgrade.py`).
- Filtered queries (`WHERE` on other columns with `ORDER BY v <-> q LIMIT k`): the filter is applied above the index,
  and the scan goes on in batches until the executor is satisfied (`trigpoint.iterative_scan`). On the local loop's
  100k-row corpus, at 10 %, 1 % and 0.1 % selectivity, LIMIT 10 comes back full with recall@10 1.00 / 1.00 / 0.97
  against the filtered exact answer, at 4x / 18x / 60x the unfiltered scan's 0.4 ms (the executor has to look at
  about 10 / s rows); a few 0.1 % queries hit the 20,000-row cap and come back short, `trigpoint.max_scan_tuples` raises
  it. A `WHERE` on the indexed column itself is refused (no index quals).
- Not supported: inner product (`<#>`), backward scans, `INCLUDE` columns, planner-parallel scans; one indexed column
  per index.

## Tests

`sh scripts/ci.sh` builds the local Docker image and runs every suite (`tests/test_pg_*.py`, the filtered-scan suite
among them) plus the short soak, crash and standby
scripts; `--quick` runs the pytest suites only; `SANITIZE=1` builds the ASan/UBSan image (PostgreSQL itself compiled from
source with the sanitizers and assertions, pgvector and the extension against it; several times slower).