Data Modeling & Transactions

Each new PostgreSQL connection forks a backend process — too expensive to open per query. A pool keeps a set of live connections and hands them out, so requests reuse them instead of reconnecting. Go's database/sql pools automatically; size it with SetMaxOpenConns, SetMaxIdleConns, and SetConnMaxLifetime.

A transaction groups several statements into one atomic unit — either all take effect or none do. COMMIT makes its changes permanent and visible to other transactions; ROLLBACK discards everything done since BEGIN, leaving the database as if the transaction never ran.

Atomicity — all of a transaction's statements apply, or none do. Consistency — it moves the database from one valid state to another, never breaking constraints. Isolation — concurrent transactions don't observe each other's unfinished work. Durability — once COMMIT returns, the change survives a crash, backed by the WAL.

Normalization organizes a relational schema into well-formed tables (1NF, 2NF, 3NF) by storing each fact exactly once. It removes redundancy and the update, insert, and delete anomalies that arise when the same value is duplicated across many rows.

The four levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each one forbids progressively more anomalies — dirty read, non-repeatable read, then phantom read. Higher isolation costs more concurrency; PostgreSQL defaults to Read Committed.

A constraint is a rule the database enforces on every write to keep data valid; a violating statement is rejected. Common ones are NOT NULL, UNIQUE, PRIMARY KEY (unique and not null), FOREIGN KEY (referential integrity), and CHECK (a predicate must hold).

A row-level lock guards a single row so only one transaction can modify it at a time; others touching that row wait until the holder commits or rolls back. It is finer-grained than a table lock, so unrelated rows stay concurrent, and both UPDATE and SELECT ... FOR UPDATE acquire one.

Denormalization deliberately reintroduces redundancy — duplicated columns or precomputed aggregates — to cut the joins a read path needs. Apply it when read latency matters more than write simplicity, accepting that every duplicated copy must be kept consistent on write.

Multi-Version Concurrency Control keeps several versions of each row, stamped with the transaction that created (xmin) and the one that deleted (xmax) it. A reader sees the version valid for its snapshot, so a writer creating a new version never blocks the reader and vice versa. The cost is dead row versions, which VACUUM later reclaims.

Optimistic locking holds no DB lock: a version column is read, and the update runs ... WHERE id = ? AND version = ? while bumping the version. If RowsAffected is 0, another writer won and you retry. It beats pessimistic SELECT FOR UPDATE when conflicts are rare — no held locks, higher throughput — but suffers repeated retries under high contention.

Vertical scaling means a bigger machine — simple, but capped and a single point of failure. Read replicas scale reads but lag asynchronously and do not help writes. Partitioning splits one table by range, list, or hash for pruning. Sharding spreads data across nodes by a shard key, scaling writes but making cross-shard joins hard.

A serial/identity key is a compact 4/8-byte integer whose sequential inserts append to the right of the B-tree, giving good locality. A random UUID is 16 bytes, unique, and generatable client-side with no round-trip, but scatters inserts — prefer UUIDv7.

SELECT ... FOR UPDATE takes a row-level write lock on each selected row, so other transactions touching those rows block until the holder commits. If two transactions lock rows in opposite order, each waits on a row the other holds — a deadlock. The engine detects the cycle and aborts a victim; consistent lock ordering avoids it.

PostgreSQL is a row-store OLTP engine: you dedup with a UNIQUE constraint or INSERT ... ON CONFLICT, enforced transactionally per row. ClickHouse is a columnar OLAP engine built for huge append-only scans and has no unique constraint — you dedup with a ReplacingMergeTree that collapses duplicates lazily at merge time, or argMax/GROUP BY at query time.

Document stores allow a flexible, per-record schema and embed related data inside one document, so a read needs no joins. They also shard horizontally more easily. Relational databases instead give joins, multi-row ACID transactions, fine-grained row locking, and enforced referential integrity.

Never one giant UPDATE. Batch by primary-key ranges (a few thousand rows per transaction), each batch a short transaction so locks are held briefly and autovacuum and replicas keep up. Make it resumable by tracking the last processed key, and idempotent so a re-run skips already-fixed rows (a WHERE that no longer matches a fixed row). Throttle between batches, watch replication lag and back off under load, and run off-peak.

An app-side pool is per-instance: run hundreds of replicas and each opens its own pool, so total connections far exceed what Postgres can serve (every connection is a backend process). PgBouncer sits in front and multiplexes many client connections onto a small server pool — in transaction mode a server connection is borrowed only for the length of one transaction.

Virtual sharding maps keys not directly to physical nodes but to a large, fixed number of virtual shards (buckets); each node then owns a set of those shards. Adding or removing a node moves whole virtual shards rather than rehashing every key, so rebalancing is cheap and data stays evenly distributed. It decouples the logical partitioning from the physical topology.