17 Essential Database Lessons Every Developer Should Know

Google Cloud engineer Jaana Dogan summarizes 17 hard‑earned database lessons to help developers avoid common pitfalls.

If the network is reliable 99.999% of the time, you are lucky.

ACID has many meanings.

Each database implements consistency and isolation differently.

When you cannot manage locks, use optimistic locking.

Beyond dirty reads and data loss, other anomalies exist.

Your database and your sorting expectations may not always align.

Application‑level sharding can exist outside the application.

AUTOINCREMENT can be harmful.

Stale data can be useful and lock‑free.

Clock drift occurs between any clock sources.

Latency has many meanings.

Evaluate the performance requirements of each transaction.

Nested transactions can be harmful.

Transactions should not hold application state.

The query planner can provide all information about a database.

Online migrations can be complex but are achievable.

Significant database growth introduces unpredictability.

Network Reliability

Even with 99.999% availability, Google attributes only 7.6% of Spanner incidents to network issues, questioning the reliability of networks in practice.

ACID Explained

ACID stands for atomicity, consistency, isolation, and durability. It defines the guarantees a database transaction must provide, even in the face of crashes, errors, or hardware failures. Not all databases fully implement ACID, and interpretations vary.

Consistency and Isolation Levels

Databases offer multiple isolation levels—from serializable (most strict) to read‑uncommitted (least strict). The choice affects performance, contention, and the likelihood of anomalies such as write skew.

SELECT * FROM articles WHERE author = "rakyll" ORDER BY title;

Optimistic Locking

Optimistic locking records a version number or timestamp when reading a row and checks it before updating.

UPDATE products SET name = 'Telegraph receiver', version = 2 WHERE id = 1 AND version = 1

Other Anomalies

Beyond dirty reads, anomalies like write skew can break logical constraints. Detecting them often requires serializable isolation or explicit constraints.

BEGIN tx1; SELECT COUNT(*) FROM operators WHERE oncall = true; UPDATE operators SET oncall = TRUE WHERE userId = 4; COMMIT tx1; BEGIN tx2; SELECT COUNT(*) FROM operators WHERE oncall = true; UPDATE operators SET oncall = TRUE WHERE userId = 2; COMMIT tx2;

Sorting Guarantees

Database ordering follows the order in which transactions reach the database, not the order in application code, which can lead to surprising results in highly concurrent systems.

result1 = T1(); // promises result2 = T2();

Application‑Level Sharding

When predictable access patterns exist, developers can implement horizontal partitioning (sharding) at the application layer, allowing independent evolution of sharding strategies without redeploying the application.

AUTOINCREMENT Pitfalls

Auto‑increment IDs require a global lock in distributed systems, can create hotspot partitions, and may be less meaningful than natural keys such as usernames.

Stale Data and MVCC

Multi‑Version Concurrency Control (MVCC) allows reading older snapshots without locks, which can be useful for analytics or approximate queries, reducing latency.

Clock Drift and TrueTime

All clocks drift; NTP synchronization can still introduce latency. Google’s TrueTime combines GPS and atomic clocks to provide bounded time intervals, enabling Spanner to wait for time certainty before committing.

Latency Definitions

Latency can refer to database internal latency, network latency, or client‑perceived latency; distinguishing them is crucial for debugging performance issues.

Evaluating Transaction Performance

Measure throughput and latency for realistic workloads (e.g., inserting a row into a 50 M‑row table, querying friends‑of‑friends, or fetching a user timeline) to set reliable SLOs.

Nested Transactions

Nested transactions are not universally supported and can cause unexpected rollbacks; avoid them or handle them carefully.

with newTransaction():    Accounts.create("609-543-222")    with newTransaction():        Accounts.create("775-988-322")        throw Rollback();

Transactions Should Not Hold Application State

Embedding mutable application state in a transaction can cause retries to use inconsistent values; keep transactions stateless.

var seq int64; with newTransaction():     newSeq := atomic.Increment(&seq); Entries.query(newSeq); // other ops

Query Planner Insights

The query planner chooses between full‑table scans and index scans based on cost estimates; developers can use EXPLAIN output to diagnose slow queries.

Online Migrations

Online migrations involve dual writes, read‑path switching, and eventual cut‑over to a new database without downtime.

Growth‑Induced Unpredictability

As databases scale, assumptions about capacity, network partitions, and performance become invalid, requiring redesign of data models, deployment topology, and operational processes.