Why can one 20 GB gzipped CSV file in S3 make a Redshift Spectrum query slow?

## How Spectrum gets its parallelism The Spectrum scan fleet is elastic, but it can only apply that elasticity if the data can be divided. Work is handed out as **splits** — independent ranges a reader can process without seeing the rest. Where those boundaries come from depends entirely on the file format and its compression. - **Parquet and ORC** are internally chunked into row groups and stripes, each self-describing with its own footer metadata. Any reader can jump to any row group. These formats split well by construction, even when their internal pages are compressed, because the compression is applied per column chunk rather than across the whole file. - **Plain text (CSV, JSON lines), uncompressed** can be split at record boundaries: a reader starts at a byte offset, skips to the next newline, and proceeds. - **bzip2-compressed text** is block-structured and remains splittable. - **gzip-compressed text** is a single continuous DEFLATE stream. There is no way to start decoding at byte 10,000,000 without having decoded everything before it. **One gzip file is one split, and therefore one reader.** ## What that does to a 20 GB gzipped CSV The query's entire S3 phase collapses onto a single worker. It must: 1. stream and decompress 20 GB serially, 2. parse every record, 3. decode **every column**, because CSV has no column-level structure, and 4. only then apply your filter and projection. Adding nodes to the Redshift cluster does not help — the bottleneck is a single-threaded decompression of one object. Nor does the filter help the scan cost: the bytes were read regardless. ## The mirror-image failure: too many small files The intuitive fix — "split it into lots of files" — overshoots easily. Every object carries fixed costs: a listing entry, a request round-trip, format metadata to parse, a reader assignment. When a partition contains hundreds of thousands of tiny objects, those fixed costs dominate and throughput collapses again, this time from overhead rather than serialisation. Streaming ingestion that writes one file per micro-batch produces exactly this shape, which is why lake pipelines almost always run a compaction step. ## Skew Even with a splittable format, badly *uneven* files hurt. If one partition holds a single 30 GB file and forty 50 MB files, the readers assigned to the small files finish immediately and the query waits on the long pole. The goal is not just "enough splits" but **evenly sized** splits, so all readers finish at roughly the same time — the same straggler logic that governs any parallel scan. ## What to aim for There is no single magic number, and it depends on partition size and query shape, but the shape of the target is consistent: - **Columnar and splittable**: Parquet or ORC, with internal compression such as Snappy or zstd. - **Substantially larger than trivial**: files in the tens-to-hundreds-of-megabytes range rather than kilobytes, and not so large that a single file becomes the straggler. - **Enough files per partition to occupy the readers** — file count comfortably above the number of parallel readers the query can use, which relates to the cluster's slice count. - **Evenly sized**, produced by a compaction job rather than by whatever the ingestion batch happened to emit. ## Diagnosing it `SVL_S3QUERY_SUMMARY` is the instrument. Its `files` and `splits` columns say how divisible the data actually was — `splits` barely exceeding `files` for a supposedly columnar table is a red flag. `avg_request_parallelism` shows how many readers were actually busy. `max_request_duration` beside a much smaller average is the straggler signature: one file, one reader, everyone else waiting. ```sql SELECT files, splits, avg_request_parallelism, max_request_duration, s3_scanned_bytes FROM svl_s3query_summary WHERE query = pg_last_query_id(); ``` If the numbers show one split for a large object, the fix is upstream in the pipeline that wrote it, not in the SQL.