🚀 PostgreSQL Query Optimization: Let's Learn from ZERO!

Imagine your social media app has only 30 posts today. Everything is fast. Now imagine it grows to:

• 👥 10,000 users
• 📝 1 million posts
• ❤️ 10 million likes
• 💬 Millions of comments

Suddenly some API requests become slow, the database takes longer to retrieve data, and your server does more work than necessary.

📌 Query optimization is about making database operations faster and more efficient without changing the correctness of the results.

We'll learn everything from zero, using PostgreSQL and examples from a social-feed project. 🎯

1. 🤔 What Does Query Optimization Mean?

Take a posts table:

You run:

SELECT * FROM posts WHERE user_id = 101; 

You want all posts by user 101. But how does the database find them?

1. It can inspect every row one by one
2. It can use an index to find matching rows more directly

The second approach can be much faster for suitable queries. That's where indexing and query optimization come in.

1.1 📨 What is a database query?

A query is simply a request sent to the database:

SELECT * FROM users; -- get all users SELECT * FROM posts WHERE id = 10; -- get post 10 SELECT * FROM likes WHERE post_id = 25; -- get all likes of post 25 

Every time your FastAPI backend needs data, it may execute queries:

User opens the app ▼ React frontend requests /feed ▼ FastAPI receives the request ▼ Service layer executes a database query ▼ PostgreSQL retrieves matching posts ▼ FastAPI returns a JSON response ▼ React displays posts 

If the database query is slow, the API response may also become slow.

⚠️ Query optimization isn't only about writing shorter SQL. It's about reducing unnecessary database work.

2. 📚 What is a Database Index?

This is the foundation of the whole topic. Imagine a 1,000-page textbook and you want the topic "Normalization".

• Without an index: you start at page 1, then page 2, page 3... until you find it on page 650. That's like a Sequential Scan
• With an index: the back of the book says Normalization → Page 650. You go straight there. That's like an indexed lookup

Normalization → Page 650 Transactions → Page 720 Primary Key → Page 100 SQL Joins → Page 250 

📌 A database index is an additional data structure that helps PostgreSQL locate rows more efficiently.

CREATE INDEX idx_posts_user_id ON posts(user_id); 

This creates an index on user_id. Now this query can potentially use it:

SELECT * FROM posts WHERE user_id = 101; 

Without an index:

Check row 1 → Check row 2 → Check row 3 → Check row 4 → ... 

With an index:

Search the index ▼ Find user_id = 101 ▼ Locate matching row positions ▼ Retrieve matching rows 

🧠 Important things to remember

• An index is separate from the actual table data
• An index helps speed up certain SELECT queries
• An index consumes additional storage
• INSERT, UPDATE and DELETE may become slower because indexes also need maintenance
• Not every query benefits from an index

🏆 Golden rule: index columns based on actual query patterns, not randomly.

3. 🔍 EXPLAIN: Understanding How PostgreSQL Executes Queries

You write a query, but how do you know PostgreSQL runs it efficiently? You can't assume a query is fast just because it looks simple. PostgreSQL provides EXPLAIN.

3.1 🤔 What is EXPLAIN?

📌 EXPLAIN shows the query execution plan: how PostgreSQL plans to retrieve the requested data.

EXPLAIN SELECT * FROM posts WHERE user_id = 101; 

Possible output:

Seq Scan on posts Filter: (user_id = 101) 

A. Seq Scan. Seq means sequential. PostgreSQL reads through the table's rows to find matching records.

posts table Row 1 → Check Row 2 → Check Row 3 → Check ... 

Is Seq Scan bad? NO! This is extremely important. If your table has only 10 rows, using an index might actually cost more than checking all 10. PostgreSQL intelligently chooses between execution strategies.

B. Filter. Filter: (user_id = 101) means PostgreSQL keeps rows satisfying user_id = 101 and discards the others.

3.2 📋 Common scan types

1. Sequential Scan: Seq Scan on posts reads the table and checks rows
2. Index Scan: Index Scan using idx_posts_user_id on posts uses an index to find matching rows, then retrieves the actual table rows
3. Index Only Scan: if the index contains all the columns the query needs, PostgreSQL may answer without visiting the table's data pages. It can still need visibility checks depending on the visibility map
4. Bitmap Index Scan: useful when many matching rows must be retrieved. PostgreSQL first identifies matching row locations using the index, then fetches table pages efficiently

3.3 🗺️ What is a query execution plan?

📌 A query execution plan is the sequence of operations PostgreSQL chooses to execute your query.

Index Scan using idx_posts_user_id on posts Index Cond: (user_id = 101) 

• Index Cond: the condition used to search through the index
• Filter: an additional condition applied to rows after they are retrieved

For example:

SELECT * FROM posts WHERE user_id = 101 AND is_deleted = false; 

PostgreSQL might use an index on user_id, then filter out deleted posts.

3.4 💰 What does "cost" mean?

Index Scan (cost=0.15..8.20 rows=5 width=120) 

⚠️ PostgreSQL's cost is an internal estimate, not milliseconds. It helps PostgreSQL compare different possible plans.

4. ⏱️ EXPLAIN ANALYZE: Measuring Actual Execution

A very common interview question: what's the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the estimated plan and doesn't normally execute the query:

EXPLAIN SELECT * FROM posts WHERE user_id = 101; 

EXPLAIN ANALYZE actually executes the query and shows the plan along with measured statistics:

EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 101; 

Index Scan using idx_posts_user_id on posts (cost=0.15..8.20 rows=5 width=120) (actual time=0.025..0.041 rows=5 loops=1) Planning Time: 0.120 ms Execution Time: 0.065 ms 

🎯 Interview important: EXPLAIN = what PostgreSQL estimates it will do. EXPLAIN ANALYZE = what PostgreSQL actually did, with measured statistics.

🚨 Warning: EXPLAIN ANALYZE executes the query. For SELECT that's generally safe. For INSERT, UPDATE or DELETE it really performs the modification. Wrap it in a transaction and ROLLBACK if you want to test without keeping changes.

BEGIN; EXPLAIN ANALYZE UPDATE posts SET is_deleted = true WHERE id = 10; ROLLBACK; 

You can also ask for buffer statistics:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM posts WHERE user_id = 101; 

BUFFERS shows information about database pages accessed from shared buffers and storage, which helps you spot queries doing too much disk or memory I/O.

5. 🧰 Types of Indexes: B-tree vs GIN vs GiST

PostgreSQL supports different index types because different queries search for different kinds of data: a user ID, a word inside a text document, locations within a distance, elements inside a JSON document. One index type isn't suitable for every situation.

5.1 🌳 B-tree Index

The most commonly used. B-tree stands for Balanced Tree and it's PostgreSQL's default index type.

CREATE INDEX idx_users_email ON users(email); 

This creates a B-tree index by default.

Imagine these numbers: 10, 20, 30, 40, 50, 60, 70. A simplified tree:

 40 / \ 20 60 (smaller) (larger) 

(A simplified binary-tree illustration. Real B-trees have multiple keys and children per node.) The database navigates the tree instead of checking every value.

B-tree is excellent for:

-- 1. Equality searches SELECT * FROM users WHERE id = 10; -- 2. Range searches SELECT * FROM posts WHERE id > 100; -- 3. Sorting SELECT * FROM users ORDER BY created_at; -- 4. Minimum and maximum values SELECT MIN(created_at) FROM posts; 

It's particularly useful for integers, IDs, email addresses, dates and timestamps, numeric values, and sorting and range conditions. Whenever you search, compare or sort ordinary scalar values, B-tree is usually the first index type to consider.

5.2 📇 GIN Index

GIN stands for Generalized Inverted Index. It's especially useful for searching inside collections of values or documents.

A post has tags:

Caption: Learning PostgreSQL Tags: ["database", "backend", "python"] Caption: Learning React Tags: ["frontend", "javascript", "web"] 

You want all posts containing the tag backend. Use PostgreSQL's array type:

CREATE TABLE posts ( id SERIAL PRIMARY KEY, caption TEXT, tags TEXT[] ); INSERT INTO posts (caption, tags) VALUES ( 'Learning PostgreSQL', ARRAY['database', 'backend', 'python'] ), ( 'Learning React', ARRAY['frontend', 'javascript', 'web'] ); 

Query:

SELECT * FROM posts WHERE tags @> ARRAY['backend']; 

@> means contains: "give me posts whose tags contain backend". Now create a GIN index:

CREATE INDEX idx_posts_tags ON posts USING GIN(tags); 

PostgreSQL can use it for supported array containment queries.

Why "inverted"? Think of a dictionary. Instead of storing Post 1 → database, backend, python, an inverted index conceptually maps each value back to the records containing it:

database → Post 1 backend → Post 1 python → Post 1 frontend → Post 2 javascript → Post 2 web → Post 2 

Where is GIN useful? PostgreSQL arrays, JSONB data, full-text search, and searching for elements within collections. A JSONB example:

CREATE TABLE products ( id SERIAL PRIMARY KEY, details JSONB ); INSERT INTO products(details) VALUES ('{"color": "black", "brand": "Nike"}'); CREATE INDEX idx_products_details ON products USING GIN(details); SELECT * FROM products WHERE details @> '{"color": "black"}'; 

This searches for JSON documents containing the specified JSON structure.

🧠 GIN is not a replacement for B-tree. They solve different search problems.

5.3 🗺️ GiST Index

GiST stands for Generalized Search Tree. It's a flexible indexing framework for different kinds of data and search operations. One great example is geographical data.

Imagine finding "all users within 5 km of Bengaluru Palace". That's a spatial search, and a B-tree isn't designed to efficiently handle every kind of spatial relationship. GiST can support spatial indexing through extensions such as PostGIS.

CREATE INDEX idx_locations_geom ON locations USING GIST(geom); 

A spatial query:

SELECT * FROM locations WHERE ST_DWithin( geom, ST_SetSRID(ST_MakePoint(77.59, 12.97), 4326)::geography, 5000 ); 

This finds locations within 5,000 metres of the point, assuming geom is stored with a geography-compatible coordinate reference system.

Where is GiST useful? Geographic and spatial searches, geometry operations, range types, and certain similarity searches using suitable operator classes.

⚖️ B-tree vs GIN vs GiST

🎯 Interview takeaway:

• B-tree → IDs, emails, dates, range searches
• GIN → arrays, JSONB, full-text search
• GiST → spatial and specialized searches

📝 PostgreSQL also has BRIN and hash indexes. You may meet them in advanced work, but B-tree, GIN and GiST are the important three here.

6. 🧱 Composite Indexes

Another VERY important concept. Take this table:

You frequently run:

SELECT * FROM posts WHERE user_id = 101 ORDER BY created_at DESC; 

That's two operations: find posts of user 101, then sort them newest first. You could create two separate indexes:

CREATE INDEX idx_posts_user ON posts(user_id); CREATE INDEX idx_posts_created ON posts(created_at); 

But PostgreSQL might need to combine index information or do extra sorting. There's a better approach.

6.1 🤔 What is a composite index?

📌 A composite index is an index created using multiple columns.

CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC); 

One index, two columns. Conceptually it's organized by user_id first, then created_at:

user_id = 101 ├── Nov 5 ├── Nov 3 └── Nov 1 user_id = 102 └── Nov 2 user_id = 103 └── Nov 4 

The actual index is ordered by the first column, then the second column within matching first-column values. PostgreSQL can potentially find a user's posts and retrieve them in date order without a separate sorting step.

6.2 ⚠️ Column order matters!

CREATE INDEX idx_posts_user_created ON posts(user_id, created_at); 

This index is particularly useful for:

-- Both columns SELECT * FROM posts WHERE user_id = 101 AND created_at > '2026-01-01'; -- First column only SELECT * FROM posts WHERE user_id = 101; 

But what about this?

-- Second column only SELECT * FROM posts WHERE created_at > '2026-01-01'; 

A regular B-tree composite index is generally much less effective here, because it's ordered primarily by user_id.

📝 PostgreSQL 18 added some B-tree skip-scan capabilities, but don't assume a composite index is an equally good substitute for a separate index on its second column.

🔑 The Leftmost Prefix Rule

CREATE INDEX idx_example ON posts(user_id, created_at, is_deleted); 

This is a general guideline, not an absolute rule.

📍 When to create composite indexes

When your app repeatedly runs queries using the same combination of columns. A social feed might have:

SELECT * FROM posts WHERE user_id = 101 AND is_deleted = false ORDER BY created_at DESC LIMIT 20; 

A possible index:

CREATE INDEX idx_posts_user_deleted_created ON posts(user_id, is_deleted, created_at DESC); 

But always check your actual query patterns and data distribution before deciding the column order.

⚠️ More indexes do not automatically mean better performance.

7. 🐌 The N+1 Query Problem

🎯 One of the most common database performance problems in apps using ORMs such as SQLAlchemy, Django ORM and Hibernate.

Your home feed contains 10 posts. Each has post info, author info, comments and likes. A beginner might write:

posts = session.query(Post).limit(10).all() for post in posts: author = session.get(User, post.user_id) print(post.caption, author.username) 

Let's count the queries.

Step 1: fetch all posts (1 query)

SELECT * FROM posts LIMIT 10; 

Step 2: fetch each author (10 more queries)

SELECT * FROM users WHERE id = 101; SELECT * FROM users WHERE id = 102; SELECT * FROM users WHERE id = 103; -- ...and so on 

Total: 1 + 10 = 11 queries. This is the N+1 Query Problem:

• 1 query fetches the main records
• N additional queries fetch related data for each record

Fetch 100 posts and you might run 101 queries. Fetch 1,000 and you might run 1,001. That seriously affects performance.

7.1 🤔 Why is N+1 a problem?

If every query takes 10 ms:

10 posts: 11 queries × 10 ms = 110 ms 100 posts: 101 queries × 10 ms = 1010 ms 

(An illustration assuming sequential queries with equal latency. Real timings vary.) Every unnecessary database round trip adds overhead. Even if each query is individually fast, hundreds of calls make your API slow.

7.2 ✅ How do we solve N+1?

Solution 1: use a JOIN

SELECT posts.id, posts.caption, users.username FROM posts JOIN users ON posts.user_id = users.id LIMIT 10; 

PostgreSQL retrieves post and author information in one query.

Solution 2: eager loading in SQLAlchemy. SQLAlchemy supports two common strategies: joinedload() and selectinload().

A. joinedload()

from sqlalchemy import select from sqlalchemy.orm import joinedload stmt = ( select(Post) .options(joinedload(Post.author)) .limit(10) ) posts = session.scalars(stmt).all() 

SQLAlchemy loads posts and their authors using a JOIN, so related data arrives as part of the query. For collection relationships, joined loading can multiply result rows, and SQLAlchemy requires .unique() when consuming results in that case.

B. selectinload()

from sqlalchemy import select from sqlalchemy.orm import selectinload stmt = ( select(Post) .options(selectinload(Post.comments)) .limit(10) ) posts = session.scalars(stmt).all() 

First query:

SELECT * FROM posts LIMIT 10; 

Second query:

SELECT * FROM comments WHERE post_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10); 

Instead of 10 separate comment queries, SQLAlchemy fetches related comments in one batch. A major improvement. 🎉

⚖️ joinedload vs selectinload

Practical guideline:

• Use joinedload() for many-to-one or one-to-one relationships when appropriate
• Use selectinload() for one-to-many and many-to-many relationships in many common cases

💡 Neither is universally best. Measure the actual workload.

7.3 📱 N+1 in a social-feed app

For GET /feed, the response contains posts, authors, comments and like counts:

[ { "id": 1, "caption": "Beautiful day", "author": { "username": "deepika" }, "comments": [], "likes_count": 25 } ] 

A poor implementation queries all four things separately for every post. A better one:

1. Fetches posts using pagination
2. Eager-loads authors
3. Batch-fetches comments if needed
4. Calculates like counts using aggregation or a grouped query
5. Builds the response without additional queries inside a loop

SELECT post_id, COUNT(*) AS likes_count FROM likes WHERE post_id IN (1, 2, 3, 4, 5) GROUP BY post_id; 

That calculates counts for multiple posts in one query. This is the kind of thinking expected from a good backend developer! 🧠

8. ⚖️ Performance vs Storage Trade-offs

If indexes are so useful, why not index every column? Because indexes are not free.

8.1 💾 Indexes consume storage

posts table → 500 MB user_id index → 100 MB created_at index → 120 MB tags GIN index → 200 MB ----------------------------- Total → 920 MB 

Your table was 500 MB, but with three indexes storage grew to 920 MB in this simplified example. Real sizes depend on data types, values, table structure and index configuration.

8.2 🐢 Indexes can slow down INSERT

INSERT INTO posts(user_id, caption) VALUES (101, 'My new post'); 

Without indexes: Insert row → Done With indexes: Insert row ▼ Update user_id index ▼ Update created_at index ▼ Update other indexes ▼ Done 

Every relevant index needs maintenance, so more indexes can mean more write overhead.

8.3 🐢 Indexes can slow down UPDATE and DELETE

UPDATE posts SET user_id = 102 WHERE id = 10; 

If user_id is indexed, PostgreSQL must maintain that index as the indexed value changes. DELETE operations must also maintain index entries.

8.4 🐇 Indexes improve read performance

If your app frequently runs SELECT * FROM posts WHERE user_id = 101;, an appropriate index can significantly reduce the work needed to find those posts. So there's a trade-off:

Related posts

Industrial Communication — Ch.10: Factory Automation [EN]

STATERA Library · Series 07 · Volume 07 · Chapter 10 · EN Source: https://github.com/Statera-Library/07_Vol_07_Industria

0dev.to

Your product depends on one AI provider: when it cuts you off, what does your code do?

On October 28, an internal tool I wrote for my own prospecting stopped drafting. The provider was not down. My claude.ai

0dev.to

FreeBSD Capsicum requires a file-descriptor rights design

FreeBSD Capsicum requires a file-descriptor rights design Capsicum changes the security model for a FreeBSD process by m

0dev.to

I got tired of waiting for APIs to be ready, so I built a mock server that runs on my laptop

Every project I work on hits the same wall: the front end is ready, but the API it calls isn't. Or it is, but it's slow,

0dev.to

Come far trasformare a Claude la sua ricerca in una presentazione dentro la tua Note di osFoundry

Alla fine, una conversazione di Claude fa la sua ricerca, apre una delle tue Notes di osFoundry , aggiunge una nuova sch

0dev.to

Why I Built Syndicator: Automating Blog Distribution Across X, Bluesky, Mastodon, Threads, and Nostr on a $0 Cloudflare Worker

After moving my blog to EmDash CMS , getting an article live finally felt frictionless. I finish a draft, push it over M

0dev.to