pg_sift 4.0.0: index-only reads that do not visit the heap for rows changed and not yet vacuumed, an index layout that is chosen from the data (entity and timestamp, compact order arrays), search with the text search configurations and `INCLUDE` columns, and a fix for wrong rows of `LATERAL` and correlated sub-selects that every release from 3.4.0 to 3.7.1 had. It writes new on-disk formats that 3.7 and older refuse (column format minors 40 to 42, BM25 metapage minor 1.5): read "Upgrading from 3.7" before you install it on a primary whose standby or rollback target stays on 3.7. ## Known issues Fixed in 4.0.1 (a patch release that changes only the library: install the packages and restart the server; [notes of 4.0.1](https://github.com/brauliobo/pg_sift/releases/tag/v4.0.1)): the partition and inheritance item, the primary key item, the dump and `pg_upgrade` item and the `REINDEX CONCURRENTLY` item. The workarounds stay for 4.0.0. The `pg_squeeze` item is fixed in 4.0.2 ([notes of 4.0.2](https://github.com/brauliobo/pg_sift/releases/tag/v4.0.2)), which also ends the log of a table at the attach, so a table attached after the update needs no `REINDEX INDEX` at the detach. - **Wrong results: grouped reads of a table that is attached as a partition or made an inheritance child after its `sift_col` index was created (fixed in 4.0.1).** For a table that already has a `sift_col` index with `track_changes` on (the default) and is later attached (`ALTER TABLE parent ATTACH PARTITION t`, or `ALTER TABLE t INHERIT parent`), the DELETEs and key-changing UPDATEs issued on the parent fire none of t's statement triggers. They are not in the index's log, yet reads still take the log for complete and count rows that are gone: in a test the grouped `count` and `sum` of the child through `Custom Scan (SiftAgg)` differed from the heap's in 10 of 10 groups, and the heap's answer was right. Only grouped aggregates were verified; other reads of the index's units use the same path. Not affected: an index created after the table is a partition or child (it is created untracked, with a NOTICE), `CREATE INDEX` on a partitioned parent, INSERT, statements issued on the child itself, and tables never attached. Workaround: `ALTER INDEX SET (track_changes = 'off')` (no rebuild; reads check the heap as before 4.0), before the attach, or on a table that is attached already; turn it on again only when the table is a plain table again, with `REINDEX INDEX `. To list the indexes at risk: ```sql SELECT i.indexrelid::regclass AS index, i.indrelid::regclass AS tbl FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid JOIN pg_am a ON a.oid = c.relam WHERE a.amname = 'sift_col' AND c.relkind = 'i' AND i.indrelid IN (SELECT inhrelid FROM pg_inherits) AND (SELECT track_changes FROM sift.col_tracking(i.indexrelid)) ORDER BY 1; ``` A table that was attached, written through the parent and detached again is not listed; for it, and for any table in doubt, `REINDEX INDEX ` is the cure. From 4.0.2 the log is also ended at the attach, so a table attached after that update needs no `REINDEX INDEX` at the detach; a table attached before it still does. - **Wrong results: a primary key that is dropped and added again (fixed in 4.0.1).** On a table with a `sift_col` index with `track_changes` on (the default), `ALTER TABLE ... DROP CONSTRAINT `, then DELETEs or UPDATEs, then `ADD PRIMARY KEY` again leaves the log marked complete although the statements that ran without the key are not in it, and a grouped read counts rows that are gone (in a test 10 of 10 groups differed from the heap; after `REINDEX INDEX` they matched). Workaround: after the key was dropped and added again (or whenever the key of such a table was ever dropped), run `REINDEX INDEX `; do not drop and add the key of such a table without it. - **Wrong results: `pg_squeeze` of a table with a `sift_col` index can leave rows out of the index (since 3.6.0; fixed in 4.0.2).** While pg_squeeze runs, the index of its copy can miss the rows that were inserted or updated meanwhile: in tests (a real pg_squeeze, 200,000 rows, one writer) 3 to 7 of 12 runs of every release from 3.6.0 to 4.0.1 lost a whole buffered batch (28 to 39 of about 130 inserted rows), with `track_changes` on or off, and 3.5.3 lost none in 12 runs. The aggregates and reads that go through the index then undercount. Affected: only a table that pg_squeeze rewrites. Not affected: a `sift` (BM25) index, a `sift_table` (pg_squeeze refuses it), pg_repack, and logical replication apply. Check: `SELECT sift.col_verify('', true)` returns no row for a good index (it flagged all 12 bad runs and none of the 12 good ones). Repair: `REINDEX INDEX `. Before 4.0.2, do not run pg_squeeze on a table with a `sift_col` index, or run `REINDEX INDEX ` after every run and check with `col_verify`. With `track_changes` on, pg_squeeze also failed every squeeze in the tests with `track_changes: column "id" can be NULL`, and after a squeeze that completes the log is incomplete until `REINDEX INDEX `. - **A dump of a table with a primary key and a default `sift_col` index cannot be restored reliably, and `pg_upgrade` can fail (fixed in 4.0.1).** The default `track_changes = 'auto'` is stored as `track_changes = id`, which needs the unique index of the key, and `pg_restore -j` or `pg_upgrade` creates the index before that unique index exists: in a test `pg_restore -j8` failed 19 to 21 of 24 `CREATE INDEX` with `track_changes: column "id" has no unique index of its own`, and `pg_upgrade` on PostgreSQL 18 failed (the index is dumped before the primary key constraint). The data in the dump is not affected. Workaround: `ALTER INDEX SET (track_changes = 'off')` before the dump or `pg_upgrade`, and `ALTER INDEX SET (track_changes = 'id')` after the restore. - **`REINDEX INDEX CONCURRENTLY` and `REINDEX TABLE CONCURRENTLY` of a `sift_col` index with `track_changes` (the default; fixed in 4.0.1).** Every UPDATE and DELETE on the table fails afterwards with `could not open relation with OID n`. INSERT, reads, aggregates and the data are not affected, and nothing is damaged. Repair: `REINDEX INDEX `, or `ALTER INDEX SET (track_changes = 'off')` and then `SET (track_changes = 'auto')`. Avoid the concurrent form: use a plain `REINDEX`, or `CREATE INDEX CONCURRENTLY` and `DROP INDEX`. Do not run `REINDEX CONCURRENTLY` while pg_repack runs on the same table. - **Fails loudly, not wrong.** `DROP INDEX CONCURRENTLY` of an index with `track_changes` and `pg_repack --only-indexes` fail with `must be first action in transaction`; run `ALTER INDEX SET (track_changes = 'off')` first. `ENABLE TRIGGER ALL` after `DISABLE TRIGGER ALL` (what `pg_restore --disable-triggers` does) leaves the reads of the table on the heap, which is slower and not wrong, until `REINDEX INDEX `. This is unchanged in 4.0.1. ## Highlights - **The headline cases of the README, measured on this release.** 300,000 rows, PostgreSQL 18.6, one backend, warm medians of 5 runs (run 3c5e625dfb80-20261103T202442Z-8ca1), the planner's own plan with the extension against the plan PostgreSQL chooses without it, each figure checked against the records of that run: the live rows of a table with soft-deleted rows grouped from the units' pre-aggregates (`softdel_live_groupby`) 0.057 ms, 0.667 M instructions against 50.8 ms, 596 M instructions (891x); `count(DISTINCT key)` of a whole table from the key lists of its units (`key_presence_distinct`) 0.14 ms, 2.09 M instructions against 56.7 ms, 410 M instructions (405x); a `LIMIT 1` child with an `ORDER BY` read as a first-row lookup (`lateral_first_row`) 4.17 ms, 65.6 M instructions against 1,449 ms, 25,536 M instructions (347x); the rows of three keys of a foreign key column (`key_table_point`) 0.075 ms, 0.748 M instructions against 19.3 ms, 273 M instructions (257x); `count(DISTINCT)` of a unique key (`count_distinct_unique`) 1.13 ms, 14.7 M instructions against 238 ms, 2,962 M instructions (211x); the aggregate of one foreign key with no b-tree on it (`point_agg_fk`) 0.063 ms, 0.391 M instructions against 10.7 ms, 121 M instructions (170x); an unsorted integer column's `LIMIT n` from the sorted runs of the units (`topn_sorted_runs`) 0.115 ms, 0.873 M instructions against 22.4 ms, 150 M instructions (195x); an `IN` list of 100 ids looked up in the point directory (`point_in_100`) 0.076 ms, 1.07 M instructions against 14.8 ms, 161 M instructions (195x); three `UNION ALL` arms over the elements of a jsonb array, counted `DISTINCT` above one shared common table expression (`cte_series_arms`) 8.76 ms, 133 M instructions against 858 ms, 8,283 M instructions (98x). The table of 26 cases, with instructions and blocks read for each, is in the README. - **Reads that do not visit the heap for changed rows (`track_changes`).** A `sift_col` index on a heap table keeps a log of the entries that stopped being live, written by two internal statement triggers (AFTER UPDATE and AFTER DELETE; one NOTICE at `CREATE INDEX`), and a read of an MVCC snapshot takes the log for the whole truth when it is complete. On 300,000 rows with the recent 20% updated and not yet vacuumed, a grouped count goes from 21.77 to 10.66 M instructions and from 1,241 to 2 heap blocks, and 10.09 MB to 0.41 MB read cold; the top 20 by a key from 2.28 to 0.80 M instructions. It has a cost on the write side (100,000 rows, a primary key and one `sift_col` index, instructions of the statement with it on against off): an `UPDATE` of 1 row +14.2%, of 100 rows +35.9%, of 5% of the rows +43.6%, a `DELETE` of 2% +34.9%, an `INSERT` of 5% more rows +0.6%, `CREATE INDEX` and `REINDEX` +27.8% (the README has the table with times, WAL and blocks). On by default as `'auto'` (the primary key of the table when the index holds it; otherwise off with a NOTICE); `WITH (track_changes = 'off')` turns it off. Indexes of earlier releases are not changed by `ALTER EXTENSION UPDATE`. - **The layout is chosen from the data.** An index with an entity column (a foreign key or a `*_id` column of 10 values or more) and a date or timestamp gets `key_order = 'auto: entity, ts'` where the layout pays, and `'auto: ts'` for a key cut by a timestamp alone; a NOTICE says what was chosen and `key_order = 'off'` keeps the old layout. 300,000 rows sorted by the entity (100 entities): the latest row of each entity reads 4.3 M instructions instead of 72.7 M and `ORDER BY entity, ts OFFSET 10,000` 14.7 M instead of 51.0 M, with an index 40% smaller; a timestamp key cut alone, `OFFSET 10,000` 6.2 M instead of 51.9 M. - **Compact order arrays (`order_layout`).** `auto`, the default, keeps a chunk of an order array compact where that is at least a fifth smaller: a microsecond timestamp takes 3.6 bytes a row where the whole order takes 6.5, an index of 1,000,000 rows 12.12 MB against 15.02 (a b-tree on the column: 22.49), and `ORDER BY ts LIMIT 20 OFFSET 100000` reads the same entries in 3.27 ms and 108 cold blocks against 3.08 ms and 142 (a b-tree: 4.10 ms, 277). The order array of a text column names its strings by dictionary code and keeps no second copy of them. - **Paging, joins and `LATERAL`.** `ORDER BY g, k` and `WHERE g = x ORDER BY k` over an index on `(g, k)` are an index-only scan of the second order grouped by `g`; the `OFFSET` of a page is cut off the front of the order array in one step, for an integer, a date, a timestamp and a text key; a `LATERAL` top-N over a table partitioned by range or by hash reads the top-N of each partition and merges them, or one partition, where PostgreSQL scanned every partition and sorted for each outer row. The planner offers the plain index scan to a join that looks up a `sift_col` index with one equality for each outer row. - **Loads of a `sift_table`.** A COPY, `CREATE TABLE AS` or `REFRESH MATERIALIZED VIEW` without parallel workers encodes each unit into one open segment as it fills: a `COPY` of 10,000,000 rows and the maintenance that followed, 24,534 to 13,836 ms and 255,160 to 167,908 M instructions, WAL 47.7 to 14.2 bytes a row, 28 segments to 2, the table 196 to 137 MB (a heap table: 8,050 ms and 106,902 M). - **Search.** `stemmer` takes the name of a PostgreSQL text search configuration (`stemmer = 'german'`; `french`, `spanish`, `russian`, the 29 Snowball languages of the server, or your own dictionary chain): the words are the ones `to_tsvector` indexes, and on corpora of 340,000 documents the index is 25% to 32% smaller than with no stemmer at the same build cost. A sift index can name `INCLUDE` columns (`CREATE INDEX ON docs USING sift (body) INCLUDE (category_id, created_at)`, up to 8 columns of `boolean`, `smallint`, `integer`, `bigint`, `date`, `timestamp`, `timestamptz`): a ranked scan drops the documents that fail a condition on them before it scores them, at 0.5% to 0.6% of the index a column. - **Documentation of what the library says.** `docs/MESSAGES.md` lists every NOTICE, WARNING and `VACUUM VERBOSE` line of the library: the exact text, when it appears, the decision taken, its benefit and trade-off with the numbers of the option cases, and how to switch it off. - **Fixed: wrong rows.** In 3.4.0 through 3.7.1, a `LATERAL` join or a correlated sub-select whose inner query reads the global order of a `sift_col` index (`order_array_columns` on the `ORDER BY` column, with or without `order_array_group`) took the group it pins (`WHERE e.g = d.id`) and the bounds of its key from the outer query as 0 instead of the value of each outer row, so the inner query found no row, with no error (`LEFT JOIN LATERAL` gave NULLs, a scalar sub-select NULL). `sift.enable_agg = off` shows the right rows on an affected release. The values of each probe are used now. The rest of the fixes (a crash planning a `MATERIALIZED` CTE with a `DISTINCT` sub-select, a crash of `sift.suggest_paths` on a `sift_table`, a segmentation fault of `sift.col_merge` next to writes, a stall of an insert behind a merge of the maintenance worker, and others) are in [CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.0/CHANGELOG.md). ## Known limits - **Formats are one way.** 3.7 and older cannot read what 4.0 writes (below); a standby or a rollback target that stays on 3.7 needs the settings of "Upgrading from 3.7". - **`track_changes` costs two statement triggers and needs a key.** An index on a table the triggers cannot hear, or on one without a key the index holds, gets `track_changes = 'off'` with a NOTICE; the reads of that index visit the heap for changed rows as before. An index of 3.7 or earlier keeps its options, has no triggers and stays without a key until `REINDEX INDEX`, or until `ALTER INDEX i SET (track_changes = '')` installs them. ## Upgrading from 3.7 1. **Preflight on the primary:** `scripts/upgrade-check.sh --all`, and with `--to 3.7` what 3.7 could not read (both read-only). The second lists every index that 3.7 would refuse and the `REINDEX` that writes what 3.7 reads. 2. **Install the files and restart the server** on the standbys first, then the primary (a backend that loaded the old library keeps it until its connection ends). Run `ALTER EXTENSION pg_sift UPDATE` in each database: the extension SQL goes from 3.7 to 4.0 (`sql/pg_sift--3.7--4.0.sql` adds `sift.col_extents`, `sift.col_space` and the trigger function of `track_changes`). 3. **What 4.0 writes that 3.7 refuses:** column format minors 40 to 42 (complete arrays in a list of `order_array_columns`; a compact chunk in an order array, which `order_layout = 'auto'` writes for spread keys such as a timestamp; the order array of a text column by dictionary code), written when `sift.col_format_minor` is 15 or more (the default), and the BM25 metapage minor 1.5 (an index with `INCLUDE` columns or a text search configuration as `stemmer`). Existing files are read as they are; a merge, a `VACUUM` rewrite or a `REINDEX` writes the new formats. 4. **If a standby or a rollback target stays on 3.7:** `SET sift.col_format_minor = 14` (`postgresql.conf`, or for the role and the database) before the primary writes the new formats, then `REINDEX INDEX` for each index that `scripts/upgrade-check.sh --all --to 3.7` lists; do not create an index with `INCLUDE` columns or a text search configuration as `stemmer` on such a primary. 5. **Check:** `SELECT sift.verify('idx', true)` for a BM25 index and `SELECT sift.col_verify('idx', true)` for a `sift_col` index; `scripts/upgrade-check.sh` again: the second run has no warning the first one did not explain. No setting changes its default for an existing index (`track_changes` and the layout choices of `CREATE INDEX` apply to an index you create or `REINDEX` after the update; a 3.7 index keeps its whole orders and is read as before). `docs/UPGRADING.md` has the full procedure and the table of the first release that reads each format. ## Compatibility - PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with `make USE_PGXS=1`). Extension SQL 4.0 (`pg_sift--3.7.sql` and `pg_sift--3.7--4.0.sql`); column format minors 3 to 42 are read, BM25 metapage minors 1.0 to 1.5. The SQLite and MariaDB extensions: no change since 3.7.0. ## Packages x86-64 and arm64 packages for PostgreSQL 17, 18 and 19 are attached below (`SHA256SUMS` covers every asset); other versions build from source with `make USE_PGXS=1`. - Debian 12 and 13, Ubuntu 22.04 and 24.04 (PGDG `postgresql-N`, N = 17, 18 or 19): `sudo apt install ./postgresql-N-pg-sift_4.0.0-1_amd64.deb` (`_arm64.deb` on arm64) - RHEL, Rocky, Alma 9 and 10 (PGDG `postgresqlN-server`): `sudo dnf install ./pg_sift_N-4.0.0-1.el9.x86_64.rpm` (`.aarch64.rpm` on arm64) - Amazon Linux 2023 (the distribution's `postgresqlN-server`, N = 17 or 18): `sudo dnf install ./pg_sift_N-4.0.0-1.amzn2023.x86_64.rpm` (`.aarch64.rpm` on arm64) - Arch Linux: AUR `pg_sift`. Benchmarks: the table of the headline cases in the [README](https://github.com/brauliobo/pg_sift/blob/v4.0.0/README.md). Every change: [CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.0/CHANGELOG.md). ## SQLite extension pg_sift for SQLite 4.0.0 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.0.0 release: no change to the SQL interface or the shared format. - **No change:** the changes of this release are in the PostgreSQL column index, the aggregate planner, the BM25 index and the packages, 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.4.0 to 3.7.0 open as they are, nothing to rebuild. Download `sift-sqlite-4.0.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/v4.0.0/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.0/sqlite/CHANGELOG.md). ## MariaDB extension pg_sift for MariaDB 4.0.0 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.0.0 release: no change to the SQL files. - **No change:** the changes of this release are in the PostgreSQL column index, the aggregate planner, the BM25 index and the packages, 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:` 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. - Index format 2: an index of 3.4.0 to 3.7.0 opens as it is, nothing to rebuild. Download `sift-mariadb-4.0.0-linux-x86_64.tar.gz` (below, covered by `SHA256SUMS` with the packages), unpack it, copy `lib//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/v4.0.0/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.0/mariadb/CHANGELOG.md).