Warning gap for index rewrites during ALTER TABLE .. ALTER COLUMN TYPE

Description

Currently, YugabyteDB warns users to avoid concurrent data update operations (DML) when a table modification (DDL) has caused a full table rewrite, because of the risk of inconsistent behavior. This warning exists because, without object locks (also known as Table-level locks), the database cannot prevent concurrent writes from being missed in the objects being rebuilt.

This advisory describes a gap in that warning coverage. In certain ALTER TABLE .. ALTER COLUMN TYPE scenarios, secondary indexes are rewritten even when the main table is not, and because the warning logic was tied to table rewrites, no notice was raised. If you ran concurrent DML during these operations, you would not have been alerted to the risk. This gap has been corrected in YugabyteDB v2025.2.3.0, v2025.1.4.0, and v2024.2.9.0.

Fix

The fix addresses the warning gap in two complementary ways. When an index rewrite occurs without a table rewrite, a NOTICE is now raised so you can avoid performing DML until the ALTER TABLE is complete. This matches the existing behavior for table rewrites. Additionally, the number of scenarios where an index rewrite occurs in the absence of a table rewrite has been reduced.

The long-term solution to address this class of issues is the EA Table-level locks feature (available as an Early Access feature in v2025.2.0.0 and later).

Currently, table-level locks are disabled by default; see Enable table-level locks. General availability is planned for a future release.

Mitigation

The standing guidance applies: avoid concurrent DML during any ALTER TABLE .. ALTER COLUMN TYPE operation, particularly when secondary indexes exist on the affected column. When in doubt, avoid concurrent DML during any ALTER TABLE operation.

If you believe concurrent DML may have been executed during one of the affected scenarios before upgrading to a fixed release, use the YSQL index consistency checker (yb_index_check()) to verify that your secondary indexes are consistent with the base table.

Details

Certain variants of ALTER TABLE require a rewrite of the table and associated indexes in both PostgreSQL and YugabyteDB. In PostgreSQL, these commands block concurrent DML through table locks, which prevents inconsistency.

Currently, as YugabyteDB does not enforce table-level locks by default, the database raises a notice and relies on you to avoid concurrent DML while the operation is in progress.

A warning similar to the following is raised to give you notice of this issue:

NOTICE:  table rewrite may lead to inconsistencies
DETAIL:  Concurrent DMLs may not be reflected in the new table.
HINT:  See https://github.com/yugabyte/yugabyte-db/issues/19860. Set 'ysql_suppress_unsafe_alter_notice' yb-tserver gflag to true to suppress this notice.

What was not accounted for in the original warning logic is that index rewrites are not always coupled to table rewrites. Some scenarios can still cause an index rewrite even when the base table does not require one, including partial or expression indexes (for example, CREATE INDEX tbl_col ON tbl(lower(col))) and collation changes. This behavior is expected in both YugabyteDB and PostgreSQL. Because the warning was only triggered by table rewrites, these index-only rewrite cases went unwarned. The fix ensures the warning is raised whenever a rewrite of any kind (table or index) is triggered by an ALTER TABLE operation.

Furthermore, there were scenarios where YugabyteDB rewrote secondary indexes when PostgreSQL would not. Specifically, ALTER TABLE .. ALTER COLUMN TYPE was rewriting secondary indexes that included the altered column in all cases, even when the base table did not require a rewrite. That behavior has also been corrected.

Related articles

• ALTER TABLE
• YSQL index consistency checker