pg_sift 3.6.1: a parallel CREATE INDEX or REINDEX no longer leaves an index file at twice its live size when a standby streams (at the price of a heavier build, see “Upgrade note”), three statement shapes that 3.6.0 planned as PostgreSQL’s plan (a semi, anti or inner join by an int4 key against a table of keys, GROUP BY over 400 hash partitions) are planned as the scan again, EXPLAIN (VERBOSE) of a top-N with window totals works, and the default point_tid_columns no longer lists a timestamp. Update with the files of this release and restart the server; no ALTER EXTENSION step, and REINDEX only if you want smaller indexes (see “Upgrade note”).

Highlights

  • Changed: a parallel build merges its own segments into a gap it reserved and cuts the file; the file ends at the live size. The segments of the participants of a parallel CREATE INDEX or REINDEX with key_cut overlap (each holds some rows of every key), so 3.6.0 left them to the first VACUUM, which wrote the index again after them: the file ended at twice the index, and stayed there while a physical standby streams (a cut stalls the replay behind a long query of the standby). The first fix of this release rewrote the segments in the build and then moved them down, which cost 43.9 CPU seconds and 325 MB of WAL for the build (below). The build now reserves a gap in front of its segments, sized from the statistics of the table (a tenth over their estimate of the index), merges the segments into it after the publish, releases the gap and cuts the free end; the relocation copy is gone. The file of a build belongs to the transaction, so no query of a standby can hold it, and the cut waits for nobody. A gap too small leaves the rest of the output above the free blocks, which the space step brings down; a build without key_cut or with no worker launched reserves none; there is no format change and an index built by 3.6.0 reads and vacuums as before. Measured on 3,000,000 rows, 13 columns, key_cut, 8 workers, one streaming standby, PostgreSQL 18.6, one session, other load on the machine (CPU seconds are the figure to read, wall time is not):
REINDEX CPU wall WAL file / live after it first VACUUM CPU, WAL file / live after it
3.6.0 18.9 s 9.3 s 104 MB 104 / 103 MB 8.9 s, 101 MB 206 / 99 MB (2.08x)
first fix 43.9 s 32.4 s 325 MB 115 / 115 MB 0.02 s, none 115 / 115 MB
gap (this release) 39.3 s 30.3 s 255 MB 115 / 115 MB 0.03 s, none 115 / 115 MB (1.0x)

The price: the build writes every page twice (the participants, then the merge, which is serial in the leader): 39.3 CPU seconds where 3.6.0 took 18.9 and 255 MB of WAL where it wrote 104; with the first VACUUM that used to follow, 3.6.0’s work is 27.8 s and 205 MB, so the build costs +41% CPU and +24% WAL for a file at 1.0x. The gap is an estimate and overshoots on a wide table: 202 MB reserved for 115 MB of index here (1.8x, the statistics overstate its json columns), so the file peaks near 2.8x the index while the build runs and ends at 1.0x. A build with no streaming standby gets the same compact file; REINDEX CONCURRENTLY behaves as before. 10,000,000 rows were not measured. Test: test/t/col_build_standby_space.sh. - Fixed: key-only lookup joins were planned as PostgreSQL’s hash join. k IN (SELECT ..), EXISTS, NOT EXISTS and an inner join by an int4 column against a table of 550,000 keys (the cases keys_subq_500k, keys_exists_500k, keys_join_500k, keys_semi_load_first) priced the table of keys with its dictionary and the probes of the keys as hash probes, so the scan lost to the normal plan on cost. They are priced as the keys alone (10 operators a row) and a byte-table probe (5 operators a tuple): the scan of 1,000,000 rows that joins the table costs 25,600 where it cost 77,300 against 32,300 for the normal plan, and it takes 7 ms and 23.2 MB read cold where the normal plan takes 149 to 160 ms and 84.5 to 85 MB. The table of keys is also loaded without its key dictionary and row chains: 35 ms where it took 59 ms. Tests agg_keys_plan, agg_lookup_keys_only. - Fixed: partitions_scan. Since 3.5.0 a partitioned table of 400 hash partitions whose indexes keep different key tables (key_columns = 'auto' follows the data of each partition) was refused with partition_source and ran PostgreSQL’s plan. The scan answers with the measures every partition holds: GROUP BY country over 400 partitions of 2,500 rows takes 8.9 ms and 26.7 MB read cold where the normal plan took 88 ms and 108.4 MB. Test agg_part_presence. - Fixed: EXPLAIN (VERBOSE) of a top-N with window totals. EXPLAIN (VERBOSE) and EXPLAIN (ANALYZE, VERBOSE) of a SiftAgg top-N that computes count(*) OVER () (the page of a list and its total) raised could not find window clause for winref 1. Test agg_topn_window. - Fixed: the default point_tid_columns listed a timestamp column, and its growth estimate was wrong. A date or timestamp column is no longer in the default (an equality on one is rare, its reads are ranges and an order), and the NOTICE predicts the growth from the values the index stores, not from the width of the documents of a jsonb column. A 13-column index with paths of 1,000,000 rows said +12% and grew 3.0 times; it now lists the id alone and says +44%. Tests col_point_auto, col_gorder_auto. - Fixed: a planner crash (signal 11) on a statement with a flattened lateral and shared CTEs. When the verdict cache held the verdict of another size of request than the one planned, the planner checked the plan of the form that won against the verdict of its size band and then discarded the memory context of the form that band named, which is never planned when the band names the other form: a wild pointer in plan_flat (MemoryContextDelete). It ended the backend in 3 of the first 512 comparisons of the differential corpus of analytic statements. The planner now discards the context of the plan it made. - Fixed: an out-of-bounds read of four bytes when an element predicate that tests two keys is pushed down. The element test of an aggregate (EXISTS over the elements of an array column, @>) indexed the leaves of the layout with the leaf number of an AND child that has none (-1): a read of four bytes before the array, in 3.6.0 only (sift_aggspec_elem_test, src/agg/spec.c). It was found by the sanitizer run of the deep nightly tier (a heap-buffer-overflow under AddressSanitizer). No wrong result was found; the test now declines such a predicate. Test agg_elem_test_leaf.

Upgrade note

  • No format change. Column format minors 3 to 32 are read and written as in 3.6.0, the extension SQL stays 3.6 (ALTER EXTENSION pg_sift UPDATE has nothing to do and there is no pg_sift--3.6--3.6.1.sql). Install the files and restart the server (a standby too), or reconnect every pool: a backend that already loaded the old library keeps it until its connection ends.
  • Existing indexes keep their size until REINDEX. An index built by 3.6.0 keeps its point directory for date and timestamp columns and any free space the build left; the plans and answers are those of the new planner as soon as the library is loaded. A new CREATE INDEX or REINDEX writes no point directory for a date or timestamp column unless point_tid_columns names it (smaller, and the NOTICE says what it chose), and with a standby streaming it cuts the file itself. Nothing is required: REINDEX only if you want the smaller file.
  • The keys_* numbers. The README and benchmark rows for keys_semi_load_first (and the other keys_* cases) were measured on the 3.7 development tip with these fixes; the fixes in this release are the same code.
  • Behaviour change: expect a heavier REINDEX or CREATE INDEX of a key_cut index. 39.3 CPU seconds and 255 MB of WAL where 3.6.0 took 18.9 s and 104 MB (3,000,000 rows, 13 columns; +41% CPU and +24% WAL against 3.6.0 with the first VACUUM that followed it), in return for the file at the live size and a first VACUUM that rewrites nothing. The file peaks near 2.8x the index while the build runs on a wide table. Each standby must replay the extra WAL, and a non-concurrent REINDEX holds its locks that long. REINDEX CONCURRENTLY is unchanged.

Who is affected and what to do

  • The file after a parallel build with a standby: 3.6.0 with a streaming physical standby. REINDEX after the update gives a file of the live size.
  • The three plans: 3.6.0 (the partitions one since 3.5.0). The rows were right; the statements were slower. SET sift.enable_agg = off is PostgreSQL’s plan, as it was.
  • EXPLAIN (VERBOSE): 3.6.0, with a window function in a top-N the scan answers.
  • The out-of-bounds read: 3.6.0, a statement with an element predicate on two keys over a sift_col index with an array column. A release build read four bytes of the memory before the array and gave the rows it should; a build with the sanitizers aborts.
  • PostgreSQL 19: a NOT IN sub-select inside a multi-arm CTE keeps PostgreSQL’s plan (a close call: the inline scans are priced 1531 against 1359 for the shared plan, which PostgreSQL 19rc1 prices 36% under PostgreSQL 18’s 2123 because it estimates one row from the anti join). The rows are equal, and the plan is slower than the scans: warm, EXPLAIN (ANALYZE, BUFFERS) on PostgreSQL 19rc1, the statement of the test agg_cte_hoist with sift.agg_force = on (the scans) against PostgreSQL’s plan (sift.enable_agg = off): 30,000 rows 2.7 to 5.5 ms against 67 to 97 ms, 300,000 rows 21.4 ms against 459 ms, 1,500,000 rows 117 to 129 ms against 2,497 ms (15 to 25 times). SET sift.agg_force = on takes the scans. PostgreSQL 17 and 18 are unchanged (their inline scans are cheaper than the shared plan). The key-only probe price is not the cause (a byte-table probe against a 50-key table measures 50 instructions a tuple, above the 5 operators priced); the estimate of the shared plan under a one-row anti join is a 3.7 item.
  • The planner crash: 3.6.0 (the corpus ends a backend with signal 11 on a build of the tag; src/agg/cte.c is identical in 3.6.0 and the tip before the fix); a statement with a flattened lateral and shared CTEs on a table with a sift_col index, planned with sift.enable_agg = on, when the verdict cache holds another size band’s verdict. SET sift.enable_agg = off for the role avoids the plan.
  • Nothing else changes: answers and formats are those of 3.6.0.

Regression tests

Every case fixed here has a test in the normal suite (test/sql, test/t) that was run against a build of the tag v3.6.0 and fails there, and passes on this release. The table, with the observed output, is docs/releases/3.6.1-REGRESSIONS.md.

Case Test On v3.6.0 On 3.6.1
Default point_tid_columns with a timestamp col_point_auto, col_gorder_auto (in installcheck-batch3) fails (auto: rid, ts against auto: rid) passes
Key-only lookup joins priced as hash joins agg_keys_plan (plan), agg_lookup_keys_only (rows) agg_keys_plan fails (normal instead of scan); agg_lookup_keys_only passes by design: it checks the rows of the changed executor passes
partitions_scan refused agg_part_presence fails (partition_source instead of scan) passes
Free space after a parallel build with a standby col_build_standby_space (TAP) fails (after the first VACUUM the file is 2,990,080 bytes for 1,294,336 live, 2.31x; the test allows 1.1x) passes
EXPLAIN (VERBOSE) with window totals agg_topn_window fails (could not find window clause for winref 1) passes
Element test without a leaf agg_elem_test_leaf fails under SANITIZE=1 (UBSan: load of value 128, which is not a valid value for type 'bool'; the same read is a heap-buffer-overflow under AddressSanitizer) passes
Order of tied keys of a page (a contract, not a change) agg_topn_ties passes by design passes

These ran on PostgreSQL 18; the outputs on 17 and 19 are those of the Full-scope CI run of the release.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A drop-in replacement of 3.6.0: column format minors 3 to 32 are read, extension SQL 3.6, no setting changes its default. The SQLite and MariaDB extensions: no change since 3.6.0. pg_get_loaded_modules() on PostgreSQL 18 says 3.6.1+<commit>; the extension version stays 3.6.

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.1-1_amd64.deb (_arm64.deb on arm64)
  • RHEL, Rocky, Alma 9 and 10 (PGDG postgresqlN-server): sudo dnf install ./pg_sift_N-3.6.1-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.1-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.1 (sift, a loadable extension; a separate component from the PostgreSQL extension above). Rebuilt with the PostgreSQL 3.6.1 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.6.0 open as they are, nothing to rebuild.

Download sift-sqlite-3.6.1-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.1 (sift, a UDF library and two SQL files; a separate component from the extensions above). Rebuilt with the PostgreSQL 3.6.1 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.6.0 opens as it is, nothing to rebuild.

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