pg_sift 3.5.0: point lookups by a foreign key or a unique id read one record of a segment or a few units, a top-N and a count over a range start at the range, the column index picks more of its structures from the statistics of each column, and sift_table has the pre-aggregates and text tests of sift_col. Update with ALTER EXTENSION pg_sift UPDATE in every database; no REINDEX is required.

Highlights

  • Lookups by key. WHERE key = x (an IN list, a range, GROUP BY key) with count, sum, min and max of a numeric, date or timestamp column reads one record of each segment through a key table (column format minor 22, key_table, on by default): 300 keys over 900,000 rows, count(*), sum(amount), min(ts), max(ts) WHERE key = 17 5.20 ms before, 0.107 ms now (100,000 rows: 0.49 and 0.054 ms); a sift_table keeps one too (sift.table_key_table). A unique *_id column keeps key lists, lists of runs of consecutive keys (minor 23) or a bloom filter of up to 5% of the unit, so a lookup by id, an IN list of up to 65,536 keys or a range of ids reads the few units that hold the keys.
  • Counts over a range or a prefix. count(*) WHERE col BETWEEN a AND b on a column with a global order counts from the entries between the bounds: 800,000 rows, 7,900 in the range 8.7M to 1.3M instructions (0.9 to 0.2 ms). count(*) WHERE txt LIKE 'abc%' (or =, <, > in the C collation) is read from two places of the dictionary (count_columns, minor 24): 1,000,000 unique strings, 4,012 matching, 5.3 ms to 0.14 ms (a b-tree 0.43 ms). count(*) WHERE col IS [NOT] NULL reads the zone counts of the units.
  • Scattered columns and top-N. The build picks order_array_columns for scattered columns (1,000 or more distinct values, correlation with the heap order under 0.99, at most two, at most 25% of the index): a range of 790 of 800,000 rows hands the heap its addresses from the sorted keys, 18.4M to 2.4M instructions. An ORDER BY .. LIMIT n over a sorted run starts at the first entry of the range (WHERE ts >= x ORDER BY ts LIMIT 10 over 3,000,000 rows 18.7 to 8.4 million instructions; the page after a key, LIMIT 50, 63.6 to 10.4 million) and reads a descending window from its best end (2.67 to 0.65 million instructions).
  • The scan wins small statements. The start of a scan is priced from what it does: on 100,000 to 300,000 rows (PostgreSQL 18, an x86-64 host, 1,000 groups, warm) WHERE grp = x ORDER BY ts DESC LIMIT 10 0.23 to 0.10 ms, a keyset page 0.28 to 0.10 ms, max(ts) WHERE grp = x 0.36 to 0.10 ms.
  • count(DISTINCT) in the workers. timestamptz, float4, float8 and uuid (1,000,000 rows, 8 groups, 2 workers): timestamptz 675 to 201 ms, float8 656 to 242 ms, uuid 1,542 to 582 ms. Exact, as before.
  • sift_table catches up with sift_col. Min and max per group in the cube (GROUP BY g with min and max over 500,000 rows 2.860 to 0.140 ms), key lists and sorted runs of the unit, rollups of declared jsonb lists, text tests on the encoded strings (a group-by filtered by s = 'x' over a million rows 44 to 15 million instructions, by a prefix 448 to 59), partitioned tables as one SiftAgg source; docs/COL-TAM.md section 13 lists what works.
  • Planner. Prices measured per row and per group of aggregates that run on PostgreSQL’s own transition functions, the dictionary of a wide column, the workers' start, lookups of a few keys and the choice between a shared CTE and its inline form (sift.agg_verdict_cache); a forced scan now gets workers.
  • Cheaper opens and scans. Opening the head of a unit of a churned table 86,000 to 40,000 instructions; an ungrouped aggregate the pre-aggregates do not answer counts and sums a chunk in one pass; SIMD kernels for x86-64 (AVX2) and arm64 (NEON) have an automated check of every level.

What a 3.4 user must know

  • No REINDEX is required. ALTER EXTENSION pg_sift UPDATE runs pg_sift--3.4--3.5.sql, which moves the version, adds sift.json_build_object(VARIADIC "any") and replaces sift.upgrade_commands(); no other SQL object changes. sift.version() says 3.5; the exact build is in pg_get_loaded_modules() on PostgreSQL 18 (3.5.0+<commit>).
  • New column format minors 22 to 24, written after the update (key table, lists of runs, count_columns). 3.5 reads every segment of 3.4. A 3.4 library refuses the newer ones with a clear error (has a segment of column format 1.22, this build reads 1.3 to 1.21); going back is not a supported path, scripts/upgrade-check.sh --to 3.4 lists what it cannot read.
  • Defaults chosen by benefit against cost, per column. A structure is built by default where its measured benefit (a speedup on a common class, at least 100,000 instructions per query) outweighs its index growth and its write cost (growth at most 25%), and the statistics of the column decide. Changed in 3.5: rollup_dims = 'auto' (the pair of low-cardinality columns whose combinations fit a unit; 'off' writes none), order_array_columns = 'auto' (scattered columns), key_columns = 'auto' (a unique *_id column, bloom filter up to 5% of the unit), count_columns (text leaves read in place), sift.table_dict_lookup (a sift_table stores the order of the strings of a dictionary of 1,024 strings or more, so the first test of a string in a new backend reads it in place), key_table and sift.table_key_table. Each CREATE INDEX that chooses says so in a NOTICE with the columns and the MB; docs/OPTIONS.md has gain, growth, write cost and the switch to turn each off, measured for every option.
  • Plans can change: the cost of opening a scan and of the aggregates moved; sift.explain_why(query) says why a statement took or refused the scan, and sift.enable_agg = off restores PostgreSQL’s plan.

Fixes

  • A backend crash in the stock plan of 3.4.0, 3.4.1 and 3.4.2. A merge join over partitions with a runtime key could plan a bitmap heap path without the outer relations of the index path under it (add_address_paths); the executor died in CheckExprStillValid (SIGSEGV). Fixed; there is no 3.4.3, upgrade to 3.5.0.
  • A partitioned table whose partitions did not choose the same rollup of the rows (rollup_dims = 'auto' picks it from each partition’s data, so a partition attached later with few rows has none) lost the single Custom Scan (SiftAgg) over its partitions and ran the normal plan; the scan keeps the rollups every partition has and reads the others by their rows.
  • VACUUM of a sift_table with a unit of more than 65,000 rows failed with could not read blocks N..N once rows were deleted from every such unit; it finishes.
  • A min() or max() planned as a LIMIT 1 sub-select with sift.explain_refusals on crashed the next plan of the session.
  • ORDER BY k, expr LIMIT n over a range of the sort key failed with “sift_col sorted run is corrupted”; CREATE INDEX with hll_columns and a leaf that is NULL in nearly every row failed with “segment 1 is corrupted”; sift.col_verify could report a false difference of a unit’s exceptions; a merge of an index with key_columns = 'auto' dropped the key lists and the key table of a column the cube holds.
  • With sift.enable_agg = off the normal plan is the 3.3 plan again (3.4.0 planned another one for 92 of 156 statements of a replay corpus).
  • sift.upgrade_check('3.3') did not list an index with a global order (minor 21) as unreadable by 3.3; the build against PostgreSQL 17, 18, 19 and GCC 11 (el9) is clean.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below are for PostgreSQL 18 (other majors build from source with make USE_PGXS=1). Column format minors 3 to 24 are read; 3.4.x reads up to 21. SQLite and MariaDB extensions: no change since 3.4.2.

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

Every change: CHANGELOG.md.

SQLite extension

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

  • No change: the fixes of this release are in the PostgreSQL column index, the aggregate planner and sift_table, 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 open as they are, nothing to rebuild.

Download sift-sqlite-3.5.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.5.0 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.5.0 release: no change to the SQL files.

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

Download sift-mariadb-3.5.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.