Best Self-Hosted Database Query Profiling & Optimization Tools 2026

Slow queries are the silent killers of application performance. A single unoptimized query can cascade into connection pool exhaustion, memory pressure, and eventually full database unavailability. Commercial database performance platforms charge per instance, per metric, or per query analyzed — costs that scale directly with the size of the infrastructure you are trying to monitor.

This guide covers the best self-hosted database query profiling and optimization tools available in 2026: Percona Monitoring and Management (PMM) for comprehensive query analytics, pg_activity for real-time PostgreSQL monitoring, and pgcenter for PostgreSQL command-line diagnostics. You will learn how to install, configure, and use each tool to identify and fix slow queries without sending any data to third-party services.

Why Self-Host Your Database Query Profiling

Running query profiling and analysis on your own infrastructure offers several advantages over cloud-based database performance platforms:

• Zero query data exposure: Cloud APM services receive your actual SQL statements, schema names, table structures, and sometimes parameter values. For databases containing PII, financial records, or healthcare data, this is a compliance risk. Self-hosted tools keep every query within your network.
• Unlimited retention: Cloud platforms limit query history to 7–30 days on standard plans. Self-hosted query logs and analytics data persist as long as your storage allows, enabling month-over-month trend analysis and seasonal pattern detection.
• No sampling bias: Cloud services often sample query data to reduce storage costs. When you self-host, you can capture every single query execution, including rare edge-case queries that only run during specific business processes.
• Cost predictability: Percona PMM, pg_activity, and pgcenter are all free and open-source. Your only cost is the compute and storage for the monitoring infrastructure itself.
• Custom alerting thresholds: Define alert rules based on your specific workload characteristics rather than generic industry benchmarks. A query that takes 500ms might be fine for an analytics workload but catastrophic for a real-time trading system.

For broader database monitoring beyond query profiling, see our pgwatch2 vs Percona PMM vs pgmonitor comparison and database benchmarking tools guide.

Tool Comparison at a Glance

Percona Monitoring and Management (PMM): Full-Stack Query Analytics

Percona PMM is the most comprehensive open-source database monitoring platform. Its Query Analytics (QAN) component captures every slow query, aggregates execution patterns, and presents them in a web-based dashboard with execution plan visualizations.

How PMM Query Analytics Works

PMM deploys agents (pmm-agent) on your database servers that collect query metrics from slow query logs, Performance Schema (MySQL), or pg_stat_statements (PostgreSQL). These metrics are sent to a central PMM server running Grafana and ClickHouse for storage and visualization.

Installing PMM Server via Docker

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# Create persistent data volume
docker volume create pmm-data

# Pull and run the PMM server container
docker run -d \
  -p 443:443 \
  --volumes-from pmm-data \
  --name pmm-server \
  --restart always \
  percona/pmm-server:2

The PMM server is available at https://<your-server-ip>/ with default credentials admin / admin. You will be prompted to set a new password on first login.

Connecting a PostgreSQL Database

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# Install pmm-client on the database server
wget https://www.percona.com/get/pmm2-client
sudo dpkg -i pmm2-client_2.44.0-12.amd64.deb

# Connect to PMM server
pmm-admin config --server-insecure-tls --server-url=https://admin:newpassword@pmm-server:443

# Add PostgreSQL service for monitoring
pmm-admin add postgresql --username=pmm_user --password=secret_password \
  --query-source=pgstatstatements \
  --service-name=production-postgres \
  --host=127.0.0.1 --port=5432

The --query-source=pgstatstatements flag tells PMM to use the pg_stat_statements extension for query data. You need to enable this extension in PostgreSQL first:

1
2
3
4
5
6
7

-- In postgresql.conf or via ALTER SYSTEM:
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.max = 10000;

-- Then restart PostgreSQL and create the extension:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Connecting a MySQL Database

1
2
3
4
5

# Add MySQL service for monitoring
pmm-admin add mysql --username=pmm_user --password=secret_password \
  --query-source=slowlog \
  --service-name=production-mysql \
  --host=127.0.0.1 --port=3306

For MySQL slow query log parsing, enable the slow query log:

1
2
3

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Using the Query Analytics Dashboard

Once connected, the QAN dashboard at /graph/d/pmm-qan/ shows:

• Top queries by load: Queries ranked by total execution time, sorted by frequency × average duration
• Query execution plans: Visual EXPLAIN output for each query pattern
• Latency distribution: Histogram showing P50, P95, P99 response times
• Query fingerprint aggregation: Similar queries grouped by normalized pattern (e.g., SELECT * FROM users WHERE id = ?)

The dashboard automatically detects new query patterns and can alert when query latency exceeds your defined thresholds. For production deployments, pair PMM with a self-hosted alerting solution for PagerDuty-style notifications.

pg_activity: Real-Time PostgreSQL Monitoring

pg_activity is a command-line tool that provides a top-like view of PostgreSQL activity. It shows running queries, lock contention, transaction age, and database-level statistics in real-time — ideal for terminal-based database administration.

Installation

1
2
3
4
5
6
7
8
9

# Install via pip (requires Python 3.7+)
pip3 install pg-activity

# Or install from your distribution's package manager
# Debian/Ubuntu
sudo apt install pg-activity

# Fedora
sudo dnf install pg_activity

Basic Usage

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# Connect to local PostgreSQL as default user
pg_activity

# Connect to a remote server with specific credentials
pg_activity -h db.example.com -p 5432 -U postgres -d production

# Filter to show only queries running longer than 5 seconds
pg_activity --min-duration 5

# Show only queries from a specific database
pg_activity -d myapp_production

Understanding the Interface

The pg_activity interface displays:

• Header: PostgreSQL version, uptime, number of connections, transactions/sec, tuples read/sec
• Process list: Each running query with PID, database, user, duration, state, and the full query text
• I/O statistics: Read/write throughput per database
• Lock information: Active locks and blocked queries highlighted in red

Key keyboard shortcuts:

• Space: Pause/resume display refresh
• R: Reverse sort order
• q: Quit
• d: Filter by database name
• u: Filter by user name

pg_activity is particularly effective during incident response. When a production database slows down, connecting with pg_activity --min-duration 2 immediately shows which queries are causing the bottleneck, how long they have been running, and whether they are blocked by locks.

pgcenter: PostgreSQL Command-Line Diagnostics Toolkit

pgcenter is a comprehensive command-line toolkit for PostgreSQL administration and troubleshooting. Unlike pg_activity which focuses on real-time query monitoring, pgcenter provides a suite of tools for viewing statistics tables, managing configuration, and performing deep diagnostic analysis.

Installation

1
2
3
4
5
6
7

# Download the latest release binary
wget https://github.com/lesovsky/pgcenter/releases/download/v0.9.2/pgcenter_0.9.2_linux_amd64.tar.gz
tar -xzf pgcenter_0.9.2_linux_amd64.tar.gz
sudo mv pgcenter /usr/local/bin/

# Or build from source
go install github.com/lesovsky/pgcenter@latest

Key Commands

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# Top-like view of PostgreSQL statistics
pgcenter top -h 127.0.0.1 -U postgres

# View pg_stat_statements statistics
pgcenter stat statements -h 127.0.0.1

# View table-level I/O statistics
pgcenter stat user_tables -h 127.0.0.1

# View index usage statistics (find unused indexes)
pgcenter stat user_indexes -h 127.0.0.1

# Record statistics to a file for later analysis
pgcenter record -h 127.0.0.1 -f /var/log/pgcenter_$(date +%Y%m%d).csv

# Replay a recorded statistics file
pgcenter replay -f /var/log/pgcenter_20260423.csv

Analyzing Query Performance with pg_stat_statements

The pg_stat_statements view is the foundation of PostgreSQL query profiling. pgcenter provides a formatted view of this data:

1
2
3
4
5
6
7
8

# Show top queries by total execution time
pgcenter stat statements --sort-by total_time --limit 20

# Show queries with the highest mean execution time
pgcenter stat statements --sort-by mean_time --limit 20

# Show queries with the most disk I/O
pgcenter stat statements --sort-by blk_read_time --limit 20

Output columns include:

• Total time: Cumulative execution time across all calls
• Mean time: Average execution time per call
• Rows: Total number of rows returned or modified
• Blk read/write time: Time spent reading/writing shared buffers
• Temp file count/size: Number and size of temporary files created

The record and replay functionality is particularly valuable for capacity planning. Record statistics during peak hours, then replay the data to identify trends in query patterns, table growth, and connection usage over time.

Complementary Tool: HypoPG for Index Testing

While not a profiling tool itself, HypoPG is a PostgreSQL extension that lets you test hypothetical indexes without actually creating them. This is essential for the optimization workflow: identify slow queries with PMM or pg_activity, then use HypoPG to test whether a specific index would improve performance before committing to the schema change.

1
2
3
4

# Install HypoPG extension
sudo apt install postgresql-16-hypopg  # Debian/Ubuntu
# or
sudo dnf install hypopg_16  # Fedora/RHEL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- Enable the extension
CREATE EXTENSION hypopg;

-- Create a hypothetical index (no actual index is created)
SELECT * FROM hypopg_create_index(
  'CREATE INDEX idx_users_email ON users (email)'
);

-- Check if the query planner would use the hypothetical index
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- The output will show "Index Scan using <145893_btree_users_email>"

-- Clean up hypothetical indexes
SELECT * FROM hypopg_reset_index();

This workflow — profile with PMM, test with HypoPG, deploy the winning index — is the most efficient path to query optimization without production risk.

Step-by-Step: From Slow Query to Fixed Query

Here is a complete workflow combining all three tools to identify and resolve a slow query in production:

Step 1: Detect the Problem with PMM QAN

Open the PMM Query Analytics dashboard. Sort by “Load” to see the queries consuming the most total database time. Look for queries with high P99 latency or a growing execution count.

1
2

# You can also query the slow query log directly
tail -f /var/log/mysql/slow.log | grep -A 5 "Query_time"

Step 2: Inspect Active Queries with pg_activity

1
2

# Connect to the affected database
pg_activity -h 127.0.0.1 -U postgres -d production_db --min-duration 1

Look for queries in the “active” state with durations above your SLA threshold. Note the PID and the full query text.

Step 3: Analyze the Query Plan

1
2
3
4
5
6
7
8

-- Get the execution plan with actual timing
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
ORDER BY o.total DESC
LIMIT 100;

Look for Sequential Scan nodes, high row estimates, or excessive buffer reads.

Step 4: Test a Hypothetical Fix

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- If the EXPLAIN shows a sequential scan on customer_id, test an index
SELECT * FROM hypopg_create_index(
  'CREATE INDEX idx_orders_customer_id ON orders (customer_id)'
);

-- Re-run EXPLAIN to see if the planner chooses the index
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > '2026-01-01'
ORDER BY o.total DESC
LIMIT 100;

Step 5: Monitor the Improvement

After deploying the index, use pgcenter to record baseline statistics, then compare with post-deployment data:

1
2
3
4
5
6
7
8

# Record before deployment
pgcenter record -h 127.0.0.1 -f /tmp/before.csv

# Deploy the index
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

# Record after deployment
pgcenter record -h 127.0.0.1 -f /tmp/after.csv

FAQ

What is the difference between database monitoring and query profiling?

Database monitoring tracks overall server health metrics like CPU usage, memory consumption, connection counts, and replication lag. Query profiling specifically analyzes individual SQL statements to identify slow queries, understand execution plans, and find optimization opportunities. Tools like Percona PMM do both, while pg_activity focuses primarily on real-time query visibility.

Can I use Percona PMM for both MySQL and PostgreSQL?

Yes. PMM supports MySQL, MariaDB, PostgreSQL, and MongoDB. You can monitor a mixed database environment from a single PMM server instance. Each database type has its own set of dashboards and query analyzers within the same interface.

Is pg_activity suitable for production monitoring?

pg_activity is designed for interactive, real-time inspection rather than continuous monitoring. It does not store historical data or provide alerting. Use it during active troubleshooting sessions — for example, when you receive an alert from your monitoring system and need to immediately see what queries are running. For continuous monitoring, deploy PMM or a similar platform.

How does HypoPG differ from creating a real index?

HypoPG creates “hypothetical” indexes that exist only in the query planner’s memory. The actual table data is not indexed, so there is zero storage overhead and zero impact on write performance. The planner treats the hypothetical index as if it existed when generating execution plans, allowing you to test optimization strategies without any risk to the production database.

What is the resource overhead of running PMM agents?

PMM agents typically consume 1–3% CPU and 50–100MB of memory per monitored database instance. The agent collects metrics at 1-second intervals for system metrics and 10-second intervals for query metrics. For very high-throughput databases (10,000+ queries per second), you may want to increase the query collection interval to reduce overhead.

Can pgcenter work with PostgreSQL versions older than 12?

Yes. pgcenter supports PostgreSQL 9.6 and newer. The available statistics views vary by version — newer PostgreSQL versions expose more detailed statistics through additional pg_stat_* views. pgcenter automatically adapts its display based on the connected server version.

Pre-Publish Checklist