pg_sift 3.2.0: far more aggregate shapes answered by the `SiftAgg` pushdown (lookup joins kept between queries, `UNION ALL` arms in one pass, partitioned tables of up to 4,096 partitions, `DISTINCT ON`, outer joins), more pre-aggregates, a cheaper `sift_table` write path and maintenance without `VACUUM`. The extension SQL goes from 3.1 to 3.2: install the new files, then run `ALTER EXTENSION pg_sift UPDATE TO '3.2'` once per database (from 3.0 the same command runs both steps). No `REINDEX` is needed; a 3.1.x library refuses some column segments 3.2 writes (see Compatibility).

## Highlights

- **Lookup joins in the aggregate pushdown.** A backend keeps the rows of the tables a `SiftAgg` scan joined (`JOIN`, `IN (SELECT ..)`) for its next query while the table is unchanged and vacuumed (`sift.agg_lookup_cache_kb`, 256 MB per backend, 0 disables; `Cached Lookup Tables` in `EXPLAIN ANALYZE`). A key or a column value that repeats is stored once: a 500,000-row mapping table keyed by three text columns takes 12 MB instead of 76 MB, and a probe allocates the rows its key matches. Parallel workers read the tables from shared memory (`Lookup Rows Shared`) instead of each loading a copy, so joins of sub-selects, `VALUES` lists and functions now run with workers. A correlated `LEFT JOIN LATERAL (.. WHERE t.a = x LIMIT 1)` over a unique key is read as a lookup table (`First Row Lookup Tables`; three keys over a 100,000-row table and 60,000 facts: 47 ms against 269 ms in the normal plan). Inner and semi joins test their integer, `int8` and wide-range keys against the loaded table before tuples are made (`sift.agg_join_filter`), and an inner join that returns no column of the dimension is answered from a byte table of the keys when no key of the kept rows repeats.
- **Aggregate shapes.** `UNION ALL` arms that read the same rows run in one pass over the index when they ask for other aggregates, differ in their `WHERE`, join the same lookup or the same common table expression, have no `GROUP BY` (a total beside its breakdowns) or have more than four keys together (`sift.agg_fuse = off` runs them alone; three arms over a lookup join, 100,000 documents: 18.7 ms arm by arm, 8.6 ms in one pass). New: `count(DISTINCT x)` of the per-row group key under an outer aggregate (`Custom Scan (SiftAggRows)`, `sift.agg_rows`), `DISTINCT ON` over a unique `NOT NULL` index, `RIGHT` and `FULL JOIN` and a `LEFT JOIN` whose right side is the indexed table, lateral sub-selects, `UNION` arms, `generate_series` and `unnest` that read the joined row, `EXISTS` over the elements of an array with a lookup per element, `GROUPING SETS`, `ROLLUP` and `CUBE` over the rollups of a list, `ctid` range predicates, partial `sift_col` indexes whose predicate the query implies, and the planner now prices the scan at the speed it runs, so shapes that were 8 to 35 times faster as a scan but lost the cost comparison take it. `sift.enable_agg = off` restores the normal plan.
- **Partitioned tables: up to 4,096 partitions, pruned by value.** `SiftAgg` reads a partitioned table whose partitions carry the same `sift_col` index. A parameter of a generic plan or a stable function (`d >= $1`, `k = ANY ($1)`, `now() - interval '30 days'`) prunes the partitions when the scan starts; a table split by hash drops the partitions whose remainder the values cannot have; the keys of an inner or semi join drop the partitions none of them can be in (`Partitions Removed`). The limit went from 1,024 to 4,096 partitions: the scan opens only the partitions pruning leaves (3,000 partitions pruned to one: 92 ms to 9.5 ms of execution) and releases the buffers of a partition when it moves to the next.
- **Pre-aggregates.** The default cube keeps up to eight measures of each dim (it kept three; index size on a ten-column table +0.0% to +1.6%). `sum` and `avg` of an `int8` column plus, minus or times a constant are answered from the sums of a unit with PostgreSQL's own `bigint out of range` error; an aggregate with `FILTER (WHERE ..)` on the dim the query groups or filters by is answered from that dim's histogram; the rollups of the elements of a list answer `FILTER` on dims, functions of one dim (`left(day, 7)`, `CASE` bands) and grouping sets. A unit with a rollup is answered from it only when its tuples cost less than its elements (`sift.agg_rollup_cost`), so a rollup that does not pay never slows a query. Units are pruned by an `OR` of zone tests, and a top-N scan on a key that does not follow the table walks the superblocks by the best bound of their zones (`sift.topn_walk`, default on): `LIMIT 10` runs 2.3 to 6.4 times faster for a key in blocks and 3.1 to 3.4 times for a random key.
- **`sift_table` and index writes and maintenance.** A write buffer append and a tombstone log the changed bytes of the page instead of copying and comparing the 8 kB page for the generic WAL record (same record, replayed as before): `INSERT .. SELECT` of 200,000 rows into a `sift_table` 320 ms instead of 366, a loop of 100,000 single-row `INSERT`s 312 ms instead of 354, `DELETE` of 10% of the rows 14% faster. The table directory is read again only when its version changed. `sift.table_dead_rewrite_pct` defaults to 30 (was 50): a table with scattered deletes keeps a file near 1.4 times its live rows instead of up to 2 times. `VACUUM` of `sift_col`, `sift_table` and `sift` returns internal free space to the operating system by moving tail segments into free runs (`vacuum_truncate = false` disables it; `sift.vacuum_rewrite_limit`, default 128 MB, caps what it rewrites). New, opt-in and off by default: `sift.col_auto_merge` (a background worker merges a `sift_col` index that churn scattered, after the `VACUUM` that finds it so) and `sift.table_auto_defrag` (a `sift_table` is defragmented without `VACUUM`); `sift.table_maintain(tbl)` and `sift.col_maintain(index)` apply the rules from a session or a scheduled job.
- **`sift_table` paths and more column types.** `sift.table_set_paths(table, column, spec)` declares typed `jsonb` paths of a `sift_table` (the `paths` grammar of `sift_col`), a stored generated column is read as the expression it was written from, and a `sift_col` index accepts `numeric` of any precision, `inet`, `cidr`, `macaddr`, `macaddr8`, `timetz`, `bit` and `varbit` columns, and a `sift_table` reads `numeric` above 18 digits, `interval`, `uuid`, `bytea`, `inet`, array and range columns through a dictionary of their values. `sift_table` returns the real `xmin`, `xmax`, `cmin` and `cmax` of a row. `col @@> ANY (array)` is scanned and ranked as the `OR` of its elements, and `sift.score()` compared with a constant in `WHERE` is applied in the ranked scan.
- **Correctness and checks.** Queries that read a value a `sift_col` leaf cannot store (a numeric string in a number path, a document that is an array, a JSON `null` against a missing key) read those rows again from the heap and answer, errors included, as the normal plan does, and where they cannot prove it they keep the normal plan; `EXPLAIN ANALYZE` shows `Rows Read Again From The Heap`. Column format minor 6 fixes a wrong answer after merging a minor-3 segment into minor 4 (see Compatibility). Page, chunk and segment checks of the write buffers, tombstones and bitmap chunks are stricter: a structure that reaches past its page is a corruption error, and `VACUUM` keeps the writes that follow a failed relocation.

## Compatibility

- PostgreSQL 17 and 18; PostgreSQL 19 is experimental (CI builds it from git on every push and it passes the same jobs).
- **Extension SQL:** 3.1 to 3.2 (`pg_sift--3.1--3.2.sql`): the table `sift.table_paths` (declared in the dump) and `sift.table_set_paths`. Run `ALTER EXTENSION pg_sift UPDATE TO '3.2'` in every database after installing the files; from 3.0 the update runs 3.0 to 3.1 to 3.2.
- **Formats** (`docs/FORMATS.md`): a `sift_col` index writes column format minor 6 for declared JSON paths, which guarantees their complete JSON kinds (it fixes a wrong answer after merging a minor-3 segment into minor 4, where a JSON `null` could become a missing key), and minor 5 for wide leaves (`numeric` of any precision, `money`, `interval`, `uuid`, `bytea`, arrays, ranges). Minors 3 to 5 and old write buffers stay readable; `REINDEX` and `VACUUM (FULL)` of a `sift_table` rebuild the kinds from the documents. The BM25 segment format (3) and the `sift_table` metapage are unchanged. Schemas without JSON paths keep their previous minor.
- **A 3.1.x library refuses a column segment of minor 5 or 6** with a format error and a `REINDEX` hint. Install 3.2 on every server that opens the data, standbys included, before the first write. To keep new narrow segments readable by 3.1, set `sift.col_format_minor = 4` (3 keeps the 3.0 layout; wide leaves still need at least minor 5).
- **Going back to 3.1.x:** there is no 3.2 to 3.1 extension script. A `sift_col` index written at minor 5 or 6 is refused by the 3.1 library until it is rebuilt: `REINDEX` it with `sift.col_format_minor = 4` (indexes with wide leaves cannot be read by 3.1), then install the 3.1 files.
- **Defaults that change:** `sift.table_dead_rewrite_pct` 50 to 30 (`SET sift.table_dead_rewrite_pct = 50` restores it); `VACUUM` returns internal free space and promotes filled write buffers (the index option `vacuum_truncate = false` turns the first off; `sift.vacuum_rewrite_limit = 0` keeps one merge step and disables the page relocation); the planner takes `SiftAgg` for more shapes and each backend may keep up to 256 MB of lookup rows (`sift.agg_lookup_cache_kb = 0`) and 64 MB of parsed dictionaries. **Off by default:** `sift.col_auto_merge`, `sift.table_auto_defrag`, and the Vulkan backend (`make VULKAN=1`, experimental, from 3.1).
- Plans change, results do not: `sift.enable_agg = off` restores the normal plan, and each new pushdown has a switch (`sift.agg_fuse`, `sift.agg_join_filter`, `sift.agg_share_lookups`, `sift.agg_two_level`, `sift.agg_rows`, `sift.agg_semi_lut`, `sift.agg_key_skew`, `sift.topn_walk`).
- **Known gaps:** a table split by hash tested with anything but an equality, `= ANY` or a stable function of the key, a partition without the index or with other options, an inheritance parent and `ONLY` keep the normal plan; so do `ctid` `<>` and expressions and a `DISTINCT ON` whose `ORDER BY` has no unique `NOT NULL` index; a `FILTER` on two dims or on a measure is scanned, not answered from the pre-aggregates. `sift.table_auto_defrag` clears no index entry; `VACUUM` still removes the tombstones and the index entries.

## 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.2.0-1_amd64.deb`
- RHEL, Rocky, Alma 9 and 10 (PGDG `postgresql18-server`): `sudo dnf install ./pg_sift_18-3.2.0-1.el9.x86_64.rpm`
- Arch Linux: AUR `pg_sift`.

Every change: [CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v3.2.0/CHANGELOG.md).

## SQLite extension

pg_sift for SQLite 3.2.0 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.2.0 release: no change to the storage format or the SQL interface; the source files of the extension and of the shared core were renamed and split.

- **No functional change claimed:** the other changes of this release (the aggregate pushdown, the column engine, the `sift_table` write path and maintenance) are PostgreSQL features and are not in this extension. No speed-up or slow-down is claimed for it.
- 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.1.0 open as they are, nothing to rebuild.

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

## MariaDB extension

pg_sift for MariaDB 3.2.0 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.2.0 release: no change to the index format or the SQL files; the source files of the extension and of the shared core were renamed and split.

- **No functional change claimed:** the other changes of this release are PostgreSQL features and are not in this extension. No speed-up or slow-down is claimed for it.
- 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.1.0 opens as it is, nothing to rebuild.

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