pg_sift 3.3.0: everything since 3.2: the aggregate scan takes many more query shapes (top-N with any sort key, joins to small tables, semi and anti joins, two-level arms), sift_col indexes keep their pre-aggregates and answer from them after churn without a setting, point lookups by value, PostgreSQL 19 as a supported major, and the first release that tells you after the update what is left to do. Update the files, restart, then ALTER EXTENSION pg_sift UPDATE and run the commands it prints (a REINDEX of every sift text index is required).

Highlights

  • Maintenance on by default. sift.col_auto_merge (3.2: off) is on: a background worker vacuums the table when a thousandth of its rows changed and rewrites a sift_col index after the VACUUM, within sift.col_auto_merge_wal_pct (5) of the WAL the server writes, so the units stay answerable from their pre-aggregates after churn (+0.9% to +1.9% WAL with full-page images in the churn tests; the CHANGELOG.md line has the units answerable). sift.table_auto_defrag (3.2: off) cleans a sift_table in the background past 5% dead rows (sift.table_auto_defrag_dead_pct, was 20), with sift.table_auto_defrag_wal_rate and sift.table_auto_defrag_max_active to bound it. Both need sift.async_maintenance (on). sift.col_maintenance_status(index) and sift.col_index_status say what the worker waits for.
  • Pre-aggregates by default. cube_minmax, superunit = 16 and calendar_columns (every date and timestamp column) are on for a new sift_col index; key_columns = auto keeps the keys of foreign key and *_id columns in each unit. A whole-table count/sum goes from 0.26-0.37 ms to 0.05-0.08 ms and a GROUP BY with min/max of an integer from 5.5 to 0.34 ms (200,000 rows), for an index 1.5% larger.
  • Point lookups. point_columns (a directory of the rows of a unit by value) answers an equality or a short IN list from the units that hold the value: 0.015 to 0.022 ms at 1,000,000 rows. point_tid_columns (column format minor 16, opt-in) lists heap addresses instead: 0.011 to 0.013 ms.
  • Top-N and ordering. ORDER BY .. LIMIT n is a scan of the column index for text, uuid, bytea, inet, bool, time and money keys, for expressions (coalesce, lower, upper, arithmetic, casts, CASE), for OFFSET and for a page with count(*) OVER (), with parallel workers; order_columns keeps sorted runs for it (opt-in).
  • More shapes in the scan. A group table that outgrows work_mem spills to partitions, count(DISTINCT) of a column of a few values keeps a few words per group, semi and anti joins to a tree of small tables, LEFT JOIN LATERAL over the elements of a JSON list, two-level arms over a spine, 16 UNION ALL arms. sift.explain_refusals and sift.explain_why(query) say why a query kept its plan; sift.plan_scale plans as if the tables were larger.
  • sift_table. A row over the write buffer is accepted (sift.table_wide_rows, up to 1 GB through tail pages); COPY, CREATE TABLE AS and REFRESH MATERIALIZED VIEW write column groups (sift.table_bulk_groups); a backend reads the tombstone log of a table 2.2 times faster; the row fetch of a text from a dictionary stored in blocks no longer loads the whole dictionary (1.4 ms where it took 28.6 ms).
  • Row-level security. sift.rls_scores (superuser setting, off by default) gives a role under a policy the ranked scan and sift.score(); the scores are those of the whole index, so they depend on rows the role cannot see (the caveat is in docs/USAGE.md, “Row-level security”).
  • PostgreSQL 19 is a supported major (17, 18 and 19), no longer experimental. AArch64 has a neon level of sift.simd (Linux arm64 and Apple silicon).
  • Upgrades. ALTER EXTENSION pg_sift UPDATE prints what is left to do, sift.upgrade_commands() lists it, scripts/upgrade-check.sh is a preflight (also --to an older release), and docs/UPGRADING.md has the downgrade sequence; docs/LIMITS.md lists every fixed limit with the setting that changes it.

What a 3.2 user must know

  • A REINDEX of every sift text index is required (except analyzer = keyword). In 3.3 a word over 255 bytes is stored as its start and a hash; an older index holds it cut at 255 bytes, so queries miss rows until it is rebuilt. The update prints the commands.
  • A database written with the 3.3 defaults cannot be read by a 3.2 library. 3.3 writes at sift.col_format_minor = 15 by default (a sift_col index created or rewritten after the update, a sift_table with a date or timestamp column); a 3.2 library refuses what it cannot read, with a REINDEX hint. To keep 3.2 able to read new writes, set sift.col_format_minor = 6 (4 without JSON paths) for the server (ALTER SYSTEM) before the update: a setting of one session or of one role does not reach the background worker of sift.col_auto_merge, which would write the default 15 into an index. Or create the indexes WITH (cube_minmax = false, superunit = 0, calendar_columns = 'off'). The way back, step by step (the preflight with --to 3.2, one command per blocker, then install the 3.2 files), is in docs/UPGRADING.md, “Going back to 3.2”. A standby that stays on 3.2 needs the same.
  • The default minor is 15; minor 16 is opt-in. point_tid_columns writes minor 16 only for the segments of an index created with it, and a 3.2 library refuses minor 16 like any column format it does not know. To roll the option back, sift.col_format_minor = 14 and a REINDEX of that index (the point directory then lists rows again).
  • Background work is on. The auto-merge worker also runs VACUUM on the tables of sift_col indexes and writes WAL bounded by sift.col_auto_merge_wal_pct (5% of the WAL the server writes); the CREATE INDEX and REINDEX NOTICE says how to stop it. sift.col_auto_merge = off and sift.table_auto_defrag = off restore the 3.2 behavior; a sift_table pass adds about 60% more WAL on an update, insert and delete mix at 200,000 rows (the line in CHANGELOG.md has the numbers).
  • Other defaults that change bytes or plans: sift.agg_lookup_shared_kb shares the rows of a joined table between backends (-1: the smaller of sift.agg_lookup_cache_kb and 5% of shared_buffers; 0 turns it off); sift.table_wide_rows and sift.table_bulk_groups are on; the planner compares the shared and the inline form of a CTE with a margin of 1.6.
  • The first statement of a fresh backend with bound parameters (a driver on the extended protocol, EXECUTE with arguments) is planned again through the planner hook in 3.3.0, as a plain SELECT already was in 3.2.3: 3.2.3 still plans it without sift once per backend (220 to 260 ms against 3 to 5 ms for the next statement). session_preload_libraries = 'pg_sift' avoids that cost in both.
  • A backend can hold up to 64 MB for sift_col scans. 3.3 keeps the checked heads and the zone arrays of the segments a backend reads, besides the parsed dictionaries that 3.2.3 already keeps, in one least-recently-used cache per backend capped at 64 MB (dict_cache_kb, col/dictcache.c; a compile-time constant in 3.3.0, no setting changes it). A long-lived backend (a pooled connection, a parallel worker) grows toward the cap and stays there; the entries of segments that a merge or a promotion replaced stay until the cache pushes them out. Measured under a write-heavy soak that creates segments all the time (6 backends, 120 s, PostgreSQL 18): 13 to 36 MB per backend, about 2.1 kB per cached segment; 3.2.3 does not grow that way. Budget up to 64 MB of private memory per backend for sift_col scans; to bound it, limit the number of long-lived backends (a pooler with a server lifetime) or rebuild with a lower CDICT_CACHE_MAX_KB. A setting is planned for 3.4.

Fixes for defects present in 3.2.3 and earlier

There is no 3.2.4: these fixes ship with 3.3.0.

  • sift_table: a bitmap-scan UPDATE or DELETE could stop early without an error. When a VACUUM promoted the write buffer of the table while the statement ran, the statement skipped the rows of the promoted blocks and reported success (1530 of 3000 rows updated in the reproduction). Present in 3.2.3 and, by the code path, since 3.0.0. Affected: sift_table tables updated or deleted through a bitmap scan while a VACUUM or the background defrag runs on them. Until the update, SET enable_bitmapscan = off for such statements, or do not run them concurrently with VACUUM or the defrag; a bulk statement that reports fewer rows than the table holds for its WHERE clause is the sign.
  • sift_table: an UPDATE or DELETE could fail with failed to fetch tuple being updated. It happened when a VACUUM or the background defrag replaced the tombstone log while the statement scanned the table (sequential or bitmap scan). Present in 3.2.3. The statement rolls back and no rows are lost; run it again, or avoid running it while a VACUUM is in progress.
  • GROUP BY an expression of a date column that holds infinity and -infinity failed when the aggregate scan was chosen (3.2.3: a column range of 4294967296 values is too wide for the aggregate scan). An error only, no wrong rows. Workaround: SET sift.enable_agg = off for the statement.
  • GROUP BY on a date column whose selected rows are all infinity (or all -infinity) stopped the backend. The range table of the group keys stepped over the end of the 32-bit range and wrote far outside its allocation; a serial scan was enough, no parallel worker needed. Present in 3.2.3 and earlier. Workaround: SET sift.enable_agg = off for the statement.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below are for PostgreSQL 18 (other majors build from source with make USE_PGXS=1; PostgreSQL 19 was tested on its release candidate). Extension SQL 3.3; ALTER EXTENSION pg_sift UPDATE reaches it from any older version in one command. Column format minor 15 by default (16 opt-in); the BM25 index needs the REINDEX above. docs/UPGRADING.md has the preflight and 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.3.0-1_amd64.deb
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresql18-server): sudo dnf install ./pg_sift_18-3.3.0-1.el9.x86_64.rpm
  • Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

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

  • No change: the 3.3 work is in the PostgreSQL column index, the aggregate scan 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.2.3 open as they are, nothing to rebuild.

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

  • No change: the 3.3 work is in the PostgreSQL column index, the aggregate scan 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.2.3 opens as it is, nothing to rebuild.

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