pg_sift 4.0.1: fixes for five problems of track_changes, the log that lets a read of a sift_col index skip the heap. Two silent wrong answers of grouped reads are closed (a table attached as a partition or inheritance child after its index was built; a primary key dropped and added again), a dump of a table with a primary key and a default sift_col index restores and upgrades, and REINDEX CONCURRENTLY and pg_repack no longer break the table’s writes or the log. A patch release of 4.0.0: the library changes, nothing on disk and no SQL.

Known issue

  • 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>. On 4.0.1, 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>.

Highlights

  • Fixed: wrong grouped reads of a table attached as a partition or made an inheritance child. For a table whose sift_col index tracks its changes and that is attached afterwards (ATTACH PARTITION, INHERIT), a statement on the parent fires none of the table’s statement triggers, so the deletes and key changes it made were not in the log, yet a read took the log for complete and a grouped read counted rows that were gone (in a test, 10 of 10 groups differed from the heap, whose answer was right). A read now leaves the log, and the heap answers, while the table is a partition or a child, as it already did for a subscription target. INSERT and statements on the child itself were not affected in 4.0.0.
  • Fixed: wrong grouped reads after a primary key was dropped and added again. With the key dropped, the DELETEs and UPDATEs that ran were not in the log, and ADD PRIMARY KEY left the log marked complete: a grouped read counted the deleted rows (10 of 10 groups). A statement that finds no unique index to look its rows up by now ends the completeness of the log, so a read checks the heap until REINDEX INDEX <index> makes the log whole again.
  • Fixed: a dump of a table with a primary key and a default sift_col index could not be restored reliably, and pg_upgrade failed. The default track_changes = 'auto' is stored as the name of the key (track_changes = id), and CREATE INDEX refused it with column "id" has no unique index of its own when the index was made before the key, which pg_restore -j does for most tables and the restore of pg_upgrade did for all of them (pg_restore -j8 failed 19 to 21 of 24 CREATE INDEX). CREATE INDEX and REINDEX now accept an explicit key column that has no unique index yet, with a NOTICE; ALTER INDEX ... SET keeps the error, and a column that does not exist, is not in the index or can be NULL is still an error at CREATE INDEX. Reads take the log only when a valid unique index on that column exists, and check the heap until then. A dump made by 4.0.0 needs nothing: the fix is in the library that restores it.
  • Fixed: UPDATE and DELETE failed after a concurrent reindex (could not open relation with OID n). REINDEX INDEX CONCURRENTLY or REINDEX TABLE CONCURRENTLY of a sift_col index with track_changes left the two statement triggers with the oid of the dropped index, so every UPDATE and DELETE of the table failed afterwards. A trigger now serves the index it depends on, and the swap moves that dependency to the new index. INSERT, reads, aggregates and the data were not affected and nothing was damaged.
  • Fixed: after pg_repack of a table, the log of its index was off until the next REINDEX. sift.col_tracking said the triggers and the heap-free reads were off, so reads of rows changed and not yet vacuumed went to the heap. The index built on the copy now takes the identities of the table’s own triggers, which pg_repack leaves in place, so the log is complete at the swap.

Who is affected and what to do

  • Partitions and inheritance children: a sift_col index with track_changes on a table attached (ATTACH PARTITION, INHERIT) after the index was created, on 4.0.0 (an index created after the attach is untracked and was not affected). With 4.0.1 the answers are right while the table is a child. Known limit (fixed in 4.0.2 for tables attached after that update): after DETACH PARTITION or NO INHERIT of a table that was attached and written through its parent, the log is stale until REINDEX INDEX <index>: run it after the detach (it is also the cure for a table written through its parent on 4.0.0, and for any table in doubt).
  • A primary key that was dropped and added again on a table with a tracked index, on 4.0.0: run REINDEX INDEX <index> once after installing 4.0.1 for each such table (or for every table whose key was ever dropped); nothing else is needed after that.
  • Dump, pg_restore and pg_upgrade: a database on 4.0.0 with a table that has a primary key and a default sift_col index. With 4.0.1 installed a dump made by 4.0.0 restores as it is (an index made before its key gets a NOTICE and reads check the heap until the key exists). On 4.0.0, ALTER INDEX <index> SET (track_changes = 'off') before the dump or pg_upgrade and ALTER INDEX <index> SET (track_changes = 'id') after.
  • Concurrent reindex: repair a table that already failed (on 4.0.0, or after installing 4.0.1 on a table that ran the command before) with REINDEX INDEX <index> (the plain form), or ALTER INDEX <index> SET (track_changes = 'off') and then ALTER INDEX <index> SET (track_changes = 'auto'). After a concurrent build the log of the new index is not complete, as for CREATE INDEX CONCURRENTLY: a read takes the log only where it is complete, and sift.col_tracking(index) says whether it is.
  • What 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. After REINDEX CONCURRENTLY, pg_restore --disable-triggers, or ENABLE TRIGGER ALL after DISABLE TRIGGER ALL, the heap-free reads of the table stay off (reads go to the heap: slower, not wrong) until a plain REINDEX INDEX <index>.
  • Update: install the files and restart the server (a standby too), or reconnect every pool: only the library changes, and a backend that already loaded the old library keeps it until its connection ends. No format change, no ALTER EXTENSION UPDATE step.
  • Nothing else changes: plans, speeds and answers outside the cases above are those of 4.0.0.

Tests

col_track_attach (partition and inheritance: ten groups differed before the fix), col_track_restore (restore with the index before the key: pg_restore -j8 in five of five runs with no error, pg_dump | psql, a dump with the indexes before the keys), col_pg_repack, pg_upgrade (a tracked index, PostgreSQL 18 to 18: 25 checks) and the new block of col_track_deletes (REINDEX INDEX CONCURRENTLY, then an UPDATE and a DELETE). col_heapfree_replication passes on a physical standby, after a promote and on a logical subscriber. The full matrix ran on both trees, 78 cells each, with no wrong answer.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A drop-in replacement of 4.0.0: extension SQL 4.0 (ALTER EXTENSION pg_sift UPDATE has nothing to do and there is no pg_sift--4.0--4.0.1.sql); 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.0.1-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.0.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.0.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.0.1 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 4.0.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.0.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.0.1 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.0.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.0.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.