Hi,
I'm trying to understand whether the following behavior is expected in
PostgreSQL.
Environment
- PostgreSQL 15.17
- Declarative partitioning
- B-tree indexes
According to the documentation, increasing the length of a varchar column
does not require a table rewrite.
PostgreSQL: Documentation: 15: ALTER TABLE
However, I observed different behaviors depending on whether the parent
partitioned table has an index referencing the altered column.
========================================================================
Case 1 - No parent index
========================================================================
CREATE TABLE orders
(
id bigint,
order_date date,
data_field varchar(30),
PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_p202510
PARTITION OF orders
FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');
CREATE INDEX orders_p202510_data_field_idx
ON orders_p202510(data_field);
ALTER TABLE orders
ALTER COLUMN data_field TYPE varchar(50);
Observation:
- No heap rewrite occurs.
- pg_stat_progress_cluster remains empty.
- pg_stat_progress_create_index remains empty.
- relfilenode of orders_p202510_data_field_idx remains unchanged.
========================================================================
Case 2 - Parent partitioned index exists
========================================================================
CREATE TABLE orders
(
id bigint,
order_date date,
data_field varchar(30),
PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_p202510
PARTITION OF orders
FOR VALUES FROM ('2025-10-01') TO ('2025-11-01');
CREATE INDEX idx_orders_data_field
ON orders(data_field);
ALTER TABLE orders
ALTER COLUMN data_field TYPE varchar(50);
Observation:
- No heap rewrite occurs.
- pg_stat_progress_cluster remains empty.
- pg_stat_progress_create_index reports:
command = CREATE INDEX
phase = building index: scanning table
- relfilenode of the child index changes.
- ALTER TABLE execution time increases proportionally with the child index
size.
========================================================================
Additional observations
========================================================================
I also tested other parent index definitions.
The behavior is the same whether data_field is:
CREATE INDEX idx1 ON orders(data_field);
or
CREATE INDEX idx2 ON orders(id) INCLUDE (data_field);
In both cases, child indexes are rebuilt.
On the other hand, if the parent table has no index referencing data_field,
child indexes are left untouched.
>From my testing, the deciding factor appears to be simply the existence of
a parent index that depends on the altered column.
========================================================================
Questions
========================================================================
1. Is this expected behavior?
2. If so, what is the rationale for rebuilding child indexes even though
the heap itself is not rewritten?
3. Is rebuilding triggered simply because a parent partitioned index
depends on the altered column, or is there another internal reason for
doing so?
I searched the documentation and mailing list archives but couldn't find
any discussion describing this specific behavior.
Any clarification would be greatly appreciated.
Thank you.
