How do you perform zero-downtime schema migrations on a large production table?
How do you perform zero-downtime schema migrations on a large production table?
The goal is to change the schema while the application keeps running, without long locks or incompatible code. The principle is expand, migrate, contract: every step is backward compatible with the code currently deployed.
-- 1. cap how long DDL may wait for a lock: waiting blocks every query queued behind it
SET lock_timeout = '3s';
-- 2. adding a nullable column is metadata-only; a constant default is also fast on PostgreSQL 11+
ALTER TABLE orders ADD COLUMN channel VARCHAR(20);
-- 3. build indexes without blocking writes
CREATE INDEX CONCURRENTLY idx_orders_channel ON orders (channel);
-- 4. add a constraint without a long scan under lock
ALTER TABLE orders ADD CONSTRAINT chk_channel CHECK (channel IS NOT NULL) NOT VALID;
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.