RDS Query Optimization: Database Performance Analysis

Slow database queries increased Lambda execution time and costs. We analyzed query performance, added indexes, and rewrote inefficient queries, achieving a 50× speedup for critical operations.

Our RDS PostgreSQL database served as the source of truth for user progress, content, and analytics. As the user base grew from 5,000 to 50,000, previously acceptable queries slowed to 2-5 seconds, causing Lambda timeouts and degraded user experience.

Symptoms:

• API endpoints timing out (30s Lambda limit)
• Increased RDS CPU utilization (70-90%)
• Slow mobile app response times (3-5s for lesson loading)
• Rising RDS costs due to increased instance size

The breaking point came when user progress queries took 3+ seconds, making the app feel sluggish and causing users to abandon lessons.

Query Performance Profile:

Query Analysis Process

Step 1: Enable Query Logging

-- Enable slow query logging in RDS
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries > 1s
ALTER SYSTEM SET log_statement = 'all';
SELECT pg_reload_conf();

Step 2: Analyze Query Plans

-- Identify slow queries from pg_stat_statements
SELECT
    query,
    calls,
    total_exec_time / 1000 AS total_time_seconds,
    mean_exec_time AS avg_time_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Step 3: Use EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM attempts WHERE user_id = 12345 ORDER BY created_at DESC;

-- Output showed:
-- Seq Scan on attempts  (cost=0.00..1250000.00 rows=50000000)
-- Planning Time: 0.5ms
-- Execution Time: 2500.0ms
-- ^ Indicates full table scan, needs index

Index Creation Strategy

Composite Indexes for Multi-Column Queries:

-- User attempts (user_id + created_at)
CREATE INDEX idx_attempts_user_created
  ON attempts(user_id, created_at DESC);

-- Content by subject (subject_id + order)
CREATE INDEX idx_content_subject_order
  ON content(subject_id, display_order);

-- Session lookup (user_id + status)
CREATE INDEX idx_sessions_user_status
  ON sessions(user_id, status);

-- Analytics rollup (event_date + user_id)
CREATE INDEX idx_analytics_date_user
  ON analytics_events(event_date, user_id);

Partial Indexes for Filtered Queries:

-- Active sessions only (status = 'active')
CREATE INDEX idx_sessions_active
  ON sessions(user_id, created_at)
  WHERE status = 'active';

-- Recent attempts (last 30 days)
CREATE INDEX idx_attempts_recent
  ON attempts(user_id, created_at)
  WHERE created_at > NOW() - INTERVAL '30 days';

Query Rewriting Examples

**Example 1: Eliminate SELECT ***

-- Before
SELECT * FROM users WHERE email = 'user@example.com';
-- Fetches 25 columns, including JSONB fields (slow)

-- After
SELECT id, email, name, created_at FROM users WHERE email = 'user@example.com';
-- Fetches only needed columns (5× faster)

Example 2: Add Pagination

-- Before
SELECT * FROM attempts WHERE user_id = 12345 ORDER BY created_at DESC;
-- Fetches all 5,000 rows (slow)

-- After
SELECT id, bit_id, correct, created_at
FROM attempts
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 100 OFFSET 0;
-- Fetches only 100 rows per page (50× faster)

Example 3: Use JOIN instead of Subquery

-- Before (subquery, slow)
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM sessions WHERE status = 'active'
);
-- Execution time: 1,800ms

-- After (JOIN, fast)
SELECT DISTINCT u.*
FROM users u
INNER JOIN sessions s ON s.user_id = u.id
WHERE s.status = 'active';
-- Execution time: 120ms (15× faster)

Example 4: Use COUNT(*) Optimization

-- Before (exact count, slow for large tables)
SELECT COUNT(*) FROM attempts WHERE user_id = 12345;
-- Execution time: 800ms

-- After (approximate count for display purposes)
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'attempts';
-- Execution time: 5ms (160× faster)
-- Acceptable for "~5,000 attempts" display

Database Connection Pooling

Before: New Connection Per Request

# src/core/database.py
def get_db_connection():
    return psycopg2.connect(
        host=DB_HOST,
        database=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD
    )
# Connection time: 50-100ms per request

After: Connection Pooling with SQLAlchemy

# src/core/database.py
from sqlalchemy import create_engine
from sqlalchemy.pool import QueuePool

engine = create_engine(
    DATABASE_URL,
    poolclass=QueuePool,
    pool_size=20,           # 20 persistent connections
    max_overflow=10,        # Up to 30 total connections
    pool_recycle=3600,      # Recycle connections every hour
    pool_pre_ping=True      # Verify connection before use
)

def get_db_connection():
    return engine.connect()
# Connection time: <1ms (reuse existing)

Query Execution Time

Top 10 Queries After Optimization:

RDS Resource Utilization

CPU and IOPS Metrics:

Lambda Execution Impact

Lambda Duration Savings:

RDS Instance Rightsizing

Instance Downgrade:

Lambda Cost Reduction

Duration-Based Cost Savings:

Total Cost Savings: $630 (RDS) + $560 (Lambda) = $1,190/month

CloudWatch RDS Metrics

Custom Dashboard:

Alerting Rules:

1. Slow query count > 50 - Alert if many queries exceed 100ms
2. Sequential scan rate > 10% - Indicates missing indexes
3. Cache hit rate < 85% - Indicates poor query patterns
4. CPU utilization > 60% - May need further optimization

Quantified Outcomes:

• 50× average speedup - 2,500ms → 50ms for critical queries
• 93% reduction in API latency - 2.8s → 0.2s average
• $1,190/month saved - RDS downsizing + Lambda duration
• 100% elimination of timeouts - 120/day → 0
• 352 hours/week saved - Reduced time in database queries

1. Indexes are non-negotiable for production databases. Every filtered or sorted column should have an appropriate index.

2. SELECT * is expensive. Fetching only needed columns reduced query time by 5× in some cases.

3. EXPLAIN ANALYZE reveals truth. Query performance intuition is often wrong—always measure.

4. Connection pooling matters. 50-100ms connection overhead per request adds up quickly.

5. Cost optimization follows performance optimization. Faster queries enabled RDS downsizing, compounding savings.

Database query optimization delivered the highest ROI of any performance improvement: 93% latency reduction and $1,190/month in cost savings with a one-time engineering investment.

Related Posts:

• API Response Caching Strategy: Reduce Database Load
• Scheduler Query Optimization: Background Job Efficiency
• Lambda SnapStart Rollout & Disable

Documentation: 2026-01-20-lambda-perf-analysis-findings.md Impact: 50× query speedup, $1,190/month saved, zero timeout errors