Query Tuning with Redshift

Welcome back! Now that you have learned how to load and export data efficiently between S3 and Amazon Redshift, it is time to focus on making your queries run as fast as possible. In analytics, speed is not just a luxury — it is essential for delivering insights quickly and keeping costs under control. Even with well-designed tables and efficient data loading, queries can slow down if Redshift does not have up-to-date information about your data or if the data is not stored optimally.

This lesson will show you how to use two important Redshift commands — ANALYZE and VACUUM — to keep your tables in top shape. These commands help Redshift understand your data and organize it for fast access. You will see how to measure query performance before and after tuning, so you can see the impact for yourself. By the end of this lesson, you will know how to keep your warehouse running smoothly and efficiently.

As your data changes — whether you are loading new records, updating existing ones, or deleting old data — Redshift needs to keep track of how your tables are organized. This is where ANALYZE and VACUUM come in.

The ANALYZE command updates the statistics that Redshift uses to plan queries. These statistics tell Redshift how much data is in each table, how the data is distributed, and what values are common. When statistics are out of date, the query planner might make poor choices, leading to slow queries. Running ANALYZE after loading or changing a lot of data helps Redshift make smarter decisions.

The VACUUM command reorganizes your table’s storage. Over time, as you load and delete data, the physical layout of your table can become fragmented. This makes it harder for Redshift to read data efficiently, especially if you are using sort keys. VACUUM helps by compacting your data and restoring the sort order, which can make queries much faster.

Both commands are safe to run and are a normal part of maintaining a healthy Redshift warehouse. You do not need to run them after every small change, but it is a good idea to use them after large data loads or major updates. The exact sequence depends on the scenario: after large bulk loads, running ANALYZE is usually the most important step, and VACUUM may not be necessary right away if sort order is still healthy. After heavy deletes, updates, or sort-sensitive operations, many teams prefer VACUUM first and then ANALYZE so the statistics reflect the table's post-vacuum layout.

Let’s walk through a practical example that shows how ANALYZE and VACUUM can improve query performance. You will use Python and the boto3 library to connect to Redshift and run SQL commands. If you are working in the CodeSignal environment, the necessary libraries and environment variables are already set up for you. If you are working elsewhere, make sure your environment variables for region, workgroup, database, secret ARN, and account ID are configured.

When benchmarking on Redshift Serverless, remember that the service abstracts workload management for you, but concurrency and shared compute still affect observed timing. If other queries are running at the same time, your measurements may fluctuate. In practice, it is a good idea to run multiple trials and compare patterns instead of trusting a single timing.

Here is a script that measures the time it takes to run a query before and after tuning. The script first defines a reusable statement runner and a small timing helper.

First, set up your environment and helper functions:

import boto3
import os
import time

# Load environment variables
REGION = os.environ.get('REGION')
WORKGROUP_NAME = os.environ['WORKGROUP_NAME']
DATABASE = os.environ['DB_NAME']
SECRET_ARN = os.environ['SECRET_ARN']
ACCOUNT_ID = os.environ['ACCOUNT_ID']

FACT_BORROW = f"warehouse_{ACCOUNT_ID}.fact_borrow_{ACCOUNT_ID}"
DIM_BOOK = f"warehouse_{ACCOUNT_ID}.dim_book_{ACCOUNT_ID}"

client = boto3.client('redshift-data', region_name=REGION)

def run_statement(sql):
    response = client.execute_statement(
        WorkgroupName=WORKGROUP_NAME,
        Database=DATABASE,
        SecretArn=SECRET_ARN,
        Sql=sql
    )
    statement_id = response['Id']
    # Wait for the statement to finish
    while True:
        desc = client.describe_statement(Id=statement_id)
        if desc['Status'] in ['FINISHED', 'FAILED', 'ABORTED']:
            break
        time.sleep(1)
    if desc['Status'] == 'FINISHED':
        if desc.get('HasResultSet', False):
            result = client.get_statement_result(Id=statement_id)
            return result['Records']
        else:
            return None
    else:
        print(f"Statement failed: {desc.get('Error', 'Unknown error')}")
        return None

def timed_query(sql):
    start = time.time()
    result = run_statement(sql)
    elapsed = time.time() - start
    return result, elapsed

Now, use these functions to benchmark your query before and after tuning:

if __name__ == "__main__":
    # Baseline query
    query_sql = f"""
    SELECT b.book_genre, AVG(f.fine_amount)
    FROM {FACT_BORROW} f
    JOIN {DIM_BOOK} b ON f.book_id = b.book_id
    GROUP BY 1;
    """
    print("Running baseline query...")
    _, baseline_time = timed_query(query_sql)
    print(f"Baseline query time: {baseline_time:.2f} seconds")

    # Refresh stats and vacuum
    print("Running ANALYZE and VACUUM...")
    run_statement(f"ANALYZE {FACT_BORROW};")
    run_statement(f"VACUUM SORT ONLY {FACT_BORROW};")

    # Re-run query
    print("Running query after ANALYZE/VACUUM...")
    _, tuned_time = timed_query(query_sql)
    print(f"Query time after tuning: {tuned_time:.2f} seconds")

    print(f"Performance improvement: {baseline_time:.2f} -> {tuned_time:.2f} seconds")

When you run this script, you will see output similar to the following:

Running baseline query...
Baseline query time: 4.12 seconds
Running ANALYZE and VACUUM...
Running query after ANALYZE/VACUUM...
Query time after tuning: 1.87 seconds
Performance improvement: 4.12 -> 1.87 seconds

This output shows the time it took to run the query before and after tuning. In this example, the query became more than twice as fast after running ANALYZE and VACUUM. Your results may vary depending on your data and table size, but you should see a noticeable improvement.

The script uses environment variables to keep your configuration flexible and secure. The run_statement function handles running SQL commands and waiting for them to finish, while timed_query measures how long each query takes. This approach makes it easy to benchmark your own queries and see the impact of tuning.

In this lesson, you learned how to use the ANALYZE and VACUUM commands to keep your Redshift tables optimized for fast queries. You saw how outdated statistics and fragmented storage can slow down your queries, and how these commands help Redshift make better decisions and access data more efficiently. By measuring query performance before and after tuning, you can see the real-world impact of these maintenance tasks.

As you move on to the practice exercises, you will get hands-on experience running these commands and benchmarking your own queries. This will help you build the skills you need to keep your analytics warehouse running smoothly and efficiently. Remember, regular tuning is a key part of any data engineering workflow, and now you have the tools to do it confidently.