Amazon Redshift: Cloud Data Warehousing and Analytics

You've got 50TB of log data and your queries are timing out. Your team spent three days tuning indexes on PostgreSQL, and it still can't handle the load. That's when you realize: transactional databases are not data warehouses. Amazon Redshift exists to solve exactly this problem—analytics at scale. It's not a toy; it's a production workhorse that processes exabytes of data for companies like Netflix and Lyft. But it's also a sharp knife: misconfigure it, and you'll bleed money on concurrency scaling or watch your vacuum operations lock up your cluster. This guide cuts through the marketing fluff to show you how Redshift actually works under the hood, where it fails, and how to keep it running in production without burning your budget.

Why Redshift? The Case for Columnar Storage at Scale

When you're processing terabytes to petabytes of structured data, row-based databases like PostgreSQL or MySQL choke on analytical queries. Redshift's columnar storage compresses similar data types together, reducing I/O and enabling vectorized execution. This isn't theory—it's why Redshift can scan billions of rows in seconds. But columnar storage comes with trade-offs: single-row inserts are slow, and UPDATE/DELETE operations are expensive. You must design for bulk loads and immutable fact tables. If you're coming from OLTP, unlearn row-level thinking. Redshift is for analytics, not transactions.

-- Create a columnar-optimized sales table
CREATE TABLE sales (
  sale_id BIGINT IDENTITY(1,1),
  product_id INT NOT NULL,
  customer_id INT NOT NULL,
  sale_date DATE NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL
)
DISTKEY(customer_id)
SORTKEY(sale_date);

-- Insert sample data (bulk load preferred)
INSERT INTO sales (product_id, customer_id, sale_date, quantity, unit_price, total_amount)
VALUES
  (101, 1001, '2024-01-15', 2, 19.99, 39.98),
  (102, 1002, '2024-01-15', 1, 49.99, 49.99);

Distribution Styles: The Key to Parallel Execution

Redshift distributes data across compute nodes. Choosing the right distribution style determines whether your joins are local (fast) or broadcast (slow). EVEN distribution spreads rows round-robin—good for staging tables. KEY distribution co-locates rows with the same distribution key on the same node, enabling collocated joins. ALL distribution copies the entire table to every node—use only for small dimension tables (<1M rows). The golden rule: distribute fact tables on their join key with large dimension tables. Avoid distribution keys that cause skew (e.g., a column with 90% NULLs). Monitor with STV_SLICES and SVV_DISKUSAGE.

-- EVEN distribution: good for staging or when no join key exists
CREATE TABLE staging_events (
  event_id BIGINT IDENTITY(1,1),
  event_time TIMESTAMP,
  payload VARCHAR(5000)
)
DISTSTYLE EVEN;

-- KEY distribution: co-locate on customer_id for joins
CREATE TABLE orders (
  order_id BIGINT IDENTITY(1,1),
  customer_id INT NOT NULL,
  order_date DATE,
  amount DECIMAL(12,2)
)
DISTKEY(customer_id);

-- ALL distribution: small dimension table
CREATE TABLE customers (
  customer_id INT NOT NULL,
  name VARCHAR(100),
  email VARCHAR(100)
)
DISTSTYLE ALL;

Sort Keys: Optimizing Range-Restricted Queries

Sort keys define the physical order of data on disk. A compound sort key is most effective for queries with equality and range filters on the leading columns. An interleaved sort key gives equal weight to all columns but increases maintenance overhead. Use compound sort keys for time-series data (e.g., date as first column). Queries with WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31' will skip large portions of data. Monitor sort key effectiveness with SVV_TABLE_INFO. If your sort key has low cardinality, consider zone maps instead.

-- Compound sort key: date first, then product_id
CREATE TABLE sales_compound (
  sale_id BIGINT IDENTITY(1,1),
  sale_date DATE NOT NULL,
  product_id INT NOT NULL,
  quantity INT,
  amount DECIMAL(12,2)
)
DISTKEY(product_id)
COMPOUND SORTKEY(sale_date, product_id);

-- Query that benefits from the sort key
SELECT product_id, SUM(amount)
FROM sales_compound
WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31'
  AND product_id IN (101, 102)
GROUP BY product_id;

Data Loading Best Practices: COPY vs INSERT

Never use INSERT for bulk data loading. Each INSERT generates a separate transaction and creates many small 1MB blocks, leading to table bloat and poor compression. Always use the COPY command from Amazon S3. COPY loads data in parallel across nodes, automatically compresses, and can transform data on the fly. Use manifest files for multi-file loads. Set COMPUPDATE ON to let Redshift choose optimal compression encodings. For streaming data, stage files in S3 every 5-15 minutes and load with COPY. Avoid single-row INSERTs at all costs.

-- Create table with no compression initially
CREATE TABLE sales_raw (
  sale_id INT,
  product_id INT,
  customer_id INT,
  sale_date DATE,
  quantity INT,
  unit_price DECIMAL(10,2),
  total_amount DECIMAL(12,2)
);

-- COPY from S3 with automatic compression
COPY sales_raw
FROM 's3://my-bucket/sales/'
IAM_ROLE 'arn:aws:iam::123456789012:role/MyRedshiftRole'
CSV
IGNOREHEADER 1
DATEFORMAT 'auto'
COMPUPDATE ON;

-- Verify row count
SELECT COUNT(*) FROM sales_raw;

Vacuum and Analyze: Keeping Performance Consistent

Over time, UPDATE and DELETE operations create dead rows and unsorted regions. VACUUM reclaims space and re-sorts data. ANALYZE updates table statistics for the query planner. Without regular maintenance, query performance degrades. Run VACUUM after large deletes or updates. Use VACUUM SORT ONLY if you only need to re-sort. For tables with frequent updates, consider a deep copy instead: CREATE TABLE new AS SELECT * FROM old; DROP old; ALTER new RENAME to old. This avoids VACUUM overhead entirely.

-- Check table stats before maintenance
SELECT "table", size, tbl_rows, unsorted, stats_off
FROM svv_table_info
WHERE "table" = 'sales';

-- Run VACUUM (full)
VACUUM sales;

-- Run ANALYZE
ANALYZE sales;

-- Deep copy alternative
BEGIN;
CREATE TABLE sales_new (LIKE sales);
INSERT INTO sales_new SELECT * FROM sales;
DROP TABLE sales;
ALTER TABLE sales_new RENAME TO sales;
COMMIT;

Workload Management (WLM): Concurrency and Prioritization

Redshift's WLM allows you to define queues with different concurrency levels and memory allocations. By default, there's one queue with concurrency 5. For mixed workloads, create separate queues for ETL and reporting. Assign users or groups to queues. Use query monitoring rules to abort runaway queries. Avoid setting concurrency too high—each slot gets a fraction of memory, and queries spill to disk. Monitor with STV_WLM_SERVICE_STATE. For predictable performance, consider using Redshift's auto WLM, which dynamically manages concurrency.

-- Create a custom WLM queue (via AWS Console or CLI)
-- Example: Two queues
-- Queue 1: ETL (concurrency 3, memory 50%)
-- Queue 2: Reporting (concurrency 5, memory 50%)

-- Assign user to queue
ALTER USER etl_user SET QUEUE 'etl_queue';

-- Query monitoring rule: abort queries running > 1 hour
CREATE RULE abort_long_queries
  WHERE elapsed_time > 3600000000
  ACTION ABORT;

Concurrency Scaling: Handling Burst Traffic

When your concurrency exceeds the main cluster's capacity, queries queue up. Concurrency Scaling automatically spins up additional clusters to handle read-only queries. You pay per second for the scaled-out capacity. Enable it for critical dashboards. Queries are routed to the scaling cluster only if the main cluster is saturated. Monitor with STV_WLM_QUERY_QUEUE_STATE. Set a limit on the number of scaling clusters to control costs. Concurrency Scaling is ideal for unpredictable spikes, not sustained high load.

-- Check if a query ran on the main or scaling cluster
SELECT query, service_class, query_type, elapsed_time
FROM stl_wlm_query
WHERE query = 123456;

-- service_class = 6 means main cluster, 7 means scaling cluster

-- Enable Concurrency Scaling (via AWS Console)
-- Set max scaling clusters to 2

Redshift Spectrum: Querying Data Directly in S3

Redshift Spectrum allows you to query data stored in S3 without loading it into Redshift tables. Use it for infrequently accessed data or data lakes. Spectrum leverages thousands of nodes to scan data in parallel. Define external tables using the AWS Glue Data Catalog. Spectrum supports Parquet, ORC, Avro, JSON, CSV, and more. Partition your data by date or region to limit scans. Spectrum queries are slower than local tables but cheaper for cold data. Combine with local tables using joins.

-- Create external schema pointing to Glue catalog
CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 'spectrum_db'
IAM_ROLE 'arn:aws:iam::123456789012:role/MySpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

-- Create external table partitioned by year/month
CREATE EXTERNAL TABLE spectrum_schema.sales_external (
  sale_id INT,
  product_id INT,
  sale_date DATE,
  amount DECIMAL(12,2)
)
PARTITIONED BY (year INT, month INT)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS PARQUET
LOCATION 's3://my-bucket/sales_parquet/';

-- Query external data
SELECT year, month, SUM(amount)
FROM spectrum_schema.sales_external
WHERE year = 2024
GROUP BY year, month;

Resizing and Elastic Resize: Scaling Your Cluster

As data grows, you may need to resize your cluster. Classic resize creates a new cluster and copies data, taking hours. Elastic resize adds or removes nodes in minutes but requires the cluster to be on the latest maintenance track. Elastic resize is ideal for temporary capacity changes (e.g., end-of-month processing). Classic resize is for permanent changes. Both require read-only mode during the operation. Plan resizes during maintenance windows. Monitor progress with SVV_RESIZE_STATUS.

-- Check resize status
SELECT * FROM svv_resize_status;

-- Example output
-- resize_type | status    | progress
-- -------------+-----------+----------
-- elastic     | COMPLETED | 100

Monitoring and Troubleshooting: Essential System Views

Redshift provides system views for deep performance analysis. STL_QUERY shows query execution details. STV_TBL_PERM shows table storage. SVL_QUERY_SUMMARY breaks down query steps. Use these to identify slow queries, disk spills, and lock contention. Common issues: missing statistics (stats_off > 20), disk spills (rows_pre_filter > rows), and table scans (scan rows > needed). Set up automated monitoring with CloudWatch and SNS alerts. Create a dashboard for key metrics: query queue time, disk usage, and error rates.

-- Top 10 longest running queries
SELECT query, elapsed, substring(querytxt,1,50) as query_text
FROM stl_query
WHERE elapsed > 0
ORDER BY elapsed DESC
LIMIT 10;

-- Queries that spilled to disk
SELECT query, step, rows, workmem, is_diskbased
FROM svl_query_summary
WHERE is_diskbased = 't'
LIMIT 10;

-- Table statistics age
SELECT "table", stats_off
FROM svv_table_info
WHERE stats_off > 20;

Security: Encryption, IAM, and VPC

Redshift supports encryption at rest (KMS or CloudHSM) and in transit (SSL). Use IAM roles for COPY/UNLOAD instead of hardcoding keys. Restrict network access with VPC security groups. Enable audit logging to S3 for all queries. Use column-level access control with views or Redshift's built-in column-level security (available in newer versions). Rotate IAM keys regularly. For multi-tenant environments, use row-level security with views filtering by tenant_id. Never use the master user for daily operations—create individual users with least privilege.

-- Create a read-only user
CREATE USER analyst WITH PASSWORD 'StrongPassword123!';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

-- Column-level security: create a view that hides sensitive columns
CREATE VIEW sales_public AS
SELECT sale_id, product_id, sale_date, quantity, total_amount
FROM sales;

-- Grant access to view
GRANT SELECT ON sales_public TO analyst;

-- Enable audit logging (via AWS Console)
-- Configure to log all queries to S3 bucket

Cost Optimization: Right-Sizing and Reserved Instances

Redshift costs come from compute nodes, storage, and data transfer. Right-size your cluster: use dc2 for compute-heavy, ds2 for storage-heavy. Reserved instances offer up to 75% discount for 1-3 year terms. Use concurrency scaling sparingly. Compress data with appropriate encodings (e.g., BYTEDICT for low-cardinality columns). Use Spectrum for cold data. Delete old data with time-based partitioning. Monitor with AWS Cost Explorer. Consider using RA3 nodes with managed storage for elastic compute.

-- Check table storage size
SELECT "table", size, tbl_rows, estimated_visible_rows
FROM svv_table_info
ORDER BY size DESC;

-- Identify tables with poor compression
SELECT "table", size, tbl_rows, ratio
FROM svv_table_info
WHERE ratio < 5
ORDER BY ratio ASC;