PostgreSQL Replication – Handling Table Locks During ALTER TABLE

ddlpostgresqlreplication

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?

Best Answer

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.

Related Question