# 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: ```plaintext 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. ```sql 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 `UPDATE`s, 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. ```bash 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: ```bash 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: ```yaml 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):** ```bash 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):** ```bash 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. ```bash psql -h localhost -p 5432 -U postgres -d myapp ``` Or as a connection string (same format you'd put in a `.env`): ```bash 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: ```bash 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: ```bash 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: ```bash 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). ```bash pg_lsclusters ``` ```plaintext Ver Cluster Port Status Owner Data directory Log file 16 main 5432 online postgres /var/lib/postgresql/16/main ... ``` ```bash 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 ```sql 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 ```sql 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 ```sql 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 ```sql 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 ```sql 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: ```sql -- 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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: ```sql 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): ```bash 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: ```sql 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_`): ```conf 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: ```bash docker logs my-postgres -f ``` ### Statistics Postgres tracks internal activity in system views, all under the `pg_stat_*` namespace: ```sql -- 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: ```sql 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: ```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`): ```sql 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 ```yaml 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: ```sql 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.