How to Add a New Column Without Downtime in PostgreSQL and MySQL

Adding a new column can look simple on paper. In practice, the wrong approach can lock tables, drop queries, or push latency into the red. Whether you’re working with PostgreSQL, MySQL, or another relational database, the method you choose will determine uptime and speed.

In PostgreSQL, ALTER TABLE ADD COLUMN is straightforward for columns without default values. The command is metadata-only and completes instantly. But a non-null column with a default will scan and rewrite the entire table, which can cripple performance for large datasets. One solution is to add the column as nullable, backfill in small batches, then enforce constraints.

In MySQL, adding a new column often requires a full table copy unless you use features like ALGORITHM=INPLACE or tools like pt-online-schema-change. For large, hot tables, online schema changes are essential to avoid downtime. Always measure the lock times and replication lag during tests before applying changes to production.

Key steps when adding a new column:

• Use metadata-only changes when possible.
• Avoid defaults that trigger table rewrites.
• Batch backfills to distribute load.
• Test changes on a replica with realistic traffic.
• Monitor replication delay, locks, and slow queries.

Schema migrations feel small until a badly planned one breaks production. Get them right, and you can evolve your data model without interruption.

See how you can run safe, zero-downtime schema changes — including adding a new column — with Hoop. Try it live at hoop.dev in minutes.