Live SQL Profiler & Query Replay

Last update on October 07 2026 12:12:27 (UTC/GMT +8 hours)

Live SQL Profiler & Query Replay: An Introduction

Every database-backed application has a hidden cost center: the queries it executes. Some are fast, some are slow, and a few are quietly catastrophic — running thousands of times per request, dragging down response times, and consuming database resources that could serve other traffic. The problem is that these queries are largely invisible. They execute inside an ORM abstraction layer, buried beneath method calls and relationship traversals, leaving developers with little visibility into what actually happens at the database level.

Traditional profiling tools offer snapshots: run a profiler, capture a trace, analyze it after the fact. But modern application development moves faster than that. Developers need real-time visibility into query behavior as they work, with the ability to experiment, compare, and understand the why behind performance problems. The Live SQL Profiler & Query Replay is a real-time dashboard that hooks into SQLAlchemy's event system to capture every query an application executes — including execution time, rows affected, and full SQL — and gives developers the tools to replay queries, compare performance across indexes, and visualize N+1 patterns as flame graphs.

What It Does

At its core, the Live SQL Profiler is an observability tool purpose-built for SQLAlchemy applications. Rather than requiring developers to configure logging, parse log files, or use external APM tools, it hooks directly into SQLAlchemy's event system and captures every query as it executes. The captured data flows through a WebSocket connection to a live dashboard, where developers see queries appear in real time as their application runs.

The profiler captures a comprehensive set of metrics for each query:

Full SQL text with parameter bindings substituted, so developers see exactly what the database received
Execution time in milliseconds, measured at the driver level
Rows affected or returned, revealing queries that scan entire tables
Timestamp and duration, enabling timeline analysis
Call stack context where available, linking queries back to the code that triggered them

But observation alone is not enough. The profiler's distinguishing feature is query replay: the ability to take any captured query, modify its parameters, and re-execute it against the database to see how performance changes. This transforms the tool from a passive monitor into an active performance laboratory.

Developers can replay a query with different parameter values to understand how selectivity affects performance. They can run the query with and without specific indexes to quantify the impact. They can compare execution plans side by side. And crucially, they can do all of this without modifying application code or restarting the application.

The third major capability is N+1 visualization. N+1 queries — where an application executes one query to fetch a list of parent records and then N additional queries to fetch related children — are among the most common and most damaging performance antipatterns in ORM-based applications. The profiler detects these patterns and renders them as flame graphs, making the characteristic shape of N+1 behavior immediately visible: a wide fan of similar queries emanating from a single logical operation.

Architecture and Data Flow

Capturing Queries with SQLAlchemy Events

SQLAlchemy provides a rich event system that allows observers to hook into the query execution lifecycle. The profiler uses two primary events:

before_cursor_execute: Fired immediately before a query is sent to the database. The handler records the start time and captures the SQL statement and parameters.

after_cursor_execute: Fired immediately after the database returns results. The handler calculates elapsed time, records rows affected, and emits a complete query event.

A minimal implementation looks like this:

python:


import time
from sqlalchemy import event

@event.listens_for(engine, "before_cursor_execute")
def before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    conn.info.setdefault("query_start_time", []).append(time.perf_counter())

@event.listens_for(engine, "after_cursor_execute")
def after_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    start_time = conn.info["query_start_time"].pop()
    duration_ms = (time.perf_counter() - start_time) * 1000
    emit_query_event(statement, parameters, duration_ms, cursor.rowcount)

The conn.info dictionary provides per-connection storage, which is essential for correctly matching before and after events when multiple queries execute concurrently on different connections. Using a stack (append and pop) rather than a single value handles nested or sequential queries correctly.

For applications using async SQLAlchemy with AsyncEngine, the event system works similarly, though the events are attached to the underlying sync engine via engine.sync_engine. This allows the profiler to capture queries in both sync and async applications without modification.

Streaming to the Dashboard with WebSockets

Captured query events flow to the frontend through a WebSocket connection managed by FastAPI. WebSockets are the right choice here because the data is inherently push-based and continuous — a polling architecture would either introduce latency (long polling intervals) or waste resources (frequent empty responses).

The FastAPI side maintains a connection manager that tracks active WebSocket connections and broadcasts query events to all connected clients:

python:


from fastapi import FastAPI, WebSocket
from fastapi.websockets import WebSocketDisconnect

app = FastAPI()

class ConnectionManager:
    def __init__(self):
        self.active_connections: list[WebSocket] = []

    async def connect(self, websocket: WebSocket):
        await websocket.accept()
        self.active_connections.append(websocket)

    def disconnect(self, websocket: WebSocket):
        self.active_connections.remove(websocket)

    async def broadcast(self, message: dict):
        for connection in self.active_connections:
            await connection.send_json(message)

manager = ConnectionManager()

@app.websocket("/ws/queries")
async def websocket_endpoint(websocket: WebSocket):
    await manager.connect(websocket)
    try:
        while True:
            await websocket.receive_text()  # keep connection alive
    except WebSocketDisconnect:
        manager.disconnect(websocket)

Query events are published to the manager from the SQLAlchemy event handler. In practice, this requires bridging the synchronous SQLAlchemy event system with the asynchronous WebSocket layer — typically achieved through an asyncio.Queue that the event handler writes to and a background task reads from.

Enriching with pg_stat_statements

While the SQLAlchemy event system captures queries as the application issues them, PostgreSQL's pg_stat_statements extension provides a complementary view: aggregate statistics about query execution at the database level. This extension tracks normalized query patterns, total execution time, call counts, rows processed, and cache hit ratios — data that is invaluable for identifying which queries are consuming the most database resources over time.

The profiler queries pg_stat_statements periodically and correlates its data with the real-time event stream. This allows the dashboard to show not just individual query executions but also historical context: "This query has been called 10,000 times in the last hour and consumed 45 seconds of database time."

The relevant query against pg_stat_statements looks like:

sql:


SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This combination — real-time events from SQLAlchemy and historical aggregates from PostgreSQL — gives developers both immediate visibility and the broader context needed to prioritize optimization efforts.

Storing History in Redis

Query history is stored in Redis, which is well-suited to this workload. Redis offers low-latency writes (essential when capturing queries in a hot path), automatic expiration via TTL (preventing unbounded growth), and data structures like sorted sets and lists that map naturally to query histories and rankings.

Each captured query is serialized and pushed to a Redis list or sorted set, with a configurable TTL — typically a few hours in development or a few days in staging. Recent queries are retrieved instantly for display, and the profiler can compute aggregate statistics (queries per second, average latency, slowest queries) directly from Redis without touching the primary database.

Redis also enables the replay feature: when a developer selects a query to replay, its full SQL and parameters are retrieved from Redis, modified as needed, and re-executed against the target database.

Visualizing with D3.js Flame Graphs

Flame graphs are the ideal visualization for N+1 query patterns. Originally developed by Brendan Gregg for CPU profiling, flame graphs display hierarchical data as stacked rectangles, where width represents magnitude (in this case, time or count) and stacking represents call depth.

For SQL profiling, the flame graph is organized by request or logical operation. The top-level rectangle represents a single HTTP request or application operation. Beneath it, child rectangles represent individual queries or groups of related queries. When an N+1 pattern occurs, the visualization shows a distinctive wide fan of nearly identical rectangles — a pattern that is immediately recognizable and impossible to miss.

D3.js provides the flexibility needed to build this visualization. Unlike charting libraries with fixed chart types, D3.js gives developers direct control over SVG rendering, enabling interactive flame graphs with zoom, pan, tooltips, and click-to-filter behavior. The flame graph updates in real time as new queries arrive, with smooth transitions that make the visualization feel alive without being distracting.

Query Replay: From Observation to Experimentation

The Replay Workflow

Query replay transforms the profiler from a monitoring tool into a performance laboratory. The workflow is straightforward:

Developer selects a query from the live stream or history
The query's full SQL and parameters are loaded into an editor
Developer modifies parameters, adds hints, or changes the query structure
Developer executes the modified query against the database
Results — including execution time, rows affected, and execution plan — are displayed alongside the original

This workflow supports several distinct investigation patterns:

Parameter sensitivity analysis: A query that is fast with one parameter value and slow with another often indicates a selectivity problem or a missing index. Replaying with different values reveals the pattern.

Index impact measurement: Running a query before and after creating an index provides a direct measurement of the index's benefit. The profiler can even automate this: create a hypothetical index, measure performance, and drop it — all within the replay interface.

Execution plan comparison: Using EXPLAIN ANALYZE, developers can see how the query planner handles the query and compare plans across variations. This reveals sequential scans, nested loops, and other plan features that explain performance differences.

Safety Considerations

Query replay executes arbitrary SQL against a database, which raises obvious safety concerns. The profiler addresses these through several mechanisms:

Read-only enforcement: By default, replay operates within a read-only transaction, preventing accidental data modification
Environment guardrails: The profiler is designed for development and staging environments, with configuration that discourages production use
Timeout limits: Statement timeouts prevent runaway queries from consuming resources indefinitely
Audit logging: All replayed queries are logged for accountability

N+1 Detection and Visualization

Understanding the N+1 Problem

The N+1 problem occurs when an application executes one query to fetch a collection of parent records, then executes one additional query per parent to fetch related data. For a collection of 100 parents, this results in 101 queries — hence "N+1." The performance impact is severe: each query incurs network round-trip overhead, and the database must parse, plan, and execute each statement independently.

In SQLAlchemy applications, N+1 patterns typically arise from lazy-loaded relationships. Accessing parent.children inside a loop triggers a separate query for each parent. The ORM's convenience becomes a performance liability when used without awareness of the underlying query behavior.

Detection Strategy

The profiler detects N+1 patterns by analyzing the query stream for characteristic signatures:

Temporal clustering: Multiple similar queries executing within a short time window
Structural similarity: Queries with the same normalized structure, differing only in parameter values
Cardinality correlation: The number of queries matching the expected N+1 pattern for a given parent collection

A practical detection heuristic groups queries by their normalized form (strip literals, standardize whitespace) and flags groups where the count exceeds a threshold within a rolling window. More sophisticated detection correlates queries with application-level context — for example, identifying that 50 queries executed during a single request handler all target the same table with different foreign key values.

Flame Graph Representation

The flame graph makes N+1 patterns visually obvious. Consider a request handler that fetches 100 orders and their associated customers using lazy loading. The flame graph shows:

A single narrow rectangle for the initial SELECT * FROM orders query
A wide band of 100 nearly identical rectangles for the subsequent SELECT * FROM customers WHERE id = ? queries

The visual contrast between the single initial query and the fan of follow-up queries is striking — even a developer unfamiliar with N+1 problems will recognize that something is wrong. Clicking on any rectangle reveals the full SQL, parameters, and execution time, and the flame graph can be filtered to show only queries from a specific request or operation.

Modern Technology Stack

SQLAlchemy Event Listeners

SQLAlchemy's event system is the foundation of the profiler. Its before_cursor_execute and after_cursor_execute events provide exactly the hooks needed to intercept queries without modifying application code. The event system is stable, well-documented, and designed for precisely this kind of instrumentation.

FastAPI and WebSockets

FastAPI's native WebSocket support makes it straightforward to build the real-time streaming layer. The framework's async architecture handles many concurrent WebSocket connections efficiently, and its dependency injection system cleanly manages shared resources like the Redis client and database connections.

PostgreSQL pg_stat_statements

The pg_stat_statements extension provides database-level query statistics that complement the application-level view. Enabling it requires adding pg_stat_statements to shared_preload_libraries and creating the extension. Once active, it tracks every query executed against the database, aggregating by normalized query text.

D3.js Flame Graphs

D3.js provides the rendering foundation for the flame graph visualization. Its data-driven approach to DOM manipulation, combined with SVG support, enables the interactive, real-time visualization that makes N+1 patterns immediately apparent.

Redis for Query History

Redis stores the recent query history, providing fast reads for the dashboard and durable storage for the replay feature. Its TTL support ensures that history does not grow unbounded, and its data structures (lists, sorted sets, hashes) map naturally to the access patterns the profiler requires.

User Benefits

Instant Identification of Performance Problems

The profiler's most immediate benefit is visibility. Developers see queries as they execute, with timing and row counts that reveal performance characteristics at a glance. Slow queries stand out. High-frequency queries stand out. Queries that return far more rows than expected stand out. The dashboard surfaces what would otherwise remain hidden.

Solving N+1 Problems

N+1 problems are notoriously difficult to diagnose without tooling. They do not cause errors; they cause slowness that scales with data volume. The profiler's flame graph visualization makes N+1 patterns impossible to miss and provides the context needed to fix them — which queries are involved, how many times they execute, and where in the application they originate.

Quantifying Index Impact

The replay feature turns index tuning from guesswork into measurement. Rather than creating an index and hoping it helps, developers can measure the before-and-after performance directly. This empirical approach leads to better indexing decisions and prevents the accumulation of unused indexes that slow down writes without benefiting reads.

Development and Staging Focus

The profiler is explicitly designed for development and staging environments, where the cost of experimentation is low and the benefit of early detection is high. Catching N+1 problems and missing indexes before they reach production prevents performance incidents, reduces the need for emergency optimization, and results in applications that perform well from day one.

Learning Tool

For developers new to database performance, the profiler serves as an educational tool. Seeing queries in real time, understanding how ORM operations translate to SQL, and observing the impact of indexes builds intuition that carries forward into future development. The flame graph, in particular, teaches developers to think about query patterns rather than individual queries.

Conclusion

The Live SQL Profiler & Query Replay brings real-time observability and active experimentation to SQLAlchemy application development. By hooking into SQLAlchemy's event system, streaming data through WebSockets, enriching with PostgreSQL statistics, and visualizing with D3.js flame graphs, it provides a comprehensive view of database behavior that no logging configuration or external APM tool can match.

The combination of live monitoring, query replay, and N+1 visualization addresses the three most common performance problems in ORM-based applications: slow queries, missing indexes, and N+1 patterns. And by focusing on development and staging environments, it enables developers to find and fix these problems before they affect production.

For teams building SQLAlchemy applications, the Live SQL Profiler is more than a tool — it is a practice. It encourages developers to think about query behavior, to measure rather than assume, and to treat database performance as a first-class concern throughout the development lifecycle.