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 ` 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](https://github.com/brauliobo/pg_sift/blob/v4.0.3/docs/messages.tsv) (the one source of [docs/MESSAGES.md](https://github.com/brauliobo/pg_sift/blob/v4.0.3/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](https://github.com/brauliobo/pg_sift/releases/tag/v4.0.1) and [4.0.2](https://github.com/brauliobo/pg_sift/releases/tag/v4.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 ` on the `sift_col` index of such a table, and `SELECT sift.col_verify('', 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](https://github.com/brauliobo/pg_sift/blob/v4.0.3/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 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 `. A table that was attached (`ATTACH PARTITION`, `INHERIT`) before 4.0.2 and written through its parent needs `REINDEX 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](https://github.com/brauliobo/pg_sift/blob/v4.0.3/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](https://github.com/brauliobo/pg_sift/blob/v4.0.3/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.3/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:` 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//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](https://github.com/brauliobo/pg_sift/blob/v4.0.3/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.0.3/mariadb/CHANGELOG.md).