pg_sift 1.3.1: fixes CREATE INDEX failing on large tables with a small maintenance_work_mem. Install the new files; no ALTER EXTENSION step, no REINDEX and no on-disk format change.

Highlights

  • Any table builds at any maintenance_work_mem. A parallel CREATE INDEX on a large table failed with sift parallel build worker needs more than 256 runs (and a serial one with would need more than 128 segments) when the memory budget was small for the table. The build now merges its runs in passes, like an external sort, so the budget only decides how fast it goes (down to 1MB), in parallel or not. When a small budget would need more than 128 segments the build writes fewer, larger ones. The index answers and scores exactly as one built with more memory. Builds at the default or a generous budget take as long as before; at 1MB they take up to 1.5x longer.
  • No workaround needed. Setting max_parallel_maintenance_workers = 0 or raising maintenance_work_mem is no longer required to build or rebuild an index on a big table.

Compatibility

  • PostgreSQL 17 and 18; PostgreSQL 19 is experimental (CI builds it from git on every push). No on-disk format change; existing indexes keep working.

Packages

x86-64 packages for PostgreSQL 18 are attached below (SHA256SUMS covers every asset); other versions build from source with make USE_PGXS=1.

  • Debian 13, Ubuntu 24.04 (PGDG postgresql-18): sudo apt install ./postgresql-18-pg-sift_1.3.1-1_amd64.deb
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresql18-server): sudo dnf install ./pg_sift_18-1.3.1-1.el9.x86_64.rpm
  • Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 1.3.1 (sift, a loadable extension; a separate component from the PostgreSQL extension above). No change to the extension; it is released with the PostgreSQL one.

  • 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: nothing to upgrade from 1.3.0.

Download sift-sqlite-1.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.