PostgreSQL Complete Guide
PostgreSQL is a free, open-source relational database known for strict standards compliance, rock-solid reliability, and features most databases only added decades later — real transactions, sophisticated indexing, and a native JSONB type that blurs the line with document stores. This doc walks through the 11 concepts a working engineer actually needs, in the order you're likely to need them, with a diagram, a "where to use it" guide, and concrete SQL for every single one.
Use the sidebar to jump straight to any topic — each one opens its own page with a full explanation, a diagram of exactly how it fits with the concepts around it, and the concrete SQL to actually try it. If you're starting from zero, work through Phase 1 first: nothing else on this roadmap makes sense without a server to connect to and a table to put data in.
This is the shape every PostgreSQL feature ultimately serves. A client talks to the server, which reads and writes tables that live on disk; indexes make the reads fast and the write-ahead log (WAL) makes every write durable and crash-safe. Because every change is committed to the WAL before it's applied, and the whole sequence of statements between BEGIN and COMMIT either happens completely or not at all, PostgreSQL can guarantee full ACID transactions even across a crash mid-write. Everything below is its own page in this sidebar.
All 11 topics in this guide
Summary & Patterns
Once the individual topics make sense on their own, these references tie them together: the order to learn them in, how urgently each one matters, and how the query planner actually decides what a query costs.
Recommended Learning Order
Recommended Priority
Anatomy of a Query Plan
The single most useful habit on this whole roadmap: read EXPLAIN ANALYZE before assuming a slow query needs an index — the planner already tells you exactly what it did and why.
The planner never looks at the actual data — it estimates the cost of each possible path from stored table statistics ①, then hands the executor whichever path it believes is cheapest, sequential scan or index scan. EXPLAIN ANALYZE shows you both the estimate and what actually happened, which is the only reliable way to know whether a slow query needs an index or already has one it isn't using.
Official Documentation Reference URLs
Every topic covered in this guide, with a direct link to its official PostgreSQL docs page.