The short answer
Loading data with regular SQL INSERT INTO table VALUES (...) statements is a common mistake made by developers coming from transactional databases. In Redshift, single-row INSERT statements route through the leader node as discrete transactions, writing tiny uncompressed disk blocks and causing heavy lock contention.
The standard way to load data into Redshift is the COPY command. COPY connects the cluster directly to Amazon S3, allowing all compute slices to stream data files in parallel.
COPY sales_events
FROM 's3://my-lake-bucket/curated/sales/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3ReadOnly'
FORMAT AS PARQUET;Best practices for COPY performance
- Split data into multiple files: If your cluster has 16 slices, split your dataset into a multiple of 16 files (for example, 16, 32, or 64 files) of roughly equal size. If you supply a single giant file, only one slice reads it while the remaining 15 slices sit idle.
- Use compressed columnar formats: Reading Parquet or ORC files is much faster than parsing uncompressed CSV or text files. Parquet stores columnar metadata, allowing COPY to read columns selectively and skip string parsing overhead.
- Provide an role: Authenticate using an IAM role attached to the Redshift cluster rather than hardcoding AWS access keys in your SQL text.
- Use a manifest file when needed: When files in an S3 prefix change continuously, supply a manifest JSON file listing the exact object URIs to load, preventing partial reads or duplicate ingestion.