pg_sift 3.7.1: a backend crash while planning one shape of statement is fixed (3.7.0 and 3.6.3 had it), a server crash in the scan of a sorted list (ORDER BY .. LIMIT) is fixed (3.7.0 had it), a sift (BM25) index keeps its phrase positions through promotions and merges and gets back the ones it lost at its next merge, and three lookups of the write buffers of a sift_col index read the rows they need. Update with the files of this release and restart the server; no ALTER EXTENSION step, no REINDEX.

Highlights

  • Fixed: a backend crash while planning (SIGSEGV). A statement with a MATERIALIZED CTE of ids, a CTE that joins it back to a table with a column index, and a third CTE that joins a DISTINCT sub-select of that table to a pair table and to the second CTE, read by a scalar sub-select next to the second CTE, killed the backend (terminated by signal 11) in 3.7.0 and 3.6.3. The planner walked through the sub-select with the range table of the outer query; it now continues with the sub-select’s own. The new test agg_cte_lookup_subquery has the statement: it returns its row.
  • Fixed: a server crash in the scan of a sorted list. A page of a sorted list (ORDER BY .. LIMIT) with a join to a table or an IN sub-select, whose rows tie on the first sort key beyond the statistics, ended the server process: the top-N scan starts again with a larger heap, and the restart tested the key conjuncts of the first start without their state, so a sub-select read a second time came back empty. 3.7.0 had it; 3.6.x is not affected. The new test agg_topn_widen_keys has the statement.
  • Fixed: a sift (BM25) index with positions = on (the default) kept its positions through promotions and merges. The write buffer keeps terms and counts, not where in the text they are, so a promotion wrote a segment without positions, and a merge keeps positions only when every segment it merges has them: the first promotion followed by a merge turned the positions of the whole index off, and every phrase statement was decided by heap rechecks (the index 56% of the size of a fresh build, a ranked phrase +17.7% instructions, heap checks 2,505 to 23,855 over 300 statements). A promotion now reads the text of its documents from the heap, as a build does, and writes the position records of its terms (114 to 137 ms against 57 to 72 for 10,000 documents, in the maintenance worker; the INSERT statement is unchanged). No format change: the segments are the ones a build writes.
  • Fixed: an index that lost its positions gets them back at its next merge. A merge that finds a segment without positions analyzes its documents again from the heap (key order, live documents; a row that is gone is left out, as a VACUUM would), through the buffer a promotion fills: +0.45 s per 100,000 documents of such a segment, once (200,000 documents in 20 segments: the merge takes 2.80 s against 1.91 s for the same merge of segments with positions), and the result is byte for byte the size of the healthy index (42,336,256 bytes). No REINDEX is needed.
  • Fixed: lookups of the write buffers of a sift_col index. An IN list of the keys of a scope with an equality on one of them tested the 4,650 keys of the list for every unit and row (6 ms a scan against 0.8 ms for the equality alone) and was priced for the list: the list is cut to the constants the equality holds. The rows of the write buffers were looked up by the first condition the scan found, the list when the planner puts it first, so 85,693 rows and 4,652 heap blocks went to the heap for 3,455 matches; the condition with the fewest values finds them (4,757 rows, 1,149 heap blocks). A list that keeps more than a quarter of the buffered rows narrowed nothing and sent the other conditions to the heap (119,086 rows, 3,959 heap blocks for 37,500 matches); the buffers' batches now test every condition (50,000 rows, 771 heap blocks). The answers are those of 3.7.0; the work is less.

Who is affected and what to do

  • The planner crash: any database on 3.7.0 or 3.6.3 (earlier releases were not checked) that runs a statement of the shape above. Update; there is nothing to repair afterwards.
  • The sorted-list crash: any database on 3.7.0 that reads a page of a sorted list (ORDER BY .. LIMIT) with a join to a table or an IN sub-select whose rows tie on the first sort key after the statistics were made. Update; there is nothing to repair afterwards. 3.6.x is not affected.
  • The BM25 positions: a sift index on 3.7.0 that took a promotion of its write buffer followed by a merge, or an index that already lost its positions (the effect is speed only: every answer is right, the phrases are rechecked on the heap, and the index is smaller than a fresh build of the same rows). After the update the next merge of such an index writes the positions back, and the promotions that follow keep them. To get them at once, REINDEX INDEX CONCURRENTLY idx rebuilds it with them.

  • The lookups: speed only; no setting changes.

  • 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 for statements the fixes do not touch and every answer are those of 3.7.0.

Regression tests

New in this release: agg_cte_lookup_subquery (the planner crash), agg_topn_widen_keys (the sorted-list crash), col_list_equality and col_wbuf_lookup (the lookups of the write buffers, with the rows and heap blocks they read), phrase_churn, merge_parts, merge_heal and recheck_sure (the size and the phrase heap checks of a BM25 index after batches and a forced merge are those of a fresh build; the damaged index heals at a merge). PostgreSQL 17, 18 and 19 are those of the quick-tier run of the release.

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.7.0: extension SQL 3.7 (ALTER EXTENSION pg_sift UPDATE has nothing to do and there is no pg_sift--3.7--3.7.1.sql); column format minors 3 to 39 are read and written as in 3.7.0, BM25 metapage minors 1.0 to 1.4; no setting changes its default. The SQLite and MariaDB extensions: no change since 3.7.0.

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.1-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-3.7.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-3.7.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 3.7.1 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.7.1 release: no change to the SQL interface or the shared format.

  • No change: the changes of this release are in the PostgreSQL BM25 index, the column index and the aggregate planner, 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.7.0 open as they are, nothing to rebuild.

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

  • No change: the changes of this release are in the PostgreSQL BM25 index, the column index and the aggregate planner, 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.7.0 opens as it is, nothing to rebuild.

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