Quick answer:
A columnar database stores each column’s values together instead of each row’s, so analytical queries read only the columns they need. Redshift writes columns into 1 MB blocks.
ENCODE AUTO manages Redshift’s column encodings by default, using AZ64 for supported numeric and date types, LZO for text, and RAW for sort keys and certain types. RAW means no compression. Redshift can revise encodings automatically; ANALYZE COMPRESSION recommends changes, and ALTER TABLE applies manual choices.
Amazon Redshift is a managed data warehouse provided by Amazon Web Services (AWS). One of its key features, and part of the secret behind its performance, is its columnar database structure.
What is a Columnar Database?
Many databases store their data by rows, which look similar to a spreadsheet. All the data about a record is stored in one row. By contrast, a columnar database stores its data by columns, with the data for each column stored together.
Take this data for example:
111, Bob Smith, 21 Main Street, 555-1212
112, Jane Doe, 14 First Avenue, 444-2222
In a SQL or standard flat file database the data would be stored like this:
| ID Number | Name | Address | Telephone |
|---|---|---|---|
| 111 | Bob Smith | 21 Main Street | 555-1212 |
| 112 | Jane Doe | 14 First Avenue | 444-2222 |
In a columnar database, the data storage would be like this:
| ID Number | 111, 112 |
|---|---|
| Name | Bob Smith, Jane Doe |
| Address | 21 Main Street, 14 First Avenue |
| Telephone | 555-1212, 444-2222 |
Columnar databases are typically used to store large amounts of data for analysis, such as in Business Intelligence systems. By storing all the records for one field together, columnar databases can query and perform analysis on that similar data far quicker than row-based databases.
As an example, if you want to know the mean order total for all of your customers, a columnar database would only need to look at the order total column to pull the data, and can quickly calculate the mean. Performing the same operation in a row-based database might require scanning millions or billions of rows to gather all the values.
Snowflake, BigQuery and ClickHouse use columnar storage, as do DuckDB and Parquet or ORC files on a data lake. Row stores suit transactional systems that read and write whole records, such as order-entry applications.
Amazon Redshift Columnar Storage
As a large scale, cloud-hosted data warehouse solution, Redshift is optimized for fast data analysis on vast amounts of data. Because Redshift is used for analytical queries, which are concerned with aggregates across many rows, a columnar structure performs much better:
- Using columnar storage, each data block can hold column field values for as many as three times the records as row-based storage. This reduces the number of I/O operations by 2/3. In tables with very large numbers of columns and a large number of rows, storage efficiency is even greater.
- An added advantage is that a columnar structure enables much better compression. In a database table, each column contains the same data type, typically with similar data. This creates an opportunity for much more efficient compression compared to a traditional database structure.
- The savings in storage space also carry over to retrieving and storing the data in memory. Many database operations only need to access or operate on a small number of columns at a time, and so you can save memory space by only retrieving the columns you actually need for your query.
For example, consider a table that contains 100 columns. A query that uses five columns will only need to read about 5% of the data. This saving is repeated for possibly billions or even trillions of records in large datasets. In contrast, a row-wise database would read the blocks that contain the 95 unneeded columns as well.
Blocks, Sort Keys and Skipped Reads
Redshift stores each column in 1 MB blocks, versus the 2 KB to 32 KB typical of other databases, and records each block’s minimum and maximum values in metadata. Range filters can skip blocks outside their bounds. In AWS’s example, sorting five years of data by date lets a one-month query skip up to 98% of blocks. Like the earlier I/O and column-count examples, this illustrates potential savings, not a guarantee.
New tables default to SORTKEY AUTO and DISTSTYLE AUTO, so Redshift picks the sort and distribution keys itself based on how your queries use the table. The Redshift architecture chapter explains how slices and nodes divide the blocks.
Redshift Spectrum queries files in Amazon S3 directly. AWS recommends columnar formats such as Parquet (ORC is columnar too), because a query that selects only the columns it needs then reads less data from S3.
Column Compression in Redshift
Redshift compresses data by default: new tables use ENCODE AUTO, which lets Redshift choose the encoding for every column and change it later if a different encoding would perform better. Each column within a table can use a different type of compression.
There are several ways to encode columnar data when compressing it; choosing the right type of encoding for each data type is key to achieving efficient compression. Redshift supports nine families of column encoding formats:
| Encoding type | Keyword | Data types |
|---|---|---|
| Raw (no compression) | RAW | All |
| AZ64 | AZ64 | SMALLINT, INTEGER, BIGINT, DECIMAL, DATE, TIMESTAMP, TIMESTAMPTZ |
| Byte dictionary | BYTEDICT | SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION, CHAR, VARCHAR, DATE, TIMESTAMP, TIMESTAMPTZ |
| Delta | DELTA / DELTA32K | SMALLINT, INT, BIGINT, DATE, TIMESTAMP, DECIMAL / INT, BIGINT, DATE, TIMESTAMP, DECIMAL |
| LZO | LZO | SMALLINT, INTEGER, BIGINT, DECIMAL, CHAR, VARCHAR, DATE, TIMESTAMP, TIMESTAMPTZ, SUPER |
| Mostlyn | MOSTLY8 / MOSTLY16 / MOSTLY32 | SMALLINT, INT, BIGINT, DECIMAL / INT, BIGINT, DECIMAL / BIGINT, DECIMAL |
| Run-length | RUNLENGTH | SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION, BOOLEAN, CHAR, VARCHAR, DATE, TIMESTAMP, TIMESTAMPTZ |
| Text | TEXT255 / TEXT32K | VARCHAR only |
| Zstandard | ZSTD | SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION, BOOLEAN, CHAR, VARCHAR, DATE, TIMESTAMP, TIMESTAMPTZ, SUPER |
It is possible to let Redshift select encoding for column compression automatically, or select it manually when creating or altering a table.
ENCODE AUTO: The Default
Without explicit encodings, Redshift starts with the assignments below and can change them later under ENCODE AUTO. Temporary-table columns start with RAW.
Columns defined as sort keys are assigned a RAW compression, which means that they are not compressed. Analysts should consider this when selecting a column as a sort key.
- Columns of a numerical type, like REAL and DOUBLE PRECISION, as well as BOOLEAN, GEOMETRY and GEOGRAPHY, are also assigned a RAW compression.
- Columns of type SMALLINT, INTEGER, BIGINT, DECIMAL, DATE, TIME, TIMETZ, TIMESTAMP and TIMESTAMPTZ are assigned AZ64, Amazon’s own encoding for numbers, dates and times.
- CHAR, VARCHAR and VARBYTE columns are assigned the LZO compression, which provides a tradeoff between performance and compression ratio.
Introduced in October 2019, AZ64 supplies the initial encoding for the types listed above, except sort keys. Specifying any column encoding turns ENCODE AUTO off for the whole table.
Automatic Compression with the COPY Command
COPY chooses encodings only for empty tables; appended rows use existing encodings. COMPUPDATE ON samples data and can replace encodings; PRESET chooses by data type without sampling. Omitting COMPUPDATE also avoids sampling and applies automatic compression only when the table is empty and no non-RAW encodings were specified. Sampled compression requires at least 100,000 rows per slice; COMPROWS defaults to that sample size, and AWS still recommends COPY for automatic compression.
To apply automatic compression with COPY:
- Ensure the table is empty, and run the following command:
truncate biglist; - Load the table with a single COPY command, set the COMPUPDATE parameter to ON to overwrite previous compression settings in the table. By default Redshift will select 100,000 rows per slice as its sample for analyzing the data for compression.
copy biglist from 's3://mybucket/biglist.txt'
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
delimiter '|' COMPUPDATE ON;
- Examine the new schema for the table to see the auto-selected encoding schemes:
select "column", type, encoding
from pg_table_def where tablename = 'biglist';
| Column | Type | Encoding |
|---|---|---|
| listid | integer | az64 |
| sellerid | integer | az64 |
| eventid | integer | az64 |
| dateid | smallint | none |
| numtickets | smallint | az64 |
| priceperticket | numeric(8,2) | az64 |
| totalprice | numeric(8,2) | az64 |
| listtime | timestamp without time zone | az64 |
The sort key dateid remains uncompressed, shown as none; every other column in this example gets AZ64.
- Finally, verify that the correct number of rows was loaded to the table:
select count(*) from biglist;
Selecting Compression Encoding Manually
Advanced users can select encoding types manually for certain columns. This should be done based on an analyst’s understanding of the data. For example, if the analyst knows that a certain column contains a limited number of “dictionary” terms, such as countries, the Byte Dictionary encoding type can be manually applied.
To manually apply compression encodings, specify them in the create table command like this:
create table customer(
custkey int encode delta,
custname varchar(30) encode raw, -- this text column uses raw encoding
gender varchar(7) encode text255, -- this text column uses text255 encoding
address varchar(200) encode text255,
city varchar(30) encode text255,
state char(2) encode raw,
zipcode char(5) encode bytedict,
start_date date encode delta32k); -- this date column uses delta32k encoding
The explicit column encodings disable ENCODE AUTO for customer, preventing Redshift from automatically revising these choices.
Checking and Changing Encodings Later
ANALYZE COMPRESSION recommends encodings and estimates size reductions compared with RAW, sampling 100,000 rows per slice by default. It does not change encodings. Its exclusive table lock prevents concurrent reads and writes, so schedule it outside busy hours.
Since October 2020, ALTER TABLE … ALTER COLUMN … ENCODE can change encodings while the table remains queryable, except on tables with interleaved sort keys. A manual encoding change disables ENCODE AUTO. ALTER TABLE … ALTER ENCODE AUTO restores automatic management, preserving current encodings until Redshift decides to change them.
analyze compression customer;
alter table customer alter column city encode zstd;
alter table customer alter encode auto;
This was only a brief introduction to Redshift’s compression and encoding options, and there are more details to be considered. We recommend you review the Column compression to reduce the size of stored data section in the Redshift documentation for more information.
Challenges with Redshift’s Columnar Structure
For each column, you’ll need to define one of the supported Redshift data types. Unlike other RDBMS systems, Redshift is used solely for data warehousing. You will not see LOB or LONG types in Redshift; large values go into SUPER (up to 16 MB per object) or VARBYTE (up to 16,777,216 bytes) instead.
You can design a table for data warehousing using data types like SMALLINT, INTEGER, and BIGINT to store whole numbers of various ranges. Use DECIMAL or NUMERIC to store values with user-defined precision. Similarly, you can use VARCHAR, DATE, TIMESTAMP for its respective data.
VARCHAR and CHAR lengths count bytes, not characters. VARCHAR supports up to 65,535 bytes and UTF-8 characters of up to 4 bytes each; CHAR accepts only single-byte characters, up to 4,096 bytes. A VARCHAR(30) column may hold fewer than 30 characters of non-Latin text, so size it by byte length.
The challenge lies in data integrity. You need to be careful about how the conversion and compatibility of number data type works while manipulating or querying data. Make sure you select only the data types the table needs, rather than choosing data types at random. Careful selection of data type reduces the size of the rows, and the database, making reads and writes faster and more efficient.
Implicit conversion can change values: inserting a decimal into an integer column rounds it, while exceeding a numeric column’s range raises an overflow error. Queries using unsupported PostgreSQL types, such as JSON, UUID or arrays, generally return an error, including through explicit or implicit casts.
Do not select data types nor design the ETL in a hurry as it can cause errors, or worse, data corruption. Knowing how each data type behaves and the events that occur when the checks are performed will be of help when working on table design. Compatibility checks and conversion occur during various database operations, and if the wrong data type is defined or data is not converted properly, data integrity can be compromised.
If an ETL tool or a dbt model creates your tables, inspect its generated types and encodings before the first large load. Those types affect ANALYZE COMPRESSION recommendations, and explicit encodings can disable ENCODE AUTO.
Common questions
Is Amazon Redshift a columnar database?
Yes. Amazon Redshift stores each column separately in 1 MB blocks and manages its encoding independently. It also keeps minimum and maximum values for each block, allowing queries with suitable filters to skip blocks. Analytical queries can read only the columns they need instead of whole rows. This layout suits aggregates across large datasets.
What compression encoding does Redshift use by default?
ENCODE AUTO is the default. Initial encodings for non-temporary tables are AZ64 for SMALLINT, INTEGER, BIGINT, DECIMAL, DATE, TIME, TIMETZ, TIMESTAMP and TIMESTAMPTZ; LZO for CHAR, VARCHAR and VARBYTE; RAW for sort keys, BOOLEAN, REAL, DOUBLE PRECISION, GEOMETRY and GEOGRAPHY. RAW means no compression. Redshift can change these assignments later.
What is AZ64 encoding?
AZ64 is Amazon’s compression encoding for supported numeric, date and time types, introduced in October 2019. It initially applies to SMALLINT, INTEGER, BIGINT, DECIMAL, DATE, TIME, TIMETZ, TIMESTAMP and TIMESTAMPTZ unless the column is a sort key. REAL and DOUBLE PRECISION start with RAW instead, and ENCODE AUTO can revise assignments.
How do I change a column’s encoding in Redshift?
Use ALTER TABLE … ALTER COLUMN … ENCODE to change an encoding while the table remains queryable; this disables ENCODE AUTO and excludes tables with interleaved sort keys. ANALYZE COMPRESSION provides recommendations but takes an exclusive table lock. ALTER TABLE … ALTER ENCODE AUTO restores automatic management, initially preserving current encodings.
Where to next
Read Redshift Architecture for how nodes and slices hold these blocks and Redshift Cluster 101 to set up a cluster. Compare designs in BigQuery architecture and Redshift vs. BigQuery. For the basics, see Data Warehouse Concepts and the difference between a database and a data warehouse.
Snowflake, BigQuery, Redshift, Databricks, Fivetran, Airbyte and the rest, with pricing and what each one replaces.
Browse the tools