Server Responsibilities · 4 of 6

Persistence

How the server moves data between memory and durable storage — through ORMs, query builders, transactions, and migrations. Where most performance and correctness problems eventually surface.

ORMsTransactionsMigrationsConnection PoolsN+1Caching
← Back to Server Side
Quick Facts

At a Glance

Basic Concepts

  • Repository / DAO: the layer that hides "how data is stored" from "what the app does."
  • ORM: object-relational mapper — maps rows to objects so you can write code instead of SQL.
  • Transaction: a unit of work that succeeds or fails atomically (the A in ACID).
  • Migration: a versioned, repeatable change to the schema, checked into source control.
  • Connection pool: a fixed set of reusable DB connections, sized to match the database — not the app.
Layers

From Object to Row

Raw SQL Drivers

pg, mysql2, jdbc. Fastest, most explicit, no magic. Pair with parameterized queries always.

Query Builders

Knex, jOOQ, sqlx, Kysely. Programmatic SQL — composable, type-safe, no surprises.

Lightweight ORMs

Drizzle, Prisma, SQLAlchemy Core. Schema-aware but stay close to SQL.

Full ORMs

Hibernate/JPA, Entity Framework, Django ORM, ActiveRecord. Rich object graphs, lazy loading, lifecycle hooks.

Heavier ORMs trade clarity for productivity. They shine on CRUD-heavy apps and obscure performance characteristics on hot paths — know when to drop down.

Transactions

Getting Atomicity Right

Isolation Levels
LevelAllowsWhen
Read CommittedNon-repeatable reads, phantoms.Default in Postgres & Oracle. Fine for most CRUD.
Repeatable ReadPhantoms (in some DBs).Reports needing a stable snapshot mid-transaction.
SerializableNothing — behaves as if transactions ran one at a time.Money, inventory, anywhere correctness > throughput. Expect retries on conflict.
Optimistic vs Pessimistic Locking

Optimistic: include a version column; on UPDATE, check the version hasn't changed; retry on conflict. Great for low contention.

Pessimistic: SELECT ... FOR UPDATE — others wait. Right when contention is high or retries are expensive.

Unit of Work

Group all changes a request makes, flush once, commit once. Don't sprinkle individual saves through your service — it makes rollback ineffective and produces N round-trips where one would do.

The Outbox Pattern

If a transaction must also publish an event ("order placed → email user"), write the event to an outbox table in the same transaction. A separate worker reads the outbox and publishes — guaranteeing the event ships iff the data was committed. Avoids the dual-write problem.

Performance

The Usual Suspects

The N+1 Query Problem

You load 50 orders, then iterate to load each one's customer — 1 + 50 round-trips instead of 1. Fix with eager loading (JOIN FETCH, include, select_related) or DataLoader-style batching. The single biggest performance bug in ORM-heavy code.

Connection Pool Sizing

App pool size should be smaller than the DB's max_connections divided by the number of app instances. Postgres usually wants fewer connections than developers expect — pgBouncer or RDS Proxy in front lets thousands of app connections share a small pool of real ones.

Indexes & Query Plans
  • Read EXPLAIN ANALYZE until you can. Sequential scan on a million-row table is the warning sign.
  • Index for the queries you actually run — composite indexes are order-sensitive.
  • Every index slows writes. Audit them periodically; remove unused ones.
  • Functions in WHERE clauses defeat indexes (WHERE lower(email) = …) — use a functional index or store normalized.
Caching Layers
  • In-process: LRU caches inside the app — fastest, but per-instance and stale-prone.
  • Distributed: Redis / Memcached. Set a TTL; design for cache miss being correct, not just fast.
  • Read-through vs write-through: read-through is simpler; write-through keeps cache fresh but doubles write latency.
  • Invalidation is the hard part — prefer short TTLs over clever event-based invalidation when you can.
Schema Evolution

Migrations Without Downtime

Migrations are code. Check them in. Run them automatically. Make them backwards-compatible with the previous app version so deploys can roll back.

  • Expand → Migrate → Contract: add new column, dual-write, backfill, switch reads, drop old column. Each step ships independently.
  • Avoid long-locking DDL on hot tables. In Postgres, prefer CREATE INDEX CONCURRENTLY; add columns with no default, then backfill.
  • Tools: Flyway, Liquibase (Java); Alembic (Python); Prisma Migrate, Drizzle Kit, Knex (Node); EF Core Migrations (.NET); Rails migrations (Ruby).
Pitfalls

Common Mistakes

  • Long-running transactions — they hold locks and bloat the WAL. Keep them short; never await user input inside one.
  • Distributed transactions across services — usually a sign you've drawn the service boundary wrong. Prefer the outbox pattern or sagas.
  • Soft deletes everywhere — every query needs WHERE deleted_at IS NULL forever. Use them deliberately, not by default.
  • Forgetting timezone-aware timestamps — store as timestamptz; never as a naive timestamp.
  • Auto-increment IDs in URLs — they leak volume and enable enumeration. Use UUIDs (or ULIDs) for anything user-visible.
Continue

Other Server Responsibilities

Authentication & SessionsAuthorizationValidationIntegrationsBackground Jobs ↑ Back to Server Side