Keeping Redshift System Table History Past 7 Days with S3 Tables

Redshift keeps its SYS_* monitoring views for seven days in cluster, which is fine until an auditor or an incident review asks for something older. Until now the usual fix was a custom export job to S3 and a Glue crawler to keep it queryable. AWS just added a native system table integration with S3 Tables that skips that pipeline entirely: Redshift can write system table data straight into Amazon S3 Tables, in Apache Iceberg format, with no ETL to maintain.

What it runs on

This applies to Redshift Provisioned on RA3 and RG instance types, and to Redshift Serverless. Older provisioned instance families are not supported. AWS launched it across a wide set of commercial regions rather than a single preview region, but region coverage is the kind of detail that shifts, so confirm your region against the current availability list before you enable it.

Permissions you need

The principal that enables the feature needs:

• redshift:EnableLogging for a provisioned cluster, or redshift-serverless:UpdateNamespace for Serverless
• s3tables:CreateTableBucket, s3tables:PutTableBucketEncryption, and s3tables:PutTableBucketPolicy, to stand up the aws-redshift table bucket

That is the whole permission set. Once the bucket exists, Redshift creates namespaces and tables inside it through a service trust relationship with S3 Tables, so nobody needs standing permission to create namespaces or tables themselves.

1. Decide what you are retaining and how it should be organized

You choose which SYS_* views to publish, from SYS_QUERY_HISTORY and SYS_QUERY_TEXT to SYS_CONNECTION_LOG and SYS_VACUUM_HISTORY, or all of them at once. You also pick a deployment model. Per-warehouse keeps each cluster's data in its own set of tables, which matters if SYS_QUERY_TEXT or SYS_PROCEDURE_MESSAGES could contain sensitive literal values. Consolidated writes every warehouse in the account and Region into shared tables, distinguished by a warehouse_name column, which is better for cross-warehouse observability.

Supported system tables

You can select any of these SYS_* views.

2. Enable delivery on the cluster

For a provisioned cluster, this reuses the existing logging API with a new destination type. The principal running this needs redshift:EnableLogging plus permission to create and configure an S3 table bucket named aws-redshift in the account.

-- publish selected system tables to S3 Tables, consolidated across the account
aws redshift enable-logging \
    --cluster-identifier my-redshift-cluster \
    --log-destination-type s3table \
    --log-exports sys_query_history sys_query_text sys_connection_log \
    --s3-table-granularity account
 
-- Redshift Serverless uses update-namespace instead
aws redshift-serverless update-namespace \
    --namespace-name my-namespace \
    --log-destination-type s3table \
    --s3-table-action Enable \
    --s3-table-names all \
    --s3-table-granularity namespace
  

3. Confirm data is actually flowing

Delivery runs in batches at a fixed frequency and only includes completed activity, so a query still running will not show up until it finishes. Check the last ingestion time per view with describe-logging-status on a provisioned cluster or get-namespace on Serverless.

aws redshift describe-logging-status \
    --cluster-identifier my-redshift-cluster
  

4. Register the table bucket with Glue Data Catalog

Querying the retained data from Redshift, Athena, or any other Iceberg-compatible engine requires the aws-redshift S3 table bucket to be integrated with AWS Glue Data Catalog first. This is a one-time step per account and Region, not something you repeat per cluster.

5. Query the history from Redshift

Once the catalog integration is in place, point an external schema at it and query the historical view like any other table. The rows carry warehouse_name, warehouse_namespace_arn, and s3_tables_ingestion_time alongside the original SYS_QUERY_HISTORY columns, which is what lets a consolidated deployment separate one cluster's activity from another's.

-- replace <glue_database> with the database created by the S3 Tables / Glue integration
CREATE EXTERNAL SCHEMA redshift_history
FROM DATA CATALOG
DATABASE '<glue_database>'
IAM_ROLE 'arn:aws:iam::111122223333:role/RedshiftHistoryReadRole';
 
-- queries older than the 7-day in-cluster window
SELECT warehouse_name, query_id, start_time, elapsed_time
FROM redshift_history.sys_query_history
WHERE start_time < dateadd(day, -7, getdate())
ORDER BY elapsed_time DESC
LIMIT 20;
  

What it costs

Writing the data out of Redshift into S3 Tables is free. What you pay for is standard S3 Tables storage and maintenance, meaning compaction and snapshot upkeep, on whatever you retain, plus normal usage pricing for the engine you query it with, whether that is Redshift Spectrum, Athena, or something else. Since data without an expiration policy is kept forever, an unset retention policy is really a storage cost decision, not just a compliance one.

Gotchas worth knowing before you enable this

• Disabling and re-enabling, or switching between per-warehouse and consolidated, never backfills. Whatever happened during the gap is gone for good.
• Delivered rows are immutable. You cannot update or delete individual rows through Redshift, only through S3 Tables record expiration.
• Dropping the S3 Tables permanently deletes everything retained in them and Redshift does not recreate them automatically. Re-enabling starts a fresh table with no history.
• A customer managed KMS key has to be set the first time you enable the feature. Changing it later means dropping the tables, and the retained data, and starting over.
• The views marked with an asterisk above need patch P203 or later. On an older patch the tables get created but stay empty.
• Everything is scoped to a single account and a single region. Cross-account or cross-region analysis means combining results at query time, not a single unified table.
• Only completed activity is delivered. A query that is still running will not show up until it finishes, aborts, or is canceled.

Before you run this in production

Set a record expiration policy directly in S3 Tables once delivery is running, since without one the data is kept indefinitely and keeps accruing storage cost. Start with the per-warehouse model if SYS_QUERY_TEXT or SYS_PROCEDURE_MESSAGES might carry sensitive literal values, and only move to consolidated once you have confirmed what those views actually capture in your environment. 


If you are pulling SYS_QUERY_EXPLAIN history into this pipeline for performance work, PlanTrace is a free tool for turning those query plans into something you can actually read.

Five Ways Redshift Serverless Quietly Eats Your Budget

It is Friday, the queries are running, and nobody is watching the bill. That is the whole charm of Redshift Serverless: you stop thinking about nodes and resizes. It is also exactly how money slips out the door. Below are five habits that run up RPU charges while you look the other way, all backed by the docs.

1. Running with no maximum RPU-hours limit

By default, Serverless scales to meet load and meters you per second. With no ceiling on total consumption, one rough week of ad-hoc queries can scale well past whatever number you had in your head. Set a daily, weekly, or monthly RPU-hours limit on the workgroup and pick an action when it trips: alert, log, or turn off user queries entirely. See maximum RPU hours usage limit for the setup.

aws redshift-serverless create-usage-limit \
  --resource-arn arn:aws:redshift-serverless:us-east-1:123456789012:workgroup/analytics-wg \
  --usage-type serverless-compute \
  --amount 100 \
  --period weekly \
  --breach-action deactivate
  

2. Leaving idle sessions with open transactions

An open transaction keeps compute alive. If a session sits idle with a transaction still open, RPUs keep getting consumed until the session closes, and Serverless will wait up to six hours before ending it for you. The advice in the billing guide is blunt: close your transactions, and resist the urge to extend SESSION TIMEOUT unless a specific use case demands it.

3. Letting connection pools spam health checks

Here is the sneaky one. Serverless counts every incoming query as billable user activity, and that includes the lightweight keepalive pings your connection pool fires on a schedule. A chatty pool with an aggressive validation interval can keep compute warm during hours when no real work is happening. Check your pool's keepalive and validation settings, because those tiny queries add up.

4. Skipping the MaxRPU scaling cap

The RPU-hours limit from item 1 caps your total spend over a period. MaxRPU is a different lever: it caps how high you can scale at any single moment. Without it, one heavy query is free to grab a large slice of compute all at once. Set MaxRPU to the highest burst level you are actually willing to pay for, and Serverless will scale within that line. The mechanics live in the compute capacity docs.

5. Oversizing the base capacity

Base RPU is the floor that is always ready to serve queries, and the default sits at 128. If your steady-state workload only needs 32, you are paying for headroom you rarely touch. Start the base lower and let autoscaling cover the spikes. You can adjust it anywhere from 8 to 512 in steps of 8, at any time, with no impact on running queries.

Wrapping Up

None of these require heroic tuning. Set an RPU-hours limit and a MaxRPU cap, keep transactions short, audit your pool's keepalive, and right-size the base. The one habit worth building today: configure usage limits from day one rather than after the first surprising invoice arrives.

Killing Explicit Sort Steps in Redshift with the Right SORTKEY

You paste a slow Redshift query plan into PlanTrace and one of the tuning insights reads "SORTKEY optimization candidate", pointing at an explicit sort step that's adding cost you don't need to pay. 

A sort step runs at query time, every time, and on a wide table with millions of rows it can dominate the total cost. 

The fix is usually a SORTKEY that matches what the query is sorting on.

What an explicit sort step actually is

When your query has an ORDER BY, a window function, or a merge join that needs ordered input, Redshift has two options. If the data is already physically stored in the order it needs, it just reads it. If not, it sorts the rows on the fly. 

That on the fly sort is the explicit sort step, and it shows up as an XN Sort operation with its own Sort Key line and a cost range. 

Spotting it in raw EXPLAIN text means scanning indentation and matching cost numbers by eye. This is where reading the plan as a graph helps: PlanTrace renders each node with its cost broken out, so the expensive sort is obvious instead of buried, and the insight names the exact table and column behind it. The underlying mechanics are documented in the AWS guide on reviewing query plan steps.

What causes it?

The root cause is simple: the order the query needs does not match the physical order the table is stored in. A table with no SORTKEY, or with a SORTKEY on columns the query doesn't use, forces Redshift to materialize and sort the result set at runtime. The wider the rows and the larger the row count, the more expensive that becomes. In the graph you'll see the cost concentrated on the sort while the scans underneath look comparatively cheap, which is exactly the pattern the SORTKEY insight keys off of.

How to fix it

Define a SORTKEY that matches the column the query orders or joins on. When the table is already sorted that way, Redshift can skip the runtime sort and read rows in order straight from disk. 

Add the column to the SORTKEY definition with ALTER TABLE, which lets you change sort keys on existing tables without recreating them and without blocking concurrent reads or writes, as noted in the Redshift sort key recommendation announcement.

-- Add a compound sort key matching your ORDER BY / join column
ALTER TABLE lineitem
ALTER COMPOUND SORTKEY (l_quantity);

-- Or let Redshift manage it automatically
ALTER TABLE lineitem ALTER SORTKEY AUTO;
  

After the change, sort the existing data so the new key takes effect, then re-run EXPLAIN:

VACUUM SORT ONLY lineitem;
ANALYZE lineitem;

EXPLAIN
SELECT l_quantity, sum(l_extendedprice)
FROM lineitem
GROUP BY l_quantity
ORDER BY l_quantity;
  

Just note that on very large tables, ALTER table and VACUUM can take significant time to complete.  

Paste the new plan back into PlanTrace and compare it against the old one. 

If the SORTKEY matches, the XN Sort node either drops out of the plan or its cost falls sharply, and the candidate insight no longer fires. That before and after check is the only reliable confirmation. 

A SORTKEY that doesn't line up with the query's order changes nothing. Everything runs client side in your browser, so your plans are never stored or sent anywhere.

Wrapping up

An explicit sort step is Redshift telling you the data isn't stored in the order your query wants. Match the SORTKEY to the ORDER BY or join column, vacuum and analyze, then compare the plans to confirm the sort is gone. Letting PlanTrace surface the candidate and verify the result beats squinting at cost numbers in raw text. And remember that a sort key only helps queries that actually filter, join, or order on those columns, so optimize for the patterns that matter most.

Optimizing Redshift Performance by Configuring WLM Queues

Efficient query performance in Amazon Redshift often comes down to how well you manage workload concurrency. Redshift's Workload Management (WLM) queues enable you to control how queries share resources, helping avoid bottlenecks during peak loads. Properly configuring WLM queues ensures critical queries get the resources they need while maximizing cluster throughput.

This post will guide you through the basics of WLM queue configuration and share actionable steps to optimize query execution and concurrency for your Redshift workloads.

Automatic WLM vs. Manual WLM

Before diving into manual configuration, it's important to note that Amazon recommends Automatic WLM as the default approach for most workloads. With Automatic WLM, Redshift dynamically determines resource utilization as queries arrive and adjusts concurrency and memory allocation on the fly — without requiring manual tuning. It maximizes total throughput and handles unpredictable or changing workloads more efficiently than static manual configuration.

Manual WLM is best suited for scenarios where your workload patterns are predictable, you need to guarantee multiple workload types run simultaneously with fixed resource allocations, or you need to throttle certain query types at specific times of day. If you proceed with manual WLM, be prepared to monitor and tune it regularly as workloads evolve.

Step-by-Step Guide (Manual WLM)

1. Understand Redshift WLM Queues and Slots
WLM enables you to define queues with resource slots. Each query consumes one or more slots depending on queue configuration. Slots allocate memory and concurrency allowing multiple queries to run simultaneously without resource contention.
Learn more about WLM fundamentals here: Redshift WLM Documentation.

2. Analyze Your Query Workload
Identify query types and their resource demands. Separate lengthy ETL or analytical queries from short, high-priority reporting queries. Segmenting workloads allows for assigning appropriate queue configurations matching query resource profiles.

3. Create or Modify Queues Based on Workload Types
For example, create a "high_priority" queue with fewer concurrency slots but higher memory allocation for fast-running reports, and a "default" queue for ad-hoc or batch queries running with more concurrency but less memory per query.

4. Adjust Slots and Memory Allocations
Use the AWS Console or Redshift parameter groups to configure slots via the wlm_json_configuration parameter. An example config snippet:

-- Example JSON for WLM queue definition in parameter group:
[
  {
    "query_group": "high_priority",
    "concurrency": 5,
    "memory_percent_to_use": 30
  },
  {
    "query_group": "default",
    "concurrency": 10,
    "memory_percent_to_use": 20
  }
]

This example balances memory and concurrency to prioritize faster queries effectively.

5. Monitor and Tune Using System Views
Query views like STV_WLM_QUERY_STATE and STL_WLM_QUERY to analyze queue usage, wait times, and query runtimes. Use this insight to iteratively adjust concurrency and memory settings.

Conclusion

Optimizing Redshift WLM queues is key to balancing concurrency and performance in mixed workloads. For most users, Automatic WLM is the recommended starting point. If manual WLM better fits your use case, classifying queries, assigning queues based on priority, and tuning memory and slot allocations will ensure efficient resource use and a better experience for high-priority queries.
Regular monitoring and tuning with Redshift system views complete the feedback loop for continuous improvement.

Official Documentation for more details:
Amazon Redshift Workload Management

PlanTrace: Stop Reading Redshift EXPLAIN Plans. Start Seeing Them

Introducing PlanTrace — a free, browser-based tool that turns raw execution plans into interactive graphs and actionable tuning insights.

plantrace.studyyourdata.com

PlanTrace graph view — color-coded cost nodes, execution flow arrows, and the tuning insights drawer, all in one screen.

If you work with Amazon Redshift, you've been there. You run EXPLAIN on a slow query, and you get back something like this:

XN Limit  (cost=2000000000000.00..2000000000125.00 rows=100 width=256)
  ->  XN Merge  (cost=2000000000000.00..2000000000125.00 rows=100 width=256)
        ->  XN Hash Join DS_DIST_BOTH  (cost=384729183746.25..903741293847.50 rows=32000000000 width=256)
              Hash Cond: ("outer".customer_id = "inner".customer_id)
              ->  XN Hash Join DS_BCAST_INNER  (cost=182938471623.00..723481920384.00 rows=28000000000 width=224)
                    ...

Now imagine 80+ operators deep. Nested joins, multiple redistributions, broadcasts stacked on top of each other, costs in the trillions. You're supposed to manually scan this and figure out what's killing performance.

Execution plans are not logs. They are structured graphs of data movement and compute decisions. They deserve to be treated that way.

That's why I built PlanTrace.

---

What PlanTrace does

PlanTrace takes your raw EXPLAIN output, parses it into a structured tree, and gives you three ways to reason about it — all in your browser, with no data ever leaving your machine.

The graph view

The moment you paste a plan and click Visualize, PlanTrace renders it as an interactive graph — nodes for every operator, arrows showing execution flow, and a minimap for large plans.

Each node is color-coded by cost intensity using a 6-level semaphore — from green (cheap) to red (expensive). You stop reading plans. You see them. Hotspots are obvious in seconds.

Hover over any node to see operator documentation inline, distribution movement notes (DS_BCAST_INNER, DS_DIST_BOTH, etc.), and cost range, estimated rows, and output width. Click a node to pin its detail panel — useful for digging into join conditions without losing your place in the graph.

The table view

Table view — sortable by any column, with cost severity pills making hotspots immediately visible.

Not a graph person? The table view flattens all operators into sortable columns — cost, rows, width, distribution code. Sort by total cost to instantly surface the most expensive operators. Toggle tree-order mode to preserve the parent/child structure while keeping sortable columns.

The cost severity pills make it hard to miss the bad stuff — red means it's on fire, and in a real production query, you'll know immediately which joins to attack first.

The chart view

Chart view — each dot is a plan operator. Top-left quadrant (high cost, few rows) flags overestimated work that needs ANALYZE. Dot size encodes row width.

The chart view plots every operator on a cost × estimated-rows scatter chart (both log scale). Four quadrants tell you immediately what kind of problem you're dealing with:

• Top-left (Overestimated Work) — high cost, few rows. Run ANALYZE — statistics are likely stale.
• Top-right (Balanced) — high cost, many rows. Expected for big operations; focus on join strategy.
• Bottom-left (Ideal) — low cost, few rows. This is what you're aiming for.
• Bottom-right (Cardinality Risk) — low cost estimate, many rows. Potential runtime spill.

Dot size encodes row width, so you can also spot wide rows that will blow up memory. Hover any dot for the full operator detail.

The operator legend

Every operator type has built-in documentation — click the legend to understand exactly what each operator does and when it becomes a problem.

One of the things I wanted PlanTrace to be is educational, not just diagnostic. Every operator type in the legend links to inline documentation explaining what it does, when it's fast, and when it's a problem. You don't need to have the Redshift docs open in a second tab.

The insights engine

This is the part I'm most proud of. PlanTrace runs a rule-based insight engine across your plan and surfaces prioritized, actionable recommendations sorted by severity — as many as the plan warrants. On a complex query, you might get 14 or more distinct findings.

To give you a sense of what it catches, here's what it found on a real monster query — 118 operators, 27 tables, costs reaching 2 trillion:

118Total operators

14Tuning insights

2TMax total cost

27Tables involved

The engine flagged, among other things:

• A redistribution chain of 6 DS_DIST_BOTH joins in the same branch — at least 6 full-cluster redistributions before any result is produced
• 14 broadcast joins (DS_BCAST_INNER), several of them on fact-sized tables with hundreds of millions of rows
• 8 direct fact-to-fact joins without pre-aggregation — a classic source of runaway cost
• A useless sort inside a subquery feeding directly into a hash join, which discards sort order anyway
• Scan amplification of 10,000,000× on one table — reading 1 billion rows to return 100
• 27 tables flagged for ALTER DISTSTYLE AUTO with ready-to-run SQL for each

Every insight is tied to an actual signal in the plan, no generic advice, no "consider your indexes" stuffs.

PDF export

Once you've analyzed a plan, click ↓ PDF in the toolbar. PlanTrace generates a formatted A4 report with an executive summary, all insights with severity color bands, an operator distribution quadrant breakdown, and the top 10 most expensive operators.

It's a ready-to-share document you can drop into a Slack thread, attach to a Jira ticket, or present to your team without having to explain what an EXPLAIN plan is.

Privacy by design

PlanTrace is entirely client-side. Your query plans never leave your browser. No backend, no analytics on your plans, no storage. Paste your plan, get your insights, close the tab — nothing is retained.

This matters because EXPLAIN output often reveals table names, column names, join structures, and data volumes that you probably don't want floating around in some SaaS vendor's logs.

---

How to use it

1. Go to plantrace.studyyourdata.com
2. Run EXPLAIN <your query>; in your Redshift client and copy the output
3. Paste it into the left panel and click Visualize
4. Switch between Graph / Table / Chart views
5. Open the insights drawer for tuning recommendations
6. Download the PDF if you want a shareable report

It's free. No account required. No install.

What it's not

PlanTrace is deliberately focused. It is not a query runner, not a monitoring dashboard, and not a replacement for runtime telemetry from views like SVL_QUERY_SUMMARY or STL_ALERT_EVENT_LOG. Those require a live connection and runtime execution data that EXPLAIN alone can't provide.

PlanTrace's job is narrower and more immediate: take a plan you already have, make it understandable, and tell you what to look at first.

---

PlanTrace is free to use — no account, no install, no data leaves your browser.

plantrace.studyyourdata.com →

If you work with Redshift and have a slow query you've been staring at, paste the EXPLAIN output and see what PlanTrace finds. I'd love to hear what you think — drop a comment below or find me on LinkedIn.

How to Find Expensive Queries in Amazon Redshift

Slow-running queries can degrade your Redshift cluster’s performance and lead to increased costs. Identifying the most expensive queries is crucial to optimize resource usage and improve overall system efficiency.

Step-by-Step Guide

1. Connect to your Redshift cluster.
   Use your preferred SQL client or the Redshift Query Editor to establish a connection with your cluster.


2. Query the stl_query system table for the most resource-intensive queries.
   The stl_query table logs all completed queries, including their runtime metrics. Use the following SQL to retrieve the top 10 queries with the longest execution time over the last 24 hours:

   SELECT query, userid, label, starttime, endtime,
          DATEDIFF(seconds, starttime, endtime) AS elapsed_seconds
   FROM stl_query
   WHERE starttime >= GETDATE() - INTERVAL '1 day'
   ORDER BY elapsed_seconds DESC
   LIMIT 10;
       



3. Retrieve the SQL text of the expensive queries.
   Use the stl_querytext table to get the text of the queries identified:

   SELECT text
   FROM stl_querytext
   WHERE query = <query_id>
   ORDER BY sequence;
       

   Replace <query_id> with the actual query ID from the previous step to analyze the query text for possible optimizations.



4. Focus your optimization efforts.
   Look for joins without indexes, large data scans, or missing filters and consider rewriting or adjusting these queries for better performance.

Conclusion

Tracking and analyzing expensive queries using Redshift’s system tables is a simple yet powerful way to maintain a healthy and efficient data warehouse environment. Regular monitoring helps reduce costs and speeds up analytics workflows.

For further reading, visit the official Amazon Redshift system tables documentation: Amazon Redshift System Tables