I’m unfamiliar with ctid (which I now plan to read up on)… I’ve been looking at relfilenode
I’ve observed that relfilenode changes when altering from old_type à varchar(9) and the operation takes 6 seconds on this data set.
PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20669469
(1 row)
PSQL:alpha_db/postgres@[local]=# alter table test alter COLUMN exec_id set data type varchar(9);
ALTER TABLE
Time: 6605.454 ms
PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20671802
(1 row)
And then the other way… from varchar(9) à old_type
refilenode does not change, and the operation takes 0.3ms
PSQL:alpha_db/postgres@[local]=# alter table test alter COLUMN exec_id set data type execid_t;
ALTER TABLE
Time: 1.360 ms
PSQL:alpha_db/postgres@[local]=# select relfilenode from pg_class where relname='test';
relfilenode
-------------
20671802
(1 row)
Time: 0.331 ms
Apologies if this formats badly :-/ transcribing between devices not well suited to email.
Tim