LEARN. PRACTICE. GROW.A unit of work
A transaction lets related changes commit or roll back. Constraints such as UNIQUE and FOREIGN KEY help preserve database rules, including with multiple clients. You must still choose isolation and handle concurrency conflicts.
Evidence-based performance
An index can speed up certain access patterns but adds write and storage cost. Analyze real queries and execution plans before adding indexes indiscriminately. Test changes in an appropriate environment and understand operational impact.
Guided workplace application
A fictional scheduler writes a run’s state and an audit event. If both must exist together, include both writes in the same transaction and commit only when the operation is valid. Otherwise roll back the still-open unit. To prevent two runs sharing the same mandatory key, combine NOT NULL with UNIQUE and handle conflicts in the application. A preceding SELECT does not prevent two sessions from observing absence simultaneously. In PostgreSQL 18, UNIQUE alone permits multiple NULLs by default; that may contradict the intention of a mandatory key. Local atomicity also does not cover a call to another system. Design external-effect coordination explicitly, including what happens when the outcome is unknown.
BEGIN;
UPDATE jobs SET status = 'running' WHERE id = 42;
INSERT INTO job_events(job_id, event) VALUES (42, 'started');
COMMITA scheduler must prevent two runs with the same key. An application-only check may fail under concurrency; an appropriate constraint protects the rule.
Common pitfalls
Using a prior SELECT as mutual exclusion; assuming UNIQUE implies NOT NULL.
Related topics: Indexes and execution plans · Concurrency and safe updates
The database should help preserve rules, even under concurrency.
Reference: PostgreSQL 18: Transactions · PostgreSQL 18 reference semantics; DR SQL 2026.2