patternsqlMinor
Replication and table locks during alter table
Viewed 0 times
duringreplicationandalterlockstable
Problem
Recently we have noticed that a number of our Rails migrations end up deadlocking / freezing our app+DB in production. Preliminary investigation reveals that this is probably due to concurrent access by the app and the migration on a table with very high reads.
Would it make sense to explore a replicated PG setup (master-slave perhaps), where all writes and migrations are executed against the master, and all high volume reads are executed against the slave?
How does PG behave when an ALTER TABLE statement is replicated to a slave? Does the slave also acquire the same table locks? Will replication solve the problems that we are currently facing?
Would it make sense to explore a replicated PG setup (master-slave perhaps), where all writes and migrations are executed against the master, and all high volume reads are executed against the slave?
How does PG behave when an ALTER TABLE statement is replicated to a slave? Does the slave also acquire the same table locks? Will replication solve the problems that we are currently facing?
Solution
Would it make sense to explore a replicated PG setup (master-slave perhaps), where all writes and migrations are executed against the master, and all high volume reads are executed against the slave?
Yes, that's one option. But if you write your migrations more carefully (Rails's defaults are pretty simplistic) you can greatly reduce the locking that's required on the master anyway.
For example, instead of
you can write
The latter will not hold a strong lock over the table rewrite, and will be much less disruptive. It does more work overall, but with lower lock levels.
If you take more care with your migration writing and testing, you'll find that this issue mostly goes away. I've helped with near-zero downtime schema changes on multi-terabyte databases and the same principles apply there.
How does PG behave when an ALTER TABLE statement is replicated to a slave? Does the slave also acquire the same table locks?
Yes. You can always switch reads to the master while the replia applies a big schema update that's already committed on the master, though.
Yes, that's one option. But if you write your migrations more carefully (Rails's defaults are pretty simplistic) you can greatly reduce the locking that's required on the master anyway.
For example, instead of
ALTER TABLE tblah ADD COLUMN cblah DEFAULT dblah NOT NULLyou can write
ALTER TABLE tblah ADD COLUMN cblah;
ALTER TABLE tblah ALTER COLUMN cblah DEFAULT dblah;
UPDATE tblah SET cblah = dblah WHERE cblah IS NULL;
ALTER TABLE tblah ALTER COLUM cblah NOT NULL;The latter will not hold a strong lock over the table rewrite, and will be much less disruptive. It does more work overall, but with lower lock levels.
If you take more care with your migration writing and testing, you'll find that this issue mostly goes away. I've helped with near-zero downtime schema changes on multi-terabyte databases and the same principles apply there.
How does PG behave when an ALTER TABLE statement is replicated to a slave? Does the slave also acquire the same table locks?
Yes. You can always switch reads to the master while the replia applies a big schema update that's already committed on the master, though.
Code Snippets
ALTER TABLE tblah ADD COLUMN cblah DEFAULT dblah NOT NULLALTER TABLE tblah ADD COLUMN cblah;
ALTER TABLE tblah ALTER COLUMN cblah DEFAULT dblah;
UPDATE tblah SET cblah = dblah WHERE cblah IS NULL;
ALTER TABLE tblah ALTER COLUM cblah NOT NULL;Context
StackExchange Database Administrators Q#184863, answer score: 6
Revisions (0)
No revisions yet.