Reindexing BRIN Indexes and Extension Security in PostgreSQL 13.12
PostgreSQL 13.12, released on August 10, 2023, fixed a bug that caused some Block Range Indexes (BRIN indexes) to miss rows with…
In PostgreSQL, a partitioned table stores its rows in separate tables called partitions. A partitioned index is a parent index that represents the indexes on those partitions, while the child indexes store the actual index data. PostgreSQL 14.9 fixed bugs that let an invalid child index count as a match, caused PostgreSQL to mark the parent index valid too early, or made an update to the parent index's validity status fail.
In this context, PostgreSQL validates a partitioned index by keeping each index's status accurate in the system catalog, the tables that store database metadata. Release 14.9 did not add a command that scans every row to verify index contents. Before you rely on a parent index's status, check that each partition has a matching index marked valid.
PostgreSQL 14.9 fixed three related bugs in how it matches partition indexes and records their status, as described in the 14.9 release notes.
When PostgreSQL created a partitioned index, it could mistake an existing but invalid index on a partition for a usable match. It could then mark the parent index valid prematurely. The release notes warn that later queries against the partitioned table could misbehave or trigger assertion failures.
When you attached a table as a partition, PostgreSQL could also match an invalid index on that table to the parent index. In 14.9, PostgreSQL skips that invalid index and creates a new child index.
PostgreSQL also fixed a possible failure when it updated the parent index's validity entry in the system catalog. The release notes identify an "attempted to update invisible tuple" error as one possible symptom.
The release notes limit these fixes to index matching and status updates. They do not report how often the bugs occurred or claim any performance change.
PostgreSQL stores the indisvalid flag in pg_index to show whether an index is safe for query use. An invalid index might be incomplete, so PostgreSQL ignores it during queries, though PostgreSQL still has to maintain it during inserts and updates. The pg_index catalog documentation describes this status.
The parent index does not hold the rows' index entries. PostgreSQL creates or attaches an index for each partition, and the parent represents their relationship. When every required partition has a matching index attached, PostgreSQL can mark the parent valid. The partitioning documentation describes this structure.
This index status is separate from checking a table constraint. A CHECK constraint limits which row values are allowed, and a foreign-key constraint requires referenced rows to exist. ALTER TABLE ... VALIDATE CONSTRAINT checks existing rows against a constraint that was previously declared NOT VALID. The 14.9 fixes concern whether PostgreSQL matches child indexes correctly and marks the parent valid.
On a large partitioned table, you can create the parent index without recursively building every child index, then build each child index concurrently and attach it. When you run CREATE INDEX CONCURRENTLY, PostgreSQL lets writes continue while it builds the index. See the CREATE INDEX documentation for its behavior and restrictions.
-- Create the parent index without building indexes on every partition.
CREATE INDEX parent_idx ON ONLY parent_table (indexed_column);
-- Build an index on a leaf partition while allowing writes to continue.
CREATE INDEX CONCURRENTLY child_1_idx
ON child_1 (indexed_column);
-- Attach the child index to the parent index.
ALTER INDEX parent_idx
ATTACH PARTITION child_1_idx;
Build and attach a child index for each of the other partitions. Each child index must match the parent index's definition, and the parent stays invalid until you attach all required child indexes. PostgreSQL does not let you run CREATE INDEX CONCURRENTLY on the partitioned table itself, so you build the child indexes concurrently to reduce write blocking, as described in the partitioning documentation.
When you attach a table to a partitioned table with ALTER TABLE ... ATTACH PARTITION, PostgreSQL handles existing indexes differently than when you attach an index directly. PostgreSQL can reuse a valid equivalent index already on that table. Under the 14.9 behavior, PostgreSQL skips an invalid index and creates a new child index for the parent index, as described in the ALTER TABLE documentation.
If an index has an unexpected validity status, inspect its pg_index entry and the entries for the indexes on each partition. Confirm that every partition has a matching child index and that each child's indisvalid flag is true. When you diagnose this issue, check the child indexes even if the parent flag is valid.
PostgreSQL ignores an invalid child index during queries, but it still has to maintain that index during writes. When you attach a table that has an invalid index, PostgreSQL 14.9 creates a new child index instead of treating the invalid one as a valid match.
According to the release notes, you do not need to dump and restore when you upgrade from another PostgreSQL 14.x release to 14.9. The notes also include a separate BRIN reindex note, which applies to BRIN indexes rather than these partitioned-index bugs.
Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.
Take a look at vroni.com