What is ACID in databases

ACID is an acronym for Atomicity, Consistency, Isolation, and Durability—a set of properties that guarantee database transactions are processed reliably, even in the event of errors, crashes, or concurrent access.

Here is a breakdown of how PostgreSQL implements each property:

Atomicity

Definition: Ensures that a transaction is treated as a single, indivisible unit: either all of its operations succeed, or the entire transaction is rolled back (the "all-or-nothing" rule).

PostgreSQL Implementation:

• Uses explicit transaction control (BEGIN, COMMIT, ROLLBACK).
• If any single statement fails within a transaction block, the entire transaction is marked as failed and is rolled back, preventing partial data changes.

Consistency

Definition: Guarantees that a transaction brings the database from one valid state to another. This means all changes must adhere to predefined rules and constraints.

PostgreSQL Implementation:

• Enforces all integrity constraints defined in the schema (e.g., PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL).
• If a transaction attempts to violate any rule, PostgreSQL automatically rolls back the entire transaction to maintain data validity.

Isolation

Definition: Ensures that concurrent transactions do not interfere with each other. Each transaction operates as if it were the only one running, preventing concurrent issues like dirty reads.

PostgreSQL Implementation:

• Uses Multi-Version Concurrency Control (MVCC). Instead of using locks to block readers, MVCC allows each transaction to see a consistent snapshot of the data as it existed when the transaction began.
• PostgreSQL supports the standard SQL Isolation Levels (Read Committed is the default, and Serializable offers the strongest isolation guarantee).

Durability

Definition: Guarantees that once a transaction has been committed, its changes are permanent and survive any subsequent system failure (e.g., power loss or crash).

PostgreSQL Implementation:

• Employs Write-Ahead Logging (WAL). All changes are written to a sequential log file on stable storage before the changes are applied to the main data files and the transaction is reported as complete.
• In the event of a crash, the WAL is replayed during recovery to restore the database to its last committed state, ensuring no committed data is lost.