If you do frequent range filtering or equality filtering on one column, specify that column as the sort key.

Queries will be more efficient because they can skip entire blocks that fall outside the time range.

If recent data is queried most frequently, specify the timestamp column as the leading column for the sort key.

When it comes to choosing the best sortkey, Amazon has this to say: If the data is not sorted, more of the disk blocks (possibly all of them) have to be scanned.” “For example, if a table stores five years of data sorted by date and a query specifies a date range of one month, up to 98% of the disk blocks can be eliminated from the scan. Here’s an example from Amazon’s documentation: A prominent example would be a date column. Which Columns Should be Sortkeys?Ī good rule of thumb is to designate any column that is commonly used in JOINs or in the “WHERE” clause of a SQL query as a sortkey. Civis's import feature currently supports a maximum of two sortkeys per table. Redshift allows for up to 400 columns in a table to be designated as sortkeys. Assigning a sortkey can help Redshift quickly find data when executing a query. Sortkeys What is a Sortkey?Ī sortkey is a designation given to a column that allows Redshift to optimize its query planning process. It will slow down joins between the events table and your other atomic tables because data will need to be shuffled between nodes.Distkeys and Sortkeys are Redshift-only column designations that can help speed up query performance. A skew value of 4.00 or higher is considered undesirable. Amazon has an article on data skew including a query to find the skew value: Identifying Tables with Data Skew or Unsorted Rows. to domain_userid) will have two negative impacts: 1.Increasing data skewĭata skew will affect query performance.

DISTKEYĪs standard all our table definitions have the distkey set to the event_id or root_id to ensure an even distribution.Ĭhanging this (e.g. The more unsorted rows there are, the more often a vacuum needs to be run. if that event was cached for a long time). With the derived_tstamp, it’s possible that events in a later run will have a derived_timestamp smaller than an earlier arriving event (e.g. This prevents the table from becoming unsorted over time. In almost all cases, events in a later run will all have a collector_tstamp that is greater than those in all earlier runs. It will increase the number of unsorted rows in atomicĮvents are sorted on collector_tstamp when we load them into Redshift. Updating the SORTKEY in atomic.events will slow down these joins. We recommend joining the atomic tables on both event_id = root_id and collector_tstamp = root_tstamp (this prevents issues with duplicates - more information). You want joins between the different atomic tables to be fast It’s possible but we do not recommend it, for the following reasons: SORTKEY 1. We received this question from one of our users:ĭo people tend to modify the sortkey or distkey of the atomic tables in Redshift if they decide to use something other than collector_tsamp?īecause the answer might help other users, we’re cross-posting it to Discourse.

Redshift sortkey and distkey