How to Configure DuckDB Slow Query Log
You set up a duckdb slow query log by turning on query profiling in your session. You then direct the output to a local file to track run times. Teams often use this setup to find slow analytical reads in their apps. Still, local database files often run with broad user roles and shared host permissions. When every developer has full host rights, anyone can change the log rules or read private data. How a duckdb slow query log works in practice DuckDB runs inside your app process rather than
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 duckdb slow query log by turning on query profiling in your session. You then direct the output to a local file to track run times. Teams often use this setup to find slow analytical reads in their apps. Still, local database files often run with broad user roles and shared host permissions. When every developer has full host rights, anyone can change the log rules or read private data.
How a duckdb slow query log works in practice
DuckDB runs inside your app process rather than as a separate server. Because of this design, it does not have a background log service running at all times. You must turn on profiling inside each active session to capture run metrics. Once you turn it on, the engine measures how long each step takes to finish.
The engine can write these timing details in standard text or structured formats. Teams usually send this output to a local disk path after a statement finishes. You can then check the file to see which joins or scans took the most time. An external script or log shipper reads those files and filters out fast runs. This workflow gives you a list of slow statements that need attention from your team.
What native profiling solves and what it misses
Native profiling helps engineers spot bad join orders and missing filters in large analytical reads. It shows exact timing for each stage of the query plan after the run ends. This visibility helps teams fix slow reports and cut down on memory spikes.
Still, session profiling only records what happened after a query finishes on the host. It does not check who ran the statement or stop bad queries before they start. Because the database runs in the local process, any user with file access can turn logging off. Host permissions decide who can open the database file, not what individual statements do in flight.
Also, native logs often capture full query text along with sensitive filter values. If a query filters by emails, phone numbers, card numbers, or national IDs, those values sit in plain text. Anyone who reads the log file can see those raw fields on the disk. Data masking is needed to rewrite matched fields in results before they reach the user or log.
End-to-End Encryption + Log Aggregation & Correlation: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Managing query logs across shared team hosts
When teams run analytical jobs on shared servers, keeping log files safe gets much harder. Multiple users or scripts often share the same host role to read the same data files. If two people log in with the same Linux user, the native log cannot tell them apart. You see the slow query in the file, but you do not know which person ran it.
Shared hosts also make it hard to keep log settings consistent across every run. One script might turn on profiling, while an interactive user forgets to set the rule. Because each session sets its own rules, your team misses slow ad-hoc queries run by hand. To get complete audit trails, you need to link every session to a real user identity. You also need a way to enforce logging rules outside the local database session.
Add runtime guardrails and masking beside your databases and APIs
Login rules, role grants, and CI checks govern who connects to your stack. Yet any allowed script, engineer, or AI agent still runs commands with the full scope of its role. For workloads on Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes, SSH, or internal HTTP services, hoop.dev sits inline in the protocol beside the service as an open-source proxy, a sidecar for your database or API. On every production query an agent or human runs, hoop.dev decides whether to pass, mask, block, or escalate it for review.
Before a query runs, hoop.dev guardrails check the statement against your rules, an AI Analyzer you configure can score intent or hold risky calls for human approval, and hoop.dev data masking rewrites sensitive fields in responses in memory. Explore the MIT-licensed project on GitHub.
Frequently asked questions
Does DuckDB have a built-in time threshold for slow queries?
No, the engine records timing for all profiled runs in that session. You must filter the output files by total run time using your own log tool.
Where does the engine store query profile files?
The engine writes profile output to the local file path you choose in your session settings. Make sure your app role has write permissions for that folder.
Can users turn off query profiling during their session?
Yes, anyone with session control can change profiling settings at any time. Native settings do not lock logging rules against changes by the local user.
Related guides
In this post