🐘
Documentation Course
PostgreSQL In-Depth
From First Query to Production Architecture
A complete A-Z PostgreSQL curriculum — from absolute basics for beginners through production patterns for mid-level engineers to the deep internals that only senior engineers ever learn. Built from years of running PostgreSQL on a live blockchain indexer whose database grew to 10 TB.
Written by Jatin Jain Saraf · Architect of SupraScanFree35 modulesBeginner to AdvancedWritten / Documentation-style35 of 35 modules live
How to use this course
This course works as both a sequential read and a standalone reference. Read front-to-back to build a complete mental model of Postgres under production load. Or jump to any module when you hit a specific incident — VACUUM bloat, a lock queue outage, a query regression after a data load.
Total reading time
~11 hrs
across 35 live modules
Built from
10 TB
live blockchain indexer, cut to 2.7 TB
Prerequisite
Zero SQL to production internals
Beginner through senior engineer
Your instructor
Jatin Jain Saraf
Architect of SupraScan, Supra Blockchain's explorer and indexer: 500–1,000 TPS on a PostgreSQL database that grew to 10 TB, now 2.7 TB. The case studies show the production work behind these courses.
See it in production
- Case studyScaling a Blockchain Database From a Single Partition to 4.6 TBA partition strategy that worked in 2021 became a 10TB liability by 2026. Here's the five-stage evolution that got it back under control, the outage the last stage caused, and the storage arc from 10TB avoided to 4.6TB today.
- Case studyConcurrency Design Under High TPS: Moving Wallet Counters Off Database TriggersA 3-4 day indexer lag traced back to four wallets and a database trigger. The fix moved counting logic out of the database entirely, and a routine check-with-the-docs step turned up an architectural assumption that had been wrong all along.
- Case studyPaying Down Three Years of JSONB DebtJSONB is the right tool for a blockchain's variable-shaped data, until it isn't. A tiered removal plan, twelve columns re-classified one by one, and three columns nobody's allowed to touch because another team reads them directly.
- Case studyA Type Migration Across Three Services: VARCHAR to BIGINT at ScaleThe migration itself was the easy part. The real risk was every downstream function quietly assuming a column would always come back as a string. Two hotfixes later, that assumption was gone.
- Case studyA Reliability-Hardening Sprint: Isolation, Guardrails, and a Query Plan That Went From 45 Million to 206None of these fixes were triggered by an outage. Per-network cache isolation, timeouts on every resolver, dead-code removal, a partition-pruning fix with a genuinely startling before/after number, and a real external security report handled the right way.
Phase 1 — Foundation
Absolute basics · No prior knowledge assumed
F-1
What Is PostgreSQL and Why Does It Exist?
The problem databases solve, the client-server model, installation, and your first database — zero prior knowledge assumed.
14 min read
F-2
Tables, Rows, and the Relational Mental Model
Data types, CREATE TABLE, INSERT, SELECT, UPDATE, DELETE — every fundamental operation from first principles.
16 min read
F-3
Filtering, Sorting, and Finding What You Need
WHERE, ORDER BY, LIMIT, OFFSET, string functions, and date arithmetic — the tools you will use on every single query.
17 min read
F-4
Aggregation — Summarising Your Data
COUNT, SUM, AVG, GROUP BY, HAVING — the mental model that confuses beginners, explained from scratch.
15 min read
F-5
Connecting Tables — Joins Demystified
INNER JOIN, LEFT JOIN, FULL OUTER JOIN, self-joins, and multi-table queries — the most feared concept made simple.
18 min read
F-6
Constraints and Data Integrity
PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK — letting the database enforce your rules so application code does not have to.
15 min read
F-7
Your First Real Application Schema
Translating a real-world problem into tables, connecting from application code, and basic backup and restore.
14 min read
Phase 2 — Practitioner
Production patterns · Real application architecture
P-1
Advanced SQL — The Patterns You Will Use Every Week
CTEs, window functions, upserts, subqueries, UNION — the SQL that separates proficient engineers from beginners.
15 min read
P-2
Indexes — When and How to Add Them
The B-tree index for practitioners, EXPLAIN basics, composite and partial indexes, and the write cost trade-off.
17 min read
P-3
Transactions and ACID in Practice
BEGIN, COMMIT, ROLLBACK, isolation levels, and safe atomic operations — what every production application must understand.
15 min read
P-4
Schema Design for Real Applications
Normalisation, denormalisation trade-offs, correct data types for money and time, soft deletes, and schema migration tools.
17 min read
P-5
JSON and JSONB — Working With Semi-Structured Data
JSONB vs JSON, containment operators, GIN indexes, and when JSONB is the right tool vs. a schema design shortcut.
13 min read
P-6
Authentication, Row-Level Security, and Access Control
Roles, privileges, Row-Level Security policies, and multi-tenant data isolation — the production access model.
16 min read
P-7
Full-Text Search
tsvector, tsquery, GIN indexes, relevance ranking, and generated tsvector columns — search without Elasticsearch.
16 min read
P-8
Performance Tuning for Application Engineers
EXPLAIN ANALYZE for practitioners, key config parameters, N+1 queries, PgBouncer basics, and slow query logging.
20 min read
P-9
External Services, Caching Layers, and Deployment
ORMs vs. raw SQL, read replicas, materialised views, managed databases, and a production-ready deployment checklist.
16 min read
P-10
Zero-Downtime Schema Migrations
The ALTER TABLE lock matrix, CREATE INDEX CONCURRENTLY, NOT VALID + VALIDATE CONSTRAINT two-phase pattern, adding NOT NULL columns safely on 500M-row tables, column rename via view aliasing, lock_timeout + retry in migration tools, and the exact migration sequences that have taken production sites down.
20 min read
P-11
Connection Pooling Failure Modes: PgBouncer, Serverless, and the Edge
The silent bugs that only appear in production: prepared statements, temp tables, and serverless connection spikes.
16 min read
Phase 3 — Architect
Engine internals · High-throughput systems · Distributed architecture
A-0
Before You Proceed: Mental Model Reset
Why most engineers have a shallow model of Postgres — and what it costs them in production.
19 min read
A-1
The Storage Engine: Pages, Heaps, and the True Cost of a Row
Where your data actually lives and why every abstraction above this layer has a cost.
23 min read
A-2
MVCC: The Architecture That Makes Concurrency Possible (and Expensive)
How an 18-hour idle-in-transaction session caused 17 GB of dead tuple bloat — and the MVCC mechanics behind it.
22 min read
A-3
Write-Ahead Logging: Durability, Replication, and the Price of Every Write
Every byte you write passes through WAL. Understanding WAL is understanding your write amplification.
28 min read
A-4
Autovacuum: The Process Everyone Misconfigures
How a stalled replication slot pinned OldestXmin for 48 hours, accumulating 800 million dead tuples — and the autovacuum mechanics that let it happen.
31 min read
A-5
Index Internals: B-Tree, Hash, GIN, BRIN and the Planner's View
An index is not a free performance boost. Every index has a write cost, a bloat trajectory, and a planner interaction that can go wrong.
31 min read
A-6
Query Planning and Execution: How Postgres Decides What to Do With Your Query
The planner is a cost-based optimizer. Every wrong plan has a root cause in statistics, configuration, or schema design.
26 min read
A-7
Schema Design at Scale: Partitioning, Sharding and Multi-Tenancy
Every schema decision on day one compounds at 100x data volume. This module covers the decisions that matter most.
24 min read
A-8
PostgreSQL 18: Asynchronous I/O and What It Changes
The most architecturally significant change to Postgres in a decade — the definitive technical breakdown.
19 min read
A-9
The RETURNING Clause Evolved: OLD/NEW Aliases and Eliminating Race Conditions
PostgreSQL 18 introduces OLD and NEW in RETURNING. This eliminates entire classes of application-level race conditions.
17 min read
A-10
Replication, High Availability, and the CAP Trade-offs
Replication is not a backup. Understanding the consistency guarantees of each replication mode is non-negotiable.
25 min read
A-11
SQL vs NoSQL: An Engineer's Framework, Not a Marketing Debate
A structured decision framework using storage engine mechanics, consistency models, and scaling limits.
21 min read
A-12
Production Operations: Monitoring, Migration, and the Runbooks That Matter
The difference between junior and senior is not SQL knowledge — it is having the right runbooks before the incident.
26 min read
A-13
Locking Internals: Row Locks, Table Locks, and Advisory Locks
Concurrency bugs are almost never about wrong SQL — they are about wrong locking assumptions. The complete PostgreSQL locking matrix.
18 min read
A-14
Advanced Partitioning: Pruning, Maintenance, and pg_partman
The operational difference between designing a partition and maintaining 500 of them at scale.
15 min read
A-15
The Extensions Ecosystem: pg_cron, TimescaleDB, Citus, and pgvector
How Postgres extensions replace entire categories of specialized databases — without leaving ACID behind.
17 min read
A-16
Logical Replication and CDC Pipelines
Logical vs physical replication, replication slots and the slot bloat disaster, publication/subscription model, WAL sender process, Debezium + Kafka change data capture, zero-downtime major version upgrades using logical replication as a migration bridge, and replication identity modes.
21 min read