table and index rewrites when changing type in postgresql

PostgreSQL needs to rewrite the table during an ALTER TABLE ... ALTER COLUMN ... TYPE ... if the internal binary format of the new data type is different from that of the old one, that is, if they look different on disk. For example, changing from integer to bigint will rewrite the table, because integer is four bytes in size, while bigint is eight bytes.

The most important data types with equivalent internal format are varchar(n) and text: changing between these data types will only rewrite the table if the new length limit is smaller than the old one.

If PostgreSQL rewrites a table, it will also rewrite all its indexes. However, it may be necessary to rewrite an index, even if there is no need to rewrite the table. This is for example necessary if the collation of the column changes:

CREATE TABLE test (col text COLLATE "und-x-icu");
CREATE INDEX testidx ON test (col);
/* will rewrite the index, but not the table */
ALTER TABLE test ALTER col TYPE text COLLATE "C";

SELECT relfilenode FROM pgclass
WHERE relname = 'table or index name'
AND relnamespace = 'schema name'::regnamespace;

Then run the ALTER TABLE. Afterwards, query pg_class again and see if the relfilenode has changed. If yes, ALTER TABLE rewrote the objects.