pg_sift 3.3.0: everything since 3.2: the aggregate scan takes many more query shapes (top-N with any sort key, joins to small tables, semi and anti joins, two-level arms), `sift_col` indexes keep their pre-aggregates and answer from them after churn without a setting, point lookups by value, PostgreSQL 19 as a supported major, and the first release that tells you after the update what is left to do. Update the files, restart, then `ALTER EXTENSION pg_sift UPDATE` and run the commands it prints (a `REINDEX` of every `sift` text index is required).

## Highlights

- **Maintenance on by default.** `sift.col_auto_merge` (3.2: off) is on: a background worker vacuums the table when a thousandth of its rows changed and rewrites a `sift_col` index after the `VACUUM`, within `sift.col_auto_merge_wal_pct` (5) of the WAL the server writes, so the units stay answerable from their pre-aggregates after churn (+0.9% to +1.9% WAL with full-page images in the churn tests; the CHANGELOG.md line has the units answerable). `sift.table_auto_defrag` (3.2: off) cleans a `sift_table` in the background past 5% dead rows (`sift.table_auto_defrag_dead_pct`, was 20), with `sift.table_auto_defrag_wal_rate` and `sift.table_auto_defrag_max_active` to bound it. Both need `sift.async_maintenance` (on). `sift.col_maintenance_status(index)` and `sift.col_index_status` say what the worker waits for.
- **Pre-aggregates by default.** `cube_minmax`, `superunit = 16` and `calendar_columns` (every date and timestamp column) are on for a new `sift_col` index; `key_columns = auto` keeps the keys of foreign key and `*_id` columns in each unit. A whole-table `count`/`sum` goes from 0.26-0.37 ms to 0.05-0.08 ms and a `GROUP BY` with `min`/`max` of an integer from 5.5 to 0.34 ms (200,000 rows), for an index 1.5% larger.
- **Point lookups.** `point_columns` (a directory of the rows of a unit by value) answers an equality or a short `IN` list from the units that hold the value: 0.015 to 0.022 ms at 1,000,000 rows. `point_tid_columns` (column format minor 16, opt-in) lists heap addresses instead: 0.011 to 0.013 ms.
- **Top-N and ordering.** `ORDER BY .. LIMIT n` is a scan of the column index for text, uuid, bytea, inet, bool, time and money keys, for expressions (`coalesce`, `lower`, `upper`, arithmetic, casts, `CASE`), for `OFFSET` and for a page with `count(*) OVER ()`, with parallel workers; `order_columns` keeps sorted runs for it (opt-in).
- **More shapes in the scan.** A group table that outgrows `work_mem` spills to partitions, `count(DISTINCT)` of a column of a few values keeps a few words per group, semi and anti joins to a tree of small tables, `LEFT JOIN LATERAL` over the elements of a JSON list, two-level arms over a spine, 16 `UNION ALL` arms. `sift.explain_refusals` and `sift.explain_why(query)` say why a query kept its plan; `sift.plan_scale` plans as if the tables were larger.
- **`sift_table`.** A row over the write buffer is accepted (`sift.table_wide_rows`, up to 1 GB through tail pages); `COPY`, `CREATE TABLE AS` and `REFRESH MATERIALIZED VIEW` write column groups (`sift.table_bulk_groups`); a backend reads the tombstone log of a table 2.2 times faster; the row fetch of a text from a dictionary stored in blocks no longer loads the whole dictionary (1.4 ms where it took 28.6 ms).
- **Row-level security.** `sift.rls_scores` (superuser setting, off by default) gives a role under a policy the ranked scan and `sift.score()`; the scores are those of the whole index, so they depend on rows the role cannot see (the caveat is in docs/USAGE.md, "Row-level security").
- **PostgreSQL 19 is a supported major** (17, 18 and 19), no longer experimental. AArch64 has a `neon` level of `sift.simd` (Linux arm64 and Apple silicon).
- **Upgrades.** `ALTER EXTENSION pg_sift UPDATE` prints what is left to do, `sift.upgrade_commands()` lists it, `scripts/upgrade-check.sh` is a preflight (also `--to` an older release), and `docs/UPGRADING.md` has the downgrade sequence; `docs/LIMITS.md` lists every fixed limit with the setting that changes it.

## What a 3.2 user must know

- **A `REINDEX` of every `sift` text index is required** (except `analyzer = keyword`). In 3.3 a word over 255 bytes is stored as its start and a hash; an older index holds it cut at 255 bytes, so **queries miss rows** until it is rebuilt. The update prints the commands.
- **A database written with the 3.3 defaults cannot be read by a 3.2 library.** 3.3 writes at `sift.col_format_minor = 15` by default (a `sift_col` index created or rewritten after the update, a `sift_table` with a date or timestamp column); a 3.2 library refuses what it cannot read, with a `REINDEX` hint. To keep 3.2 able to read new writes, set `sift.col_format_minor = 6` (4 without JSON paths) for the server (`ALTER SYSTEM`) before the update: a setting of one session or of one role does not reach the background worker of `sift.col_auto_merge`, which would write the default 15 into an index. Or create the indexes `WITH (cube_minmax = false, superunit = 0, calendar_columns = 'off')`. The way back, step by step (the preflight with `--to 3.2`, one command per blocker, then install the 3.2 files), is in [docs/UPGRADING.md](https://github.com/brauliobo/pg_sift/blob/v3.3.0/docs/UPGRADING.md), "Going back to 3.2". A standby that stays on 3.2 needs the same.
- **The default minor is 15; minor 16 is opt-in.** `point_tid_columns` writes minor 16 only for the segments of an index created with it, and a 3.2 library refuses minor 16 like any column format it does not know. To roll the option back, `sift.col_format_minor = 14` and a `REINDEX` of that index (the point directory then lists rows again).
- **Background work is on.** The auto-merge worker also runs `VACUUM` on the tables of `sift_col` indexes and writes WAL bounded by `sift.col_auto_merge_wal_pct` (5% of the WAL the server writes); the CREATE INDEX and REINDEX NOTICE says how to stop it. `sift.col_auto_merge = off` and `sift.table_auto_defrag = off` restore the 3.2 behavior; a `sift_table` pass adds about 60% more WAL on an update, insert and delete mix at 200,000 rows (the line in CHANGELOG.md has the numbers).
- **Other defaults that change bytes or plans:** `sift.agg_lookup_shared_kb` shares the rows of a joined table between backends (-1: the smaller of `sift.agg_lookup_cache_kb` and 5% of `shared_buffers`; 0 turns it off); `sift.table_wide_rows` and `sift.table_bulk_groups` are on; the planner compares the shared and the inline form of a CTE with a margin of 1.6.
- **The first statement of a fresh backend with bound parameters** (a driver on the extended protocol, `EXECUTE` with arguments) is planned again through the planner hook in 3.3.0, as a plain `SELECT` already was in 3.2.3: 3.2.3 still plans it without sift once per backend (220 to 260 ms against 3 to 5 ms for the next statement). `session_preload_libraries = 'pg_sift'` avoids that cost in both.
- **A backend can hold up to 64 MB for `sift_col` scans.** 3.3 keeps the checked heads and the zone arrays of the segments a backend reads, besides the parsed dictionaries that 3.2.3 already keeps, in one least-recently-used cache per backend capped at 64 MB (`dict_cache_kb`, `col/dictcache.c`; a compile-time constant in 3.3.0, no setting changes it). A long-lived backend (a pooled connection, a parallel worker) grows toward the cap and stays there; the entries of segments that a merge or a promotion replaced stay until the cache pushes them out. Measured under a write-heavy soak that creates segments all the time (6 backends, 120 s, PostgreSQL 18): 13 to 36 MB per backend, about 2.1 kB per cached segment; 3.2.3 does not grow that way. Budget up to 64 MB of private memory per backend for `sift_col` scans; to bound it, limit the number of long-lived backends (a pooler with a server lifetime) or rebuild with a lower `CDICT_CACHE_MAX_KB`. A setting is planned for 3.4.

## Fixes for defects present in 3.2.3 and earlier

There is no 3.2.4: these fixes ship with 3.3.0.

- **`sift_table`: a bitmap-scan `UPDATE` or `DELETE` could stop early without an error.** When a `VACUUM` promoted the write buffer of the table while the statement ran, the statement skipped the rows of the promoted blocks and reported success (1530 of 3000 rows updated in the reproduction). Present in 3.2.3 and, by the code path, since 3.0.0. Affected: `sift_table` tables updated or deleted through a bitmap scan while a `VACUUM` or the background defrag runs on them. Until the update, `SET enable_bitmapscan = off` for such statements, or do not run them concurrently with `VACUUM` or the defrag; a bulk statement that reports fewer rows than the table holds for its `WHERE` clause is the sign.
- **`sift_table`: an `UPDATE` or `DELETE` could fail with `failed to fetch tuple being updated`.** It happened when a `VACUUM` or the background defrag replaced the tombstone log while the statement scanned the table (sequential or bitmap scan). Present in 3.2.3. The statement rolls back and no rows are lost; run it again, or avoid running it while a `VACUUM` is in progress.
- **`GROUP BY` an expression of a `date` column that holds `infinity` and `-infinity` failed when the aggregate scan was chosen** (3.2.3: `a column range of 4294967296 values is too wide for the aggregate scan`). An error only, no wrong rows. Workaround: `SET sift.enable_agg = off` for the statement.
- **`GROUP BY` on a `date` column whose selected rows are all `infinity` (or all `-infinity`) stopped the backend.** The range table of the group keys stepped over the end of the 32-bit range and wrote far outside its allocation; a serial scan was enough, no parallel worker needed. Present in 3.2.3 and earlier. Workaround: `SET sift.enable_agg = off` for the statement.

## Compatibility

- PostgreSQL 17, 18 and 19; the packages below are for PostgreSQL 18 (other majors build from source with `make USE_PGXS=1`; PostgreSQL 19 was tested on its release candidate). Extension SQL 3.3; `ALTER EXTENSION pg_sift UPDATE` reaches it from any older version in one command. Column format minor 15 by default (16 opt-in); the BM25 index needs the `REINDEX` above. [docs/UPGRADING.md](https://github.com/brauliobo/pg_sift/blob/v3.3.0/docs/UPGRADING.md) has the preflight and the steps.

## Packages

x86-64 packages for PostgreSQL 18 are attached below (`SHA256SUMS` covers every asset); other versions build from source with `make USE_PGXS=1`. The packages depend on `liblz4` and `libzstd`.

- Debian 13, Ubuntu 24.04 (PGDG `postgresql-18`): `sudo apt install ./postgresql-18-pg-sift_3.3.0-1_amd64.deb`
- RHEL, Rocky, Alma 9 and 10 (PGDG `postgresql18-server`): `sudo dnf install ./pg_sift_18-3.3.0-1.el9.x86_64.rpm`
- Arch Linux: AUR `pg_sift`.

Every change: [CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.3.0/CHANGELOG.md).

## SQLite extension

pg_sift for SQLite 3.3.0 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.3.0 release: no change to the SQL interface of the extension.

- **No change:** the 3.3 work is in the PostgreSQL column index, the aggregate scan and `sift_table`, which are not in this extension. No speed-up or slow-down is claimed.
- Needs SQLite 3.38 or newer; Linux x86-64, glibc 2.34 or newer. Built on Debian 12, loaded into the SQLite of Debian 12, Ubuntu 24.04 and AlmaLinux 10 before release.
- Storage format 2: tables of 3.2.3 open as they are, nothing to rebuild.

Download `sift-sqlite-3.3.0-linux-x86_64.tar.gz` (below, covered by `SHA256SUMS` with the packages), unpack it and `.load ./sift` from the `sqlite3`
shell (`sha256sum --ignore-missing -c SHA256SUMS`). Documentation: [sqlite/README.md](https://github.com/brauliobo/pg_sift/blob/v3.3.0/sqlite/README.md).
Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.3.0/sqlite/CHANGELOG.md).

## MariaDB extension

pg_sift for MariaDB 3.3.0 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.3.0 release: no change to the SQL files.

- **No change:** the 3.3 work is in the PostgreSQL column index, the aggregate scan and `sift_table`, which are not in this extension. No speed-up or slow-down is claimed.
- One library per MariaDB series, because it calls the server's own symbols and refuses to load into another series: 10.11, 11.4, 11.8, 12.1 and 13.0; Linux x86-64. Each was built in the stock `mariadb:<series>` image against that server's headers and loaded into a server of the same image before release. `BUILD-INFO` in the tarball lists the server version and the glibc each library needs; a server of another distribution or of a series not listed is built from source (`make -C mariadb`).
- Index format 2: an index of 3.2.3 opens as it is, nothing to rebuild.

Download `sift-mariadb-3.3.0-linux-x86_64.tar.gz` (below, covered by `SHA256SUMS` with the packages), unpack it, copy `lib/<series>/libsift_udf.so` of your server's series
(`SELECT VERSION()`) into its `plugin_dir`, then `SOURCE sql/functions.sql` once per server and `sql/sift.sql` once per database (`sha256sum --ignore-missing -c SHA256SUMS`).
Documentation: [mariadb/README.md](https://github.com/brauliobo/pg_sift/blob/v3.3.0/mariadb/README.md).
Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.3.0/mariadb/CHANGELOG.md).