Database cost optimization

.

Step 1: Baseline Measurement & Query Profiling

Enable pg_stat_statements and capture a 7-day baseline. Identify queries consuming >80% of total execution time or I/O. Use EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) to extract actual row counts, heap fetches, and shared buffer hits.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Step 2: Index Strategy & Query Plan Validation

Target high-frequency queries with composite indexes that match filter and sort order. Avoid single-column indexes unless they serve independent query paths. Use covering indexes to eliminate heap fetches.

-- Before: Sequential scan on large table
EXPLAIN ANALYZE SELECT id, status, created_at FROM orders WHERE customer_id = 12345 AND status = 'pending';

-- After: Targeted composite index
CREATE INDEX CONCURRENTLY idx_orders_customer_status ON orders (customer_id, status) INCLUDE (created_at);

Validate that EXPLAIN ANALYZE shows Index Only Scan or Index Scan with Heap Fetches: 0 for read-heavy paths.

Step 3: Connection Pooling & Session Management

Raw database connections consume memory and CPU per session. Implement a pool with strict limits, statement timeouts, and idle eviction.

import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  max: 25, // Scale based on instance class: (vCPU * 2) + 5
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
  statement_timeout: 5000, // Prevent runaway queries
  query_timeout: 5000,
});

export async function query(text: string, params?: unknown[]) {
  const start = Date.now();
  const res = await pool.query(text, params);
  const duration = Date.now() - start;
  
  if (duration > 2000) {
    console.warn(`Slow query detected: ${duration}ms | ${text.substring(0, 100)}`);
  }
  
  return res;
}

Step 4: Storage Lifecycle & Tiering

Partition cold data, archive to object storage, and downgrade provisioned IOPS. Use table partitioning for time-series data and drop or archive partitions older than retention windows.

CREATE TABLE orders (
  id BIGSERIAL,
  customer_id INT NOT NULL,
  status VARCHAR(20),
  created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- Monthly partition example
CREATE TABLE orders_2024_01 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

Archive partitions exceeding 90-day retention to S3/GCS using COPY or logical replication, then detach and drop.

Step 5: Auto-Scaling & Read Replica Configuration

Enable storage auto-scaling to prevent manual volume upgrades. Configure read replicas only for read-heavy workloads (>60% SELECT ratio). Use connection routing to direct writes to primary and reads to replicas.

Architecture decisions:

• Why pg-pool over raw pg? Connection reuse eliminates TCP handshake and authentication overhead. Strict max limits prevent memory exhaustion during traffic spikes.
• Why statement_timeout? Runaway queries block pool connections, trigger auto-scaling, and inflate CPU costs. Timeouts enforce predictable execution windows.
• Why partitioning over monolithic tables? Partition pruning reduces scan scope. Dropping old partitions is O(1) vs. DELETE which generates WAL, triggers vacuum, and inflates storage IOPS.
• Why read replicas only for read-heavy ratios? Replicas add storage, backup, and network costs. If write ratio exceeds 40%, replica lag and consistency overhead outweigh throughput gains.

Pitfall Guide

1. Indexing Everything

Mistake: Creating indexes for every filtered column to "speed up queries." Impact: Write amplification increases by 30–70%. Each INSERT/UPDATE must maintain every index. Storage bloat accelerates, increasing backup costs and vacuum overhead. Best Practice: Index only columns used in WHERE, JOIN, or ORDER BY clauses. Monitor pg_stat_user_indexes for unused indexes. Drop indexes with idx_scan = 0 over 30 days.

2. Ignoring Connection Pool Limits

Mistake: Setting max connections to 100+ or leaving it unlimited. Impact: Each connection consumes ~10MB RAM. 100 connections = 1GB baseline overhead. CPU context switching degrades throughput. Cloud providers charge for IOPS and CPU; idle connections waste both. Best Practice: Calculate max = (vCPU * 2) + 5. Use idleTimeoutMillis to reclaim sessions. Implement queueing at the application layer if demand exceeds pool capacity.

3. Blind Auto-Scaling

Mistake: Relying on cloud auto-scaling to handle inefficient queries or connection leaks. Impact: Scale events trigger during peak load, adding latency. Auto-scaling masks root causes, inflating baseline costs. You pay for larger instances while queries remain unoptimized. Best Practice: Treat auto-scaling as a safety net, not a strategy. Fix query plans and connection management first. Enable auto-scaling only for storage volume, not compute.

4. Neglecting Storage Lifecycle

Mistake: Retaining all data on provisioned IOPS volumes indefinitely. Impact: Cold data consumes expensive storage tiers. Backup retention policies multiply storage costs. WAL archiving grows unbounded. Best Practice: Implement 30/90/365-day retention tiers. Move >90-day data to standard storage or object storage. Automate partition detachment and archival.

5. Skipping Query Plan Validation

Mistake: Deploying schema changes without re-running EXPLAIN ANALYZE. Impact: Index bloat, statistic drift, or data distribution changes can flip Index Scan to Sequential Scan. Cost spikes silently until latency alerts trigger. Best Practice: Integrate query plan regression tests into CI/CD. Capture baseline plans before deployments. Alert on plan changes exceeding 20% execution time variance.

6. Misconfigured Caching

Mistake: Using application cache without TTL alignment or invalidation strategy. Impact: Stale data causes business logic errors. Cache stampedes during TTL expiry spike database load. Memory waste on unused keys. Best Practice: Align TTL with data volatility. Use cache-aside pattern with probabilistic early expiration. Monitor hit ratio; drop caches below 40% efficiency.

7. Overlooking Data Gravity

Mistake: Deploying databases in regions far from compute or using cross-AZ traffic for sync. Impact: Network egress charges accumulate. Cross-AZ replication adds latency and bandwidth costs. Multi-region active-active setups multiply storage and backup expenses. Best Practice: Co-locate database and compute in the same availability zone. Use read replicas only in secondary regions if latency requirements justify the cost. Prefer async replication for cost-sensitive workloads.

Production Bundle

Action Checklist

• Enable pg_stat_statements: Capture 7-day baseline of query execution, I/O, and memory usage.
• Validate top 20 queries: Run EXPLAIN ANALYZE, identify sequential scans, and target missing indexes.
• Configure connection pool: Set max connections to (vCPU * 2) + 5, enable statement_timeout, and implement slow query logging.
• Implement storage partitioning: Partition time-series tables, detach cold partitions, and archive to object storage.
• Enable storage auto-scaling: Set minimum/maximum bounds, disable compute auto-scaling until query optimization completes.
• Schedule index maintenance: Run REINDEX and ANALYZE during low-traffic windows; drop unused indexes monthly.
• Monitor cross-AZ traffic: Route reads/writes to same AZ; alert on egress exceeding 10% of total traffic.
• Establish query budget: Define max execution time per endpoint; reject or queue queries exceeding thresholds.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Read-heavy API (>70% SELECT)	Primary + 1 Read Replica + Connection Routing	Offloads SELECT traffic, reduces primary CPU pressure	+15% storage, -20% primary compute cost
Write-heavy transactional system	Single Primary + Optimized Indexes + Partitioning	Replicas add lag and cost; write paths benefit from tuning	-30% IOPS, -25% backup storage
Time-series telemetry data	Range Partitioning + Cold Archive + Standard Storage	Hot data stays fast; cold data moves to cheaper tiers	-60% storage cost, -40% backup cost
Microservices sharing one DB	Schema-per-service + Read-only Views + Connection Pool per Service	Isolates workload, prevents cross-service query contention	+10% overhead, -35% contention-related scaling
Unpredictable traffic spikes	Fixed Right-Sized Instance + Query Optimization + Queue Backpressure	Auto-scaling adds latency; queueing prevents cascade failures	-40% peak compute cost, stable p95 latency

Configuration Template

Terraform (AWS RDS PostgreSQL + Auto-Scaling + Parameters)

resource "aws_db_instance" "optimized" {
  identifier             = "app-db-optimized"
  engine                 = "postgres"
  engine_version         = "15.4"
  instance_class         = "db.r6g.large"
  allocated_storage      = 100
  max_allocated_storage  = 500
  storage_type           = "gp3"
  storage_encrypted      = true
  multi_az               = false
  backup_retention_period = 7
  deletion_protection    = true
  parameter_group_name   = aws_db_parameter_group.optimized.name

  tags = { Environment = "production", CostCenter = "database-optimization" }
}

resource "aws_db_parameter_group" "optimized" {
  family = "postgres15"
  name   = "app-db-params"

  parameter {
    name  = "shared_preload_libraries"
    value = "pg_stat_statements"
  }
  parameter {
    name  = "statement_timeout"
    value = "5000"
  }
  parameter {
    name  = "log_min_duration_statement"
    value = "1000"
  }
  parameter {
    name  = "effective_cache_size"
    value = "16384" # MB, ~50% of instance RAM
  }
}

Node.js Connection Pool Config (dotenv)

DB_HOST=your-rds-endpoint
DB_PORT=5432
DB_NAME=app_production
DB_USER=app_user
DB_PASS=secure_password
DB_POOL_MAX=25
DB_IDLE_TIMEOUT=30000
DB_STATEMENT_TIMEOUT=5000
DB_QUERY_TIMEOUT=5000

Quick Start Guide

1. Instrument baseline: Enable pg_stat_statements via parameter group, restart instance, and run SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; to capture top cost drivers.
2. Deploy pool configuration: Copy the TypeScript pool setup into your data access layer, set max connections to (vCPU * 2) + 5, and enforce statement_timeout = 5000.
3. Target top 3 queries: Run EXPLAIN ANALYZE on the highest total_exec_time queries, add composite indexes matching filter/sort order, and verify Heap Fetches: 0 in the plan.
4. Enable storage auto-scaling: Apply the Terraform template or cloud console settings, set max_allocated_storage to 3x current usage, and disable compute auto-scaling until query optimization completes.
5. Validate cost impact: Monitor CPUUtilization, ReadIOPS, WriteIOPS, and DatabaseConnections in CloudWatch for 48 hours. Expect CPU to stabilize at 60–75%, IOPS to drop by 30%+, and monthly invoice to reflect reduced provisioned capacity.