pg_sift 3.7.0: faster and smaller analytic scans, a smaller BM25 position stream, a standby that no longer keeps a file at twice its live size, and a planner that spends fewer instructions on the statements it cannot help. It writes new on-disk formats that 3.6 and older refuse (column format minors 33 to 39, BM25 segment format 4): read “Upgrading from 3.6” before you install it on a primary whose standby or rollback target runs 3.6.

Highlights

  • BM25 positions in blocks (segment format 4). The position stream of a term is coded in blocks of 32 postings. On 218,058 Wikipedia articles the index goes from 136,871,936 to 124,452,864 bytes (-9.07%); a phrase count(*) of three words takes 9,357.8 k instructions to 4,311.8 k (-53.9%) and the top 10 of it 15,735.9 k to 12,808.3 k (-18.6%), with the same answers and 640 to 476 cold blocks. A merge costs 1.8% to 4.2% more instructions and CREATE INDEX 7.9% more. 3.6 and older refuse an index that holds a segment of format 4.
  • Cold reads of a sift_col index. A scan reads the first 4 kB of a unit, not 24 kB, and the chunk table of a key-cut unit comes before its directory: a cold scan of a narrow set of leaves reads 17% to 49% fewer blocks (1,000,000 rows, 8 leaves: GROUP BY 1,214 to 816 blocks, count(DISTINCT) 909 to 650), and a key_cut index 20% to 26% fewer again. The point directory has ceil(keys * 4 / 3) slots instead of a power of two: 10.7 bytes a key instead of 10.7 to 21.3 (400,000 unique keys: 4.27 MB instead of 8.39 MB).
  • Index builds. The participants of a parallel CREATE INDEX or REINDEX of a key_cut index merge by key range when the output has 8 partitions or more: 3,000,000 rows, 8 workers, wall 35.6 s to 24.7 s at the same CPU and WAL. The sample that decides the default key_cut is 16 superblocks at least, not 32: CREATE INDEX of 1,000,000 rows takes 16,554.8 M instructions to 13,774.6 M and 14,335.3 M (-13% to -17%).
  • A standby does not keep a file at twice its live size. VACUUM of a sift_col index and the concurrent builds give the free end of the file back under a lock that a standby does not replay: with a standby query of 30 s on the table the file of 120 MB for 46 MB of live segments (2.6 times) goes to 46 MB (1.00) and the replay stands still for 0.1 s.
  • Aggregates. Grouping sets that lie inside one of them (ROLLUP, CUBE) are summed from the finest set: grouping_sets_report over 1,000,000 orders 394 M instructions to 126.5 M. ORDER BY up to 8 columns LIMIT 10 runs in the top-N scan: 2.8 ms for 5 columns over 1,000,000 orders where PostgreSQL’s sort of the table takes 64 ms. The planner prices and plans a statement it cannot help with fewer instructions (the constants of an IN list are read once for a planning; the passes before the planner do not walk a list of constants; a statement whose normal plan is far cheaper than any scan is not offered to the arms of the scan).
  • sift_table. An UPDATE that keeps the columns the indexes read is HOT as the heap’s (40,000 updates by key of 2,000 rows with two b-trees: 7,152 M instructions to 3,853 M, WAL 23.8 to 15.4 MB; sift.table_hot_update = off returns the old behavior). ANALYZE of a table of more than about 120,000 rows builds heap tuples for a share of the rows (VACUUM (FREEZE, ANALYZE) of 1,000,000 orders 4,164 M to 1,528 M instructions). A bulk load writes a run of 8 superblocks as one segment (1,000,000 orders: 6 segments to 3, 22.24 MB to 19.91 MB).
  • Fixed: wrong results. A sift_col index with key_cut and an order array lost rows through Bitmap and Index Scans for a range or an equality on the order-array column (fixed in 3.6.2), and a bitmap scan of a text column with an order array returned too few rows; both are fixed here. The rest of the fixes are in CHANGELOG.md.

Known limits

  • Reports dominated by sorts, windows and wide group-bys gain less. The scan makes the aggregates and counts of such reports faster, but most of their time stays in PostgreSQL’s own sort and window nodes, which the scan does not replace; a different statement shape, not a faster scan, is what would help them.
  • Fixed costs of a small query. A scan has a fixed cost of reading and planning that a point lookup by key does not pay; the planner keeps the normal plan where it is cheaper, and a statement of one query level whose normal plan costs under half of the cheapest scan is not offered to the scan.
  • Formats are one way. 3.6 and older cannot read what 3.7 writes (below); a standby or a rollback target that stays on 3.6 needs the settings of “Upgrading from 3.6”.

Upgrading from 3.6

  1. Preflight on the primary: scripts/upgrade-check.sh --all, and with --to 3.6 what 3.6 could not read (both read-only). The second lists every index and table that 3.6 would refuse and the REINDEX that writes what 3.6 reads.
  2. Install the files and restart the server on the standbys first, then the primary (a backend that loaded the old library keeps it until its connection ends). Run ALTER EXTENSION pg_sift UPDATE in each database: the extension SQL goes from 3.6 to 3.7 and the script has nothing to change.
  3. What 3.7 writes that 3.6 refuses: column format minors 33 to 39 (point directory slots, the chunk table before the directory, the rollup lengths, the dictionary order bits, the directory records at the end of the dictionary section), written when sift.col_format_minor is 15 or more (the default), and BM25 segment format 4 (metapage minor 1.4). Existing files are read as they are; a merge, a VACUUM rewrite, a promotion or a REINDEX writes the new formats from then on.
  4. If a standby or a rollback target stays on 3.6: SET sift.col_format_minor = 14 (postgresql.conf, or for the role and the database) before the primary writes the new formats, then REINDEX INDEX for each sift_col index that scripts/upgrade-check.sh --all --to 3.6 lists (VACUUM FULL t for a sift_table, which takes an ACCESS EXCLUSIVE lock). At 14 the files are written without the sections of minors 15 to 39: the point directories keep a power of two of slots and the units of a key_cut index are cut by superblock, so the gains of minors 15 to 39 are not there until the setting returns to its default. A BM25 index cannot be written at the old segment format: keep it on 3.6, or drop it and create it again with 3.6.
  5. Check: SELECT sift.verify('idx', true) for a BM25 index and SELECT sift.col_verify('idx', true) for a sift_col index; scripts/upgrade-check.sh again: the second run has no warning the first one did not explain.

No setting changes its default for existing indexes. docs/UPGRADING.md has the full procedure and the table of the first release that reads each format.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). Extension SQL 3.7 (pg_sift--3.7.sql, pg_sift--3.6--3.7.sql); column format minors 3 to 39 are read, BM25 metapage minors 1.0 to 1.4. The SQLite and MariaDB extensions: no change since 3.6.3.

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.7.0-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-3.7.0-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.7.0-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.7.0 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.7.0 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.3 open as they are, nothing to rebuild.

Download sift-sqlite-3.7.0-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.7.0 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.7.0 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.3 opens as it is, nothing to rebuild.

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