Indexing & Query Optimization: How to Make Your Database Fast

Mhamd Ghanoum — Tue, 09 Jun 2026 17:14:40 +0000

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;

The database scans the entire table.

With an index:

CREATE INDEX idx_vendors_id ON vendors(id);

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);

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);

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;

Expression Index
Index on computed values:

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

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;

Not:

SELECT * FROM vendors;

✔️ Use wildcards only at the end
Good:

WHERE name LIKE 'Sam%'

Bad:

WHERE name LIKE '%Sam%'

✔️ Use LIMIT when previewing data

SELECT * FROM orders LIMIT 50;

✔️ 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

SQL vs NoSQL — How to Choose the Right Database for Your System

Mhamd Ghanoum — Fri, 05 Jun 2026 10:32:01 +0000

Hello everyone!
My name is Mhamd, a backend engineer who loves building scalable systems and explaining complex backend topics in a simple, practical way.

If you enjoy system design, backend architecture, or want to exchange ideas — feel free to reach out!

Why This Topic Matters?
When designing a new system, one of the most important architectural decisions is choosing the right type of database.

This choice affects:

performance
scalability
consistency
development speed
and even cost

In this article, I’ll break down SQL vs NoSQL, when to use each, and how I combine both in real-world projects.

SQL — Structured, Relational, and Consistent
SQL databases (PostgreSQL, MySQL, SQL Server…) rely on:

A predefined schema
Strong consistency
ACID transactions
Clear relationships between tables When SQL is a great fit? Use SQL when:
Your data structure is known in advance
You need strong consistency
You have complex relationships (Users ↔ Orders ↔ Products)
You need powerful queries and joins

Example: E‑commerce
Entities like:

Products
Users
Orders
Payments

…have clear relationships and require consistent transactions.
SQL is perfect here.

But what about Amazon‑scale systems?
As the system grows globally:

traffic explodes
data becomes distributed
sharding becomes complex
consistency becomes harder
SQL still works — but requires advanced architecture.

NoSQL — Flexible, Fast, and Horizontally Scalable
NoSQL is not one technology — it’s a family:

Document stores (MongoDB)
Key‑value stores (Redis)
Wide‑column stores (Cassandra)
Graph databases (Neo4j)

When NoSQL is a great fit?
Use NoSQL when:

Your data is semi‑structured or unstructured
You need high write throughput
You need horizontal scaling
You’re building real‑time systems
Your schema changes frequently

Examples

Analytics
Real‑time feeds
Chat systems
IoT
Caching layers

Using Both Together — The Hybrid Approach
Modern systems rarely fit into one category.
In my projects, I often combine SQL + NoSQL.

Example: My Delivery Backend System
PostgreSQL for:

Orders
Products
Users
State machines
Auditing
Because these require structure and consistency.

Redis for:

Real‑time deliverer tracking
WebSocket events
Caching
Fast lookups
Because these require speed and flexibility.

What Happens When You Use GraphQL on Top of PostgreSQL?

When you put GraphQL above PostgreSQL:

PostgreSQL remains the source of truth GraphQL becomes a flexible API layer The client can request exactly the data it needs No overfetching or underfetching.Perfect for dashboards and mobile apps

This combination gives you:

SQL consistency
GraphQL flexibility
A single endpoint for all queries

Conclusion
SQL is perfect for structured, relational, consistent data

NoSQL is perfect for speed, flexibility, and large-scale distributed systems

Most real systems use both.
The right choice depends on your data and your system’s behavior — not on trends.

If you enjoyed this article
I’ll be writing more about:

system design
caching
event-driven architecture
microservices
real-time backend patter ns

Follow me on DEV Community — and feel free to share your thoughts or questions!

DEV Community: Mhamd Ghanoum

Indexing & Query Optimization: How to Make Your Database Fast

SQL vs NoSQL — How to Choose the Right Database for Your System