Indexing & Query Optimization: How to Make Your Database Fast

Indexing & Query Optimization: How to Make Your Database Fast

# database# indexing# postgres
Indexing & Query Optimization: How to Make Your Database FastMhamd Ghanoum

Imagine you have a huge vendors table in an e‑commerce system, and thousands of orders are created...

Imagine you have a huge vendors table in an e‑commerce system, and thousands of orders are created every second.
Every time a new order comes in, the system needs to:

  • check if the vendor exists
  • check if the vendor is active
  • check if the product belongs to that vendor

If your table is large, the database will scan the entire table to find the vendor.
This is called a full table scan, and it becomes extremely slow as your data grows.

This is where indexes save your system.

🟦 What Is an Index?
An index is a small, optimized data structure that tells the database engine exactly where a specific value is located.

  • Instead of scanning the whole table, the database:
  • looks at the index
  • finds the exact row location
  • jumps directly to it

This turns a slow O(n) search into a fast O(log n) lookup.

🟩 Example: Indexing the Vendor ID
Without an index:

SELECT * FROM vendors WHERE id = 123;
Enter fullscreen mode Exit fullscreen mode

The database scans the entire table.

With an index:

CREATE INDEX idx_vendors_id ON vendors(id);
Enter fullscreen mode Exit fullscreen mode

Now the database:

  • checks the index
  • finds the row instantly
  • returns the result in milliseconds

This is the difference between a system that collapses under load…
and a system that handles millions of requests smoothly.

🟧 Why Not Index Everything? (The Trade‑Off)
Indexes make reads faster,
but they make writes slower.

Every time you:

  • INSERT
  • UPDATE
  • DELETE …the database must also update every index.

Too many indexes = slow writes = bottlenecks.

So we only index the hot paths — the most frequently queried fields.

🟪 Clustered vs Non‑Clustered Indexes (PostgreSQL)
Clustered Index
The table is physically sorted by the index

  • Fast range queries
  • Only one per table
  • Non‑Clustered Index
  • Separate structure
  • Points to the actual rows
  • You can have many of them
  • PostgreSQL uses B‑Tree indexes by default.

🟦 Other Index Types in PostgreSQL (Short Overview)
PostgreSQL supports several index types optimized for different use cases:

B‑Tree (default)
Best for equality and range queries.

Hash Index
Fast equality lookups (=), but limited.

GIN Index
Perfect for:

  • JSONB
  • Arrays
  • Full‑text search
  • Tags

Example:

CREATE INDEX idx_products_tags ON products USING gin(tags);

Enter fullscreen mode Exit fullscreen mode

GiST Index
Used for:

  • Geospatial data
  • Distances
  • Geometric shapes

Example (useful in delivery apps):

CREATE INDEX idx_locations_gist ON locations USING gist(geo_point);
Enter fullscreen mode Exit fullscreen mode

BRIN Index
Great for very large tables with naturally ordered data (logs, events, time‑series).

Partial Index
Index with a condition:

CREATE INDEX idx_active_vendors ON vendors(id) WHERE active = true;
Enter fullscreen mode Exit fullscreen mode

Expression Index
Index on computed values:

CREATE INDEX idx_lower_email ON users (LOWER(email));
Enter fullscreen mode Exit fullscreen mode

This flexibility is one of PostgreSQL’s biggest strengths.

🟨 Query Optimization Tips
Indexing is powerful, but you also need efficient queries

✔️ Select only the columns you need

SELECT id, name FROM vendors;
Enter fullscreen mode Exit fullscreen mode

Not:

SELECT * FROM vendors;
Enter fullscreen mode Exit fullscreen mode

✔️ Use wildcards only at the end
Good:

WHERE name LIKE 'Sam%'
Enter fullscreen mode Exit fullscreen mode

Bad:

WHERE name LIKE '%Sam%'

Enter fullscreen mode Exit fullscreen mode

✔️ Use LIMIT when previewing data

SELECT * FROM orders LIMIT 50;
Enter fullscreen mode Exit fullscreen mode

✔️ Run heavy queries during off‑peak hours
Especially analytics or batch jobs.

🟫 Conclusion
Indexes make your reads extremely fast

But they slow down writes

Use them wisely on the most important fields

Combine indexing with good query practices

Always measure performance before and after

Indexing is one of the simplest ways to make your backend feel instant, even under heavy load