3

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?

1 Answer 1

6

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

ALTER TABLE tblah ADD COLUMN cblah DEFAULT dblah NOT NULL

you 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.

1
  • +1 for another way to write ALTER TABLE. Very useful. Sep 1, 2017 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.