[j-cheong]

Changing a PostgreSQL column type without following the author's instructions and just running the following command is VERY anti-pattern. Confused why people do this in the first place.

ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type

>you need to make sure the source system has enough disk space to hold the WAL files for a long enough time

if the asynchronous replication process has an external buffer instead of the WAL, then it addresses this issue

[chatmasta]

> Confused why people do this in the first place

Probably because every tutorial on the Internet, along with the docs, recommends doing it this way. All the gotchas are buried in the footnotes.

[yen223]

The unsafe ALTER COLUMN is one step.

The safe option is four steps minimum.

It's not hard to see why people would be tempted by the unsafe option.

[mattashii]

> Confused why people do this in the first place.

Because you lose a significant amount of performance if you start adding NULL and variable-length columns just because you're afraid of a table rewrite.

Because the resulting table will not have had 1 table of update-induced bloat at the end of the operation.

Because you can be sure the modification is applied atomically and you as the user can be sure the migration from A to B goes through as expected or has a graceful rollback to the old data, rather than getting stuck or failures halfway through the migration.

Because toasted data from DROP-ed columns is not removed from storage with the DROP COLUMN statement, but only after the row that refers to that toasted value is updated or deleted.

...

Every column you "DROP" remains in the catalogs to make sure old tuples' data can be read from disk. That's overhead you now will have to carry around until the table is dropped. I'm not someone who likes having to carry that bloat around.

[dgan]

Not everyone has billion rows in their tables ¯\_(ツ)_/¯