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 INSERTs 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.

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