pg_sift 4.0.3: pg_upgrade of a cluster whose extension is still below 4.0 no longer stops in the restore, an automatic merge pass no longer fails when the WAL ends at a page boundary, a COPY into a sift_table with parallel workers no longer fails when the workers finish first, building a sift_col index now prints one short NOTICE, and sift.index_choices() and sift.help() tell what was chosen, why, what it cost and how to turn it off. The same table gets the same defaults on PostgreSQL 17 and 18, OFFSET over the top-N scan and the track_changes triggers of a table with several indexes cost less. A patch release of 4.0.2 with two new SQL functions: install the packages, restart the server, then run ALTER EXTENSION pg_sift UPDATE in each database.

Highlights

  • Fixed: pg_upgrade of a cluster whose extension is still below 4.0 (3.x) could stop in the restore with sift: function sift.col_track_old_rows() does not exist, when the dump created a sift_col index after the primary key of its table (4.0.1 and 4.0.2; the order of the dump depends on the names and on the PostgreSQL major). The restore runs while the extension is still the old one, which has no sift.col_track_old_rows(); nothing wrong was written. Now track_changes = 'auto' leaves such an index untracked with the advice the extension is not updated yet, and an explicit track_changes column is refused with a hint to run ALTER EXTENSION pg_sift UPDATE; after the update REINDEX INDEX <index> makes the log (sift.col_tracking). With 4.0.1 or 4.0.2 the way around is ALTER EXTENSION pg_sift UPDATE in each database of the old cluster before pg_upgrade.
  • Fixed: a COPY into a sift_table with parallel workers could fail with invalid memory alloc request size 1879048192 (4.0.0 to 4.0.2). When the workers had finished every slot of the load ring before the leader looked (a loaded host makes it likely), the leader’s hand-over loop counted past the end of the ring and doubled its array until the allocation failed. The statement is aborted, nothing is committed and no row is wrong, and running it again usually works. The loop now stops at the end of the ring.
  • Changed: one short NOTICE for CREATE INDEX, REINDEX and REINDEX CONCURRENTLY of a sift_col index. Each automatic choice used to print a NOTICE with a DETAIL and a HINT of its own. Now one NOTICE counts the choices, the advice and the limits, gives their values on two lines and names the command that lists the rest. SELECT * FROM sift.index_choices('index') gives, for each choice, the value, the cost measured on the index, the reason and the command that turns it off; sift.help('slug') returns the row of docs/messages.tsv (the one source of docs/MESSAGES.md) for a message or a choice. A concurrent build says in one line that the track_changes log is not complete and which REINDEX makes it so. The choices are the same; client_min_messages = warning hides the summary.
  • Fixed: an automatic merge pass could fail with xlog flush request .. is not satisfied (every release since 3.4.0). The worker that merges the segments of a sift_col index (sift.col_auto_merge) flushes the write-ahead log before the VACUUM it runs; when the last WAL record ended exactly at the end of a WAL page, it asked for a position past the written WAL, the server answered with that ERROR and the pass failed and was tried again later. Rare, and nothing wrong was written. It now flushes up to the end of what is inserted.
  • Fixed: the same table gets the same defaults on PostgreSQL 17 and 18. The defaults that CREATE INDEX chooses from the statistics of the table (point_tid_columns, order_array_columns, order_array_group, key_order, the analyze of a table that has none) counted the rows from pg_class, which a b-tree built before it had left holding the real count on 17 and nothing on 18, so one version analyzed the table and the other did not and the two built different indexes. The rows are now those of the pages the table has times the density of the rows pg_class counted, or of a sample of its pages when none was counted. On PostgreSQL 18 this changes what a table gets that was loaded and indexed in one go and never analyzed: with 100,000 rows or more it is now analyzed by the build, as it was meant to be, and gets the defaults (point_tid_columns, order_array_columns) that 4.0.2 did not choose for it because it counted too few rows; and a table of 60,000 rows of 1,000 bytes, which 4.0.2 analyzed on the strength of the planner’s guess at the width, is no longer analyzed.
  • Faster: ORDER BY .. LIMIT n OFFSET m answered by the top-N scan. The Limit node no longer pulls the m rows to throw them away: the scan counts the entries of the OFFSET itself. SELECT d FROM olt ORDER BY d OFFSET 100000 LIMIT 10 over a date of 730 values in 1,000,000 rows takes 1.82 ms and 20.9 M instructions, against 2.86 ms and 40.9 M before; the blocks are the same.
  • Cheaper writes on a table with several track_changes indexes. The statement triggers of the indexes of one table probe the unique index once for all of them, not once each; each index keeps its own log. With 3 indexes a DELETE of 2% of 100,000 rows costs 73.4 M instructions and a key change of 1% 92.3 M.
  • Docs and packages: the README leads with analytics and the figures it shows have a measured run behind them, and the description of the project is the same on GitHub, in the packages and in the AUR.

Who is affected and what to do

  • Update (the steps, in this order): install the files; restart the server (a standby too), because a backend that already loaded the old library keeps it until its connection ends; then run ALTER EXTENSION pg_sift UPDATE in each database. The update creates sift.index_choices and sift.help and nothing else: the extension goes from 4.0 to 4.0.3, no column format changes, no REINDEX. Before the ALTER EXTENSION, the new library runs against the 4.0 SQL and the NOTICE of a build already is the short one, but the two functions do not exist yet.
  • Coming from 4.0.0 or 4.0.1: this release contains the fixes of 4.0.1 and 4.0.2, which correct wrong grouped reads (a table attached as a partition or inheritance child, a primary key dropped and added again, pg_squeeze): after the update run REINDEX INDEX <index> on the sift_col index of such a table, and SELECT sift.col_verify('<index>', true) returns no row for a good index. A database from 4.0.2 needs nothing else.
  • Coming from 4.0.0 to 4.0.2 and loading with COPY into a sift_table: nothing to do after the update; a load that failed with invalid memory alloc request size is simply run again.
  • Going back to 4.0.2 after ALTER EXTENSION pg_sift UPDATE: docs/UPGRADING.md, “An older release”.
  • Still as before (not fixed here): DROP INDEX CONCURRENTLY of an index with track_changes and pg_repack --only-indexes fail with must be first action in transaction (run ALTER INDEX <index> SET (track_changes = 'off') first); after ENABLE TRIGGER ALL following a DISABLE TRIGGER ALL (what pg_restore --disable-triggers does), and after REINDEX CONCURRENTLY the reads of the table go to the heap, which is slower and not wrong, until a plain REINDEX INDEX <index>. A table that was attached (ATTACH PARTITION, INHERIT) before 4.0.2 and written through its parent needs REINDEX INDEX <index> at its DETACH PARTITION or NO INHERIT: its log is stale after the detach and a grouped read can count rows that were deleted through the parent. A table attached by 4.0.2 or later ends its log at the attach, and the reads check the heap until that REINDEX.
  • Nothing else changes: plans, speeds and answers outside the cases above are those of 4.0.2.

Tests

col_build_par_choice (the choice of the participants of a parallel build), col_keycols_rows (the rows the defaults count), col_track_shared_probe (one probe for the triggers of a table), agg_topn_offset_skip (the OFFSET of the top-N scan), agg_part_open_cost, and col_choices_summary and col_logical_apply (the NOTICE and sift.index_choices; a logical subscriber keeps sift_col and sift indexes). The update paths 3.7 to 4.0.3 and 4.0 to 4.0.3 and a fresh install are in extension_lifecycle; a cluster written by 4.0.2 and by 4.0.0 is upgraded with pg_upgrade and updated in pg_upgrade, pg_upgrade_31, pg_upgrade_32 and pg_upgrade_33 (a cluster of 3.x, with an index named after the key of its table), and col_track_old_extension (the log of an index made while the extension is below 4.0) runs on every major.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A replacement of 4.0.2, 4.0.1 and 4.0.0 with one step after the install: extension SQL 4.0.3 (pg_sift--4.0--4.0.3.sql, two functions); column format minors 3 to 42 are read and written as in 4.0.0, BM25 metapage minors 1.0 to 1.5. The SQLite and MariaDB extensions: no change since 4.0.0.

Packages

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

  • 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_4.0.3-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-4.0.3-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-4.0.3-1.amzn2023.x86_64.rpm (.aarch64.rpm on arm64)
  • Arch Linux: AUR pg_sift.

Every change: CHANGELOG.md.

SQLite extension

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

  • No change: the changes of this release are in the PostgreSQL column index, which is 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-4.0.3-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 4.0.3 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 4.0.3 release: no change to the SQL files.

  • No change: the changes of this release are in the PostgreSQL column index, which is 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.4.0 to 3.7.0 opens as it is, nothing to rebuild.

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