PostgreSQL Mastery 🐘

Learning PostgreSQL from zero to production — completely in the open.

---

👋 What is this?

I'm Ranesh Rajit, a B.Tech CS student from India, learning PostgreSQL from scratch — installation to production optimisation, one concept at a time.

Every query I write, every experiment I run, and every production tip I learn goes into this repo so that anyone else on the same journey can learn alongside me for free.

📖 See the full roadmap with every concept planned.

---

📈 Learning Progress

Phase	Topic	Status
1	Foundation & Tools	🔄 In progress
2	SQL Power Tools	⬜ Upcoming
3	Data Integrity & Design	⬜ Upcoming
4	Transactions & Concurrency (MVCC)	⬜ Upcoming
5	Performance & Indexing	⬜ Upcoming
6	Programmability & Logic	⬜ Upcoming
7	Administration & Security	⬜ Upcoming
8	Modern PostgreSQL & Extensions	⬜ Upcoming
9	Production & Monitoring	⬜ Upcoming

See the full roadmap with sub‑topics in ROADMAP.md.

---

🎯 Learning Milestones

• Installed PostgreSQL and connected via psql or a GUI
• Created first database and table
• Wrote basic SELECT, INSERT, UPDATE, DELETE statements
• Understood primary keys, foreign keys, and relationships
• Mastered different JOIN types (INNER, LEFT, RIGHT, FULL)
• Used GROUP BY and aggregate functions (COUNT, SUM, AVG)
• Wrote subqueries and WITH (CTE) queries
• Built a recursive query (e.g., threaded comments, tree traversal)
• Learned about transactions, BEGIN, COMMIT, ROLLBACK
• Understood MVCC and witnessed tuple visibility in action
• Implemented constraints: CHECK, UNIQUE, exclusion constraints
• Created views and materialised views
• Wrote a PL/pgSQL function or stored procedure
• Used triggers to automate logic (BEFORE, AFTER)
• Read and interpreted EXPLAIN ANALYZE output
• Created B‑Tree, GIN, GiST indexes to speed up queries
• Optimised a slow query from seconds to milliseconds
• Performed backup and restore with pg_dump and pg_restore
• Managed roles, permissions, and Row‑Level Security
• Configured connection pooling with PgBouncer
• Worked with JSON/JSONB and indexed unstructured data
• Implemented full‑text search using tsvector and tsquery
• Used pgvector for similarity search (AI embeddings)
• Explored PostGIS for geospatial queries
• Set up table partitioning for large datasets
• Monitored query performance with pg_stat_statements
• Deployed a PostgreSQL instance for a real project

---

🧠 Concepts Covered (so far)

• ✅ Installation & Tooling (psql, DataGrip, pgAdmin)
• ✅ Database creation, basic data types
• 🔜 Multi‑version concurrency control (MVCC)
• 🔜 Recursive Common Table Expressions (CTEs)
• 🔜 Window functions, DISTINCT ON, CASE
• 🔜 Table relationships, JOINs, GROUP BY, subqueries
• 🔜 Indexing strategies, EXPLAIN ANALYZE
• 🔜 Transactions, isolation levels, locks
• 🔜 JSON, full‑text search, PostGIS, pgvector

(See the roadmap for every topic planned.)

---

📁 Repository Structure

All SQL files are runnable directly in your own PostgreSQL instance.

---

🚀 How to use this repo

1. Clone the repo
2. Open the .sql files in DataGrip / psql / pgAdmin
3. Run them in order (phase → sub‑phase)
4. Experiment – break things, then fix them

---

🔧 Environment & Tools

---

📚 Resources I'm Learning From

• 📖 Official PostgreSQL Documentation
• 📺 Amigoscode SQL Course
• 📺 freeCodeCamp PostgreSQL Course
• 📺 Hussein Nasser – Deep Dives
• 🎙️ Postgres FM Podcast
• 🧪 My own experiments, shared right here

---

💖 Sponsors

This repo is free for everyone — no paywalls, no paid courses. If it's helped you or you want to support the journey:

📚 My other learning journeys

• Java + DSA Progress – 173+ solved problems
• DeepSeekWidget – Android widget for AI

---

🔜 What's Next?

• Learn how to read and use EXPLAIN ANALYZE
• Create indexes (B‑Tree, GIN, GiST) and measure speed improvements
• Dive into transactions, isolation levels, and MVCC
• Write custom functions with PL/pgSQL
• Use triggers to automate data logic
• Explore JSON/JSONB for semi‑structured data
• Implement full‑text search (tsvector, tsquery)
• Set up backups and restores (pg_dump, pg_restore)
• Work with pgvector for AI‑powered similarity search
• Try PostGIS for geospatial queries
• Partition large tables for performance
• Deploy PostgreSQL for a real project (local or cloud)

---

⭐ Star this repo if you're learning PostgreSQL too — it keeps me pushing forward!