Month: March 2024

Welcome to Day 7.

Everything we’ve learned so far leads to this lesson.

Until now, you’ve learned:

• How to write SQL
• How JOINs work
• How GROUP BY works
• How indexes work
• How PostgreSQL chooses execution plans

Today, we’ll combine everything to solve real-world performance problems.

Today’s Goals

By the end of today, you’ll be able to answer questions like:

• Why is this query slow?
• Should I add an index?
• Should I rewrite the query?
• Is this a database problem or an application problem?
• How would I debug this in production?

These are exactly the kinds of discussions that happen in senior Rails interviews.

A Senior Engineer’s Workflow

Suppose your manager says:

“The Users page takes 8 seconds to load.”

A junior developer might immediately say:

“Let’s add an index.”

A senior developer thinks:

1. Is the query actually slow?
2. Which query is slow?
3. How much data is involved?
4. What is PostgreSQL doing?
5. Can I rewrite the query?
6. Do I need an index?
7. Is the application causing the problem?

Notice:

Adding an index is Step 6, not Step 1.

Our Practice Schema

Let’s build something closer to a real Rails application.

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;

Users

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    city TEXT
);

Products

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC(10,2),
    category TEXT
);

Orders

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    status TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

Order Items

CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INTEGER,
    price NUMERIC(10,2)
);

The Six-Step Performance Checklist

Every slow query investigation should start with this checklist.

Step 1 – Measure

Never optimise blindly.

Run:

EXPLAIN ANALYZE
SELECT ...

Step 2 – Understand the Business Question

Example:

Show the last 20 completed orders.

Don’t optimise before understanding what the query should do.

Step 3 – Read the Plan

Look for:

• Seq Scan
• Nested Loop
• Hash Join
• Sort
• Aggregate
• Bitmap Heap Scan

Step 4 – Find the Bottleneck

Ask:

• Which node took the most time?
• Which node processed the most rows?

Step 5 – Decide the Fix

Possible fixes:

• Better index
• Better SQL
• Better schema
• Better ActiveRecord
• Better pagination

Step 6 – Measure Again

Never assume the optimisation worked.

Always compare before and after.

Scenario 1 – Missing Index

Query:

SELECT *
FROM users
WHERE email='john@example.com';

Execution plan:

Seq Scan
rows=100000
actual rows=1

Question:

What’s wrong?

Diagnosis

No index on email.

Fix

CREATE INDEX idx_users_email
ON users(email);

Run again:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email='john@example.com';

Expect:

Index Scan

Scenario 2 – Wrong Index

Suppose the application runs:

SELECT *
FROM users
WHERE city='Chicago'
AND age=30;

Indexes:

(city)
(age)

Question:

Better solution?

Answer

Composite index:

CREATE INDEX idx_city_age
ON users(city, age);

Because the application almost always filters by both.

Scenario 3 – Sorting

Query:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;

Plan:

Seq Scan
↓
Sort
↓
Limit

Question:

Can we avoid sorting?

Solution

CREATE INDEX idx_orders_created_at_desc
ON orders(created_at DESC);

Now PostgreSQL can read the index in order.

Often:

Index Scan
↓
Limit

No Sort node.

Scenario 4 – N+1 Queries

Rails code:

orders = Order.limit(100)

orders.each do |order|
  puts order.user.name
end

SQL executed:

SELECT * FROM orders LIMIT 100;

Then:

SELECT * FROM users WHERE id=1;

SELECT * FROM users WHERE id=2;

…

100 additional queries.

Total

101 queries

Fix

Order.includes(:user)

Now:

SELECT * FROM orders;

SELECT *
FROM users
WHERE id IN (...);

Two queries.

Interview Question

Which is faster?

includes

or

joins

Answer:

They solve different problems.

Scenario 5 – OFFSET Pagination

Query:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20
OFFSET 100000;

Looks harmless.

But PostgreSQL must skip:

100000 rows

before returning:

20 rows

Large OFFSET values become increasingly expensive.

Better Solution

Keyset Pagination.

Instead of:

OFFSET 100000

Use:

WHERE created_at < '2026-07-01'
ORDER BY created_at DESC
LIMIT 20;

This lets PostgreSQL continue from the last seen row instead of counting through earlier rows.

Rails Example

Instead of:

Order.order(created_at: :desc)
     .offset(100000)
     .limit(20)

Use:

Order
  .where("created_at < ?", last_created_at)
  .order(created_at: :desc)
  .limit(20)

This is called keyset pagination or cursor pagination.

Scenario 6 – SELECT *

Query:

SELECT *
FROM users;

Returns:

id
name
email
city
address
bio
avatar
...

Suppose the page only displays:

• name
• city

Why fetch everything?

Better:

SELECT
    name,
    city
FROM users;

Rails:

User.select(:name, :city)

Scenario 7 – COUNT(*)

Suppose:

SELECT COUNT(*)
FROM orders;

On:

300 million rows

Question:

Can this be slow?

Yes.

Because PostgreSQL must count visible rows.

Unlike some databases, PostgreSQL generally doesn’t maintain an exact row count that’s instantly available for arbitrary COUNT(*).

Scenario 8 – DISTINCT

Query:

SELECT DISTINCT users.*
FROM users
JOIN orders
ON users.id=orders.user_id;

Question:

Why is DISTINCT needed?

Because JOIN duplicates users.

Could EXISTS express the requirement more directly?

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id=u.id
);

Sometimes that’s clearer.

Scenario 9 – Functions in WHERE

Query:

SELECT *
FROM users
WHERE LOWER(email)='john@example.com';

Normal email index?

Not useful.

Solution:

CREATE INDEX idx_lower_email
ON users(LOWER(email));

Expression index.

Scenario 10 – Too Many Indexes

Suppose:

users
↓
12 indexes

Problem?

Every INSERT must update:

Table
+
12 indexes

Indexes speed reads but slow writes.

Always consider the workload.

Optimization Decision Tree

When a query is slow, ask:

Is PostgreSQL scanning too many rows?
        │
        ▼
Yes
        │
        ▼
Would an index help?
        │
        ▼
Yes
        │
        ▼
Do I already have one?
        │
        ▼
No
        │
        ▼
Create the correct index.

But also ask:

Am I returning unnecessary data?
Am I sorting unnecessarily?
Am I joining unnecessarily?
Am I executing the query too many times?

Real Rails Optimization Example

Suppose this page loads slowly:

@orders = Order
            .where(status: "completed")
            .order(created_at: :desc)
            .limit(20)

Questions:

1. Is there an index on status?
2. Is there an index on created_at?
3. Would a composite index help?

Potential solution:

CREATE INDEX idx_orders_status_created_at
ON orders(status, created_at DESC);

Why?

Because the query filters by status and orders by created_at.

Senior Interview Exercise

Suppose you see:

SELECT *
FROM orders
WHERE user_id = 100
ORDER BY created_at DESC
LIMIT 20;

Which index would you create?

Many people answer:

(user_id)
(created_at)

A stronger answer is:

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

Because it supports both the filter and the ordering in one index.

Common Performance Mistakes

Mistake 1

Adding indexes without measuring.

Mistake 2

Using SELECT * everywhere.

Mistake 3

Ignoring N+1 queries.

Mistake 4

Using huge OFFSET values.

Mistake 5

Creating duplicate indexes.

Mistake 6

Ignoring EXPLAIN ANALYZE.

Senior-Level Mental Model

Every query has a “cost.”

The cost comes from:

Rows read
+
Rows sorted
+
Rows joined
+
Rows transferred
+
Application round trips

The goal of optimisation is to reduce one or more of these.

Practical Exercises

Exercise 1

Create:

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

Run:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id=1
ORDER BY created_at DESC
LIMIT 20;

Observe whether PostgreSQL can avoid an explicit Sort.

Exercise 2

Compare:

SELECT *
FROM users;

with:

SELECT name, city
FROM users;

Think about the amount of data returned.

Exercise 3

Write three versions of:

“Find users with completed orders.”

Using:

1. JOIN
2. EXISTS
3. IN

Then compare their execution plans.

Exercise 4

Find a query that performs a Seq Scan.

Add an appropriate index.

Run EXPLAIN ANALYZE again.

What changed?

Interview Case Study

Imagine you’re in a senior Rails interview.

The interviewer says:

“A customer reports that the Orders page takes 6 seconds to load.”

A strong answer isn’t:

“I’ll add an index.”

A stronger answer is:

1. Reproduce the issue.
2. Identify the SQL generated by ActiveRecord.
3. Run EXPLAIN ANALYZE.
4. Inspect scan types, joins, and sort operations.
5. Check existing indexes.
6. Decide whether the fix belongs in the SQL, indexes, ActiveRecord code, or schema.
7. Measure again after the change.

That systematic approach demonstrates senior-level thinking.

Homework

Build a small benchmark using your practice schema.

1. Populate:
   • 100,000 users
   • 500,000 orders
2. Measure these queries before and after adding indexes:
   • Find a user by email.
   • Find recent orders for a user.
   • Find completed orders.
   • Find users with no orders.
3. For each query, record:
   • Execution plan
   • Execution time
   • Scan type
   • Rows estimated
   • Rows returned
4. Explain why PostgreSQL chose each plan.

What’s Next?

At this point, you’re already covering topics that many experienced Rails developers never study in depth.

For Day 8, I recommend Window Functions:

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LAG()
• LEAD()
• Running totals
• Moving averages
• Top N per group

Window functions are common in reporting, analytics, and senior backend interviews because they solve problems that are difficult or inefficient with plain GROUP BY. Understanding them will significantly broaden your SQL toolkit.

Happy Learning! 🚀