Learning Path

PostgreSQL Roadmap

A progressive path from SQL fundamentals to PostgreSQL performance, reliability, and production operations.

Foundations

PostgreSQL Fundamentals

  • What PostgreSQL is
  • When to choose it
  • Core ecosystem overview

Coming soon

Setup

Installation and Local Environment

  • Install Postgres locally
  • Configure users/databases
  • Use psql and GUI tools

Coming soon

Setup

Database and Schema Basics

  • Create databases and schemas
  • Search path basics
  • Naming conventions

Coming soon

Data Modeling

Data Types and Constraints

  • Core SQL types
  • CHECK/UNIQUE/NOT NULL
  • Primary and foreign keys

Coming soon

Data Modeling

Table Design Principles

  • Normalization trade-offs
  • Modeling one-to-many and many-to-many
  • Choosing surrogate vs natural keys

Coming soon

Querying

SELECT Fundamentals

  • Filtering and sorting
  • Pagination patterns
  • Projection best practices

Coming soon

Querying

Joins and Aggregations

  • INNER/LEFT joins
  • GROUP BY/HAVING
  • Window function introduction

Coming soon

Querying

Subqueries and CTEs

  • Correlated vs non-correlated subqueries
  • WITH/CTE patterns
  • Readability vs performance

Coming soon

Indexing

Index Fundamentals

  • B-tree basics
  • Indexing filter and join columns
  • Write cost trade-offs

Coming soon

Performance

EXPLAIN and Query Plans

  • Read EXPLAIN ANALYZE
  • Detect table scans and bad joins
  • Iterative tuning workflow

Coming soon

Transactions

ACID and Isolation Levels

  • Transaction boundaries
  • Read committed vs repeatable read
  • Consistency guarantees

Coming soon

Transactions

Locks and Concurrency

  • Row/table locks
  • Deadlock basics
  • Designing low-contention workflows

Coming soon

Advanced Features

JSONB and Semi-Structured Data

  • Store and query JSONB
  • GIN indexes
  • When JSONB is a good fit

Coming soon

Advanced Features

Full-Text Search

  • tsvector/tsquery basics
  • Ranking and dictionaries
  • Search index maintenance

Coming soon

Operations

Migrations and Versioning

  • Migration workflow
  • Rollback strategy
  • Safe schema evolution

Coming soon

Operations

Backup and Restore

  • pg_dump/pg_restore basics
  • Point-in-time recovery concepts
  • Backup verification

Coming soon

Operations

Monitoring and Maintenance

  • Core DB metrics
  • Autovacuum basics
  • Routine maintenance tasks

Coming soon

Production

Scaling and Reliability

  • Connection pooling
  • Replication basics
  • High-availability mindset

Coming soon