Good post, you can tell the author has some burn marks from seeing some sparks in production.
> Let's start with The Bad News - you cannot avoid rewriting the entire table, which will generate a significant amount of WAL files in the process. This is a given, and you must plan how to manage it.
There are some exceptions to this. The blog post focuses on changing a column from int to bigint, which indeed needs a full table rewrite to reallocate 8 bytes per ID value instead of 4. But:
> As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed. [0]
So, you could change from varchar to text, for example, or change from varchar(20) to varchar(4), etc. and not incur a full table rewrite.
> Let's start with The Bad News - you cannot avoid rewriting the entire table, which will generate a significant amount of WAL files in the process. This is a given, and you must plan how to manage it.
There are some exceptions to this. The blog post focuses on changing a column from int to bigint, which indeed needs a full table rewrite to reallocate 8 bytes per ID value instead of 4. But:
> As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed. [0]
So, you could change from varchar to text, for example, or change from varchar(20) to varchar(4), etc. and not incur a full table rewrite.
0: https://www.postgresql.org/docs/current/sql-altertable.html