Redshift sortkey and distkey11/22/2023 ![]() ![]() Within those column blocks, the rows are all in the same order.Įach block also has a min and max value ("Zone Maps"), making it very easy for Redshift to 'skip over' blocks that do not contain a desired value. Data for each column is stored in separate blocks (and most likely each column uses many blocks), but within the column blocks the rows are in the same order.įor example, if a table has three columns, it will occupy at least three blocks per slice (one for each column). For the rows stored on each slice, they are stored in SORTKEY order. The SORTKEY determines how the rows are ordered within the table. The general rule is to use a column that appears in the most/biggest JOINs. Unique identifiers like product_id would make a better DISTKEY. If two tables have the same DISTKEY, then all rows in both tables with the same value for the DISTKEY column will be located on the same slice.īy the way, dates and timestamps are not good candidates for DISTKEY because they are very rarely used in a JOIN. That means that all columns for those rows are in that slice. In your example, all rows with a given orderdate would be located in the same slice. The DISTKEY distributes rows amongst slices. The first level of distribution is my DISTKEY (dates are not good but just to follow with the same example) and then internally redshift sorts by my SORTKEY, giving something like: Thanks so muchīased on the post answering this question I have tried to picture how the data perhaps look stored. So sorry folks if I'm so wrong but I need to understand well how this architecture drive the data internally. Now if I use a DISTKEY so my data is stored based that punctual column order, so if later on, I use a SORTKEY the other for my DISTKEY can't be changed or altered so how this works? So I might overall something like:ĭISTKEY storage tier and SORTKEY query execution behave If Redshift is a columnar approach then shouldn't each column has a different way to be stored? or what this really means is that: Based on a column wisely picked out among all, the whole columns are going to be stored on the same slice along with the DISTKEY and then to guarantee the performance the user can even focus the query on a specific zone to pull the required data. But, what happens with the column product_id and product_name ? are these distributed along with orderdate on the same slice and then when I execute a query Redshift uses the zone maps based on my SORTKEY to point out the zone of the column that has the data and retrieve it? In my example is clear that probably the way how the data will be distributed across the slices for the computing nodes is based on the DISTKEY orderdate. Now, we know that Redshift is a columnar approach DB optimized for data warehousing. My question is let's suppose I have a table A with three columns: CREATE TABLE ( ![]() I have read this amazing post that explains very well what means each of these regarding the table design. ![]() I have a very technical question about how Redshift deals with DISTKEY and SORTKEY internally in order to fulfill the storing tier and the query execution demands. ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |