A transaction groups a set of database operations so they succeed or fail as one unit. Transfer money between two accounts and you must debit one and credit the other together — never one without the other. PostgreSQL guarantees this through the ACID properties, and understanding them is the foundation of writing correct concurrent applications, because the database is constantly serving many transactions at once.
ACID stands for Atomicity (all-or-nothing), Consistency (the database moves from one valid state to another, respecting constraints), Isolation (concurrent transactions do not corrupt each other's view of the data), and Durability (a committed transaction survives a crash). PostgreSQL delivers all four, but Isolation in particular is tunable: you choose how strictly transactions are insulated from one another.
That choice is the isolation level, and it trades strictness against concurrency. PostgreSQL offers Read Committed (the default), Repeatable Read, and Serializable. Alongside isolation, explicit locking lets you coordinate access to specific rows when the default behaviour is not enough. Getting these right is what separates a correct system under load from one that silently corrupts data.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.