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.

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. Every change: 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. Every change: mariadb/CHANGELOG.md.