#PostgreSQL
18 posts
PostgreSQL Basics #5 Reading EXPLAIN: Diagnosing Queries with Execution Plans
How to confirm why a query is slow with the execution plan instead of guesswork. The difference between EXPLAIN and EXPLAIN ANALYZE (the latter actually executes), reading the plan tree from the innermost node outward, what Seq Scan, Index Scan, Index Only Scan, and Bitmap Scan mean, comparing cost and actual time and estimated vs actual rows, the remedy when estimates are badly off (ANALYZE), and the habit of checking read volume with BUFFERS.
PostgreSQL Basics #4 Index Fundamentals: When Indexes Get Used and When They Don't
Indexes are a reliable way to make reads fast, and this chapter grounds them in PostgreSQL specifics. How a B-tree index changes lookups, the classic reasons an index you created is not being used (leading-column mismatch, functions applied to the column, type mismatch, low selectivity), the cost in write performance and storage, and the working order of "build from the WHERE, JOIN, and ORDER BY of actually slow queries."
PostgreSQL Basics #3 Joins and Aggregation: Working Instincts for Read Queries
Joins and aggregation make up most of real-world read queries, and this chapter builds the instincts. Choosing between INNER and LEFT JOIN and the classic mistake of filtering LEFT JOIN results in WHERE (turning them back into INNER), the division of labor between GROUP BY and HAVING, the double-counting trap when joins meet aggregation, the fork between subqueries and joins, and the PostgreSQL-native way to get the latest row per group (DISTINCT ON).
PostgreSQL Basics #2 Data Types and Table Design: What to Store Things As
Half of PostgreSQL design is choosing data types. Why text alone is enough for strings, why numeric is mandatory for money, timestamptz as the standard for points in time and the timestamp trap, primary key strategy (bigint IDENTITY vs UUID, and uuidv7), enforcing data integrity at the database layer with NOT NULL, CHECK, and foreign keys, and where enums and arrays belong.
PostgreSQL Basics #1 What PostgreSQL Is: Why It Became the Default, Installation and First psql Session
Starting from why PostgreSQL became the default database for new projects: standards compliance, a rich set of data types, the extension ecosystem (pgvector, PostGIS), and a permissive license. Then the fastest local setup with Docker, connecting with psql and the essential meta-commands (\l, \dt, \d), a first cycle of creating a table, inserting, and querying, and a map of the whole course.
Docker in Practice #2: Django + PostgreSQL compose — Two Containers as One
Bundling a Django app and PostgreSQL into one docker compose file. Migration entrypoint, depends_on relying on healthcheck, data volumes, .env separation, and collectstatic — a production-shaped compose setup.