pg_sift 4.3.0: reads that order, page and list by a column take fewer instructions and blocks (top-N, OFFSET, ranges and IN lists by an order array), a serial id and the second order by a group column take less of the index, writes put fewer pages and less WAL in the index (the rows of a write buffer stay in shared memory), builds and merges keep to maintenance_work_mem, and a sift_col index can be connected to the index of the parent table it joins, so that EXISTS, NOT EXISTS and counts over a join are answered from the child and a left join groups the child first; extension SQL 4.3 (reached from 4.2, 4.2.1 and 4.2.2 in one step): install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database. A library before 4.3 refuses what 4.3 writes with its defaults (column format minor 44): update every node that reads an index before the primary writes (see “Who is affected and what to do”).

Highlights

Figures are those CHANGELOG.md and docs/OPTIONS.md state, with the rows of the table they were measured on, as before then after. Instructions are those of one backend, minimum of several runs; a b-tree figure is given where one was measured.

Reads and order arrays

  • Faster: top-N, OFFSET, ranges and IN lists by an order array. A chunk of an order array may be seekable (a fence key every 32 entries), so a seek or a scan decodes 32 entries and not 512; a walk keeps the handles of the segments for the backend’s later statements and decodes only the addresses it consumes. 5,000,000 rows, a plain array on a timestamp: top-N descending 1.38 M to 0.88 M instructions, OFFSET ascending 1.03 M to 0.87 M, a range 2.47 M to 1.37 M, an IN list 3.05 M to 2.56 M. A range with both bounds and LIMIT 20 seeks the far end of each segment’s range only when the scan gets there (5,000,000 rows in 26 segments: 1.138 M to 0.898 M instructions and 149 to 64 blocks read cold; a b-tree 0.265 M and 23). A cold seek reads the header of an order block alone, reads the directory when a seek first needs it and starts no read-ahead after a lookup (the same table, blocks read cold: range 258 to 188, top-N 227 to 160, OFFSET 100 250 to 187; a b-tree 23, 23 and 123). An equality or an IN list of ids on an array decodes the addresses of its entries and not those of the whole chunk (200,000 rows: 64 keys 2.68 M to 1.47 M instructions, 16 keys 0.82 M to 0.54 M, one key 0.18 M to 0.16 M). The seekable form takes 9.33 bytes a row for 1,000,000 random keys where the plain one took 9.86 (a b-tree 9.31). An index built before keeps its chunks until a merge or REINDEX writes them again.
  • Faster: a top-N, page or OFFSET of an integer or time key with an IN list on it. ORDER BY u LIMIT 100 with u IN (...) reads the entries of each value of the list one after another, a seek apiece, where it read a range from the first value to the last and tested every entry between (1.4 to 3.8 billion instructions for 4 to 16 values over 1,000,000 rows) or, past 16 values, the units (27 million instructions over 1,000,000 rows, 166 million over 5,000,000). 1,000,000 rows, LIMIT 100: lists of 4, 8, 16, 17 and 40 values take 0.549 M, 0.770 M, 1.22 M, 1.26 M and 2.46 M instructions (a b-tree 0.22 M to 0.94 M); 40 values over 5,000,000 rows 5.4 M (a b-tree 0.95 M). A list of more than 1,024 values still reads the units.
  • Faster: reads over rows that were updated and not vacuumed. A top-N, OFFSET or range by an order array of an index that tracks its changes (track_changes, the default) asks the tombstone log about the heap page of each entry it takes and not about the whole superblock of each: with 30% of 1,000,000 rows updated and not vacuumed, OFFSET 100 took 9.19 M instructions with track_changes on and 0.87 M with it off, because the keys are spread over the whole heap and the masks read the tombstones of the whole table; the rows are the same. A read of a group array over a table with updated and vacuumed rows lists the dead rows of the units whose pages it reads and not of the whole segment: the top rows of one group take 0.88 M instructions and 230 buffers over 1,000,000 rows (6.6 M and 304 before) and 1.53 M and 480 over 5,000,000 (30.9 M and 1,078), with 30% of the rows updated and vacuumed.
  • Faster: the second order by a group column (order_array_group). WHERE col = x ORDER BY ts LIMIT n on a table that has the b-tree of that shape no longer reads the units once the b-tree is replaced by the sift index. The build groups the second order by an integer column that a non-unique b-tree of the table runs the shape of (the column and a date or timestamp of the index as its first two key columns, in either order), as it grouped by an entity before; the NOTICE and sift.index_choices say whether a b-tree, an entity or the hint chose the group. 5,000,000 rows, an index of an id, a 100-value group, a timestamp and an amount: the b-tree on the group and the timestamp takes 0.33 M instructions and 24 blocks, the sift index without the group 4.69 M and 1,491, with it 0.89 M and 44. A group names the entries of a chunk by their position in the plain order of the column where the positions of a group are close, which makes the index grow +20.2% over the plain order where it grew +59.6% (the group by an integer of 100 values), +14.2% where +30.6% (1,000 values), for a build that takes 7.7% more instructions than the group by keys and addresses, and ORDER BY ts LIMIT and IN of the group read half the blocks or fewer. The table of groups of 1,024 groups or more and no NULL key takes about 4.4 bytes a group where it took 24: 93,000 values over 5,000,000 rows, the index is 65.0 MB (102.7 MB before, 39.5 MB without the group), the build 0.2% more instructions, a top-N of one group 5% more and an IN of two values 6% fewer, with 24 to 37% fewer blocks. A top-N by a group reads a compact chunk with 12% fewer instructions (35 segments of 5,000,000 rows: 1.609 M to 1.418 M). An index built before keeps its arrays until REINDEX.
  • Changed: the choice of the second order prices what each segment writes. order_array_group = 'auto' prices the table of groups that every segment writes (24 bytes a group, about 4 from 1,024 groups with no NULL key in the segment) and not one table for the index, so an entity of many values with few rows apiece is no longer refused for a table it does not write (300,000 rows, 60,000 values: refused before, chosen now, the index grows 4.3 bytes a row), and an entity with NULL keys is refused where its tables would be the index. The choice is the same at 100, 1,000 and 93,000 values over 1,000,000 and 5,000,000 rows.
  • Faster: planning an index with many segments reads fewer blocks. The header page of a segment keeps a copy of the small ranges that a plan and the first read open (the schema, the unit directory, the point directories, the order headers, the zone directory, the pre-aggregate and set headers), so a plan over 5,000,000 rows in 18 segments reads 81 index pages where it read 146, the first ordered range query 98 where 121 (116 where 193 with the index and heap evicted) and a top-N 75 where 106; the index grows by 0.26%, a warm statement runs 1 to 4% more instructions, and build, promotion, merge and VACUUM times do not change. The statistics of an index count the compact chunks from the headers alone, and the share of units without a histogram or a rollup is read only when the cost of a pre-aggregate answer needs it: the first statement of a new backend over 5,000,000 rows in 26 segments plans from 238 blocks where it read 416 (buffer hits 1,021 to 279), and over 1,000,000 rows from 81 where it read 112. A statement whose choice between plans is kept plans with 0.3 M fewer instructions: a left join of generated days plans in 977 k instructions with the child-first plan (it was 1,277 k).

Index size

  • Smaller: a serial id is an order array, not a point directory. A nearly unique integer that follows the heap order (correlation 0.99 or more, no unique b-tree of its own) gets an order array chosen by the build, kept whole, and no point directory: runs of consecutive heap addresses take 0.4 bytes a row where the directory took 3.3. 5,000,000 rows, an index of an id, a group, a timestamp and an amount: 63.3 MB to 49.1 MB; 1,000,000 rows 9.0 MB to 6.1 MB. An equality on the id takes 0.46 M instructions and 5 cold blocks with the directory and 0.51 M and 2 with the array (a b-tree 0.12 M); the other classes are within 3%. A list of many ids costs more with the array (id IN of 64 keys over 1,000,000 rows: 3.7 M instructions in 94 cold blocks against 1.0 M in 155 with the directory), so ask for the directory with WITH (point_tid_columns = 'id') where the reads are long id lists. A table with a primary key keeps the directory of the key for the writes that supersede rows. An index built before keeps its directory until a merge decides again for the segments it writes, or REINDEX for all of them (docs/INDEX-RECIPES.md).
  • Smaller: a build by ranges of the key for a table of one to three runs. A serial CREATE INDEX or REINDEX of a key_cut index on a table of more than one run (it needed more than three before) is built by ranges of the key, so a table of one to three runs no longer leaves runs that overlap in the key for the maintenance to rewrite after the build: the build writes the index once, and it is smaller.
  • Changed: key_order = 'col' of one column builds no point directory. The directory made the index 71 to 95% larger (4.89 to 9.34 MB at 1,000,000 rows, 23.98 to 46.74 MB at 5,000,000) and made an IN list of 100 values 5.7 to 11.8 times cheaper (a b-tree is 1.7 to 3.8 times cheaper still) and an equality up to 1.6 times, and nothing else. Set point_tid_columns by hand for such lists. Indexes built before keep their directory.
  • Smaller: fewer and larger segments in the memory given. The point directories and order arrays of a segment are built from the memory the rows already took, a segment is cut at the memory it will reside in, and the allocator keeps the arrays it frees from staying in the process: a build of one integer column of 1,000,000 rows peaked at 89 MB and peaks at 56 MB; the four columns of a 5,000,000-row table at 64 MB of maintenance_work_mem 134 MB and now 68 MB, and at 256 MB 253 MB and now 233 MB with 2 segments where it wrote 5.

Write amplification and memory

  • Fewer pages and less WAL: the rows of a write buffer stay in shared memory (sift.col_wbuf_ref, on). A row of a heap table with a sift_col index that tracks changes goes to the write buffer as a 16-byte reference to its heap tuple; its values are kept in a shared arena by the backend that inserted it, and a reader takes them from there. 500,000-row index, 48 batches of 2,000 rows: index pages written 410 to 199 bytes a row with updates, deletes and VACUUM, 100 to 32 bytes a row for inserts alone; index WAL 506 to 162 bytes a row (inserts alone 76 to 15.5); WAL of all relations 5,970 to 5,617 bytes a row. The index file is 37.9 to 31.4 MB for inserts alone and 64.1 to 42.3 MB with a snapshot held, and a VACUUM that waits for the lock of the cut of the free end does not wait the 5 seconds of its retry, because the free end it has to cut is 5.0 MB without references and 0.6 MB with them. The costs: an insert +0.2% instructions, a promotion +1.7%, a read of the buffer within 1% warm, the first statement of a backend +4% to +5% at 47,000 buffered rows, and the first read of a buffer after a restart, on a standby or on a promoted standby builds its entries from the heap once (about 11,900 instructions a row: 12,000 rows 65 M to 209 M instructions, 8 ms to 24 ms). The arena takes 88 bytes of shared memory a buffered row, in 1 MB segments made on demand up to 128 MB for the server, and sift.col_arena_status() shows it. SET sift.col_wbuf_ref = off writes the rows with their values as before. A write buffer that holds a reference is column format minor 44 (see the upgrade notes).
  • Fewer pages for small promotions and overlays. A segment under half the rows of the largest (an overlay) is written without point directories, whose arrays span the rows of the whole index and cost more than the rows of an overlay, and without the order arrays while the overlays together hold at most a tenth of the rows of the index and no more than a walk of the arrays takes from them in memory (65,536 rows; a sixteenth of that for a text key); a top-N, OFFSET, range or IN list reads such a segment from the rows in memory and prices them for each segment it reads, instead of sending the walk of the whole index to the units, and a per-key ordered probe is no longer slower with four overlays alive than with one. It gets no net pre-aggregate sidecar for its dead rows (the next merge drops them). Merges take the smallest overlays that come to twice the largest (it was one and a half times), and more than four alive in an index that is read are merged whatever their sizes. Fewer index pages and less index WAL are written for each row ingested and the file is smaller; no format change.
  • Less memory: a merge keeps to maintenance_work_mem. A unit decoded for a merge no longer leaves a buffer of its rows in the merge’s memory until the merge ends, an input with no unit in a step gives back the arrays of its largest unit, the arrays that are held count with the writer’s memory when a segment is cut, and the partitions are cut at their share of the bytes still to write, so none is a short last one. A forced merge of the 5,000,000-row test table at the default 64 MB writes 15 segments where it wrote 17 and peaks at 59 MB (97 MB before); at 256 MB it writes 2 and peaks at 229 MB (261 MB before).
  • Fixed: a forced merge of an index at its fewest segments writes nothing. sift.col_merge(index, true) on an index whose segments are already the partitions its bytes make (all of the partition size, none with a dead row, no more of them than the merge would write) used to read and write all of the index again each time it was called (62 MB of WAL for each call on a 5,000,000-row index); a second call, or a loop of them, is free.

Joins and child-first plans

  • Added: connected indexes (WITH (connect = 'fk -> parent(key)'), docs/COL-CONNECT.md). The option connects a sift_col index to the sift_col index of the parent table it joins. The child index depends on the parent index (DROP INDEX of the parent needs CASCADE), the parent index keeps a journal of the keys that its updates, deletes and key changes ended (one 32-byte record for each entry, only while a child is connected), and the segments of the child keep a flag for each connection that the parent row was found (a boolean leaf of 176 bytes at 1,000,000 rows); both are column format minor 44. One option names up to 8 connections, the same parent may be connected by two columns, and pg_dump writes the text and REINDEX resolves it again. sift.col_connections and sift.col_connection_state(index) list the connections and the state of the journal of each parent.
  • Faster: EXISTS, NOT EXISTS and counting joins to the parent of a connected index. A semi, anti or inner join that reads no column of the parent is answered from the found flag, less the keys the journal of the parent names (at most 16,384) and plus those of them the parent holds now; 1,000,000 rows and 100,000 parents: the join count 4.3 ms against 7.2 ms for the join that loads the parent and 228 ms for the normal plan, NOT EXISTS 2.8 ms against 6.1 and 244 ms. The ordinary join runs, with the same answer, when a write buffer holds rows, a segment has no stamp or (for a child with rows whose key the parent lacked) a parent row was inserted since; a clean child, as a foreign key keeps it, is not slowed by writes to the parent. EXPLAIN ANALYZE shows Found Proofs, Found Keys Outside The Proof, Found Fallback, Parent Leaf Reads and Parent Heap Fetches; sift.agg_connect_found turns it off.
  • Added: CREATE INDEX and REINDEX connect an index by themselves (sift.col_auto_connect, on). Each validated single-column integer foreign key of the index’s columns whose referenced table has a sift_col index that tracks the referenced key is connected, and a NOTICE names the foreign key. An automatic connection carries no parent column (a flag leaf of a few hundred bytes and a stamp for each segment). The choice is written into the option as connect = 'auto: ..', which a dump and a restore keep without the constraint; connect = 'off', SET sift.col_auto_connect = off and an explicit connect turn it off or win. The writers of the parent table are held out from the end of the child’s scan to the end of the transaction, and only for the first child of a parent. The cost: 20,000 single-row UPDATEs of a parent of 100,000 rows take 98.7 k instructions an update with and without a connected child (+0.1%), and 335 to 368 bytes of WAL (+10.0%) for the journal record. The automatic rewrite refreshes the segments whose flags aged (sift.col_connect_refresh_pct, 10; reason stale connection of sift.col_maintenance_due) within the WAL budget of sift.col_auto_merge.
  • Faster: child-first plans for counts, [NOT] EXISTS and left joins. SELECT count(*) FROM parent WHERE [NOT] EXISTS (SELECT 1 FROM child WHERE child.fk = parent.key AND ..) with a unique int2, int4 or int8 parent key is planned also as the count of the distinct keys of the child’s rows that have a parent (NOT EXISTS: the parents' count less it), and the plan that costs less is kept; the aggregates of a table left-joined to one table (parent LEFT JOIN child ON child.fk = parent.key GROUP BY parent.attr) are made for each key of the child first and added up over the parents when that costs less. 1,000,000 rows and 100,000 parents: EXISTS of a child of one kind 6.2 ms against 11.4 ms for the parents scanned and 72.5 ms for the normal plan, NOT EXISTS 1.9 ms against 6.6 and 81.2; count and sum by a parent attribute of a LEFT JOIN 60 ms, 353 M instructions and 488 blocks against 540 ms, 1,633 M and 7,053 for the lookup join. The planning of such a statement costs one more plan: 977 k instructions against 760 k with the plan off for a left join of generated days (+0.22 M, about 0.06 ms warm; 1.2 M more in a new backend). sift.agg_child_first turns them off.
  • Faster: a LEFT JOIN of two indexed tables is also planned as the scan of the child with the preserved parent as its lookup (it was so planned only when the parent had no index), and the cheaper plan runs: the count of children per parent, the sum with conditions in the ON clause and the grouped counts of a parent attribute take between a third and two thirds of the time they took with the parent scanned (a million children of a hundred thousand parents). The child grouped first is kept when it costs less, not only when it costs half; count(e.id) over the nullable side now runs in the scan.
  • Faster: lookup joins. The classes of the keys of a joined dimension are made for text, varchar, numeric, float4, float8 and time columns and for the expressions an index holds or a test computes, as they were for integers, dates, timestamps and booleans, and for uuid, interval, bytea, inet and numeric of more than 18 digits where these sent the whole table to the heap (1,000,000 parents, 2,000,000 children, GROUP BY an interval column of 1,000 values: 1,099 ms to 28 ms; a column with more values than a table of classes holds, 4,096 tuples, is still read from the heap). The classes are kept in shared memory once a backend has read them from the leaves (sift.agg_dimmap_kb, 64 MB, 0 turns it off): a grouped count and sum of 1,000,000 facts joined to a dimension of 100,000 keys with 60 groups takes 76 M instructions (12 ms) with the map or with the classes the lookup caches keep, against 1,915 M in the normal plan, and with the dimension written since its last vacuum and its index connected 112 M to 91 M (105 M to 90 M on a vacuumed dimension), because the classes of the lookup caches need a table whose every page is visible. The classes of a dimension of 640,000 keys or more are made by workers together (5,000,000 keys: 138 ms in one process, 87 ms with four workers); the tests of a joined table’s columns are computed once for each tuple of values they read (4,000,000 keys and three tests: 195 ms where it took 495). min, max, sum and avg of a joined integer, date, timestamp or float8 column are added tuple by tuple (5,000,000 children of 250,000 parents, each parent with its own value: min and max of an integer 2,681 M instructions and 1,142 ms to 1,700 M and 525 ms; sum of an int4 2,400 M and 1,244 ms to 1,474 M and 514 ms; sum, min and avg of a float8 2,583 M and 786 ms to 2,057 M and 390 ms). A parallel aggregate whose scan joins one table by an integer, date or timestamp key splits the rows by that key (Rows Split By: the key of a lookup join): over 5,000,000 children of 100,000 parents the instructions with two workers are 1.11 times those of none where they were 2.04 times, with four 1.21 times, and the wall time is 116 ms (no workers), 62 ms (two) and 58 ms (four). The values of a column kept as a dictionary of numbers are looked up with one gather of four values on AVX2 and AVX-512: a sum and a count per group of 5,000,000 rows with a filter that keeps a quarter of them 263 M instructions to 235 M. A joined table whose join columns are in two sift_col indexes is loaded from the leaves of both where it was read from its heap.
  • Fixed: prices of the aggregate scan. A NOT IN of a hashed list is priced for a call of the subplan and a probe of its hash for each distinct value and for the misses of the cache when the values are many: a column of 1,000,000 rows and 447,000 values with NOT IN of a list of 1,000 was priced 8,028 units for the scan against 27,135 for the normal plan and ran 156 to 258 ms against 142 ms; it is priced 43,682 units now and PostgreSQL keeps it (a column of 1,000 values, 13 ms against 95, and one of 100,000, 20 to 51 ms, keep the scan). A table of keys that only tells whether a key exists is priced as the keys it is loaded as: a fact of 1,000,000 rows and a table of 1,000,000 sparse keys was priced 327,178 units for the scan against 90,166 and ran as the normal plan in 532 ms; it is priced 31,647 units now and runs in 139 ms (68 ms with a column index on the table). A lookup table that another lookup keeps rows of (the datasets of one tenant) is priced for the rows its index finds: a fact of 1,000,000 rows joined to a dimension of 500,000 rows, restricted to one tenant, read 22,609 rows of the dimension and was priced for 501,001; the scan is priced 25,051 units instead of 117,112 (the normal plan 13,673) and takes 26 ms against 141 ms.

Fixed

  • A join to a table kept as classes of its keys answered a query that reads the key column of that table itself with the key of the first row of the class (sum(t.k), count(DISTINCT t.k) over t.k = f.k for an int4 key and columns with few distinct tuples): such a join is no longer kept as classes and is loaded row by row.
  • CREATE TABLE AS and SELECT INTO of a statement that the extension plans as it is and with its sub-selects pulled up failed with XX000 unrecognized node type (and could read freed memory) the first time the statement was planned in a server, when the pulled-up plan won; the planner now plans a copy of such a statement.
  • A scan of a sift_table keeps the directory, write buffers, tombstones and versions it opened with, and the workers of a parallel aggregate scan or an index-fetch scan open on the ones the leader read: a later VACUUM or invalidation can no longer reach a scan that has begun, and a table nothing was written to is read without copying a null array. A parallel scan of a sift_col index opens on the leader’s heap end as well as its directory, and the participants of a parallel unranked scan of a sift index bind to the directory the leader exported.
  • A VACUUM with parallel index workers no longer fails to repair a log that a concurrent rebuild left incomplete (cannot assign transaction IDs during a parallel operation): the repair waits for a VACUUM without workers and the reads check the heap until then.
  • An index whose tombstone log has a newer column format is refused when opened, with the message and hint of the segments, where an older build would have appended tombstones to a log that may hold records it would not keep.

Options deprecated in 4.3 (removed in 4.4)

  • order_columns, point_columns and key_cut_rows are accepted and ignored or replaced. order_columns: a build writes no sorted run in its units, and the order arrays (order_array_columns, chosen by the build) answer a top-N, a range, an OFFSET and an IN list over any number of units for the same bytes where the runs read 4 to 6 times the instructions beyond a top-N of 32; a key_order no longer asks for the runs, and order_array_columns no longer has to name columns of order_columns. point_columns: a build lists its columns in the directory of heap addresses (point_tid_columns), which reads no unit for an equality or a short IN list where the directory of rows read the unit of each row, for the size (within 3%); a release before 3.7 cannot read the segments written so. key_cut_rows: the units a key_cut build, promotion or merge writes aim at the rows of an average superblock, which kept the number of units and the planner’s per-unit costs and gave no query a gain at any size measured. An index that carries one keeps reading the segments it has; CREATE INDEX and REINDEX say so in their NOTICE and in sift.index_choices, scripts/upgrade-check.sh lists the index as a warning with the ALTER INDEX .. RESET that clears it, and 4.4 removes the options (docs/UPGRADING.md).

Who is affected and what to do

  • Update (the steps, in this order): install the files and restart the server (a standby too), because a backend that already loaded the old library keeps it until its connection ends; then ALTER EXTENSION pg_sift UPDATE in each database. 4.3 is default_version = '4.3': a database at 4.2 runs pg_sift--4.2--4.3.sql, and a database at 4.2.1 or 4.2.2 first runs the empty script back to 4.2 and then that one. The script adds the functions and the view of connected indexes (sift.col_connection_state(), sift.col_connection_list(), sift.col_connection_fill(), sift.col_connections) and sift.col_arena_status(), and replaces sift.workers, sift.col_index_status and sift.upgrade_commands(); it drops nothing. Run scripts/upgrade-check.sh before and after: it lists the deprecated options as warnings, and nothing blocks the update.
  • Nothing has to be rebuilt. 4.3 reads every index and table that 4.2 and older wrote. An index keeps the choices it was built with: a merge makes the choices again for the segments it writes and REINDEX for all of them, so the serial id array, the second order by a group, the seekable chunks and the automatic connection reach an old index there. REINDEX INDEX CONCURRENTLY is worth it for an index with a serial id (5,000,000 rows: 63.3 MB to 49.1 MB), for one that is read by WHERE col = x ORDER BY ts LIMIT n through a b-tree that you want to drop, and for one whose table has a foreign key you want connected.
  • Everyone with a standby, a replica node or a rollback plan on 4.2 or older: a library before 4.3 refuses what 4.3 writes with its defaults. 4.3 writes column format minor 44 for a segment of an index with order arrays that a promotion or merge writes after the update (seekable and positional chunks), for a write buffer that holds a reference (sift.col_wbuf_ref, on by default) and for a connected index (the key journal of the parent and the flags of the child). A 4.2 library that reads such an index fails with has a segment of column format 1.44, this build reads 1.3 to 1.43 and the hint to REINDEX, and refuses the merge and promotion of a connected index (the tests found no wrong row). Update every node that reads an index before the primary writes one: the standbys first. SET sift.col_wbuf_ref = off and sift.col_auto_connect = off keep the references and the connections out; sift.col_format_minor = 14 keeps minor 44 out of what a build writes, and an index built under it does not track changes (see “Going back” in docs/UPGRADING.md). scripts/upgrade-check.sh --to 4.2 lists the indexes that 4.2 cannot read, with the setting that writes what it reads.
  • If you set order_columns, point_columns or key_cut_rows: they are ignored or replaced in 4.3 and removed in 4.4. Reset them now (ALTER INDEX .. RESET (option); for point_columns set point_tid_columns to the same columns first). If you use key_order = 'col' for lists of the key: it builds no point directory; set point_tid_columns on the key.
  • Plans change, files do not, for the joins: a statement of the shapes above may take the child-first plan or the found-flag answer from the next statement. SET sift.agg_child_first = off, sift.agg_connect_found = off and sift.enable_agg = off plan it as before; sift.explain_why(query) says why a statement did or did not take the scan.
  • Memory and locks to know: the write-buffer arena takes up to 128 MB of shared memory for the server, in 1 MB segments made on demand, and the shared dimension map takes up to sift.agg_dimmap_kb (64 MB) of dynamic shared memory; in a container keep both below what /dev/shm holds (docs/CONTAINERS.md). The first connected child of a parent holds the writers of the parent table out from the end of its scan to the end of its transaction.
  • If a statement reads the key column of a joined table through a lookup join (sum(t.k) or count(DISTINCT t.k) over t.k = f.k): the answer was wrong for tables kept as classes of their keys and is the normal plan’s now; compare such a result with SET sift.enable_agg = off if you stored it.
  • Going back to 4.2: docs/UPGRADING.md has the steps (scripts/upgrade-check.sh --to 4.2, the setting, REINDEX, the connections and the buffer of references, then the 4.2 files). There is no script back to 4.2 in the catalog.
  • Nothing else changes: answers outside the cases above are those of 4.2.2; BM25 (sift) indexes have no format change.

Compatibility

  • PostgreSQL 17, 18 and 19; the packages below cover all three (other builds from source with make USE_PGXS=1). A replacement of 4.2.x with the install and a restart; extension SQL 4.3 (pg_sift--4.2--4.3.sql; 4.2.1 and 4.2.2 reach it through their empty scripts back to 4.2); new sift_col column format minor 44, which 4.2 and older refuse; no BM25 metapage minor change (still 1.6, written since 4.2).

Packages

x86-64 and arm64 packages for PostgreSQL 17, 18 and 19 are attached below (SHA256SUMS covers every asset); other versions build from source with make USE_PGXS=1.

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

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

  • No change: the changes of this release are in the PostgreSQL column index, which is 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.
  • Index format 2: an index of 3.4.0 to 3.7.0 opens as it is, nothing to rebuild.

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