PostgreSQL, Distributed SQL & Database Internals
A focused course on relational database engineering with PostgreSQL. You learn to read execution plans, design indexes, reason about transactions and isolation levels, and scale with partitioning and replication, finishing with an introduction to distributed SQL and the Raft consensus protocol.
- Duration
- 6 Weeks
- Effort
- 45 Hours
- Curriculum
- 6 Modules · 21 Lessons
- Outcome
- Capstone Project
Why this programme
What you will learn
Query Performance
Read execution plans and fix slow queries with the right indexes.
Transactions & MVCC
Isolation levels, locking and how PostgreSQL keeps data consistent.
Scaling Out
Partitioning, replication and the basics of distributed consensus.
Learning outcomes
By the end of this course you will be able to:
- Read and interpret EXPLAIN ANALYZE output.
- Choose and design B-tree, GIN and partial indexes.
- Explain MVCC, isolation levels and common concurrency anomalies.
- Partition large tables and plan replication topologies.
- Describe how distributed SQL databases use consensus protocols such as Raft.
Target profiles
Who should enrol
- Backend developers who want to diagnose and fix database performance problems.
- Engineers preparing for system design interviews.
- Data engineers who work with PostgreSQL in production.
Career pathways
Backend Engineer • Database Engineer • Data Engineer
Entry baseline
Course prerequisites
SQL basics
SELECT, JOIN, GROUP BY and basic table design.
One backend language
Able to connect to a database from an application.
Unsure whether you’re ready? Ask our academic team.
Structured syllabus
Curriculum: 6 modules, 21 lessons and labs
A step-by-step roadmap built around deliberate practice, coding labs and production-grade projects.
How PostgreSQL stores data on disk and turns SQL into an execution plan.
- Pages, tuples and the heap
- The query planner and cost model
- Reading EXPLAIN ANALYZE
- Lab 1.1: Diagnose and fix five slow queries
B-tree, hash, GIN and partial indexes, and when not to index.
- B-tree internals
- GIN, GiST and partial indexes
- Covering indexes and index-only scans
- Lab 2.1: Index design for a reporting workload
ACID, MVCC, isolation levels, locking and deadlocks.
- ACID and the write-ahead log
- MVCC and vacuum
- Isolation levels and anomalies
- Locks and deadlocks
- Lab 3.1: Reproduce and fix a race condition
Table partitioning, streaming and logical replication, and high availability.
- Declarative partitioning
- Streaming and logical replication
- High availability and failover
- Lab 4.1: Partition a time-series table
Sharding, consensus with Raft, and the trade-offs of distributed SQL databases.
- Sharding strategies
- Consensus and the Raft protocol
- Distributed transactions and trade-offs
Audit and tune a realistic database and present the results.
- Capstone: database performance audit and tuning report
Capstone project
A performance audit of a realistic PostgreSQL database with measured improvements and a written report.
Upcoming cohorts
Batch schedule & timings
All times are in Indian Standard Time (IST).
Fees and batch dates are sample data pending confirmation
New batches will be announced soon. Contact us to be notified.
Faculty
Taught by practitioners
Instructor profiles for this course will be published before the batch begins.
Frequently asked questions
Contact your batch coordinator as early as possible. Arrangements for catching up on missed sessions are shared at the start of each batch.
You need a computer running Windows 10/11, macOS or Linux with at least 8 GB of RAM (16 GB is recommended for Docker, Kubernetes and machine learning courses), a stable internet connection, and permission to install developer tools such as VS Code, an IDE, Docker Desktop and Git.
Each course page shows its programme fee in Indian Rupees. Online payment is not yet available on this website; once you submit an enrolment request, the DevCrest team will share the confirmed fee and payment options with you.
Details of course completion credentials will be confirmed for each batch. DevCrest certificates are issued by DevCrest and are not a government or university accreditation.