How to Configure Redshift Slow Query Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a redshift slow query log by turning on audit logs and checking system views. Teams also set workload rules to catch and record long database runs. Most teams start by giving engineers broad admin roles to read these system tables. Those wide roles let too many people see raw data across the whole warehouse. How the redshift slow query log works The data warehouse does not use a single file for slow queries. Instead, it writes run times and query text into internal system views. Yo

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 redshift slow query log by turning on audit logs and checking system views. Teams also set workload rules to catch and record long database runs. Most teams start by giving engineers broad admin roles to read these system tables. Those wide roles let too many people see raw data across the whole warehouse.

How the redshift slow query log works

The data warehouse does not use a single file for slow queries. Instead, it writes run times and query text into internal system views. You can also send audit logs to cloud storage or a log group. Both methods help your team find bad queries that slow down the cluster.

To set this up, an admin turns on audit logging in the cluster settings. The admin picks a storage bucket or a log group as the target. The warehouse then sends query history and user logs on a regular schedule. For faster checks, engineers run queries against internal views right inside the database.

Teams also use workload rules to watch query run times. You can set a rule that spots any query running past a set time limit. That rule can log the event, alert the team, or stop the run. This gives admins a clear list of heavy queries to fix.

What native logging solves and where it falls short

Native logging helps data teams find bottlenecks and tune bad joins. It shows which user ran a heavy query and how long it took. Admins can see disk spills, queue wait times, and CPU load. This data is vital for keeping daily reports fast and costs low.

Still, reading these system views creates a big access problem for teams. By default, regular users only see their own queries in the system views. To see all slow queries across the cluster, a user needs superuser or admin rights. Teams often hand out these wide roles so engineers can debug slow runs.

Redshift Security + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Native database roles only decide who can log in and read tables. They do not check what single statements do while they run in flight. Once an engineer has an admin role for debugging, they can query sensitive tables too. Also, query logs often hold raw values like emails, phone numbers, or card numbers.

Managing permissions for query checks

To lower risk, teams try to build custom views over the system tables. An admin creates a view that shows query times and hides some user details. Then the admin grants read rights on that single view to the team. This helps, but it takes ongoing work to keep up across many clusters.

Even with custom views, engineers still need direct database logins to run their checks. Shared passwords or long-lived keys often sit in local files on laptops. If a key leaks, an attacker gets the same database access as the engineer. Teams need a way to inspect queries and mask fields like national IDs in results.

Check database and API queries on the wire

Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev sits inline in the wire protocol beside those services as an open-source proxy, a sidecar for your Postgres or API. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.

Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.

Frequently asked questions

Where does the warehouse store slow query history?

The warehouse stores query run times in internal system tables and views. You can also export audit logs to cloud storage or a log service. System tables hold recent history for a few days before rotating out.

Why can I only see my own queries in the system views?

Standard database users only have rights to view their own session activity. To see slow queries from other users, you need admin or monitoring roles. Many teams grant broad roles to fix this, which creates security risks.

Can slow query logs expose private user data?

Yes, the logged query text shows the exact filter values used in the statement. If a query filters by emails or card numbers, those values appear in the log. You should restrict who can read raw query text and mask sensitive fields in results.

The query runs. The emails stay hidden.