Topolearn

Learn PostgreSQL

Advanced open-source relational database with rich types and extensions

31 lessons · 6 hours · 4 levels · covers PostgreSQL 18

PostgreSQL is a powerful open-source relational database known for correctness, standards compliance and extensibility. Beyond standard SQL it offers jsonb, arrays, ranges, full-text search, many index types and an extension ecosystem (PostGIS, pgvector), and it is the default database for many web back ends. This curriculum covers using it day to day through to operating, tuning and replicating it.

Sign in free to track progressOfficial docs ↗

Before you start

PostgreSQL builds on these; the tree links to the exact lessons you need.

SQLEssentials level

How deep do you want to go?

1. EssentialsRead and write simple code, do the everyday basics9 lessons · 2 hours in total
2. PracticalUse it productively at work, with the usual idioms18 lessons · 3 hours in total
3. AdvancedThe less common features, performance and trade-offs27 lessons · 5 hours in total
4. ExpertInternals and the edge cases experts know31 lessons · 6 hours in total

The PostgreSQL tree

PostgreSQLInstalling PostgreSQL and psqlInstalling PostgreSQLand psqlpsql productivitypsql productivityClusters, databases and schemasClusters, databasesand schemasConnecting applicationsConnectingapplicationsChoosing core data typesChoosing core datatypesIdentity columns and UUIDsIdentity columns andUUIDsTimestamps and time zonesTimestamps and timezonesRETURNING and upsertsRETURNING andupsertsBulk loading with COPYBulk loading withCOPYRoles and privilegesRoles and privilegesjsonb in practicejsonb in practiceArrays, enums and domainsArrays, enums anddomainsPartial, expression and concurrent indexesPartial, expressionand concurrent ind…EXPLAIN ANALYZE with BUFFERSEXPLAIN ANALYZEwith BUFFERSTransactions in PostgreSQLTransactions inPostgreSQLSQL and PL/pgSQL functionsSQL and PL/pgSQLfunctionsMaterialized viewsMaterialized viewsBackups with pg_dump and pg_restoreBackups withpg_dump and pg_re…Choosing data typesChoosing data typesPrimary keys and identity columnsPrimary keys andidentity columnsINSERT, UPDATE and DELETEINSERT, UPDATE andDELETENOT NULL, UNIQUE, CHECK and DEFAULTNOT NULL, UNIQUE,CHECK and DEFAULTIndex basicsIndex basicsReading query plans with EXPLAINReading query planswith EXPLAINTransactions and ACIDTransactions andACIDViews and materialized viewsViews andmaterialized viewsMVCC, row versions and VACUUMMVCC, row versionsand VACUUMLocks, SKIP LOCKED and deadlocksLocks, SKIP LOCKEDand deadlocksTable partitioningTable partitioningWrite-ahead log, backups and replicationWrite-ahead log,backups and replic…
Essentials and Practical shown (18 of 31 lessons); the full list is below. Click a lesson to read it.

All 31 lessons, in order

Getting started

  1. Installing PostgreSQL and psql · Essentials · 10 min

    Install a PostgreSQL server, connect with the psql client and run your first queries.

  2. psql productivity · Essentials · 10 min

    Use psql's describe commands, expanded output, timing, variables and script files to work fast.

  3. Clusters, databases and schemas · Essentials · 10 min

    How a PostgreSQL cluster contains databases, databases contain schemas, and search_path resolves names.

  4. Connecting applications · Essentials · 10 min

    Connection URIs, libpq environment variables, pg_hba.conf authentication and drivers for your language.

Types and writing data

  1. Choosing core data types · Essentials · 12 min

    Pick PostgreSQL types well: text vs varchar, integer sizes, numeric vs float, boolean and bytea.

  2. Identity columns and UUIDs · Essentials · 10 min

    Generate keys with GENERATED AS IDENTITY instead of serial, or with gen_random_uuid() and uuidv7().

  3. Timestamps and time zones · Essentials · 12 min

    Store moments in time as timestamptz, understand the TimeZone setting, and do interval arithmetic.

  4. RETURNING and upserts · Essentials · 10 min

    Get generated values back from INSERT, UPDATE and DELETE with RETURNING, and combine it with ON CONFLICT.

  5. Bulk loading with COPY · Essentials · 8 min

    Load and export CSV fast with COPY on the server or \copy from psql.

  6. jsonb in practice · Practical · 14 min

    Query, update and index jsonb documents with ->, ->>, @>, jsonb_set and SQL/JSON functions.

  7. Arrays, enums and domains · Practical · 12 min

    Use array columns with ANY and unnest, enum types for fixed value sets, and domains for reusable constraints.

  8. Range types and exclusion constraints · Advanced · 12 min

    Model intervals with tstzrange and daterange, query overlaps, and forbid overlapping bookings with EXCLUDE.

Security, backup and operations

  1. Roles and privileges · Practical · 12 min

    Create login and group roles, GRANT the minimum privileges, and set default privileges for future tables.

  2. Backups with pg_dump and pg_restore · Practical · 12 min

    Take logical backups in custom or directory format, restore selectively and in parallel, and test restores.

  3. Row-level security · Advanced · 12 min

    Restrict which rows each role or tenant can see and change with policies enforced by the database.

  4. Extensions and pg_stat_statements · Advanced · 12 min

    Install extensions with CREATE EXTENSION and find your most expensive queries with pg_stat_statements.

Indexes and query performance

  1. Partial, expression and concurrent indexes · Practical · 12 min

    Build indexes without blocking writes, index expressions and subsets of rows, and find unused indexes.

  2. EXPLAIN ANALYZE with BUFFERS · Practical · 14 min

    Read PostgreSQL plans with timing and buffer counts, spot misestimates and catch slow plans with auto_explain.

  3. GIN, GiST, BRIN and operator classes · Advanced · 14 min

    Pick the right index access method for jsonb, arrays, text search, geometry and huge append-only tables.

  4. Full-text search · Advanced · 14 min

    Search natural-language text with tsvector, tsquery, ranking and a GIN index, without a separate search engine.

Transactions and concurrency

  1. Transactions in PostgreSQL · Practical · 12 min

    Transactional DDL, savepoints, aborted transactions, and timeouts that protect the server from stuck sessions.

  2. VACUUM and autovacuum · Advanced · 15 min

    Why dead tuples accumulate, how autovacuum cleans them, and how to tune it and spot bloat and wraparound risk.

  3. Locks, blocking and job queues · Advanced · 14 min

    Find who blocks whom with pg_stat_activity and pg_blocking_pids, and build job queues with SKIP LOCKED.

Server-side programming

  1. SQL and PL/pgSQL functions · Practical · 14 min

    Write SQL functions and PL/pgSQL functions and procedures with variables, control flow and exceptions.

  2. Materialized views · Practical · 10 min

    Store a query's result with CREATE MATERIALIZED VIEW and refresh it, concurrently, on a schedule.

  3. Triggers and LISTEN/NOTIFY · Advanced · 14 min

    Run PL/pgSQL on row changes with BEFORE and AFTER triggers, and notify listeners with NOTIFY.

Internals and scaling

  1. Declarative partitioning in practice · Advanced · 14 min

    Partition large tables by range, attach and detach partitions concurrently, and keep pruning working.

  2. Heap storage, TOAST and HOT updates · Expert · 15 min

    How rows live in 8 kB heap pages, how TOAST stores big values, and how HOT updates avoid index writes.

  3. WAL archiving and point-in-time recovery · Expert · 15 min

    Physical backups with pg_basebackup plus archived WAL let you restore to any moment, not just the last dump.

  4. Streaming and logical replication · Expert · 15 min

    Run hot standbys with streaming replication and replication slots, and replicate selected tables logically.

  5. Memory settings and connection pooling · Expert · 15 min

    Size shared_buffers, work_mem and max_connections sensibly, and put PgBouncer in front of many clients.