pg_sift 3.0.1: a COPY or INSERT ... SELECT of any size into a sift_table in one transaction, four fixes in the aggregate pushdown (a crash, a wrong refusal, a stale overflow flag, a missed row) and the CI harness. Install the new files: the extension SQL does not change (it stays 3.0), so there is no ALTER EXTENSION pg_sift UPDATE step, and no REINDEX.

Highlights

  • Fixed: a single COPY or INSERT ... SELECT into a sift_table is no longer limited to about 500 MB (about 4 million rows of the orders layout). The 3.0.0 known issue: a larger load failed with Too many write buffers waiting for promotion, because the rows of a running transaction cannot be promoted into columns. Once the directory is half full, the write buffers that one command of one transaction wrote now become column groups that carry the writer’s transaction and command; they are invisible to everybody else until it commits (at every isolation level, through scans, indexes, bitmap scans, parallel plans and SiftAgg), and VACUUM drops them if it aborts or freezes them once it is committed. A 5,000,000-row COPY into a table with a primary key, in one transaction, loads in 18 s with a backend peak of 182 MB; 10,000,000 rows take 32 s and 180 MB; the load speed of a 1,000,000-row COPY is unchanged. A transaction that writes through many commands (a loop of single-row INSERTs) or concurrent writers into the same write buffers are still limited by the directory, and the error now says so. See docs/COL-TAM.md.
  • Fixed: a backend crash in Custom Scan (SiftAgg) for per-item aggregates under an outer aggregate (the arms of a UNION ALL over a shared CTE) when an inner aggregate has a FILTER on a text leaf (count(*) FILTER (WHERE p = 'x')): the scan did not register the predicates of the inner aggregates' FILTER.
  • Fixed: values a sift_col index cannot store. A query that reads a leaf which a unit flags with an overflow value (a JSON string in a float8 path, a text over its declared length) raised “a column of the aggregate scan holds values its index cannot store” where the normal plan reads the value from the heap. The planner now keeps the normal plan for such a query, so the rows are the normal plan’s; queries that read other leaves are still pushed down. The write buffer (until sift.col_promote) and a cached generic plan made before the value was written still raise the error. The overflow flag also stayed after the rows that held the value were repaired and vacuumed, until REINDEX: VACUUM now rewrites a lone segment that flags the overflow of a leaf only in dead rows.
  • Fixed: ORDER BY <column> LIMIT n through the top-N scan returned other rows than PostgreSQL after an UPDATE moved a row, because the new version of an updated row is stored wherever the heap has room. A window is now read when the zones of its units, taken together, can beat the n-th row; the in-order cases cost the same instructions as before (1.00x at 80 superblocks).
  • Test infrastructure, no product impact: the harness flakes seen on the 3.0.0 release commit are fixed (jobs that install a library flavor run after the lanes before PostgreSQL 18, the regression clusters run without autovacuum, the WAL consistency standby reserves WAL from the creation of its slot, query_dos no longer leaves a 1 ms timeout armed).

Compatibility

  • PostgreSQL 17 and 18; PostgreSQL 19 is experimental (CI builds it from git on every push). No extension SQL change (it stays 3.0) and no change of the BM25 or sift_col formats: 3.0.0 indexes and sift_table tables open as they are. After installing the new files nothing else is required.
  • Going back to 3.0.0: run VACUUM on every sift_table loaded by 3.0.1 first. A load too big for the write buffers is stored as column groups that carry the writer’s transaction until VACUUM freezes them, and a 3.0.0 library reads a group that is not yet frozen as frozen, so the rows of a rolled-back load would show up.

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.0.1-1_amd64.deb
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresql18-server): sudo dnf install ./pg_sift_18-3.0.1-1.el9.x86_64.rpm
  • Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

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

  • No change: the fixes of this release are in the column engine, the aggregate pushdown and sift_table, which are PostgreSQL features and 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.0.0 open as they are.

Download sift-sqlite-3.0.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.0.1 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.0.1 release: no change to the index format, the SQL files or the shared core.

  • No change: the fixes of this release are in the column engine, the aggregate pushdown and sift_table, which are PostgreSQL features and 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.
  • Index format 2: an index of 3.0.0 opens as it is, nothing to rebuild.

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