PostgreSQL Query Optimization: Reducing API Response Time from 2.8 Seconds to 74 Milliseconds

Introduction

Performance issues rarely announce themselves with obvious errors. More often, they appear as subtle increases in latency that gradually worsen as data volume grows.

While working on a Node.js and PostgreSQL application, I noticed a user-facing API endpoint consistently taking approximately 2.8 seconds to respond. The endpoint was functionally correct, but its performance was unacceptable for production use.

This article walks through the investigation process, root cause analysis, optimization strategy, and the changes that reduced response time to 74 milliseconds.

The Problem

The API endpoint was responsible for retrieving customer orders.

GET /api/orders?customerId=100

At first glance, nothing seemed unusual.

However, monitoring revealed:

• Metric Before
• Average Response Time 2.8s
• Database Execution Time 2.5s
• Records in Orders Table 1.2M+
• User Experience Poor

As traffic increased, response times continued to grow.

The objective was clear:

Identify the bottleneck and restore acceptable API performance.

Application Stack

• Node.js
• Express.js
• PostgreSQL
• TypeScript
• REST APIs

Database size:

Orders Table: 1.2+ million rows
Customers Table: 350,000+ rows
Initial Investigation

The first assumption was that application logic might be responsible.

I reviewed:

• Express middleware
• API controller logic
• Network latency
• PostgreSQL logs

No significant issues appeared in the application layer.

The next step was analyzing the database query itself.

The Query

The endpoint executed the following query:

SELECT *
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC;

The query looked straightforward.

However, execution time suggested otherwise.

Query Analysis Using EXPLAIN ANALYZE

To understand PostgreSQL's execution strategy, I used:

EXPLAIN ANALYZE

SELECT *
FROM orders
WHERE customer_id = 100
ORDER BY created_at DESC;

The output revealed the real problem.

Seq Scan on orders

PostgreSQL was performing a sequential scan across the entire table.

Instead of locating matching records efficiently, the database was examining over one million rows.

Root Cause

The filtering column lacked an index.

Because customer_id was not indexed, PostgreSQL had no efficient path to locate matching records.

Every request required:

1. Reading large portions of the table
2. Filtering matching rows
3. Sorting results afterward

As table size increased, query performance degraded significantly.

The Optimization Strategy
Step 1: Create an Index

The most obvious improvement was indexing the search column.

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Performance improved immediately.

However, there was still room for optimization.

Step 2: Optimize Sorting

The query also sorted results by creation date.

A composite index was created:

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at DESC);

This allowed PostgreSQL to:

• Filter records efficiently
• Return results in sorted order
• Avoid additional sorting operations

Validation

After applying the changes, I reran:

EXPLAIN ANALYZE

The execution plan changed from:

Seq Scan

to:

Index Scan

PostgreSQL was now using the new index correctly.

Results

---

Before Optimization

Metric Value
API Response Time 2.8s
Query Execution Sequential Scan
CPU Usage High
User Experience Poor

After Optimization

Metric Value
API Response Time 74ms
Query Execution Index Scan
CPU Usage Reduced
User Experience Excellent
Improvement
2.8 seconds → 74 milliseconds

Performance Improvement: ~97.4%

The endpoint became approximately 38 times faster.

Lessons Learned

1. Database Performance Is Application Performance

Even perfectly written backend code cannot compensate for inefficient database queries.

Understanding query execution plans is one of the most valuable skills for backend developers.

2. Measure Before Optimizing

Assumptions often lead developers in the wrong direction.

Tools such as:

EXPLAIN ANALYZE

provide objective evidence of where bottlenecks exist.

3. Indexes Are Powerful but Strategic

Indexes dramatically improve read performance.

However, they should be created thoughtfully because they introduce additional storage and write overhead.

4. Small Changes Can Deliver Massive Impact

The final code change consisted of only a few lines of SQL.

The majority of the effort was understanding the problem correctly.

Conclusion

This optimization reinforced an important engineering principle:

Performance improvements are often achieved through better understanding, not more code.

By analyzing query execution plans, identifying a missing index, and implementing a targeted optimization strategy, API response time dropped from 2.8 seconds to 74 milliseconds.

For backend engineers working with PostgreSQL, regularly reviewing query plans and indexing strategies can prevent scalability bottlenecks long before they impact users.