4 ms·

> A good example is the recent release of PostgreSQL version 14.4. It is a minor upgrade, so no new features. But it requires extensive work on B-tree indexes.

> A good example is the recent release of PostgreSQL version 14.4. It is a minor upgrade, so no new features. But it requires extensive work on B-tree indexes. I find it wrong to call out B-tree indexes -- any index that is (re)indexed concurrently in PG14 <14.4 might have had corruption. Just because PG14 only has a way to detect it for b-trees does not mean it doesn't exist in other index methods. The author also seems to confuse upgrade complexity with the complexity of verification and decorruption tasks (that you should do if you have run concurrent (re)index in your cluster in PG 14.0-14.3). The thing is that you are not at all required to do that during the upgrade, because you might not run (re)index concurrently, or because fixing the corruption is also possible when still on <14.4, by using non-concurrent reindex, or by doing the manual snapshot control that I detailed in [1]. [1] https://news.ycombinator.com/item?id=31688332 https://news.ycombinator.com/item?id=31688332

smileysteve 4y ago
The concurrent reindex operation only impacted breed indexes. There's no known impact to brin or gin/gist indexes as these haven't seen major changes in recent versions.
- mattashii 4y ago
  The corruption issue is with concurrent (re)index operations, which can be done for all index types. That is, the corruption is best described as 'some table rows may skipped when scanning the table during reindex, so the index can be incomplete', and unrelated to index methods, as any index can be reindexed concurrently. btree is only the default index type, but otherwise unrelated, with the sole exception being that it is easy to verify whether it's corrupted - but that doesn't mean that other index methods are not affected. Absence of evidence is not evidence of absence.
  - smileysteve 4y ago
    Brin was implemented in a lazy fashion already. So concurrent reindexing isn't a thing
    - mattashii 4y ago
      BRIN lazily summarizes blocks _at the end of the table_. When you build the index (by either creating or reindexing, concurrently or not) it will generate summaries for all full block ranges of the table. As such, it is possible that even a BRIN index was corrupted by concurrent reindex in PG14 < 14.4 because it didn't receive all tuples that it should have summarized.

smileysteve 4y ago

The concurrent reindex operation only impacted breed indexes. There's no known impact to brin or gin/gist indexes as these haven't seen major changes in recent versions.

mattashii 4y ago
The corruption issue is with concurrent (re)index operations, which can be done for all index types. That is, the corruption is best described as 'some table rows may skipped when scanning the table during reindex, so the index can be incomplete', and unrelated to index methods, as any index can be reindexed concurrently. btree is only the default index type, but otherwise unrelated, with the sole exception being that it is easy to verify whether it's corrupted - but that doesn't mean that other index methods are not affected. Absence of evidence is not evidence of absence.
- smileysteve 4y ago
  Brin was implemented in a lazy fashion already. So concurrent reindexing isn't a thing
  - mattashii 4y ago
    BRIN lazily summarizes blocks _at the end of the table_. When you build the index (by either creating or reindexing, concurrently or not) it will generate summaries for all full block ranges of the table. As such, it is possible that even a BRIN index was corrupted by concurrent reindex in PG14 < 14.4 because it didn't receive all tuples that it should have summarized.