Upgrading

What to run, in order, to move a database to a newer pg_sift, to a newer PostgreSQL major, or back to an older pg_sift. On-disk formats and the first release that reads each: FORMATS.md.

Change What happens to the files Steps
a newer pg_sift (patch or minor) the new library reads the files of the older ones; it writes its own format from the next write on preflight, install and update
3.2.1 to 3.2.2 library only: the extension SQL stays at 3.2, no format changes install the files and restart the server (an open session keeps the old library, and pg_sift in shared_preload_libraries is read at start only); no ALTER EXTENSION pg_sift UPDATE, no REINDEX; going back to 3.2.1 needs nothing (notes)
4.0.2 to 4.0.3 the library and two functions, sift.index_choices and sift.help: the extension SQL goes from 4.0 to 4.0.3, no format changes install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database; no REINDEX; going back: an older release (notes)
4.0.3 to 4.0.4 the library only; the extension SQL stays at 4.0.3. The log header of a sift_col index with track_changes gets one more flag (additive: an older library reads it as an incomplete log and checks the heap) install the files, restart the server (a standby too); no ALTER EXTENSION UPDATE, no REINDEX: an index that 4.0.0 to 4.0.3 left with wrong counts is re-baselined by the next VACUUM of its table, by the first read, or by the maintenance worker (sift.col_tracking: complete true, heap_free false until then); going back: an older release
4.0.4 to 4.0.5 library only: the extension SQL stays at 4.0.3, no format changes; two wrong answers of the aggregate scan are fixed install the files and restart the server (a standby too; an open session keeps the old library); no ALTER EXTENSION pg_sift UPDATE, no REINDEX; going back needs nothing first (notes)
4.0.3 to 4.1.0 the library and the planner (new scans and costs; four new settings, sift.agg_probe_rewrite, sift.agg_probe_direct, sift.col_build_key_ranges, sift.col_auto_merge_pass_mb): the extension SQL goes from 4.0.3 to 4.1 with no change of its objects, no format changes install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database; no REINDEX (a REINDEX of a key_cut index builds it by key ranges); from 4.0 to 4.0.2 the update goes through 4.0.3 by itself; going back: an older release (notes)
4.1.0 to 4.1.1 library only: the extension SQL stays at 4.1, no format changes; one new value of an option, track_changes = 'ctid' (never chosen by auto) install the files and restart the server (a standby too; an open session keeps the old library); no ALTER EXTENSION pg_sift UPDATE, no REINDEX; going back to 4.1.0: first ALTER INDEX <index> SET (track_changes = 'auto') on every index you set to 'ctid' (4.1.0 does not know its row trigger and fails UPDATE and DELETE of the table with sift.col_track_old_rows is the statement trigger of an index option), then nothing else (notes)
4.1.1 to 4.1.2 library only: the extension SQL stays at 4.1, no format changes; the log of a sift_col index with track_changes that an earlier release wrote is re-checked and re-baselined on its own install the files and restart the server (a standby too; an open session keeps the old library); no ALTER EXTENSION pg_sift UPDATE, no REINDEX, no ALTER INDEX (after pg_upgrade, once for each index: see the section below); going back to 4.1.1 needs nothing first (notes)
4.1.2 to 4.1.3 library only: the extension SQL stays at 4.1, no format changes; REINDEX INDEX CONCURRENTLY of a sift_col index with track_changes no longer takes ACCESS EXCLUSIVE on the table install the files and restart the server (a standby too; an open session keeps the old library); no ALTER EXTENSION pg_sift UPDATE, no REINDEX; going back to 4.1.2 needs nothing first (notes)
4.1.3 to 4.1.4 library only: the extension SQL stays at 4.1, no format changes; two wrong answers of the aggregate scan are fixed install the files and restart the server (a standby too; an open session keeps the old library); no ALTER EXTENSION pg_sift UPDATE, no REINDEX; going back needs nothing first (notes)
a newer pg_sift from 4.1 to 4.2 seven changes of files, plans, a setting and one new SQL function what 4.2 changes
4.2.0 to 4.2.1 the library and one function, sift.col_track_convert(): the extension SQL goes from 4.2 to 4.2.1, no format changes; DROP INDEX CONCURRENTLY of a sift_col index with track_changes works (the index depends on its two triggers, where they depended on it) install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database: it converts the indexes of the database (it takes SHARE UPDATE EXCLUSIVE on each table that has a tracked index, which waits for a DROP INDEX, REINDEX or VACUUM of the table that is running and lets the writers go on), no DDL on a tracked sift_col index needs to wait for it (a CREATE INDEX, REINDEX, ALTER INDEX or DROP INDEX before the update behaves as after it), only the DROP INDEX CONCURRENTLY of an index made by 4.2.0 or earlier does, and until it runs the concurrent drop of an index made by 4.2.0 or earlier still fails (its triggers are found, so reads, writes, REINDEX, ALTER INDEX and a plain DROP INDEX work as before; an index made before the update is dropped with its triggers); no REINDEX is needed to drop; going back to the 4.2.0 library after the update leaves such an index with triggers that library does not find (REINDEX there makes them again), and ALTER EXTENSION pg_sift UPDATE TO '4.2' does not undo the conversion (its script is empty: the index keeps depending on its triggers, and a plain DROP INDEX with the 4.2.0 library then leaves the triggers behind, which fail the writes of the table: drop them with DROP TRIGGER after the index)
4.2.1 to 4.2.2 the library and one function, sift.table_find_stale_versions(): the extension SQL goes from 4.2.1 to 4.2.2, no format changes; a VACUUM of a sift_table that waited for the maintenance lock no longer makes old versions of updated rows, or deleted rows, visible again (4.0.0 to 4.2.1) install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database; no REINDEX; the update does not repair rows that already came back: run sift.table_find_stale_versions('<table>') on each updated sift_table and follow the notes; a database at 4.2 goes through 4.2.1 by itself; going back to 4.2.1: install its files, restart, ALTER EXTENSION pg_sift UPDATE TO '4.2.1' (the function stays, the cause returns) (notes)
4.2 to 4.3 (also from 4.2.1 and 4.2.2) a new minor: the library, the planner, connected indexes, a write buffer of references, seekable order chunks (column format minor 44, which 4.2 and older refuse), three deprecated options and new functions: the extension SQL goes from 4.2 to 4.3 install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database; no REINDEX is needed; update every node that reads an index (the standbys first) before the primary writes one; going back needs a setting, REINDEX and the 4.2 files: 4.2 to 4.3 (notes)
4.3 to 4.3.1 the library and one statement of the extension SQL (sift.table_option_values gets its replica identity: with a FOR ALL TABLES publication, 4.2.0 to 4.3.0 failed every DROP INDEX and DROP TABLE of the database, ALTER EXTENSION pg_sift UPDATE repairs it), no new object, no format changes; a read inside a statement that deletes rows (an AFTER ROW trigger, an AFTER STATEMENT trigger that fires before the index’s own, a function the statement calls) no longer counts the rows the statement deleted, through a sift_col index with track_changes (4.0.0 to 4.3.0) install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database; no REINDEX, nothing stored is wrong; until then ALTER INDEX <index> SET (track_changes = 'off') on the indexes read that way; going back to 4.3: install its files, restart, ALTER EXTENSION pg_sift UPDATE TO '4.3' (the cause returns) (notes)
4.3.1 to 4.3.2 the library only: the extension SQL goes from 4.3.1 to 4.3.2 with no new object, no format changes; a correlated sub-select over a join with a derived table that PostgreSQL does not pull up (GROUP BY, DISTINCT, UNION, HAVING) no longer returns the answer of an earlier outer row (4.0.5, 4.1.4, 4.2.2, 4.3.0 and 4.3.1) install the files, restart the server (a standby too), then ALTER EXTENSION pg_sift UPDATE in each database; no REINDEX, nothing stored is wrong; until then SET sift.enable_agg = off for such a query; going back to 4.3: install its files, restart, ALTER EXTENSION pg_sift UPDATE TO '4.3' (the cause returns) (notes)
a newer PostgreSQL major pg_upgrade copies (or links) the files as they are pg_upgrade
an older pg_sift (a downgrade, or a standby that stays on an older release) an older library refuses what it cannot read preflight with --to

1. Preflight

scripts/upgrade-check.sh runs sift.upgrade_check() (scripts/upgrade_check.sql) in each database. It reads the catalogs and metapages and changes nothing (the function is loaded in a transaction that is rolled back, so a database with an older extension can be checked). It needs a superuser on the primary and the files of the new release installed:

scripts/upgrade-check.sh --all                 # every database that accepts connections
scripts/upgrade-check.sh app reporting         # these databases (or connection strings)
scripts/upgrade-check.sh --strict app          # a warning also ends with exit status 1

One row per finding: STATUS (ok, warn, blocker), OBJECT, KIND, DETECTED and ADVICE. The exit status is 0 when nothing blocks, 1 when a database has a blocker (or a warning with --strict) and 2 when a database could not be checked.

Kind What it looks at Status and advice
version pg_extension against sift.version() warn: the library is newer than the extension (ALTER EXTENSION pg_sift UPDATE) or the new release is available; blocker: the library is older than the extension scripts
index, table the metapage format and the column formats of every sift index, sift_col index and sift_table blocker: this library cannot open it (the error and its hint are the advice); with --to, the first release that reads it is newer than the target
option options that only units written afterwards follow warn: cube_minmax, rollup_columns, key_columns, order_columns, point_columns, point_tid_columns, key_cut or hll_columns on an index with older units, JSON paths on segments before column format 6 (the advice is the command from sift.upgrade_commands(), which an extension older than 3.3 has after its update; a sift_table below the format of its schema is a table row with VACUUM FULL), the deprecated mutable_limit
setting sift.* in the server configuration, the session, ALTER DATABASE and ALTER ROLE warn: a setting the library does not define (removed or renamed), a feature switched off (a boolean setting that defaults on), sift.col_format_minor kept below the default
trigger with --to a release before 3.3: the event triggers of 3.3 blocker: the older library has not their function and would fail every ALTER TABLE and CREATE TRIGGER; ALTER EVENT TRIGGER x DISABLE
test test-only settings and the extension pg_sift_test warn: remove them; they need the test build of the library

Run it before and after the update: the second run must have no warn the first one explained.

2. Install and update


# 1. install the files of the new release (package, or from source)
sudo apt install ./postgresql-N-pg-sift_X.Y.Z-1_amd64.deb
make USE_PGXS=1 && sudo make USE_PGXS=1 install

# 2. preflight (section 1), then in every database that has the extension
psql -d app -c "ALTER EXTENSION pg_sift UPDATE"

#    library keeps it until its connection ends, so after every library install restart the server or reconnect every pool and worker)
sudo systemctl restart postgresql

# 4. check the update
psql -d app -c "SELECT extversion, sift.version() FROM pg_extension WHERE extname = 'pg_sift'"
psql -d app -c "SELECT sift.verify('docs_idx', true)"            # a sift index
psql -d app -c "SELECT sift.col_verify('facts_col', true)"       # a sift_col index
scripts/upgrade-check.sh app

ALTER EXTENSION pg_sift UPDATE runs the scripts from the installed version to the default one of the control file, through every release in between. Index and table files are not rewritten: an older index stays in its format until a merge, VACUUM or REINDEX writes it again, and the preflight and the update tell which REINDEX is worth it. A sift_table cannot be reindexed; it follows sift.col_format_minor on its next write.

The extension version of a patch release. From 4.2 a patch release X.Y.N has the extension version X.Y.N, and sift.version() (the loaded library) returns the same string, so ALTER EXTENSION pg_sift UPDATE after installing the files moves the database to it and the two queries of step 4 agree. The step from one patch release to the next changes no object. A database at any X.Y.N reaches the next minor with the one script of that minor, because every patch release also ships an empty script back to X.Y. The releases before 4.2 kept the extension version of their minor (4.1.1 to 4.1.4 are 4.1, 4.0.4 and 4.0.5 are 4.0.3), so on them ALTER EXTENSION pg_sift UPDATE has nothing to do and PostgreSQL prints its own notice, version "4.1" of extension "pg_sift" is already installed: it is harmless, the library files are the ones that changed. sift.version() of those releases is the minor too (4.1); the release that runs is in SELECT version FROM pg_get_loaded_modules() WHERE module_name = 'pg_sift' (PostgreSQL 18 and later: 4.1.4+ and the commit), after a restart or a reconnect, as an open session keeps the library it loaded. If sift.version() is older than extversion, the library files are older than the extension scripts: install the release or a newer one (scripts/upgrade-check.sh reports it as a blocker).

The last step of an update to 3.3 (from any older version) prints what is left to do, as warnings with the commands (the first 20 of each, then the count of the rest), or a notice that nothing is (PostgreSQL shows only warnings from an extension script). SELECT * FROM sift.upgrade_commands() lists the same later, for the indexes and tables the role owns or may maintain (every one for a superuser): one row per command (kind reindex or rewrite, the object, the reason, the command), the cheapest first.

Warning Why Command
a sift (text) index needs a REINDEX queries miss rows: since 3.3 a word over 255 bytes is stored as its first 236 bytes and a hash; an older index holds it cut at 255 bytes, so a query for it finds nothing. The index does not record whether it holds such a word, so every text index except analyzer = keyword is listed, also after its REINDEX REINDEX INDEX CONCURRENTLY schema.index;
a sift_col index would gain from a REINDEX an option set after the units were written (cube_minmax, JSON paths, rollup_columns, key_columns, order_columns, point_columns, point_tid_columns, key_cut, hll_columns): older units stay readable and are scanned (for order_columns, read whole by an ORDER BY .. LIMIT) until then REINDEX INDEX CONCURRENTLY schema.index;
a sift_table would gain from a rewrite segments below the column format of its schema: no calendar cells for its date and timestamp columns (format 8), JSON kinds that may be incomplete for declared paths (format 6). A sift_table cannot be reindexed VACUUM FULL schema.table;

Only the first warning means wrong results without the command. VACUUM FULL takes an ACCESS EXCLUSIVE lock and rewrites the whole table in the format of sift.col_format_minor, so schedule it; once it ran the table leaves the list. REINDEX CONCURRENTLY cannot run in a transaction block: run the commands one at a time. The check reads the catalogs and metapages and cannot fail the update; when it cannot answer (a relation locked by another session) it says so, and sift.upgrade_commands() can be run after reconnecting.

What 4.2 changes

Ten changes of 4.2 touch what an index holds, what an older library does with it, a plan you may have measured, or an object you may have made. Only 7, 8 and 9 ask for a step (remove a setting that is no longer accepted, before the update); ALTER EXTENSION pg_sift UPDATE runs pg_sift--4.1--4.2.sql and the new library reads the indexes of 4.1 and older as they are. Each entry says what changes, what an older library does with the result, whether a REINDEX is needed, and the switch that turns it off.

  1. The index keeps a presence bit for the jsonb columns that may be NULL. CREATE INDEX and REINDEX of a sift_col index write one boolean leaf for each jsonb column that has paths and no NOT NULL constraint on the column of the table (an index expression counts as nullable; a CHECK or a domain that forbids NULL is not seen), so NOT (doc @> x), (doc @> x) IS FALSE, NOT (doc ? 'k') and doc IS [NOT] NULL, also in a FILTER or a CASE, are answered by the index where they read the table. There is no option and no NOTICE: the layout of an index is the one it was built with (ALTER TABLE .. SET NOT NULL or DROP NOT NULL changes nothing; REINDEX decides again). No column format minor changes. Measured on 150,000 documents, 1% to 50% of them without a declared member: count(*), sum(id) WHERE NOT (doc @> x) 84.9 M to 7.4 M cycles at 1% and 85.0 M to 9.9 M at 50%, count(*) WHERE doc IS NULL 34.9 M to 1.9 M at 5%. The price, on 1,000,000 rows: CREATE INDEX +3.2% instructions, an insert +0.4%, a promotion +4.6%, a merge +5.0%, the index +2.0% in size after a merge (up to +13% right after the build of a wide index, which a merge removes).

    • Existing indexes are not touched: they have no leaf, those forms keep reading the table, and nothing is wrong; REINDEX writes the leaf.
    • An older library (3.2, 4.0 and 4.1, tried with 3.2.3, 4.0.5 and 4.1.4 on the files of 4.2) refuses an index that has the leaf: a read, an aggregate included, fails with sift_col index "x" does not match its column layout (hint REINDEX the index.), a write with index "x" was built with other column definitions (hint Use REINDEX to apply the new options.), and sift.col_verify reports The metapage does not describe this column layout. The way back is to REINDEX INDEX x with the older release, which writes the layout it reads; there is no switch. scripts/upgrade-check.sh --to 4.1 lists the indexes with the leaf as blockers, with that advice.

  2. The write buffer of a BM25 (sift) index is format 6. The full pages of the buffer have a bloom of term hashes of 8,192 bits where format 0 has 256, so a search for a term that none of the buffered documents has no longer reads every page of the buffer (220,000 documents and a buffer of 2,000: 1.96 M to 1.23 M cycles). Upgrading takes no step: the buffer that is open at the update keeps format 0 and is read as it is; the next one is format 6, and the first of them raises the metapage of the index to minor 1.6 and nothing lowers it (FORMATS.md).

    • Existing indexes need nothing; sift_col indexes and sift_table tables have no such buffer.
    • An older library (4.1 and older) refuses a BM25 index of 1.6 on a read, a sift.verify and an insert: sift index "x" has on-disk format 1.6, this build (pg_sift 4.1) reads formats 1.0 to 1.5, hint Install the pg_sift release that wrote the index on every node that reads it, or REINDEX it. A standby or a node that stays on the older release is such a node. scripts/upgrade-check.sh --to 4.1 lists the index as a blocker.
    • To go back: install the older release and REINDEX the index there (it is built again from the table). Off: none; an index that has taken no insert since the update is still format 0.

  3. The aggregate scan is chosen more often for a grouped aggregate of one table. For a grouped aggregate with no lookup join, no search and no top-N whose normal plan is a sequential scan and a hash of the groups, the scan’s own work per row is now priced in the units of the plan it is compared with (PostgreSQL’s estimate of that plan runs at about 210 cost units a millisecond, the scan’s price at 833), so a scan that was up to 4 times faster is no longer refused as cost when its rows are not few: max(ends - starts) by tenant over 150,000 rows 18.6 to 7.1 ms, a COALESCE measure over 20,000 events 3.4 to 2.1 ms. A statement the scan does not win stays on the normal plan. This changes plans, not files.

    • An older library and the indexes: nothing; a statement is planned by the library that runs. REINDEX: no.
    • Off: no switch separates this choice; SET sift.enable_agg = off (a session, a role or a database) plans the statement as PostgreSQL does without pg_sift, and sift.explain_why(query) says why a statement did or did not take the scan.

  4. One rule for HAVING column IN (SELECT ..) and EXISTS (SELECT ..), and sub-selects of the groups. In a plain GROUP BY, such a HAVING condition is always answered as a filter of the rows, as a semi join the scan runs, where PostgreSQL keeps it above the aggregate and the sub-select ran for each group (GROUP BY kind_id HAVING kind_id IN (SELECT id FROM kinds WHERE enabled): 300,000 rows, 1,000 keys, 40.1 to 0.58 ms); the rows form was the cheaper in all 40 cells measured, at 0.09 to 0.78 of the cycles. A sub-select in the select list or the HAVING that reads the group keys or the aggregates is evaluated by the scan once for each group (36.8 to 1.85 ms for 300,000 rows and 1,000 groups). NOT IN, NOT EXISTS, an expression or a cast of the key, a correlated IN, grouping sets and a HAVING without GROUP BY keep their HAVING. The rows are the same (agg_having_where: 46 statements equal to the rows with the scan off).

    • An older library, REINDEX: nothing, no file changes. Off: no switch for this rule alone; sift.enable_agg = off as above.

  5. A sift_col index with track_changes repairs its own log in more cases. The log of an index whose triggers do not vouch for it (after pg_upgrade, which makes the triggers again; pg_restore --disable-triggers; ALTER TABLE .. DISABLE TRIGGER ALL then ENABLE TRIGGER ALL; CREATE INDEX CONCURRENTLY; REINDEX CONCURRENTLY; DETACH PARTITION) was checked against the heap on every read until REINDEX or ALTER INDEX. Now the next VACUUM, the first read that checks the heap or the maintenance worker that a write asks for re-baselines it (ShareUpdateExclusiveLock only). Triggers enabled for the origin only (what ENABLE TRIGGER ALL leaves, tgenabled = 'O') are enabled for all sessions ('A') by the worker, which asks for the lock of ENABLE TRIGGER without waiting; triggers you disabled ('D') stay disabled and the reads keep checking the heap. 300,000 rows churned and not vacuumed: the second statement of a grouped count costs 38.1 M instructions and 918 blocks while the log is not vouched, 29.7 M and 467 when it is; after the repair it is 33.9 M and 585 (the repaired log is a little larger, a VACUUM or a merge shrinks it).

    • Format: none, no minor changes; answers are right before and after. An older library: tried with 4.1.1 on an index that 4.2 repaired (ALTER TABLE .. DISABLE TRIGGER ALL, ENABLE TRIGGER ALL, a VACUUM, the worker on: sift.col_tracking true, true, true, true and the triggers 'A' again): it reads and writes the index without an error, its aggregates equal the heap’s before and after its own writes and sift.col_verify is clean, but it does not take the log for complete (sift.col_tracking: triggers, complete and heap_free false), so its reads check the heap as they did before the repair; the repair itself is the re-baseline of 4.1.2 and later. REINDEX: not needed. Off: none for the repair; ALTER INDEX x SET (track_changes = 'off') drops the log and its triggers for an index that should not have them.

  6. sift.refusal_actions() returns what to change for each refusal slug (the statement, the index or a setting, the hint of the notice of sift.explain_refusals): sift.explain_why(q) w JOIN sift.refusal_actions() a USING (slug). It is a new function; sift.refusal_slugs() keeps its three columns, and pg_sift--4.1--4.2.sql drops or replaces no function, so a view, a default or a function of yours that reads the objects of 4.1 does not make ALTER EXTENSION pg_sift UPDATE fail (test/t/extension_lifecycle.sh updates a database that has a view over sift.refusal_slugs()). Files, REINDEX, off: not applicable.

  7. sift.eval_methods no longer takes latest_hint, snapshot_masks, unit_rewrite and skip_trigger_lookup (nor the -name form of them); penalty_maintenance is the only name left. skip_trigger_lookup is now always on (the statement trigger of track_changes leaves out the look at an UPDATE that kept the key), latest_hint and snapshot_masks had nothing behind them, and the writer of unit_rewrite is gone (segments an earlier release wrote with it stay readable). What happens to a setting that names one (tried on this tree): with the library loaded, a SET, ALTER ROLE .. SET, ALTER DATABASE .. SET and ALTER SYSTEM SET fail with ERROR: invalid value for parameter "sift.eval_methods": "NAME" and DETAIL: Unknown evaluation method: NAME, so a script that runs one breaks; a value already stored (postgresql.conf, postgresql.auto.conf, a role or database setting) does not stop the server or a login, but every session that loads the library and the log of the server say the same text as a WARNING, and the setting is ignored. Remove them before updating: sql SELECT coalesce(r.rolname, '(all roles)') AS role, coalesce(d.datname, '(all databases)') AS database, c AS setting FROM pg_db_role_setting s LEFT JOIN pg_roles r ON r.oid = s.setrole LEFT JOIN pg_database d ON d.oid = s.setdatabase, unnest(s.setconfig) c WHERE c LIKE 'sift.eval_methods=%'; -- ALTER ROLE r [IN DATABASE d] RESET sift.eval_methods; or ALTER DATABASE d RESET sift.eval_methods SELECT name, setting, sourcefile, sourceline FROM pg_file_settings WHERE name = 'sift.eval_methods'; -- superuser; remove the line, or ALTER SYSTEM RESET sift.eval_methods, then SELECT pg_reload_conf() scripts/upgrade-check.sh lists each of them as a blocker with the command (any release of the library, so it can run before the update). Files, REINDEX: no. Off: the names are gone; leave the setting empty.

  8. sift.enable_col is removed. It was declared in 3.0 and no code ever read it (sift.enable_col_scan and sift.enable_agg are the switches of the planner), so setting it changed nothing, whatever its documentation said. What happens to a setting that names it (tried on this tree): with the library loaded, a SET, ALTER ROLE .. SET, ALTER DATABASE .. SET and ALTER SYSTEM SET fail with ERROR: invalid configuration parameter name "sift.enable_col" and DETAIL: "sift" is a reserved prefix., so a script that runs one breaks; a value already stored (postgresql.conf, postgresql.auto.conf, a role or database setting) does not stop the server or a login, but every session that loads the library and the log of the server say WARNING: invalid configuration parameter name "sift.enable_col", removing it with DETAIL: "sift" is now a reserved prefix., and SHOW sift.enable_col is unrecognized configuration parameter. Remove it before updating: sql SELECT coalesce(r.rolname, '(all roles)') AS role, coalesce(d.datname, '(all databases)') AS database, c AS setting FROM pg_db_role_setting s LEFT JOIN pg_roles r ON r.oid = s.setrole LEFT JOIN pg_database d ON d.oid = s.setdatabase, unnest(s.setconfig) c WHERE c LIKE 'sift.enable_col=%'; -- ALTER ROLE r [IN DATABASE d] RESET sift.enable_col; or ALTER DATABASE d RESET sift.enable_col SELECT name, setting, sourcefile, sourceline FROM pg_file_settings WHERE name = 'sift.enable_col'; -- superuser; remove the line, or ALTER SYSTEM RESET sift.enable_col, then SELECT pg_reload_conf() scripts/upgrade-check.sh lists each of them as a blocker with the command (any release of the library, so it can run before the update). Files, REINDEX: no. Off: not applicable; to compare a plan with the normal one, SET sift.enable_agg = off and SET sift.enable_col_scan = off.

  9. sift.table_auto_defrag_dead_pct is removed. It was the trigger of the background pass of a sift_table in 3.3 (a percent of tombstones); since 4.0 the pass is asked for by the reads of the table and the setting changed nothing. It is refused and dropped exactly as sift.enable_col of item 8 (ERROR: invalid configuration parameter name "sift.table_auto_defrag_dead_pct", DETAIL: "sift" is a reserved prefix.; a stored value: WARNING: invalid configuration parameter name "sift.table_auto_defrag_dead_pct", removing it), and the queries and commands of item 8 with that name find and clear it; scripts/upgrade-check.sh lists each as a blocker. Files, REINDEX: no. Off: not applicable (sift.table_auto_defrag = off turns the pass off).

  10. Two reloptions of sift_col have clearer names: rollup is rollup_leaves (the array elements and map values a unit groups by) and rollup_dims is rollup_columns (the scalar columns of the rollup of the rows). The older names stay accepted through 4.x: CREATE INDEX and ALTER INDEX .. SET with one say NOTICE: reloption "rollup" is the older name of "rollup_leaves" ("rollup_dims" and "rollup_columns" for the other) and read it as the new one; when both are given the new one wins; ALTER INDEX .. SET and RESET of the old name work; an index that carries the old name keeps it in pg_class.reloptions, pg_dump writes it as it is, and pg_upgrade and ALTER EXTENSION UPDATE need nothing. The build writes the new name where it decides a value itself (rollup_columns = 'auto: a, b'). The messages, the choices of sift.index_choices and the errors name the new spelling (invalid rollup_columns option: ..). sift.upgrade_commands() matches both spellings, so an index with either gets the same advice; a script or a view of yours that matches the text of pg_class.reloptions for rollup_dims should match rollup_columns too. sift.index_choices reports the choice as rollup_columns. The limit ids of sift.stats() keep their names (limit.rollup_dims). Files, REINDEX: no (the files hold no option names). Off: not applicable; the old names go away in 5.0.

4.2 to 4.3

ALTER EXTENSION pg_sift UPDATE runs pg_sift--4.2--4.3.sql (4.3 is default_version = '4.3'); a database at 4.2.1 or 4.2.2 first runs the empty script back to 4.2 that every patch release ships, 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() (a view or a function of yours that reads them keeps working); it drops nothing. The new library reads every index and table that 4.2 and older wrote, and nothing asks for a REINDEX. Four things are worth reading before the update.

  1. Column format minor 44, which a library before 4.3 refuses. A segment is stamped with the lowest minor that describes what it holds. A 4.3 build writes minor 44 for each of the following, and the defaults of 4.3 turn each of them on:

    Written when By default Switch
    an order chunk that is seekable, a positional chunk of the second order by a group, or the packed table of groups: every index with order arrays chosen by the build yes, under sift.col_format_minor 15 or more SET sift.col_format_minor = 14 before the build
    the tombstone log of a parent index that has a connected child (the key journal), and the segments of a connected child (a found flag for each connection) when an index is connected (below) sift.col_auto_connect = off, connect = 'off'
    a write buffer that holds a record that is a reference to a heap tuple (sift.col_wbuf_ref) yes, for a heap table whose index tracks changes sift.col_wbuf_ref = off (records already written stay references until the buffer is promoted)

    A 4.2 library that reads such a segment fails with sift_col index "x" has a segment of column format 1.44, this build reads 1.3 to 1.43 and the hint to REINDEX (a scan, VACUUM, sift.col_verify and sift.col_segments); it refuses the merge and the promotion of a connected index, and appends tombstones to the log of a parent without the journal records, so a database must not be written by 4.2 once a connected child exists. No answer is wrong: the tests found no wrong row. scripts/upgrade-check.sh --to 4.2 lists each such index as a blocker. An index written by a pre-release build of 4.3 (which stamped minors 45 to 47) is refused by the release with this build reads 1.3 to 1.44 and needs a REINDEX; no released version is concerned.

  2. What happens to an index that exists. Nothing, until a write or a rebuild: a merge makes the choices of the build again for the segments it writes, and REINDEX for all of them, and the old and the new segments answer the same queries meanwhile. So an index of 4.2 gets, from its next merge or REINDEX: a serial id as an order array and no point directory (5,000,000 rows: 63.3 MB to 49.1 MB), the second order by a group column chosen from a b-tree of the table or from a foreign key, seekable order chunks (minor 44), and, at REINDEX, the automatic connection. The choices are named in the NOTICE of CREATE INDEX and REINDEX and listed by sift.index_choices('index'): sift_col index "x" is column format minor 44, which a library before 4.3 cannot read.

  3. sift.col_format_minor is kept. It is the switch for a rollback: SET sift.col_format_minor = 14 before a build writes a format that an older library reads (the hint of that NOTICE), and the build then says does not track changes: sift.col_format_minor is below 15 for an index that would track them. It is a superuser setting, and the background worker that rewrites an index follows the server and database settings only, so use ALTER SYSTEM or ALTER DATABASE when no write may go beyond 4.2 (the same rule as for 3.2 below).

  4. A new write path and two new plans. (a) sift.col_wbuf_ref (on): the rows of a write buffer of a heap table with a tracked index are 16-byte references and their values live in a shared arena of up to 128 MB for the server; the first read of a buffer after a restart, on a standby or on a promoted standby reads the heap once (about 11,900 instructions a row). (b) CREATE INDEX and REINDEX of a sift_col index connect it to the sift_col index of the table that each validated single-column integer foreign key references (sift.col_auto_connect, on), and say so in a NOTICE; a REINDEX of an index of 4.2 can therefore connect it, and the first child of a parent holds the writers of the parent table out from the end of its scan to the end of the transaction. © The planner takes the child-first plan and the found-flag answer where they cost less (sift.agg_child_first, sift.agg_connect_found; sift.enable_agg = off plans as PostgreSQL does). Only (a) and (b) change files: they are the minor above; © changes plans only.

What to do.

  • Update as in section 2: install, restart (a standby too), ALTER EXTENSION pg_sift UPDATE in each database, then scripts/upgrade-check.sh (it warns for the three deprecated options below and for the test setting sift.test_col_legacy_options; nothing blocks). Update the standbys and every node that reads the index before the primary, or set sift.col_wbuf_ref = off and sift.col_auto_connect = off (and sift.col_format_minor = 14 for the other sections of minor 44) on the primary until they are updated.
  • REINDEX INDEX CONCURRENTLY is worth it for an index with a serial id, for an index whose table is read by WHERE col = x ORDER BY ts LIMIT n through a b-tree you want to drop, and for a child table whose foreign key you want connected; not for the rest. Reset the three deprecated options first (below). A REINDEX after ALTER INDEX x SET (connect = 'off') leaves an index unconnected.
  • The options order_columns, point_columns and key_cut_rows are accepted and ignored or replaced by 4.3 and removed in 4.4: What 4.3 deprecates and 4.4 removes. key_order = 'col' of one column no longer builds the point directory: set point_tid_columns on the key for lists of keys.

Going back to 4.2

Run this on the 4.3 database while the 4.3 library still runs. There is no script from 4.3 to 4.2: ALTER EXTENSION pg_sift UPDATE TO '4.2' fails with extension "pg_sift" has no update path from version "4.3" to version "4.2", the catalog stays at 4.3, and what 4.3 added to it (the functions of connected indexes and sift.col_arena_status()) is unusable with the 4.2 library, as in the section “After the downgrade” below.

# 1. what 4.2 cannot read: one row for each index, with the advice
scripts/upgrade-check.sh --to 4.2 app

# 2. every write from now on is readable by 4.2 (superuser; new sessions pick it up)
psql -d postgres -c "ALTER SYSTEM SET sift.col_wbuf_ref = off" -c "ALTER SYSTEM SET sift.col_auto_connect = off" \
     -c "ALTER SYSTEM SET sift.col_format_minor = 14" -c "SELECT pg_reload_conf()"

# 3. connected indexes: switch the connection off (child and parent), then rebuild every index of step 1
psql -d app -c "ALTER INDEX public.events_col SET (connect = 'off')"
psql -d app -c "REINDEX INDEX CONCURRENTLY public.events_col"
psql -d app -c "REINDEX INDEX CONCURRENTLY public.dim_a_col"

# 4. nothing blocks any more (exit status 0; the warnings that the settings are below the default are the point)
scripts/upgrade-check.sh --to 4.2 app

# 5. stop the server, install the 4.2 files, start it; then check
psql -d app -c "SELECT sift.col_verify('events_col', true)"
  • The REINDEX under sift.col_format_minor = 14 writes no seekable chunk and no section of minors 15 and above (so no minor 44), and an index built so does not track changes (its NOTICE says it), so its reads check the heap. After the 4.2 files run, ALTER SYSTEM RESET sift.col_format_minor and REINDEX the index again to log its changes again; keep sift.col_format_minor = 14 while any 4.3 library still writes.
  • ALTER INDEX x SET (connect = 'off') drops the dependency on the parent index, and the parent keeps its journal (minor 44) until its own REINDEX, which is why step 3 rebuilds the parent too. sift.col_wbuf_ref = off does not turn a buffer that holds references into one that does not: the REINDEX of step 3 does, or its promotion.
  • A standby that stays on 4.2 needs steps 2 and 3 on its primary before anything is written, and sift.col_format_minor set there for every later write.
  • Settings the 4.3 library accepts and 4.2 does not know (sift.col_wbuf_ref, sift.col_auto_connect, sift.agg_child_first, sift.agg_connect_found, sift.agg_dimmap_kb, sift.col_connect_refresh_pct): remove them from postgresql.conf and from role and database settings when the 4.2 files are installed, as for any setting a release does not define.

What 4.3 deprecates and 4.4 removes

Three sift_col options are accepted and ignored by 4.3 and are removed in 4.4, when an ALTER INDEX .. SET or a CREATE INDEX that names one is refused. A CREATE INDEX or a REINDEX that names one says so in its NOTICE, and scripts/upgrade-check.sh lists each index that carries one as a warning with the ALTER INDEX .. RESET that clears it. Indexes built before keep reading as they are; REINDEX writes the layout the defaults choose.

Option What 4.3 does What to do before 4.4
order_columns writes no sorted run; the order arrays (order_array_columns, chosen by the build) answer a top-N, a range and an OFFSET ALTER INDEX .. RESET (order_columns); keep order_array_columns if you set it
point_columns lists its columns as heap addresses, as point_tid_columns does set point_tid_columns to the same columns and reset point_columns
key_cut_rows the units aim at the rows of an average superblock ALTER INDEX .. RESET (key_cut_rows)

order_layout and sift.col_format_minor stay through 4.4. The setting sift.test_col_legacy_options, which makes a build honor the three options and which the test clusters of this project set, is for tests only and goes away with them; do not set it on a server.

3. A newer PostgreSQL major

pg_upgrade copies the relation files and restores the catalogs. pg_sift needs only its library for the new major next to the server (the same release as before: pg_upgrade does not change the extension version), and the same data checksum setting on both clusters (PostgreSQL 18 enables checksums by default; a cluster without them is initialised with initdb --no-data-checksums).


# the library of the same pg_sift release for the new major
sudo apt install ./postgresql-N-pg-sift_X.Y.Z-1_amd64.deb
make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config && sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config install   # from source

# the new cluster, then the check (it starts both clusters and loads pg_sift in each), then the upgrade
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main
/usr/lib/postgresql/18/bin/pg_upgrade -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
    -d /var/lib/postgresql/17/main -D /var/lib/postgresql/18/main --check
/usr/lib/postgresql/18/bin/pg_upgrade -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
    -d /var/lib/postgresql/17/main -D /var/lib/postgresql/18/main

Afterwards: copy the sift.* lines of the old postgresql.conf (pg_upgrade carries settings stored in the catalogs, ALTER DATABASE and ALTER ROLE, not the file), start the new cluster, run vacuumdb --all --analyze-in-stages, and check as in section 2 (sift.verify, sift.col_verify, scripts/upgrade-check.sh --all). BM25 results and scores, column aggregates and sift_table contents are the same as before, and writes, VACUUM and merges work on what the old cluster wrote (test/t/pg_upgrade.sh: PostgreSQL 17 to 18 when both are installed, otherwise 18 to 18). The triggers of a sift_col index that tracks its changes are made again by the restore, so until the log is re-baselined (the first VACUUM of the table, which the step above runs, or the maintenance worker that a read or a write of the table asks for) its reads check the heap: the answers are right, and the instructions of a statement over rows that were not vacuumed rise (sift.col_tracking(index) shows triggers false). Nothing to run by hand (test/t/col_track_revouch.sh: a cluster written by 4.1.0, PostgreSQL 17 to 18).

4. An older release

A BM25 (sift) index written by 4.2 is not readable by 4.1 or older once it has taken an insert. 4.2 opens the write buffer of a BM25 index in buffer format 6 (a bloom of 8,192 bits for each full page, FORMATS.md); the first such buffer raises the metapage of the index to minor 1.6 and nothing lowers it. A 4.1 or older library refuses the index with the format error and a REINDEX hint (reads formats 1.0 to 1.5). Upgrading takes no step: the buffer that is open at the upgrade keeps format 0 and is read as it is. To go back, install the older release and REINDEX the index there (the old library does not read the index, but REINDEX builds a new one from the table); a sift_col index or a sift_table is not affected.

A database written by the 3.3 library with its default settings can no longer be read by a 3.2 library. An older library refuses a metapage minor or a column format it does not know, with a REINDEX hint (old_lib_refusal), and 3.3 writes at sift.col_format_minor = 15 by default: a sift_col index created or rewritten after the update carries sections 3.2 does not know, and so does a sift_table with a date or timestamp column. Only what was written with the settings below stays at minor 4 or 6, the highest a 3.2 library reads (a segment is stamped with the lowest minor that describes its content):

Minor Written when Keeps the 3.2 bytes
7 a sift_col index with cube_minmax (on by default) WITH (cube_minmax = false), or sift.col_format_minor 6 or less
8 the calendar cells of a date, timestamp or timestamptz column: a sift_col index (calendar_columns defaults to all of them), a sift_table WITH (calendar_columns = 'off'); a sift_table: sift.col_format_minor 7 or less
9 rollup_columns (option, off) do not set it, or sift.col_format_minor 8 or less
10 key_columns naming a column that keeps a summary of its keys: text or uuid, or an int2, int4, int8 or date one whose key list or bitmap would not fit (option; auto never picks one) do not name it, or sift.col_format_minor 9 or less
11 a sift_table write buffer that took a row over the buffer (sift.table_wide_rows, on by default; a 3.2 library reads, writes beside and promotes it, but the preflight lists it until promoted) SET sift.table_wide_rows = off (a row over the buffer is refused again)
12 a sift_col index with superunit (16 by default) WITH (superunit = 0), or sift.col_format_minor 11 or less
13 order_columns (option, off) do not set it, or sift.col_format_minor 12 or less
14 a string dictionary of 1,024 strings or more (text, varchar, uuid) stored in blocks to be read in place, where that adds at most 3% to the segment (ids, hashes, uuids, long strings; short repeating strings stay compact), with the string order for a column in key_columns, point_columns or return_columns, any column with dict_lookup = on, or in a sift_table with sift.table_dict_lookup = on; point_columns (option, off) sift.col_format_minor 13 or less
15 key_columns: a key list or bitmap of an int2, int4, int8 or date column (the foreign key and _id columns the default auto picks, or the ones named) WITH (key_columns = 'off'), or sift.col_format_minor 14 or less
16 point_tid_columns (option, off): the point directory lists heap addresses sift.col_format_minor 14 or less (then it lists rows, minor 14)
17 key_cut (option, automatic from 3.4 for an index that benefits, or named): the units are cut by the range of a key and list heap addresses ALTER INDEX x SET (key_cut = 'off') and REINDEX; sift.col_format_minor 14 or less also writes superblock units
18 order_columns (option, off): the sorted runs also hold their heads (the best keys at each end of a unit with their heap addresses) ALTER INDEX x RESET (order_columns) and REINDEX, or sift.col_format_minor 14 or less (the runs are written without heads, minor 13) and REINDEX
19 a leaf NULL in nearly every row of a unit (a soft-delete stamp): the unit also keeps the pre-aggregates of the non-NULL rows, so leaf IS NULL is answered from them REINDEX (a merge writes them too), or sift.col_format_minor 14 or less (written without) and REINDEX
20 hll_columns (option, off): a HyperLogLog sketch of a column in every unit, for sift.approx_count_distinct ALTER INDEX x RESET (hll_columns) and REINDEX (the setting does not apply: the option stamps the minor)
21 order_array_columns (option, auto from 3.5: the build chooses the scattered columns of a table of 100,000 rows or more), order_array_group (option, off): a global order array of the keys of a segment ALTER INDEX x SET (order_array_columns = 'off') (and RESET (order_array_group)) and REINDEX; the setting does not apply: the option stamps the minor
22 key_table (option, on) of an index, sift.table_key_table (setting, on) of a sift_table: the key table of a leaf of key_columns, or of a foreign key or *_id column of a table, in a segment ALTER INDEX x SET (key_table = off) and REINDEX; for a table SET sift.table_key_table = off and VACUUM FULL t; before the older files are installed (a setting below 15 also suppresses key tables)
23 key_columns lists of runs of consecutive keys (on when it pays) ALTER INDEX x SET (key_columns = 'off') and REINDEX, before the older files are installed (a 23 segment makes 3.4 refuse the index)
24 count_columns (option, on for a text leaf read in place): the rows of each string of a text leaf’s dictionary ALTER INDEX x SET (count_columns = 'off') and REINDEX, before the older files are installed (a 24 segment may also hold key tables: key_table = off too, for 3.4)

So WITH (cube_minmax = false, superunit = 0, calendar_columns = 'off') at creation, or sift.col_format_minor = 6 for the server (ALTER SYSTEM, as in “Going back to 3.2”) or the database, writes what 3.2 reads (4 for a schema without JSON paths, 6 with them; 4 for 3.1, 3 for 3.0 with the setting at 4 or 3). An index with wide leaves (column format 5) is written for 3.2 and later at every setting.

A session setting (SET), or one on an application role, is not enough while sift.col_auto_merge is on (the default since 3.3): the background worker that rewrites a sift_col index runs in its own process, as the bootstrap superuser, and follows only the server and database settings, so it would write the default 15 into an index that 3.2 must read. Use the server setting when no 3.3 write may go beyond 3.2, or sift.col_auto_merge = off while the session setting is the one in force.

Going back to 3.2

Run this sequence on the 3.3 database while the 3.3 library still runs (test/t/compat_matrix.sh, “3.2 downgrade”, runs it end to end on a database holding an index of every minor above, a sift_table with calendar cells, one with a 5 MB row and a BM25 index, then starts the 3.2.0 library on it):


# 1. what the target cannot read: one blocker row per object, with the advice
scripts/upgrade-check.sh --to 3.2 app

# 2. every write from now on is readable by 3.2 (superuser; new sessions pick it up)
psql -d postgres -c "ALTER SYSTEM SET sift.col_format_minor = 6" -c "SELECT pg_reload_conf()"

#    with DISABLE (the 3.2 library has not the function of the two event triggers of 3.3, and would fail every ALTER TABLE and CREATE TRIGGER of the database)
psql -d app -c "REINDEX INDEX CONCURRENTLY public.facts_col"
psql -d app -c "VACUUM FULL public.orders"
psql -d app -c "ALTER EVENT TRIGGER sift_table_ddl_check DISABLE" -c "ALTER EVENT TRIGGER sift_table_repack_guard DISABLE"

# 4. nothing blocks any more (exit status 0; the warning that the setting is below the default is the point)
scripts/upgrade-check.sh --to 3.2 app

# 5. stop the server, install the 3.2 files, start it; then check
psql -d app -c "SELECT sift.col_verify('facts_col', true)"       # each sift_col index
psql -d app -c "SELECT sift.verify('docs_idx', true)"            # each sift index
  • Keep sift.col_format_minor = 6 while any 3.3 library still writes: a write at the default after step 4 would put a section 3.2 cannot read back into the database.
  • VACUUM FULL takes an ACCESS EXCLUSIVE lock and rewrites the table in the format of the setting, whatever the row size (tested with a 5 MB row). ALTER TABLE t SET ACCESS METHOD heap and back rewrites it too, but fails with row is too big on a row that does not fit a heap page, so the preflight does not name it.
  • A sift (BM25) index built by 3.7 is at segment format 4 (metapage minor 1.4), which 3.2 refuses with the format error and the hint to REINDEX (no wrong answer; scripts/upgrade-check.sh --to 3.2 lists it as a blocker). A 3.3 to 3.6 index is at minor 1.3, the one 3.1 and 3.2 write (tested: built with 3.3, verified and answered the same by 3.2). There is no setting that makes 3.7 write the older format, so the way back is the REINDEX of the index by the older library after the downgrade (tested: it verifies and answers as before). A word over 255 bytes is stored as a hash since 3.3 and cut at 255 bytes by 3.2 (section 2): REINDEX such an index with 3.2 if a query for the long word must find it.
  • The catalog keeps the extension at 3.3 (below). The access methods, sift.col_verify, sift.verify, the other functions 3.2 has, and writes, promotion, merge and REINDEX by the 3.2 library work on the downgraded database (tested).
  • A standby that stays on 3.2 needs steps 2 and 3 on its primary, and sift.col_format_minor set there for every later write.
  • point_tid_columns (minor 16). 3.2, 3.1 and 3.0 refuse minor 16 segments with a format error and the REINDEX hint (tested; no answer is ever wrong). Their planner falls back to other plans only for an index it can open, so a refused index fails the query until rewritten. The setting of step 2 (6, 4 or 3) writes no point directory, so the REINDEX of step 3 drops the heap addresses. The index keeps the option in pg_class: a 3.2 library ignores it when reading (tested), but a restore into 3.2 reports unrecognized parameter. Roll back: ALTER INDEX x RESET (point_tid_columns) (before the dump, if any), then the REINDEX of step 3. Go forward again: ALTER INDEX x SET (point_tid_columns = 'col') and REINDEX INDEX x with the setting reset (the option does not apply to segments written before it). REINDEX, sift.col_refresh and 3.3 merges keep the option and write minor 16 again (tested).
  • key_cut (minor 17). Same refusal by 3.2, 3.1 and 3.0 (tested; no answer is ever wrong). The setting of step 2 (14 or less, 6, 4 or 3) writes superblock units again, so the REINDEX of step 3 drops the cut; the option stays in pg_class with the same restore message. Roll back: ALTER INDEX x SET (key_cut = 'off') (a RESET lets the next REINDEX decide again) and RESET (key_cut_rows), before the dump, then the REINDEX. Go forward again: ALTER INDEX x SET (key_cut = 'col') and REINDEX INDEX x with the setting reset. REINDEX, sift.col_refresh and merges keep the option and write minor 17 again (tested).
  • hll_columns (minor 20). 3.3 and older refuse minor 20 segments with a format error and the REINDEX hint. sift.upgrade_check('3.3') lists the index as a blocker with ALTER INDEX x RESET (hll_columns) and the REINDEX; sift.approx_count_distinct exists only from 3.4, so a query that calls it fails on an older library whatever the index. It and count(DISTINCT) are different aggregates: no setting turns one into the other.
  • order_array_columns (minor 21). 3.3 and older refuse minor 21 segments with a format error and the REINDEX hint. sift.upgrade_check('3.3') lists the index as a blocker with ALTER INDEX x SET (order_array_columns = 'off') (and RESET (order_array_group)) and the REINDEX; from 3.5 a CREATE INDEX or REINDEX of a table of 100,000 rows or more may choose columns (auto), so a rollback to 3.3 sets it to off before the REINDEX.
  • key_table (minor 22, on by default). A 3.4 library refuses a segment of minor 22 with the format error (index "x" has a segment of column format 1.22, this build reads 1.3 to 1.21) and the REINDEX hint, in a scan, VACUUM, sift.col_verify and sift.col_segments; an INSERT into the table fails with invalid directory update: unknown segment format; nothing crashes (checked with the 3.4.2 library on a database written by 3.5). The error is SQLSTATE 55000 (HINT: Use a newer pg_sift) and comes from opening the index in the planner, so every statement on the table fails, even a plain select count(*), not only the scans of sift; the server stays up. DROP INDEX x makes the table usable again (tested with the 3.4.2 library). A REINDEX on the 3.4 library rebuilds the index without key tables (it does not know the option), so it fixes it too; an index written with key_table = off is read by 3.4.2 without error. sift.upgrade_check('3.4') lists the index as a blocker with ALTER INDEX x SET (key_table = off) and the REINDEX (for a sift_table: SET sift.table_key_table = off and VACUUM FULL t, which rewrites the segments without tables); run both on the 3.5 library, before the files of the older release are installed (an older library does not know the option, but ignores it in pg_class, as it ignores the others in this list). Plan the way back before the update: an index whose segments got key tables cannot be read by 3.4 again (segments too small for one stay at minor 15). The setting does not apply: sift.col_format_minor is 15 at most, and a table is written under 15 only. A segment written under a lower setting has none.
  • count_columns (minor 24, on for a text leaf read in place). A 3.4 library refuses a segment of minor 24 as it does minor 22: the format error (index "x" has a segment of column format 1.24, this build reads 1.3 to 1.21) with the REINDEX hint, in a scan, VACUUM, sift.col_verify and sift.col_segments, and an INSERT fails with invalid directory update: unknown segment format. sift.upgrade_check('3.4') lists the index as a blocker with the advice ALTER INDEX x SET (count_columns = 'off', key_columns = 'off', key_table = off) and REINDEX. An index has such a segment when a text leaf with 1,024 strings or more is read in place (point_columns, point_tid_columns, key_columns, return_columns or dict_lookup = on) and count_columns is not off.
  • The hash of the dictionaries (minor 25, on for a text or uuid leaf read in place). A 3.5 library refuses a segment of minor 25 with the format error (index "x" has a segment of column format 1.25, this build reads 1.3 to 1.24) and the REINDEX hint, as it refuses minor 24 in a 3.4 library; sift.upgrade_check('3.5') lists the index as a blocker with the advice SET sift.col_format_minor = 14, then REINDEX INDEX x. There is no option of its own: every dictionary of 1,024 strings or more written in the lookup form (point_columns, point_tid_columns, key_columns, return_columns or dict_lookup = on) has the hash, so a rollback sets the setting for the REINDEX (it also leaves out the sections of minors 15 to 24) and an update to 3.6 leaves the older segments as they are until a merge or REINDEX writes them again.
  • For 3.1 use 4 and for 3.0 use 3 in step 2, and --to 3.1 or --to 3.0 in steps 1 and 4 (the matrix test checks that those releases read what the settings 4 and 3 write, not the full sequence).

After the downgrade: the catalog, pg_dump and the way forward

  • The catalog stays at 3.3. There is no script from 3.3 to 3.2: ALTER EXTENSION pg_sift UPDATE TO '3.2' fails with extension "pg_sift" has no update path from version "3.3" to version "3.2", and DROP EXTENSION .. CASCADE would drop every index and sift_table that uses it. What 3.3 added to the SQL stays and is unusable with the 3.2 library: its functions (sift.upgrade_commands, sift.stats, sift.explain_why, sift.worker_state, sift.table_space, …) and the two event triggers fail with could not find function "..." in file ".../pg_sift.so". Hence the DISABLE of step 3: enabled, they make every ALTER TABLE and CREATE TRIGGER of the database fail, and the preflight lists them as blockers for a target before 3.3. The tables and indexes, sift.version(), sift.col_verify and sift.verify work (tested).
  • pg_dump and a server that has the 3.2 files only. The dump holds CREATE EXTENSION pg_sift without a version, so the restore creates the extension at the version of the files it finds (3.2); the tables are restored with their rows and the 3.2 library builds every index again in its own format (no setting is needed, no 3.3 or 3.4 section is left). A CREATE INDEX with an option that 3.3 or 3.4 added (cube_minmax, superunit, calendar_columns, dict_lookup, rollup_columns, key_columns, order_columns, point_columns, point_tid_columns, key_cut, key_cut_rows) fails with unrecognized parameter, and the restore goes on without that index. A sift_table with a row too large for a write buffer page (3.3 stores it, 3.2 refuses it with row of sift_table "t" is too large) restores no row at all: its COPY fails as a whole. These are the only errors (tested); a dump without them restores clean, and an index without an option is built at the 3.2 defaults. Reset the option before the dump (ALTER INDEX x RESET (option)) or edit the statement, and keep such a table out of a dump for 3.2.
  • pg_dump of a catalog at 4.2 under an older library. 4.2 added sift.table_option_values, the per-table options of a sift_table, whose key type sift.tblref has its input and output functions in the 4.2 library only: pg_dump fails with could not find function "sift_tblref_out" while it copies that table out (even when it is empty). Leave its data out, pg_dump --exclude-table-data=sift.table_option_values, and set the options again with 4.2 (sift.table_options() lists them before the downgrade); the options are the only thing a downgrade to a release before 4.2 loses.
  • Going forward again. Install the 3.3 files and restart. A database downgraded in place is at 3.3 in the catalog already: ALTER EXTENSION pg_sift UPDATE has nothing to do. Run ALTER EVENT TRIGGER sift_table_ddl_check ENABLE and ALTER EVENT TRIGGER sift_table_repack_guard ENABLE, then ALTER SYSTEM RESET sift.col_format_minor and SELECT pg_reload_conf() so writes carry the 3.3 sections again; what was rewritten at minor 6 stays at 6 until a REINDEX or VACUUM FULL (the preflight without --to and sift.upgrade_commands() list them). A database restored at 3.2 takes the update of section 2. The matrix test runs both.

Every blocker row of the preflight says how to write what the release reads:

Object Advice
sift_col index SET sift.col_format_minor = N (3 for 3.0, 4 for 3.1, 6 for 3.2; 3.3 reads every minor up to 16, 3.4 up to 21, 3.5 up to 24, 3.6 up to 32, 3.7 up to 39 and 4.0 up to 40; the default setting is 15, and minors 16, 17, 18, 20 and 21 are written only for an index with point_tid_columns, key_cut or order_columns, hll_columns or order_array_columns, minor 22 for an index with key_columns leaves unless key_table = off and for a sift_table with a foreign key or *_id column unless sift.table_key_table = off, minor 24 for an index with count_columns, minor 25 for a lookup dictionary of 1,024 strings or more, and 19 only for a leaf NULL in nearly every row) and REINDEX INDEX x; an index with wide leaves (column format 5) is written for 3.2 and later at every setting: drop it for 3.1
sift_table with sift.col_format_minor set, VACUUM FULL t rewrites it in that format (ALTER TABLE t SET ACCESS METHOD heap and back fails with row is too big on a row over the heap page limit)
sift (BM25) index the segment format is the library’s (3.7 writes format 4, which 3.6 and older refuse with the format error): drop the index and create it again with the older release, or keep the newer library

Defaults changed in 3.3

An existing index or table keeps what it holds; the new default applies to units written after the update (promotion, merge, REINDEX), and sift.upgrade_commands() lists the REINDEX that gives older units the new sections. To keep the 3.2 behavior set the value in the last column. sift.table_auto_defrag is read from the library, not the extension script: it is on for every sift_table of the cluster as soon as the 3.3 library runs (after the restart), whatever the extension version, and the preflight of section 1 warns about the off that keeps 3.2, as for any feature switched off.

Setting 3.2 3.3 Measured (200,000 rows, 53 units) To keep the 3.2 behavior
sift_col option cube_minmax off on GROUP BY with min/max of an integer 5.5 -> 0.34 ms; index +0.17% WITH (cube_minmax = false)
sift_col option superunit 0 16 whole-table aggregates 4 to 7 times faster; index +1.0% WITH (superunit = 0)
sift.table_auto_defrag (server setting, with sift.async_maintenance, on in 3.2 too) off on four clients, update/insert/delete mix on 200,000 rows: 7,536 against 5,092 transactions a second, range read p99 16.8 against 34.2 ms; a table that only appends starts no pass sift.table_auto_defrag = off
sift.col_auto_merge (server setting, with sift.async_maintenance, on in 3.2 too) off on 1M rows, 3% of the rows changed per round: the units answerable from their pre-aggregates 32% to 51% (wide rows), 33% to 83% (mixed workload); total WAL +0.9% to +1.9% (full-page images on, a checkpoint per round), +0.0% with them off (the rewrite then waits for the WAL: none in 20 rounds); applies to the existing sift_col indexes as soon as the new library is loaded, and the CREATE INDEX of a new one says so in a NOTICE sift.col_auto_merge = off
sift_col option key_columns none auto: the foreign key and _id columns of type int2, int4, int8 or date that are not unique and not a cube dimension, where their key lists or bitmaps fit the 3% of a unit; CREATE INDEX and REINDEX say which in a NOTICE count(DISTINCT fk) 2.0 to 8.6 times faster on the foreign keys of three 200,000-row tables; index +1.1% to +1.4% WITH (key_columns = 'off'), or sift.col_format_minor = 14
sift_col option calendar_columns none all date, timestamp and timestamptz columns date_trunc / extract group-bys of a date 3.7 to 4.5 times faster; index +1.8% for two columns WITH (calendar_columns = 'off')
sift.table_wide_rows off on a row over the write buffer is accepted (up to 1 GB) instead of refused; no cost for rows that fit SET sift.table_wide_rows = off
sift.table_bulk_groups off on COPY of 200,000 rows: CPU -25%, WAL 44 -> 1 MB SET sift.table_bulk_groups = off

5. First release that reads each format

Format First release
BM25 metapage minor 1.2 (segment formats 1 and 2) 3.0
BM25 metapage minor 1.3 (segment format 3) 3.1
BM25 metapage minor 1.4 (segment format 4, the position stream in blocks) 3.7
column format 3 3.0
column format 4 (tags and kinds, version section of a sift_table) 3.1
column format 5 (wide leaves), 6 (complete JSON kinds) 3.2
column format 7 (cube_minmax), 8 (the calendar cells of a date or timestamp in the cube of a sift_table, or of a sift_col index with calendar_columns), 9 (rollup_columns), 10 (key_columns, a summary of the keys), 11 (the write buffer of a sift_table with a row on tail pages), 12 (superunit), 13 (order_columns), 14 (a string dictionary of 1,024 strings or more, stored in blocks to be read in place), 15 (key_columns, a key list or bitmap) 3.3
column format 16 (point_tid_columns) 3.3
column format 17 (key_cut), 18 (heads of the sorted runs), 19 (the exceptions of a NULL-heavy leaf), 20 (hll_columns), 21 (order_array_columns) 3.4
column format 22 (key_table, the key table of a segment) 3.5
column format 24 (count_columns, the row counts of a text dictionary) 3.5
column format 25 to 32 (the hash of the lookup dictionaries, the containment lanes of range, inet and cidr columns, the text signatures, the finer global order of order_array_columns for dates and text) 3.6
column format 33 to 39 (the point directories at ceil(keys * 4 / 3) slots, the chunk table before the directory, the rollup lengths, the directory records at the end of the dictionary section, the packed order of a dictionary searched in place) 3.7; 3.6 and older refuse them
column format 40 4.0; 3.7 and older refuse the segments; a list in order_array_columns keeps complete arrays
column format 41 (a compact chunk in an order array; order_layout auto, the default, writes it for spread keys such as a timestamp) 4.0; 3.7 and older refuse the segments; order_layout = 'whole' and a REINDEX, or sift.col_format_minor 14, write what they read
column format 42 (the order array of a text column names its strings by dictionary code) 4.0; 3.7 and older refuse the segments; sift.col_format_minor 14 and a REINDEX write what they read
column format 44 (the key journal of a connected parent index and the found flags of a connected child, a write buffer of references to heap tuples, seekable and positional order chunks and the packed table of groups of the second order) 4.3; 4.2 and older refuse the segments; sift.col_format_minor 14 and a REINDEX write what they read, with connect = 'off' and sift.col_wbuf_ref = off for the connection and the references

scripts/upgrade_check.sql holds this table; a release that adds a format adds its row there.

Schema sift is open to PUBLIC since 3.3

Up to 3.2 a role needed GRANT USAGE ON SCHEMA sift to call any sift.* function or read a sift.* view (permission denied for schema sift). The install and ALTER EXTENSION pg_sift UPDATE to 3.3 grant it to PUBLIC: existing grants stay and are no longer needed, and nothing else is granted (the functions check the table or index they read; sift.table_paths is not readable). A database that does not want it runs REVOKE USAGE ON SCHEMA sift FROM PUBLIC after the update; the next update grants it again. PostgreSQL creates the schema named in the control file but does not make it a member of the extension (ALTER EXTENSION pg_sift ADD SCHEMA sift is refused), so DROP EXTENSION leaves it empty, as in 3.2, with the privileges it had; CREATE EXTENSION then gives the objects and privileges of a fresh install again, and DROP SCHEMA sift removes it.

Known issue of 3.2.0, 3.2.1 and 3.2.2: a role cannot drop anything

In 3.2.0, 3.2.1 and 3.2.2 the event trigger function sift.table_paths_drop() is not SECURITY DEFINER, so a DROP by a role that is neither a superuser nor the extension owner fails with permission denied for schema sift (any table, index or schema, also one without pg_sift). ALTER EXTENSION pg_sift UPDATE to 3.3 fixes it. Until then, run once in each database with the extension, as a superuser (and again after a restore from a dump, which creates the extension from the 3.2 script):

ALTER FUNCTION sift.table_paths_drop() SECURITY DEFINER SET search_path = pg_catalog, pg_temp;

Known issue of 4.0.0 to 4.1.0: aggregates could count old versions of rows

Fixed in 4.1.1 (install the files and restart the server; nothing to update in the database). In 4.0.0, 4.0.1, 4.0.2, 4.0.3 and 4.1.0 an aggregate over a sift_col index with track_changes (the default for a table with a primary key) could count the old versions of rows that a transaction had updated more than once: the first statement of a new session that needed a table the backends share (the dictionary of a text or varchar key, the statistics of the index, the choices of the planner) was a write (UPDATE, INSERT ... ON CONFLICT DO UPDATE, DELETE, COPY), the shared memory was attached inside a subtransaction, and the command id advanced under that statement. Reproduced on 4.0.0, 4.0.2, 4.0.3 and 4.1.0 (4.0.1 shares the code) with a text key; the cases with an integer key did not show it. Before 4.0.0 a read checks the heap, so 3.x does not show it.

A second form: an UPDATE, MERGE, INSERT ... ON CONFLICT DO UPDATE or a cursor UPDATE ... WHERE CURRENT OF that calls a function that runs SQL inside the statement (a plpgsql function with PERFORM or EXECUTE, a trigger that updates the table) advanced the command id inside the statement, and the entry of each version the statement ended was not removed after the first call, so the rows were counted twice. A third: a bulk UPDATE of a table that also has a sift (BM25) index, which seals its segments in a subtransaction of the statement. Confirmed on 4.1.0 and expected on every release since 4.0.0.

If you cannot update yet: ALTER INDEX <index> SET (track_changes = 'off') on each sift_col index of a table with a primary key or another tracked key. It takes a ShareUpdateExclusiveLock, rewrites nothing, needs no REINDEX (35 ms on 600,000 rows, and about 0.1 s for the SET (track_changes = 'id') that turns it back on), avoids all three routes, and repairs an index that was already wrong because every row is then checked against the heap; REINDEX alone repairs it only until the next such statement. The price: on a table that has churned and was not vacuumed, the statements after an update check more rows in the heap (the instructions of the next statement rose by 27% to 53% in the cases measured), nothing after a VACUUM; writes get cheaper; the first statement of a session is the same. After the update, ALTER INDEX <index> SET (track_changes = 'id') turns the tracking back on. For the first route only, ALTER SYSTEM SET sift.col_dict_shared_cache_kb = 0 (with sift.col_leaf_cache_kb = 0 and sift.verdict_cache_kb = 0), then SELECT pg_reload_conf() (or the same in postgresql.conf, or ALTER DATABASE .. SET; superuser settings, no restart) also avoids it: each backend then parses its own dictionaries and keeps its own statistics (what docs/OPTIONS.md gives for these settings). Putting pg_sift in shared_preload_libraries does not avoid it. See also Reading without the heap (track_changes).

The wrong counts that 4.0.0 to 4.1.0 left in an index, and 4.1.2

Installing 4.1.1 stopped new wrong counts; it did not take away the old versions that an earlier release had left in the log of an index, so such an index went on counting them after the update (ALTER EXTENSION UPDATE changes nothing there; reproduced from 4.0.3 and 4.1.0 to 4.1.1 and 4.2 with the three routes above), until a REINDEX, ALTER INDEX .. SET (track_changes = 'off') and back, or a VACUUM that vacuumed the indexes (PostgreSQL skips that when fewer than 2% of the pages hold a dead row: 30 stale entries of a 300,000-row table stayed through a plain VACUUM). 4.1.2 repairs them by itself, with the files installed and the server restarted and no other step:

  • The log of a sift_col index with track_changes that an earlier release wrote (flags 1 in its header) is not taken for complete any more: its reads check the heap, so their answers are the heap’s at once (sift.col_tracking shows complete true and heap_free false). The cost is that of an index with track_changes = 'off' on a table that has churned and was not vacuumed (600,000 rows, 30,000 each updated, deleted and inserted, the second statement of a session, millions of instructions, heap-free against checked: one entity 1.5 against 2.3, a set of 75 entities 5.6 against 7.2, the full group 57.6 against 73.4), nothing on a vacuumed table.
  • It is re-baselined by the first of: the next VACUUM of the table (also one that skips the indexes), the first read that checks the heap for it (the maintenance worker, sift.col_auto_merge on, so a table nobody writes or vacuums is repaired too), and the maintenance worker that a write of the table asks for. The step writes a tombstone for every heap address that no version is live for, marks the log (flags 3), and the heap-free reads resume. 600,000 rows, 30,000 of them updated, deleted and inserted since the last VACUUM: 111 to 119 ms and 7 to 14 MB of WAL; after a VACUUM 71 ms and 3 kB. It holds a ShareUpdateExclusiveLock on the table (writers go on, a VACUUM waits or skips). The re-baseline of an index that was fine changes nothing in its answers.
  • A standby cannot write the log: its reads check the heap until the re-baseline of the primary reaches it by replication.
  • An earlier library that reads a log with flags 3 takes it for incomplete and checks the heap, which is right.
  • An index created, reindexed or rebuilt by ALTER INDEX .. SET (track_changes = ..) by 4.1.2 or later is marked at once.
  • After pg_upgrade, a dump restore with triggers disabled, or REINDEX CONCURRENTLY, nothing is repaired by itself. The restore recreates the triggers with a new xmin, so the witnesses of the log no longer match: every tracked sift_col index has flags 1, triggers false and heap_free false (damaged or not; since 4.0.0), the worker, VACUUM and writes change nothing, and the answers are right, taken from the heap. Run REINDEX INDEX <index> or ALTER INDEX <index> SET (track_changes = '<value>') (the value the index had: its key column, 'auto' or 'id') once for every tracked sift_col index, one by one; the indexes that are still to do:
SELECT c.oid::regclass AS index FROM pg_class c JOIN pg_am a ON a.oid = c.relam, LATERAL sift.col_tracking(c.oid) t WHERE a.amname = 'sift_col' AND t.track_changes AND NOT t.heap_free;

From 4.2 on the maintenance worker, VACUUM or a read that has to check the heap repairs it by itself (see the pg_upgrade section above): nothing to run.

What is tested

What Test
the preflight on a clean database, with --to, with options, settings and test objects left behind, on an older extension and on an index the library refuses test/t/upgrade_check.sh
pg_upgrade --check and the upgrade of sift, sift_col and sift_table objects (tablespace, wide leaf types, JSON paths, open write buffers), answers, sift.verify, sift.col_verify, writes and merges afterwards test/t/pg_upgrade.sh
a same-major pg_upgrade of the BM25 indexes, ALTER EXTENSION UPDATE along every release test/t/extension_lifecycle.sh
the previous releases' libraries write, this tree reads (and the other way) test/t/old_lib_refusal.sh, test/t/compat_31_column.sh, and scripts/release.sh