ACID and isolation
Atomicity, consistency, isolation and durability define a transaction. Isolation levels trade correctness for concurrency: read committed (default in many engines) permits non-repeatable reads; repeatable read prevents them; serializable prevents write skew but can abort transactions that must be retried.
MVCC
Multi-version concurrency control keeps multiple row versions so readers never block writers. The cost is version bloat, which vacuum or compaction must reclaim — a common source of mysterious slowdowns in write-heavy Postgres deployments.
Distribution
Replication gives read scaling and failover, but asynchronous replicas lag, so a read-after-write on a replica can return stale data. Sharding partitions data by key for write scaling and makes cross-shard joins and transactions expensive. Under a network partition, CAP forces a choice between consistency and availability.