How to Configure ClickHouse Slow Query Log
You set up a clickhouse slow query log by turning on query logging in server settings. Then you filter the system query table by run time to find slow reads and writes. Teams often share broad admin roles so developers can check these system tables during an outage. Still, broad permissions expose raw query text and sensitive fields to every user on the team. How the clickhouse slow query log works in practice ClickHouse does not write slow statements to a separate text file on disk by defaul
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
You set up a clickhouse slow query log by turning on query logging in server settings. Then you filter the system query table by run time to find slow reads and writes. Teams often share broad admin roles so developers can check these system tables during an outage. Still, broad permissions expose raw query text and sensitive fields to every user on the team.
How the clickhouse slow query log works in practice
ClickHouse does not write slow statements to a separate text file on disk by default. Instead, the database engine records query activity inside an internal system table. Each row stores the query text, run duration, memory use, and the user who ran it. Admins find slow statements by reading this table and sorting rows by elapsed time.
To control who can read these logs, teams use SQL-driven access control. Turning this on takes a storage folder on the server and an admin user account. That admin account needs access management rights turned on to create new roles and users. A user can only grant the privileges that they already hold themselves.
When you create a user, you set their login method, allowed hosts, and default roles. You can also attach settings profiles and resource quotas to that user or role. Settings profiles let you set query time limits and log thresholds for specific groups of users. Quotas track resource usage over time so one bad query does not lock up the server.
You can also lock down the query log table using column-level read grants. When you grant read rights on specific columns, the user can only query those named columns. If that user runs a wildcard read across all columns, the database refuses the request by default. The engine returns no data at all unless the table holds only the granted columns. Recent database versions also let you grant rights across tables that share a name prefix.
What native log roles solve and what they leave open
Native roles and column grants let you share timing data while hiding other system columns. An engineer can check query run time and memory usage without reading every field in the table. Settings profiles also stop runaway reads before they crash the cluster.
ClickHouse Access Management + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Still, native database permissions only check who logs in and which columns they request. They do not inspect what individual statements do in flight or mask sensitive text inside columns. For example, the query text column in the system log often holds raw customer filter values. If a slow read filtered by email or card number, that value sits in the log.
Granting read rights on the query text column shows those raw emails and phone numbers. Withholding that column makes it impossible for the developer to see which query ran slow. Native grants also cannot pause a risky statement for peer approval before it runs on production. Teams must choose between blocking developers from the logs or exposing raw data inside query strings.
Check ClickHouse queries and result columns on the wire
ClickHouse users and row policies decide who connects. They do not check each statement on the wire or redact sensitive string columns in query results. With hoop.dev, you run an open-source proxy, a sidecar for your ClickHouse, inline in ClickHouse's native TCP protocol. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.
Multi-statement queries and table mutations are split and checked by hoop.dev guardrails, which refuse banned actions with a native ClickHouse error or hold risky ones for approval. On returned rows, hoop.dev data masking rewrites String and FixedString columns in memory, and the audit trail logs each query and verdict. See the MIT-licensed source on GitHub.
Frequently asked questions
Where does ClickHouse store slow query records?
The database stores finished and running query records inside internal system tables rather than separate text files. You query these tables and filter by run duration to find slow statements.
How do you limit who can read the query log table?
You create a role with column-level read grants on the system query log table. Users with this role can read timing and memory columns without seeing restricted columns.
Why do wildcard queries fail for users with column grants?
When a user only has rights on specific columns, ClickHouse blocks wildcard reads on that table. Users must name the exact allowed columns in their query to get results back.
Related guides
In this post