Why is loading Redshift with COPY from S3 faster than many single-row INSERT statements?
answer
- the warehouse is not an OLTP database
- one commit per row is the cost
- blocks are 1 MB and immutable
- slices load S3 files in parallel
- bulk load once, never row by row
basics
~20 sCOPY 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.
solid answer
~50 sRedshift is a columnar MPP store whose unit of storage is a 1 MB block per column, per slice, and blocks are immutable. `COPY` hands the S3 file list to the compute nodes, every slice loads its share in parallel, values are encoded and packed into full blocks, and the whole load is one transaction with one commit. A loop of single-row `INSERT`s does the opposite: each statement is its own transaction, commits are serialized cluster-wide, and each row touches a block per column that stays almost empty — so a few hundred thousand rows can occupy gigabytes and take hours. The rule of thumb is: get the data into S3 (or another Redshift table) and load it in bulk. If the rows are already inside Redshift, use one set-based `INSERT INTO ... SELECT` rather than a loop.
code
sql · 10 lines-- Anti-pattern: one transaction and one block touch per row
INSERT INTO events (id, ts, payload) VALUES (1, '2026-01-01', 'a');
INSERT INTO events (id, ts, payload) VALUES (2, '2026-01-01', 'b');
-- ...repeated 50,000 times
-- Preferred: parallel, compressed, one commit
COPY events (id, ts, payload)
FROM 's3://analytics-landing/events/2026-01-01/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoad'
FORMAT AS PARQUET;go deeper
Know that COPY from S3 is the standard way to load Redshift and that looping single-row INSERTs is the textbook mistake. Be able to write a basic COPY with an IAM role and a format.
Explain the mechanics: 1 MB immutable column blocks per slice, parallel file loading across slices, one commit for the whole COPY versus one commit per INSERT, and why partially filled blocks inflate storage.
Be ready to diagnose it in the wild — disk growth outpacing row growth, load duration scaling with row count — and to redesign an event producer to buffer into S3 files instead of writing rows directly.
Own the ingestion contract between producers and the warehouse: batch size, file layout, delivery guarantees and who pays for the buffering. Argue why the warehouse should never be an application's direct write target.
## Why this is the first question asked about Redshift Row-at-a-time writing is the classic Amazon Redshift anti-pattern. It is the mistake teams make when they treat the warehouse like the OLTP database they came from, and the symptoms — a load that takes hours, a table far larger than the data it holds, queries that get slower after every ingest — are all traceable to the same physical facts. ## What Redshift storage actually looks like Redshift stores each column separately. The unit of storage is a **1 MB block**, and each block holds values from exactly one column, on exactly one slice. A slice is a unit of parallelism: every compute node is divided into slices, each owning a share of the table's rows and its own CPU and disk. Three consequences follow: - A table's minimum footprint is roughly one block per column per slice. A 12-column table on a cluster with dozens of slices occupies meaningful space even when almost empty. - Blocks are **immutable**. Redshift never edits a block in place; it writes new blocks and marks old rows as deleted. - Because a block is 1 MB and columnar, it is efficient only when it is full of values from a large batch. ## What COPY does ```sql COPY events (id, ts, payload) FROM 's3://analytics-landing/events/2026-01-01/' IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoad' FORMAT AS PARQUET; ``` The leader node parses the statement, lists the S3 objects under the prefix, and distributes that file list across the slices. Every slice pulls its own files straight from S3 — the data does not funnel through the leader node. Rows are routed to their owning slice according to the table's distribution style, values are compressed with each column's encoding, and full blocks are written. The whole thing is one transaction, so there is exactly one commit no matter how many billions of rows arrive. That parallelism is the entire point of the command. Throughput scales with the number of slices, which is why the same COPY runs faster on a bigger cluster. ## What a loop of INSERTs does Each `INSERT ... VALUES` with one row is its own transaction unless you wrap it. Redshift serializes commits cluster-wide, so thousands of tiny transactions queue behind one another; the commit, not the data volume, becomes the bottleneck. Meanwhile every insert appends to the current block for each column on the target slice, so blocks fill at the rate rows arrive rather than being packed by a bulk writer. Add per-statement network round trips from the client and the result is orders of magnitude slower than a bulk load of the same rows, with disk usage that bears no relationship to the logical data size. A further problem is that inserted rows land in the table's *unsorted region*. Redshift relies on sorted data and per-block min/max metadata to skip blocks at scan time; a table fed by dribbled inserts loses that property and needs background maintenance to recover it. ## What to do instead - **Data outside the cluster:** write it to S3 as a set of files and run one `COPY`. Parquet, Avro, ORC, JSON and delimited text are all supported. - **Data already inside the cluster:** use one set-based statement — `INSERT INTO target SELECT ... FROM staging` — which runs entirely on the compute nodes in parallel. This is a legitimate and fast pattern; the anti-pattern is the loop, not the `INSERT` keyword. - **Small ad-hoc batches:** a single multi-row `INSERT ... VALUES (...), (...), (...)` is far better than one statement per row, because it is one transaction and one pass. It is still not a substitute for `COPY` at scale. - **A stream of events:** buffer them into files in S3 (a few MB or more each) and load in batches, or use Redshift's managed continuous-ingestion paths, rather than writing every event as it arrives. ## How you notice it in production Disk usage that grows much faster than row count, a load job whose duration scales linearly with row count instead of with data volume, and queries that degrade after every ingest are the tells. `STL_LOAD_ERRORS` tells you why a COPY rejected rows; a load that is merely slow is usually a shape problem — too few files, or no COPY at all.
- Is INSERT ever acceptable in Redshift?Yes — as a set-based statement. `INSERT INTO target SELECT ... FROM staging` runs on the compute nodes in parallel and is the normal way to move data between Redshift tables. A single multi-row `INSERT ... VALUES` is also fine for small, occasional batches. What you must avoid is one statement per row, especially in a client-side loop, because the cost is per statement rather than per row.
- Why does a table loaded row by row take up so much more disk than the same data loaded with COPY?Redshift's storage unit is a 1 MB block holding one column's values on one slice, and blocks are immutable. A bulk load packs values into full, compressed blocks. Dribbled inserts leave many blocks partially filled, and a table's floor is already about one block per column per slice, so the physical footprint can be many times the logical data size.
- An application must record events as they happen. How do you get them into Redshift without row-by-row inserts?Buffer them outside the warehouse. Have the producer write batched files to S3 — a few megabytes each — and load them with COPY on a schedule, or use Redshift's managed continuous ingestion so the cluster pulls new files or stream records itself. Either way the write amplification is paid once per batch, not once per event.
Loading a warehouse row by row is like moving house one teaspoon at a time in a shipping container: each trip carries almost nothing, but you still pay for the whole container.
saying these in an interview costs you the question
- Claiming Redshift batches consecutive INSERT statements automatically
- Thinking INSERT is always wrong, including INSERT INTO ... SELECT
- Believing row-by-row loading only costs time, not disk space
- Assuming COPY streams data through the leader node
- Suggesting a bigger cluster fixes single-row insert throughput