The R.A.G (Redshift Analyst Guide): Data Types and Compression

Alex Antra - Nov 20 '19 - - Dev Community

Welcome to the R.A.G, a guide about Amazon's Redshift Database written for the Analyst's out there in the world who use it.


Previously on the R.A.G....


When building tables you determine what 'data type' the column(s) will be. You do this column by column and while it may seem silly to specify exactly what is going to go into each column, especially when Redshift can guess for you, it can be a big contributing factor in terms of speeding up performance and decreasing table size.

You can also choose to compress your columns.
Compression will allow for more data to fit inside a block, again decreasing table size. It will also improve the efficiencies of the Zone Maps, another thing that can speed up performance.

smaller

Data Types

Data Keywords Description
SMALLINT ,INT2 This is for WHOLE numbers that only take up 2 bytes of data, range: -32768 to +32767
INTEGER, INT, INT4 Also for whole numbers that only take up 4 bytes of date, range: -2147483648 to +2147483647
BIGINT, INT8 Also for whole numbers that only take up 8 bytes of date, range: -9223372036854775808 to 9223372036854775807
DECIMAL, NUMERIC For numbers with decimal points, up to 38 digits total. When you classify a column as decimal you must declare both the TOTAL length and then how many decimals. For example decimal(10,2) means ten numbers max with two decimal places, this equates to 8 digits on the left of the decimal, and 2 on the right.
REAL, FLOAT4 For storing smaller, rounded down, floating point numbers
DOUBLE PRECISION, FLOAT , FLOAT8 For storing larger, non rounded, floating point numbers
BOOLEAN, BOOL Boolean is a single byte flag which is either 1 or 0, true or false. Though it can hold a null value. It can also get represented as a checkbox. You can specify that the default value is true or false, if you don't specify a default value then the default value will be null
CHAR, CHARACTER, NCHAR , BPCHAR CHAR is a fixed length text string, ignore references to NCHAR and BPCHAR those are old functionality merged all into one. CHAR always takes up all of the space you specify, so if you specify char(100) but only put 'Hi' into the column, the remain 98 characters of space will be filled with spaces. Which can cause issues with EXACT object matching. Use when your column is always going to be a fixed length. CHAR will always use up at least 4 bytes of data, even if you specify CHAR(2)
VARCHAR, CHARACTER VARYING, NVARCHAR, TEXT VARCHAR allows for varying character length which is good for free text fields. Unlike CHAR it will only use however much space has been entered. So a in a VARCHAR(100) the word 'Christmas' will only use 9 of that 100, saving space. VARCHAR will always use up at least 6 bytes of data, even if you specify VARCHAR(2)
DATE Use this for dealing with time spanning as small as a day, ie. this will not use / show / handle time.
TIMESTAMP, TIMESTAMP WITHOUT TIME ZONE Use this for dealing with time AND where your entire data warehouse is in the same timezone
TIMESTAMPZ, TIMESTAMP WITH TIMEZONE Use this for dealing with time across various time zones

Data Type Usage Examples

Below are some examples of how to use the above data types in your code.

CREATE TABLE    <TABLE_NAME>
(
    COLUMN_NAME     SMALLINT,
    COLUMN_NAME     INT,
    COLUMN_NAME     INTEGER,
    COLUMN_NAME     INT,
    COLUMN_NAME     INT4,
    COLUMN_NAME     BIGINT,
    COLUMN_NAME     INT8,
    COLUMN_NAME     DECIMAL(20,2),
    COLUMN_NAME     NUMERIC(20,2),
    COLUMN_NAME     REAL,
    COLUMN_NAME     FLOAT4,
    COLUMN_NAME     DOUBLE PRECISION,
    COLUMN_NAME     FLOAT,
    COLUMN_NAME     FLOAT8,
    COLUMN_NAME     BOOLEAN DEFAULT FALSE,
    COLUMN_NAME     BOOL DEFAULT FALSE,
    COLUMN_NAME     CHAR(36),
    COLUMN_NAME     CHARACTER(36),
    COLUMN_NAME     NCHAR(36),
    COLUMN_NAME     BPCHAR(36),
    COLUMN_NAME     VARCHAR(36),
    COLUMN_NAME     CHARACTER VARYING(36),
    COLUMN_NAME     NVARCHAR(36),
    COLUMN_NAME     TEXT(36),
    COLUMN_NAME     DATE,
    COLUMN_NAME     TIMESTAMP,
    COLUMN_NAME     TIMESTAMP WITHOUT TIMEZONE,
    COLUMN_NAME     TIMESTAMPZ,
    COLUMN_NAME     TIMESTAMP WITH TIMEZONE,    
);
Enter fullscreen mode Exit fullscreen mode

Which Data Type to use?

So there's a lot of data types to pick, and plenty of overlap, so why not just use VARCHAR(999) for everything and go about your day? Reasons, that's why! The below will help.

Dealing with numbers

  • Firstly, numbers are WAY more performant than text so you should never use CHAR or VARCHAR when you could be using INT, DECIMAL, or DATE.
  • INTEGERS don't have decimal places, so don't use them when your customer needs to go down to the decimal level, i.e Currency.
  • INT2 is obviously more performant than INT4/INT8 however it will cap out at 32767 so only use it for small numbers.
  • When dealing with decimals, be smart about how many decimal points, it helps performance. If you only need two decimal places then just specify 2

Dealing with Flags

  • Bool is what you should use for flags,
  • Don't use CHAR(1) or VARCHAR(1) when you could use BOOL as CHAR(1) uses up 4 bytes of data and VARCHAR(1) uses 6 bytes when BOOL uses 1 byte.
  • Try and set a default flag as it will save you in the long run

Dealing with Dates

  • When you are just dealing with the DATE, i.e the day, month, or year then use DATE
  • For Time just use TIMESTAMP
  • Only use TIMESTAMPZ when dealing with multiple time zones, good data warehouses use the same timezone across all data
  • Never put a date into a VARCHAR

Dealing with Text

  • Don't use CHAR if you don't know how long the text is going to be, you will hit size errors, i.e. this text is too big for the field, or you will be forced to set the limit too high wasting space (char uses up all the space by filling it in with spaces).
  • Conversely don't use VARCHAR if you know the length of all your values. For example a GUID, which is always 36 characters long should be char(36) not VARCHAR(36) as VARCHAR(36) is actually 40 bytes long. Redshift will perform better on char in these scenarios.
  • Don't use VARCHAR for anything less than 6 bytes, you won't gain any space with VARCHAR(2)
  • Don't use CHAR or VARCHAR if you are using a flag as BOOL will be quicker and smaller

Data Compression

Compression, also known as Encoding, makes the column smaller. You can chose different types of compression for different scenarios, and some compression types can only be used on certain data types. In theory, compressing data too much can make it longer to read, however that's not often the case as Amazon makes sure it's compression methods balance out storage and reading. In some scenarios, compression actually can use up more space.

Compression Types

Smush

Type Keyword Applicable Data Types
Raw (no compression) RAW All Types
AZ64 AZ64 INT2/INT4/INT8/DECIMAL/DATE/TIMESTAMP/TIMESTAMPZ
Byte Dictionary BYTEDICT All but BOOL & TEXT
Delta DELTA INT2/INT4/INT8/DECIMAL/DATE/TIMESTAMP/TIMESTAMPZ
Delta 32k DELTA32K INT4/INT8/DECIMAL/DATE/TIMESTAMP/TIMESTAMPZ
LZO LZO All but BOOL and FLOAT/FLOAT8
Mostly8 MOSTLY8 INT2/INT4/INT8/DECIMAL
Mostly16 MOSTLY16 INT4/INT8/DECIMAL
Mostly32 MOSTLY32 INT8/DECIMAL
Run-length RUNLENGHT All but TEXT
Text TEXT255, TEXT32K VARCHAR Only
Zstandard ZSTD All but TEXT

What one do I use?

Confusion

Short Answer

AZ64, unless it doesn't apply, then ZSTD for everything else. Most analysis won't require you to nail compression.

Long Answer

Each compression has their specific use cases. Some are more general in their storage while some work in a very specific manner which lead to some very specific downsides if not used properly.

Some TLDRs...

  • RAW: No compression, your supposed to not compress your first sort key, so use it there.
  • AZ64: An aggressive compression algorithm with good savings and performance. A general all rounder for Integers and Dates.
  • BYTEDICT: Creates a dictionary of values, and so repeated values take up no space. Only use on tables with values that repeat a lot. It's dictionary is limited to 256 values, before new ones get stored as RAW.
  • DELTA /DELTA32K: Compresses data by only recording the difference between values. Only good if the delta (difference) is small and incremental. Large differences can actually cause DELTA to use more space than if it was uncompressed.
  • LZO: An aggressive compression algorithm with good savings and performance. Used to be the go to, though now you should use ZSTD or AZ64 instead as they are newer and perform better.
  • MOSTLY8/16/32: This method is one you use when most of the values in a column are 8/16/32 bits, however their are some outlying larger values. So by specifying a Mostyl8, you are saying the majority of values can be compressed to 8 bytes and the outliers left as raw. Only use when you know you can compress most of the columns.
  • RUNLENGHT: Very similar to ByteDict in the fact that repeat values, are recycled with one value. It's not limited like ByteDict to a set number of values, however you shouldn't use this on any a sort key column .
  • TEXT255/TEXT32K: For text values only, works similarly to ByteDict in that it makes a dictionary of values. TEXT255 stores values in 1 byte indexes and is limited to the first 245 unique words in a column before new values are stored uncompressed. TEXT32k stores values in 2 byte indexes and will keep storing new words until it hits a hard limit of 32k bytes of data for the combined dictionary. Only low unique text values will result in good compression.
  • ZSTD: An aggressive compression algorithm with good savings and performance. Will seldom result in using more data than it saves unlike other compression method. Use this where AZ64 does not apply.

Pro-Tip: If sort key columns are compressed more aggressively than other columns in the same query, Redshift may perform poorly.

Redshift can tell you what it recommends.

If you build a table and run the below command, Redshift will recommend, per column, what the compression should be and will even include it's guess at how MUCH the new compression will help by.

analyse compression <table_name>
Enter fullscreen mode Exit fullscreen mode

header image drawn by me


Who am I?

You should read....

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .