Skip to main content
We chose an SQL database for our photo app, and it worked well at ten thousand users. As we scale toward millions of users, some queries begin to slow—pages that used to render in ~200 ms may take ~500 ms. This slowdown is common: as tables grow, certain queries require more work and become bottlenecks.
The image illustrates the problem of slow queries with an app server and database as the number of users reaches one million, resulting in slower feed loading times.
Why does this happen? Consider a query that fetches all photos uploaded by the user Alan. Without an index, the database must do a full table scan:
  • Was this photo uploaded by Alan? No.
  • Was this photo uploaded by Alan? No.
  • …
A full table scan checks every row and becomes increasingly inefficient as row count grows.
The image shows a diagram of an app querying a database table named "photo_details" to retrieve records where the column "posted_by" is equal to "alan." The query illustration highlights inefficiency in handling over a million rows.
What an index does
  • An index is a data structure (commonly a B-tree) that maps values in a column to the row locations where those values appear.
  • Think of it like the index at the back of a book: instead of reading every page, you look up a keyword and jump directly to the relevant pages.
If we create an index on the posted_by column in photo_details, the database can quickly determine that Alan’s photos are at row 822, row 15,000, and row 100,000, and then fetch only those rows.
The image illustrates a database indexing concept, showing how an app server accesses posts by specific users, using an index to quickly jump to relevant database rows for photo details.
How the query planner uses an index
  1. The planner checks available indexes for columns used in WHERE, JOIN, ORDER BY, or GROUP BY.
  2. If a matching index exists, the planner uses it to find row pointers quickly.
  3. The database fetches only those rows, avoiding unrelated rows and reducing I/O.
This index lives inside the database engine (often a B-tree) and is transparent to application code.
The image illustrates how a database query works, showing a process where a request for photos posted by "Alan" passes through an index to quickly fetch corresponding rows from the "photo_details" table.
Quick SQL examples
  • Create a simple single-column index:
  • Create a composite index (useful when queries filter on multiple columns). Column order matters:
  • PostgreSQL covering index example (stores extra columns in the index so the query can be satisfied from the index alone):
When to add an index
  • Use monitoring and the database’s query planner to identify slow queries first.
  • Run EXPLAIN or EXPLAIN ANALYZE to see how a query is executed and which columns are causing scans.
  • Add an index targeted specifically to fix that query pattern (e.g., WHERE, JOIN, or ORDER BY columns).
  • Re-measure and iterate.
Index trade-offs and considerations
Index the columns used by your slow queries—typically those in WHERE, JOIN, ORDER BY, and GROUP BY. Use EXPLAIN / EXPLAIN ANALYZE and slow-query logs to identify which queries will benefit before adding an index.
Best-practice workflow
  1. Start with the primary key index only (or a minimal set).
  2. Monitor performance: slow-query logs, APM tools, or profiling.
  3. For a slow query, run EXPLAIN / EXPLAIN ANALYZE and inspect the execution plan.
  4. Add an index targeted to the query pattern (single-column, composite, or covering).
  5. Re-run the query and observe performance and write impact; iterate as needed.
Do not create indexes blindly. Adding many indexes increases write latency and storage cost. Start with no or few indexes, identify slow queries, and add indexes targeted at those queries only.
How many indexes should a table have? There’s no one-size-fits-all number. Many production tables have 3–5 well-chosen indexes, but the right count depends on query patterns and write throughput. The pragmatic approach is the workflow above: measure, add targeted indexes, and re-measure. References and further reading By following a measurement-driven approach to indexing—identify slow queries, inspect plans, and add focused indexes—you’ll improve read performance while keeping write overhead and storage costs under control.

Watch Video