Amazon Redshift

DISTSTYLE decides how a Redshift table's rows are spread across compute slices. KEY hashes one column so equal values co-locate, ALL puts a full copy on every node, EVEN round-robins rows, and AUTO lets Redshift pick and change it.

COPY reads many S3 files at once across all node slices, applies compression, and commits once. Single-row INSERTs pay a commit each and leave 1 MB column blocks nearly empty, so the load crawls and storage balloons.

Redshift Spectrum runs ordinary SQL directly against files in S3 without loading them into the cluster. Before querying you create an external schema that points at a data catalog database and an IAM role, then define external tables over S3 prefixes.

A slice is a partition of a compute node's memory and disk that acts as an independent worker. Redshift spreads every table's rows across all slices and runs each plan segment once per slice, so total slices set the cluster's degree of parallelism.

DS_BCAST_INNER means Redshift is copying the entire inner table to every compute node before the join, because the two sides are not co-located. You remove it by giving both tables the same DISTKEY as the join column, or by making the small side DISTSTYLE ALL.

Bytes scanned depends on how much of S3 Spectrum must actually read: row-based formats force whole-file reads, unpruned partitions add prefixes, selecting unused columns adds column chunks, and weak compression inflates every byte. Columnar files, partition filters and narrow projections all cut the bill.

Manual WLM makes you define queues with a fixed concurrency level and a memory percentage each. Automatic WLM lets Redshift decide concurrency and per-query memory from the query's estimated needs, and you steer it only with query priority. AWS recommends automatic.

COPY the batch into a staging table, reduce it to one row per key, then apply it in a single transaction with MERGE, or with DELETE ... USING followed by INSERT. Never update the fact table row by row.

Split each query's elapsed time into wait and run. In Redshift, STL_WLM_QUERY gives total_queue_time and total_exec_time per query, and SYS_QUERY_HISTORY exposes queue and execution time directly. High wait means a WLM problem; high run time means a query or table problem.

RA3 nodes keep the authoritative copy of data in Redshift Managed Storage on S3 and use node-local SSD as a cache, so compute scales independently of data volume. DC2 stores data on node-local SSD only, so storage capacity dictates how many nodes you buy.

A Redshift compound sort key orders rows by its columns strictly left to right, so it only prunes well when the leading column is filtered. An interleaved sort key gives every listed column equal weight, helping filters on any subset, but it costs far more to maintain.

Redshift applies automatic compression: when COPY loads an empty table whose columns have no explicit encoding, it samples the incoming rows, picks a compression encoding per column, applies it, and then loads the data.

COPY divides the S3 file list across the cluster's slices, so each slice loads its own files straight from S3. Give it many similarly sized files — ideally a multiple of the slice count — rather than one huge file, which one slice must handle alone.

Each slice writes its own Parquet file directly to S3 under the prefix you give, so one UNLOAD produces many part files in parallel. PARALLEL OFF makes it write serially into a single file instead.

A federated query lets Redshift read live tables in RDS or Aurora PostgreSQL and MySQL directly, through an external schema created with FROM POSTGRES or FROM MYSQL. Use it for small, fresh operational lookups — never to scan a large transactional table.

A query gets one slot's share: the queue's memory percentage divided evenly by its concurrency level. If its hash tables or sorts exceed that, the step spills to disk instead of failing, and the query slows down sharply.

Elastic resize adds or removes nodes in minutes by remapping data across the new slice count, with a short pause in query processing. Classic resize provisions a new cluster and copies the data, taking far longer with the source read-only. Prefer elastic; use classic when elastic cannot make the change you need.

Check per-table row balance across slices with the skew_rows column of SVV_TABLE_INFO, and block counts per slice in STV_BLOCKLIST. A DISTKEY on a lumpy or NULL-heavy column sends most rows to one slice, so the whole query runs at that slice's pace.

New rows land in an unsorted region at the end of the table rather than in sort-key order, so their blocks hold wide min/max ranges and cannot be skipped. VACUUM merges that region into the sorted region and restores pruning; ANALYZE separately refreshes the statistics.

Spectrum's scan layer reads, projects, filters and often partially aggregates the S3 data, then streams the surviving rows to the cluster's compute nodes. The join itself always runs on the cluster, and because external tables have no distribution key those rows must be broadcast or redistributed first.

Compare total_partitions with qualified_partitions in SVL_S3PARTITION for the query. If all partitions qualify, the predicate is not usable for pruning — it names a data column, wraps the partition column in a function, mismatches its type, or arrives only via a join.

Concurrency scaling adds transient clusters when queries start queueing, routes eligible queued queries to them, and shuts them down after the burst. It needs concurrency scaling enabled on the WLM queue, actual queueing, and an eligible query — and it never speeds up a single running query.

Redshift Serverless bills compute in RPU-seconds and scales automatically with no cluster to size, which suits spiky or intermittent workloads. A provisioned RA3 cluster suits steady, predictable load where reserved pricing and direct control over cluster shape and workload management pay off.

A Redshift table has one DISTKEY, so a fact can co-locate only one join. Spend it on the join that moves the most bytes, replicate small dimensions with DISTSTYLE ALL so their joins are local anyway, and accept redistribution for the rest.

Drive it from query volume, not data volume. Data queried repeatedly by dashboards belongs in local Redshift storage where sort keys, distribution and materialized views apply; rarely-scanned history and data other engines must also read belongs in S3 behind Spectrum.

Route the two workloads to separate WLM queues by user group, run automatic WLM with BI at a higher priority than ETL, enable concurrency scaling on the BI queue and monitoring rules on the ad-hoc one. When contention persists, move BI to its own compute via data sharing.

AQUA, the Advanced Query Accelerator, is a hardware-accelerated caching and compute layer that sits between eligible RA3 compute nodes and managed storage. It pushes scan-time filtering and simple aggregation closer to the data, so fewer rows cross the network.

Gzip is not splittable, so the whole file must be decompressed sequentially by a single Spectrum reader. No matter how large the cluster, one worker does all the work, and because CSV is row-based every column is decoded too.

Two managed paths. Auto-copy attaches a COPY job to an S3 prefix so Redshift loads new files as they land, tracking what it already loaded. Streaming ingestion reads a Kinesis or MSK stream through an auto-refreshing materialized view.