PostgreSQL Internals — Complete Guide
PostgreSQL Internals — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of PostgreSQL Tutorial on Toolliyo Academy.
On this page
PostgreSQL Tutorial · Lesson 5 of 100
PostgreSQL Internals
SQL → Advanced
SQL · 1 — Queries · ~6 min · PostgreSQL — Foundations
What is this?
Inside PostgreSQL, every table is stored as heap files with 8 KB pages. The planner reads statistics from pg_catalog to pick index scans vs seq scans. MVCC keeps old row versions until vacuum reclaims space.
Why should you care?
When a Flipkart search slows down, internals knowledge tells you whether bloated pages, stale stats, or missing indexes are the culprit.
See it live — copy this example
Run in pgAdmin or psql.
SELECT relname, relpages, reltuples::bigint
FROM pg_class
WHERE relname = 'pg_database';What happened?
- pg_class catalogs every relation.
- relpages counts disk pages; reltuples is the planner estimate of row count.
- Inspecting catalog tables is how DBAs peek under the hood without opening data files.
Practice next
- Connect to PostgresVerse.
- Run the pg_class query and note relpages for pg_database.
- Run \d+ pg_database in psql to see column storage details.
- Create a small table, insert rows, run SELECT pg_relation_size('your_table');
- Compare seq_scan vs idx_scan in pg_stat_user_tables after queries.
Remember
Tables are heap pages on disk plus catalog metadata. The planner uses statistics, not magic. MVCC defers physical cleanup to vacuum.
PostgresVerse bloat hunt
A DBA checks pg_class and pg_stat_user_tables when orders table scans get slower month over month.
Outcome: They schedule VACUUM FULL off-peak instead of blindly adding RAM.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
