pg_sift 3.4.0: the aggregate scan reaches more query shapes and runs them in parallel, a sift_col index cuts its units by a key when that pays and keeps answering from its pre-aggregates after churn, top-N gets sorted-run heads and optional order arrays, and sift_table closes gaps with sift_col. Update the files, restart, then ALTER EXTENSION pg_sift UPDATE in every database; no REINDEX is required.

Highlights

  • Units cut by a key (key_cut, column format minor 17). CREATE INDEX and REINDEX of a sift_col index cut the units by a key (or by a date or timestamp column) when a sample of the heap shows that zones cannot skip the units of that key: the first key-shaped column whose equality reads at least 8 times the units its rows fill and at least 64 units, no other leaf follows the heap order, the index has no order_columns, and the predicted index growth is at most 3%. Otherwise a NOTICE gives the units read, the predicted growth and the command. A lookup of one key value then reads 1 unit instead of all of a table (one value of 600: 1 of 100 units against 8 of 8). sift.key_cut_decision(index) says what a REINDEX would decide; key_cut = 'off' switches it off. A build takes 3.4 to 4.1 times the CPU of the superblock cut when the cut is taken; the memory of a build and of a merge is bounded by maintenance_work_mem.
  • Churned tables keep answering from pre-aggregates. A unit with dead rows is answered from its stored counts and sums minus its dead rows (sift.agg_dead_delta, default 15%, 0 turns it off; no stored byte, no format change): after 60 days of churn at 20% a month the grouped sum of 3,000,000 rows goes from 21.6 to 15.0 ms. The overlays that churn leaves are cut by TID range into units of sift.col_unit_floor rows (default 2,048): 10,552 to 721 units, a key lookup 35.0 to 7.5 ms at 3,000,000 rows. VACUUM no longer moves the large segments of an index that is due for the automatic rewrite, which started the WAL budget of sift.col_auto_merge_wal_pct over, so the units stay answerable after every cycle.
  • Top-N. A top-N for each key an enclosing query sets (CROSS JOIN LATERAL (.. WHERE device = d.device ORDER BY ts DESC LIMIT 3)) is one scan started over for each key: 20 devices over 1,000,000 readings take 1.1 ms where the normal plan takes 739 ms. The sorted runs of order_columns keep heads (minor 18, +0.6% to +2.4% of the index): ORDER BY ts DESC LIMIT 1000 over 3,000,000 rows takes 2.6 ms, not 4.2 ms. ORDER BY a, b LIMIT n with only a in the index, a float key, ORDER BY <aggregate> LIMIT n (the groups that cannot be among the first n are dropped: 14.6 against 96.6 ms over 400,000 groups) and OFFSET take the scan. order_array_columns and order_array_group (opt-in, minor 21) keep a global order, so a page of 50 rows of a scattered timestamp reads 0.29 ms instead of 17 ms, at about 6 bytes a row for each array.
  • More shapes in the scan, and in parallel. LEFT JOINs from a calendar or a spine, hashed sub-plans (x IN (SELECT ..)), lookups that read a CTE or a function, and count(DISTINCT) of a joined column plan workers (1,600,000 rows, a preserved join: 522 to 79 ms with 8 workers). EXISTS and NOT EXISTS sub-plans, LEFT JOIN LATERAL aggregates, mode() and percentile_cont/percentile_disc on the histogram of a group, a comparison of two columns of one row, and the sum/avg of a decimal expression of up to 38 digits (exact in 128 bits: 19 ms where it took 200 ms at 400,000 rows) are scan shapes now. A sum or avg of a numeric column without a precision runs in the workers (1,000,000 rows, 16 groups, 2 workers: 50.3 to 23.3 ms while PostgreSQL’s parallel plan takes 43.4; 10,000,000 rows 439 to 160 ms against 389). The lookup table of a join is loaded from the leaves of a sift_col index when one holds the columns (sift.agg_lookup_columns, on).
  • Planning. The planner prices an IN list by the structure that tests it, the fixed cost of opening a scan is 100,000 operator costs (it was 210,000), a table with several sift_col indexes is planned through the cheapest one, and the choice between a shared CTE and its inline plan is kept in the shared memory of the server (sift.verdict_cache_kb, 256 kB) so a new backend plans the winning form only. sift.agg_force (off) takes the scan wherever it can be built, for diagnosis.
  • sift_table. Integer filters run on the encoded chunks of its column groups, a unit that keeps few rows after the filters is read for those rows alone, a uuid column is a dictionary of its values, bulk COPY spends 12% fewer instructions, UPDATEs of the same keys in one transaction are 3 times faster, and the b-tree asks the table’s tombstone log, not each row, before a page splits (40,000 updates of 2,000 rows of a 600,000-row table: 9.0 to 3.1 s; a heap table 0.7 s).
  • Index scans by default (sift.enable_col_scan is on). A sift_col index with the sift.col_*_ops classes is a planner path (bitmap scan, Index Scan) priced against the b-trees and the sequential scan, so a table whose b-trees were dropped reads its point, range and IN lookups from the index and not by a sequential scan (1,000,000 rows: id = x 29.5 to 0.21 ms, id IN (100 ids) 43.4 to 0.62 ms, a range of 100 rows 32.8 to 0.31 ms, a range of 1,000 rows 40.6 to 2.9 ms). With the b-trees present the plans are the b-tree’s (24 of 26 shapes identical at 100,000 and 1,000,000 rows), and a few count(*) shapes that the aggregate scan answered take the bitmap scan by cost, up to 0.16 ms slower. SET sift.enable_col_scan = off (or postgresql.conf) restores the earlier planner.
  • Pre-aggregates that keep more of the columns by default. The byte budget of the cube of a unit counts what the cube holds (every measure of a kept dimension, with its minimum and maximum) and is 40% of the unit’s encoded bytes (it was 2% of the first three measures), so an index of sixteen columns whose dimensions have 50 to 200 values answers a GROUP BY of any of them from the pre-aggregates: the last of eight dimensions of 50 values over 500,000 rows goes from 2.3 to 0.28 ms (the normal plan takes 63 ms). The price is index size: at 1,000,000 rows the index grows by 8.9% for the cube instead of 6.7% (16 columns of 50 values), 17.9% instead of 6.7% (16 columns of 100 values) and at most 41% (32 columns of 200 values); the build time stays within 0.1 s; the index of the store benchmark is the same file to the byte. A grouped sum or avg of a float8 column is not answered from the cube (float sums are added row by row), whatever the budget. This is above the 3% growth of the default rule, chosen because the index size is not what these tables pay for. No format change: segments written after the update use the new budget, REINDEX or sift.col_refresh(index) rewrite the older ones.
  • Approximate distinct counts, opt-in. sift.approx_count_distinct(x) returns a HyperLogLog estimate (relative standard error 0.81%); hll_columns stores a sketch for each unit (minor 20; opt-in: +23% of an index for four columns). count(DISTINCT) stays exact whatever the settings say.
  • Advice. sift.cluster_advice() names key_cut, sift.index_advice names preagg = 'auto' and the columns that look rewritten often (an UPDATE of them is not a HOT update), and CREATE INDEX says in a NOTICE which foreign key columns the index does not hold. docs/OPTIONS.md has one entry for every option, setting and automatic behavior: what it buys and costs, why its default, how to turn it off, with the numbers.
  • arm64. NEON kernels for the fused range filter over bit-packed values, run expansion and float4 keys of a decimal-scaled chunk (4096 values on a test host: 1750 to 313 ns for the range filter); the kernels that did not beat the scalar loop on that CPU stay scalar.

What a 3.3 user must know

  • No REINDEX is required. The 3.4 library reads every index of 3.3 as it is. ALTER EXTENSION pg_sift UPDATE runs pg_sift--3.3--3.4.sql: it adds sift.approx_count_distinct, replaces sift.upgrade_commands() and sift.cluster_advice() and comments sift.suggest_paths(); it changes no setting and no stored data. The library starts with the next backend (session_preload_libraries needs a reconnect, shared_preload_libraries a restart).
  • New column format minors, written only after the update. sift.col_format_minor stays 15. Minor 17 (key_cut, and the TID-range cut of churned overlays), 18 (run heads of order_columns), 19 (pre-aggregates of the non-NULL rows of a unit whose leaf is NULL in nearly every row), 20 (hll_columns) and 21 (order_array_columns) are written for segments created by a build, a merge or a REINDEX after the update; the segments already there keep their minor, and both live in one index. A 3.3 library refuses a segment of minor 17 or newer with a REINDEX hint. To keep a 3.3 library able to read new writes, set sift.col_format_minor = 14 for the server (ALTER SYSTEM; a session or role setting does not reach the background worker of sift.col_auto_merge) and key_cut = 'off' before the first REINDEX. sift.upgrade_check('3.3') and scripts/upgrade-check.sh --to 3.3 list the blockers; docs/UPGRADING.md has the way back.
  • Automatic key cut. The first CREATE INDEX or REINDEX of a sift_col index after the update may cut its units by a key (the NOTICE says so). A build that cuts takes 3.4 to 4.1 times the CPU of one that does not. ALTER INDEX .. SET (key_cut = 'off') before the REINDEX keeps the 3.3 layout.
  • Defaults that change plans or bytes: sift.enable_col_scan on (was off: no REINDEX, no stored change; off restores the 3.3 plans), the cube budget 40% of a unit (was 2%: larger indexes, more GROUP BYs answered from the pre-aggregates; no setting, no format change), sift.agg_dead_delta 15 (new; 0 restores the scan of units with dead rows), sift.col_unit_floor 2,048 (new; 0 restores the superblock cut), sift.agg_lookup_columns on, sift.verdict_cache_kb 256 kB of shared memory. Statements of the shapes above change plans after the update; their rows do not (checked against the normal plan by the differential tests).
  • Plans of 3.3 can change. The cost of opening a scan and the pricing of joins moved: a statement that kept PostgreSQL’s plan in 3.3 may take the scan, and the other way. sift.explain_why(query) and sift.explain_refusals say why; sift.enable_agg = off restores the normal plan for a statement.

Fixes

  • count(DISTINCT d) over a date column could return the number of months (or years), not days. It happened when the column was not one of the sixteen dimensions of the pre-aggregate cube but the units kept the calendar cells of the date: the count was read from the cells' histogram as if it were the column’s own (14 for a column of 400 days over 14 months, with every unit answered from the pre-aggregates). The count now reads the histogram of the column itself, and a column without one is scanned. Found on the 3.4 tip while the cube’s byte budget was being raised, which keeps the cells of more dates. The same condition is in the code of 3.3.0, where the owner did not reproduce the wrong count; if a 3.3 database counts the distinct dates of a calendar-celled column from the pre-aggregates, compare it with SET sift.enable_agg = off until the update.

Known regressions in 3.4.0 (to be fixed in 3.4.1)

Found by the release benchmark run (1,000,000 orders, PostgreSQL 18.6, an x86-64 host); none changes an answer.

  • Store question 03 (sum(amount) GROUP BY date_trunc('month', created_at)) after the maintenance step: the 3.3 results file has 0.12 to 4.62 ms across the four sift lanes (sift 0.66, sift_maint 0.12, sift_table 0.53, sift_table_maint 4.62 ms); the 3.4 run has 4.1 to 4.6 ms in all four (sift 4.54, sift_maint 4.49, sift_table 4.12 ms). A local run of the same churn did not reproduce it (1.47 ms against 1.41 ms on 3.3.0). Under investigation.
  • window_first_rows (the first 3 rows of each group by a window function, one month of 1,000,000 orders): the planner now takes the aggregate scan, which is slower than PostgreSQL’s own plan: 26.2 ms against 22.2 ms on the release host (3.3.0 kept PostgreSQL’s plan: 19.5 against 18.8 ms). SET sift.enable_agg = off for the statement restores it.
  • A numeric column without a precision with almost unique values (2,000,000 rows, 6 groups) chooses the scan with the default 2 workers and takes 79.6 ms against 61.7 ms for PostgreSQL’s plan; with 8 workers and tuned page costs the scan wins (74.1 against 104.7 ms).
  • Cases where the aggregate scan or sift_table is slower than a heap table or a plain index, measured on the release host with the 3.3.0 library and the 3.4.0 library back to back (milliseconds, sift against the same statement without it): oltp_insert_index 57.7 against 37.5 in 3.3.0 and 63.3 against 42.8 in 3.4.0, oltp_update_index 104.8 against 75.7 and 118.5 against 86.1, tbl_update_keys 5,314 against 602 and 834 against 631, bulk_copy_wal 1,304 against 545 and 1,265 against 620, verdict_shared_report 29.1 against 20.9 (a 3.4 setting; no 3.3 figure). The ratios are as in 3.3.0 (both columns moved together with the host), except tbl_update_keys, which is 6 times faster than in 3.3.0 and still 1.3 times slower than a heap table.

Not verified, and limits

  • AVX-512 was not run: the kernels compile for it and the dispatcher never selects it on a machine without it; the AVX2, scalar and NEON paths are the ones tested. arm64 was verified on one a test host and in CI on a hosted arm64 runner, not on a Graviton-class machine.
  • Pull requests that landed without the corpus replay (plans, rows and instructions of a fixed corpus of statements, the gate used for earlier changes in this series): , , , , , , , , , , , , , , , and had none; , , , and had only the plans and rows part. The replay runs once on the release and a finding goes to 3.4.1.
  • PostgreSQL 17 deep-tier test reds (the long soak and fuzz runs of the nightly tier, on 17 only) are known and deferred to 3.4.1.
  • Parallel float sums add the lanes in a fixed order, so the last digits of a float8 sum can differ from a serial plan; the difference is bounded by 2(n-1)·2⁻⁵³ of the sum of the absolute values (the largest seen was 7.8e-10 relative).

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below are for PostgreSQL 18 (other majors build from source with make USE_PGXS=1). Extension SQL 3.4; ALTER EXTENSION pg_sift UPDATE reaches it from any older version in one command. Column format minor 15 by default; the library reads minors up to 21. The BM25 index format is unchanged. docs/UPGRADING.md has the preflight and the steps.

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

Every change: CHANGELOG.md.

SQLite extension

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

  • No change: the 3.4 work is in the PostgreSQL column index, the aggregate scan 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.3.0 open as they are, nothing to rebuild.

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

  • No change: the 3.4 work is in the PostgreSQL column index, the aggregate scan 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.3.0 opens as it is, nothing to rebuild.

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