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): 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), 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 <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 <index>. To list the indexes at risk:
  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 <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 <key>, 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 <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('<index>', true) returns no row for a good index (it flagged all 12 bad runs and none of the 12 good ones). Repair: REINDEX INDEX <index>. Before 4.0.2, do not run pg_squeeze on a table with a sift_col index, or run REINDEX INDEX <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 <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 <index> SET (track_changes = 'off') before the dump or pg_upgrade, and ALTER INDEX <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 <index>, or ALTER INDEX <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 <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 <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.

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 = '<key column>') 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. Every change: 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. Every change: 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:<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.
  • 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/<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. Every change: mariadb/CHANGELOG.md.