pg_sift 3.6.3: REINDEX INDEX CONCURRENTLY and CREATE INDEX CONCURRENTLY of a sift_col index with key_cut give the index a file of its live size also while a physical standby streams (a space fix, no wrong result). Update with the files of this release and restart the server; no ALTER EXTENSION step, no REINDEX.

Highlights

  • Fixed: the file of a concurrently built key_cut index kept the free blocks of the build while a standby streams. The build cuts the file behind the segments it merged, and it did so only under an AccessExclusiveLock, which a standby replays by waiting for every query of the standby that planned on the table since the index entered the catalog; so while a physical standby streams, the cut was skipped and the file stayed with the blocks that the participants' segments left free (1,000,000 rows, 4 workers: 7.6 MB for 2.5 MB of live segments, 3.0 times; now 2.5 MB). The new index of a concurrent build is not ready until its build ends, so no query reads it and no insert reaches it: the cut takes an ExclusiveLock, which a standby does not replay. The non-concurrent CREATE INDEX and REINDEX already cut the file (their new file is not visible either). A VACUUM of an index that is ready still does not cut its file while a standby streams: that is unchanged. Test: the TAP test col_build_standby_space (2 more checks: REINDEX INDEX CONCURRENTLY leaves the file at most 1.1 times its live bytes and nothing to rewrite).

Who is affected and what to do

Affected: a sift_col index with key_cut built by CREATE INDEX CONCURRENTLY or REINDEX INDEX CONCURRENTLY on a primary that has a physical standby streaming at the time of the build, on 3.6.1 or 3.6.2 (the gap the build reserves came with 3.6.1; earlier releases were not measured for this case). The effect is disk space only (the index is valid and every answer is right): a file up to 3 times its live bytes on the test table.

How to detect: compare the file with the live bytes of the index, SELECT pg_relation_size('idx'), (SELECT bytes FROM sift.col_index_info('idx')): a ratio well above 1.1 after a concurrent build with a standby streaming is this case.

To get the space back on 3.6.2: a non-concurrent REINDEX INDEX idx cuts the file (it takes a lock that blocks writes while it runs), or update and run REINDEX INDEX CONCURRENTLY idx again.

  • No format change, no REINDEX: the stored index is valid. Install the files and restart the server (a standby too), or reconnect every pool: a backend that already loaded the old library keeps it until its connection ends.
  • Nothing else changes: plans, answers and formats are those of 3.6.2.

Regression tests

The TAP test col_build_standby_space fails on a build of the tag v3.6.2 and passes on this release (table: docs/releases/3.6.3-REGRESSIONS.md); PostgreSQL 17, 18 and 19 are those of the quick-tier run of the release.

Two quick-tier results of this branch are not defects of the release:

  • col_build_standby_space, the bound of the file (PostgreSQL 17, one red in seven runs). One quick run failed the check “the file is at most 1.1 times its live bytes” after a REINDEX with a gap of 0 blocks: the file was 1,466,368 bytes against 1,318,912 live (1.112, 18 blocks over; the bound is 1.1). The test index is 160 blocks, and the blocks above the live bytes (metadata, the free list, the few free blocks a merge leaves) are 3 to 13 blocks whatever the size; 6 serial runs of the test on PostgreSQL 17.11 passed, 3 on this branch and 3 on v3.6.2, so it is not a regression. Since the bound has an absolute allowance of 32 blocks (256 kB) next to the 10%: it is there to catch a file that doubles.
  • leakcheck_gate on macOS arm64 (one flake). The job failed once in the second make check and passed in the rerun of the failed job alone; its cause, the cluster check that lands while a client’s backend still holds its temporary-table locks, is fixed by 0c81bb74c, which this branch has.

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 3.6.2: column format minors 3 to 32 are read, extension SQL 3.6 (ALTER EXTENSION pg_sift UPDATE has nothing to do and there is no pg_sift--3.6--3.6.3.sql), no setting changes its default. The SQLite and MariaDB extensions: no change since 3.6.2.

Packages

16 packages are attached below (SHA256SUMS covers every asset): x86-64 and arm64 .deb and .rpm for PostgreSQL 17, 18 and 19 (12), and the Amazon Linux 2023 .rpm for PostgreSQL 17 and 18 on both architectures (4); other versions build from source with make USE_PGXS=1. The packages depend on liblz4 and libzstd. The 19 ones are built and tested against PGDG’s 19~rc1. Each package was installed in fresh containers of its architecture, where CREATE EXTENSION, an update from 1.0, a sift index and its queries ran: the .deb in Debian 12, Debian 13, Ubuntu 22.04 and Ubuntu 24.04 (built on Ubuntu 22.04, glibc 2.35), the .rpm in Rocky 9, Alma 9 and Alma 10, the Amazon Linux 2023 one in amazonlinux:2023 (the arm64 ones on a hosted arm64 runner). The arm64 libraries are built for plain ARMv8-A and NEON, and the smoke test of each arm64 package runs scripts/check-aarch64-baseline.sh on the installed library.

  • 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_3.6.3-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-3.6.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-3.6.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 3.6.3 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.6.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, the aggregate planner and the packages, which are not in this extension. No speed-up or slow-down is claimed.
  • Needs SQLite 3.38 or newer; Linux x86-64, glibc 2.34 or newer. Built on Debian 12, loaded into the SQLite of Debian 12, Ubuntu 24.04 and AlmaLinux 10 before release.
  • Storage format 2: tables of 3.4.0 to 3.6.2 open as they are, nothing to rebuild.

Download sift-sqlite-3.6.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 3.6.3 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.6.3 release: no change to the SQL files.

  • No change: the changes of this release are in the PostgreSQL column index, the aggregate planner and the packages, which are not in this extension. No speed-up or slow-down is claimed.
  • One library per MariaDB series, because it calls the server’s own symbols and refuses to load into another series: 10.11, 11.4, 11.8, 12.1 and 13.0; Linux x86-64. Each was built in the stock mariadb:<series> image against that server’s headers and loaded into a server of the same image before release. BUILD-INFO in the tarball lists the server version and the glibc each library needs; a server of another distribution or of a series not listed is built from source (make -C mariadb).
  • Index format 2: an index of 3.4.0 to 3.6.2 opens as it is, nothing to rebuild.

Download sift-mariadb-3.6.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.