QuerySheriff

The most advanced open-source PostgreSQL performance monitoring

QuerySheriff helps developers find the root cause of PostgreSQL performance problems. It's built around query tags, which trace every slow query, lock wait and long transaction back to the application code that caused it.

Slow queries, traced to the code that sent them

A query tag is a short comment your application adds to each query, like /* operation=ListOrders service=api */. QuerySheriff puts the tags next to every query and lets you filter by them, so a slow query leads you to the job or endpoint that runs it.

Samples, transactions and lock waits carry the same tags, so you always know which part of your application you're looking at.

QuerySheriff Queries

Every query's history, down to its slow runs

Each query gets a chart of its calls and latency over time. Slow runs are saved with their real values and execution plans, so you can rerun the exact query.

Here, the request_id tag is the ID of the HTTP request behind each run. Paste it into Kibana or wherever your application logs live to see the whole request.

QuerySheriff Query details

Who blocked whom, and for how long

Each lock incident is one complete row, with the query that waited, the one holding the lock and the total wait time.

Again, query tags show what the wait cost your users. If the waiting query came from the endpoint that saves an order, the wait time is how long someone watched a spinner after clicking Save.

QuerySheriff Locks

Long transactions on a timeline

QuerySheriff lays out every query a long transaction ran, so you see where the minutes went: a slow query, a lock wait, or an app that left the transaction open and went off to do something else.

Here, a background job locks an order, sits idle for 14 minutes, then fails and rolls everything back.

QuerySheriff Transactions

PostgreSQL logs, sorted by what happened

QuerySheriff puts every PostgreSQL log entry into a category. A heatmap shows when each category spiked, and you can filter by any field or search the text.

When PostgreSQL logs a slow query, the log entry links to that query's page and its execution plan.

QuerySheriff Logs

Slack alerts and a weekly report

Give each server its own Slack channel. QuerySheriff posts there when monitoring stops, the database crashes, a query gets blocked or runs too long, or a transaction stays open too long.

A blocked-query alert shows both queries with their tags and the exact command to end the blocking session. Every Monday, a report lists the week's busiest queries, so you catch the one a recent deploy made expensive.

db-alerts

QuerySheriff App

Query blocked by a lock

A query has been waiting more than 4 minutes for a lock.

Waiting: operation=UpdateOrderStatus request_id=3f9c2a71e08b4d56 service=api

UPDATE orders SET status = 'CANCELLED', updated_at = now() WHERE id = 2731958

Blocking: operation=SyncOrders request_id=b81e0d4c97a2f315 service=worker

INSERT INTO orders (external_id, customer_id, status, total_cents, placed_at) SELECT external_id, customer_id, status, total_cents, placed_at FROM orders_import WHERE batch_id = 18342 ON CONFLICT (external_id) DO UPDATE SET status = EXCLUDED.status, total_cents = EXCLUDED.total_cents, updated_at = now()

Kill blocking: SELECT pg_terminate_backend(31877);

db-primary-1 · app_db

QuerySheriff App

Weekly report

Top 10 busiest queries in the last week:

• 35109 calls averaging 1.2 s operation=ListOrders service=api

open in querysheriff

• 161959 calls averaging 141 ms operation=SearchProducts service=api

open in querysheriff

• 3553 calls averaging 4.7 s operation=SyncOrders service=worker

open in querysheriff

db-primary-1

Light on PostgreSQL, built to grow

A small collector runs next to each PostgreSQL server and barely adds any load. Everything else happens in a backend that scales out, with the data in ClickHouse.

A PostgreSQL server running a billion queries a month leaves about 30 million rows of query statistics. ClickHouse keeps all of them in under 400 MiB.

ClickHouse is fast enough that the dashboard never keeps you waiting, even at this scale. A whole month of charts and query rankings loads in under half a second.

The backend scales by adding replicas, and ClickHouse can spread the data across a cluster.