Adding a new column should begin with the schema. Examine how the relational model will evolve. Decide whether the column is nullable, whether it needs a default value, and how it affects primary keys or unique constraints. In PostgreSQL, ALTER TABLE ADD COLUMN is the simplest path, but simplicity hides complexity. Locking behavior, concurrent queries, and replication lag must all be considered.
Type selection is critical. Choose the smallest data type that fits the need. For timestamps, use timestamptz to keep time zone drift from corrupting data. For strings, set a bound when possible. Over-large types waste memory and reduce cache efficiency.
Migration strategy matters. In production, large tables can’t afford blocking writes for minutes or hours. Use tools like pg_repack or perform batched backfills to populate values without halting traffic. In MySQL or MariaDB, consider online schema change tools to avoid locking.