Skip to content

Database Performance Degradation Runbook

Last Updated: 2026-10-25 Severity: HIGH MTTR Target: 15 minutes


📋 Overview

This runbook guides you through diagnosing and resolving database performance issues in FraiseQL applications. Database performance degradation can manifest as slow query responses, connection pool exhaustion, or query timeouts.


🚨 Symptoms

Primary Indicators

  • GraphQL queries taking > 5 seconds
  • Database connection pool exhausted
  • Query timeout errors (> 30 seconds)
  • High database CPU usage (> 80%)
  • Increasing query latency over time

Prometheus Metrics to Monitor

FraiseQL doesn't time its SQL statements: fraiseql_db_query_duration_seconds and the pool gauges exist but only hold values the app records itself (see Metrics). The request latency is always recorded once setup_metrics(app) is installed:

# Average /graphql request over 5 seconds
sum(rate(fraiseql_http_request_duration_seconds_sum{endpoint="/graphql"}[5m]))
  / sum(rate(fraiseql_http_request_duration_seconds_count{endpoint="/graphql"}[5m])) > 5

# Connection pool near capacity (the app feeds the gauges with update_db_connections)
fraiseql_db_connections_active / fraiseql_db_connections_total > 0.8

Log Entries

A statement that query_timeout cancels in db.run() is logged on the fraiseql.security logger at ERROR, as a JSON security event:

{
  "event_type": "query.timeout",
  "severity": "error",
  "timestamp": "2026-10-25T10:45:30.123456+00:00",
  "user_id": "user_456",
  "user_email": null,
  "ip_address": null,
  "user_agent": null,
  "request_id": null,
  "resource": null,
  "action": null,
  "result": "timeout",
  "reason": "Query execution timeout",
  "metadata": {
    "query_hash": null,
    "execution_time": 30,
    "error": "canceling statement due to statement timeout",
    "query_type": "database_query"
  }
}

The fraiseql.db logger also logs the database error (❌ Database error executing query, with the traceback). FraiseQL logs nothing about slow queries that finish: PostgreSQL does, with log_min_duration_statement (below).


🔍 Diagnostic Steps

Step 1: Check Current Query Performance

Via Prometheus:

# p95 request duration, by path
histogram_quantile(0.95,
  sum by (le, endpoint) (rate(fraiseql_http_request_duration_seconds_bucket[5m]))
)

# Slowest resolvers (those decorated with @with_metrics("query"))
topk(5,
  sum by (operation_name) (rate(fraiseql_graphql_query_duration_seconds_sum[5m]))
  / sum by (operation_name) (rate(fraiseql_graphql_query_duration_seconds_count[5m]))
)

Via Logs:

# Statements cancelled by the timeout (security log)
grep '"query.timeout"' security_events.log | tail -50

# Slow statements (PostgreSQL log, with log_min_duration_statement set)
grep 'duration:' /var/log/postgresql/postgresql.log | tail -50

Via pg_stat_statements (if the extension is installed):

SELECT
  calls,
  round(mean_exec_time::numeric, 1) AS mean_ms,
  round(total_exec_time::numeric, 1) AS total_ms,
  left(query, 100) AS query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Step 2: Check Connection Pool Status

Via Prometheus (when the app feeds the pool gauges):

# Active connections
fraiseql_db_connections_active

# Idle connections
fraiseql_db_connections_idle

# Total connections
fraiseql_db_connections_total

# Pool utilization percentage
(fraiseql_db_connections_active / fraiseql_db_connections_total) * 100

Via Application Code:

The repository exposes its psycopg pool, whose get_stats() counts connections and waiting requests (from inside the running app, e.g. a resolver or an admin route):

pool_stats = info.context["db"].get_pool().get_stats()
print(f"Open: {pool_stats['pool_size']}")
print(f"Idle: {pool_stats['pool_available']}")
print(f"Waiting for a connection: {pool_stats.get('requests_waiting', 0)}")

Step 3: Identify Problematic Queries

PostgreSQL - Find Long-Running Queries:

SELECT
  pid,
  usename,
  application_name,
  state,
  query,
  now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '5 seconds'
ORDER BY duration DESC
LIMIT 10;

PostgreSQL - Check Table-Level Stats:

SELECT
  schemaname,
  relname AS table_name,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_tup_ins + n_tup_upd + n_tup_del AS writes
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 10;

PostgreSQL - Missing Indexes:

SELECT
  schemaname,
  relname AS table_name,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / seq_scan AS avg_seq_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
  AND idx_scan = 0
ORDER BY seq_tup_read DESC
LIMIT 10;

Step 4: Check Database Resource Usage

PostgreSQL - Database Size:

SELECT
  pg_size_pretty(pg_database_size(current_database())) AS db_size;

PostgreSQL - Connection Count:

SELECT count(*) AS total_connections
FROM pg_stat_activity;

SELECT
  state,
  count(*) AS count
FROM pg_stat_activity
GROUP BY state;

PostgreSQL - Cache Hit Ratio (should be > 95%):

SELECT
  sum(heap_blks_read) AS heap_read,
  sum(heap_blks_hit) AS heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS cache_hit_ratio
FROM pg_statio_user_tables;

🔧 Resolution Steps

Immediate Actions (5 minutes)

1. Verify Query Timeout Configuration

query_timeout (seconds, default 30) becomes a transaction-local statement_timeout on every statement the repository runs: reads (find, find_one, count and the other read helpers), db.run() and mutation functions (execute_function), so PostgreSQL cancels one that runs longer:

from fraiseql.fastapi import FraiseQLConfig, create_fraiseql_app

config = FraiseQLConfig(
    database_url="postgresql://localhost/mydb",
    query_timeout=60,  # For slow queries, temporarily increase from 30
)
app = create_fraiseql_app(config=config, types=[Order], queries=[orders])

Environment Variable Override (read when the app is built from a FraiseQLConfig):

# In production environment
export FRAISEQL_QUERY_TIMEOUT=60  # seconds

find(), find_one() and the other reads don't get it, so bound every statement the app sends with a timeout on its database role as well; new pool connections pick it up:

ALTER ROLE app_user SET statement_timeout = '30s';

2. Kill Long-Running Queries (If Critical)

-- Find problematic query PID
SELECT pid, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '30 seconds';

-- Kill specific query
SELECT pg_cancel_backend(<pid>);

-- Force terminate if cancel doesn't work
SELECT pg_terminate_backend(<pid>);

3. Temporarily Increase Connection Pool Size

The pool opens at most database_pool_size + database_max_overflow connections:

from fraiseql.fastapi import FraiseQLConfig

config = FraiseQLConfig(
    database_url="postgresql://localhost/mydb",
    database_pool_size=30,  # Default: 20 (create_fraiseql_app uses 10 in development)
    database_max_overflow=10,  # Default: 10
)

Environment Variables (read when the app is built from a FraiseQLConfig):

export FRAISEQL_DATABASE_POOL_SIZE=30
export FRAISEQL_DATABASE_MAX_OVERFLOW=10

Short-Term Fixes (15-30 minutes)

1. Analyze and Optimize Slow Queries

Enable Query Logging:

-- PostgreSQL configuration
ALTER DATABASE your_db SET log_min_duration_statement = 1000;  -- Log queries > 1s
ALTER DATABASE your_db SET log_statement = 'all';

Use EXPLAIN ANALYZE:

-- For slow GraphQL query
EXPLAIN ANALYZE
SELECT ... FROM users WHERE ...;

Common Issues:

  • Missing indexes → Add indexes
  • Sequential scans → Add WHERE clause indexes
  • Join order → Reorder joins
  • N+1 queries → Use DataLoader pattern

2. Add Missing Indexes

-- Example: Index on frequently filtered column
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Example: Composite index for common WHERE clauses
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders(user_id, status);

-- Example: Index for foreign key lookups
CREATE INDEX CONCURRENTLY idx_order_items_order_id
ON order_items(order_id);

Note: Use CONCURRENTLY to avoid locking the table.

3. Optimize Connection Pool Settings

Configuration Guidelines:

# Calculate optimal pool size
# Rule of thumb: pool_size = (available_connections / number_of_app_instances) * 0.8

# Example for PostgreSQL with max_connections=100, 2 app instances
pool_size = (100 / 2) * 0.8  # = 40 connections per instance, overflow included

config = FraiseQLConfig(
    database_url="postgresql://localhost/mydb",
    database_pool_size=30,
    database_max_overflow=10,  # 30 + 10 = 40
    database_pool_timeout=30,  # seconds to wait for connection
    database_pool_recycle=3600,  # recycle connections after 1 hour
)

Long-Term Solutions (1+ days)

1. Implement Query Result Caching

Cache expensive reads with fraiseql.caching (PostgresCache + ResultCache + CachedRepository, stored in a PostgreSQL UNLOGGED table): see Caching for the setup and invalidation.

For reads that are expensive to compute rather than frequent, pre-compose them into a tv_ projection table instead.

2. Use Database Read Replicas

fraiseql connects to one database_url and has no read/write routing. To offload reads, run a separate deployment of the API pointed at a replica (or at a connection pooler that routes to replicas), and send read-only traffic to it.

3. Partition Large Tables

-- Example: Partition orders by created_at month
CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER,
    created_at TIMESTAMP,
    ...
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE orders_2025_01 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02 PARTITION OF orders
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

A fraiseql query reads one view; embed related rows in its data JSONB and the whole tree comes back from a single SQL statement, however many orders there are:

CREATE VIEW v_order AS
SELECT
    o.id,
    jsonb_build_object(
        'id', o.id,
        'total', o.total,
        'created_at', o.created_at,
        'user', jsonb_build_object('id', u.id, 'name', u.name)
    ) AS data
FROM tb_order o
JOIN tb_user u ON u.pk_user = o.fk_user;
import fraiseql
from fraiseql.types import ID


@fraiseql.type
class OrderUser:
    id: ID
    name: str


@fraiseql.type(sql_source="v_order", jsonb_column="data")
class Order:
    id: ID
    total: float
    user: OrderUser


@fraiseql.query
async def orders(info, limit: int = 100) -> list[Order]:
    db = info.context["db"]
    return await db.find("v_order", limit=limit)

📊 Monitoring & Alerts

Prometheus Alert Rules

# alerts/database.yml
groups:
  - name: database_performance
    interval: 30s
    rules:
      - alert: GraphQLSlowRequests
        expr: |
          sum(rate(fraiseql_http_request_duration_seconds_sum{endpoint="/graphql"}[5m]))
          / sum(rate(fraiseql_http_request_duration_seconds_count{endpoint="/graphql"}[5m])) > 5
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "GraphQL requests are slow (> 5s average)"
          description: "Average request duration is {{ $value }}s"

      # Needs the pool gauges fed with update_db_connections
      - alert: DatabaseConnectionPoolExhausted
        expr: |
          fraiseql_db_connections_active / fraiseql_db_connections_total > 0.9
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "Database connection pool is exhausted"
          description: "{{ $value | humanizePercentage }} of connections in use"

Cancelled statements (query.timeout) have no metric: alert on the security event in your log stack. The buffer cache hit ratio comes from PostgreSQL (pg_statio_user_tables, query in Step 4), or from an exporter you run for it.

Grafana Dashboard Panels

1. Request Duration by Path (p95):

histogram_quantile(0.95,
  sum by (le, endpoint) (rate(fraiseql_http_request_duration_seconds_bucket[5m]))
)

2. Connection Pool Utilization (pool gauges fed):

fraiseql_db_connections_active
fraiseql_db_connections_idle
fraiseql_db_connections_total

3. Slowest Resolvers (resolvers decorated with @with_metrics("query")):

sum by (operation_name) (rate(fraiseql_graphql_query_duration_seconds_sum[5m]))
  / sum by (operation_name) (rate(fraiseql_graphql_query_duration_seconds_count[5m]))

4. Cancelled Statements (log panel): the query.timeout security events over time.


🔍 Verification

After applying fixes, verify performance has improved:

1. Check Metrics

# Request duration should decrease
sum(rate(fraiseql_http_request_duration_seconds_sum{endpoint="/graphql"}[5m]))
  / sum(rate(fraiseql_http_request_duration_seconds_count{endpoint="/graphql"}[5m]))

# Connection pool utilization should decrease (pool gauges fed)
fraiseql_db_connections_active / fraiseql_db_connections_total

2. Check Logs

# No recent cancelled statements
grep '"query.timeout"' security_events.log | tail -10

# No recent slow statements (PostgreSQL log)
grep 'duration:' /var/log/postgresql/postgresql.log | tail -10

3. Synthetic Test

# Run load test
curl -X POST http://localhost:8000/graphql \
  -H "Content-Type: application/json" \
  -d '{"query": "{ users { id name email } }"}'

# Check response time < 1 second

📝 Post-Incident Review

After resolving the incident:

  1. Document Root Cause:
  2. What caused the performance degradation?
  3. Missing index? Poor query? Connection leak?

  4. Update Monitoring:

  5. Add alerts for similar patterns
  6. Adjust thresholds based on normal load

  7. Implement Preventive Measures:

  8. Add missing indexes
  9. Optimize slow queries
  10. Tune connection pool settings

  11. Update Runbook:

  12. Add new diagnostic steps
  13. Document solution for future reference


🆘 Escalation

If issue persists after following this runbook:

  1. Gather Evidence:
  2. Prometheus metrics screenshot
  3. Recent structured logs (last 15 minutes)
  4. PostgreSQL query stats
  5. Connection pool stats

  6. Escalate To:

  7. Database Administrator (for PostgreSQL tuning)
  8. Platform Team (for infrastructure scaling)
  9. Development Team (for query optimization)

  10. Emergency Contact:

  11. On-call DBA: [Contact info]
  12. Platform On-call: [Contact info]
  13. Engineering Manager: [Contact info]

Version: 1.0 Last Tested: 2025-12-29 Next Review: 2026-03-29