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](https://github.com/brauliobo/pg_sift/blob/v4.3.0/CHANGELOG.md) and [docs/OPTIONS.md](https://github.com/brauliobo/pg_sift/blob/v4.3.0/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](https://github.com/brauliobo/pg_sift/blob/v4.3.0/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](https://github.com/brauliobo/pg_sift/blob/v4.3.0/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 `UPDATE`s 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](https://github.com/brauliobo/pg_sift/blob/v4.3.0/docs/UPGRADING.md#what-43-deprecates-and-44-removes)). ## 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](https://github.com/brauliobo/pg_sift/blob/v4.3.0/docs/UPGRADING.md#42-to-43)). `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](https://github.com/brauliobo/pg_sift/blob/v4.3.0/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](https://github.com/brauliobo/pg_sift/blob/v4.3.0/docs/UPGRADING.md#42-to-43) 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](https://github.com/brauliobo/pg_sift/blob/v4.3.0/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](https://github.com/brauliobo/pg_sift/blob/v4.3.0/sqlite/README.md). Every change: [sqlite/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.3.0/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:` 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//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](https://github.com/brauliobo/pg_sift/blob/v4.3.0/mariadb/README.md). Every change: [mariadb/CHANGELOG.md](https://github.com/brauliobo/pg_sift/blob/v4.3.0/mariadb/CHANGELOG.md).