PostgreSQL Tutorial
Learn PostgreSQL step by step: 90 lessons in 14 chapters with examples, quizzes, diagrams and projects.
Start learning →
of lessons completed
Getting Started with PostgreSQL
- What Is PostgreSQL? Meet PostgreSQL: where it came from, how MVCC and the SQL standard shape it, how it differs from MySQL and SQLite, and how this track runs its examples.
- PostgreSQL vs MySQL Compare PostgreSQL and MySQL: licensing, storage engines, SQL features, strictness, case sensitivity, replication and the traps to avoid when migrating between them.
- Installing PostgreSQL and Connecting Install PostgreSQL from packages or Docker, understand initdb and services, learn pg_hba.conf basics, and connect with URIs and libpq environment variables.
- psql: The Command-Line Client Learn psql, PostgreSQL's command-line client: meta-commands like \d+ and \x, running scripts with -f and variables, \copy, \watch and a .psqlrc setup.
- Clusters, Databases and Schemas Understand the PostgreSQL hierarchy of cluster, database, schema and table, how search_path resolves names, why public changed in 15, and what templates are.
- A Tour of PostgreSQL SQL A quick tour of what PostgreSQL SQL adds to the basics: RETURNING, ILIKE, the :: cast, generate_series, DISTINCT ON, FILTER and LATERAL, and what is portable.
Data Types
- Numeric Types Pick the right PostgreSQL number type: smallint, integer, bigint, exact numeric(p,s), real and double precision, why floats surprise you, and why to avoid money.
- Text Types and Collations Choose between text, varchar(n) and char(n) in PostgreSQL, understand collations and ICU, and handle case-insensitive matching with lower(), citext and custom collations.
- Booleans and Enums Use boolean columns correctly, understand three-valued logic with NULL, and decide between CREATE TYPE AS ENUM, CHECK constraints and lookup tables in PostgreSQL.
- Dates, Times and Time Zones Learn PostgreSQL date, time, timestamp, timestamptz and interval types, AT TIME ZONE, date_trunc, extract, age and generate_series, with live ISO-8601 practice.
- UUIDs, Serials and Identity Columns Compare serial, GENERATED AS IDENTITY and UUID primary keys in PostgreSQL, learn how sequences create gaps, and choose between bigint keys, UUID v4 and time-ordered UUID v7.
- Sequences in Depth Master PostgreSQL sequences: CREATE SEQUENCE options, nextval, setval, CACHE, CYCLE, why gaps appear, fixing a sequence after a load and gapless alternatives.
- Arrays Store and query PostgreSQL array columns with literals, ANY and ALL, unnest, array_agg, slices and the @> and && operators, and learn when a join table is better.
- JSON and JSONB Learn the difference between json and jsonb in PostgreSQL, the -> ->> #>> and @> operators, jsonb_build_object, jsonb_set and a first look at jsonpath.
- JSONB in Practice Use PostgreSQL JSONB well: choose between documents and columns, validate with CHECK, expand arrays into rows, and avoid the update and TOAST pitfalls.
- Range and Multirange Types Learn PostgreSQL range and multirange types: bounds, the overlap and containment operators, and how ranges power booking systems. Live SQLite overlap demo.
- Composite Types, Domains and Custom Types Define your own PostgreSQL types: composite types, reusable domains with constraints, row types, hstore, inet and cidr network types and bit strings.
- Binary, Geometric, Network and Bit-String Types Tour PostgreSQL's specialist types: bytea for binary data, point/box/circle geometry, inet/cidr/macaddr network types, bit strings and xml, with operators and index tips.
- Generated Columns Use PostgreSQL generated columns to keep derived values correct: STORED columns, PostgreSQL 18 virtual columns, rules, and when to prefer views or triggers.
Querying with PostgreSQL Features
- DISTINCT ON and Top-N per Group Learn PostgreSQL DISTINCT ON to pick one row per group, how it differs from ROW_NUMBER for top-N, and how FETCH FIRST WITH TIES keeps tied rows.
- FILTER, GROUPING SETS, CUBE and ROLLUP Count and sum conditionally with the aggregate FILTER clause, then build subtotals and cross-tabs in one query with GROUPING SETS, ROLLUP, CUBE and GROUPING().
- LATERAL Joins Learn PostgreSQL LATERAL joins: subqueries and functions in FROM that see earlier tables, top-N per row, unnest, LEFT JOIN LATERAL, plus runnable SQLite stand-ins.
- generate_series and Set-Returning Functions Learn PostgreSQL set-returning functions: generate_series for numbers and dates, unnest, WITH ORDINALITY, regexp_split_to_table, and calendar tables with gap filling.
- INSERT ON CONFLICT and RETURNING Learn PostgreSQL upserts with INSERT ON CONFLICT DO NOTHING and DO UPDATE, EXCLUDED, partial-index targets, RETURNING, MERGE in 15+, and RETURNING from MERGE in 17.
- MERGE in PostgreSQL Learn MERGE in PostgreSQL 15+: WHEN MATCHED and NOT MATCHED branches, DELETE, NOT MATCHED BY SOURCE, RETURNING, concurrency and when to use ON CONFLICT instead.
- Full-Text Search Learn PostgreSQL full-text search: tsvector and tsquery, websearch_to_tsquery, ts_rank, ts_headline highlighting, GIN indexes, weights and language dictionaries.
- Pattern Matching and Regular Expressions Learn PostgreSQL pattern matching: LIKE and ILIKE, SIMILAR TO, POSIX regex operators ~ and ~*, regexp_replace, substring with regex, and pg_trgm similarity search.
- Window Functions in Depth Master PostgreSQL window functions: ROWS, RANGE and GROUPS frames, EXCLUDE, named WINDOW clauses, FILTER, ntile, first_value, nth_value and percentile_cont.
- CTEs, Materialization and Recursive Queries Learn PostgreSQL WITH queries: MATERIALIZED and NOT MATERIALIZED, writable CTEs, and recursive CTEs with the SEARCH and CYCLE clauses for trees and graphs.
Schema Design and Constraints
- Designing Tables the PostgreSQL Way Design PostgreSQL tables with confidence: naming rules, primary keys, normalization checks, data type choices, column order and padding, and timestamptz.
- Temporary and Unlogged Tables Learn PostgreSQL TEMP and UNLOGGED tables: lifetime, ON COMMIT, crash behavior, replication limits, name shadowing and when each is the right tool.
- Copying and Comparing Tables Copy PostgreSQL tables with CREATE TABLE AS, LIKE ... INCLUDING and SELECT INTO, then compare two tables with EXCEPT and FULL JOIN to find missing and changed rows.
- Constraints in Depth Master PostgreSQL constraints: NOT NULL, CHECK, UNIQUE with NULLS NOT DISTINCT, primary and foreign keys, ON DELETE actions, DEFERRABLE and NOT VALID.
- Exclusion Constraints and Temporal Design Stop double bookings in PostgreSQL with EXCLUDE USING gist and range types, then see PostgreSQL 18 temporal keys with WITHOUT OVERLAPS and PERIOD.
- Views and Materialized Views Use PostgreSQL views and materialized views well: updatable views, WITH CHECK OPTION, security_barrier, security_invoker and REFRESH CONCURRENTLY.
- Table Inheritance and Common Design Patterns Learn when PostgreSQL INHERITS helps and when it does not, plus proven patterns: soft delete, audit columns, polymorphic links, EAV pitfalls and tagging.
- Schema Changes and Migrations Change a live PostgreSQL schema safely: know which ALTER TABLE commands rewrite the table, how lock levels and lock_timeout work, and use expand/contract.
Indexes
- How Indexes Work in PostgreSQL See how a PostgreSQL index points into the heap with TIDs, why the planner picks a sequential, index or bitmap scan, and what every index costs on writes.
- B-tree Indexes Master the default PostgreSQL index: multicolumn order, equality then range, ASC/DESC and NULLS FIRST, unique indexes, INCLUDE columns and index-only scans.
- Partial and Expression Indexes Index only the rows you query with partial indexes, index computed values like lower(email), meet the IMMUTABLE rule, and enforce soft-delete uniqueness.
- GIN and GiST Indexes Learn when to use GIN and GiST indexes in PostgreSQL: jsonb, arrays and full-text search with GIN, ranges, geometry and exclusion with GiST, plus pg_trgm.
- BRIN, Hash and SP-GiST Indexes Learn the specialist PostgreSQL index types: tiny BRIN indexes for time-series, hash for equality, SP-GiST for tries and points, and pgvector's HNSW and IVFFlat.
- Index Maintenance and Bloat Keep PostgreSQL indexes healthy: find unused and duplicate indexes, rebuild with REINDEX CONCURRENTLY, understand index bloat, fillfactor and HOT updates.
Transactions, MVCC and Concurrency
- Transactions and ACID Learn how PostgreSQL transactions work: BEGIN, COMMIT, ROLLBACK, savepoints, transactional DDL, the aborted-transaction state and idle in transaction sessions.
- MVCC: How PostgreSQL Keeps Versions Understand PostgreSQL MVCC: row versions, xmin and xmax, snapshots and visibility, dead tuples, long-running transactions and transaction ID wraparound.
- Isolation Levels and Anomalies Learn PostgreSQL isolation levels with two-session examples: Read Committed, Repeatable Read, Serializable (SSI), write skew and how to retry serialization failures.
- Locks, Row Locking and Deadlocks Learn PostgreSQL locking: table lock modes, SELECT FOR UPDATE, NOWAIT, SKIP LOCKED job queues, advisory locks, deadlocks and how to inspect pg_locks.
- VACUUM, Autovacuum and Bloat Learn what VACUUM and autovacuum do in PostgreSQL: reclaim dead tuples, VACUUM vs VACUUM FULL, ANALYZE, per-table autovacuum tuning, freezing and monitoring.
Functions, PL/pgSQL and Triggers
- SQL Functions and Procedures Write PostgreSQL functions in plain SQL: RETURNS TABLE and SETOF, IMMUTABLE vs STABLE vs VOLATILE, inlining, and CREATE PROCEDURE with CALL.
- PL/pgSQL Basics Learn PL/pgSQL, PostgreSQL's procedural language: blocks, DECLARE, IF and CASE, loops, RAISE NOTICE, RETURN QUERY and dollar quoting, with clear examples.
- PL/pgSQL: Cursors, Dynamic SQL and Errors Go deeper with PL/pgSQL: build safe dynamic SQL with EXECUTE and format(), handle errors with EXCEPTION blocks, use cursors, and avoid SECURITY DEFINER traps.
- Triggers Learn PostgreSQL triggers: trigger functions, BEFORE and AFTER timing, row vs statement level, NEW and OLD, WHEN, transition tables, audit and updated_at.
- Event Triggers, LISTEN and NOTIFY Use PostgreSQL event triggers to react to DDL, and LISTEN/NOTIFY with pg_notify for cache invalidation and queues, including limits and the outbox pattern.
- Other Procedural Languages and Aggregates Survey PostgreSQL's other procedural languages (PL/Python, PL/Perl), write custom aggregates and operators, meet foreign data wrappers, and decide what logic belongs in the database.
Extensions and Ecosystem
- Extensions Learn how PostgreSQL extensions work: CREATE EXTENSION, contrib modules, pg_available_extensions, trusted extensions and managed-service allowlists.
- pg_trgm, hstore, citext, pgcrypto and uuid-ossp Meet five handy PostgreSQL extensions: fuzzy search with pg_trgm, key-value hstore, case-insensitive citext, hashing with pgcrypto and UUIDs with uuid-ossp.
- pgvector and PostGIS Add vector search and maps to PostgreSQL: pgvector columns, distance operators, HNSW and IVFFlat indexes, plus PostGIS geometry and geography basics.
- Foreign Data Wrappers and Cross-Database Access Query data outside your database with PostgreSQL foreign data wrappers: postgres_fdw, file_fdw, IMPORT FOREIGN SCHEMA, remote pushdown and dblink.
- Time-Series, Queues and Caching in PostgreSQL Use PostgreSQL for time-series data, SKIP LOCKED job queues, unlogged cache tables and scheduled jobs with pg_cron, and learn when to add other tools.
Performance Tuning
- EXPLAIN and EXPLAIN ANALYZE Learn to read PostgreSQL query plans: plan trees, cost versus actual time, rows, loops, buffers, scan nodes and join nodes, with EXPLAIN ANALYZE.
- Reading Slow Plans Learn to find the node that makes a PostgreSQL plan slow: row misestimates, nested loop vs hash vs merge joins, sorts that spill to disk, work_mem and JIT.
- Statistics and the Query Planner How PostgreSQL's planner guesses row counts: pg_stats, ANALYZE, default_statistics_target, extended statistics, cost settings and generic vs custom plans.
- Query Tuning Patterns Practical PostgreSQL query tuning patterns: sargable predicates, avoiding SELECT *, keyset pagination, EXISTS vs IN, batching, N+1 queries, CTEs and COUNT(*).
- Finding Slow Queries with pg_stat_statements Find the queries that cost the most: enable pg_stat_statements, rank by total time, add auto_explain and log_min_duration_statement, and read logs with pgBadger.
- Configuration Tuning Tune the PostgreSQL settings that matter: shared_buffers, work_mem, maintenance_work_mem, effective_cache_size, WAL and checkpoint settings, and connection limits.
- Connection Pooling Why PostgreSQL connections are expensive, how PgBouncer's session, transaction and statement modes work, what breaks in transaction mode, and how to size pools.
- Prepared Statements and Generic vs Custom Plans Learn PREPARE and EXECUTE, custom vs generic plans, plan_cache_mode, pg_prepared_statements and pooler caveats, with a live cost-model demo.
Partitioning and Scaling
- Declarative Partitioning Split big PostgreSQL tables with declarative partitioning. Learn range, list and hash partitions, the default partition, pruning, indexes, ATTACH and DETACH.
- Managing Partitions in Production Keep partitioned PostgreSQL tables healthy: automate partition creation, use pg_partman, sub-partition, migrate a big table online and avoid common pitfalls.
- Write-Ahead Logging Explained Understand PostgreSQL write-ahead logging: how commits become durable, what LSNs and WAL segments are, checkpoints, full-page writes, wal_level, and how WAL powers recovery.
- Streaming and Logical Replication Understand PostgreSQL replication: WAL, streaming standbys, synchronous vs asynchronous commit, replication slots, publications, subscriptions and lag monitoring.
- Backup, Restore and Point-in-Time Recovery Back up PostgreSQL with pg_dump, pg_restore and base backups, archive WAL for point-in-time recovery, meet pgBackRest, and learn why you must test restores.
- High Availability and Upgrades Plan PostgreSQL high availability: automatic failover with Patroni, managed services, major upgrades with pg_upgrade or logical replication, and minor releases.
Security and Administration
- Roles, Privileges and GRANT Control access in PostgreSQL with roles, LOGIN, GRANT and REVOKE, default privileges and schema rights. Build least-privilege roles for apps and reporting.
- Row-Level Security Use PostgreSQL row-level security to filter rows per user or tenant with policies, USING and WITH CHECK, current_setting, and avoid the common bypass pitfalls.
- Authentication, TLS and Encryption Configure pg_hba.conf, SCRAM passwords, TLS, pgcrypto, disk encryption and pgaudit, and learn how to keep database secrets out of code and logs.
- SQL Injection and Safe Queries in PostgreSQL Prevent SQL injection in PostgreSQL with parameters, format() using %I and %L, safe SECURITY DEFINER functions with a fixed search_path, and least privilege.
- Monitoring and System Catalogs Monitor PostgreSQL with pg_stat_activity, pg_stat_database and the system catalogs: find long queries, blockers, table sizes and cache hit ratio, and read the logs.
- Tablespaces and Physical Storage See how PostgreSQL stores tables on disk: the data directory, 8 kB pages, relation files, CREATE TABLESPACE, moving tables and measuring size with pg_relation_size.
Using PostgreSQL from Applications
- PostgreSQL from Python, Node.js, PHP and Java Use PostgreSQL from psycopg, node-postgres, PDO and JDBC: connect, pass parameters, run transactions, pool connections, bulk load with COPY and listen for notifications.
- ORMs, Migrations and PostgreSQL Features Use JSONB, arrays and enums from SQLAlchemy, Prisma, Eloquent and Django, drop to raw SQL when needed, and manage schema changes with Flyway, Alembic or Liquibase.
- Bulk Loading and COPY Load millions of rows into PostgreSQL fast: COPY FROM and TO, psql \copy, INSERT batching, unlogged staging tables and index handling. Live staging demo.
Projects
- Project: Booking System with Ranges and Constraints Build a meeting-room booking schema in PostgreSQL: tstzrange, a GiST exclusion constraint that forbids double bookings, availability queries and reports.
- Project: Product Catalog with JSONB and Search Build a flexible product catalog in PostgreSQL: JSONB attributes, containment filters, GIN indexes, facet counts and ranked full-text search.
- Project: Audit Log with Triggers Build a generic audit trail in PostgreSQL: one PL/pgSQL trigger function, who/when/what columns, JSONB diffs, history queries and a retention plan.
- Project: Job Queue with SKIP LOCKED Build a reliable job queue in PostgreSQL: a jobs table, safe claiming with FOR UPDATE SKIP LOCKED, retries with backoff, dead letters and lease timeouts.
- Project: Multi-Tenant SaaS Schema Design a multi-tenant SaaS schema in PostgreSQL: tenant_id columns, composite foreign keys, row-level security policies, per-tenant reports and onboarding.
Practice and Review
- PostgreSQL Exercises with Solutions Fourteen graded PostgreSQL exercises with solutions on types, queries, upserts, indexes, transactions and schema design. Portable parts run live in your browser.
- PostgreSQL Interview Questions Common PostgreSQL interview questions with concise answers on MVCC, vacuum, indexes, isolation levels, replication, query tuning and schema design.
- PostgreSQL Cheat Sheet A one-page PostgreSQL cheat sheet: psql commands, data types, operators, DDL and DML patterns, index choices and admin queries, with links to the full lessons.