How to Use Redshift STL Views for Logging and Monitoring

Introduction

If you work with Amazon Redshift, it’s important to monitor query execution and system activity. This will help you improve performance, troubleshoot issues, and ensure smooth operations. This will help you optimize performance, troubleshoot issues, and ensure smooth operations. Fortunately, Redshift provides a powerful set of System Tables and Views (STL) that allow you to access detailed information about queries, connections, and various system metrics.

In this article, we will dive into the basics of Redshift STL views, focusing on stl_query, stl_connection_log, and other essential views for logging and monitoring.

What are Redshift STL Views?

Redshift System Tables and Views (STL) are a collection of built-in views that store information about the internal operations of the Redshift cluster. These views provide valuable insights into query execution, system performance, resource utilization, and more. By querying the STL views, you can gain a deep understanding of how your queries are running, identify bottlenecks, and make informed decisions to optimize your Redshift workloads. Redshift generates STL system views from log files.

Exploring stl_query View

One of the most commonly used STL views is stl_query. This view contains a record of every query executed on the Redshift cluster, along with detailed information about each query. Let’s take a closer look at how to use stl_query for logging and analysis.

Querying stl_query

To access the information stored in stl_query, you can run a simple SELECT statement. Here’s an example:

SELECT query, starttime, endtime, elapsed, aborted
FROM stl_query
ORDER BY starttime DESC
LIMIT 10;

This query retrieves the last 10 queries executed on the Redshift cluster, along with their start time, end time, elapsed time, and whether they were aborted. The result will give you a quick overview of recent query activity.

Analyzing Query Performance

By digging deeper into the stl_query view, you can gain valuable insights into query performance. For example, you can identify long-running queries by filtering on the elapsed column:

SELECT query, elapsed, substring(querytxt, 1, 50) as query_snippet
FROM stl_query
WHERE elapsed > 60000 -- Query duration in milliseconds
ORDER BY elapsed DESC;

This query retrieves queries that took longer than 60 seconds to execute, along with a snippet of the query text. By analyzing these slow queries, you can identify performance bottlenecks and optimize your SQL statements accordingly.

Monitoring Connections with stl_connection_log view

Another important STL view for logging is stl_connection_log. This view records information about client connections to the Redshift cluster, including connection start and end times, user names, and client IP addresses. Let’s explore how to use stl_connection_log for monitoring connections.

Querying stl_connection_log

To view the connection log, you can run a simple SELECT query on stl_connection_log:

SELECT username, starttime, endtime, duration, remotehost, remoteport
FROM stl_connection_log
ORDER BY starttime DESC
LIMIT 10;

This query retrieves the last 10 connections to the Redshift cluster, including the username, start time, end time, duration, remote host, and remote port. By monitoring the connection log, you can track user activity and identify any suspicious or unauthorized connections.

Analyzing Connection Patterns

You can also use stl_connection_log to analyze connection patterns and identify potential issues. For example, you can count the number of connections per user:

SELECT username, COUNT(*) as connection_count
FROM stl_connection_log
GROUP BY username
ORDER BY connection_count DESC;

This query gives you an overview of the number of connections made by each user. If you notice an unusually high number of connections from a particular user, it could indicate a problem or an inefficient application design that needs attention.

Additional Redshift Logging and Monitoring Views

In addition to `stl_query` and `stl_connection_log`, Redshift provides several other system views that help monitor cluster activity, troubleshoot performance issues, and track user behavior.

Tracking Query Errors: stl_error

Logs all query-related errors. Useful for debugging failed queries.

SELECT username, query, substring(error, 1, 50) AS error_snippet, starttime
FROM stl_error
ORDER BY starttime DESC
LIMIT 10;

Monitoring Workload Queues: stl_wlm_query

Shows how Redshift’s Workload Management (WLM) is handling queued and running queries.

SELECT queue_start_time, total_queue_time, total_exec_time, slot_count
FROM stl_wlm_query
ORDER BY queue_start_time DESC
LIMIT 10;

Auditing Data Loads: stl_load_commits

Tracks COPY command usage, helping you monitor load durations and efficiency.

SELECT query, table_name, lines_scanned, num_files, start_time, end_time,
DATEDIFF(seconds, start_time, end_time) AS duration
FROM stl_load_commits
ORDER BY start_time DESC
LIMIT 10;

Real-Time Monitoring: STV Views

• stv_recents – Recently completed queries.
• stv_inflight – Currently executing queries and duration.
• stv_wlm_service_class_config – Current WLM config and limits.
• stv_blocklist – Disk block usage and space allocation.

Enhancing Redshift Logging with DataSunrise

While Redshift STL views provide detailed logs and real-time metrics, they do not include granular access controls, masking, or external audit integration. That’s where DataSunrise adds value.

• Real-time query auditing across all Redshift sessions
• Dynamic data masking based on user roles, IPs, or query content
• Centralized logging with long-term retention and search
• Block risky operations like full table scans or exports
• Compliance alignment with GDPR, HIPAA, PCI DSS, and more

DataSunrise sits between clients and your Redshift cluster, analyzing, logging, and controlling traffic without changing your data structure.

Conclusion

Redshift STL and STV views provide powerful tools for logging query behavior, tracking access, and optimizing system performance. But for environments that require audit trails, fine-grained data access control, and compliance enforcement, native logging alone isn’t enough.

DataSunrise enhances Redshift visibility and control with intelligent masking, alerting, and audit capabilities. Request a demo to see how you can take Redshift monitoring to the next level while meeting enterprise data protection standards.