PostgreSQL from Scratch: A Full-Stack Developer's Guide

Most tutorials teach PostgreSQL by dropping you straight into SQL syntax — SELECT, INSERT, JOIN — without explaining what a relational database actually is, how the engine works underneath, or how to run and configure it in the real world.

This post takes the opposite approach. It's structured as a full learning path in five parts:

1. What relational databases actually are (and how they compare to NoSQL)

2. Core RDBMS concepts — the practical vocabulary, the formal relational model, and engine internals like ACID, MVCC, and WAL

3. Installation and setup — Docker, package managers, cloud, and service management

4. SQL — DDL, DML, and advanced topics

5. Configuration — logging, statistics, and extensions

By the end, you'll have a working mental model of what Postgres is doing under the hood, know how to run it locally and in production, and have a foundation to build real applications on.

---

Part 1: What Is a Relational Database?

A relational database organizes data into tables (relations) — rows and columns — where relationships between tables are defined through keys, not by nesting data inside each other.

Instead of stuffing a user's orders inside their user record, you'd have:

users              orders
--------           --------
id (PK)            id (PK)
name               user_id (FK) -> users.id
email              amount
                   created_at

The user_id in orders is a foreign key pointing to users.id — this is how relationships are modeled. You then use JOINs to pull related data back together.

The theory behind this is called the relational model (Edgar Codd, 1970), built around set theory — tables are treated like mathematical relations, and SQL is the language for querying them.

The Core Concept: Normalization

Relational databases are designed around normalization — splitting data to avoid duplication. If you store a customer's address in every single order row, and they move, you now have to update N rows. Normalize it into a customers table, and you update one row.

This is the single biggest conceptual shift coming from JavaScript, where you're used to nesting JSON freely ({ user: { orders: [...] } }). In Postgres, that nesting gets flattened into separate tables connected by keys.

Benefits of Relational Databases

• ACID guarantees — Atomicity, Consistency, Isolation, Durability. Transactions either fully happen or don't happen at all. Critical for payments, inventory, bookings.

• Data integrity via constraints — foreign keys, unique constraints, NOT NULL, CHECK constraints. The database itself refuses to store bad data, rather than relying on application code to enforce it.

• No data duplication through normalization — one source of truth per fact.

• Powerful querying — SQL's JOINs, aggregations, and window functions let you ask complex questions across relationships without writing app-level logic.

• Mature tooling — decades of query planners, indexing strategies, replication, and backup tools.

Limitations

• Schema rigidity — you define the shape of your data upfront. Changing it later means migrations. This is friction if your data shape is unpredictable.

• Horizontal scaling is harder — relational DBs traditionally scale up more easily than out, because JOINs across distributed nodes are expensive. This has improved (Postgres has read replicas, sharding extensions like Citus) but it's not as natural as NoSQL's distributed-by-design approach.

• JOIN overhead at scale — deeply relational queries with many JOINs can get slow on very large datasets if not indexed well.

• Overkill for simple key-value needs — if you just need to store/retrieve blobs by ID with no relationships, a relational DB adds unnecessary ceremony.

SQL vs NoSQL vs Postgres — Untangling the Terms

These three terms sit at different levels, which is why the comparison is confusing:

• SQL = the query language, not a database. It's the standard language relational databases speak.

• NoSQL = an umbrella term for databases that don't use the relational model — document stores (MongoDB), key-value stores (Redis, DynamoDB), wide-column stores (Cassandra), graph databases (Neo4j). "NoSQL" isn't one thing; it's "not-only-SQL."

• Postgres = one specific, open-source implementation of a relational database that uses SQL.

So the real comparison is:

	Relational (Postgres, MySQL)	Document (MongoDB)	Key-Value (Redis, DynamoDB)
Data shape	Tables, rows, fixed-ish schema	JSON-like documents, flexible schema	Simple key → value
Relationships	First-class (JOINs, FKs)	Manual (embed or reference)	None
Consistency	Strong (ACID)	Often eventual (modern Mongo supports transactions)	Usually eventual
Best for	Structured data with relationships	Fast-changing schemas, nested data	Caching, sessions, leaderboards
Scaling	Vertical-first, horizontal via extra tooling	Horizontal by design (sharding built in)	Horizontal by design

In practice for a Next.js developer: reach for Postgres when your data has clear relationships (users → posts → comments), you need data integrity, or you'll eventually run analytical queries. Reach for MongoDB when your data is naturally document-shaped and the schema keeps evolving. Reach for Redis for caching or sessions on top of your main DB — not instead of it.

Postgres vs Other RDBMS

• Postgres vs MySQL — historically MySQL was "faster but simpler," Postgres was "slower but more feature-rich." That gap has mostly closed. Postgres now generally wins on richer data types (JSONB, arrays, ranges, geometric types), stricter standards compliance, extensibility (custom types, extensions like PostGIS, pgvector), and better handling of complex queries. For a modern app, Postgres is the safer default.

• Postgres vs SQLite — SQLite is a single-file, serverless embedded database. Great for local dev, mobile apps, small tools, or edge cases with low concurrency. Not meant for a production app with concurrent writers.

• Postgres vs SQL Server / Oracle — commercial, licensed, often enterprise-locked-in. Postgres gives you 90%+ of the enterprise feature set for free/open-source, which is why it's the default choice for most new projects.

Why Postgres Specifically Stands Out Today

• JSONB — lets you store schema-less JSON inside a relational table and query/index it efficiently. Best-of-both-worlds: relational structure where you want it, document flexibility where you need it.

• Extensions — pgvector for AI embeddings, PostGIS for geospatial, pg_cron for scheduled jobs. Postgres becomes a multi-tool.

• Ecosystem fit for modern stacks — Neon, Supabase, Vercel Postgres, Railway give you serverless/managed Postgres that plugs cleanly into Next.js, and ORMs like Drizzle and Prisma have first-class Postgres support.

---

Part 2: Core RDBMS Concepts

Let me separate the practical everyday terms (table, row, column) from the formal relational model terms (relation, tuple, attribute). Understanding both will make SQL error messages and academic docs make a lot more sense.

Layer 1: The Practical Building Blocks

• Database — a self-contained collection of related data (e.g., myapp_production). A single Postgres server can host multiple databases.

• Schema — a namespace inside a database that groups tables together (e.g., public, auth, analytics). Think of it like a folder for tables. Most apps just use the default public schema until they need organizing.

• Table — the actual structure holding data: a fixed set of columns, and any number of rows.

• Row (a.k.a. record) — one entry in a table. One user, one order, one comment.

• Column (a.k.a. field) — one piece of data in each row, with a defined data type.

• Data Types — the type constraint on a column: INTEGER, TEXT, BOOLEAN, TIMESTAMP, NUMERIC, JSONB, UUID, arrays, etc. Postgres is strict — you can't shove a string into an integer column like you might in JS.

• Query — a request written in SQL to read or modify data.

The containment hierarchy is:

Server → Database → Schema → Table → Rows & Columns

Layer 2: The Formal Relational Model

Here's where the academic terms map onto what you just learned. Codd's original relational model (1970) used stricter, math-flavored language:

Relational Model Term	Practical Equivalent	Meaning
Relation	Table	A set of tuples sharing the same attributes
Tuple	Row	One ordered set of values — one record
Attribute	Column	A named property of the relation
Domain	Data type / valid value set	The set of all legal values an attribute can hold
Degree	Number of columns	How many attributes a relation has
Cardinality	Number of rows	How many tuples a relation currently holds

A few terms worth going deeper on:

• Domain is subtler than "data type." A column's type might be INTEGER, but its domain could be restricted further (e.g., "must be between 1–5" for a rating). In Postgres you enforce this via CHECK constraints, or literally with CREATE DOMAIN.

• Constraints — rules the database enforces so bad data can never be stored, regardless of what the app code does:

  • PRIMARY KEY — uniquely identifies each tuple

  • FOREIGN KEY — enforces that a value must exist in another relation (referential integrity)

  • UNIQUE — no duplicate values in a column

  • NOT NULL — column can't be empty

  • CHECK — custom rule (e.g., price > 0)

• NULL — represents "unknown" or "absent," not zero or empty string. NULL = NULL evaluates to NULL (not true), because you're asking "is one unknown equal to another unknown?" — unanswerable. That's why SQL uses IS NULL / IS NOT NULL instead of =.

Layer 3: High-Level Database Engine Concepts

This is where relational databases go from "spreadsheet with rules" to something genuinely sophisticated.

Transactions

A transaction is a group of one or more operations executed as a single logical unit — either all of them succeed, or none do.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If the server crashes between those two UPDATEs, you don't want money to vanish. The transaction wrapper guarantees either both updates happen, or (on ROLLBACK or crash) neither does.

ACID — the guarantees a transaction gives you

• Atomicity — the transaction is all-or-nothing (the money transfer example above).

• Consistency — the database moves from one valid state to another valid state — all constraints still hold after the transaction commits.

• Isolation — concurrent transactions don't see each other's half-finished work. If two people are reading/writing at the same time, each should behave as if it's running alone (to varying degrees, controlled by isolation levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE).

• Durability — once a transaction commits, it's permanent — even if the server crashes 1 millisecond later. This is where the Write-Ahead Log comes in.

Write-Ahead Log (WAL)

This is the mechanism that makes Durability possible. Before Postgres modifies actual data on disk, it first writes a record of the intended change to a sequential log file — the WAL.

Why this order?

• Writing to the end of a log file is fast (sequential disk I/O).

• Writing to random pages of a big table on disk is slower (random I/O).

• So Postgres writes the change to WAL first (fast, durable), acknowledges the commit to the client, and applies the actual change to the table's data pages later, in the background (checkpointing).

If the server crashes right after a commit, Postgres replays the WAL on restart to reconstruct any changes that hadn't been flushed to the actual table files yet. The WAL is the safety net that guarantees durability without needing every single row-write to hit disk synchronously.

WAL is also what powers replication — a replica server continuously streams and replays the primary's WAL to stay in sync.

MVCC (Multi-Version Concurrency Control)

This is how Postgres achieves Isolation without just locking everything (which would kill concurrency).

Instead of one copy of a row that everyone fights over, Postgres keeps multiple versions of a row as it's updated. When you UPDATE a row, Postgres doesn't overwrite it in place — it writes a new version and marks the old one as expired.

• Each transaction sees a consistent snapshot of the data as of when it started — readers never block writers, and writers never block readers, because they're literally looking at different row versions.

• Old row versions eventually get cleaned up by a background process called VACUUM (this is why you'll hear Postgres DBAs talk about vacuuming — it reclaims space from dead row versions).

Postgres is closer to an append-mostly system with background cleanup than a naive "mutate in place" database.

Query Processing

When you run a SELECT, Postgres doesn't execute your SQL literally as written. It goes through stages:

1. Parser — checks SQL syntax, turns it into a parse tree.

2. Rewriter — applies rules (e.g., expanding views into their underlying queries).

3. Planner/Optimizer — considers multiple possible ways to execute your query (should it scan the whole table, or use an index? Should it join A to B first, or B to A first?) and picks the plan it estimates will be cheapest, based on statistics about your data.

4. Executor — actually runs the chosen plan and returns rows.

This is why EXPLAIN ANALYZE is one of the most important tools you'll learn — it shows you exactly which plan the optimizer chose and how expensive each step actually was.

---

Part 3: Installation and Setup

Option A: Docker (recommended for learning/dev)

The cleanest way to start — no system-level install, no version conflicts, fully disposable.

docker run --name my-postgres \
  -e POSTGRES_PASSWORD=devpassword \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  -d postgres:16

• -e POSTGRES_PASSWORD — sets the superuser (postgres) password

• -e POSTGRES_DB — creates a database on first boot

• -p 5432:5432 — maps container port to your host, so local tools can connect to localhost:5432

• -d — detached, runs in background

For persistence across container restarts, add a volume:

docker run --name my-postgres \
  -e POSTGRES_PASSWORD=devpassword \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:16

For a real project, a docker-compose.yml is nicer since you version-control the config alongside your app:

services:
  db:
    image: postgres:16
    restart: always
    environment:
      POSTGRES_PASSWORD: devpassword
      POSTGRES_DB: myapp
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata:

Then docker compose up -d.

Option B: Package Managers

macOS (Homebrew):

brew install postgresql@16
brew services start postgresql@16

brew services is Homebrew's own service wrapper (runs on launchd — macOS doesn't have systemd).

Ubuntu/Debian (apt):

sudo apt update
sudo apt install postgresql postgresql-contrib

This installs Postgres and Debian/Ubuntu's own cluster-management layer (pg_ctlcluster, pg_lsclusters) on top of systemd.

Windows: either the official installer from postgresql.org, winget install PostgreSQL.PostgreSQL, or just run it in Docker Desktop and skip native install entirely. Docker is usually the least-friction path.

Connecting with psql

psql is Postgres's official command-line client.

psql -h localhost -p 5432 -U postgres -d myapp

Or as a connection string (same format you'd put in a .env):

psql "postgresql://postgres:devpassword@localhost:5432/myapp"

Once inside, essential psql meta-commands (prefixed with \):

Command	What it does
\l	list databases
\c dbname	connect to a different database
\dt	list tables in current schema
\d tablename	describe a table's columns/constraints
\dn	list schemas
\du	list users/roles
\x	toggle expanded output (great for wide rows)
\q	quit

If you're running Docker, you don't even need psql installed locally:

docker exec -it my-postgres psql -U postgres -d myapp

Cloud Deployment

Two very different approaches:

Managed Postgres (recommended for a Next.js app) — you don't manage the server at all:

• Neon — serverless Postgres, git-like branching, generous free tier. Popular with Next.js/Vercel projects.

• Supabase — Postgres + auth + storage + realtime bundled in.

• AWS RDS for Postgres — managed instance with automated backups, Multi-AZ failover; you still pick instance size/storage.

• Vercel Postgres / Railway — simple, tightly integrated with deploy pipelines.

For these, "installation" is: create the instance via dashboard/CLI, copy the connection string into your .env, done.

Self-managed on a VM (EC2, DigitalOcean droplet) — you install Postgres directly on the VM, and now you own everything below the SQL layer: OS patching, backups, replication, the systemd service, firewall rules.

Managing the Running Service — pg_ctl, systemd, and Debian's cluster tools

This is genuinely a layered mess across distros. Here's how the pieces relate:

pg_ctl is Postgres's own low-level binary for starting/stopping/reloading a single Postgres server process. It talks directly to a PGDATA directory:

pg_ctl -D /usr/local/var/postgres start
pg_ctl -D /usr/local/var/postgres stop
pg_ctl -D /usr/local/var/postgres restart
pg_ctl -D /usr/local/var/postgres reload    # re-read config without restarting
pg_ctl -D /usr/local/var/postgres status

Works on any OS, but nothing manages it for you at boot or restarts it after a crash.

systemd (Linux) wraps that in a proper service manager:

sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql
sudo systemctl status postgresql
sudo systemctl enable postgresql   # start automatically on boot
journalctl -u postgresql           # view logs

Under the hood, the systemd unit file is just calling pg_ctl for you.

Debian/Ubuntu's cluster layer (pg_ctlcluster, pg_lsclusters) is an extra abstraction on top, because Debian-based distros support running multiple Postgres versions side-by-side on one machine (e.g., Postgres 14 and 16 simultaneously, each isolated).

pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 online postgres /var/lib/postgresql/16/main ...

sudo pg_ctlcluster 16 main start
sudo pg_ctlcluster 16 main stop
sudo pg_ctlcluster 16 main restart

So on Ubuntu, the actual chain is: systemctl start postgresql → wrapper service → calls pg_ctlcluster for each configured cluster → calls pg_ctl against that cluster's specific data directory and port. Day-to-day you'll mostly just use systemctl.

On macOS with Homebrew, no systemd or cluster layer — brew services is the top-level wrapper, calling pg_ctl underneath.

Practical recommendation: use Docker locally for day-to-day dev, and Neon or RDS for anything deployed. That sidesteps systemd/pg_ctlcluster complexity almost entirely — you'd only need that layer if you're self-hosting Postgres on a VM yourself.

---

Part 4: SQL

SQL splits into DDL (Data Definition Language — the shape of your database) and DML (Data Manipulation Language — the data itself), plus advanced topics.

DDL Queries

For Schemas

CREATE SCHEMA billing;
CREATE SCHEMA IF NOT EXISTS analytics;
DROP SCHEMA billing CASCADE;  -- CASCADE also drops everything inside it

You'd reference a table in a non-default schema as billing.invoices instead of just invoices (which implicitly means public.invoices).

For Tables

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

DROP TABLE users;               -- fails if other tables reference it via FK
DROP TABLE users CASCADE;       -- drops dependent objects too
TRUNCATE TABLE users;           -- wipes all rows, keeps table structure (faster than DELETE)

Data Types

The common ones you'll reach for:

Category	Types
Numbers	INTEGER, BIGINT, NUMERIC(precision, scale) (exact decimals — use for money), REAL/DOUBLE PRECISION (floating point — avoid for money)
Text	TEXT (unlimited), VARCHAR(n) (capped length), CHAR(n) (fixed length, rare)
Date/time	DATE, TIME, TIMESTAMP, TIMESTAMPTZ (timestamp with timezone — almost always what you want)
Boolean	BOOLEAN
Identifiers	UUID, SERIAL/BIGSERIAL (auto-incrementing integers)
Flexible	JSONB (binary JSON, indexable — the Postgres superpower), ARRAY (e.g. TEXT[])

Rule of thumb for a JS dev: use TIMESTAMPTZ over TIMESTAMP almost always (JS Date objects are UTC under the hood, and timezone bugs are miserable), and NUMERIC over REAL for anything involving money.

DML Queries

Querying Data

SELECT id, name, email FROM users;
SELECT * FROM users LIMIT 10 OFFSET 20;   -- pagination
SELECT DISTINCT country FROM users;
SELECT name FROM users ORDER BY created_at DESC;

Filtering Data

SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name LIKE 'A%';          -- pattern match
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE country IN ('NP', 'IN', 'US');
SELECT * FROM users WHERE deleted_at IS NULL;       -- never `= NULL`
SELECT * FROM users WHERE age > 18 AND country = 'NP';

Modifying Data

INSERT INTO users (name, email) VALUES ('Aakib', 'a@x.com');
UPDATE users SET age = 26 WHERE id = 1;
DELETE FROM users WHERE id = 1;

-- "upsert" pattern
INSERT INTO users (name, email) VALUES ('Aakib', 'a@x.com')
  ON CONFLICT (email) DO NOTHING;

Joining Tables

Where the relational model actually pays off:

-- Inner join: only rows that match in both tables
SELECT users.name, orders.amount
FROM users
JOIN orders ON orders.user_id = users.id;

-- Left join: all users, even those with no orders (NULLs for order columns)
SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

Join types worth knowing:

• INNER JOIN — matches only

• LEFT JOIN — all of left + matches

• RIGHT JOIN — all of right + matches

• FULL JOIN — all of both, matched where possible

Import / Export Using COPY

Postgres's bulk-load/unload command — much faster than row-by-row INSERT for large datasets:

COPY users TO '/tmp/users.csv' WITH CSV HEADER;
COPY users FROM '/tmp/users.csv' WITH CSV HEADER;

There's also \copy in psql itself, which runs on your local machine instead of the server — useful when the server's filesystem isn't accessible (e.g., managed cloud instance).

Advanced Topics

Transactions

BEGIN / COMMIT / ROLLBACK wrapping multiple statements into one atomic unit — already covered in Part 2 conceptually.

CTE (Common Table Expressions)

A named, temporary result set you can reference within a query — makes complex queries readable by breaking them into named steps:

WITH recent_orders AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT users.name, COUNT(*)
FROM users JOIN recent_orders ON recent_orders.user_id = users.id
GROUP BY users.name;

Subqueries

A query nested inside another query:

SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

CTEs are often preferred today for readability, but subqueries are still common inline in WHERE/SELECT.

Lateral Join

A join where the right-hand side can reference columns from the left-hand side — think "for each row on the left, run this correlated query." Useful for "top N per group" queries:

SELECT users.name, recent.amount
FROM users
CROSS JOIN LATERAL (
  SELECT amount FROM orders
  WHERE orders.user_id = users.id
  ORDER BY created_at DESC LIMIT 1
) AS recent;

Regular joins can't reference the left table's columns inside a subquery like that — LATERAL is what unlocks it.

Grouping

Aggregating rows into summary rows:

SELECT country, COUNT(*), AVG(age)
FROM users
GROUP BY country
HAVING COUNT(*) > 5;   -- filters groups (WHERE filters rows, HAVING filters groups)

Set Operations

Combining results of two queries as sets:

SELECT email FROM users
UNION                    -- combines, removes duplicates
SELECT email FROM newsletter_subscribers;

SELECT email FROM users
INTERSECT                -- only rows in both
SELECT email FROM newsletter_subscribers;

SELECT email FROM users
EXCEPT                   -- rows in first, not in second
SELECT email FROM newsletter_subscribers;

---

Part 5: Configuration, Logging, Statistics, and Extensions

This is the operational side — where you configure Postgres to tell you what it's doing, rather than being a black box.

Configuration Files

Two files matter most:

• postgresql.conf — main server config (logging, memory, connections, statistics)

• pg_hba.conf — host-based authentication (who can connect, from where, with what auth method)

Find them from inside psql:

SHOW config_file;
SHOW hba_file;
SHOW data_directory;

In Docker, you'd typically mount a custom postgresql.conf in, or pass settings via command: overrides in docker-compose.yml.

After editing postgresql.conf, most settings need a reload (not restart):

sudo systemctl reload postgresql
-- or from inside psql:
SELECT pg_reload_conf();

Some settings (like memory allocation, shared_buffers) require a full restart. Check via:

SELECT name, context FROM pg_settings WHERE name = 'shared_buffers';

If context = postmaster, it's restart-only.

Logging

Controlled via postgresql.conf settings (all prefixed log_):

logging_collector = on              # actually write logs to files
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_statement = 'all'               # 'none', 'ddl', 'mod', 'all'
log_min_duration_statement = 200    # log any query slower than 200ms
log_connections = on
log_disconnections = on
log_line_prefix = '%m [%p] %u@%d '  # timestamp, pid, user, database

log_min_duration_statement is the single most useful one — set it to 200 (ms) and Postgres will log every query slower than that, giving you a free slow-query log. log_statement = 'all' logs every query — useful locally but too noisy for production.

Docker log viewing:

docker logs my-postgres -f

Statistics

Postgres tracks internal activity in system views, all under the pg_stat_* namespace:

-- Table-level: sequential scans vs index scans, rows read, dead tuples
SELECT * FROM pg_stat_user_tables;

-- Index usage — are your indexes actually being used?
SELECT * FROM pg_stat_user_indexes;

-- Currently running queries
SELECT * FROM pg_stat_activity;

-- Overall database stats: commits, rollbacks, cache hit ratio
SELECT * FROM pg_stat_database;

pg_stat_activity is what you'd query to find a stuck/long-running query and its PID, so you can SELECT pg_terminate_backend(pid) on it if needed.

The most valuable extension: pg_stat_statements

Tracks aggregated stats per normalized query — you can find your slowest or most-frequently-run queries across your whole app:

CREATE EXTENSION pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

This one needs shared_preload_libraries = 'pg_stat_statements' set in postgresql.conf before you can CREATE EXTENSION it — since it hooks into the query executor at a low level, it has to be loaded at server startup.

Extensions

An extension is a packaged bundle of extra functionality — new types, functions, index types, or background workers — that ships separately from core Postgres but installs cleanly via SQL:

CREATE EXTENSION IF NOT EXISTS extension_name;
DROP EXTENSION extension_name;
\dx                                       -- list installed extensions
SELECT * FROM pg_available_extensions;    -- list what's available

Some extensions are just SQL/functions (install instantly). Others (like pg_stat_statements, pg_cron) need to be loaded at server boot via shared_preload_libraries because they hook into the server process itself.

Extensions most relevant to a full-stack developer

Extension	What it does
uuid-ossp / pgcrypto	UUID generation (gen_random_uuid()) — common for primary keys instead of serial ints
pg_trgm	Trigram-based fuzzy text search — powers fast ILIKE '%term%' and typo-tolerant search
pgvector	Vector similarity search — the one to know for AI/embeddings features (semantic search, RAG)
pg_cron	Schedule SQL jobs to run periodically, inside Postgres itself, cron-syntax — avoids needing external cron for simple periodic tasks
postgis	Full geospatial support — points, polygons, distance queries
pg_stat_statements	Query performance stats (covered above)

Example — UUID primary keys with pgcrypto (modern replacement for uuid-ossp):

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL
);

Note on managed Postgres: you don't get full superuser access to install any extension. Each provider whitelists a specific set. Supabase enables pg_cron; vanilla RDS has its own allow-list; Neon has a slightly different one. Check the provider's docs before assuming an extension is available.

Docker Example — Custom Config + Preloaded Extension

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: devpassword
    command: >
      postgres
      -c shared_preload_libraries=pg_stat_statements
      -c log_min_duration_statement=200
      -c logging_collector=on
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata:

Then on first connect:

CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION pgcrypto;

---

Wrapping Up

That's the full picture — from what a relational database is at a conceptual level, all the way down to how you configure logging, statistics, and extensions on a real instance.

The natural next steps from here:

1. Actually spin up a Docker Postgres instance with the config from Part 5 and run the SQL from Part 4 against it.

2. Learn indexing — B-tree, hash, GIN, GiST indexes and when to use each.

3. Learn EXPLAIN ANALYZE — the tool that shows you what the query planner from Part 2 actually chose, and why.

4. Connect it to a real app — using Drizzle or Prisma from a Next.js API route or Server Action, to close the loop between what you've learned and how you'd use it day-to-day.

The single most valuable habit early on: use psql directly on your dev database and get comfortable with the meta-commands (\d, \dt, \dn, \du). ORMs are useful, but if you can't drop down to raw SQL and understand what the ORM is generating, you'll hit a ceiling fast on debugging and performance work.