Sync vs async
- SQL course
- Expert
- Lesson 26/45
- 5 min
Learn synchronous and asynchronous replication.
Sync and async
Synchronous replication does not finish a write until the slave acknowledges; consistent but slower. Asynchronous skips the wait; faster but with a brief lag.
ALTER SUBSCRIPTION sub_orders
SET (synchronous_commit = on);Sync is consistent, async is fast.
Sync and async — details
Financial systems prefer synchronous: lost data is unacceptable. Analytical systems accept asynchronous; a brief lag is fine and performance matters more.
Practice Sync and async
Make the sub_orders subscription synchronous: SET (synchronous_commit = on).
Starter code
-- set synchronous
ALTER SUBSCRIPTION sub_ordersSample solution
ALTER SUBSCRIPTION sub_orders SET (synchronous_commit = on);Expected output
ALTER SUBSCRIPTION
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Sync vs async: match the terms
| Term | Meaning |
|---|---|
sync |
sync |
async |
async |
acknowledge |
ack |
lag |
lag |
2 Sync setting
ALTER SUBSCRIPTION sub_orders SET (synchronous_commit = ____)ALTER SUBSCRIPTION sub_orders SET (synchronous_commit = on)Explanation: With synchronous_commit = on the primary waits for the replica's confirmation before committing.
3 What does synchronous replication wait for?
- The backup to finish
- User input
- The disk to fill up
- Confirmation from the replica Correct answer
Explanation: Synchronous replication waits for slave acknowledgement.