pg_sift 3.4.1: a backend that loads several extensions no longer dies with FATAL: out of relcache_callback_list slots on the first query that uses a sift_col index, a lookup of one or a few keys keeps the normal plan, a key_cut build that does not fit in maintenance_work_mem stops at once and says how much it needs, a statement on an index with many units no longer pays a fixed cost for each morsel, a filter on a text column can be cancelled, and the first rows of each group asked with a window function are planned at their price. Library only: install the new files and restart the server (or reconnect, when the library is preloaded per session); the extension SQL does not change (it stays 3.4), so there is no ALTER EXTENSION step and no REINDEX.

Highlights

  • Fixed: FATAL: out of relcache_callback_list slots. A backend that loads several extensions could die with FATAL: out of relcache_callback_list slots on the first query that used a sift_col index. PostgreSQL gives every backend ten relcache callback slots, shared by all loaded libraries, and pg_sift used up to four of them (the sift_table relation cache, the CTE verdict cache, the partition bounds and the partition facts). pg_sift now takes none: the partition bounds and facts follow the rows of pg_class through syscache callbacks (sixty-four slots of their own), a sift_table notices the invalidation of its relation by a mark in the rd_amcache that the relcache frees with it, and the CTE verdicts are no longer dropped by an invalidation. The test relcache_slots finds 8 free slots without pg_sift and 4 with 3.4.0; with 3.4.1 it finds 8 and 8.
  • Fixed: a lookup of one or a few keys took the scan at a price far above the normal plan’s, and lost. A request for one key of a lookup (a CTE of ids joined to the table, a grouped aggregate over the elements of a jsonb map) was charged on its normal plan as a lost estimate, 64,555.85 where the plan is priced 48.29 (1,000,000 rows, one key of about 200 rows), so the scan, priced 3,507.02, won whatever it cost. A lookup of up to four keys, as PostgreSQL counts them, keeps its btree probes at the price they are planned for. Lab, 3,000,000 rows, one small and one medium key, milliseconds: 8.8 and 12.4 in 3.4.0, 0.9 and 6.0 in 3.4.1 (the normal plan: 1.1 and 7.0); a workload of single-key lookups that took 340 ms each takes between 2 and 6 ms. Test agg_few_keys_pick.
  • Fixed: key_cut builds that run out of maintenance_work_mem. A CREATE INDEX (also CONCURRENTLY) or REINDEX of a sift_col index with key_cut on a table whose rows made more runs than the 121 segments of the directory failed after reading as many rows as the runs held (needs more than 60 segments, at 1.7% of a 136,750,000-row scan at 64 MB with one worker). The build now holds the rows of up to 32 superblocks before it reads the table, counts the runs, uses fewer parallel workers when that makes them fit (6,000,000 rows of 580 bytes at 64 MB: two participants failed at 60 segments each, a serial build makes 52 runs in 28 s, a plain serial build takes 25.5 s), and otherwise stops before the first row with the maintenance_work_mem it needs. The automatic decision (key_cut unset) gives up a cut that does not fit, with a NOTICE, and builds the index without it. Tests col_key_cut and col_key_cut_auto.
  • Fixed: a fixed cost for each statement on an index with many units. A statement whose filter is a set of ranges on the zones of a sift_col index with key_cut started the setup of a scan for each morsel; the setup is made once for the scan now. An index with many units paid about 0.3 s for each statement; a typical scan of the lab went from 8.3 ms down to 1.6 ms, and a list of 10 keys from 15-18 ms to 3.6 ms; an index without key_cut gains in proportion to its units.
  • Fixed: a filter on a text column could not be cancelled. A filter on a text column (LIKE, a function of the column) is evaluated once for each entry of the dictionary of the column before the scan reads a row, and the loop had no interrupt check: statement_timeout and a cancel waited for all of it (about 200 ms over 2,800,000 rows, seconds with a dictionary of a hundred thousand long names and an expression that costs microseconds an entry). The evaluation checks for interrupts now; the test interrupt_dict_filter ends a statement that ran 3,192 ms in under a second at statement_timeout = 100. An audit of about 70 other long statements (aggregates, builds, REINDEX, merges, VACUUM, sift_table scans and writes, 1,000,000 to 2,800,000 rows) found every one cancelled within about 110 ms.
  • Fixed: the first rows of each group by a window function. row_number() OVER (PARTITION BY k ORDER BY o) with rn <= N, N > 1, ran in the scan when its plan priced less than the window’s, which left out the 1.4 to 2 microseconds the executor spends on each group to unnest its rows: 6,263 groups cost 12.3 ms there and were priced 4.5. The plan now pays for the unnest (window_first_rows, 1,000,000 rows, 31,675 in range: the window plan 26.2 ms, the scan 32.8 ms; with 30 or 40 groups the scan stays and wins, 22.4 against 33.2 ms). This is the second item of “Known regressions in 3.4.0”. Test agg_topn_rownumber.

Who is affected and what to do

  • The relcache fix: every PostgreSQL 17, 18 and 19 server whose sessions load other libraries that register relcache callbacks. Measured on PostgreSQL 18: the server takes 2 of the ten slots, citus.so 1 at load and 4 more on the first query when the citus extension is created in the database, pg_cron 1 and timescaledb 1 (a hypertable adds none); postgis, citus_columnar, pgvector, pg_hint_plan, pgmq, pg_clickhouse and pg_background take none. A session with those libraries and without the citus extension had 5 taken before pg_sift and 9 with its four, which is why such a database could run 3.4.0 for hours; with the citus extension created it had 9 before pg_sift, and the second cache of pg_sift was the eleventh registration. Install 3.4.1 and reconnect; nothing else changes.
  • Other libraries can still use the ten slots. The limit is a PostgreSQL constant shared by every library: with the citus extension created, citus, timescaledb, pg_cron and the server leave one slot spare, and any library that registers two or more relcache callbacks ends the session whether or not pg_sift is loaded. Do not create the citus extension in a database that does not use it.
  • Lookups of one or a few keys: a statement that took the scan with 3.4.0 for one key (SiftAgg in the plan, slower than SET sift.enable_agg = off) takes the normal plan now.

Known limits of 3.4.1

  • An explicit key_cut build of a table of wide rows still needs maintenance_work_mem of about (workers + 1) x 2 or 3 GB, and a table that needs more runs than the directory holds at the memory given cannot be built with key_cut: the build stops at the start and names the memory it needs (give it that, or ALTER INDEX .. SET (key_cut = 'off') before the build). Merging the runs inside the build, which lets it finish at any memory, comes in 3.5.
  • One key of a very large entity now takes the normal plan, as in 3.3 (3,000,000 rows, a grouped sum: 13.9 ms in the scan of 3.4.0, 20.9 ms in the plan), because the planner sees the same estimate for a small and a large key. Lookups of 10 keys or more keep the charge, so the scan still costs more than the normal plan in the estimate and wins in time (22 ms for 10 keys where the normal plan takes 76 ms). A choice made at run time is 3.5 work.
  • Store question 03 after the maintenance step (listed in “Known regressions in 3.4.0”) is not a regression of 3.4: with the same harness the median is 4.02 ms on 3.4.0 and 3.97 ms on the 3.3.0 library (1,000,000 orders, PostgreSQL 18.6, after the update and maintenance steps; 0.37 to 0.58 ms before them). 3.4.1 does not change it. The other items of that list (a numeric column without a precision with almost unique values, the slow cases against a heap table) are unchanged.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below are for PostgreSQL 18 (other majors build from source with make USE_PGXS=1). A drop-in replacement of 3.4.0: extension SQL 3.4, no change of the BM25, sift_col or sift_table formats, no setting changes its default. Install the new files and restart the server; going back to 3.4.0 needs nothing. docs/UPGRADING.md has the steps.

Packages

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

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

Every change: CHANGELOG.md.

SQLite extension

pg_sift for SQLite 3.4.1 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.4.1 release: no change to the SQL interface or the shared core.

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

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

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

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