My progress
Menu

Backend Developer · Focus area 2: Databases and caching

BE3: Fast queries

Goal: find out why a request is slow and fix the database side: read query plans, add the indexes that matter, remove the "query per item" pattern, page and pool connections sensibly, and know when one database isn't enough.

You'll need: BE2, Module 57 (transactions and indexes), the TiffinBox kit with its data set up, and psql.

Where this is used: a menu that takes two seconds to load during the lunch rush, a dashboard that times out at month end, or a search that slows down as the business grows. Most backend slowness is a few queries doing far more work than they need to.

How it differs: a frontend developer makes the page feel fast in the browser (FE4). A backend developer makes the server's answer fast, and keeps it fast as data grows from thousands of rows to millions.

Time: reading about 2 hours · practice (the Try it tasks) about 3 hours · assignment about 3 to 4 hours.

Lessons

  1. 1How the database runs a queryReading 15–25 minutes, plus practice✓
  2. 2Indexes that earn their keepReading 15–25 minutes, plus practice✓
  3. 3The query-per-item trapReading 15–25 minutes, plus practice✓
  4. 4Connections and big result setsReading 15–25 minutes, plus practice✓
  5. 5When one database isn't enoughReading 15–25 minutes, plus practice✓

🛠️Assignment: A faster TiffinBox menu

This prepares Project 2.

  1. Record EXPLAIN ANALYZE for the starter menu's per-dish queries, and the query count from /_stats.
  2. Write the combined menu query and prove its output matches the starter's for three kitchens.
  3. Add at least one index as a migration, and show its effect, positive or not, with numbers.
  4. Run the load test before and after, and explain the change in terms of round trips and pooled connections.
  5. Write a one-page scaling note: which of replicas, partitioning or sharding TiffinBox might need first, and the trigger.

Self-check before you finish:

✅Module check

Explain in a few sentences, in your own words:

How do you make a slow database-backed endpoint fast, and keep it fast as data grows?

Show a good answer

A good answer mentions: measuring first with EXPLAIN ANALYZE on realistic data and counting queries per request; indexes chosen from real filters, joins and sorts, in the right column order, including foreign keys, and checked for use; replacing N+1 loops with one set-based query using joins, aggregates and LEFT JOIN; small connection pools, short use of connections and paging or streaming large results; and replicas, partitioning and sharding as later steps for measured needs, with their costs such as replication lag and cross-shard queries.

📚Official references

Official documentation and guidance for this module's topics, chosen on 2 November 2026. Tools, laws and standards change, so check the current version before you rely on a detail. If a link has moved, search the same site.

📝Module quiz

10 questions · 10 marks each · 100 marks in all · pass mark 70% · retry as often as you like ·

1. An endpoint is fast on a developer's laptop with 300 rows and takes 6 seconds in production with 5 million. What should the developer do first?

Why each option is right or wrong

  • “Add an index on every column of the table” (Incorrect): Indexes without evidence slow writes, and may not match the slow query at all.
  • “Move to a bigger database server” (Incorrect): That costs money before anyone knows what is slow.
  • “Cache the response for a day so the database isn't touched” (Incorrect): Caching may help later, but a day-old answer can be wrong, and the cause is still unknown.
  • “Run EXPLAIN ANALYZE on realistic data and find the costly step” is correct.

    Plans on tiny data look nothing like plans on millions of rows. Measuring shows the real step to fix.

    • Look for: sequential scans on large tables and many rows removed by filter.
    • Use: a copy with production-like volume (synthetic is fine).

📖 Revise: Lesson 1: How the database runs a query

Question ID sp-backend-03-q1 · Report a mistake in this question
2. A plan shows rows=12 estimated but actual rows=480000 for a step, and the planner chose a nested loop. What's a likely cause?

Why each option is right or wrong

  • “The query has a syntax error” (Incorrect): A syntax error would stop the query from running at all.
  • “Nested loops are the wrong choice here and should be disabled” (Incorrect): They're good for small inputs; the issue is that the input was misjudged.
  • “The server needs more RAM before it can count rows properly” (Incorrect): Counting isn't limited by RAM; the estimate comes from statistics.
  • “Stale statistics; run ANALYZE and check the plan again” is correct.

    The planner chose the plan from a bad row estimate. Fresh statistics usually give better estimates and a better join.

    • When: after loading or deleting lots of data.
    • Normally: autovacuum keeps statistics current.

📖 Revise: Lesson 1: How the database runs a query

Question ID sp-backend-03-q2 · Report a mistake in this question
3. A query filters WHERE kitchen_id = $1 AND placed_at >= $2. Which index helps it most?

Why each option is right or wrong

  • “An index on placed_at alone” (Incorrect): It narrows by date but then reads every kitchen's recent orders.
  • “Separate indexes on every column of orders, just in case” (Incorrect): Many single-column indexes slow writes and are a weaker fit than one matching index.
  • “An index on (placed_at, kitchen_id), putting the range column first” (Incorrect): With the range column first, the index can't jump straight to one kitchen.
  • “An index on (kitchen_id, placed_at)” is correct.

    Equality column first, then the range: the database jumps to one kitchen and reads only its recent orders.

    • Check: the plan shows an index scan using it.
    • Remember: add it as a migration.

📖 Revise: Lesson 2: Indexes that earn their keep

Question ID sp-backend-03-q3 · Report a mistake in this question
4. A team added indexes on order_items (menu_item_id) and ratings (menu_item_id), and their 62-query menu page went from 440 ms to 96 ms. What does this tell them?

Why each option is right or wrong

  • “The page is now as fast as it can be” (Incorrect): 62 sequential queries still cost far more than one set-based query.
  • “Indexes make every query at least ten times faster” (Incorrect): Gains vary with data and query; here it was about four times.
  • “The indexes should be removed because they didn't help enough” (Incorrect): They did help; the question is what to do next.
  • “Indexes helped; the 62 round trips are still the main cost” is correct.

    The right indexes made each lookup faster, yet every request still makes 62 round trips. Removing the N+1 pattern cuts more.

    • Measured in the kit: one combined query took about 24 ms without the extra indexes.
    • Lesson: measure each change; here, the best fix wasn't an index.

📖 Revise: Lesson 2: Indexes that earn their keep

Question ID sp-backend-03-q4 · Report a mistake in this question
5. A class-list page fetches 40 students, then runs one query per student for their attendance and one for their marks. What's the pattern, and the fix?

Why each option is right or wrong

  • “A missing cache; store each student's marks in Redis for a day” (Incorrect): Caching hides the cost for repeat visits but every first load still makes 81 queries.
  • “Too few connections; raise the pool to 100” (Incorrect): More connections don't reduce the number of round trips.
  • “Run all 80 per-student queries at the same time with Promise.all” (Incorrect): That grabs many connections at once and starves other requests, while still making 81 queries.
  • “N+1 queries; use one query with joins and grouping” is correct.

    1 + 40 × 2 = 81 queries per page. One set-based query computes attendance and marks for all 40 at once.

    • Keep: students with no marks yet, using LEFT JOIN.
    • Measure: queries per request before and after.

📖 Revise: Lesson 3: The query-per-item trap

Question ID sp-backend-03-q5 · Report a mistake in this question
6. After rewriting a menu with one combined query, dishes that nobody has ordered yet disappear from the menu. What's the likely cause?

Why each option is right or wrong

  • “The cache still holds an old menu” (Incorrect): A stale cache shows old data, not fewer dishes than before.
  • “The dishes were deleted by mistake during the rewrite” (Incorrect): The dishes are in the table; they're missing only from the query's output.
  • “The GROUP BY sorted them to the end of the list” (Incorrect): Grouping changes rows, not their visibility; sorting doesn't remove rows.
  • “An inner JOIN to the orders counts drops dishes without matches” is correct.

    Inner joins keep only rows with a match on both sides. LEFT JOIN with coalesce(n, 0) keeps every dish.

    • Test: compare the new output with the old for several kitchens.
    • Same for: ratings.

📖 Revise: Lesson 3: The query-per-item trap

Question ID sp-backend-03-q6 · Report a mistake in this question
7. Under load, an app's requests time out waiting for a database connection. Each request makes 30 quick queries in sequence. The team proposes raising the pool from 10 to 200. What's the better first step?

Why each option is right or wrong

  • “Raise it to 200 as proposed, since more connections serve more users” (Incorrect): The database still has limited cores; hundreds of connections compete and add overhead.
  • “Remove the pool and open a fresh connection for each query” (Incorrect): New connections are slow and uncontrolled numbers can overwhelm the database.
  • “Add Promise.all so the 30 queries run in parallel on 30 connections” (Incorrect): That uses even more connections per request and makes the queue worse.
  • “Cut the round trips each request makes, so connections free up sooner” is correct.

    The pool empties because each request holds a connection through 30 round trips. Fewer, set-based queries shorten that hold.

    • Then: measure again; a modest pool plus PgBouncer suits many instances.
    • Also: set a timeout for getting a connection.

📖 Revise: Lesson 4: Connections and big result sets

Question ID sp-backend-03-q7 · Report a mistake in this question
8. An "export all orders" button loads every order into an array before writing a CSV. With 3 million orders, the server runs out of memory. What's the fix?

Why each option is right or wrong

  • “Add more memory to the server so the whole export fits in RAM” (Incorrect): It postpones the crash until the data grows again.
  • “Limit the export to the first 1,000 orders” (Incorrect): The business needs the full export; this only hides the problem.
  • “Cache the export in Redis” (Incorrect): The whole export still has to be built in memory first.
  • “Stream rows from a cursor, writing each as it arrives” is correct.

    Streaming keeps memory flat however many rows there are, and the download starts sooner.

    • Or: page through with a cursor in batches.
    • For very large exports: generate in the background and email a link.

📖 Revise: Lesson 4: Connections and big result sets

Question ID sp-backend-03-q8 · Report a mistake in this question
9. A customer places an order, the app reloads their order list, and the new order is missing for a few seconds. The list is served from a read replica. Why?

Why each option is right or wrong

  • “The order failed to save, and the app showed success by mistake” (Incorrect): It appears a few seconds later, so it was saved.
  • “Replicas only copy data once an hour” (Incorrect): Streaming replicas usually catch up within seconds, not hours.
  • “The app's browser cache stored an empty list” (Incorrect): The list isn't empty; only the newest order is missing, matching lag.
  • “Replication lag; the replica hadn't received the write yet” is correct.

    Replicas apply changes slightly after the main database. Reads that must include the user's own latest write should go to the main database.

    • Pattern: read-your-own-writes for a short time after a change.
    • Replicas suit: reports and pages where seconds-old data is fine.

📖 Revise: Lesson 5: When one database isn't enough

Question ID sp-backend-03-q9 · Report a mistake in this question
10. A new app with 2,000 users plans to shard its database across 8 servers by city before launch. Most queries are "everything for one user". What's the best advice?

Why each option is right or wrong

  • “Shard by city as planned, since sharding early saves work later” (Incorrect): Sharding adds cost now, and the key doesn't match the main access pattern.
  • “Shard by user ID across 64 servers instead, to be even more ready for growth” (Incorrect): More shards add even more operating work for traffic one server can handle.
  • “Move all data into Redis, which is faster than any database” (Incorrect): Redis isn't a replacement for a relational database's constraints and queries.
  • “One indexed database, adding replicas or shards only when measured” is correct.

    Efficient queries, indexes, caching and a replica serve a lot of traffic. Sharding brings cross-shard queries and rebalancing; take it on for a real, measured need.

    • If needed later: choose the shard key from the most common queries.
    • Record: the trigger that would justify each step.

📖 Revise: Lesson 5: When one database isn't enough

Question ID sp-backend-03-q10 · Report a mistake in this question

ProLearners Assist