The Mindset

A database is not a spreadsheet. It is not a file you read and write. It is a rule engine for your data.

The database's job is to enforce rules so your application never has to deal with garbage data. When your application has a bug, the database prevents corruption. When two users act simultaneously, the database maintains order. When you need answers, the database gives them fast.

But there is a second mindset shift: A database schema is not a one-time drawing. It is a living contract between your application and your data. You will change it under load, under pressure, and while users are actively writing to it. Design for the data; migrate with a plan.

What Is a Database, Really?

The Problem with Files

Imagine storing user data in a text file:

alice@example.com|Alice|30
bob@example.com|Bob|25

This works for a tiny system. But what happens when:

• Two programs write at the same time? → Corrupted file (the classic "last write wins" race).
• You want only users older than 25? → You must read the entire file, parse every line, and discard the rest. No indexes exist.
• You delete Bob but leave his orders somewhere else? → Orphaned data. No referential integrity.
• The system crashes while writing? → Partial writes, corrupt file. No durability or crash recovery.
• You want to update Alice's age? → You must rewrite the whole file. No in‑place updates.

A Database Management System (DBMS) solves all of these. It provides:

• Concurrency Control – Multiple users can safely read/write simultaneously.
• Query Language – Ask complex questions (e.g., "Find all orders from last month where total > $1000") and get answers fast, thanks to indexes and query optimizers.
• Integrity – Enforce rules (primary keys, foreign keys, check constraints, uniqueness) so bad data never enters.
• Durability – Committed data survives crashes. Write‑ahead logs, replication, and backups ensure safety.
• Security – Fine‑grained permissions (who can read/update which tables and columns).

Design Before Code

The most expensive mistake in backend engineering is building the schema while building the API. Tables are harder to change than application code. A column added without thought becomes a migration headache six months later.

Before you write CREATE TABLE, answer these five questions:

1. What are the entities? (User, Order, Product)
2. How do they relate? (1:1, 1:N, M:N)
3. What must always be true? (An order must have a customer. Price cannot be negative.)
4. How will this table grow? (1,000 rows or 1 billion?)
5. How will I change this later without downtime? (The question most guides skip.)

Relational Databases: The Foundation

A relational database organises data into tables (also called relations). Each table represents an entity (like customers, products, or orders) or a relationship between entities.

• A table is a two‑dimensional grid of rows and columns.
• Each row (or record) is a single instance of that entity.
• Each column (or attribute) holds a specific piece of data about that entity.

What is an entity?

An entity is an object or concept that can be described by many attributes.

Suppose you run a store selling Sci-Fi products. Each product is an entity with attributes:

• name
• description
• price
• manufacturer

These become columns. Each product becomes a row.

product_id is the primary key (PK): unique, non-NULL, and indexed automatically.

Relational vs. Other Databases

Rule: Start relational. Move to specialized stores only when you can measure that PostgreSQL is the bottleneck.

RDBMS & SQL

A Relational Database Management System is software that manages data on disk.

• PostgreSQL — Feature-rich, strict, excellent for complex queries.
• MySQL — Ubiquitous, simpler, good for read-heavy workloads.
• SQLite — File-based, zero setup. Great for testing, prototypes, and mobile.

SQL (Structured Query Language) is the language to create, modify, and query data:

SELECT *
FROM products
WHERE price > 20;

Your First Table & CRUD Operations

Let's get our hands dirty. We'll create a database, a table, and perform the four basic operations: Create, Read, Update, Delete (CRUD).

Creating a Database

In PostgreSQL, you connect to a server and then create a new database:

-- Connect with psql (command-line tool)
-- $ psql -U postgres
 
CREATE DATABASE learning;
\c learning   -- connect to it

If you're using a GUI (like pgAdmin, DBeaver, or DataGrip), you can create it via the interface.

Creating a Table

A table is a template. It defines the columns and their types, plus any constraints.

CREATE TABLE students (
    student_id   BIGSERIAL PRIMARY KEY,      -- Use BIGINT for IDs in real systems
    first_name   VARCHAR(50) NOT NULL,
    last_name    VARCHAR(50) NOT NULL,
    email        VARCHAR(100) UNIQUE,
    birth_date   DATE,
    gpa          DECIMAL(3,2),
    enrolled     BOOLEAN DEFAULT TRUE,
    created_at   TIMESTAMPTZ DEFAULT NOW(),   -- Always track creation time
    updated_at   TIMESTAMPTZ DEFAULT NOW()
);

Keyword breakdown:

Seeing Your Table

\d students                -- Describes the table (in psql)
SELECT * FROM students;    -- See all rows (empty initially)

The Four Operations (CRUD)

CREATE: Inserting Data

-- Insert a single row
INSERT INTO students (first_name, last_name, email, birth_date, gpa)
VALUES ('Alice', 'Johnson', 'alice@example.com', '2000-05-12', 3.85);
 
-- Insert multiple rows
INSERT INTO students (first_name, last_name, email, birth_date, gpa)
VALUES
    ('Bob', 'Smith', 'bob@example.com', '2001-08-22', 3.20),
    ('Charlie', 'Brown', 'charlie@example.com', '1999-11-03', 3.90);
 
-- Notice we didn't provide student_id (auto-generated) and enrolled (defaults TRUE).

Key Point: When you insert, you can omit columns that have defaults or are nullable. If you omit a NOT NULL column without a default, you'll get an error.

Notice we didn't specify student_id (auto-generated) or enrolled (defaults to TRUE).

READ: Selecting Data

-- All columns, all rows
SELECT * FROM students;
 
-- Specific columns
SELECT first_name, last_name FROM students;
 
-- Concatenation and aliases
SELECT first_name || ' ' || last_name AS full_name FROM students;
 
-- Filtering (we'll dive deeper later)
SELECT * FROM students WHERE gpa > 3.5;

UPDATE: Changing Data

-- Update Alice's GPA
UPDATE students
SET gpa = 3.95
WHERE student_id = 1;
 
-- You can update multiple columns at once
UPDATE students
SET gpa = 4.0, enrolled = FALSE
WHERE student_id = 2;

⚠️ WARNING: If you omit the WHERE clause, every row in the table will be updated. This is a common beginner mistake.

DELETE: Removing Data

DELETE FROM students WHERE student_id = 2;

Same warning: without WHERE, you delete everything.

Production reality: In real systems, you almost never DELETE. You soft delete by adding a deleted_at TIMESTAMPTZ column. This preserves referential integrity, audit trails, and accidental recovery.

-- Instead of DELETE:
UPDATE students SET deleted_at = NOW() WHERE student_id = 2;
 
-- Then filter it out in every query:
SELECT * FROM students WHERE deleted_at IS NULL;

Table Management Commands

-- Add a new column
ALTER TABLE students ADD COLUMN phone VARCHAR(20);
 
-- Change a column's data type (may require casting)
ALTER TABLE students ALTER COLUMN phone TYPE TEXT;
 
-- Rename a column
ALTER TABLE students RENAME COLUMN phone TO mobile;
 
-- Drop a column (be careful)
ALTER TABLE students DROP COLUMN mobile;
 
-- Drop the entire table (irreversible)
DROP TABLE students;

Asking Questions with SELECT

SELECT is where you spend 80% of your SQL life. Master it and you master data retrieval.

Basic Filtering with WHERE

-- Comparison operators
SELECT * FROM students WHERE gpa > 3.5;
SELECT * FROM students WHERE enrolled = TRUE;
SELECT * FROM students WHERE birth_date >= '2000-01-01';

Comparison operators you'll use:

Examples:

-- Range
SELECT * FROM students WHERE gpa BETWEEN 3.0 AND 3.5;
 
-- In a list
SELECT * FROM students WHERE last_name IN ('Smith', 'Johnson', 'Brown');
 
-- Missing data
SELECT * FROM students WHERE email IS NULL;
 
-- Pattern matching with LIKE (case‑sensitive in PostgreSQL)
-- % = any sequence of characters, _ = exactly one character
SELECT * FROM students WHERE email LIKE '%@example.com';  -- Ends with @example.com
SELECT * FROM students WHERE first_name LIKE 'A%';         -- Starts with A
SELECT * FROM students WHERE last_name LIKE '_mith';       -- ?mith (e.g., Smith)

• % = any number of characters (including zero)
• _ = exactly one character

Logical Operators

-- AND, OR, NOT
SELECT * FROM students
WHERE gpa > 3.5 AND enrolled = TRUE;
 
SELECT * FROM students
WHERE gpa > 3.9 OR birth_date < '2000-01-01';
 
SELECT * FROM students
WHERE NOT enrolled;   -- Same as enrolled = FALSE

Sorting Results

-- ASC (ascending) is default, DESC for descending
SELECT * FROM students ORDER BY gpa DESC;        -- Highest first
SELECT * FROM students ORDER BY last_name ASC;    -- A to Z
SELECT * FROM students ORDER BY gpa DESC, last_name ASC;   -- Tie‑breaker

Limiting and Pagination

-- Top 5 students by GPA
SELECT * FROM students ORDER BY gpa DESC LIMIT 5;
 
-- Pagination: items 21‑30 (page 3 with 10 per page)
SELECT * FROM students ORDER BY student_id LIMIT 10 OFFSET 20;

Note: OFFSET can be slow for large offsets. For large pagination, use keyset pagination (e.g., WHERE id > last_id ORDER BY id LIMIT 10).

The Heart of Relational Design: Keys

This is the most important concept in this entire guide. Everything else is syntax. This is architecture.

The Problem: One Table to Rule Them All?

Imagine an orders table like this:

What's wrong?

1. Repetition: Alice's name and email appear on every order row. If she changes her email, you must update every row.
2. Price duplication: Product price is stored per order. If you later change the product's price, you lose historical accuracy (did Alice pay the old or new price?).
3. Update anomalies: If you update only one row's product price, you have inconsistent data.
4. Insertion anomalies: Can you add a new product that hasn't been ordered yet? No, because there's no order row to attach it to.
5. Deletion anomalies: If you delete Bob's order, you lose all information about the product he bought.

The Solution: Normalise into Separate Entities

We split data into entities (things) and connect them with keys. This is called normalisation (we'll cover it deeply later).

┌─────────────────┐         ┌─────────────────┐
│    customers    │         │     orders      │
├─────────────────┤         ├─────────────────┤
│ customer_id (PK)│◄────────│ order_id (PK)   │
│ name            │    1:N  │ customer_id (FK)│
│ email           │         │ order_date      │
└─────────────────┘         └─────────────────┘
                                    │
                                    ▼
                            ┌─────────────────┐
                            │   order_items   │
                            ├─────────────────┤
                            │ item_id (PK)    │
                            │ order_id (FK)   │
                            │ product_id (FK) │
                            │ quantity        │
                            │ unit_price      │   ← snapshot price at order time
                            └─────────────────┘
                                    │
                                    ▼
                            ┌─────────────────┐
                            │    products     │
                            ├─────────────────┤
                            │ product_id (PK) │
                            │ name            │
                            │ current_price   │
                            └─────────────────┘

Now:

• Customer's name is stored once. Update it in one place.
• Product price is stored once in products.current_price; order items store a snapshot unit_price so historical orders remain correct.
• You can add a product without any orders.
• Deleting an order doesn't delete the product or customer.

Primary Key (PK)

The primary key uniquely identifies each row in a table. Every table should have one.

• SERIAL for small tables.
• BIGSERIAL for tables that might grow huge (orders, logs).
• UUID when IDs are exposed in URLs (prevents guessing, helps distributed systems).

-- Example with UUID
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL
);

Foreign Key (FK)

A foreign key is a column (or set of columns) that references the primary key of another table. It establishes a relationship and enforces referential integrity.

CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMPTZ DEFAULT NOW(),
    total DECIMAL(10,2) DEFAULT 0
);

The REFERENCES keyword tells PostgreSQL: "Every customer_id in orders must exist in customers."

What this prevents:

• Creating an order for a customer that doesn't exist.
• Deleting a customer who still has orders (unless you specify cascade).

Referential Actions

When you define a foreign key, you can specify what happens when the referenced row is deleted or updated.

CREATE TABLE orders (
    customer_id BIGINT NOT NULL
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT      -- default, prevents deletion
        ON UPDATE CASCADE       -- if customer_id changes, cascade here
);

Best Practice: Use ON DELETE RESTRICT for financial data where you never want accidental data loss. Use ON DELETE CASCADE for child records that have no meaning without the parent (e.g., order_items). Always think about the business meaning.

Foreign Key Syntax: Inline vs. Named

-- Inline (simple, but limited)
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT REFERENCES authors(author_id) ON DELETE CASCADE
);
 
-- Named constraint (more flexible, allows composite keys and custom names)
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT,
    CONSTRAINT fk_books_authors
        FOREIGN KEY (author_id)
        REFERENCES authors(author_id)
        ON DELETE CASCADE
);

Pro tip: Always name your constraints. It makes error messages clearer and migrations easier.

Composite Keys

A primary key or foreign key can consist of multiple columns.

-- Example: An enrollment table that tracks which student is enrolled in which course.
-- The combination (student_id, course_id) must be unique.
CREATE TABLE enrollments (
    student_id BIGINT REFERENCES students(student_id),
    course_id BIGINT REFERENCES courses(course_id),
    enrollment_date DATE DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);
 
-- Foreign key can also be composite (references a composite primary key)
CREATE TABLE order_items (
    order_id BIGINT,
    product_id BIGINT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

JOINs Demystified

JOIN combines rows from two (or more) tables based on a related column. This is where relational databases shine.

Setup for Examples

We'll use a simple employees and departments table.

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);
 
INSERT INTO departments (dept_name) VALUES
    ('Engineering'),
    ('Sales'),
    ('HR'),
    ('Marketing');
 
CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT REFERENCES departments(dept_id)
);
 
INSERT INTO employees (emp_name, dept_id) VALUES
    ('Alice', 1),
    ('Bob', 1),
    ('Charlie', 2),
    ('Diana', NULL),   -- no department
    ('Eve', 5);        -- department 5 doesn't exist (violates FK, so this wouldn't be allowed)

Note: The Eve insert would fail if the FK is enforced. We'll use a dataset without that row for the examples.

How JOINs Actually Work (Internally)

Before filtering, a JOIN conceptually produces a Cartesian product – every combination of rows from the left table and the right table. Then the ON clause filters to only matching rows.

Given a products table with 3 rows and a reviews table with 3 rows, the database first generates 9 combinations, then applies the ON condition to keep only those where products.id = reviews.product_id.

You can see this with CROSS JOIN (which is the Cartesian product).

-- Cross join – rarely used directly
SELECT * FROM products CROSS JOIN reviews;
-- This returns all 3×3 = 9 rows.

The JOIN with ON is just a filtered cross join.

INNER JOIN: Only Matches

Returns only rows where the join condition matches on both sides.

SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Result:

• Diana is excluded because dept_id IS NULL.
• No Marketing department appears because no employee is assigned there (so no matching rows).

LEFT JOIN: All from Left, Matching from Right

Returns all rows from the left table, plus matching rows from the right. If no match, the right side columns are filled with NULL.

SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Result:

Now Diana appears because she's on the left. Marketing still doesn't show because no employee has that department.

RIGHT JOIN: All from Right

Rarely used; it's the same as a LEFT JOIN with the tables swapped.

SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

This returns all departments, even those with no employees. Marketing would appear with NULL for emp_name.

FULL OUTER JOIN: Everything from Both

Returns all rows from both tables, matching where possible. Missing sides get NULL.

SELECT e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

Now you see Diana (no dept) and Marketing (no employees).

Multiple JOINs

In real applications, you often join many tables. For an e‑commerce order query:

SELECT
    o.order_id,
    c.name AS customer_name,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2025-01-01';

Visual Summary

!image.png

INNER JOIN:     (A ∩ B)        – Only rows that have a match in both tables.
LEFT JOIN:      A + (A ∩ B)    – All rows from A, with B's data if available.
RIGHT JOIN:     B + (A ∩ B)    – All rows from B, with A's data if available.
FULL JOIN:      A ∪ B          – Everything from both, NULLs where missing.

Self‑JOIN: Table Joining Itself

Sometimes you need to relate a table to itself (e.g., employees and their managers).

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT,
    manager_id INT REFERENCES employees(id)
);
 
-- Query to get employee and their manager's name
SELECT
    e1.name AS employee,
    e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

We use LEFT JOIN because the CEO has manager_id = NULL.

Aggregation: Thinking in Sets

Aggregation collapses many rows into one (or a few) summary row.

Basic Aggregate Functions

Common aggregate functions: COUNT, SUM, AVG, MIN, MAX.

SELECT
    COUNT(*) AS total_employees,
    AVG(salary) AS average_salary,
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary,
    SUM(salary) AS total_payroll
FROM employees;

GROUP BY: Aggregation Per Category

Most of the time, you want aggregates per group (e.g., average salary by department).

SELECT
    d.dept_name,
    COUNT(e.emp_id) AS employee_count,
    AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

Important: Every column in the SELECT list must be either:

1. Listed in the GROUP BY clause, or
2. Wrapped in an aggregate function.

HAVING: Filter After Aggregation

-- Departments with more than 5 employees
SELECT
    d.dept_name,
    COUNT(*) AS emp_count
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING COUNT(*) > 5;

Compare WHERE vs HAVING:

-- Bad: filters after aggregation (HAVING) when we could filter before (WHERE)
SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id
HAVING salary > 30000;   -- ERROR: salary not in GROUP BY
 
-- Correct: filter rows before grouping
SELECT dept_id, AVG(salary)
FROM employees
WHERE salary > 30000       -- removes low salaries first
GROUP BY dept_id
HAVING AVG(salary) > 50000; -- then keep high-avg departments

Using DISTINCT – Unique Values

-- Count distinct departments
SELECT COUNT(DISTINCT dept_id) FROM employees;
 
-- List unique department names
SELECT DISTINCT dept_name FROM departments;

Window Functions (Advanced)

-- Rank employees by salary within each department
SELECT
    emp_name,
    dept_id,
    salary,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM employees;

SQL query order of execution

Understanding the order in which SQL executes clauses is vital for writing correct and efficient queries.

Logical order (not the same as the written order):

1. FROM / JOIN : Determine the source tables and combine them
2. WHERE : Filters rows
3. GROUP BY : Group rows into buckets
4. Aggregate functions: Compute summaries per group
5. HAVING : Filters the aggregated data
6. SELECT : Select columns, compute expressions, apply aliases
7. ORDER BY : Sorts the final data
8. LIMIT / OFFSET : Slice the result set

Example

Suppose we have:

SELECT department, COUNT(*) AS employee_count
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) >= 5
ORDER BY employee_count DESC
LIMIT 3;

Logical execution:

1. FROM employees
2. JOIN departments ON e.department_id = d.id
3. WHERE salary > 50000
4. GROUP BY department
5. Aggregate COUNT(*) for each department
6. HAVING COUNT(*) >= 5
7. SELECT department, employee_count
8. ORDER BY employee_count DESC
9. LIMIT 3

Why this matters:

• You cannot use a column alias from SELECT in WHERE because WHERE runs before SELECT. Example: SELECT salary * 1.1 AS bonus WHERE bonus > 5000 is invalid. You'd have to repeat the expression or use a subquery.
• You can use column aliases in ORDER BY because it runs last.
• HAVING can reference aggregate functions because they're computed before HAVING.

Advanced Querying

Subqueries: Queries Inside Queries

-- Students with above-average GPA
SELECT first_name, gpa
FROM students
WHERE gpa > (SELECT AVG(gpa) FROM students WHERE deleted_at IS NULL);

Correlated Subquery

-- Employees earning more than their department average
SELECT e1.emp_name, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

CTEs: Make It Readable

Common Table Expressions (WITH) break complex queries into named steps.

WITH dept_stats AS (
    SELECT dept_id, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
    FROM employees
    GROUP BY dept_id
),
high_earners AS (
    SELECT emp_name, salary, dept_id
    FROM employees
    WHERE salary > 80000
)
SELECT
    h.emp_name,
    h.salary,
    d.dept_name,
    ds.avg_salary
FROM high_earners h
JOIN departments d ON h.dept_id = d.dept_id
JOIN dept_stats ds ON d.dept_id = ds.dept_id;

When to use CTEs: Any query with more than one subquery or when you need to reuse a result.

Database Relationships and Cardinality

Now that you know how tables connect with keys, let's formalize the patterns of connection.

The Three Patterns

One-to-One

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT UNIQUE
);
 
CREATE TABLE profiles (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT UNIQUE,  -- UNIQUE enforces one profile per user
    full_name TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

One-to-Many

This is the bread and butter of relational design.

CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    name TEXT
);
 
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT,
    total_amount NUMERIC,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

One customer can have many orders. Each order belongs to exactly one customer.

Many-to-Many Requires a Junction Table

You cannot connect both sides with a single foreign key because each side needs to point to multiple rows. The fix is a third table:

CREATE TABLE students (
    id BIGSERIAL PRIMARY KEY,
    name TEXT
);
 
CREATE TABLE courses (
    id BIGSERIAL PRIMARY KEY,
    title TEXT
);
 
CREATE TABLE enrollments (
    student_id BIGINT REFERENCES students(id) ON DELETE CASCADE,
    course_id BIGINT REFERENCES courses(id) ON DELETE CASCADE,
    enrolled_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (student_id, course_id)  -- Prevents duplicate enrollments
);

Optional vs. Required

Self-Referencing Tables

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id BIGINT NULL REFERENCES employees(id),
    CHECK (id <> manager_id)  -- Prevent self-management
);

Database Normalization: Eliminating Redundancy

Normalization is the process of organizing data to reduce redundancy and prevent anomalies. It's not a strict rule set but a guideline for good design.

The Three Anomalies

Let's start with a denormalized table:

Orders with product details:

Problems:

• Insertion Anomaly: Can't add a new customer without an order. Want to add Charlie? No order yet, so no row.
• Update Anomaly: Alice changes her name to "Alice Smith". You must update every row. Miss one, data inconsistent.
• Deletion Anomaly: Delete order 103 (Bob's keyboard). Bob's information disappears entirely.

Normalization solves these.

First Normal Form (1NF)

Rules:

• All rows must be unique (a primary key exists).
• Each cell contains a single atomic value (no lists or sets).
• Each column contains values of the same type.

Violation example:

Here Orders contains a list. Fix: Split into two tables.

sql

-- Orders table
CREATE TABLE orders (
	order_id SERIAL PRIMARY KEY,
	customer_id INT,
	order_date DATE
);
 
-- Order items table
CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_name TEXT,
    quantity INT
);

Also, avoid composite values like CustomerName = "Bob Jones"; use first_name and last_name.

Second Normal Form (2NF)

Requirements:

• Must be in 1NF.
• No partial dependency – every non‑key column must depend on the entire primary key (for tables with composite keys).

Example violation: A table student_courses with primary key (student_id, course_id).

course_name and instructor depend only on course_id, not on student_id. This is a partial dependency.

Fix: Split into two tables.

-- Courses
CREATE TABLE courses (
    course_id VARCHAR(10) PRIMARY KEY,
    course_name TEXT,
    instructor TEXT
);
 
-- Enrollments (junction)
CREATE TABLE enrollments (
    student_id INT,
    course_id VARCHAR(10),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Now course_name is stored once per course.

Third Normal Form (3NF)

Requirements:

• Must be in 2NF.
• No transitive dependency – non‑key columns must depend only on the primary key, not on other non‑key columns.

Example violation: A table of tournament winners.

winner_dob depends on winner, not on (tournament, year). So if Novak's DOB changes, we'd need to update multiple rows. That's a transitive dependency.

Fix:

-- Winners table
CREATE TABLE winners (
    winner TEXT PRIMARY KEY,
    dob DATE
);
 
-- Tournament results table
CREATE TABLE tournament_results (
    tournament TEXT,
    year INT,
    winner TEXT,
    PRIMARY KEY (tournament, year),
    FOREIGN KEY (winner) REFERENCES winners(winner)
);

Another example: Employee with department phone.

dept_phone depends on dept, not emp_id. Fix: split out departments.

When to Stop Normalizing (Denormalization)

Normalize to 3NF by default. Denormalize only when:

• You have a measured performance problem (e.g., slow joins on huge tables).
• You're building a data warehouse for analytics (star schemas).
• You're using a materialized view for caching.

Rule of thumb: 3NF for transactional systems (OLTP). Denormalization for reporting/analytics (OLAP). Always measure before denormalizing.

Database Design Theory

Database design is not drawing tables. Database design is translating real-world behavior into structured, queryable data.

Goals

Real-World Design Blueprint: E-Commerce

Step 1: Gather Requirements

Users can: browse products, add to cart, place orders, pay, view history. Admins can: manage products, manage inventory.

Key questions:

• Can a user place multiple orders? → Yes (one-to-many)
• Can an order contain multiple products? → Yes (many-to-many via OrderItem)
• Does a product belong to a category? → Yes (many-to-one)
• Do we track payment? → Yes
• Do we track stock? → Yes
• Can an order be refunded? → Yes (affects Payment design)
• Do we need audit trails? → Yes (who changed what)

Step 2: Identify Entities & Relationships

Entities: User, Product, Category, Order, OrderItem, Payment

Relationships:

• User → Order (1:N)
• Order → OrderItem (1:N)
• OrderItem → Product (N:1)
• Product → Category (N:1)
• Order → Payment (1:N, because retries/refunds create multiple payment records)

Step 3: Define Attributes & Keys

-- Users (never delete users; soft delete)
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ          -- Soft delete
);
 
-- Categories
CREATE TABLE categories (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);
 
-- Products
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    category_id BIGINT REFERENCES categories(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Orders
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Order Items (historical price preserved)
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price_at_purchase DECIMAL(10,2) NOT NULL  -- Snapshot at time of order
);
 
-- Payments (1:N with orders; supports retries, partial payments, refunds)
CREATE TABLE payments (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id),
    amount DECIMAL(10,2) NOT NULL CHECK (amount > 0),
    payment_method VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'completed', 'failed', 'refunded')),
    transaction_id VARCHAR(100) UNIQUE,
    paid_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Step 4: Validate Against Edge Cases

Step 5: Review with Stakeholders

Questions that arise:

• Can one order have multiple payments? → Yes (partial payments, retries).
• Do we need order tracking history? → Add order_status_history table.
• Can we refund? → Already supported via payments.status = 'refunded'.

Constraints & Data Integrity

Constraints are rules enforced by the database. They are your last line of defense against bad data.

Types of Constraints

CHECK Constraints: Custom Business Rules

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2) CHECK (price >= 0),
    stock INTEGER CHECK (stock >= 0),
    category VARCHAR(20) CHECK (category IN ('electronics', 'clothing', 'food')),
    launch_date DATE,
    discontinue_date DATE,
    CHECK (discontinue_date IS NULL OR discontinue_date > launch_date)
);

You can also add CHECK constraints after table creation:

ALTER TABLE products ADD CONSTRAINT positive_price CHECK (price >= 0);

Unique Constraints on Multiple Columns

-- Ensure a user can review a product only once
CREATE TABLE reviews (
    review_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    product_id INT REFERENCES products(product_id),
    rating INT CHECK (rating BETWEEN 1 AND 5),
    review_text TEXT,
    UNIQUE (user_id, product_id)
);

Data Types Deep Dive

Choosing the right data type is critical for performance, storage, and correctness

Strings

Rule: Use VARCHAR for most short strings, TEXT for long ones. CHAR is almost never needed.

Numbers

Integers (whole numbers):

Best practice: Use BIGINT for primary keys in modern systems. You don't want to run out of IDs and face a painful migration.

Exact Decimals (money, finance):

DECIMAL(precision, scale)

• precision = total number of digits.
• scale = digits after the decimal point.

Example: DECIMAL(10,2) = up to 10 digits total, 2 after the decimal (max value 99,999,999.99).

Always use DECIMAL or NUMERIC for monetary values. Never use floating‑point types for money. Floating-point rounding errors are catastrophic.

Floating‑Point (Approximate)

Never use these for money or any calculation where exactness is required.

Temporal Data Types

Golden Rule: Always use TIMESTAMPTZ for any timestamp that represents a real‑world moment. It stores UTC internally and converts to the client's time zone on read. This prevents timezone bugs.

-- Example
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_type TEXT,
    occurred_at TIMESTAMPTZ DEFAULT NOW()
);

PostgreSQL Extras

• UUID – Universally unique identifier. Great for distributed systems and public IDs.

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT UNIQUE NOT NULL
);

• Boolean – Simple TRUE/FALSE. Use DEFAULT TRUE or FALSE.

is_active BOOLEAN DEFAULT TRUE;

• JSONB – Binary JSON, indexed, supports querying. Perfect for semi‑structured data.

CREATE TABLE events (
    payload JSONB
);
 
INSERT INTO events (payload) VALUES ('{"user_id": 42, "action": "login"}');
 
SELECT * FROM events WHERE payload @> '{"user_id": 42}';
 
-- Index for fast JSONB queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

• Arrays – Store lists of values.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);
 
INSERT INTO posts (tags) VALUES (ARRAY['sql', 'postgres', 'tutorial']);
 
SELECT * FROM posts WHERE tags @> ARRAY['sql'];

Indexes: Making Things Fast

An index is a data structure (like a B‑tree, hash, or GiST) that improves the speed of data retrieval operations at the cost of additional writes and storage.

Mental Model: Think of a book's index. Without it, to find a topic, you'd read every page. With it, you jump directly to the right pages. But the index takes up space and must be updated when the book is revised.

The Cost

• Read speed: Faster.
• Write speed: Slower (index must be updated).
• Storage: Extra disk space.

Creating Indexes

-- Basic B‑tree (default, supports equality and range queries)
CREATE INDEX idx_students_email ON students(email);
 
-- Composite index (order of columns matters)
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date DESC);
 
-- Partial index (only index rows meeting a condition – smaller and faster)
CREATE INDEX idx_active_students ON students(email) WHERE enrolled = TRUE;
 
-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_products_sku ON products(sku);
 
-- Expression index (e.g., case‑insensitive search)
CREATE INDEX idx_email_lower ON students(LOWER(email));
 
-- Covering index (includes extra columns so table lookup is avoided)
CREATE INDEX idx_orders_covering ON orders(customer_id, status)
INCLUDE (total, created_at);

When to Use Indexes

Good candidates:

• Columns used in WHERE, JOIN, ORDER BY, GROUP BY clauses.
• Foreign key columns (to speed up joins).
• Columns with high cardinality (many distinct values) – e.g., email, user_id.
• Unique constraints (they automatically create an index).

Bad candidates:

• Columns with low cardinality (e.g., gender, status with only a few values) – unless you use a partial index.
• Columns that are frequently updated (index maintenance overhead).
• Very small tables – scanning is faster.

Composite Index Order

The order of columns in a composite index is crucial.

-- Index on (a, b)
CREATE INDEX idx_ab ON table_name(a, b);
 
-- This index can efficiently support:
-- WHERE a = 1
-- WHERE a = 1 AND b = 2
-- ORDER BY a, b
-- ... but NOT efficiently support:
-- WHERE b = 2 alone

Rule: Put the most selective column first (the one that filters the most rows). Think of it as a phone book sorted by (last_name, first_name) – you can find by last name, but not by first name alone.

Partial Indexes

Index only a subset of rows. This reduces index size and improves performance for queries that always include that condition.

-- Index only active users for login lookups
CREATE INDEX idx_active_users_email ON users(email) WHERE is_active = TRUE;
 
-- Now queries like:
SELECT * FROM users WHERE is_active = TRUE AND email = 'alice@example.com';
-- will use this small index.

Covering Indexes (Index‑Only Scans)

If an index contains all the columns a query needs, the database can answer the query entirely from the index without touching the table. This is extremely fast.

CREATE INDEX idx_users_covering ON users(email, first_name, last_name) INCLUDE (phone);
 
-- This query can be answered from the index:
SELECT email, first_name, last_name FROM users WHERE email LIKE 'a%';

Monitoring Index Usage

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Look for Seq Scan – it means a full table scan. If the table is large, add an index.

When NOT to index:

• Small tables (< 1,000 rows; sequential scan is faster).
• Columns with very low cardinality (e.g., boolean is_active where 50% are true).
• Tables with heavy write load and rare reads (indexes slow down writes).

Transactions & ACID: The Foundation of Reliability

A transaction is a group of SQL operations that execute as a single unit. Either all succeed (commit), or all are undone (rollback). This is essential for maintaining data consistency.

The Bank Transfer Example

BEGIN;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 
-- If both succeed:
COMMIT;
 
-- If something fails (e.g., insufficient funds):
ROLLBACK;  -- both updates are undone

ACID Properties

Isolation Levels

Isolation determines how much a transaction can see uncommitted changes from other transactions.

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- Levels (weakest to strongest):
-- READ UNCOMMITTED: Can read uncommitted data (dirty reads) — PostgreSQL doesn't allow
-- READ COMMITTED: Default. Sees committed data only. Non-repeatable reads possible.
-- REPEATABLE READ: Same query returns same results within transaction.
-- SERIALIZABLE: Complete isolation. Acts as if transactions ran one at a time.
 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Locking

Locking is how the database enforces isolation. This is often used in web applications to avoid long‑held locks.

-- Pessimistic locking (for high contention)
BEGIN;
SELECT * FROM inventory WHERE product_id = 5 FOR UPDATE;
-- No one else can modify this row until commit/rollback
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 5;
COMMIT;
 
-- Optimistic locking (for low contention, better performance)
-- Add version column, check on update
UPDATE products
SET name = 'New Name', version = version + 1
WHERE product_id = 5 AND version = 3;
-- If version changed, update fails (0 rows affected)

Deadlocks

A deadlock occurs when two transactions wait on each other.

Prevention:

• Always lock resources in the same order.
• Keep transactions short.
• Use SELECT ... FOR UPDATE NOWAIT to fail fast instead of waiting.

Savepoints

Within a transaction, you can set savepoints to rollback to a specific point without aborting the entire transaction.

BEGIN;
INSERT INTO orders ...;
 
SAVEPOINT before_payment;
 
-- Attempt payment
UPDATE payments SET status = 'failed' ...;
-- Payment fails, rollback to savepoint
ROLLBACK TO SAVEPOINT before_payment;
 
-- Then continue with order cancellation
UPDATE orders SET status = 'cancelled' ...;
COMMIT;

PostgreSQL Power Features

PostgreSQL has advanced features that go beyond standard SQL. Here are the most useful.

JSONB — Flexible Schema Within Rigid Schema

CREATE TABLE events (
    event_id BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Insert
INSERT INTO events (event_type, payload)
VALUES ('user_signup', '{"user_id": 42, "referrer": "google"}');
 
-- Query
SELECT * FROM events WHERE payload @> '{"referrer": "google"}';
SELECT payload->>'user_id' AS user_id FROM events;
 
-- Index for JSONB queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

Arrays

Storing arrays can be useful for tags, permissions, etc

CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags TEXT[] DEFAULT '{}'
);
 
INSERT INTO posts (title, tags)
VALUES ('PostgreSQL Guide', ARRAY['database', 'sql', 'tutorial']);
 
SELECT * FROM posts WHERE tags @> ARRAY['sql'];

Full-Text Search

Built‑in search engine for natural language

-- Add a search vector column
ALTER TABLE products ADD COLUMN search_vector tsvector;
 
-- Populate it (you can also use a generated column or trigger)
UPDATE products SET search_vector =
    setweight(to_tsvector('english', name), 'A') ||
    setweight(to_tsvector('english', description), 'B');
 
-- Index it
CREATE INDEX idx_products_search ON products USING GIN (search_vector);
 
-- Query
SELECT * FROM products
WHERE search_vector @@ plainto_tsquery('english', 'wireless headphones');

Triggers — Automated Actions

Triggers execute functions on certain events (INSERT, UPDATE, DELETE) automatically.

Example: Automatically update updated_at column.

-- Function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Trigger
CREATE TRIGGER trigger_update_products
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Use cases: Audit logs, timestamp updates, enforcing business rules, maintaining materialized views

Views and Materialized Views

Regular View – a saved query that behaves like a table but is not stored physically. It runs every time you query it.

CREATE VIEW active_users AS
SELECT * FROM users WHERE is_active = TRUE;
 
-- Use it like a table
SELECT * FROM active_users;

Materialized View – stores the query result physically. Must be refreshed.

CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT DATE(created_at) AS day, SUM(total) AS revenue
FROM orders
GROUP BY DATE(created_at);
 
-- Refresh (concurrently to avoid locking)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

Use materialized views for heavy, infrequently changing reports.

Performance & Optimization

EXPLAIN and EXPLAIN ANALYZE

The most important tool for query optimisation.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

Look for:

• Seq Scan – full table scan. Usually bad for large tables.
• Index Scan – using an index. Good.
• Bitmap Heap Scan – a combination of index and heap, acceptable.
• Nested Loop Join – may be okay for small datasets, but can be slow for large ones.
• Hash Join or Merge Join – often better for large datasets.
• Buffers: shared read – the number of 8KB blocks read from disk. Lower is better.

Connection Pooling

Opening a new database connection for every web request is expensive. Use a connection pool.

• PgBouncer – popular connection pooler for PostgreSQL.
• Application‑level pooling – most ORMs and drivers support connection pooling.

Best practice: Keep the pool size around 20–50 connections per application instance. PostgreSQL can handle hundreds, but thousands will degrade performance.

Partitioning

When a table becomes very large (>100 GB or >100 million rows), partitioning can help.

Example: Range partition by date.

CREATE TABLE orders (
    order_id BIGSERIAL,
    order_date DATE NOT NULL,
    ...
) PARTITION BY RANGE (order_date);
 
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
 
CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

Partitioning improves query performance for range scans and makes archiving/deletion easier (just drop a partition).

Caching Strategy

Schema Evolution & Migrations

Database schema changes should be treated like code: versioned, reviewed, and deployed systematically.

What Is a Migration?

A migration is a script that transitions the database schema from one state to another, typically with an up (apply) and down (rollback) step.

Example using a migration tool (like node-pg-migrate/sequelize for JavaScript, golang-migrate/goose for Go):

-- migration_20250115_add_user_phone.sql (up)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 
-- migration_20250115_add_user_phone_down.sql (rollback)
ALTER TABLE users DROP COLUMN phone;

Migration Workflow

1. Write the migration – Add columns, create tables, etc.
2. Test locally – Run on a development database.
3. Code review – Ensure no accidental destructive changes.
4. Deploy – Apply in order (usually during deployment, before the app starts using the new schema).
5. Rollback plan – Have a down migration ready if something goes wrong.

Best Practices

• Idempotency: Write migrations so they can be safely run multiple times (use IF NOT EXISTS).
• Avoid DROP in production – especially tables and columns. Use a phased approach (see zero‑downtime changes).
• Never rename a column in one step – Add new, dual-write, migrate, drop old.
• Keep migrations small – one change per migration file.
• Use a migration tool – don't manually apply scripts.

Production Patterns

1. Soft Deletes

In many systems, you should never permanently delete data. Instead, mark it as deleted.

The Problem with Hard Deletes

• Data loss: Accidentally deleting important records is irreversible.
• Audit requirements: Some regulations require you to keep records for years.
• Referential integrity: Deleting a parent row might cascade delete child rows, which is not always desired.

Soft Delete Pattern

Add a deleted_at column (nullable TIMESTAMPTZ). When a record is "deleted", set deleted_at = NOW().

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
 
-- "Delete" a user
UPDATE users SET deleted_at = NOW() WHERE id = 42;
 
-- Query active users
SELECT * FROM users WHERE deleted_at IS NULL;

When to Hard Delete

• Data that is useless and never referenced (e.g., temporary logs after a year).
• When privacy regulations require actual deletion (GDPR right to be forgotten) – but even then, you might need to anonymise.

2. Audit Logging: Who Did What and When

Tracking changes to your data is crucial for debugging, compliance, and security. For complete change history, create an audit log table that stores before/after JSON snapshots

CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT,                -- which table was changed? e.g., 'users'
    record_id BIGINT,               -- which row? e.g., user id 42
    action TEXT,                    -- INSERT, UPDATE, or DELETE
    old_data JSONB,                 -- the row as it was before (NULL for INSERT)
    new_data JSONB,                 -- the row as it is after (NULL for DELETE)
    changed_by BIGINT,              -- who did it? (user id from users table)
    changed_at TIMESTAMPTZ DEFAULT NOW()  -- when it happened
);

• Start with the same database - for perfect consistency and zero extra infrastructure.
• Move to a separate database or service - ****only if audit writes start slowing down your main database.

3. Idempotency

APIs retry automatically. Networks fail and resend requests. Users double‑click submit buttons. If you're not careful, a single "Place Order" action can create two orders, two payments, or send two emails.

Idempotency means that performing the same operation multiple times has the same effect as performing it once. If a client sends the same request twice, the server processes it only the first time and returns the cached result for subsequent attempts.

-- Idempotency key on orders
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    idempotency_key VARCHAR(100) UNIQUE,
    ...
);
 
-- Upsert (insert or do nothing if already exists)
INSERT INTO payments (order_id, amount, idempotency_key, status)
VALUES (1, 100.00, 'key-123', 'pending')
ON CONFLICT (idempotency_key) DO NOTHING;

The N+1 Query Problem – A Silent Killer

What Is It?

Imagine you need to fetch 100 users and their orders. Here's the wrong way:

// ❌ BAD: This makes 1 + 100 = 101 queries!
const users = await User.findAll(); // Query 1: SELECT * FROM users
 
for (const user of users) {
  const orders = await Order.findAll({
    // Query N: one per user
    where: { userId: user.id },
  });
  console.log(user.name, orders.length);
}

• First query: fetch all 100 users (1 query).
• Then for each user, fetch their orders (100 queries).
• Total: 101 queries just to get users with their orders!

Why Is It Bad?

• Slow – each query is a round-trip to the database (network latency adds up).
• Wasteful – 101 small queries instead of one efficient query.
• Doesn't scale – with 1000 users, you make 1001 queries. With 10,000, you make 10,001 queries. Performance degrades linearly.

The Solution: Use JOIN

Get everything in ONE query:

SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

In JavaScript (using Sequelize or Prisma):

// ✅ GOOD: Just 1 query with JOIN
const users = await User.findAll({
  include: [Order], // Eager load orders in the same query
});
 
// Or with Prisma
const users = await prisma.user.findMany({
  include: { orders: true },
});

Prevention Strategies

1. Eager loading – Always preload relationships when you know you'll need them.
2. Use JOINs – Fetch related data in a single query.
3. Cache – Store frequently accessed data in Redis to avoid repeated queries.
4. Denormalize – Store counts (like order_count) directly on the parent table if you only need numbers.

Connection Pooling – Managing Database Access

Every time your application opens a database connection, it incurs overhead: TCP handshake, authentication, memory allocation. Opening a new connection for each request is inefficient.

Connection Pooling Basics

A connection pool maintains a set of open connections that can be reused. Your application borrows a connection from the pool, uses it, and returns it.

Benefits:

• Reduced latency (no connection setup per request).
• Controlled concurrency (limit total connections to avoid overloading the database).
• Efficient resource usage.

Pool Sizing

The optimal pool size depends on your database's max connections and your application's concurrency.

• Start with a pool size of 10–20 per application instance.
• Monitor pg_stat_activity to see active connections; increase if you have idle connections.
• Total connections across all pools should not exceed the database's max_connections (typically 100–300).

PgBouncer – The External Pooler

PgBouncer is a lightweight connection pooler that sits between your app and PostgreSQL. It's especially useful when you have many application instances (e.g., 50 microservices) each with a pool.

• Session pooling: One connection per client session (less efficient).
• Transaction pooling: A connection is assigned for the duration of a transaction, then released. This is the most efficient for web workloads.
• Statement pooling: Even more aggressive (rarely used).

Your application connects to PgBouncer on port 6432 instead of directly to PostgreSQL.