PostgreSQL Core Concepts

Concept Map

1. Cluster, Databases & Schemas

A PostgreSQL cluster is a single running instance of the server. It contains one or more databases. Each database is fully isolated — you can't join tables across databases.

Within a database, schemas organize objects (tables, views, functions). Every database has a public schema by default.

-- Create a new database
CREATE DATABASE myapp;

-- Connect to it
\c myapp

-- Create a schema for your app
CREATE SCHEMA app;

-- Create a table in that schema
CREATE TABLE app.users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

2. Tables & Data Types

Tables are where your data lives. Each column has a data type that determines what values it can hold.

3. Constraints

Constraints are rules the database enforces automatically. They prevent invalid data from ever getting in.

CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,          -- auto-incrementing unique ID
  user_id    INTEGER NOT NULL REFERENCES users(id), -- foreign key
  amount     NUMERIC(10,2) CHECK (amount > 0),      -- must be positive
  status     TEXT NOT NULL DEFAULT 'pending',
  email      TEXT UNIQUE,                            -- no duplicates
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

4. Transactions & ACID

A transaction groups multiple SQL statements into a single atomic unit. Either all statements succeed, or none of them do.

BEGIN;
  -- Transfer $100 from account A to account B
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;

  -- If either UPDATE fails, ROLLBACK undoes everything
COMMIT; -- Makes both changes permanent

ACID guarantees:

• Atomicity — All or nothing. If any statement fails, everything rolls back.
• Consistency — Constraints are always satisfied before and after the transaction.
• Isolation — Concurrent transactions don't see each other's uncommitted changes.
• Durability — Once committed, data survives crashes (written to WAL on disk).

5. MVCC (Multi-Version Concurrency Control)

MVCC is PostgreSQL's secret weapon for concurrency. Instead of locking rows when someone reads them, Postgres keeps multiple versions of each row.

• Readers never block writers — a SELECT sees a snapshot of data at the transaction start
• Writers never block readers — an UPDATE creates a new row version; old version is still visible to existing transactions
• Dead tuples — old row versions accumulate until VACUUM cleans them up

6. Roles & Permissions

PostgreSQL uses roles (not "users" and "groups" — they're all roles). A role can own objects, log in, and be granted permissions.

-- Create a role that can log in
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';

-- Grant permissions
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app TO app_user;

-- Read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA app TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO readonly;