Database Reliability
Strategies for data integrity, backups, and preventing data loss.
Database Reliability
Database Reliability is ensuring that data is securely stored, not lost, and the system can recover from unexpected events (Handling failures).
Cheat Sheet: Core Reliability Concepts
| Concept | Description |
|---|---|
| Data Durability | The guarantee that once data is successfully saved (Commit), it remains permanent, even if the system crashes or power is lost. |
| Backups | Making copies of data at specific times (Snapshot) to be used for recovery when severe errors occur. |
| Replication | Copying data to other database servers in real-time (or near real-time) to increase Availability. |
| Handling Failures | The process of handling and recovering the system from disruptions such as a Server crash or Network partition. |
Practical Examples
1. Data Durability via Write-Ahead Logging (WAL)
Scenario: Order processing system How it works: Before the Database writes data directly to the Data file, the system records the details of the changes into a Write-Ahead Log (WAL). If a power outage occurs while saving data, when the system restarts, the Database will read the WAL to repeat (Redo) the incomplete actions, ensuring that Data durability is not compromised.
2. Backups Strategy (3-2-1 Rule)
Scenario: Enterprise-level data protection from attacks or accidents Practice:
- Have 3 Copies of data (1 primary data + 2 backups)
- Store in 2 different Storage types (e.g., Local disk and Cloud storage)
- Store 1 Copy in an Offsite location Example: Setting up Automated nightly backups to Amazon S3 and having Point-in-Time Recovery (PITR) to allow rolling back to any point in time within the past 7 days.
3. Replication for High Availability
Scenario: The application has users nationwide and cannot tolerate Downtime. Practice: Set up Primary-Replica architecture
- Primary Node: Responsible for handling all Write transactions
- Replica Nodes: Continuously copy data from the Primary and handle Read queries Failure Handling: If the Primary node goes down, the system immediately promotes one of the Replica nodes to Primary (Automatic Failover) so the system can continue operating without affecting users.
4. Handling Failures in Application Logic
Scenario: Transferring money between accounts Practice: Use Database Transactions (ACID) to ensure Data integrity
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE user_id = 1;
-- If the application crashes here, the entire transaction is safely rolled back.
UPDATE accounts SET balance = balance + 1000 WHERE user_id = 2;
COMMIT;Handling Failure at the code level should include an appropriate Retry Mechanism and Log errors so Engineers can immediately investigate.