How to learn PostgreSQL
This path assumes you can write basic SQL and gets you to reading execution plans, diagnosing bloat, and choosing an index that survives a production workload. It runs 110-130 hours over about 20 weeks. The focus is the three mechanisms behind most expensive incidents: indexes, MVCC, and the planner.
Hours
110-130 hours
Calendar
about 20 weeks at 6 hours a week
Stages
6
Start from
You need working knowledge of SQL: SELECT, JOIN, GROUP BY, and basic transactions. Nothing about database internals is required; people wrongly believe they need Linux kernel or C experience first.
Why the usual order fails
PostgreSQL looks like SQL, but the part that fails in production is not the syntax. It is the storage engine underneath: how rows are written, how old versions remain visible, and how the planner chooses a scan. Most learning paths start with data types and DDL, then add indexes as an afterthought. That order produces people who can create a table but cannot explain why a query that ran in 40 milliseconds on Tuesday takes four seconds after a bulk update. The common roadmap misses that indexes, MVCC and the planner are one system, not three separate chapters.
This path starts with a running instance and a pgbench-style table, then makes you read pg_stat_user_tables and EXPLAIN before you write a single CREATE INDEX. Week one is about observing tuple visibility and dead rows, not memorising syntax. Most plans have you installing and creating schemas for the first fortnight; here you run UPDATE and VACUUM on day one and watch the planner change its mind. From there, each stage adds one layer: index access methods, then planner statistics, then concurrency and locking.
The path, in the order it has to be walked.
Each stage assumes the one above it and nothing below it. The check at the foot of each is the point — you are meant to fail it, go back, and try again before moving on.
MVCC and dead tuples
weeks 1-3Start with the storage layer because every later problem shows up as dead tuples or visibility. You observe tuple headers and snapshots before touching indexes, so you can connect bloat to UPDATE and vacuum behaviour rather than treating it as a mystery.
- tuple headers
- xmin/xmax
- transaction snapshots
- MVCC visibility rules
- dead tuples
- autovacuum
- HOT updates
You can now: Create a table, run 50,000 updates, and use pg_stat_user_tables and pageinspect to show dead tuple counts and physical size before and after VACUUM.
Reading execution plans
weeks 4-6Plans sit here because you need to see what the planner thinks before you try to change it. You learn to read cost, actual time and buffer counts, which makes every later index or statistics change measurable instead of superstitious.
- EXPLAIN
- EXPLAIN ANALYZE
- BUFFERS
- seq scan
- index scan
- bitmap index scan
- cost parameters
- startup vs total cost
You can now: Take a query with three joins and predict from the plan which node reads the most buffers before running EXPLAIN (ANALYZE, BUFFERS).
B-tree and beyond
weeks 7-10Now that you can read a plan, you learn what an index actually is and when each type helps. B-tree comes first because it is the default, then the specialised types make sense as answers to specific access patterns.
- B-tree structure
- page splits
- fillfactor
- composite index column order
- covering indexes
- partial indexes
- expression indexes
- GIN
You can now: Given a table with 10 million rows and a query pattern, create an index that reduces logical reads by at least 90% and justify the choice with EXPLAIN.
Planner statistics and row estimates
weeks 11-13The planner is only as good as its statistics. You learn to read pg_stats and spot when default histograms mislead the planner, because row estimate errors are the usual reason an index is ignored or a nested loop runs for minutes.
- pg_stats
- most_common_vals
- histogram_bounds
- n_distinct
- extended statistics
- correlation
- ANALYZE
- join cardinality
You can now: Find a query where the planner's row estimate is off by more than 10x, fix it with extended statistics or a stats target, and show the plan changes.
Concurrency, locking and vacuum
weeks 14-17This stage sits after statistics because concurrency problems often look like slow queries but are actually lock waits or bloat. You reproduce deadlocks and measure vacuum progress, connecting the earlier MVCC work to production incidents.
- row-level locks
- table locks
- deadlocks
- transaction isolation levels
- serialisation failures
- VACUUM phases
- autovacuum tuning
- transaction ID wraparound
You can now: Reproduce a deadlock in two psql sessions, read the lock waits in pg_locks, and alter the transaction order so it no longer occurs.
Production diagnosis
weeks 18-20The final stage forces you to combine all three mechanisms on a live workload. You use pg_stat_statements and wait events to find the actual bottleneck, then fix it with an index, vacuum change or planner adjustment and measure the latency difference.
- pg_stat_statements
- pg_stat_activity
- wait events
- slow query log
- index bloat
- unused indexes
- REINDEX
- partitioning
You can now: Load a pgbench workload, find the top three queries by total time, and reduce the 95th percentile latency by half using only index, VACUUM or planner changes.
The hours are fixed. The calendar is yours.
The same path at three speeds, and what actually goes wrong at each.
3 hours a week
At three hours a week the path takes about 40 weeks. The risk is losing context between sessions: MVCC experiments from week one feel disconnected by week ten. You will need to rerun earlier exercises before each new stage, and the production diagnosis stage suffers most.
8 hours a week
Eight hours a week finishes in about 15 weeks. This is the sustainable pace for someone with a job. You can keep a test cluster running and repeat each week's failing query before moving on; skimp on the VACUUM and locking exercises and the final stage falls apart.
20 hours a week
Twenty hours a week compresses the path to six weeks, but the concurrency and bloat experiments cannot be rushed: autovacuum and transaction ID behaviour need wall-clock time. Expect to run some exercises overnight and accept that retention will be shallow.
Four ways people lose a year on this.
Each of these is appealing for a real reason, which is why they are common. What they cost is the part nobody mentions.
Adding an index for every slow query instead of reading the plan
People see a slow query, run CREATE INDEX on the filter column, and move on. It feels productive because the first query improves. But the planner may still choose a sequential scan, the index may bloat under updates, and write latency increases. They never learn whether the index changed row estimates or just hid a bad join order. The cost is more storage, slower writes, and the same incident later.
Tuning autovacuum before measuring dead tuples
When a table bloats, people raise autovacuum_vacuum_scale_factor or run manual VACUUM FULL. The appeal is immediate: table size drops. But without checking pg_stat_user_tables and transaction age, they miss long-running transactions holding back vacuum or a missing index on an update-heavy table. The cost is repeated bloat, wraparound risk, and downtime from VACUUM FULL.
Trusting default planner estimates on skewed data
The planner assumes column values are fairly distributed. On tables where a few accounts own most rows, default statistics produce terrible row estimates and nested loop choices. People blame the database or add more hardware. They do not inspect pg_stats or create extended statistics for correlated columns. The cost is queries that work in development fail in production after data grows.
Believing READ COMMITTED gives a stable snapshot across a transaction
People run the default READ COMMITTED and think each transaction sees one consistent view. But a single transaction can see different data across statements, and write skew still causes lost updates. They do not test with two psql sessions. The cost is double-spending or inconsistent reports under concurrency, discovered only after the first incident.
How to know it worked.
Not a syllabus you tick off — six things to attempt. If one of them is still hard, the stage it belongs to is the one to go back to.
- Run EXPLAIN on a simple SELECT and name the scan type and startup cost.
- Create a table, update one row, and confirm the old version remains visible in another session before commit.
- Run EXPLAIN (ANALYZE, BUFFERS) on a join and identify which node reads the most shared buffers.
- Create a composite index and show which query patterns can use it with different column orders.
- Find a skewed column in pg_stats and compare the planner's row estimate with actual rows from EXPLAIN ANALYZE.
- Reproduce a deadlock in two sessions and read the blocked and blocking PIDs from pg_locks.
Questions about learning PostgreSQL.
The five we hear most. The intro film answers most of them in a minute and a half.
how long does it take to learn postgresql properly
Plan for 110-130 hours of deliberate practice, roughly 20 weeks at six hours a week. That gets you to reading execution plans, diagnosing bloat, and choosing indexes under load, not to being a PostgreSQL contributor. If you only need to write basic queries, 20-30 hours is enough; production diagnosis is what takes the rest.
do i need to know c or linux internals to learn postgresql
No. You need working SQL and the patience to run psql commands. People wrongly assume you must understand the C source or kernel page cache first, but the behaviours that matter in production — MVCC visibility, planner row estimates, index page splits — are observable from SQL and catalog views. C helps only if you plan to patch the server.
is postgresql acid compliant
Yes, but ACID does not mean every transaction sees one fixed snapshot. With the default READ COMMITTED level, each statement gets a new snapshot, so a transaction can see different data over time. PostgreSQL is fully ACID at the transaction level, yet you must choose REPEATABLE READ or SERIALIZABLE if you need a stable view or protection from write skew.
why is my postgresql table getting bigger after updates
Because PostgreSQL keeps old row versions for MVCC until vacuum removes them. An UPDATE writes a new tuple and leaves the old one for concurrent readers. If autovacuum cannot keep up, dead tuples accumulate and the table and indexes bloat. Check pg_stat_user_tables for n_dead_tup and last_autovacuum before changing any settings.
does adding an index always make a query faster
No. An index helps only when it reduces the pages the planner expects to read enough to justify the index access overhead. On small tables, a sequential scan is often faster. On write-heavy tables, every index adds maintenance cost and can increase bloat. Use EXPLAIN with BUFFERS to see whether the index actually changed the plan and the buffer count.
A path is not a course.
This is the shape of PostgreSQL, not the teaching of it. Hand the same path to BiliMind and it writes the lessons onto it — in the form each one calls for, with practice that gets marked and a schedule that bends when your week does. Mapping a course is free and unlimited.
This roadmap was drafted on 18 September 2026 by the same engine that builds BiliMind courses, and reviewed before publishing. Hours are honest estimates for working competence, not for a demo — treat them as a starting point and adjust them against your own first fortnight.