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.