PostgreSQL Foundations: Persistent Data You Can Trust

Program memory is a whiteboard: fast and convenient, but wiped when the process stops. A database gives an application durable, shared memory with rules around concurrent reads and writes. It is not just a place to put objects. A good database protects facts while several users and services are trying to change them at once.

PostgreSQL is a strong default for many backend products because it combines mature relational modelling, transactions, constraints, excellent SQL, useful indexes, and escape hatches such as JSONB. Its reliability is not glamorous, but it is exactly what makes it a good first serious database.

Tables are not spreadsheets

A relational table has named columns, typed values, and rows. More importantly, it can express relationships and constraints that survive regardless of which code path writes data.

The diagram is not only documentation. It prompts useful questions: can a task exist without a project? What happens to tasks when a project is deleted? Can one user be a member of the same project twice? The schema can enforce answers to those questions.

Model the facts first

For a small project application, tables might begin like this:

create table projects (
  id uuid primary key,
  name text not null check (char_length(name) between 1 and 120),
  status text not null check (status in ('active', 'archived')),
  created_at timestamptz not null default now()
);

create table tasks (
  id uuid primary key,
  project_id uuid not null references projects(id),
  title text not null check (char_length(title) between 1 and 240),
  due_at timestamptz,
  created_at timestamptz not null default now()
);

There are several quiet design decisions here:

• not null says a value is required for every writer, not just today’s API.
• a foreign key stops a task pointing at a project that does not exist;
• check protects simple invariants even if an import script bypasses application validation;
• timestamptz stores an instant in time, which is almost always what an event timestamp means.

Application validation makes friendly errors. Database constraints are the final guardrail. Use both.

Choose types for meaning, not only storage

Some PostgreSQL choices pay off early:

Avoid double precision for currency. A tiny representation error multiplied across invoices becomes a real accounting problem. Also avoid making every column JSONB because it feels flexible; relational columns provide better constraints, joins, and query plans when the shape is known.

Relationships and deletion are product decisions

One-to-many is common: a project has many tasks, and each task has one project. A join table models many-to-many membership:

create table project_members (
  project_id uuid not null references projects(id) on delete cascade,
  user_id uuid not null references users(id) on delete cascade,
  role text not null check (role in ('viewer', 'editor', 'admin')),
  primary key (project_id, user_id)
);

on delete cascade is not a harmless default. It says deleting a project should delete its membership rows. That may be right; deleting invoices when a customer is removed almost certainly is not. Decide whether each relationship should restrict deletion, cascade it, or retain history with a soft-delete/archive policy.

Migrations are the shared history of the schema

A database schema cannot live only in the head of the developer who created it. A migration is a versioned, reviewable change that upgrades a database from one known shape to the next.

001_create_projects.sql
002_add_project_status.sql
003_create_tasks.sql

Run migrations in development, test, staging, and production through the same mechanism. For destructive changes, use an expand-and-contract sequence:

1. add the new nullable column or table;
2. deploy code that writes both old and new shapes;
3. backfill existing data in controlled batches;
4. switch reads to the new shape;
5. only then remove the old column in a later release.

This is slower than a single rename column, and much safer when two application versions may run during deployment.

Query safely and ask only for what you need

Always send values as parameters, never concatenate them into SQL:

select id, title, due_at
from tasks
where project_id = $1 and due_at >= $2
order by due_at asc
limit $3;

This prevents injection and lets PostgreSQL reuse query planning work. It also makes the query’s structure visible in code review.

Fetching a project and then a separate task list is sometimes right. Joining is sometimes right. The question is not “joins are bad” but “what data does this screen or use case actually need?” Watch for the N+1 pattern: loading 50 tasks, then issuing a separate query for each task’s creator. A join, batch query, or deliberately shaped response is usually better.

Index after you know the access path

An index is a data structure that helps PostgreSQL avoid scanning every row for certain queries. It speeds reads that match it, at the cost of storage and slower writes.

If the product page repeatedly runs:

select id, title from tasks
where project_id = $1 and status = 'open'
order by created_at desc;

then an index such as this may be justified:

create index tasks_project_status_created_at_idx
  on tasks (project_id, status, created_at desc);

Do not add indexes by superstition. Use real query patterns and EXPLAIN (ANALYZE, BUFFERS) to see what PostgreSQL does. The order of columns matters, and a pile of indexes can make inserts unexpectedly expensive.

Transactions protect a group of changes

When an operation must be all-or-nothing, use a transaction. For example, moving a task from one project to another may require updating the task, recording an audit event, and changing counters. If the second step fails, the first should not remain half-finished.

Transactions also have isolation behaviour. Start by understanding the failure modes you need to prevent—lost updates, double booking, stale reads—rather than turning every query into the strongest isolation level. Correctness comes from a combination of transactions, constraints, locks where necessary, and clear use-case design.

A practical database review

Before shipping a schema change, ask:

• What invariant does the database itself enforce?
• Could this migration lock a large table or rewrite too much data?
• What query will read this new data most often, and is it indexed appropriately?
• What happens when two requests update this record at the same time?
• Can the change be rolled out while old and new application code coexist?

The database is not an implementation detail to hide from thought. It is a partner in keeping the application honest.

A small concurrency example

Imagine two people opening the same task and both changing its title. If each request reads the row, edits it in memory, and writes the whole row back, the later write can silently erase the earlier one. This is a lost update.

One simple defence is optimistic concurrency: keep an updated_at timestamp or version number, and update only when the version still matches what the client read.

update tasks
set title = $1, version = version + 1
where id = $2 and version = $3;

If zero rows change, someone else got there first. Return a conflict and let the UI reload or help the user merge their edit. The point is not that every row needs a version column; it is that concurrency is a product decision. Booking a seat, transferring money, and editing a personal draft each need a different level of protection.