Storage size for varchar length in Redshift

redshiftvarchar

So, a lot of databases will store the length prefix for a varchar field in 1 byte if the length is less than 255, and 2 bytes if it is more. For example, the MySQL docs say:

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

I'm wondering if the same thing is true for Redshift? I've scoured the docs, but can't find it for the life of me!

Incidentally, I ask the question because a coworker built our entire data warehouse using varchar(256) for most fields – all the restrictions of short field length, none of the benefits of 1-byte length storage (unless Redshift uses 2 bytes regardless).

Best Answer

According to this page Redshift varchars use "4 bytes + total bytes for characters...". The varchar stored size is likely a much smaller issue with Redshift due to columns being compressed.

Related Solutions

Sql-server – Which problems arise, declaring the size of all varchar parameters as max in stored proc

Since your talking about the proc params:

CREATE PROCEDURE myProc
  @var1 varchar(30),
  @var2 varchar(max)
AS
...

If the parameters are defined as the correct length, SQL will throw an error when calling the procedure. If you always define as (max) then you'll have to manually handle passed parameters that are too long for the destination table column before any processing in the proc happens.

So in a nutshell, more work on your end since you'll have to add error handling to the proc you didn't need before.

Mysql – VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed

As the docs say the min size occupied by a varchar is 1B and the max is 256B (before 5.0.3) and 65,536B (after 5.0.3)

But when it comes to look-up performance always searching on a char will be faster then a varchar (it makes sense since you will have fixed size rows then the look-up process will be much faster because it can calculate exactly where in the data the desired value is )

EDIT

If you have varchar(100) and inserted a string of 10 chars then the space occupied is 11B (varchar uses 1B to store the length of the string max. 255 for ver <= 5.0.3 and 2B for ver > 5.0.3 since max. 65,535).
If you have char(100) and inserted a string of 10 chars then the space occupied is 100B.

Best Answer

Related Solutions

Sql-server – Which problems arise, declaring the size of all varchar parameters as max in stored proc

Mysql – VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed

Related Question