cd ~/tutorials/postgres

Postgres Tutorial

Learn PostgreSQL 18 from zero: install it, speak SQL fluently, design tables and constraints, make queries fast with indexes, handle transactions, JSON and full-text search, secure and back up your data, keep it healthy and replicate it, plus everything new in version 18.

19 lessons · 149 min

Start the course →

This course takes you from “what is a relational database?” to running PostgreSQL with confidence in production. All along you build the database behind Orbit, the small notes app from the other courses on this blog: its users, notes and tags, the queries its API runs, the roles it connects with, and the backups that keep it safe.

Every lesson has SQL you can copy, results that come from the same data you created in earlier lessons, three short exercises to check what you learned and answers to the questions people ask most.

Before you start: you need a terminal. If commands like cd, ls and sudo are new to you, take the first lessons of the Linux Tutorial first. Lesson 2 shows how to install PostgreSQL on Linux, macOS and Windows, or run it with Docker.

The examples target PostgreSQL 18, the current major release (version 19 is in release-candidate testing as this course is written, and everything here keeps working on it). Lessons point out when a feature arrived in a recent version, and the last lesson collects what is new in 18 in one place. Keep the Postgres Cheatsheet open in another tab: it has over 400 commands grouped the same way as this course.

ls ./lessons

  1. Introduction to PostgreSQLWhat a relational database is, why PostgreSQL became the default choice for new projects, how its release cycle works, how a Postgres server is organised into clusters, databases, schemas and tables, and the Orbit database you will build in this course.beginner7 min readCompleted
  2. Installing PostgreSQLInstall PostgreSQL 18 on Ubuntu, Debian, Rocky Linux, macOS and Windows, or run it in Docker; check the server is up, open your first psql session, create a role for yourself and find the configuration files and data directory.beginner7 min readCompleted
  3. psql EssentialsConnect with flags, URIs, service files and a password file; read the psql prompt; explore objects with backslash commands; make output readable with expanded mode and timing; run scripts safely; use variables, \gset and \watch; and set up a .psqlrc you will keep for years.beginner7 min readCompleted
  4. Databases, Schemas and TablesCreate the Orbit role and database, understand schemas and the search_path, design the users and notes tables with identity keys and defaults, inspect them from psql and the catalog, change them safely with ALTER TABLE, and follow naming conventions that keep a schema easy to live with.beginner8 min readCompleted
  5. PostgreSQL Data TypesChoose the right type for every column: integers and identity keys, numeric versus floating point for money, text and its length limits, booleans, dates, timestamps with and without time zone, intervals, UUIDs including the new uuidv7, enums and domains, arrays and jsonb, and how casting works.beginner7 min readCompleted
  6. Inserting, Updating and Deleting DataFill Orbit with users and notes, get generated values back with RETURNING, update and delete rows safely inside a transaction, write upserts with ON CONFLICT, understand why identity values have gaps, and bulk-load CSV files with \copy and COPY.beginner8 min readCompleted
  7. Querying Data with SELECTRead data with SELECT: choose columns and expressions, filter with WHERE, combine conditions correctly, understand NULL and three-valued logic, sort and paginate, match patterns with LIKE and ILIKE, build values with CASE and functions, remove duplicates, and learn the order in which a query is evaluated.beginner8 min readCompleted
  8. Joins and RelationshipsModel one-to-many and many-to-many relationships, add tags to Orbit with a join table, and combine tables with inner, left, full and cross joins; find rows without matches using anti-joins, filter with IN and EXISTS subqueries, get the latest row per group with LATERAL, and stack results with UNION.intermediate8 min readCompleted
  9. Aggregates, CTEs and Window FunctionsSummarise data with count, sum, avg, min and max; group with GROUP BY and filter groups with HAVING; count several things at once with FILTER; structure long queries with WITH and walk trees with WITH RECURSIVE; and rank, number, compare and accumulate rows with window functions.intermediate7 min readCompleted
  10. Constraints and Data IntegrityLet the database guarantee that data stays valid: NOT NULL, primary keys, unique and case-insensitive unique rules, check constraints, foreign keys with the right ON DELETE action, exclusion constraints for overlapping ranges, deferrable constraints, and how to add constraints to big tables without downtime.intermediate8 min readCompleted
  11. Indexes and Query PerformanceLoad 200,000 notes, read query plans with EXPLAIN and EXPLAIN ANALYZE, and make slow queries fast with the right index: B-tree, composite, partial, expression, covering, trigram GIN and BRIN; build indexes without blocking writes, understand what indexes cost, and find the ones you do not need.intermediate9 min readCompleted
  12. Transactions and ConcurrencyGroup changes with BEGIN, COMMIT, ROLLBACK and savepoints; understand ACID and how MVCC lets readers and writers work without blocking each other; choose an isolation level; prevent lost updates with row locks; build a job queue with SKIP LOCKED; and deal with deadlocks, lock timeouts and idle transactions.intermediate8 min readCompleted
  13. JSON and Full-Text SearchStore user settings as jsonb, read nested values with -> and ->>, filter with containment, update documents with jsonb_set, turn JSON into rows with JSON_TABLE, index documents with GIN; then add real full-text search to Orbit's notes with tsvector, websearch queries, ranking and a generated, indexed search column.intermediate7 min readCompleted
  14. Views, Functions and TriggersPackage queries as views and materialized views, write SQL and PL/pgSQL functions with the right volatility, raise errors with clear messages, run batch work in procedures, keep an updated_at column current with a trigger, audit changes, push events to the application with LISTEN and NOTIFY, and know when logic belongs in the database.advanced9 min readCompleted
  15. Roles, Privileges and SecurityGive Orbit a least-privilege application role and a read-only reporting role, grant access with GRANT and ALTER DEFAULT PRIVILEGES, isolate each user's notes with row-level security, control who may connect in pg_hba.conf, use SCRAM passwords and TLS, and keep SQL injection and leaked credentials out of your database.advanced9 min readCompleted
  16. Backup and RestoreProtect Orbit's data with logical backups (pg_dump, pg_dumpall, pg_restore) and physical backups (pg_basebackup, WAL archiving, incremental backups), restore to any point in time, choose a strategy from your recovery objectives, and prove that backups work by restoring them regularly.advanced8 min readCompleted
  17. Maintenance and MonitoringKeep Orbit healthy: understand dead rows and what VACUUM and autovacuum do, tune autovacuum for busy tables, avoid transaction ID wraparound, find slow queries with pg_stat_statements, watch sessions, sizes and cache hit rates, log what matters, set sensible memory and checkpoint settings, and pool connections with PgBouncer.advanced8 min readCompleted
  18. Replication, High Availability and UpgradesBuild a streaming replica of Orbit's database, monitor replication lag, use replication slots safely, fail over with pg_promote or Patroni, replicate selected tables with logical replication, and upgrade between minor and major versions with pg_upgrade or a near-zero-downtime logical switchover.advanced8 min readCompleted
  19. What’s New in PostgreSQL 18Every important change in PostgreSQL 18 in one place, tried on Orbit: uuidv7, virtual generated columns, OLD and NEW in RETURNING, temporal keys with WITHOUT OVERLAPS, NOT ENFORCED and NOT VALID constraints, asynchronous I/O, B-tree skip scan, OAuth login, faster upgrades that keep statistics, data checksums by default and new psql features.intermediate8 min readCompleted

↑ ↓ navigate Enter open Esc close