pg_sift 4.3.4

pg_sift 4.3.4: a patch release of 4.3.3 that fixes a statement that failed with requested DSM segment size does not match size of existing segment after a package upgrade without a PostgreSQL restart, or after a changed setting. Extension SQL 4.3.4 (no new object, no format change): install the files, then ALTER EXTENSION pg_sift UPDATE in each database; a restart of the server is recommended, no longer needed for this error.

Highlights

• Fixed: a statement failed with ERROR: requested DSM segment size does not match size of existing segment (4.x before 4.3.4, after the library was replaced under a running server, or a setting changed). The shared tables of pg_sift (the rows of joined tables and the dimension maps of the aggregate scan, the choices of the planner, the shared dictionaries, the statistics of the leaves, the write-buffer arena and the rollup state) are named segments of the server's dynamic shared memory registry. PostgreSQL keeps the name and size of the first request until the server restarts, and raises that error for a request of the same name at another size. A library replaced while the server runs keeps older and newer backends side by side: the registry still held the size an older library asked, and a newer one asked the same name at the size of its own structure (the table of joined rows is 11,544 bytes in 4.2.1 and 13,592 bytes in 4.3.0 to 4.3.3, under one name). The tables that are optional caches downgrade the error to a line in the log; the read of the rows of a joined table, the dimension maps, the write-buffer arena and the rollup state did not, so the statement failed, on and off, for the backends that connected after the install. The registry name now carries the size asked, in the one function that asks the registry (a name is never asked at two sizes: a layout of another size is a table of its own), and no other code asks the registry (a lint rule holds it). The rows of joined tables and the dimension maps are attached the way the other shared tables are: a table that cannot be had (no room in /dev/shm) leaves the statement on the path without shared tables, with a message in the log. Held by the TAP test col_dsm_registry_stale: it leaves entries of an earlier release in a running server (the names of 4.3.3 at other sizes), then runs a joined aggregate, a dimension map and a write that keeps its rows in the arena, in new backends; on 4.3.3 the three raise the error, on 4.3.4 they return the rows of the stock plan.
• A setting that sets a size is a table of its own: sift.col_leaf_cache_kb is the one setting that decides the size of a named table (the others are fixed structures or record their size in the table itself). A size that no table has yet, set after the server started, now makes a table of that size for the backends that use it; before, such a backend logged that the statistics were not shared and went without.

Who is affected and what to do

• Affected: every 4.x release before 4.3.4 where the library was replaced while the server ran (a package upgrade without a restart, which keeps backends of the older library in the registry), and the same releases where a size of a named table changed with a setting. A server that was restarted after each install, and never changed sift.col_leaf_cache_kb while it ran, is not affected.
• What you saw: a statement that failed with the error above, on and off, in the new connections; the same statement ran in a connection of an older library, and again after a restart. Nothing stored is involved: no wrong answer, no repair, no REINDEX.
• Update (the steps, in this order): install the files, then ALTER EXTENSION pg_sift UPDATE in each database (4.3.3 to 4.3.4 runs an empty script: no object changes; no format change). A restart of the server is recommended (a standby too): a backend that already loaded an older library keeps it until its connection ends, and keeps asking the registry under the name and size of that library; it cannot share a table with the new library (the names differ), so nothing is corrupted, but only after the restart is there a single library. With 4.3.4 installed the error stops in the backends that start after the install without a restart, because they ask the registry under names no older entry has; a backend of an older library that meets an entry another library made at a size of its own can still raise it until it ends.
• What stays in memory: the registry cannot give a segment back. The entries an earlier library or size made stay until the server restarts and are never joined again: one entry for each name and size ever asked. The names are the nine tables of this release and up to 128 segments of 1 MB of the write-buffer arena; the sizes of the tables are 11 kB to 1 MB for the directories, 16 MB by default for the leaf statistics (sift.col_leaf_cache_kb), 64 MB by default for the dictionary ring (sift.col_dict_shared_cache_kb) and up to 128 MB for the arena, and only the tables that backends of the earlier library used were made. Releases do not change a size often: a server that replaces the library many times without a restart keeps one such set for each size that was asked, not one for each install. A restart gives all of it back.
• If you cannot update yet: restart the server, which empties the registry; the error comes back only if the library is replaced under the running server again.
• Going back: install the 4.3.3 files and restart after ALTER EXTENSION pg_sift UPDATE TO '4.3', which runs the empty script back to 4.3 (4.3.3 itself is reached by UPDATE TO '4.3.3' from there). The cause of the error is back with those libraries.

Compatibility

• PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A replacement of 4.3.3 with the install; extension SQL 4.3.4 (pg_sift--4.3.3--4.3.4.sql, and an empty pg_sift--4.3.4--4.3.sql back); no column format minor and no BM25 metapage minor changes. The registry names change with this release: a library of 4.3.3 or earlier and one of 4.3.4 never share a table.

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.3.4-1_amd64.deb (_arm64.deb on arm64)
• RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.3.4-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.3.4-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
• Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.3.4 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.3.4 release: no change to the SQL interface or the shared format.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.3.4-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.3.4 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.3.4 release: no change to the SQL files.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.3.4-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.

pg_sift 4.3.3

pg_sift 4.3.3: a patch release of 4.3.2 that fixes a failed merge of a sift_col index: a merge that took the maintenance lock while a rewrite of the same index was still to commit failed with sift index "<name>" is corrupted. Extension SQL 4.3.3 (no new object, no format change): install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database.

Highlights

• Fixed: a merge that ran while a rewrite that chose key_cut again was still to commit failed with sift index "<name>" is corrupted (4.0.0 to 4.3.2). A rewrite of a sift_col index (a merge that rewrites the whole index, for example sift.col_merge(index, true) or the rewrite that dead_rewrite_pct asks for) may choose key_cut again; it writes that option in its own transaction, which commits after the maintenance lock was let go. A second merge (sift.col_merge, the maintenance worker, or the merge inside VACUUM) that took the lock in that window kept the options of before the commit, read the segments the rewrite had cut by the key as if they were in the order of the TIDs, and its check stopped it: ERROR: sift index "<name>" is corrupted (Segment N is not in the order of the TIDs). A transaction that wrote an option of the index now keeps the maintenance lock to its end, and the next holder reads the options again. Held by the isolation test col_key_cut_merge_lock, which fails on 4.3.2 (the second merge reports the error) and passes on 4.3.3 (the second merge waits for the rewrite and completes).

• Tests: the plan checks of the test suite accept the plans PostgreSQL 18.7 makes; no change to the extension.

Who is affected and what to do

• Affected: 4.0.0 to 4.3.2 (every 4.x release before 4.3.3); 3.x does not decide key_cut again and is not affected. The window is a rewrite that changes key_cut (the option is written in a transaction that has not committed) while a second merge of the same index takes the maintenance lock: a table with writes and a merge of its index that overlaps such a rewrite. An ordinary open transaction cannot reach it.
• What you saw: a failed call and nothing else. The merge fails before it replaces a segment, so the index is as it was; sift.col_verify passes afterwards and the same call succeeds when it is run again. A VACUUM or a maintenance worker step that met it stopped with the error and was retried by the next run. No wrong answer of a query was seen from it (see the open case below). Nothing stored is known to need repair; no REINDEX.
• Update (the steps, in this order): install the files and restart the server (a standby too), because a backend that already loaded the old library keeps it until its connection ends; then ALTER EXTENSION pg_sift UPDATE in each database (4.3.2 to 4.3.3 runs an empty script: no object changes; no format change). A database at 4.3, 4.3.1 or 4.3.2 goes through the earlier scripts by itself.
• If you cannot update yet: run the failed sift.col_merge or VACUUM again.
• Going back: install the 4.3.2 files and restart after ALTER EXTENSION pg_sift UPDATE TO '4.3', which runs the empty script back to 4.3 (4.3.2 itself is reached by UPDATE TO '4.3.2' from there). The cause of the failed merge is back with those libraries.

Open case

• Not shown to happen, not ruled out: a stale merge (one that kept the options of before the commit) whose segments cut by the key all lie in one superblock would pass the check of the order of the TIDs, and could write a misordered unit. The segments of a rewrite in that window span many superblocks, and the case could not be made to happen; the fix removes its cause, since the merge no longer takes the lock with options of before the commit.

Compatibility

• PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A replacement of 4.3.2 with the install and a restart; extension SQL 4.3.3 (pg_sift--4.3.2--4.3.3.sql, and an empty pg_sift--4.3.3--4.3.sql back); no column format minor and no BM25 metapage minor changes.

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.3.3-1_amd64.deb (_arm64.deb on arm64)
• RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.3.3-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.3.3-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
• Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.3.3 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.3.3 release: no change to the SQL interface or the shared format.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.3.3-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.3.3 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.3.3 release: no change to the SQL files.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.3.3-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.

pg_sift 4.3.2

pg_sift 4.3.2: a patch release of 4.3.1 that fixes a wrong answer of the aggregate scan: a correlated sub-select over a join with a derived table could return the count of an earlier outer row for the later ones. Extension SQL 4.3.2 (no new object, no format change): install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database.

Highlights

• Fixed: a correlated sub-select over a join with a derived table returned the answer of an earlier outer row (4.0.5, 4.1.4, 4.2.2, 4.3.0 and 4.3.1; the aggregate scan, sift.enable_agg, which is on by default). The shape is a sub-select in the select list or the WHERE of an outer query, whose FROM joins a table to a derived table that PostgreSQL does not pull up (a sub-select with GROUP BY, DISTINCT, UNION or HAVING), and whose condition compares a column of the outer row with a column of the derived table. PostgreSQL pushes that condition into its plan of the derived table as a parameter of the outer query. The aggregate scan did not see it there, read the derived table once and replayed that result for every outer row: the count (or sum) of the first outer row was returned for the later ones. The scan now leaves such a sub-select to PostgreSQL's plan, which asks the derived table once for each outer row. Plans of other queries, and their speed, are those of 4.3.1. Held by the regression test agg_correlated_levels.

Who is affected and what to do

• Update (the steps, in this order): install the files and restart the server (a standby too), because a backend that already loaded the old library keeps it until its connection ends; then ALTER EXTENSION pg_sift UPDATE in each database (4.3.1 to 4.3.2 runs an empty script: no object changes; nothing to REINDEX, no format change). A database at 4.3 or 4.3.1 goes through the earlier scripts by itself.
• Affected: a query of the shape above that the aggregate scan plans (EXPLAIN shows a SiftAgg node). Nothing stored is wrong; only the answer of such a query was.
• If you cannot update yet: SET sift.enable_agg = off for the session or the transaction of such a query (or ALTER ROLE .. SET) makes PostgreSQL's own plan answer it; the other queries of that session lose the aggregate scan until the setting is on again.
• Going back: install the 4.3.0 or 4.3.1 files and restart after ALTER EXTENSION pg_sift UPDATE TO '4.3', which runs the empty script back to 4.3 (4.3.1 itself is reached by UPDATE TO '4.3.1' from there). The cause of the wrong answer is back with those libraries.

Compatibility

• PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A replacement of 4.3.1 with the install and a restart; extension SQL 4.3.2 (pg_sift--4.3.1--4.3.2.sql, and an empty pg_sift--4.3.2--4.3.sql back); no column format minor and no BM25 metapage minor changes.

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.3.2-1_amd64.deb (_arm64.deb on arm64)
• RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.3.2-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.3.2-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
• Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.3.2 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.3.2 release: no change to the SQL interface or the shared format.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.3.2-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.3.2 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.3.2 release: no change to the SQL files.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.3.2-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.

pg_sift 4.3.1

pg_sift 4.3.1: a patch release of 4.3.0 that fixes a wrong answer of sift_col indexes that track their changes, present since 4.0.0: a read made while a DELETE, UPDATE of the key column or MERGE was running (from an AFTER ROW trigger, an AFTER STATEMENT trigger that fires before the index's own, or a function the statement calls) counted the rows that statement had just ended. It also fixes every DROP failing on a server with a FOR ALL TABLES publication (4.2.0 to 4.3.0), a warning of the parallel VACUUM of a sift (BM25) index and a rare second load of a shared lookup table; extension SQL 4.3.1 (one statement, no new object, no format change): install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database.

Highlights

• Fixed: a read inside a statement that deletes rows counted the rows it had deleted (4.0.0 to 4.3.0; sift_col with track_changes, which is the default). An index with track_changes hears the versions a statement ends (the rows of a DELETE, the old versions of an UPDATE of the key column or of a MERGE or INSERT .. ON CONFLICT DO UPDATE) from its own statement triggers, which fire after the statement has run. A read of the same transaction that runs before them took the log for the whole truth and counted those versions: an AFTER ROW trigger, an AFTER STATEMENT trigger of the table whose name sorts before the index's own (sift_track_*), a function the statement calls (in a WHERE clause or a MERGE condition) and a BEFORE trigger of the same statement. The wrong answers were counts, sums and other aggregates, filtered counts and top-N reads that the index answers without the heap (a count of 50,000 rows against 47,500 in a statement trigger that had deleted 2,500). The next statement, an AFTER trigger with a later name and the commit were right, and no stored byte is wrong: nothing to rebuild. The index now reads the heap for a table while a statement that ends row versions of it runs. Held by the regression test col_track_own_statement_reads.
• Fixed: every DROP failed on a server with a publication that publishes deletes and covers the extension's tables (4.2.0 to 4.3.0). With CREATE PUBLICATION .. FOR ALL TABLES, any DROP of a relation (DROP INDEX, DROP TABLE, a plain table too) failed with cannot delete from table "table_option_values" because it does not have a replica identity and publishes deletes: the event trigger that removes the options of a dropped table deletes from sift.table_option_values, which had no replica identity. ALTER EXTENSION pg_sift UPDATE makes the unique key index of that table its replica identity (the one statement of the 4.3 to 4.3.1 script; a fresh install has it); the other tables of the extension have a primary key. A server without such a publication was never affected. Held by the TAP test publication_all_tables (the update from 4.2.0 and from 4.3.0, a fresh install, a subscriber).
• Fixed: a VACUUM that vacuums the b-trees of a table with parallel workers no longer warns for a sift (BM25) index (4.0.0 to 4.3.0). The merge of the segments by workers needs a transaction id before a parallel mode of its own, which the leader of that VACUUM cannot take: the step logged maintenance failed: cannot assign transaction IDs during a parallel operation, counted a failure in sift.workers and left the segments to a retry after a delay. One process merges them in the same VACUUM now. The answers were right in both. Held by the TAP test vacuum_parallel_merge.
• Fixed: sessions that run one query at once could load the rows of a joined table more than once (the shared lookup). A session that missed the shared rows and asked for the load's claim just after the loader had kept the rows and dropped its claim found no claim to wait for, and loaded the table again (the answers were right; the load was wasted work). It looks for the rows again once it holds the claim. Held by the TAP test lookup_shared_claim (PostgreSQL 18 and later built with injection points).

Who is affected and what to do

• Update (the steps, in this order): install the files and restart the server (a standby too), because a backend that already loaded the old library keeps it until its connection ends; then ALTER EXTENSION pg_sift UPDATE in each database (4.3 to 4.3.1 runs an empty script: no object changes; nothing to REINDEX, no format change). A database at 4.2.1 or 4.2.2 goes through 4.2 and 4.3 by itself.
• Affected by the failing DROP: a database that has the extension at 4.2.0 to 4.3.0 on a server with a publication that publishes deletes and covers the extension's tables. Until the update, ALTER TABLE sift.table_option_values REPLICA IDENTITY USING INDEX table_option_values_key (as a superuser or the owner) does what the update does.
• Affected by the wrong answer: a table with a sift_col index that tracks its changes (every index made with the default track_changes, since 4.0.0) whose AFTER ROW triggers, AFTER STATEMENT triggers or functions called from a DELETE, UPDATE or MERGE read the same table through an aggregate or a top-N. A table nothing reads from inside its own writing statements gives the same answers as before. Plans, speeds and answers outside this case are those of 4.3.0.
• If you cannot update yet: ALTER INDEX <index> SET (track_changes = 'off') on the indexes of the tables that are read that way makes those reads check the heap (the setting that keeps the index as it was before 4.0; the heap-free reads that track_changes gives are lost until ALTER INDEX <index> SET (track_changes = '<key column>') after the update). A trigger whose name sorts after the index's sift_track_* triggers and fires AFTER STATEMENT is not affected, but an AFTER ROW trigger or a function the statement calls cannot be ordered after them.
• Going back: install the 4.3.0 files and restart; ALTER EXTENSION pg_sift UPDATE TO '4.3' runs the empty script back to 4.3. The cause of the wrong answer is back with that library.

Compatibility

• PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A replacement of 4.3.0 with the install and a restart; extension SQL 4.3.1 (pg_sift--4.3--4.3.1.sql, and an empty pg_sift--4.3.1--4.3.sql back); no column format minor and no BM25 metapage minor changes.

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.3.1-1_amd64.deb (_arm64.deb on arm64)
• RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.3.1-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.3.1-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
• Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.3.1 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.3.1 release: no change to the SQL interface or the shared format.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.3.1-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.3.1 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.3.1 release: no change to the SQL files.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.3.1-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.

pg_sift 4.3.0

pg_sift 4.3.0: reads that order, page and list by a column take fewer instructions and blocks (top-N, OFFSET, ranges and IN lists by an order array), a serial id and the second order by a group column take less of the index, writes put fewer pages and less WAL in the index (the rows of a write buffer stay in shared memory), builds and merges keep to maintenance_work_mem, and a sift_col index can be connected to the index of the parent table it joins, so that EXISTS, NOT EXISTS and counts over a join are answered from the child and a left join groups the child first; extension SQL 4.3 (reached from 4.2, 4.2.1 and 4.2.2 in one step): install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database. A library before 4.3 refuses what 4.3 writes with its defaults (column format minor 44): update every node that reads an index before the primary writes (see "Who is affected and what to do").

Highlights

Figures are those CHANGELOG.md and docs/OPTIONS.md state, with the rows of the table they were measured on, as before then after. Instructions are those of one backend, minimum of several runs; a b-tree figure is given where one was measured.

Reads and order arrays

• Faster: top-N, OFFSET, ranges and IN lists by an order array. A chunk of an order array may be seekable (a fence key every 32 entries), so a seek or a scan decodes 32 entries and not 512; a walk keeps the handles of the segments for the backend's later statements and decodes only the addresses it consumes. 5,000,000 rows, a plain array on a timestamp: top-N descending 1.38 M to 0.88 M instructions, OFFSET ascending 1.03 M to 0.87 M, a range 2.47 M to 1.37 M, an IN list 3.05 M to 2.56 M. A range with both bounds and LIMIT 20 seeks the far end of each segment's range only when the scan gets there (5,000,000 rows in 26 segments: 1.138 M to 0.898 M instructions and 149 to 64 blocks read cold; a b-tree 0.265 M and 23). A cold seek reads the header of an order block alone, reads the directory when a seek first needs it and starts no read-ahead after a lookup (the same table, blocks read cold: range 258 to 188, top-N 227 to 160, OFFSET 100 250 to 187; a b-tree 23, 23 and 123). An equality or an IN list of ids on an array decodes the addresses of its entries and not those of the whole chunk (200,000 rows: 64 keys 2.68 M to 1.47 M instructions, 16 keys 0.82 M to 0.54 M, one key 0.18 M to 0.16 M). The seekable form takes 9.33 bytes a row for 1,000,000 random keys where the plain one took 9.86 (a b-tree 9.31). An index built before keeps its chunks until a merge or REINDEX writes them again.
• Faster: a top-N, page or OFFSET of an integer or time key with an IN list on it. ORDER BY u LIMIT 100 with u IN (...) reads the entries of each value of the list one after another, a seek apiece, where it read a range from the first value to the last and tested every entry between (1.4 to 3.8 billion instructions for 4 to 16 values over 1,000,000 rows) or, past 16 values, the units (27 million instructions over 1,000,000 rows, 166 million over 5,000,000). 1,000,000 rows, LIMIT 100: lists of 4, 8, 16, 17 and 40 values take 0.549 M, 0.770 M, 1.22 M, 1.26 M and 2.46 M instructions (a b-tree 0.22 M to 0.94 M); 40 values over 5,000,000 rows 5.4 M (a b-tree 0.95 M). A list of more than 1,024 values still reads the units.
• Faster: reads over rows that were updated and not vacuumed. A top-N, OFFSET or range by an order array of an index that tracks its changes (track_changes, the default) asks the tombstone log about the heap page of each entry it takes and not about the whole superblock of each: with 30% of 1,000,000 rows updated and not vacuumed, OFFSET 100 took 9.19 M instructions with track_changes on and 0.87 M with it off, because the keys are spread over the whole heap and the masks read the tombstones of the whole table; the rows are the same. A read of a group array over a table with updated and vacuumed rows lists the dead rows of the units whose pages it reads and not of the whole segment: the top rows of one group take 0.88 M instructions and 230 buffers over 1,000,000 rows (6.6 M and 304 before) and 1.53 M and 480 over 5,000,000 (30.9 M and 1,078), with 30% of the rows updated and vacuumed.
• Faster: the second order by a group column (order_array_group). WHERE col = x ORDER BY ts LIMIT n on a table that has the b-tree of that shape no longer reads the units once the b-tree is replaced by the sift index. The build groups the second order by an integer column that a non-unique b-tree of the table runs the shape of (the column and a date or timestamp of the index as its first two key columns, in either order), as it grouped by an entity before; the NOTICE and sift.index_choices say whether a b-tree, an entity or the hint chose the group. 5,000,000 rows, an index of an id, a 100-value group, a timestamp and an amount: the b-tree on the group and the timestamp takes 0.33 M instructions and 24 blocks, the sift index without the group 4.69 M and 1,491, with it 0.89 M and 44. A group names the entries of a chunk by their position in the plain order of the column where the positions of a group are close, which makes the index grow +20.2% over the plain order where it grew +59.6% (the group by an integer of 100 values), +14.2% where +30.6% (1,000 values), for a build that takes 7.7% more instructions than the group by keys and addresses, and ORDER BY ts LIMIT and IN of the group read half the blocks or fewer. The table of groups of 1,024 groups or more and no NULL key takes about 4.4 bytes a group where it took 24: 93,000 values over 5,000,000 rows, the index is 65.0 MB (102.7 MB before, 39.5 MB without the group), the build 0.2% more instructions, a top-N of one group 5% more and an IN of two values 6% fewer, with 24 to 37% fewer blocks. A top-N by a group reads a compact chunk with 12% fewer instructions (35 segments of 5,000,000 rows: 1.609 M to 1.418 M). An index built before keeps its arrays until REINDEX.
• Changed: the choice of the second order prices what each segment writes. order_array_group = 'auto' prices the table of groups that every segment writes (24 bytes a group, about 4 from 1,024 groups with no NULL key in the segment) and not one table for the index, so an entity of many values with few rows apiece is no longer refused for a table it does not write (300,000 rows, 60,000 values: refused before, chosen now, the index grows 4.3 bytes a row), and an entity with NULL keys is refused where its tables would be the index. The choice is the same at 100, 1,000 and 93,000 values over 1,000,000 and 5,000,000 rows.
• Faster: planning an index with many segments reads fewer blocks. The header page of a segment keeps a copy of the small ranges that a plan and the first read open (the schema, the unit directory, the point directories, the order headers, the zone directory, the pre-aggregate and set headers), so a plan over 5,000,000 rows in 18 segments reads 81 index pages where it read 146, the first ordered range query 98 where 121 (116 where 193 with the index and heap evicted) and a top-N 75 where 106; the index grows by 0.26%, a warm statement runs 1 to 4% more instructions, and build, promotion, merge and VACUUM times do not change. The statistics of an index count the compact chunks from the headers alone, and the share of units without a histogram or a rollup is read only when the cost of a pre-aggregate answer needs it: the first statement of a new backend over 5,000,000 rows in 26 segments plans from 238 blocks where it read 416 (buffer hits 1,021 to 279), and over 1,000,000 rows from 81 where it read 112. A statement whose choice between plans is kept plans with 0.3 M fewer instructions: a left join of generated days plans in 977 k instructions with the child-first plan (it was 1,277 k).

Index size

• Smaller: a serial id is an order array, not a point directory. A nearly unique integer that follows the heap order (correlation 0.99 or more, no unique b-tree of its own) gets an order array chosen by the build, kept whole, and no point directory: runs of consecutive heap addresses take 0.4 bytes a row where the directory took 3.3. 5,000,000 rows, an index of an id, a group, a timestamp and an amount: 63.3 MB to 49.1 MB; 1,000,000 rows 9.0 MB to 6.1 MB. An equality on the id takes 0.46 M instructions and 5 cold blocks with the directory and 0.51 M and 2 with the array (a b-tree 0.12 M); the other classes are within 3%. A list of many ids costs more with the array (id IN of 64 keys over 1,000,000 rows: 3.7 M instructions in 94 cold blocks against 1.0 M in 155 with the directory), so ask for the directory with WITH (point_tid_columns = 'id') where the reads are long id lists. A table with a primary key keeps the directory of the key for the writes that supersede rows. An index built before keeps its directory until a merge decides again for the segments it writes, or REINDEX for all of them (docs/INDEX-RECIPES.md).
• Smaller: a build by ranges of the key for a table of one to three runs. A serial CREATE INDEX or REINDEX of a key_cut index on a table of more than one run (it needed more than three before) is built by ranges of the key, so a table of one to three runs no longer leaves runs that overlap in the key for the maintenance to rewrite after the build: the build writes the index once, and it is smaller.
• Changed: key_order = 'col' of one column builds no point directory. The directory made the index 71 to 95% larger (4.89 to 9.34 MB at 1,000,000 rows, 23.98 to 46.74 MB at 5,000,000) and made an IN list of 100 values 5.7 to 11.8 times cheaper (a b-tree is 1.7 to 3.8 times cheaper s...

pg_sift 4.2.2

pg_sift 4.2.2: a patch release of 4.2.1 that fixes a wrong answer of sift_table that is present since 4.0.0: a VACUUM of a table that waited for the table's maintenance lock could make the old versions of updated rows, or rows that were deleted, visible again. It adds sift.table_find_stale_versions() to find the copies of updated rows; extension SQL 4.2.2 (one new function, no format changes): install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database. Updating fixes the cause; it does not repair rows that already came back (see "If a sift_table may be affected").

Highlights

• Fixed: a VACUUM of a sift_table could make old versions of updated rows, or deleted rows, visible again (4.0.0 to 4.2.1; sift_table only). A VACUUM that waits for the table's maintenance lock went on with the list of segments it had read before the wait. When the holder of the lock promoted or merged write buffers in the meantime, the VACUUM did not mark the dead rows of the new column groups as removed, and then dropped the records of their deletion: every snapshot saw the old version of an updated row next to the new one, and the rows that a DELETE had removed. The VACUUM can be a manual one, autovacuum (also the one that prevents transaction ID wraparound), a pass of the background worker (sift.table_auto_defrag) or a second VACUUM of the same table; the holder can be a writer that closes a write buffer (sift.table_inline_promote), the worker, sift.table_maintain() or another VACUUM. Every maintenance entry now takes the lock and reads the list of segments in one step. sift_col and sift (BM25) indexes and the heap are not affected.
• Added: sift.table_find_stale_versions(tbl regclass, sample integer DEFAULT 10) returns one row: duplicate_keys, the number of primary key values that are on more than one row, and sample_keys, up to sample of them (text[], each key written as a row: (7), (3,k1)). It reads the table once (a sequential scan and a hash GROUP BY on the key, work_mem applies), takes only ACCESS SHARE, writes nothing and runs with the rights of the caller. It fails for a table without a primary key and returns 0 for a heap table; a partitioned table is read through all its partitions. It finds updated rows that came back; it cannot see deleted rows that came back.
• Fixed: assert builds stop where a merge of a sift index finds none of the segments it replaces in the directory (the maintenance lock and the claims rule it out). A build without assertions is unchanged.

Who is affected and what to do

• Update (the steps, in this order): install the files and restart the server (a standby too), because a backend that already loaded the old library keeps it until its connection ends; then ALTER EXTENSION pg_sift UPDATE in each database (4.2.1 to 4.2.2 adds the function; a database at 4.2 goes through 4.2.1 by itself; nothing to REINDEX, no format change).
• Everyone who has a sift_table that is updated or deleted from, and VACUUMed or maintained while it is written: update, then run the detector on each such table (below). A table that nothing ever updated or deleted from has no old versions to bring back.
• Reduce the exposure until the update is installed: sift.table_auto_defrag = off removes only the background worker's VACUUMs. A manual VACUUM, autovacuum (the anti-wraparound one cannot be disabled) and two VACUUMs of one table at once can still trigger it, and no maintenance setting removes the cause (it was reproduced with sift.table_auto_defrag = off and sift.async_maintenance = off). autovacuum_enabled = false on the table with no manual VACUUM avoids all but the anti-wraparound one, at the cost of dead space and stale statistics. VACUUM only while the table is not written, and never two at once.
• The error sift index "<name>" is corrupted ... expected page type/generation X, found Y was seen from VACUUM and sift.table_maintain() in the same race on 4.0.0 to 4.2.1: the call read a page of a segment that the lock holder had already released. The error aborts that VACUUM or sift.table_maintain() call and leaves the table and its indexes consistent (checked on a set of cases); run the call again after updating. It does not by itself mean that rows reappeared: sift.table_find_stale_versions() and the comparison with your source of truth are the check for that. The evidence is small and says what it says: three tables that raised the error in a test were examined seconds after it, and had the rows of a heap twin, no key on two rows, a clean check of the structure of its segments, and the same state after VACUUM, VACUUM (FREEZE) and sift.table_maintain(), with the B-tree indexes clean under amcheck (bt_index_check and bt_index_parents_check with heapallindexed); no REINDEX or rebuild was needed. A wrong answer is a different outcome of the same race, not a consequence of the error.

If a sift_table may be affected

The update does not repair the rows that already came back.

1. Detect updated rows that came back. SELECT * FROM sift.table_find_stale_versions('t') (the table name is an example). A count above 0 means primary key values on more than one row. On a table damaged in a test (100,000 rows in its heap twin, 100,795 in the table) it found 795 keys, and 0 on the heap twin. The function runs with sift.enable_agg, enable_indexscan, enable_bitmapscan and enable_indexonlyscan off for its own duration: with them on, the aggregate engine answers count(*) and GROUP BY as if the key were unique, and the primary key index no longer holds the old versions, so a plain GROUP BY pk HAVING count(*) > 1 finds nothing. A query of your own needs the same four as SET LOCAL.

2. Repair updated rows. The copy that was visible before the VACUUM is the one with the highest ctid (tuple identifiers are never reused and promotion and merge keep them), so one transaction rebuilds the table with that copy per key:

   BEGIN;
   LOCK TABLE t IN ACCESS EXCLUSIVE MODE;
   SET LOCAL sift.enable_agg = off;
   SET LOCAL enable_indexscan = off;
   SET LOCAL enable_bitmapscan = off;
   SET LOCAL enable_indexonlyscan = off;
   CREATE TABLE t_new (LIKE t INCLUDING ALL) USING sift_table;
   INSERT INTO t_new SELECT DISTINCT ON (pk) * FROM t ORDER BY pk, ctid DESC;
   ALTER TABLE t RENAME TO t_old;
   ALTER TABLE t_new RENAME TO t;
   COMMIT;

   It was checked against a heap twin on three data sets with several updates per row: no row differed. It holds only if the table was not rewritten since the copies appeared (VACUUM FULL and CLUSTER assign new ctids), and xmin cannot order the versions. If the table has a column that orders them (updated_at, a version number), order by it instead; otherwise choose the rows from SELECT *, ctid ... ORDER BY pk, ctid with the four settings. Foreign keys, views and grants move by hand, and the indexes and constraints of the copy are named after it.

3. Deleted rows that came back have no duplicate. SQL on the table alone cannot find them and the copy above does not remove them. Delete the keys you know were deleted again, or restore from a backup taken before the first VACUUM that could have been affected.

4. Going back: install the 4.2.1 files and restart; ALTER EXTENSION pg_sift UPDATE TO '4.2.1' runs the empty script back to 4.2 and then the 4.2.1 script. The function stays in the database and works with the 4.2.1 library; the cause of the wrong answer is back with that library.

• Nothing else changes: plans, speeds and answers outside the cases above are those of 4.2.1.

Compatibility

• PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A replacement of 4.2.1 with the install and a restart; extension SQL 4.2.2 (pg_sift--4.2.1--4.2.2.sql, and an empty pg_sift--4.2.2--4.2.sql back); no column format minor and no BM25 metapage minor changes.

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.2.2-1_amd64.deb (_arm64.deb on arm64)
• RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.2.2-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.2.2-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
• Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.2.2 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.2.2 release: no change to the SQL interface or the shared format.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.2.2-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.2.2 (`s...

pg_sift 4.2.1

pg_sift 4.2.1: a patch release of 4.2.0 with fixes for wrong answers and crashes that are older than 4.2.0 (rows of the transaction itself missing from a parallel scan, memory freed under the planner and the build, a scan of a sift_table that counted rows its snapshot had deleted), DROP INDEX CONCURRENTLY of a sift_col index with track_changes, reads that stay up while a concurrent build runs, builds and reads of tables of hundreds of millions of rows without the 1 GB allocation limit, a key_cut that picks the key whose lookups gain most, and cheaper joins to a lookup table; extension SQL 4.2.1, no format changes: install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database.

Highlights

• Fixed: a parallel read of a sift_col index missed the rows the transaction itself had inserted (3.6.0 to 4.2.0). Three paths: a parallel aggregate whose lookup join runs a child plan in the workers, a plan made before the rows were inserted (a prepared statement or a function) that scans the index under a Gather, and a parallel aggregate whose leader did not scan (parallel_leader_participation = off, or workers that were first to run). The rows wait in the backend until the index is read and a worker has none to flush; the statement now flushes them before it launches the workers.
• Fixed: code that held a string of an index's options across a lock or a catalog lookup read freed memory when the lookup reloaded the index's cache entry (a VACUUM (ANALYZE) or an ALTER INDEX of another session does that): a crash, or a wrong plan or answer. First affected release by site: the bm25 insert path, ranked scoring and the paths of a build from 3.4.2, the point column from 3.6.0, the planner check for a point lookup, the row key and the automatic key cut from 4.0.0, the choices of a build from 4.0.4 and 4.1.0. A crash seen in 4.2.0 could not be reproduced outside the test knob that provokes it. No stored data is corrupted by any of these; update is advised.
• Fixed: a scan of a sift_table counted rows its snapshot had deleted or updated when a VACUUM of the table invalidated its cache entry in the middle of the statement (serial and parallel scans, and the heap check of a BM25 index on the table). The scan now filters with the state it opened with.
• Fixed: DROP INDEX CONCURRENTLY of a sift_col index with track_changes (the default) failed with must be first action in transaction (4.0.0 to 4.2.0). The index now depends on its two triggers and the transaction that deletes the index removes them. ALTER EXTENSION pg_sift UPDATE converts the indexes of earlier releases (sift.col_track_convert(), safe to run again).
• Fixed: a CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY that had not finished made sift.workers, sift.col_index_status and the functions that open an index by name fail (invalid page layout, could not read blocks), on the primary and on a standby. The views list only indexes that are ready; a read of an index that is not ready is refused with a message.
• Fixed: builds and reads of very large tables no longer stop at 1 GB (invalid memory alloc request size). The choices CREATE INDEX makes hold no memory by rows, a segment is cut at the memory its point directories and order arrays take, the tombstone log of a sift_table opens and vacuums past 16.7 million entries, and the merge, the rewrite and VACUUM of a sift_col index hold the memory they were given, whatever the rows.
• Changed: the default key_cut cuts by the key whose lookups gain most, prices the zones every other leaf would lose, weighs a leaf that follows the order of the table by the rows it saves, and leaves a key alone when its statistics say the cut gains nothing; a column that is all NULL no longer blocks the cut of the other keys. sift.key_cut_decision reports what CREATE INDEX decides.
• Fixed: maintenance wrote an index twice. VACUUM with sift.col_auto_merge on no longer copies the segments down into the holes of the rewrite before the rewrite, a grown overlay of new rows in a key_cut index in partitions no longer rewrites the whole index each time, a held snapshot keeps the file within the live bytes plus one rewrite pass, and the free space of an index no rewrite is coming for goes back to the file system.
• Improved: joins to a lookup table. count(x) of a column of a joined table keeps a flag for each joined tuple (562 M instructions against 1,642 M for the normal plan, and 1.1 to 1.65 G before, on 1,000,000 child rows of 100,000 parents), lookup tables are priced as rows or as classes of their keys by their keys, a numeric lookup is cached, and the group table of a scan that cannot spill is bound by its groups. No change of the join code fixes a wrong answer of a released version.
• Changed: the tombstone log of a sift_table that one backend keeps in memory is bound by sift.tomb_log_limit, 8 GB by default: a read past it fails with 54000 naming the setting, and VACUUM, which is not bound by it, drops the entries of the rows it cleared.
• Changed: sift.col_sections lists the point directories (point_dir) and the global order arrays (order_arrays) in rows of their own; the rows still add up to pg_relation_size.

Who is affected and what to do

• Update (the steps, in this order): install the files and restart the server (a standby too), because a backend that already loaded the old library keeps it until its connection ends; then ALTER EXTENSION pg_sift UPDATE in each database (4.2 to 4.2.1; nothing to REINDEX). The update takes SHARE UPDATE EXCLUSIVE on each table it converts, so it waits for a concurrent drop or rebuild of an index of the table. It is no longer required before other DDL: only DROP INDEX CONCURRENTLY of an index made by 4.2.0 or earlier needs it. Until it runs, the triggers of such an index are found and its log is trusted, as before.
• If a build of a 4.2.1 development tree ever ran in a database, check for duplicate trigger pairs: SELECT tgrelid::regclass, count(*) FROM pg_trigger WHERE tgname LIKE 'sift_track_%' GROUP BY 1 (two per index with track_changes).
• Everyone who runs parallel queries over a sift_col index inside a transaction that inserts, uses prepared statements or functions that scan a sift_col index, or reads a sift_table while it is vacuumed: update.
• Known limits (new bounds, not new wrong answers): a ranked search of a sift index that has to hold every match (no LIMIT the search can take, or a filter after it) and sift.facets of a keyword index with very many distinct values fail with 54000 naming work_mem at the default 4 MB, from about 262,000 hits and about 70,000 distinct values (raise work_mem or add a LIMIT; spilling to a sort is planned); a BufFileSeek past 1 GB in the runs of an aggregate that spills and the rescans of the table by CREATE INDEX CONCURRENTLY remain known limits; parent LEFT JOIN child with an aggregate over the child's columns, with a sift_col index on both tables, runs as the normal plan or as a scan of the parent that loads the child as rows: index the child alone and the scan reads the child, several times faster.
• Going back: install the 4.2.0 files and restart; ALTER EXTENSION pg_sift UPDATE TO '4.2' runs the empty script back. An index converted by 4.2.1 has triggers that the 4.2.0 library does not find: REINDEX there makes them again (docs/UPGRADING.md).
• Known issue: the tap-shell test col_index_replica (test 7, a standby that answers the units of a merged segment from the exceptions) may fail on some runners: the standby plan can vet units by heap instead. The failure is in the test, fixed in 4.2.2; no data or answer is affected.
• Nothing else changes: plans, speeds and answers outside the cases above are those of 4.2.0, apart from the joins and the key_cut choices above.

Compatibility

• PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A replacement of 4.2.0 with the install and a restart; extension SQL 4.2.1 (pg_sift--4.2--4.2.1.sql, and an empty pg_sift--4.2.1--4.2.sql back); no column format minor and no BM25 metapage minor changes.

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.2.1-1_amd64.deb (_arm64.deb on arm64)
• RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.2.1-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.2.1-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
• Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.2.1 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.2.1 release: no change to the SQL interface or the shared format.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.2.1-linux-x86_64.tar.gz (below, covered by SHA256SUMS with the packages), unpack it and .load ./sift from the sqlite3
shell (`s...

pg_sift 4.2.0

pg_sift 4.2.0: the aggregate scan answers more of what an application runs (jsonb presence, containment and key tests, HAVING with sub-selects, FETCH FIRST .. WITH TIES, array and range tests) and is chosen more often, VACUUM gives the free blocks of a sift_col index back online, and ten changes are worth reading before the update; extension SQL 4.2 (update chain 4.0 -> 4.0.3 -> 4.1 -> 4.2): install the files, run the preflight scripts/upgrade-check.sh, restart the server (a standby too), then run ALTER EXTENSION pg_sift UPDATE in each database.

Highlights

The figures are those CHANGELOG.md states for this release, with their cases and tests, as the normal plan (PostgreSQL's plan without the scan) then the scan, or as before then after.

• Faster: jsonb tests on the index (NOT, NULL, containment and key forms). NOT (doc @> '{"b": "x"}'), (doc @> ..) IS FALSE, NOT (doc ? 'k'), (a AND b) IS FALSE and FILTER (WHERE NOT (a OR b)) of containments, the same in a CASE, and doc IS [NOT] NULL of a jsonb column that may be NULL are answered by the index, where they ran on the normal plan. The index keeps a boolean column for each jsonb column with paths that may be NULL (written by CREATE INDEX and REINDEX; an index built before it has none and keeps the normal plan for those forms until REINDEX). 150,000 documents, 1% to 50% of them without a declared member: NOT of a containment 85 M to 121 M cycles down to 7 M to 10 M, the count of doc IS NULL 35 M to 1.9 M; it costs 2.0% of the index of 1,000,000 rows after a merge and 3.2% of its build (insert +0.4%, promotion +4.6%, merge +5.0%). A containment is also answered as IS NOT TRUE, in a CASE pivot, in a FILTER and over an AND / OR with other conditions (M cycles of one backend: WHERE (doc @> ..) IS NOT TRUE 69.9 to 3.5, a CASE pivot by group 138.3 to 8.9, FILTER (WHERE (doc @> ..) IS NOT TRUE) 133.1 to 8.0). doc ? 'k', doc ?| array[..] and doc ?& array[..] are answered in a FILTER, an OR or a CASE too, and the value of a key read beside its test (sum((doc ->> 'k')::float8) FILTER (WHERE doc ? 'k')) as well: attrs ? 'a' AND dim_a_id < 500 over 300,000 documents of 400 bytes takes 15.4 M instructions and 1.1 ms where the normal plan takes 250 M instructions and 14,252 blocks. A segment written before 4.2 does not say whether a document that is a string was a missing key, so a table of old segments stays on the normal plan for doc ? 'k' until VACUUM flags them (about 140 ms for 600,000 documents of 400 bytes). A containment with a boolean, a JSON null or a string that reads as a boolean or a number as a member ({"flag": true}, {"k": null}) is answered too, and a containment or ? test that names a path the index does not declare keeps the normal plan under the new reason jsonb_path_not_declared, which names the path.
• Faster: the planner chooses the aggregate scan more often. For a grouped aggregate of one table (no lookup join, no search, no top-N) whose normal plan is a sequential scan and a hash of the groups, the scan's own work per row is now counted in the units of the plan it is compared with (PostgreSQL's estimate of that plan runs at about 210 cost units a millisecond, the scan's price at 833), so a scan that was up to 4 times faster is no longer refused as cost when its rows are not few: max(ends - starts) by tenant over 150,000 rows 18.6 to 7.1, over 18,750 rows 2.7 to 1.4, COALESCE measures over 20,000 events 3.4 to 2.1, NOT lower_inc(r) 0.25 to 0.04 (milliseconds of the statement); a statement the scan does not win stays on the normal plan. A grouped aggregate with lookup joins (facts LEFT JOIN links ON links.parent_id = facts.dim_id GROUP BY ..) takes the scan more often when the normal plan reads more than 2,000 cost units from the scanned table: on 250,000 rows of a sift_table in a tuned profile (seq_page_cost 100, 8 workers, 4 GB work_mem) 193.6 to 107.9 ms and 361,019 to 851 buffers, and no statement of the 132 plan shapes in 8 settings gets slower. The scan prices an expression that PostgreSQL evaluates (max(ends - starts)) at 21 ns for each tuple read and 69 ns for each distinct combination of its columns, so its estimate is about 3 times higher on 150,000 rows of 10 distinct pairs (291 to 907 units for 3.1 ms). The price of the normal plan that probes the keys of a lookup is multiplied by the whole skew of the key column, where it was multiplied by its square root: a statement of 12 hot keys of a column of skew 7 was refused with cost and took 282 million cycles, 42 ms, with the normal plan against 8.2 million, 3.4 ms, with the scan. A NOT EXISTS (or EXISTS, or a scalar sub-select) over a table with a condition that its index does not take is planned by what the child costs for every row the scan reads, where a probe priced at a hundredth of what it takes was chosen and ran several times longer than the normal plan. The heap fetch of a top-N is priced at a page of the b-tree plan with a LIMIT, so a list of rows of one entity (ORDER BY .. LIMIT n over WHERE entity = k) keeps the b-tree plan on (entity, key) where that plan is faster: the scan took 8 of 18 such lists measured on 600,000 rows and 11 of 12 on 200,000, at 1.7 to 2.4 times the instructions of the b-tree plan, and all 30 are the b-tree's now (1.09 million instructions and 0.09 ms against 1.88 million and 0.16 to 0.25 ms of the scan); the lists the b-tree cannot give in order keep the scan, which takes 4 to 80 times fewer instructions. The direct probe of a correlated LIMIT 1 child is priced at 55 operators where it was 800, so the probes win up to a fifth of the rows of the table as keys: of 60 choices between them 21 were wrong by instructions (1,470 million lost) and none are now.
• Faster: HAVING column IN (SELECT ..) and EXISTS (SELECT ..), sub-selects of the groups, joins to small tables and lookups. In a plain GROUP BY such a HAVING condition is always answered as a filter of the rows, as a semi join the scan runs, where PostgreSQL keeps it above the aggregate and the sub-select ran for each group (GROUP BY kind_id HAVING kind_id IN (SELECT id FROM kinds WHERE enabled): 300,000 rows, 1,000 keys, 100 of them in the list, 40.1 to 0.58 ms and 342 to 5.7 M instructions); the rows form was the cheaper in all 40 cells measured, at 0.09 to 0.78 of the cycles. A sub-select in the select list or the HAVING that reads the group keys or the aggregates is evaluated by the scan once for each group (300,000 rows and 1,000 groups: 36.8 to 1.85 ms and 350 to 23.4 M instructions). NOT IN, NOT EXISTS, an expression or a cast of the key, a correlated IN, grouping sets and a HAVING without GROUP BY keep their HAVING. A table left-joined to a join of two small tables (FROM kinds k CROSS JOIN regions r LEFT JOIN events e ON ..: every kind and region with its count, zero for the pairs of no event) runs in the scan for count, sum of integers and numerics, min and max, and count(e) and count(e.*) of the whole row of the scanned table are answered as count(*). The table of a lookup join that has its own bounds (dim_id IN (SELECT id FROM dims WHERE id <= 2000)) is read by an index scan with the bounds as its keys: a lookup table of 200,000 rows read for id <= 2000 loads in 0.24 ms for 2,000 rows read (was 10 ms for the whole table), and a grouped statement over 300,000 rows that keeps those ids runs in 16.8 M cycles (was 39.1 M). The direct probe of a correlated child (LEFT JOIN LATERAL (.. LIMIT 1)) over a uuid, a date, a timestamp or a timestamptz key compares it in the probe itself: 40,000 probes into a lookup table of 300,000 rows take 585 M instructions for a uuid key where they took 756 M, 247 M for a date key where they took 347 M and 261 M for a timestamptz key where they took 360 M; over a key of several columns 452 M where 549 M.
• Faster: top-N, ties and ranks. A correlated min() or max() sub-select of a table ((SELECT max(ts) FROM events WHERE entity = e.id) in a select list or a condition) is the first row of its entity in the order of the column, where it was a pass over the rows of the entity for each row of the query around it, or the table grouped and joined: it is planned a second time as ORDER BY ts DESC LIMIT 1 over the non-NULL values, and the planner keeps it where it runs in the scan and costs less; the rows are those of the normal plan, NULLs, empty entities and NULL keys included. FETCH FIRST n ROWS WITH TIES by integer, date or timestamp columns of the index is a top-N of the scan, which gives the n best rows and every row that ties with the n-th: ORDER BY dim_a_id DESC FETCH FIRST 10 ROWS WITH TIES over 300,000 rows takes 10.3 M instructions and 0.94 ms where the normal plan takes 387 M and 44.2 ms. rank() OVER (PARTITION BY k ORDER BY o) <= N and dense_rank() .. (N up to 1,000, N = 1 included) keep in the scan the rows of rank <= N of each group, and SELECT .. FROM (SELECT .., row_number() OVER (PARTITION BY k ORDER BY o) rn FROM t) s WHERE rn <= N (N > 1, and up to 1,000 rows of each group) keeps only the first N rows of each group as the rows pass, where the plan sorted every row; the rows are those of the window. The rows of a top-N per group whose first sort key is a plain column are dropped on that key alone once they come after the last kept one of their group.
• Faster: array tests and CREATE INDEX on array columns. NOT (tags && ARRAY[..]), NOT (tags @> ARRAY[..]) and NOT (tags <@ ARRAY[..]) over an array column the index holds run on the index (21 of 30 statements of the matrix of negations ran on the index before, all 30 now), with the same rows as the normal plan over NULL and empty arrays, NULL elements, duplicates and multidimensional arrays. CREATE INDEX on a table with an array colum...

pg_sift 4.1.4

pg_sift 4.1.4: a patch release of 4.1.3 that fixes two wrong answers of the aggregate scan, both present in earlier releases: NOT (doc @> '...') on a NOT NULL jsonb column returned the rows that match instead of the rows that do not, and a sift_col index scan that PostgreSQL runs again for each row of an outer query returned some rows twice. The library only: install the files and restart the server; no ALTER EXTENSION, no REINDEX, no format change.

Highlights

• Fixed: NOT (doc @> '{"b": "x"}') returned the rows that match instead of the rows that do not (every release since 3.6.0, reproduced on 4.1.3). On a jsonb column with a NOT NULL constraint whose sift_col index runs a text member path of the document as an equality of a text leaf (the index declares doc.b), the aggregate scan answered NOT of the containment, or NOT of the path test, by flipping a test it did not know: a statement with that NOT in WHERE or in FILTER (WHERE NOT ..) counted the matching documents (in the test, 2,668 of 6,000 instead of 3,332). A nullable column, and every other test (an IN list, an array element test, a NULL test), were right. Only an IN list is flipped now, the element test and the NULL test keep their own flip, and any other NOT keeps the normal plan. Held by agg_jsonb_not_exist (13 tests, each as NOT, NOT with another condition and FILTER, on a NOT NULL and on a nullable column).
• Fixed: a sift_col index scan run again for each row of an outer query returned some rows twice (present in every release since 4.0.0, reproduced on 4.0.0, 4.0.4, 4.1.0 and 4.1.3). When PostgreSQL runs an index scan again for each row of an outer query (the inner side of a nested loop, a LATERAL join, a correlated subquery) and the index holds rows in its write buffer (after an INSERT or UPDATE, before the merge), a scan with an equality condition on a plain column could return rows of the buffer twice: the lookup keeps more than a quarter of the buffer rows in one scan and few rows in a later one, and the rows the first scan marked were not cleared for the next (in the test, 242 rows returned where 240 are right). The fault came in with the change that keeps the source of an index scan that starts over; 3.x reopened the source on every rescan and was right. Plain rows from the scan are duplicated, so counts and sums computed over them are too large. Each row is returned once now. Held by col_wbuf_lookup_rescan.

Who is affected and what to do

• A NOT NULL jsonb column with a sift_col index that declares a text member path (paths = 'doc.b text', or the path the index chose), and a statement that negates a containment or a path test of it (WHERE NOT (doc @> '{"b": "x"}'), FILTER (WHERE NOT ..)): update. Until then, run such statements with SET sift.enable_agg = off (it gives up the aggregate scan for them), or use an index that does not declare the path, which was right.
• A sift_col index with rows in its write buffer that is read through an index scan on the inner side of a rescanning join (a nested loop, LATERAL, a correlated subquery): update. Until you update: SET sift.enable_col_scan = off for the statement (verified on 4.0.4 and 4.1.3); sift.enable_agg = off and a VACUUM do not help.
• Update (the steps, in this order): install the files and restart the server (a standby too), because a backend that already loaded the old library keeps it until its connection ends. There is nothing to update in the database: the extension SQL stays at 4.1, so no ALTER EXTENSION pg_sift UPDATE and no REINDEX; no format change, going back needs nothing first.
• Still as before: after pg_upgrade (or a dump restore with triggers disabled) the answers are right but the reads of a tracked sift_col index check the heap until REINDEX INDEX <index> or ALTER INDEX <index> SET (track_changes = '<value>') is run once for each tracked index (an automatic repair is planned for 4.2).
• Nothing else changes: plans, speeds and answers outside the cases above are those of 4.1.3.

Tests

agg_jsonb_not_exist (78 statements: 13 tests x 3 forms x a NOT NULL and a nullable column; 9 returned other rows before the change) and col_wbuf_lookup_rescan, each failing before the fix; the regression suite on PostgreSQL 18, the upgrade checks (pg_upgrade, upgrade_check, the library of 4.1.3 reading what this one writes) and the TAP tests of the maintenance workers.

Compatibility

• PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A replacement of 4.1.3, 4.1.2, 4.1.1, 4.1.0, 4.0.x with the install and a restart: extension SQL 4.1 (no change), no column format change, BM25 metapage minors 1.0 to 1.5. The SQLite and MariaDB extensions: no change since 4.0.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.1.4-1_amd64.deb (_arm64.deb on arm64)
• RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.1.4-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.1.4-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
• Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.1.4 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.1.4 release: no change to the SQL interface or the shared format.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.1.4-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.1.4 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.1.4 release: no change to the SQL files.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.1.4-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.

pg_sift 4.1.3

pg_sift 4.1.3: a patch release of 4.1.2 that makes REINDEX INDEX CONCURRENTLY of a sift_col index with track_changes concurrent: it took an ACCESS EXCLUSIVE lock on the table at its end (every release since 4.0.0, errors and a stall, never a wrong answer).

Highlights

• Fixed: REINDEX INDEX CONCURRENTLY of a tracked index stalled the writers of the table and deadlocked with them. When the old index was dropped, the two triggers that the copy had made for itself went with it, and DROP TRIGGER takes ACCESS EXCLUSIVE on the table: the writers queued behind it, and a writer that held a lock on the old index while it waited for the new one deadlocked with the swap (deadlock detected; the transaction of the writer failed). In a test with 8 clients (an update, a delete and an insert-on-conflict in each transaction) and a loop of reindexes, 40 s: 4 reindexes done, 270 deadlocks, 1.4% of the transactions failed, 400 transactions a second, against 650 reindexes, no deadlock and 32,900 a second for a btree index. The copy now creates no triggers: the two of the old index move to it at the swap, and dropping the old index drops nothing of the table (ShareUpdateExclusiveLock only): 587 to 655 reindexes, no deadlock, no failed transaction, 25,500 to 26,200 transactions a second. A reindex cancelled in the middle leaves an invalid copy that owns no trigger.

Who is affected and what to do

• Everyone who runs REINDEX INDEX CONCURRENTLY on a sift_col index with track_changes (automatic for a table with a primary key) on a table that is written meanwhile: update. Until then: ALTER INDEX i SET (track_changes = 'off') before the reindex (0 deadlocks, 634 to 663 reindexes in the same test) and ALTER INDEX i SET (track_changes = 'id') after it (the key column of the table if it is not id; a pass over the heap, about 0.1 s for 600,000 rows).
• Nothing changes for a plain REINDEX, CREATE INDEX CONCURRENTLY (tested under the same writers: no deadlock, no failed transaction) or a table that nobody writes.
• No format change, no extension update: install the files and restart the server.

Known issues

• The log of the copy of a concurrent reindex is not complete (writers run beside the build), as before: the reads check the heap and answer as it does until REINDEX INDEX (without CONCURRENTLY).
• DROP INDEX CONCURRENTLY of a tracked index still fails (must be first action in transaction: PostgreSQL drops the triggers of the index first), and so does pg_repack --only-indexes, which ends with it: ALTER INDEX i SET (track_changes = 'off') first.

Tests

col_track_reindex_concurrently (regress: the copy of the reindex has no triggers of its own, the old index's two move to it, a reindex cancelled in the middle leaves an invalid copy with no trigger) and col_reindex_concurrent (TAP: writers update, delete and upsert while a loop reindexes; no deadlock, no failed transaction; it fails on 4.1.2), and the whole suite of 4.1.2 on PostgreSQL 17, 18 and 19.

Compatibility

• PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A replacement of 4.1.2, 4.1.1, 4.1.0, 4.0.3, 4.0.2, 4.0.1 and 4.0.0 with the install and a restart: extension SQL 4.1 (no change of an object since 4.1.0); column format minors 3 to 42 are read and written as in 4.0.0, BM25 metapage minors 1.0 to 1.5. The SQLite and MariaDB extensions: no change since 4.0.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.1.3-1_amd64.deb (_arm64.deb on arm64)
• RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.1.3-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.1.3-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
• Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 4.1.3 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.1.3 release: no change to the SQL interface or the shared format.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.1.3-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.1.3 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.1.3 release: no change to the SQL files.

• No change: the changes of this release are in the PostgreSQL column index, which is 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.1.3-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.