HowToCodePage

PostgreSQL

A feature-rich object-relational database.

PostgreSQL is taught here in 13 lessons, running from psql and the basics of SQL through to Next steps: the PostgreSQL ecosystem and migrations. Each lesson takes one topic, shows the working code, and links onward to the next, so the course can be read straight through in order.

TrackDatabases
Lessons13
LevelBeginner to intermediate
Reading timeabout 3 hours
PrerequisitesHelpful, but not required: MySQL

Lessons

  1. psql and the basics of SQLDrive the server from psql, learn the meta-commands worth memorising, and create tables with the types PostgreSQL actually gives you.
  2. JSONB and arraysWhen a document column beats another table, how the JSON operators differ, and how to index both JSONB and arrays properly.
  3. Transactions and MVCC basicsIsolation levels in practice, why readers never block writers, and what vacuum is really cleaning up.
  4. Data types, tables and constraintsChoosing numeric, text and time types, uuid versus identity, domains, CHECK constraints, foreign keys and the deferrable options that matter.
  5. Joins, subqueries, CTEs and LATERALEvery join type in practice, correlated subqueries, common table expressions, recursive CTEs, LATERAL joins and RETURNING.
  6. Window functions, full-text search and regexWindow frames, ROW_NUMBER and LAG, running totals, tsvector search with ranking, pg_trgm fuzzy matching and pattern matching options.
  7. Indexes: B-tree, GIN, GiST, partial and expressionChoosing the right index type, composite column order, covering indexes with INCLUDE, partial and expression indexes, and when to reindex.
  8. Functions, procedures and PL/pgSQL triggersWriting SQL and PL/pgSQL functions, returning sets, DO blocks for one-off work, trigger functions, and RAISE with proper exception handling.
  9. Roles, permissions, schemas and row-level securityGRANT and REVOKE, role memberships, default privileges, search_path safety, row-level security policies and extension management.
  10. Query planning and performance tuningReading EXPLAIN ANALYZE, planner statistics, autovacuum tuning, connection pooling and prepared statements under load.
  11. Backup, PITR and replicationpg_dump and pg_basebackup, WAL archiving for point-in-time recovery, physical streaming replicas, logical replication and verification.
  12. Partitioning, extensions and advanced deploymentDeclarative range and list partitioning, partition pruning, maintenance, extension management, PostGIS and pgvector, and upgrade strategies.
  13. Next steps: the PostgreSQL ecosystem and migrationsMigration tooling for real production schemas, managed Postgres trade-offs, monitoring that matters, and a reading path forward.

More in Databases

SQL MySQL MongoDB Redis SQLite

FAQ

How long does the PostgreSQL course take?
It has 13 lessons, about 3 hours of reading. Expect roughly twice that if you type out and run every example.
Do I need prior experience for PostgreSQL?
Not strictly. It helps to have read MySQL first, because some lessons build on it, but every lesson explains its own assumptions.
What should I read after PostgreSQL?
Continue with MongoDB (14 lessons), the next course in Databases.