DEV Community: John Kiprito

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

John Kiprito — Wed, 15 Jul 2026 21:41:16 +0000

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.

PostgreSQL Best Practices for Backend Developers

John Kiprito — Wed, 15 Jul 2026 20:25:24 +0000

Design Your Database Schema Carefully

A well-designed schema is the foundation of a successful application. Instead of storing everything in a single table, normalize your data into related tables.

Benefits:

Reduces duplicate data
Improves consistency
Makes maintenance easier

A clean database structure will save countless hours as your application grows.

Use Indexes Wisely

Indexes can dramatically improve query performance, especially when searching large datasets.

For example, frequently searched columns such as email addresses, usernames, and foreign keys often benefit from indexing.

Benefits:

Faster query execution
Better application responsiveness
Improved scalability

However, avoid creating unnecessary indexes because they consume storage and can slow down insert and update operations.

Always Use Parameterized Queries

Security should always be a priority. Parameterized queries help prevent SQL injection attacks and improve database security.

Benefits:

Protects against malicious input
Improves application security
Better query optimization

Never build SQL queries by directly concatenating user input.

Use Proper Data Types

Choosing the correct data type for each column improves performance and data integrity.

Examples include:

INTEGER for numeric values
BOOLEAN for true/false values
TIMESTAMP for dates and times
UUID for unique identifiers
JSONB for semi-structured data

Using appropriate data types reduces storage requirements and improves query performance.

Take Advantage of JSONB

One of PostgreSQL's most powerful features is JSONB support. It allows developers to store and query structured JSON data efficiently while maintaining the advantages of a relational database.

Use cases:

Product metadata
User preferences
Dynamic application settings
Event tracking

JSONB provides flexibility without sacrificing performance.

Optimize Queries Regularly

As applications grow, database performance becomes increasingly important.

Regularly review:

Slow queries
Complex joins
Large table scans
Missing indexes

Tools like EXPLAIN ANALYZE can help identify bottlenecks and opportunities for optimization.

Use Transactions for Critical Operations

Transactions ensure data consistency and reliability.

Common examples include:

Financial transfers
Order processing
Inventory updates
Multi-step business operations

Transactions guarantee that either all operations succeed or none are applied, preventing inconsistent data.

Implement Database Backups

Every production system should have a backup strategy.

Benefits:

Protection against accidental deletion
Disaster recovery
Business continuity

Regular backups should be tested to ensure they can be restored successfully when needed.

Monitor Database Performance

Monitoring helps identify problems before users notice them.

Key metrics include:

Query execution times
Connection counts
CPU usage
Memory consumption
Database growth

Monitoring allows teams to proactively maintain system performance.

Use Connection Pooling

Creating a new database connection for every request can become expensive.

Connection pooling helps:

Reduce database overhead
Improve performance
Support higher traffic loads
Increase application scalability

Most modern backend frameworks and libraries provide built-in support for connection pooling.

Final Thoughts

PostgreSQL is much more than a traditional relational database. By following best practices such as proper schema design, indexing, query optimization, transactions, connection pooling, and performance monitoring, developers can build secure, scalable, and efficient applications.

Mastering these techniques early will help you create production-ready systems that remain reliable as your applications and user base continue to grow.