pg_sift 3.6.0: substring, pattern and containment tests run on the encoded dictionary with SIMD kernels, array columns are searched, counted and unnested through the list of their elements, `ORDER BY .. LIMIT .. OFFSET` over a date, a timestamp or a text column starts at the key, a plain `sift_col` index answers the statements that return rows, and the dictionaries a backend parses are shared. Update with `ALTER EXTENSION pg_sift UPDATE` in every database; no `REINDEX` is required, and a `REINDEX` gives an index built before the new layouts.

Numbers below are the measurements of the pull requests (PostgreSQL 18, x86-64, one backend, 1,000,000 rows unless it says otherwise): instructions and milliseconds of the statement, the blocks or buffers it reads where the pull request measured them, and a b-tree, GIN or GiST of the same table in brackets. The benchmark pass of the release, with the time and the cold IO of every case, is under Benchmarks below.

## Highlights

- **Text tests on the dictionary.** `LIKE '%part%'` is tested on the bytes of the dictionary in one SIMD pass (AVX2, AVX-512, NEON) instead of one operator call for each distinct value, and the units whose dictionary lacks the part are not read; `NOT LIKE` and `ILIKE` use the same pass, and so do `LIKE '%suffix'`, `starts_with`, `^@`, `strpos(col, 'part') > 0` and a regular expression that is a literal. Over 1,000,000 distinct URLs, `LIKE '%0e41b2'` 194 to 71 ms (2,821 M to 984 M instructions), `strpos(url, 'item/a') > 0` 153 to 73 ms, a literal regular expression 1,439 to 90 ms (19,588 M to 992 M), a short code `LIKE '%-1A'` 43 to 6.0 ms. A pattern with a `_`, several `%`, a class or a group first finds the literals every match holds (3 bytes or more) and runs the pattern on the strings that hold them: `~ 'shop17\.example\.com/item/[a-c]'` 20,196 M to 1,772 M instructions, 1,000,000 sentences `~ 'the (and|of) it'` 9,540 M to 229 M. A trigram signature for each block of a large dictionary (column format minor 29) lets a substring test skip the blocks that cannot hold the part, and the strings of an inline text column take LZ4 instead of zstd where the default formula says it pays (a substring test on 3,000,000 sentences 683 instructions a row, not 2,371; the index of those sentences takes 54% more bytes). The rows are those of the normal plan.
- **Array columns.** An index over an array column of `text`, `varchar`, `int2`, `int4` or `int8` lists the rows that own each element (`CREATE INDEX` picks the layout from a sample, a `NOTICE` says which), so `arr @> ARRAY['a']`, `&&`, `<@`, `'a' = ANY (arr)`, `x = ARRAY[..]`, `cardinality(x)` and `array_length(x, 1)` read a list and not the arrays. `count(*) WHERE arr @> ARRAY['a']` is the length of the list: 4.6 and 5.0 M instructions (2 ms) to 1.3 M (1 ms) beside GIN's 1.4 and 2.1 M, and cold, 781 and 728 blocks read (6.0 MB) to 57 and 56 (0.5 MB), GIN 373 and 556, the heap 43,436. `arr @> ARRAY['am_3', 'am_100']` 93.8 to 34.4 M instructions (15 to 9 ms; GIN 35.0 M, 7 ms; the heap 2,622 M, 247 ms) for 2.7% more index. `FROM t, unnest(arr) e` is a scan of the list: the 20 most frequent elements 8,421 M instructions (1,064 ms) to 82 M (6.6 ms), `LIKE` over the elements 17,550 M to 59 M (1,835 ms to 8.0 ms). `count(*) WHERE x IS NOT NULL AND cardinality(x) > 0` 136 ms and 1,047 M for the normal plan to 0.91 ms and 10.8 M (the index grows 10% on six array columns). Where nearly every array is different the index keeps the elements and no image: 73.3 MB in place of 123.9 MB (GIN 61.9 MB).
- **Ordered reads: dates, text, groups, OFFSET.** `order_array_columns = 'auto'` now chooses a date or timestamp column by the default formula whatever the width of the index, and can take a text or varchar column (column format minor 32). Random timestamps at 500,000 / 1,000,000 / 3,000,000 rows, instructions of one backend (a b-tree in brackets): `WHERE ts >= x ORDER BY ts LIMIT 10` 9.4 / 18.3 / 30.5 M to 0.38 / 0.39 / 0.42 M (0.19 / 0.21 / 0.20 M); `ORDER BY ts DESC LIMIT 10` 2.83 M to 0.28 M; `OFFSET 100000` 761 / 1,569 / 4,926 M to 46 / 47 / 57 M (b-tree 169 M): an entry before the OFFSET costs about 460 instructions where the b-tree's heap fetch costs about 1,800. In milliseconds, `OFFSET 1000` 0.18 ms (b-tree 0.23 ms, PostgreSQL's sort 31.0 ms) and `OFFSET 100000` 4.2 ms (b-tree 31.0 ms, sort 116.0 ms). `ORDER BY txt LIMIT 10` over 13-byte strings 403 / 806 / 2,429 M to 0.33 / 0.40 / 0.86 M (b-tree 0.22 M). `WHERE grp = x ORDER BY ts LIMIT 10` (the last events of a device) 3.34 M to 0.42 M (b-tree 0.27 M), also for an `IN` list of up to 16 values. A column with ties or NULLs no longer reads the units whose best row only ties with the n-th. The cost: an index of one timestamp column goes from 3.7 to 8.2 bytes a row (a b-tree 22) and `CREATE INDEX` costs 34.5% more instructions; `order_array_columns = 'off'` and a `REINDEX` drops it.
- **Containment of ranges, networks and jsonb.** `@>`, `<@`, `&&`, `<<`, `>>`, `&<`, `&>` and `-|-` on `int4range`, `int8range`, `daterange`, `tsrange` and `tstzrange`, and `<<`, `<<=`, `>>`, `>>=` and `&&` on `inet` and `cidr`, are answered from integer lanes of the column (column format minor 26; the default `key_cut` picks the start of a range or the node of a network): 1,000,000 random ranges `r @> x` 552 ms to 1.5 ms (a GiST 0.15 ms), `ip <<= net` 380 ms to 1.4 ms. A containment that holds in two ways prunes the units by the union of both: `ip && '2001:db8:1::/48'` 35.7 to 15.6 M instructions, 1,008 to 334 buffers, 67 of 92 units not read. `doc @> '{"k": 42, "o": {"a": 7}, "tags": ["t5"]}'` on a jsonb attribute is answered from the leaves of the paths it names: one scalar member 56.5 to 2.0 ms, a nested object 76.5 to 4.7 ms, three members 54.8 to 1.6 ms (GIN `jsonb_path_ops` 1.3, 24 and 26 ms).
- **Shared dictionaries.** The first backend that parses a dictionary of 128 kB or more keeps it in a ring of dynamic shared memory, and the other backends (a pooled connection that was reset, the workers of a scan, the next session) read it in place with its lookup structures: the first statement of a new backend with `ip = const` over 1,000,000 rows takes 56 M instructions instead of 210 M, `count(*) WHERE ip = x/51` over 3,000,000 rows of networks 2.6 M instead of 1,263 M. A dictionary of 1,024 strings or more carries the hash of its strings (column format minor 25), so the first equality of a backend on a uuid column of 3,000,000 rows reads 51 buffers in 5.0 ms where it read 5,977 in 31 ms (a b-tree 4 in 2.8 ms). **`sift.col_dict_shared_cache_kb`** sets the size of the ring: 64 MB by default for every server (4 GB at most, a quarter of `/dev/shm` with posix shared memory), `0` turns the sharing off with the same rows; the memory is in addition to `shared_buffers`, counts in `/dev/shm` and the shared memory of a container, and a change takes effect at the next start.
- **A plain index answers the statements that return rows.** An index created with the default class (`CREATE INDEX .. USING sift_col (id, name, ts)`, no `sift.col_*_ops`) serves `WHERE id = 5`, `IN` lists, ranges, `LIKE 'abc%'` and `IS NULL` on integer, date, timestamp, boolean, float, numeric, `char(n)`, text and uuid columns by an index, index-only or bitmap scan, and so do `UPDATE` and `DELETE` with such a `WHERE` (the update script adds the operators to the family; no `REINDEX`). 200,000 rows, an `int4` key 89.4 M instructions and 14.6 ms to 0.295 M and 0.052 ms (b-tree 0.184 M, 0.062 ms), a 16-key `IN` list 110.1 M to 2.30 M (b-tree 0.391 M); an `int8` lookup of 6 rows a key over 1,000,000 rows 515.9 M and 113.7 ms to 0.94 M and 0.13 ms. A bound of another date type (`d < x + interval '1 day'`, `ts >= current_date`, `now() - interval '30 days'`) is an index qual: a day of 3,000,000 rows 1,476 M instructions and 75,000 buffers to 9.97 M and 1,166 (b-tree 6.15 M), a month 1,576 M to 142.9 M.
- **Keys by default.** `key_cut` is decided by the default formula alone (the benefit of the cut against a third of the predicted growth and a fifth of the growth of the build) and sorts a key by its date leaf too (`auto: key, date`); the integer columns of nearly unique values get the point directory of heap addresses by the same formula. 3,000,000 rows of 56 bytes scattered over 1,000 days, `sift_col (d)`: a day count 25.4 M to 0.7 M instructions (b-tree 1.6 M), a group by day 88.7 M to 3.8 M (b-tree 1,885 M), the index 4.9 to 7.1 MB (b-tree 21.2 MB). An equality on a random `int8` key of a 7-column index of 400,000 rows 1.9 M to 0.2 M (b-tree 0.2 M) for 96% more index (6.50 to 12.74 MB); a 16-key `IN` list over one `int8` column of 1,000,000 rows 95.6 M to 0.49 M (b-tree 0.50 M). The `NOTICE` of `CREATE INDEX` names the column with the benefit and the cost; `key_cut = 'off'` and `point_tid_columns = 'off'` keep the index as it was.
- **Calendar and set filters.** `extract(month FROM ts) = 3`, `to_char(ts, 'YYYY-MM') = '2026-03'`, `ts::date = D`, `date_trunc('month', ts) >= M` and a weekday or an hour narrow the units to the intervals where the field has the value, and a filter on a calendar expression is evaluated once for each day, hour or minute (the weekday over 1,000,000 timestamps of three years 34 M instructions where it took 1,729 M). An `OR` of ranges of one leaf (this week and the same week a year before) is answered from the sorted run of each unit, a list of 32 or more keys is tested with a hash table, and a statement with a list of 19,257 literals compares it with the last one by its bytes (568.9 M to 465.0 M instructions).
- **Aggregates.** `sum` and `avg` of a smallint column plus, minus or times a constant come from the sums of the units (90 `SUM(w + k)` over 3,000,000 rows 1,807 ms to 1.0 ms), those of an `int2` or `int4` expression of several operations are one program for each row (16 sums over 1,000,000 rows 244.5 ms, the heap plan, to 19.1 ms), a numeric sum of near unique values adds the lanes of each row, and `count(DISTINCT x)` sorts its spilled pairs as int8 datums (1,570 M to 1,010 M instructions).
- **Writes, build and maintenance.** A lookup, `UPDATE` or `DELETE` by key finds the rows of the write buffer in their records and keeps what it read between statements (10,000 buffered rows, 6 segments: 45 M instructions to 2.5 M, then 0.28 M where an empty buffer takes 0.25 M; a b-tree 0.17 M), the rows a transaction inserts after its first go in as one WAL record for a page (`COPY` of 50,000 rows 245 to 186 bytes of WAL a row, 6,205 to 3,390 instructions a row on the standby that replays them), `CREATE INDEX` of a table without statistics analyzes the columns of the index only (300,000 rows 3.86 to 0.79 billion instructions), a merge copies runs of rows (-17%) and takes the next row from a heap (7% to 25% fewer instructions), the sorts of keys are radix sorts (-9.5% to -18.8% on the build), and the build of 10,000,000 rows at 64MB `maintenance_work_mem` no longer writes 6,000,000 rows twice (484.6 G to 372.1 G instructions, 16 merges to none). `VACUUM` rewrites a lone segment when the scans of its dirty units would cost more than the rewrite (`dead_rewrite_pct` 0, by cost).
- **Planner.** The scan is priced from what it measures: a comparison of a column 0.5 ns a value (9 ns before), a row handed to a bitmap scan 15 ns (500 before), a lookup table by the rows and the numeric columns it loads, the start of the workers at 1,100 estimate units a millisecond, the memory misses of the normal hash aggregate, and the tuples through the arms of a two-level aggregate. A grouped scan of 100,000 rows no longer starts workers that make it slower (5.6 and 4.7 ms with them, 3.4 and 2.3 alone). `sift.explain_why` answers a statement with no aggregate (does it read a `sift_col` index, and if not `no_source`, `where` or `cost` with both costs). `sift.json_build_object` reads the arguments of a typed call site from the call.
- **Reads that wait on storage.** A scan that waits on storage for its units reads the first pages of the kept units that come next through a read stream, as deep as `effective_io_concurrency` allows (`0` reads nothing ahead); a scan of cached pages never does.

## What a 3.5 user must know

- **`ALTER EXTENSION pg_sift UPDATE` in every database.** It runs `pg_sift--3.5--3.6.sql`, which adds the operators of the typed classes to the family of the default class (`sift.col_ops`): no new object, no `REINDEX`. `sift.version()` says `3.6`; the exact build is in `pg_get_loaded_modules()` on PostgreSQL 18 (`3.6.0+<commit>`). The new library reads every index of 3.5 as it is and starts with the next backend (a restart for `shared_preload_libraries`).
- **What a `REINDEX` (or a merge or rewrite of the segments) adds to an index built before**, and what it does not need: the point directory and the element lists of array columns (the element lists now reach `max(4,096, rows / 64)` rows of a segment), the cardinality and length columns of an array (`array_lengths`), the lanes of a range, `inet` or `cidr` column (an index built by 3.5 or older with such a column needs a `REINDEX` to be answered by the lanes), the hash and the trigram signature of the dictionaries, the global orders of dates, groups and text, and the default `key_cut`. `sift.upgrade_commands()` lists the `REINDEX` that gives older units a section the options would write.
- **New column format minors 25 to 32, written after the update.** 3.6 reads every segment of 3.5 and the older minors. A 3.5 library refuses a segment of minor 25 or more with a clear error (`has a segment of column format 1.25, this build reads 1.3 to 1.24`, SQLSTATE 55000); going back is not a supported path, `sift.upgrade_check('3.5')` lists what it cannot read.
- **Defaults chosen by benefit against cost, per column.** `key_cut` (a date as the second key, the lane of a range or a network, the cut by a date up to the formula), `point_tid_columns` (`auto: col`), `order_array_columns` and `order_array_group` (`auto`), the layout of array columns, `compression` (LZ4 for inline text and dictionary blocks) and the global order of text columns. Each says what it chose and why in a `NOTICE` at `CREATE INDEX`, and each has an off switch (`key_cut = 'off'`, `point_tid_columns = 'off'`, `order_array_columns = 'off'`, `order_array_group = 'off'`, `paths = ''`, `compression = 'auto; col=zstd'`). The index of a table whose date column was out of the room before grows; the figures are in the highlights and in `docs/OPTIONS.md`.
- **New and changed settings.** `sift.col_dict_shared_cache_kb` (64 MB, `0` turns the sharing off) takes shared memory in addition to `shared_buffers`: size it for a container's `/dev/shm`. `dead_rewrite_pct` is 0 (the cost decides) where it was 10; `ALTER INDEX .. SET (dead_rewrite_pct = 10)` keeps the old rule. `sift.stats()` counts `dict_shared_hits`, `dict_shared_misses` and `dict_shared_held`.
- **`EXPLAIN` text prints the counters of the custom scans as a few lines** (`Units: 384 read, 64773 pruned (2 by set summary)`, `Rows: 27819 scanned, 183 aggregated, 17 groups`) in place of one line for each counter; a counter that is zero is left out unless `EXPLAIN (VERBOSE)`. `FORMAT JSON`, `YAML` and `XML` keep every field with its name and type: a reader of the text lines (`Rows Scanned: 0`) reads the JSON.
- **`VACUUM` does not cut the free end of an index file while a physical standby streams from the server.** The standby replays the `AccessExclusiveLock` of the cut first and waits for every query that holds the relation, up to `max_standby_streaming_delay`, then cancels it (`hot_standby_feedback` does not prevent it): with a 45 s query on the standby the replay of one `VACUUM` waited 30.1 s and the query was cancelled, against 0.0 s now. The free blocks stay in the file for the next rewrite and `REINDEX` returns them; without a standby nothing changes.
- **Plans can change:** the prices of a scan, of the workers and of the lookups moved; `sift.explain_why(query)` says why a statement took or refused the scan, and `sift.enable_agg = off` restores PostgreSQL's plan.
- **The fixes of 3.5.1, 3.5.2 and 3.5.3 are in 3.6.0:** the crash of a parallel aggregate with a correlated lookup (`LEFT JOIN LATERAL (..) LIMIT 1`) when no worker could be launched, the crash loading a lookup table with a large text dictionary, wrong groups with several wide columns, and the crash and wrong rows of `col = ANY ($1)` with a NULL or packed array parameter (`sift.enable_agg = off` was the workaround).

## Fixes

- A bound of another date type (`d >= now() - interval '30 days'`, `ts >= current_date`) was not an index qual and scanned the table, and the planner priced the rows a `sift_col` index hands over at 0.5 units each and the values it filters at 0.009: a window of a twentieth of a table took the sequential scan (`d >= current_date - 30` on 3,000,000 rows 1,938 M instructions and 75,000 buffers to 412 M and 4,889, b-tree 405 M). A statement that aggregates and reads a column the index of the default class does not hold can use the index or bitmap scan of its filter (a month of 3,000,000 rows 1,470 M and 75,000 buffers to 54.9 M and 3,104, b-tree 53.8 M).
- A probe keyed by a parameter of the parent query (`EXISTS (SELECT .. WHERE t.k = outer.k) OR EXISTS (..)`) was chosen over the hashed subplan and ran for each of thousands of outer rows: a TPC-DS customer query took 2.9 s with the index against 92 ms with `sift.enable_col_scan = off`; it costs at least 90% of the sequential scan now.
- A scan that groups by a float key with a short mantissa (`1.5`, `100.0`) probed its table at bits the hash leaves constant, so 1,000 groups shared a handful of slots; a grouped scan by expressions of an integer column with as many values as rows, a lookup table that is a plan, the probe and the `ON` clause of a lookup join, a numeric `CASE` over a lookup column, a shared CTE read by scalar subqueries and an unnest over a lookup without statistics were priced at a fifth of their cost or less and chosen over the normal plan (3 to 11 times slower); they are priced for their rows. (The lookup joins by 550,000 keys are the one case that is not: see Known limitations.)
- `VACUUM`'s space step left the file of an index at twice its bytes after a merge whose output was larger than the blocks its inputs had freed; the automatic rewrite of an index waited for the WAL budget even for the segments of its own parallel build.

## Benchmarks

Every published benchmark was measured again on this release (PostgreSQL 18.6, an x86-64 host, 1,000,000 orders unless it says otherwise; build `c766747c0` plus a change of the benchmark tool only, `src/` identical; "cold" is the first run after the case's tables were emptied from shared buffers).

| Measured | pg_sift 3.6.0 against PostgreSQL 18 (or the system named) |
|---|---|
| Time, 200 cases, where the planner chooses the scan | faster on 173 of 187 numbers (1.2x to 2,366x), a tie on 3, slower on 11 (below) |
| IO, cold, MB read, 196 cases | fewer MB on 168 (1.1x to 4,805x less), about the same on 26, more on 2 (`count_distinct_lookup_pairs`, `group_many_groups`, 1.1x each) |
| IO examples | `topn_page_totals` 108 MB / 13,842 blocks to 9.28 MB / 1,188; `window_json_report` 107 MB / 13,696 to 6.21 MB / 795 |
| Store, 7 questions | the fastest system (pg_duckdb included) or within 5% on 7 of 7 warm and 4 of 7 cold; 1.2x to 286x faster than PostgreSQL. Cold, not the fastest on `point-lookup` (PostgreSQL 1.5x), `average-shipped-order` (ClickHouse 1.3x) and `big-recent-orders` (ClickHouse 2.1x) |
| Search, autocomplete on 34,191 places | pg_sift 0.16 to 0.22 ms in every query class, Elasticsearch 0.13 to 0.31 ms, pg_search 0.5 to 1.3 ms; ahead of pg_search in 36 of 36 warm cells (2.1x to 12x) |
| Search, `positions = off` | index 12 MiB (4.2x smaller than pg_search's 50 MiB, the default pg_sift index is 30 MiB); phrase 7,590 queries/s against 2,840 |

The 11 numbers where the scan is slower than PostgreSQL's plan on the same rows: `bulk_copy_wal` (time 2.0x, CPU 1.9x), `filter_few_rows` 1.5x, `verdict_shared_report` 1.5x, `tbl_update_keys` 1.3x, `oltp_update_index` (time 1.3x, CPU 1.2x), `oltp_insert_index` (time 1.1x, CPU 1.2x), `keys_subq_500k` 1.2x and `keys_exists_500k` 1.1x (the last two are the plan fallbacks under Known limitations).

The growth with the size of the table was measured again for the 26 headline cases at 100,000, 300,000, 1,000,000 and 2,000,000 orders (`cases-scale.tsv`; the pg_sift slope stays below PostgreSQL's in the README tables). The release differs from the measured build by one change of 3 lines in the write buffer (`src/col/wbuf.c`), outside the measured paths. Not measured again for this release, and marked so in the results: the run at `work_mem` 4GB (`cases-wm4g.tsv`, pg_sift 3.3), the `sets` and `extras` items, and the write-cost table (measured on 2026-10-28 with a rig that is not in the repository). The highlights above are the measurements of their pull requests; they were not repeated in this pass. Eight cases of the benchmark registry named counters in their expected plan that the compact `EXPLAIN` text no longer prints; their expected plans were corrected in this pass, and the scan answers them (`text_key_filtered`, now a scan: PostgreSQL 26.3 ms and 108 MB cold, pg_sift 2.2 ms and 7.6 MB).

## Known limitations in 3.6.0

- **Four cases run PostgreSQL's plan where an earlier release used the aggregate scan, and are not wins.**
  - **Three lookup-join shapes of the benchmark** (`keys_subq_500k`, `keys_exists_500k`, `keys_join_500k`: a semi, `EXISTS` or `IN` join by a table of 550,000 keys) run PostgreSQL's plan in 3.6 and tie it, instead of the scan they took before the planner change of  (51 to 56 ms and 23 MB read cold; now 152 to 232 ms and 85 MB against 144 to 189 ms for PostgreSQL's plan). The price of loading the lookup table is above PostgreSQL's parallel plan for that size, and the scan is not chosen; a calibration of that price is planned for 3.7. The two other cases of the family (`keys_in_wide_500k`, `keys_in_narrow_500k`) still use the scan and are 1.2x and 1.3x faster, reading 11x and 16x fewer blocks cold.
  - **A table of 400 small hash partitions** (`partitions_scan`): in pg_sift 3.5.0 and later (, a larger automatic key filter), a partitioned table of 400 small hash partitions built with the default `key_columns = auto` is planned as PostgreSQL's plan, not the aggregate scan. `GROUP BY` over 400 partitions of 2,500 rows takes 88 ms, a tie with PostgreSQL (88 ms, 108 MB read cold), where 3.4.2 took 15 ms (27 MB cold). An index created with `key_columns = 'off'` brings the scan back (cost 2088 against 36373). A fix is planned for 3.7.
  - `sift.explain_why(query)` says why a statement refused the scan, and `SET sift.enable_agg = off` restores PostgreSQL's plan for it.
- **A trade-off, not a slowdown:** `topn_from_units` now fetches 10,000 heap rows and reads 225 buffers where it fetched none and read 20, for 1.16 ms where it took 1.65.

## Compatibility

- PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with `make USE_PGXS=1`). Column format minors 3 to 32 are read (3.5.x reads up to 24). Extension SQL 3.6 (`pg_sift--3.5--3.6.sql`). The SQLite and MariaDB extensions: no change since 3.5.3.

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

## SQLite extension

pg_sift for SQLite 3.6.0 (`sift`, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.6.0 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.5.3 open as they are, nothing to rebuild.

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

## MariaDB extension

pg_sift for MariaDB 3.6.0 (`sift`, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.6.0 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.5.3 opens as it is, nothing to rebuild.

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