Amazon Redshift Cost Tuning: Getting More from Every Dollar

Amazon Redshift is the most powerful and most expensive service in many AWS data analytics stacks. A four-node ra3.4xlarge cluster runs approximately $4,800 USD per month on-demand — $57,000 per year before any Reserved Instance commitment. For organisations that have not tuned their Redshift deployment, that figure often represents 30-50% more spend than necessary for the analytical workload being served.

Redshift cost tuning is a discipline that combines cluster configuration, query engineering, workload management, and commercial decisions around pricing models. This post covers each layer with specific recommendations and, where relevant, the quantitative basis for those recommendations.

Understanding Redshift’s Cost Components

Before tuning, it is worth decomposing the Redshift bill into its constituent parts:

Compute nodes: The dominant cost. For ra3 nodes (the current generation), compute is billed separately from storage. ra3.xlplus nodes cost $0.38 USD/hour each; ra3.4xlarge is $1.086/hour; ra3.16xlarge is $4.343/hour.

Redshift Managed Storage (RMS): For RA3 nodes, storage is billed at $0.024/GB-month for hot storage (local SSD cache) and $0.023/GB-month for cold storage (S3-backed). Most data beyond your working set sits in cold storage automatically.

Redshift Serverless RPUs: Serverless charges $0.375 per RPU-hour (Redshift Processing Unit) consumed, with no charge during idle periods. The minimum allocation is 8 RPUs; maximum is 512 RPUs.

Concurrency Scaling: When enabled, concurrency scaling clusters are billed at on-demand node pricing for their usage. Each cluster accumulates one hour of free scaling credit per 24 hours of main cluster usage — so light use of concurrency scaling is effectively free.

Spectrum charges: Athena-style per-query charges of $5/TB scanned when querying S3 data via Redshift Spectrum. This is a separate cost from compute node pricing.

Provisioned vs. Serverless: The Economics

The decision between provisioned Redshift and Redshift Serverless is primarily an economics question — both provide the same SQL compatibility and query performance characteristics.

Serverless is cheaper when: your workload has significant idle time (less than 60-70% of hours with active query load). The breakeven point varies by RPU configuration, but a 128-RPU serverless deployment costs approximately $1.20/hour when active and $0 when idle. A comparable provisioned cluster runs continuously.

Run this analysis using your actual Redshift query activity data:

-- Query Redshift query history to calculate active-hour percentage
SELECT
    DATE_TRUNC('hour', starttime) AS query_hour,
    COUNT(*) AS queries_in_hour,
    SUM(elapsed) / 1000000.0 AS total_query_seconds
FROM stl_query
WHERE starttime >= DATEADD(day, -30, GETDATE())
  AND userid > 1  -- exclude internal queries
GROUP BY 1
ORDER BY 1;

Count the number of hours with at least one query. If this is below 70% of total hours, Serverless pricing is likely more cost-effective. If your workload runs continuously with thousands of queries per hour, provisioned Reserved Instances will be cheaper.

Provisioned with Reserved Instances is cheaper when: you have consistent, high utilisation. A 3-year Reserved Instance commitment for ra3.4xlarge nodes reduces the effective hourly rate from $1.086 to approximately $0.458 — a 58% reduction. One-year commitments deliver approximately 42% savings over on-demand.

The decision framework:

Utilisation Pattern	Recommended Pricing Model	Estimated Savings vs. On-Demand
>70% hours active, consistent load	Provisioned + 3yr Reserved	50-60%
>70% hours active, variable load	Provisioned + 1yr Reserved	35-45%
40-70% hours active	Serverless	20-40%
<40% hours active	Serverless	40-65%

Distribution Style and Sort Key Optimisation

Query performance directly affects cost when you are on Serverless (slower queries consume more RPU-hours) or when slow queries drive Concurrency Scaling usage. Distribution style is the single most impactful Redshift performance tuning lever.

The optimal distribution style for each table type:

-- Fact tables: distribute on the most common join key
CREATE TABLE analytics.fact_sales (
    sale_id         BIGINT NOT NULL,
    customer_key    INT NOT NULL,
    product_key     INT NOT NULL,
    date_key        INT NOT NULL,
    gross_revenue   DECIMAL(12, 2),
    quantity        INT
)
DISTKEY (customer_key)     -- matches the most frequent dimension join
SORTKEY (date_key);        -- matches the most frequent filter/ORDER BY

-- Large dimension tables: distribute on their primary key
CREATE TABLE analytics.dim_customer (
    customer_key    INT NOT NULL,
    customer_id     VARCHAR(50),
    country_code    CHAR(2),
    ...
)
DISTKEY (customer_key);    -- co-locates with fact_sales joins

-- Small dimension tables: replicate to all nodes (eliminates network shuffle)
CREATE TABLE analytics.dim_date (
    date_key        INT NOT NULL,
    full_date       DATE,
    fiscal_quarter  SMALLINT,
    ...
)
DISTSTYLE ALL;             -- replicate entire table to every node

The ANALYZE COMPRESSION command recommends column encoding for existing tables. Running it on new tables before loading data saves storage costs (typically 30-70% compression vs. raw) and improves scan performance.

-- Get compression recommendations before loading a large table
ANALYZE COMPRESSION analytics.fact_sales;

Workload Management (WLM) Configuration

Redshift Workload Management controls how queries are queued and executed across concurrent users. Poor WLM configuration is a common source of both performance problems and unexpected Concurrency Scaling charges.

For mixed workloads (a typical pattern with scheduled ETL jobs plus interactive analyst queries), configure at least two WLM queues:

Queue 1 — ETL/batch (high memory, limited slots)
  - Memory: 60%
  - Concurrency: 3
  - Query group: etl_jobs, glue_jdbc
  - Timeout: 7200 seconds (2 hours)

Queue 2 — Interactive analytics (lower memory, more slots)
  - Memory: 35%
  - Concurrency: 10
  - Query group: bi_users, analysts
  - Timeout: 300 seconds (5 minutes)

Superuser queue: 5% (reserved)

This configuration prevents long-running ETL jobs from consuming all query slots and starving interactive analysts, and prevents analysts from running accidental full-table scans that blow out the ETL memory budget.

Enable automatic WLM for the interactive queue if your concurrency needs are variable — automatic WLM dynamically adjusts memory per query based on available resources, which typically delivers better throughput than fixed slot counts.

Query Optimisation for Cost Reduction

In Redshift Serverless, every query has a direct cost: RPU-hours consumed. Queries that run in 30 seconds instead of 5 minutes cost 10x less. The three most impactful query optimisation techniques:

Eliminate full table scans with sort key alignment:

-- Expensive: no sort key filter, full table scan
SELECT SUM(gross_revenue) FROM fact_sales WHERE product_key = 42;

-- Cheap: sort key in WHERE clause, zone map prunes most blocks
SELECT SUM(gross_revenue) FROM fact_sales
WHERE date_key BETWEEN 20240101 AND 20240131
  AND product_key = 42;

Redshift zone maps store the min/max value for each 1 MB block of each column. When your WHERE clause uses the sort key, Redshift can skip blocks where the min/max range does not overlap with the filter — turning a full scan into a targeted read of a small fraction of the table.

Use COPY instead of INSERT for bulk loads:

-- Expensive: row-by-row inserts
INSERT INTO staging.raw_events VALUES (...);  -- 10,000 times

-- Cheap: parallel bulk load via COPY
COPY staging.raw_events
FROM 's3://data/raw/events/2024/01/26/'
IAM_ROLE 'arn:aws:iam::123456789:role/RedshiftS3Access'
FORMAT AS PARQUET;

COPY parallelises across all compute nodes and loads multiple files simultaneously. It is 10-100x faster than equivalent INSERT statements for large datasets and does not consume query slots in the same way.

Use materialised views for frequently computed aggregates:

CREATE MATERIALIZED VIEW analytics.mv_daily_sales_by_product AS
SELECT
    date_key,
    product_key,
    SUM(gross_revenue) AS total_revenue,
    COUNT(*) AS transaction_count
FROM analytics.fact_sales
GROUP BY date_key, product_key;

-- Auto-refresh materialised view after ETL loads
REFRESH MATERIALIZED VIEW analytics.mv_daily_sales_by_product;

Dashboard queries that previously scanned the full fact table now hit the materialised view, which is orders of magnitude smaller. QuickSight SPICE works particularly well with Redshift materialised views, reducing both query latency and RPU consumption.

Spectrum Cost Management

Redshift Spectrum charges $5/TB of S3 data scanned. For queries that regularly scan large datasets in your S3 data lake, this can become a significant line item. Key controls:

Always partition Spectrum-queried tables by the columns used in WHERE clauses. A query against a year of event data with no partition filter scans 365x more data (and costs 365x more) than a query with a date partition filter.

Convert JSON and CSV data to Parquet before querying via Spectrum. JSON compressed with GZIP typically achieves 3-5x compression vs. raw. Parquet adds columnar storage on top of compression, meaning a query that selects 10 of 100 columns scans only 10% of the data a row-format equivalent would scan. For Spectrum queries, this translates directly to an 80-90% cost reduction.

Monitor Spectrum scan volume using svl_s3query:

SELECT
    userid,
    query,
    s3_scanned_rows,
    s3_scanned_bytes / (1024.0 * 1024 * 1024) AS gb_scanned,
    s3_scanned_bytes / (1024.0 * 1024 * 1024) * 5.00 AS estimated_cost_usd
FROM svl_s3query
WHERE starttime >= DATEADD(day, -7, GETDATE())
ORDER BY s3_scanned_bytes DESC
LIMIT 20;

This query identifies your most expensive Spectrum queries — the candidates for partitioning improvements or format conversion.

Reserved Instance Purchase Strategy

For provisioned Redshift clusters that have been running consistently for 3+ months, Reserved Instance pricing is almost always the right commercial decision. The strategy:

1. Run on-demand for 60-90 days after initial deployment to establish a stable workload pattern and verify your node type is correct
2. Purchase 1-year RIs first — the risk is low (12-month commitment) and savings are substantial (35-45%)
3. Evaluate 3-year RIs after 12 months when you have confidence in the platform’s long-term role in your architecture. The additional savings (55-60% vs. on-demand) justify the commitment for stable platforms

For guidance on the broader cost optimisation across your full AWS data stack, AWS Cost Optimisation for Data Teams covers tactics across Glue, Athena, S3, and Lambda in addition to Redshift.

Making Redshift Pay Its Way

Amazon Redshift at its best is a remarkably capable analytics engine that justifies its cost through the query performance, BI integration, and data sharing capabilities it provides. At its worst, it is an expensive cluster running at 20% utilisation because nobody has reviewed the configuration since it was deployed.

The tactics in this post represent the highest-ROI Redshift optimisation work most organisations can do. Right-sizing the node type and count, moving variable workloads to Serverless, configuring WLM to protect interactive users, and optimising the top 10 most expensive queries typically reduces total Redshift spend by 30-50% with a one-sprint engineering investment.

Infra IT Consulting has tuned Redshift deployments for clients across financial services, retail, and technology sectors. Contact us to discuss a Redshift cost and performance assessment for your environment.