DEV Community

Mohammad Waseem
Mohammad Waseem

Posted on

Mastering Query Optimization in TypeScript: A Senior Architect’s Rapid Response

#optimization #typescript #performance

Tackling Slow Queries Under Pressure: A Senior Architect’s Approach with TypeScript

In high-pressure environments where data-driven decision-making and system performance are critical, slow database queries can cripple application responsiveness and user satisfaction. As a senior architect, I recently faced this challenge firsthand: diagnosing and optimizing sluggish queries within a tight deadline, all while leveraging TypeScript to maintain code quality and clarity.

Understanding the Context and Constraints

Our application relied heavily on complex SQL queries to fetch aggregated data. Over time, some queries started to slow down, impacting real-time features. The main constraints were:

  • Time: Less than 48 hours to diagnose and implement performance improvements.
  • Codebase: TypeScript code with ORM layer (e.g., TypeORM or Prisma).
  • Environment: Production database with high concurrency.

Given this scenario, our goal was to optimize these queries without major schema changes or downtime.

Step 1: Profiling and Identifying Bottlenecks

The first step involved profiling application logs and database execution plans. Using a combination of tools:

  • Enabled logging of slow queries (logSlowQueries: true) in PostgreSQL.
  • Used EXPLAIN ANALYZE directly on the SQL queries to identify unindexed scans, nested loops, or unnecessary joins. 
EXPLAIN ANALYZE SELECT ... FROM ... WHERE ...;
Enter fullscreen mode Exit fullscreen mode

In our case, we discovered that joins on large tables were causing full table scans, and some aggregations were recalculated redundantly.

Step 2: Optimizing Queries in TypeScript

Rather than rewriting the database schema—which was not feasible given the tight deadline—I focused on query-level optimizations using TypeScript. Key techniques included:

  • Using select and where clauses efficiently to limit fetched data.
  • Prefetching and caching frequent lookups.
  • Breaking complex queries into smaller, targeted ones.

For example, instead of fetching all data and filtering in the application, I optimized a query like this:

const users = await repository.createQueryBuilder('user')
    .select(['user.id', 'user.name'])
    .where('user.isActive = :active', { active: true })
    .getMany();
Enter fullscreen mode Exit fullscreen mode

This approach minimized the data transferred and utilized proper indexing.

Step 3: Using Index Hints and Query Tuning

Where supported, I ensured indexes were utilized effectively. For PostgreSQL, I verified index usage via EXPLAIN plans and applied CREATE INDEX to columns used in WHERE and JOIN clauses.

CREATE INDEX idx_user_isActive ON user(isActive);
Enter fullscreen mode Exit fullscreen mode

In TypeScript, I enforced the use of these indexes by making queries specific and leveraging the ORM's capabilities.

Step 4: Employing Materialized Views and Caching

For highly complex aggregations that didn't change often, I suggested materialized views:

CREATE MATERIALIZED VIEW recent_active_users AS
SELECT id, name, last_login
FROM user
WHERE isActive = true AND last_login > NOW() - INTERVAL '30 days';
Enter fullscreen mode Exit fullscreen mode

These were refreshed periodically, reducing query load.

In the application code, I used caching libraries like node-cache to store frequently accessed data temporarily.

import NodeCache from 'node-cache';
const cache = new NodeCache({ stdTTL: 600 });

async function getActiveUsers() {
  const cached = cache.get('activeUsers');
  if (cached) {
    return cached;
  }
  const data = await repository.createQueryBuilder('user')
      .where('user.isActive = :active', { active: true })
      .getMany();
  cache.set('activeUsers', data);
  return data;
}
Enter fullscreen mode Exit fullscreen mode

Final Thoughts

Under tight deadlines, focusing on query-level tuning within TypeScript allowed us to make immediate performance gains. Proper profiling, efficient query construction, indexing, and strategic caching proved crucial.

While more extensive schema modifications or database tuning could further improve performance, the combination of these techniques provided a rapid, effective solution that restored application responsiveness and user confidence.

In a fast-paced development environment, agility in diagnosing and applying such optimizations is essential—always guided by the principles of sound system architecture and sustainable performance practices.

🛠️ QA Tip

To test this safely without using real user data, I use TempoMail USA.

Top comments (0)