ACID Properties
ACID stands for Atomicity (all-or-nothing), Consistency (data remains valid per constraints), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes). These four properties define a reliable transaction.
ACID is the integrity pledge: Atomicity (all or nothing — like a marriage vow), Consistency (data stays clean), Isolation (private booth at a restaurant — other tables don't hear your conversation), Durability (written in stone — survives the restaurant burning down).
Atomicity: if any statement in a transaction fails, all changes roll back. Consistency: transactions move the database from one valid state to another — constraints, triggers, and rules maintain invariants. Isolation: the degree to which concurrent transactions appear independent (controlled by isolation level). Durability: committed data is written to the WAL before the client receives acknowledgment, surviving power failure. ACID is implemented via WAL (durability), MVCC (isolation), constraint checking (consistency), and undo logging (atomicity).
NoSQL databases often trade ACID for availability and throughput — they offer BASE (Basically Available, Soft state, Eventual consistency). Distributed ACID (2PC — Two-Phase Commit) coordinates transactions across multiple nodes but introduces latency and availability risk (coordinator failure). Postgres' savepoints allow partial rollbacks within a transaction: SAVEPOINT my_save; ...; ROLLBACK TO my_save (retry part of a transaction without restarting the whole thing). DDL statements in PostgreSQL are transactional — you can CREATE TABLE, INSERT, and ROLLBACK all in one transaction. Most other databases (MySQL, Oracle) have implicit DDL commits.
ACID guarantees reliable transactions. Atomicity prevents partial updates. Consistency enforces data invariants. Isolation controls concurrency anomalies. Durability ensures commits survive crashes via WAL. PostgreSQL implements all four natively. I explain the tradeoff: higher isolation = more correct but more contention. PostgreSQL's DDL is transactional, letting me develop migrations with rollback safety.
Confusing Consistency in ACID with Consistency in CAP theorem. ACID Consistency is about database constraint satisfaction. CAP Consistency is about all nodes seeing the same data simultaneously. They're completely different concepts.