All articles
Backend from First Principles

Mastering Databases with Postgres: A Mental Model for Backend Engineers

Every backend system eventually asks the same question: "Where does the data live when the server shuts down?" The answer is a database. And how you design it determines whether your system scales or collapses.

2026-03-169 min read

This is the eighth fundamental in my backend-from-first-principles series. Databases are where theory meets reality — all the HTTP, routing, validation, and architecture knowledge we've covered so far means nothing if the data layer is broken.

The Mental Model: RAM is a Whiteboard, Disk is a Filing Cabinet

Your server has two kinds of memory:

  • RAM — A whiteboard. Blazing fast to read and write, but the moment you turn off the lights (restart the server), everything is erased. Small and expensive (8-32GB typical).
  • Disk — A filing cabinet. Slower to access, but the data stays put whether you're in the room or not. Massive and cheap (512GB-2TB+).

Databases live on disk. That's the entire point — persistence. When your to-do app restarts, your tasks are still there because the database wrote them to disk.

Caches like Redis live in RAM — fast but volatile. Databases like Postgres live on disk — slower but permanent. Most production systems use both: Redis for speed, Postgres for truth.

Why Not Just Use Text Files?

Before databases existed, data was stored in flat files. This breaks down for four reasons that a DBMS (Database Management System) was invented to solve:

  • No structure. A text file accepts anything. No schema, no types, no constraints. Garbage data gets in freely.
  • Painful parsing. You manually read lines, split strings, extract fields. Slow, error-prone, and language-dependent.
  • No integrity. Nothing prevents a payment amount from being stored as "banana" instead of a number.
  • No concurrency. Two users updating the same file simultaneously? Race condition. One user's write silently overwrites the other's. A database handles concurrent access with locking and transactions.

A DBMS solves all four: structured storage, efficient querying, data integrity enforcement, and safe concurrent access.

Relational vs Non-Relational: One Decision, Two Worldviews

Relational databases (SQL) — Think of a spreadsheet. Data lives in tables with rows and columns. Every row follows the same schema. Tables connect to each other through foreign keys. You define the structure before inserting data.

Non-relational databases (NoSQL) — Think of a folder of JSON files. Each document can have a different shape. No enforced schema. Flexible but unpredictable.

The key tradeoff:

  • Relational — The database enforces integrity. Wrong data type? Rejected. Missing required field? Rejected. Referencing a user that doesn't exist? Rejected. The database is your safety net.
  • Non-relational — The database accepts anything. Integrity becomes your application code's responsibility. More flexibility, but more bugs when you get it wrong.
A practical rule: if your data has clear relationships (users have orders, orders have items, items belong to categories), a relational database is almost always the right choice. Use NoSQL when your data genuinely has no predictable structure.

Why Postgres Wins for Most Applications

PostgreSQL has a killer feature that most engineers underestimate: JSONB support. It lets you store unstructured JSON data inside a relational database, with indexing and querying support.

This means you get:

  • Structured tables with strict schemas for your core data (users, orders, payments)
  • Flexible JSONB columns for dynamic data (user preferences, feature flags, metadata)

You don't need MongoDB just for flexibility. Postgres gives you both worlds in one system — relational integrity where you need it, document flexibility where it helps.

Beyond JSONB: Postgres is open source, SQL-standard compliant (making migrations to other SQL databases easier), battle-tested at massive scale, and extensible with custom types and functions.

Data Types: The Decisions That Compound

Choosing the wrong data type doesn't hurt today. It hurts six months from now when you're running migrations on a production database with 10 million rows. Here are the rules that save you:

The Golden Rules

  • IDs → Use UUID or BIGSERIAL. UUIDs are better for distributed systems (unique across servers, hard to guess). BIGSERIAL is simpler for single-server setups.
  • Strings → Use TEXT. Not VARCHAR(255) — that's a MySQL-era habit. In Postgres, TEXT and VARCHAR perform identically. TEXT avoids future migrations when your string needs to be longer. Enforce length limits in your application/validation layer.
  • Money / prices → Use DECIMAL(10,2). Never FLOAT. Floating-point math introduces rounding errors. $19.99 + $0.01 might equal $20.000000001. Financial data demands exact precision.
  • Timestamps → Use TIMESTAMPTZ (timestamp with timezone). Never bare TIMESTAMP. Without timezone info, you'll have bugs the moment your users span multiple timezones.
  • Booleans → BOOLEAN. Clean, simple, self-documenting.
  • Dynamic data → Use JSONB (not JSON). JSONB stores binary, so Postgres can index it and query it efficiently. Plain JSON is stored as text and requires re-parsing on every read.
  • Constrained values → Use ENUM. CREATE TYPE status AS ENUM ('active', 'archived', 'deleted'). Self-documenting and prevents invalid values at the database level.

Schema Design: The Blueprint

The mental model: A schema is the architectural blueprint of your building. You don't start construction by pouring concrete — you start by drawing the floor plan. Tables, columns, types, constraints, relationships — all defined before a single row of data enters the system.

Naming Conventions

  • Table names: plural, snake_case — users, project_members
  • Column names: snake_case — created_at, user_id (Postgres is case-insensitive, so camelCase creates pain)

Relationships

One-to-One (User ↔ Profile): Split into two tables. The profile table uses the user's ID as both its primary key and foreign key. Keeps the main table lightweight.

One-to-Many (Project ↔ Tasks): A project has many tasks. The tasks table holds a project_id foreign key pointing back to projects.

Many-to-Many (Users ↔ Projects): Requires a linking table (project_members) with a composite primary key of user_id + project_id. Each row represents one membership.

Constraints: Rules the Database Enforces for You

  • PRIMARY KEY — Unique + Not Null. Every row's identity.
  • FOREIGN KEY — You can't reference a user that doesn't exist. The database blocks it.
  • NOT NULL — This field must have a value. No silent nulls.
  • UNIQUE — No duplicates (e.g., email addresses).
  • CHECK — Custom rules: CHECK (priority BETWEEN 1 AND 5).

Referential Integrity: What Happens When You Delete?

If you delete a user who owns projects, what happens to those projects?

  • RESTRICT — Block the deletion. "You can't delete this user while they own projects."
  • CASCADE — Delete the user AND automatically delete all their projects and tasks. Powerful but dangerous.
  • SET NULL — Delete the user, set project.owner_id to NULL. The project survives, orphaned.

Migrations: Version Control for Your Database

The mental model: Git tracks changes to your code. Migrations track changes to your database schema.

Your schema will change. New columns, modified types, new tables, dropped constraints. Without migrations, these changes are manual, untracked, and impossible to reproduce across environments.

Every migration has two directions:

  • Up — Apply the change (create table, add column)
  • Down — Reverse the change (drop table, remove column). Always written in reverse order of creation.
-- Up migration
ALTER TABLE users ADD COLUMN age INTEGER;

-- Down migration (reverses the above)
ALTER TABLE users DROP COLUMN age;

Migrations run sequentially, each with a unique version ID. Every developer and every server runs the same migrations in the same order, guaranteeing identical database structures everywhere.

Tools like dbmate, Flyway, or Knex manage this workflow: dbmate up applies pending migrations, dbmate down rolls back.

Indexes: The Book Index Analogy

Without an index, finding a user by email means scanning every single row — a sequential scan. With 10 million users, that's catastrophically slow.

An index is literally a book index. Instead of reading every page to find "PostgreSQL," you flip to the index at the back, find the page number, and jump straight there.

Create indexes on columns you frequently:

  • Filter by (WHERE email = ...)
  • Sort by (ORDER BY created_at)
  • Join on (JOIN ... ON user_id = ...)

The tradeoff: Indexes speed up reads but slow down writes. Every INSERT and UPDATE must also update the index. Don't index everything — index what you query.

Security: Parameterized Queries

This connects directly to the validation article. Never build SQL queries by concatenating strings.

// DANGEROUS — SQL injection vulnerability
const query = `SELECT * FROM users WHERE name = '${userInput}'`;

// If userInput = "'; DROP TABLE users; --"
// → Your database is gone.

// SAFE — Parameterized query
const query = `SELECT * FROM users WHERE name = $1`;
db.query(query, [userInput]);
// The database treats userInput strictly as a string value, never as code.

Parameterized queries are non-negotiable. Combined with input validation at the controller layer, they eliminate the entire class of SQL injection attacks.

The Design Workflow: From UI to Database

This mirrors the REST API design workflow from the previous article — and that's intentional. The database schema and the API surface are designed together:

  1. Study the UI wireframes — What data does the user see and interact with?
  2. Extract the nouns — Users, Projects, Tasks, Tags → these become tables
  3. Design the schema — Define columns, types, ENUMs, relationships
  4. Define constraints — Primary keys, foreign keys, NOT NULL, UNIQUE, CHECK
  5. Write migrations — Version-controlled, up and down
  6. Seed test data — Populate with realistic data for development
  7. Build the repository layer — Parameterized queries, proper indexes

The Mental Model Summary

Carry this forward:

  • Disk = persistence. RAM is fast but volatile. Databases write to disk so data survives restarts.
  • Relational = safety net. The database enforces structure, types, and relationships. NoSQL shifts that burden to your application code.
  • Postgres = both worlds. Tables for structured data. JSONB for flexible data. You rarely need a separate NoSQL database.
  • Data types compound. TEXT over VARCHAR, DECIMAL for money, TIMESTAMPTZ for time, JSONB over JSON, UUID for distributed IDs. Get these right early.
  • Schema = blueprint. Define before building. Plural snake_case tables, snake_case columns.
  • Constraints = database-level validation. PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK. The database rejects bad data so your app doesn't have to.
  • Migrations = version control for schema. Every change tracked, reversible, reproducible across environments.
  • Indexes = targeted speed. Index what you query. Don't index everything. Reads get faster, writes get slightly slower.
  • Parameterized queries = non-negotiable. Never concatenate user input into SQL. Ever.

The database is the foundation. Everything above it — services, controllers, APIs — depends on this layer being solid. Get the schema right, enforce integrity at the database level, version your changes, and protect against injection. Everything else follows.

This is part 8 of my "Backend from First Principles" series. Everything in this article comes from what I learned watching the Backend from First Principles playlist by Sriniously — specifically the video "Mastering Databases with Postgres for backend engineering." I'm not claiming original research. This is me taking what I studied, internalizing it, and presenting it as a mental model that made the concepts stick for me. If it helps you too, even better.

Follow along for the next one.

#BackendEngineering #PostgreSQL #Databases #SQL #SoftwareEngineering #SystemDesign #LearningInPublic