How to Optimize Database Queries for Faster Dynamic Pages

Your frontend can be perfectly optimized, but if your server takes 2 seconds to query the database, your TTFB will always be 2+ seconds. Database optimization is the foundation of fast dynamic pages.

The TTFB Breakdown

For dynamic pages, TTFB includes:

TTFB = Network latency + Server processing + Database queries

On most apps, database queries are 60-80% of server processing time. Cutting query time directly reduces TTFB.

Finding Slow Queries

Application-Level Logging

// Simple query timing wrapper
async function queryWithTiming(sql, params) {
  const start = performance.now();
  const result = await db.execute(sql, params);
  const duration = performance.now() - start;
  
  if (duration > 100) {
    console.warn(`Slow query (${duration.toFixed(0)}ms):`, sql);
  }
  
  return result;
}

Database Slow Query Log

-- PostgreSQL: enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = 100; -- Log queries > 100ms

-- MySQL: enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;

ORM Query Analysis

Most ORMs have query logging:

// Drizzle: enable logging
const db = drizzle(client, { logger: true });

// Prisma: enable query logging
const prisma = new PrismaClient({
  log: ['query', 'warn', 'error'],
});

Top Optimization Techniques

1. Add Indexes

The single most impactful optimization. Without an index, the database scans every row:

-- Before: Full table scan (500ms for 1M rows)
SELECT * FROM perf_logs WHERE page_id = 'abc' AND scanned_at > '2026-01-01';

-- Add composite index
CREATE INDEX idx_perf_logs_page_scanned 
  ON perf_logs(page_id, scanned_at);

-- After: Index scan (2ms)

Rule of thumb: Add indexes for every column used in WHERE, JOIN, or ORDER BY clauses.

2. Select Only Needed Columns

-- BAD: fetches all columns (including large text/blob fields)
SELECT * FROM blog_posts WHERE published = true;

-- GOOD: fetches only needed columns
SELECT id, title, slug, published_at FROM blog_posts WHERE published = true;

3. Use LIMIT and Pagination

-- BAD: returns all 10,000 rows
SELECT * FROM products ORDER BY created_at DESC;

-- GOOD: paginate
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 40;

4. Fix the N+1 Query Problem

// BAD: N+1 (1 query for posts + N queries for authors)
const posts = await db.query('SELECT * FROM posts LIMIT 20');
for (const post of posts) {
  post.author = await db.query('SELECT * FROM users WHERE id = ?', [post.author_id]);
}

// GOOD: JOIN (1 query total)
const posts = await db.query(`
  SELECT posts.*, users.name as author_name
  FROM posts
  JOIN users ON users.id = posts.author_id
  LIMIT 20
`);

5. Cache Frequent Queries

import { LRUCache } from 'lru-cache';

const cache = new LRUCache({ max: 500, ttl: 60_000 }); // 60s TTL

async function getPopularPosts() {
  const cached = cache.get('popular-posts');
  if (cached) return cached;
  
  const posts = await db.query(
    'SELECT * FROM posts ORDER BY views DESC LIMIT 10'
  );
  cache.set('popular-posts', posts);
  return posts;
}

6. Use Connection Pooling

Each database connection takes 20-50ms to establish. Connection pooling reuses existing connections:

// Neon serverless pooler
import { neon } from '@neondatabase/serverless';
const sql = neon(process.env.DATABASE_URL); // Uses HTTP connection pooling

// pg with pool
import { Pool } from 'pg';
const pool = new Pool({ max: 20, connectionTimeoutMillis: 5000 });

Measuring Improvement

After optimization, verify:

Metric	Before	After
Average query time	200ms	5ms
TTFB	1.5s	200ms
P95 response time	3s	500ms

Monitor Your TTFB

Database performance degrades as data grows. BadPageSpeed tracks TTFB over time so you catch slowdowns before users do.

Monitor your server performance →