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:
- Reading large portions of the table
- Filtering matching rows
- 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.
SOCIAL SHARE CARD GENERATOR