For ” How to Safely Change a PostgreSQL Column Type” (new column, trigger, etc). Is there a particular reason PostgreSQL cannot set up column migration itself out-of-the-box? I have used PSQL for many years and it is always a bit cumbersome to do the column migrations manually, even though the process itself is quite mechanical.
> Is there a particular reason PostgreSQL cannot set up column migration itself out-of-the-box?
People haven't asked hard enough to the right people, I suppose. PostgreSQL is an open-source project, where wanted and needed features are supplied by willing individuals and companies, and vetted by the community so that the code base remains in good quality.
I just suppose no-one has bothered yet with implementing ALTER TABLE CONCURRENTLY to the point that it's been accepted by the community, though another reason might be because the induced metadata churn (only 1600 distinct user-defined columns available in each table at most) might become problematic with ADD COLUMN ... DROP COLUMN.
So Postgresql and MySQL can both do full table copies as needed. And their locking has gotten better. They also can do more changes in place. Yet still too often they'll need exclusive locks which blocks even reads.
For very big tables it's often better to manually add a column, backfill then drop-swap.
