skip to content

Why does a Redshift table loaded with COPY into an empty table come out compressed?

level: middleimportance: should knowfreq 55%

answer

  1. scan cost is really block count
  2. the first load decides the encodings
  3. only when the table is still empty
  4. AZ64 for numbers and dates
  5. one command recommends, another applies

basics

~10 s

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.

solid answer

~50 s

If you create a table without spelling out `ENCODE` per column, Redshift leaves the choice to itself. The first `COPY` into that empty table samples a portion of the incoming rows, analyses each column, chooses an encoding — `AZ64` for numeric and date/time types, `ZSTD` or another text-friendly encoding for strings — applies those encodings to the table definition, and then loads. The behaviour is governed by the `COMPUPDATE` option: default lets it happen, `COMPUPDATE OFF` disables it, `COMPUPDATE PRESET` assigns encodings from the column data types instead of sampling. It only fires when the table is genuinely empty and no column carries a chosen encoding — a `COPY` into a table that already holds rows will not re-encode anything. To revisit encodings later, run `ANALYZE COMPRESSION`, which recommends but does not apply, then change columns with `ALTER TABLE ... ALTER COLUMN ... ENCODE`.

code

sql · 21 lines
sql
-- No ENCODE clauses: the first COPY into the empty table picks them
CREATE TABLE sales (
  sale_id     BIGINT,
  sale_ts     TIMESTAMP,
  customer_id BIGINT,
  channel     VARCHAR(32),
  net_amount  DECIMAL(12,2)
);

COPY sales FROM 's3://landing/sales/2026-01-01/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoad'
FORMAT AS PARQUET;

-- Explicit control instead
CREATE TABLE sales_explicit (
  sale_id     BIGINT        ENCODE az64,
  sale_ts     TIMESTAMP     ENCODE az64,
  customer_id BIGINT        ENCODE az64,
  channel     VARCHAR(32)   ENCODE zstd,
  net_amount  DECIMAL(12,2) ENCODE az64
);

go deeper

for a junior

Know that Redshift compresses columns for you and that you normally do not write ENCODE clauses by hand. Recognise AZ64 and ZSTD as the common encodings.

for a middle

Explain the trigger conditions — empty table, no explicit encoding — the sampling step, the COMPUPDATE option, and which encodings suit which types. Know that ANALYZE COMPRESSION only recommends.

for a senior

Spot the failure where an unrepresentative first load fixed poor encodings for a table's lifetime, and know the routes out: ALTER COLUMN ENCODE for a column, a deep copy for a wholesale rebuild in a maintenance window.

for a principal

Decide the policy: leave encodings to Redshift's automatic optimization, or pin them in version-controlled DDL for reproducibility. Weigh predictable, reviewable schemas against the engine adapting as data shifts.

## Compression is not optional in a columnar warehouse Redshift scans by reading 1 MB column blocks off disk. The cheapest scan is the one that reads fewest blocks, so how tightly a column packs is a direct multiplier on every query that touches it. That is why Redshift takes the encoding decision out of your hands by default rather than shipping every column as raw bytes. ## Automatic compression during COPY The mechanism fires under specific conditions: the target table is **empty**, and its columns have **no explicit encoding** chosen. In that case `COPY` reads a sample of the incoming rows, evaluates candidate encodings per column against that sample, records the winners in the table definition, discards the sampled rows, and loads the data properly with those encodings in force. Two consequences trip people up: - **It happens once.** A later `COPY` into the now-populated table does not re-sample or re-encode. If your first load was unrepresentative — a tiny smoke-test batch, or one day whose values are far less varied than the year — the encodings chosen from it live on. - **Specifying one column's encoding can suppress the analysis.** If you hand-pick encodings, you have taken responsibility for all of them. The `COMPUPDATE` option controls the behaviour explicitly: on, off, or `PRESET`. `PRESET` skips sampling and assigns encodings purely from the declared data types — faster, and a reasonable choice when you know the data shape and do not want the sampling pass. ## The encodings that matter - **AZ64** is Amazon's proprietary encoding for numeric and date/time types — `SMALLINT`, `INTEGER`, `BIGINT`, `DECIMAL`, `DATE`, `TIMESTAMP`, `TIMESTAMPTZ`. It is the modern default recommendation for those types, generally beating the older byte-oriented encodings on both ratio and decompression speed. It does **not** apply to character types. - **ZSTD** works across all column types, including `VARCHAR`, and is the usual pick for text. - **LZO** is the older general-purpose encoding, largely superseded by ZSTD and AZ64. - **RUNLENGTH**, **BYTEDICT**, **DELTA**, **MOSTLY8/16/32** and **TEXT255/TEXT32K** are specialised encodings for low-cardinality, dictionary-friendly or narrow-range data. - **RAW** means no compression. It is the right answer for a column where compression buys nothing, and it is conventional guidance to leave the leading column of a compound sort key raw so that range-restricted scans on it stay predictable. A well-chosen encoding shrinks blocks; smaller blocks mean fewer I/Os per scan, more rows summarised per block of metadata, and more of the working set resident in memory. A poorly chosen one costs CPU on every read for little size benefit. ## Revisiting encodings on an existing table `ANALYZE COMPRESSION` inspects the data already in a table and reports the encoding it would recommend per column, along with an estimated size reduction. It is a **recommendation only** — it does not modify the table — and it takes an exclusive lock on the table while it runs, so it belongs in a maintenance window rather than in the middle of an ingest. ```sql ANALYZE COMPRESSION sales; ``` Acting on the recommendation is a separate step: ```sql ALTER TABLE sales ALTER COLUMN net_amount ENCODE az64; ``` For a wholesale re-encode of a big table, the traditional route is a deep copy: create a new table with the desired encodings, `INSERT INTO new SELECT * FROM old`, then swap names. That also rewrites the table in sort order as a side effect. Do not confuse `ANALYZE COMPRESSION` with plain `ANALYZE`. The latter refreshes the optimizer's statistics about the data — row counts, value distributions — and has nothing to do with storage encoding. Both are worth running after a large load, for different reasons. ## Automatic table optimization Modern Redshift will also manage encodings for you over time when a table's columns are left to it, adjusting them as it observes the workload. That does not remove the need to understand the choice: you still need to know why a column is raw, why a text column is not `AZ64`, and why the first load's sample matters. ## The interview answer in one line Redshift compresses because scan cost is block count; it chooses for you on the first load into an empty table by sampling; `AZ64` covers numbers and dates, `ZSTD` covers everything including text; and `ANALYZE COMPRESSION` recommends rather than applies.

  • Which column types can use AZ64, and what would you use for a VARCHAR?
    AZ64 covers numeric and date/time types — SMALLINT, INTEGER, BIGINT, DECIMAL, DATE, TIMESTAMP and TIMESTAMPTZ — and is the usual recommendation for them. It does not apply to character data. For VARCHAR, ZSTD is the general-purpose choice; low-cardinality strings may do better with a dictionary or run-length encoding.
  • Your first COPY into a new table was a 500-row smoke test. Why is that a problem?
    Automatic compression samples the rows of that first load, so encodings are chosen from an unrepresentative slice of the data and then stay put — later loads into the now-populated table do not re-analyse. Load a representative batch first, or run ANALYZE COMPRESSION once real data has accumulated and re-encode the columns whose recommendation changed.
  • What is the difference between ANALYZE and ANALYZE COMPRESSION?
    ANALYZE refreshes the optimizer's statistics — row counts and value distributions — so the planner picks good join and scan strategies. ANALYZE COMPRESSION inspects stored data and recommends a storage encoding per column; it changes nothing itself and holds an exclusive table lock while it runs. Different purposes, both worth doing after a large load.

saying these in an interview costs you the question

  • Thinking every COPY re-evaluates and rewrites column encodings
  • Claiming ANALYZE COMPRESSION applies the encodings it recommends
  • Using AZ64 for VARCHAR columns
  • Confusing ANALYZE COMPRESSION with statistics collection
  • Assuming compression only saves disk, not scan time

context

resolving links…