Data Modeling & Transactions
Go code is rarely complex on its own — a goroutine takes a request, hits PostgreSQL, returns a response. The real complexity sits in two places: how the schema is designed and how transactions behave once writers pile up. database/sql and pgx give you direct SQL access but protect you from no conceptual mistake — wrong table decomposition, wrong surrogate key, wrong isolation level, wrong lock ordering: all of it compiles, passes local tests, and surfaces only under concurrent load.
The traps here are not about syntax. Candidates confuse normalization with compression and think higher normal forms merge tables rather than splitting them. They denormalize prematurely and forget the cost is paid on every write. They take a random UUID as "just another key", not noticing it breaks B-tree append locality. They believe SELECT FOR UPDATE is a shared read lock and expect the application to detect deadlocks itself. They think Read Committed saves them from phantoms and that a read replica scales writes. This topic dissects design and transactions layer by layer — so you answer each of these questions with a mechanism, not a memorized phrase.
Topic Map
- Normalization — 1NF/2NF/3NF, storing every fact exactly once, and eliminating update, insert, and delete anomalies.
- Denormalization — deliberate redundancy to cut joins off the read path, and the cost paid on every write.
- Constraints —
NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECKas race-safe invariants inside the database itself. - Surrogate Keys — serial vs UUID — a compact
serialwithB-treeappend locality versus a globally unique but scatteredUUID. - Document vs Relational Databases — flexible schema and embedding versus joins, multi-row ACID, and referential integrity.
- OLAP vs OLTP — a row-store transactional engine versus a column-store analytical one, and why the physical on-disk layout decides everything.
- Transaction Isolation — what a transaction is, the
ACIDguarantees,MVCCrow versions, three anomalies, four levels, and durability via the WAL. - Row Locking —
SELECT FOR UPDATE, lock acquisition order, and why two transactions deadlock. - Optimistic Locking — a
versioncolumn instead of holding a lock, conflict detection via rows affected, and retry. - Database Scaling — vertical, cache,
read replica, connection pool, andsharding, with their trade-offs across reads, writes, and complexity.
Common Mistakes and Traps
| Mistake | Consequence |
|---|---|
| Confusing normalization with compression or query speedup | A wrong mental model — it is schema-design discipline for correctness, not storage |
| Thinking higher normal forms merge tables | The opposite — they split data into more tables, removing duplicates |
| Denormalizing prematurely, without a read profile | Harder writes and a desync risk with no proven benefit |
| Relying on validation in the service code alone | Manual SQL, other services, and races bypass it — only a UNIQUE in the database is race-safe |
Treating PRIMARY KEY as just UNIQUE | It is also NOT NULL and defines row identity that FOREIGN KEYs reference |
Saying "UUID is slower than serial" without the cause | The cause is lost B-tree append locality from random inserts and page splits, not "big bytes" |
| Calling a document database "schemaless" | A schema exists, flexible on write; embedding removes joins, it does not add them |
Thinking OLAP is "OLTP but bigger" | The difference is layout — rows versus columns — under the query profile, not size |
Treating Serializable as the weakest isolation level | An inverted model — Serializable is the strongest, Read Uncommitted the weakest |
Thinking Read Committed catches phantom reads | Both non-repeatable and phantom reads remain possible on it |
Treating SELECT FOR UPDATE as a shared read lock | It is an exclusive row write lock — concurrent writers wait |
| Expecting the application itself to detect deadlocks | The DBMS detects and breaks the lock cycle; the code only retries the victim transaction |
| Treating optimistic locking as a "lock" in the database | There is no lock at all — the defense rests on a version column and the rows-affected check |
Treating a read replica as a way to scale writes | Replicas offload reads only; writes still bottleneck on the single primary |
Interview Relevance
Data design and transactions are a mandatory topic on any backend interview, and the question is not "do you know the word normalization" but whether you can reason about correctness, cost, and concurrency.
What interviewers check:
- Why you normalize a schema — eliminating redundancy and anomalies, not query speed; and why higher forms split tables.
- When you deliberately denormalize and what you pay for it on the write side.
- Why an invariant goes in the database (
UNIQUE,FOREIGN KEY,CHECK), not only in the service code. - The difference between
serialandUUIDat theB-treelevel, and whyUUIDv7exists. - How a document database differs from a relational one and which guarantees you lose.
- How a column-store
OLAPengine differs from a row-storeOLTPone and why it is about on-disk layout. - The four isolation levels, three anomalies, which level forbids what — and PostgreSQL's specifics.
- What
SELECT FOR UPDATEdoes, why the opposite acquisition order deadlocks, and when you take optimistic locking instead. - Which mechanisms scale reads versus writes, and the cost of each.
A typical wrong answer: "Read Committed is the safe level, it protects against all read anomalies". That triggers a discussion of how Read Committed forbids only dirty reads, while non-repeatable and phantom reads remain possible on it, and how the choice of level is a deliberate trade-off between correctness and concurrency.