pg_sift 2.0.1: a security fix for roles under row-level security and a cheaper count(*) over vacuumed indexes. Install the new files: the extension SQL does not change (no ALTER EXTENSION pg_sift UPDATE step), the on-disk format does not change (no REINDEX). One behavior changes for roles under a policy: read “Security” below.

Highlights

  • Security: hidden rows no longer influence the scores or the plans of a role under row-level security. The ranked scan and the match estimate ran before the security quals of the relation, so the BM25 statistics of the whole index (document count, document frequencies, average length) entered the scores of the rows a role can see, and EXPLAIN costs and row estimates followed the postings of the rows it cannot. With a policy hiding 3,000 of 3,020 rows, a visible row of a term that 20 visible documents have scored 0.000184, a value computed from all 3,020 documents. What a role could infer from this is aggregate (how many documents the hidden rows have and how common a term is among them); the hidden rows themselves, and the rows and counts the queries return, were never exposed.
    • Affected: tables with a sift index that are read under a policy (ENABLE ROW LEVEL SECURITY, by the roles the policy applies to) or through a security_barrier view, by a role that can run ranked queries or EXPLAIN. Not affected: tables without row-level security, the table owner and the roles that bypass it. sift.facets already refused tables with row-level security.
    • Now: the match must be securely promotable, as an index qual must be, and the estimate uses the default selectivity. For those roles the rows come from a sequential scan (no SiftScan, no SiftCount), so queries are slower on large tables, and sift.score() raises sift.score() is not available on "t" while row-level security or a security barrier view applies. A query that ranked under a policy before now fails: rank as a role the policy does not apply to. Test: rls_stats.
  • count(*) over vacuumed documents costs a fifth to a half of what it did. User instructions per query, interleaved A/B against 2.0.0, 200,000 documents with 10, 25 and 45% deleted and vacuumed: the count of a term 0.21x, of two terms 0.49x, of three with OR 0.30x; ranked AND 0.87x to 0.88x, ranked OR 0.84x to 0.85x, the filtered scan 0.91x to 0.94x. Clean indexes are no slower: the clean count of a term takes 0.57x, of two terms 0.95x to 0.97x, of three with OR 0.87x to 0.91x, and the instruction gate over 16 query cells shows no regression (0.98x to 1.00x). Counts, rows and scores are the same.
    • Known limit: a count over vacuumed documents still costs several times a clean index’s until a merge rewrites the segment: the count of a term went from 18.5 to 18.6 times the same live rows rebuilt to 6.6 to 6.7 times (two terms from 3.5 to 4.4 to 1.7 to 2.3, three with OR from 4.5 to 6.2 to 1.5 to 2.1). Closing that gap needs a change of the on-disk format and is not part of this release.
  • CI: make check uses less scratch space and time (jobs admitted by the scratch they need, small WAL for the throwaway clusters); no change for users.

Compatibility

  • PostgreSQL 17 and 18; PostgreSQL 19 is experimental (CI builds it from git on every push). No extension SQL change (it stays 1.2) and no on-disk format change: existing indexes keep working, with no REINDEX. After installing the new files nothing else is required.

Packages

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

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

Every change: CHANGELOG.md.

SQLite extension

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

  • Shared engine: the code that counts documents of segments with vacuumed documents is the one changed for PostgreSQL; the results are the same, and no SQLite speed-up is claimed (the figures above are PostgreSQL’s). The row-level security fix does not apply: SQLite has no row-level security.
  • 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: nothing to upgrade from 2.0.0.

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

  • Shared engine: the code that counts documents of segments with vacuumed documents is the one changed for PostgreSQL; the results are the same, and no MariaDB speed-up is claimed (the figures above are PostgreSQL’s). The row-level security fix does not apply: it is a PostgreSQL planner change.
  • 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). MySQL is not supported.
  • Index format 2: an index of 2.0.0 opens as it is, nothing to rebuild.

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