How to select DIST key in Redshift for fast searching and aggregation

Question

I have a data of 225+ million in my Redshift Table. This data is the activity logs of the user who are coming and going at that time after scanning the door like the access logs of the user at what time they are taking entry and exit from the door.

Now I want to select the best DIST key to divide this data across the nodes so that processing and aggregation is faster. The main fields I have are :
(id unique, user_name, door_name, company_name, time timestamp(YYYY-MM-DD HH:mm:ss))
Now I want to fetch data of the user on that particular day for which I already prepared the query so can you please let me know which one is the best DIST key here to use out of these fields or do we need to create a custom DIST key?

I have provided the sort key on time field as this is mandatory field but to get the user data on each day aggregation is required like break the timestamp into day format and then group by transformed time and user_uuid so what is the best DIST key to provide and company_name is also mandatory field.

Since we can only select one DIST key per table it is the most important step.
Also whenever we use DIST key in the filter ORDER BY and LIMIT does not work is there any alternative to it?

Thanks

Answer

When choosing your distkey , Choose the key based on how frequently it is joined and the size of the joining rows.Also check the distribution profile to ensure it is uniformly distributed.Ensure that the column does not have a significant amount of null value also check the level of data skew.

Designate both the dimension table's primary key and the fact table's corresponding foreign key as the DISTKEY. Also while choosing the column for your list key ensure to choose the the column that have the maximum distinct value and high cardinality
You mentioned you have tried both DISTSTYLE EVEN and DISTKEY ,have you tried using AUTO where Redshift automatically chooses the best distribution based on your data. For Best practices for choosing the distribution style:https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-best-dist-key.html

Based on the explanation you gave above having your sort key as a time stamp is a good practice. I added additional reading below for best practices when choosing a sort key : https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-sort-key.html

Answer

Your sort key is most important for your scans, as it allows redshift to skip multiple blocks that don't contain the values you want. Each block contains a header with the range of value it contains. If that range doesn't match the criteria redshift won't scan that block saving your i/o and time. If you will be using multiple common sort columns choose compound and filter down then. Otherwise interleaved may be the best

Answer

The choice of your DIST key will be more important for what joins you will be doing with this table as this determines whether a distribution or broadcast will needed see:

https://docs.aws.amazon.com/redshift/latest/dg/c_data_redistribution.html

Ideally your DIST key on joins will match each table so 0 distribution is needed. If you're just scanning the table and there are no joins the DIST Key will only affect performance if you choose a key with skewed values.

For example, if you distribute on a column with only two values you will only distribute on 2 nodes out of 4. So you want ideally a multiple similar (at larger distinct value counts this matters less) to the number of nodes that you also have.

How to select DIST key in Redshift for fast searching and aggregation

Relevant content