pg_sift 3.6.1: a parallel `CREATE INDEX` or `REINDEX` no longer leaves an index file at twice its live size when a standby streams (at the price of a heavier build, see "Upgrade note"), three statement shapes that 3.6.0 planned as PostgreSQL's plan (a semi, anti or inner join by an int4 key against a table of keys, `GROUP BY` over 400 hash partitions) are planned as the scan again, `EXPLAIN (VERBOSE)` of a top-N with window totals works, and the default `point_tid_columns` no longer lists a timestamp. Update with the files of this release and restart the server; no `ALTER EXTENSION` step, and `REINDEX` only if you want smaller indexes (see "Upgrade note").

## Highlights

- **Changed: a parallel build merges its own segments into a gap it reserved and cuts the file; the file ends at the live size.** The segments of the participants of a parallel `CREATE INDEX` or `REINDEX` with `key_cut` overlap (each holds some rows of every key), so 3.6.0 left them to the first `VACUUM`, which wrote the index again after them: the file ended at twice the index, and stayed there while a physical standby streams (a cut stalls the replay behind a long query of the standby). The first fix of this release rewrote the segments in the build and then moved them down, which cost 43.9 CPU seconds and 325 MB of WAL for the build (below). The build now reserves a gap in front of its segments, sized from the statistics of the table (a tenth over their estimate of the index), merges the segments into it after the publish, releases the gap and cuts the free end; the relocation copy is gone. The file of a build belongs to the transaction, so no query of a standby can hold it, and the cut waits for nobody. A gap too small leaves the rest of the output above the free blocks, which the space step brings down; a build without `key_cut` or with no worker launched reserves none; there is no format change and an index built by 3.6.0 reads and vacuums as before. Measured on 3,000,000 rows, 13 columns, `key_cut`, 8 workers, one streaming standby, PostgreSQL 18.6, one session, other load on the machine (CPU seconds are the figure to read, wall time is not):

|  | `REINDEX` CPU | wall | WAL | file / live after it | first `VACUUM` CPU, WAL | file / live after it |
|---|---|---|---|---|---|---|
| 3.6.0 | 18.9 s | 9.3 s | 104 MB | 104 / 103 MB | 8.9 s, 101 MB | 206 / 99 MB (2.08x) |
| first fix | 43.9 s | 32.4 s | 325 MB | 115 / 115 MB | 0.02 s, none | 115 / 115 MB |
| gap (this release) | 39.3 s | 30.3 s | 255 MB | 115 / 115 MB | 0.03 s, none | 115 / 115 MB (1.0x) |

**The price:** the build writes every page twice (the participants, then the merge, which is serial in the leader): 39.3 CPU seconds where 3.6.0 took 18.9 and 255 MB of WAL where it wrote 104; with the first `VACUUM` that used to follow, 3.6.0's work is 27.8 s and 205 MB, so the build costs +41% CPU and +24% WAL for a file at 1.0x. The gap is an estimate and overshoots on a wide table: 202 MB reserved for 115 MB of index here (1.8x, the statistics overstate its json columns), so the file peaks near 2.8x the index while the build runs and ends at 1.0x. A build with no streaming standby gets the same compact file; `REINDEX CONCURRENTLY` behaves as before. 10,000,000 rows were not measured. Test: `test/t/col_build_standby_space.sh`.
- **Fixed: key-only lookup joins were planned as PostgreSQL's hash join.** `k IN (SELECT ..)`, `EXISTS`, `NOT EXISTS` and an inner join by an int4 column against a table of 550,000 keys (the cases `keys_subq_500k`, `keys_exists_500k`, `keys_join_500k`, `keys_semi_load_first`) priced the table of keys with its dictionary and the probes of the keys as hash probes, so the scan lost to the normal plan on cost. They are priced as the keys alone (10 operators a row) and a byte-table probe (5 operators a tuple): the scan of 1,000,000 rows that joins the table costs 25,600 where it cost 77,300 against 32,300 for the normal plan, and it takes 7 ms and 23.2 MB read cold where the normal plan takes 149 to 160 ms and 84.5 to 85 MB. The table of keys is also loaded without its key dictionary and row chains: 35 ms where it took 59 ms. Tests `agg_keys_plan`, `agg_lookup_keys_only`.
- **Fixed: `partitions_scan`.** Since 3.5.0 a partitioned table of 400 hash partitions whose indexes keep different key tables (`key_columns = 'auto'` follows the data of each partition) was refused with `partition_source` and ran PostgreSQL's plan. The scan answers with the measures every partition holds: `GROUP BY country` over 400 partitions of 2,500 rows takes 8.9 ms and 26.7 MB read cold where the normal plan took 88 ms and 108.4 MB. Test `agg_part_presence`.
- **Fixed: `EXPLAIN (VERBOSE)` of a top-N with window totals.** `EXPLAIN (VERBOSE)` and `EXPLAIN (ANALYZE, VERBOSE)` of a SiftAgg top-N that computes `count(*) OVER ()` (the page of a list and its total) raised `could not find window clause for winref 1`. Test `agg_topn_window`.
- **Fixed: the default `point_tid_columns` listed a timestamp column, and its growth estimate was wrong.** A date or timestamp column is no longer in the default (an equality on one is rare, its reads are ranges and an order), and the NOTICE predicts the growth from the values the index stores, not from the width of the documents of a jsonb column. A 13-column index with paths of 1,000,000 rows said +12% and grew 3.0 times; it now lists the id alone and says +44%. Tests `col_point_auto`, `col_gorder_auto`.
- **Fixed: a planner crash (signal 11) on a statement with a flattened lateral and shared CTEs.** When the verdict cache held the verdict of another size of request than the one planned, the planner checked the plan of the form that won against the verdict of its size band and then discarded the memory context of the form that band named, which is never planned when the band names the other form: a wild pointer in `plan_flat` (`MemoryContextDelete`). It ended the backend in 3 of the first 512 comparisons of the differential corpus of analytic statements. The planner now discards the context of the plan it made.
- **Fixed: an out-of-bounds read of four bytes when an element predicate that tests two keys is pushed down.** The element test of an aggregate (`EXISTS` over the elements of an array column, `@>`) indexed the leaves of the layout with the leaf number of an `AND` child that has none (-1): a read of four bytes before the array, in 3.6.0 only (`sift_aggspec_elem_test`, `src/agg/spec.c`). It was found by the sanitizer run of the deep nightly tier (a heap-buffer-overflow under AddressSanitizer). No wrong result was found; the test now declines such a predicate. Test `agg_elem_test_leaf`.

## Upgrade note

- **No format change.** Column format minors 3 to 32 are read and written as in 3.6.0, the extension SQL stays 3.6 (`ALTER EXTENSION pg_sift UPDATE` has nothing to do and there is no `pg_sift--3.6--3.6.1.sql`). Install the files and restart the server (a standby too), or reconnect every pool: a backend that already loaded the old library keeps it until its connection ends.
- **Existing indexes keep their size until `REINDEX`.** An index built by 3.6.0 keeps its point directory for date and timestamp columns and any free space the build left; the plans and answers are those of the new planner as soon as the library is loaded. A new `CREATE INDEX` or `REINDEX` writes no point directory for a date or timestamp column unless `point_tid_columns` names it (smaller, and the NOTICE says what it chose), and with a standby streaming it cuts the file itself. Nothing is required: `REINDEX` only if you want the smaller file.
- **The `keys_*` numbers.** The README and benchmark rows for `keys_semi_load_first` (and the other `keys_*` cases) were measured on the 3.7 development tip with these fixes; the fixes in this release are the same code.
- **Behaviour change: expect a heavier `REINDEX` or `CREATE INDEX` of a `key_cut` index.** 39.3 CPU seconds and 255 MB of WAL where 3.6.0 took 18.9 s and 104 MB (3,000,000 rows, 13 columns; +41% CPU and +24% WAL against 3.6.0 with the first `VACUUM` that followed it), in return for the file at the live size and a first `VACUUM` that rewrites nothing. The file peaks near 2.8x the index while the build runs on a wide table. Each standby must replay the extra WAL, and a non-concurrent `REINDEX` holds its locks that long. `REINDEX CONCURRENTLY` is unchanged.

## Who is affected and what to do

- **The file after a parallel build with a standby:** 3.6.0 with a streaming physical standby. `REINDEX` after the update gives a file of the live size.
- **The three plans:** 3.6.0 (the partitions one since 3.5.0). The rows were right; the statements were slower. `SET sift.enable_agg = off` is PostgreSQL's plan, as it was.
- **`EXPLAIN (VERBOSE)`:** 3.6.0, with a window function in a top-N the scan answers.
- **The out-of-bounds read:** 3.6.0, a statement with an element predicate on two keys over a `sift_col` index with an array column. A release build read four bytes of the memory before the array and gave the rows it should; a build with the sanitizers aborts.
- **PostgreSQL 19: a `NOT IN` sub-select inside a multi-arm CTE keeps PostgreSQL's plan** (a close call: the inline scans are priced 1531 against 1359 for the shared plan, which PostgreSQL 19rc1 prices 36% under PostgreSQL 18's 2123 because it estimates one row from the anti join). The rows are equal, and the plan is slower than the scans: warm, `EXPLAIN (ANALYZE, BUFFERS)` on PostgreSQL 19rc1, the statement of the test `agg_cte_hoist` with `sift.agg_force = on` (the scans) against PostgreSQL's plan (`sift.enable_agg = off`): 30,000 rows 2.7 to 5.5 ms against 67 to 97 ms, 300,000 rows 21.4 ms against 459 ms, 1,500,000 rows 117 to 129 ms against 2,497 ms (15 to 25 times). `SET sift.agg_force = on` takes the scans. PostgreSQL 17 and 18 are unchanged (their inline scans are cheaper than the shared plan). The key-only probe price is not the cause (a byte-table probe against a 50-key table measures 50 instructions a tuple, above the 5 operators priced); the estimate of the shared plan under a one-row anti join is a 3.7 item.
- **The planner crash:** 3.6.0 (the corpus ends a backend with signal 11 on a build of the tag; `src/agg/cte.c` is identical in 3.6.0 and the tip before the fix); a statement with a flattened lateral and shared CTEs on a table with a `sift_col` index, planned with `sift.enable_agg = on`, when the verdict cache holds another size band's verdict. `SET sift.enable_agg = off` for the role avoids the plan.
- **Nothing else changes:** answers and formats are those of 3.6.0.

## Regression tests

Every case fixed here has a test in the normal suite (`test/sql`, `test/t`) that was run against a build of the tag `v3.6.0` and fails there, and passes on this release. The table, with the observed output, is docs/releases/3.6.1-REGRESSIONS.md.

| Case | Test | On v3.6.0 | On 3.6.1 |
|---|---|---|---|
| Default `point_tid_columns` with a timestamp | `col_point_auto`, `col_gorder_auto` (in `installcheck-batch3`) | fails (`auto: rid, ts` against `auto: rid`) | passes |
| Key-only lookup joins priced as hash joins | `agg_keys_plan` (plan), `agg_lookup_keys_only` (rows) | `agg_keys_plan` fails (`normal` instead of `scan`); `agg_lookup_keys_only` passes by design: it checks the rows of the changed executor | passes |
| `partitions_scan` refused | `agg_part_presence` | fails (`partition_source` instead of `scan`) | passes |
| Free space after a parallel build with a standby | `col_build_standby_space` (TAP) | fails (after the first `VACUUM` the file is 2,990,080 bytes for 1,294,336 live, 2.31x; the test allows 1.1x) | passes |
| `EXPLAIN (VERBOSE)` with window totals | `agg_topn_window` | fails (`could not find window clause for winref 1`) | passes |
| Element test without a leaf | `agg_elem_test_leaf` | fails under `SANITIZE=1` (UBSan: `load of value 128, which is not a valid value for type 'bool'`; the same read is a heap-buffer-overflow under AddressSanitizer) | passes |
| Order of tied keys of a page (a contract, not a change) | `agg_topn_ties` | passes by design | passes |

These ran on PostgreSQL 18; the outputs on 17 and 19 are those of the Full-scope CI run of the release.

## Compatibility

- PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with `make USE_PGXS=1`). A drop-in replacement of 3.6.0: column format minors 3 to 32 are read, extension SQL 3.6, no setting changes its default. The SQLite and MariaDB extensions: no change since 3.6.0. `pg_get_loaded_modules()` on PostgreSQL 18 says `3.6.1+<commit>`; the extension version stays `3.6`.

## Packages

16 packages are attached below (`SHA256SUMS` covers every asset): x86-64 and arm64 `.deb` and `.rpm` for PostgreSQL 17, 18 and 19 (12), and the Amazon Linux 2023 `.rpm` for PostgreSQL 17 and 18 on both architectures (4); other versions build from source with `make USE_PGXS=1`. The packages depend on `liblz4` and `libzstd`. The 19 ones are built and tested against PGDG's `19~rc1`. Each package was installed in fresh containers of its architecture, where `CREATE EXTENSION`, an update from 1.0, a `sift` index and its queries ran: the `.deb` in Debian 12, Debian 13, Ubuntu 22.04 and Ubuntu 24.04 (built on Ubuntu 22.04, glibc 2.35), the `.rpm` in Rocky 9, Alma 9 and Alma 10, the Amazon Linux 2023 one in `amazonlinux:2023` (the arm64 ones on a hosted arm64 runner). The arm64 libraries are built for plain ARMv8-A and NEON, and the smoke test of each arm64 package runs `scripts/check-aarch64-baseline.sh` on the installed library.

- 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_3.6.1-1_amd64.deb` (`_arm64.deb` on arm64)
- RHEL, Rocky, Alma 9 and 10 (PGDG `postgresqlN-server`): `sudo dnf install ./pg_sift_N-3.6.1-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-3.6.1-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/v3.6.1/CHANGELOG.md).

## SQLite extension

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

- **No change:** the changes of this release are in the PostgreSQL column index, the aggregate planner and the packages, 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.4.0 to 3.6.0 open as they are, nothing to rebuild.

Download `sift-sqlite-3.6.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](https://github.com/brauliobo/pg_sift/blob/v3.6.1/sqlite/README.md).
Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.6.1/sqlite/CHANGELOG.md).

## MariaDB extension

pg_sift for MariaDB 3.6.1 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.6.1 release: no change to the SQL files.

- **No change:** the changes of this release are in the PostgreSQL column index, the aggregate planner and the packages, 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.4.0 to 3.6.0 opens as it is, nothing to rebuild.

Download `sift-mariadb-3.6.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](https://github.com/brauliobo/pg_sift/blob/v3.6.1/mariadb/README.md).
Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.6.1/mariadb/CHANGELOG.md).