Changing a Column on a Live Database

Problem: I want to add/drop/change a column or index on a live db Solution: MySQL Postgres Write or use a custom program that creates a ghost table that is like the original table, adds and drops the appropriate columns, and then starts filling the new ghost table by copying data from the original table over time, all the while watching for inserts, updates, and deletes on the original table by intercepting the binary row based replication log and asynchronously applying those changes to the ghost table, and finally once everything appears to be in sync between the original table and the ghost table, performs a cut-over so the ghost table takes the place of the original table. original table ghost table master replica binary log gh-ost migration: - creates ghost table on migrated server - alters ghost table - hooks up a MySQL replica, streams binary log events - interchangeably: - applies events on ghost table - copies rows from original table onto ghost table - cut-over Preferred setup: - connects to replica - inspects table structure, table dimensions on replica - hooks as replica onto replica - apply all changes on master - writes internal heartbeat events onto master, expects them on replica BEGIN; ALTER TABLE foo ADD COLUMN bar bool; ALTER TABLE foo DROP COLUMN baz; COMMIT;

This technical meme contrasts MySQL’s elaborate gh-ost migration process with the simple PostgreSQL commands for changing a live table. The joke is that a seemingly basic schema change in MySQL requires a ghost table, replication, binary logs, synchronization, and a cut-over, while Postgres appears to solve it with a few straightforward SQL statements. It is shared by database developers reacting to the complexity or operational risk of live schema migrations. EN: This technical meme contrasts MySQL’s elaborate gh-ost migration process with the simple PostgreSQL commands for changing a live table. The joke is that a seemingly basic schema change in MySQL requires a ghost table, replication, binary logs, synchronization, and a cut-over, while Postgres appears to solve it with a few straightforward SQL statements. It is shared by database developers reacting to the complexity or operational risk of live schema migrations.