🐘
Resource Hub
PostgreSQL
Deep-dive articles and a structured course — from your first SELECT to production schema internals, written from experience running PostgreSQL at 20–30 TB scale on a live blockchain indexer.
PostgreSQL In-Depth Course
35 modulesFoundation
First concepts, core SQL, and your first production schema — zero prior knowledge assumed.
F-119 min read
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.F-222 min read
Tables, Rows, and the Relational Mental ModelData types, CREATE TABLE, INSERT, SELECT, UPDATE, DELETE — every fundamental operation from first principles.F-322 min read
Filtering, Sorting, and Finding What You NeedWHERE, ORDER BY, LIMIT, OFFSET, string functions, and date arithmetic — the tools you will use on every single query.F-418 min read
Aggregation — Summarising Your DataCOUNT, SUM, AVG, GROUP BY, HAVING — the mental model that confuses beginners, explained from scratch.F-523 min read
Connecting Tables — Joins DemystifiedINNER JOIN, LEFT JOIN, FULL OUTER JOIN, self-joins, and multi-table queries — the most feared concept made simple.F-620 min read
Constraints and Data IntegrityPRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK — letting the database enforce your rules so application code does not have to.F-719 min read
Your First Real Application SchemaTranslating a real-world problem into tables, connecting from application code, and basic backup and restore.Practitioner
Advanced SQL patterns, indexes, transactions, schema design, performance basics, and production deployment.
P-120 min read
Advanced SQL — The Patterns You Will Use Every WeekCTEs, window functions, upserts, subqueries, UNION — the SQL that separates proficient engineers from beginners.P-221 min read
Indexes — When and How to Add ThemThe B-tree index for practitioners, EXPLAIN basics, composite and partial indexes, and the write cost trade-off.P-321 min read
Transactions and ACID in PracticeBEGIN, COMMIT, ROLLBACK, isolation levels, and safe atomic operations — what every production application must understand.P-424 min read
Schema Design for Real ApplicationsNormalisation, denormalisation trade-offs, correct data types for money and time, soft deletes, and schema migration tools.P-518 min read
JSON and JSONB — Working With Semi-Structured DataJSONB vs JSON, containment operators, GIN indexes, and when JSONB is the right tool vs. a schema design shortcut.P-624 min read
Authentication, Row-Level Security, and Access ControlRoles, privileges, Row-Level Security policies, and multi-tenant data isolation — the production access model.P-723 min read
Full-Text Searchtsvector, tsquery, GIN indexes, relevance ranking, and generated tsvector columns — search without Elasticsearch.P-827 min read
Performance Tuning for Application EngineersEXPLAIN ANALYZE for practitioners, key config parameters, N+1 queries, PgBouncer basics, and slow query logging.P-923 min read
External Services, Caching Layers, and DeploymentORMs vs. raw SQL, read replicas, materialised views, managed databases, and a production-ready deployment checklist.P-1029 min read
Zero-Downtime Schema MigrationsThe 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.P-1122 min read
Connection Pooling Failure Modes: PgBouncer, Serverless, and the EdgeThe silent bugs that only appear in production: prepared statements, temp tables, and serverless connection spikes.Architect
Database internals — MVCC, WAL, autovacuum, replication, locking, and production operations at scale.
A-026 min read
Before You Proceed: Mental Model ResetWhy most engineers have a shallow model of Postgres — and what it costs them in production.A-134 min read
The Storage Engine: Pages, Heaps, and the True Cost of a RowWhere your data actually lives and why every abstraction above this layer has a cost.A-232 min read
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 6 hours of replica lag — and the MVCC mechanics behind it.A-344 min read
Write-Ahead Logging: Durability, Replication, and the Price of Every WriteEvery byte you write passes through WAL. Understanding WAL is understanding your write amplification.A-441 min read
Autovacuum: The Process Everyone MisconfiguresHow a stalled replication slot pinned OldestXmin for 48 hours, accumulating 800 million dead tuples — and the autovacuum mechanics that let it happen.A-549 min read
Index Internals: B-Tree, Hash, GIN, BRIN and the Planner's ViewAn index is not a free performance boost. Every index has a write cost, a bloat trajectory, and a planner interaction that can go wrong.A-639 min read
Query Planning and Execution: How Postgres Decides What to Do With Your QueryThe planner is a cost-based optimizer. Every wrong plan has a root cause in statistics, configuration, or schema design.A-738 min read
Schema Design at Scale: Partitioning, Sharding and Multi-TenancyEvery schema decision on day one compounds at 100x data volume. This module covers the decisions that matter most.A-827 min read
PostgreSQL 18: Asynchronous I/O and What It ChangesThe most architecturally significant change to Postgres in a decade — the definitive technical breakdown.A-923 min read
The RETURNING Clause Evolved: OLD/NEW Aliases and Eliminating Race ConditionsPostgreSQL 18 introduces OLD and NEW in RETURNING. This eliminates entire classes of application-level race conditions.A-1040 min read
Replication, High Availability, and the CAP Trade-offsReplication is not a backup. Understanding the consistency guarantees of each replication mode is non-negotiable.A-1132 min read
SQL vs NoSQL: An Engineer's Framework, Not a Marketing DebateA structured decision framework using storage engine mechanics, consistency models, and scaling limits.A-1240 min read
Production Operations: Monitoring, Migration, and the Runbooks That MatterThe difference between junior and senior is not SQL knowledge — it is having the right runbooks before the incident.A-1325 min read
Locking Internals: Row Locks, Table Locks, and Advisory LocksConcurrency bugs are almost never about wrong SQL — they are about wrong locking assumptions. The complete PostgreSQL locking matrix.A-1419 min read
Advanced Partitioning: Pruning, Maintenance, and pg_partmanThe operational difference between designing a partition and maintaining 500 of them at scale.A-1523 min read
The Extensions Ecosystem: pg_cron, TimescaleDB, Citus, and pgvectorHow Postgres extensions replace entire categories of specialized databases — without leaving ACID behind.A-1630 min read
Logical Replication and CDC PipelinesLogical 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.Articles
10 posts🔐Transactions and ACID in Practice: What Every Backend Developer Must KnowThe money left Alice's account. Bob never received it. The server crashed in between. If you don't understand transactions, this is how your application loses data, silently, permanently, with no erro
DatabasePostgreSQLBackend
📝Mastering PostgreSQL: 5 Essential Tips for Performance OptimizationWhen your database slows down, your whole application suffers. Discover 5 essential PostgreSQL optimization tips—from EXPLAIN ANALYZE to smart indexing—that will dramatically improve your query perforPostgreSQLDatabasePerformance
✍️Database Efficiency 101: Understanding Bloat, Vacuum, and the Power of pg_repack# Database Efficiency 101: Understanding Bloat, Vacuum, and the Power of pg_repack In a high-performance database environment, how we manage storage is just as important as how we write our queries.DatabasePostgreSQL
🗒️The Power of LATERAL Joins: Turning SQL into a “ForEach” Loop# The Power of LATERAL Joins: Turning SQL into a “ForEach” Loop In the world of SQL, we are taught to think in sets. We join Table A to Table B based on a common key, and the database engine decidesDatabaseSQLPostgreSQL
📖PostgreSQL 18: A Paradigm Shift for Modern Data Workloads# PostgreSQL 18: A Paradigm Shift for Modern Data Workloads PostgreSQL has long been the gold standard for relational databases, robust, reliable, and continuously evolving. With each major release…DatabaseSQL
📚Cloud‑to‑Cloud Database Migration: A Practical Guide for Large PostgreSQL Systems# Cloud‑to‑Cloud Database Migration: A Practical Guide for Large PostgreSQL Systems Migrating infrastructure from one cloud provider to another is already a complex process. But migrating a large, prDatabasePostgreSQLCloud Migration
🎯The Complete Developer’s Guide to Indexing Assets on Supra & Aptos (Coins, FAs, NFTs)# The Complete Developer’s Guide to Indexing Assets on Supra & Aptos (Coins, FAs, NFTs) As a developer building on a high-performance blockchain like Supra, you’ll soon encounter a critical challengeDatabaseWeb3Performance
⚡The PostgreSQL Elephant in the Room: A Deep Dive Into the Architecture That Powers Giants# The PostgreSQL Elephant in the Room: A Deep Dive Into the Architecture That Powers Giants PostgreSQL’s reputation for resilience, consistency, and performance isn’t accidental. It’s the result of dDatabaseSQLArchitecture
🎓From Lag to Leading: A Deep Dive into Fixing a 2-Hour PostgreSQL Replication Lag# From Lag to Leading: A Deep Dive into Fixing a 2-Hour PostgreSQL Replication Lag Every engineer responsible for a large database fears the “big one” — a problem so severe it brings operations to aDatabaseSQLPostgreSQL
📝Taming PostgreSQL Replication Lag in Real-Time Blockchain Indexers# Taming PostgreSQL Replication Lag in Real-Time Blockchain Indexers In distributed systems that process massive volumes of real-time data, such as blockchain indexers, database replication is essentDatabaseSQLWeb3