{"id":7780,"date":"2020-11-17T16:51:11","date_gmt":"2020-11-17T16:51:11","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/amazon-redshift-best-practices-for-optimizing-query-performance\/"},"modified":"2026-11-03T19:29:02","modified_gmt":"2026-11-03T19:29:02","slug":"amazon-redshift-best-practices-for-optimizing-query-performance","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/amazon-redshift-best-practices-for-optimizing-query-performance\/","title":{"rendered":"Amazon Redshift: Best practices for optimizing query performance"},"content":{"rendered":"<p><em><strong>Originally published on June 17, 2020 at the Onica.com <a href=\"https:\/\/www.rackspace.com\/solve\/2024-it-outlook-report\">blog<\/a>.<\/strong><\/em><\/p>\n<p>\nOrganizations across a plethora of industries look to use data analytics&nbsp;for operations and other functions critical to success. However, as&nbsp;data volumes grow, management and value extraction can become increasingly complex.<\/p>\n<p><strong>Amazon Redshift<\/strong><\/p>\n<p>Amazon\u00ae Redshift\u00ae is a powerful data warehouse service from Amazon Web Services\u00ae (AWS)&nbsp;that simplifies data management and analytics. Let\u2019s take a look at Amazon Redshift&nbsp;and some best practices you can implement to optimize data querying performance.<\/p>\n<p><strong>&nbsp;Data lakes versus Data warehouse<\/strong><\/p>\n<p>Before digging into Amazon Redshift, it&#8217;s important to know the differences between data lakes and warehouses. A data lake, like Amazon S3, is a&nbsp;centralized data repository that stores structured and unstructured data,&nbsp;at any scale and from many sources, without altering the data.&nbsp;On the other hand, Data warehouses store data in a reconciled state&nbsp;optimized to perform ongoing analytics and only load the data needed&nbsp;for analytics from data lakes.<\/p>\n<p>Amazon Redshift takes storage for data analytics one level further,&nbsp;amalgamating the qualities of data lakes and warehouses into a&nbsp;\u201clake house\u201d approach. It allows the querying of large exabyte-scale&nbsp;data lakes while remaining cost-effective, minimizing data redundancy,&nbsp;and minimizing maintenance overhead and operational costs.<\/p>\n<p>&nbsp;<strong>Amazon Redshift architecture<\/strong><\/p>\n<p>To rapidly process complex queries on big data sets,&nbsp;Amazon Redshift architecture supports massively parallel processing (MPP)&nbsp;that distributes the job across many compute nodes for concurrent processing.&nbsp;These nodes are grouped into clusters, and each cluster consists of three types of nodes:<\/p>\n<ul>\n<li><strong>Leader Node<\/strong>: These manage connections, act as the SQL endpoint, and coordinate parallel SQL processing.<\/li>\n<li><strong>Compute Nodes<\/strong>: Composed of *slices*, these execute queries in parallel on data stored in a columnar format and in 1 MB immutable blocks. An Amazon Redshift cluster can contain between 1 and 128 compute nodes, portioned into slices that contain the table data and act as a local processing zone.<\/li>\n<li><strong>Amazon Redshift Spectrum Nodes<\/strong>: These execute queries against an Amazon S3 data lake.<\/li>\n<\/ul>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26494-body-Redshift_Architecture_Fig1.jpg\" alt=\"redshift Pic 1\" \/><\/p>\n<p><strong>Optimizing query performance&nbsp;<\/strong><\/p>\n<p>By bringing the physical layout of data in the cluster into congruence with your query patterns,&nbsp;you can extract optimal querying performance. If Amazon Redshift is not performing optimally,&nbsp;consider reconfiguring workload management.<\/p>\n<p><strong>Reconfigure workload management (WLM)<\/strong><\/p>\n<p>Often left in its default setting, tuning WLM can improve performance.&nbsp;You can automate this task or perform it manually. When automated,&nbsp;Amazon Redshift manages memory usage and concurrency based on cluster-resource usage. It allows you to set up eight priority-designated queues. When&nbsp;done manually, you can adjust the number of concurrent queries,&nbsp;memory allocation, and targets.<\/p>\n<p>You can also optimize querying performance through the following WLM configuration parameters:<\/p>\n<ul>\n<li><strong>Query monitoring<\/strong>&nbsp;rules help you manage expensive or runaway queries.<\/li>\n<li><strong>Short query<\/strong>&nbsp;acceleration helps you prioritize short-running queries over longer-running queries by using machine learning algorithms to predict querying execution&nbsp;time.<\/li>\n<li><strong>Concurrency scaling<\/strong>&nbsp;helps you add multiple transient clusters in seconds to speed up concurrent read queries.<\/li>\n<\/ul>\n<p><strong>WLM best practices<\/strong><\/p>\n<p>Some WLM tuning best practices include:<\/p>\n<ul>\n<li>Creating different WLM queries for different types of workloads.<\/li>\n<li>Limiting maximum total concurrency for the main cluster to 15 or less to maximize throughput.<\/li>\n<li>Enabling concurrency scaling.<\/li>\n<li>Minimizing the number of resources in a queue.<\/li>\n<\/ul>\n<p><strong>Refining data distribution<\/strong><br \/>\nThe rows of a table are automatically distributed by Amazon&nbsp;Redshift across node slices, based on the following distribution styles:<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li><em>`AUTO<\/em>`: Starts with ALL and switches to EVEN as the table grows.<\/li>\n<li><em>`ALL`<\/em>: Consists of small, frequently joined, and infrequently modified tables placed on&nbsp;the first slice of each compute node.<\/li>\n<li><em>`EVEN<\/em>`: Consists of large, standalone fact tables that are not&nbsp;frequently joined or aggregated in a round-robin distribution across the slices.<\/li>\n<li><em>`KEY`<\/em>: Consists of frequently joined fact tables or large dimension tables. In this style, a column value is hashed, and the same hash value is placed on the same slice.<\/li>\n<\/ul>\n<p>Using the right distribution patterns can maximize the performance&nbsp;of `<em>JOIN<\/em>`, `<em>GROUP BY<\/em>`, and <em>`INSERT INTO SELECT<\/em>` operations.<\/p>\n<p><strong>Refining data sorting<\/strong><\/p>\n<p>Sort keys define the physical order of data on a disk.&nbsp;Table columns used in <em>`WHERE<\/em>` clause predicates are a good choice for sort keys and commonly use date or time-related columns.&nbsp;Use Zone maps, stored in memory and generated automatically, to define the value extremes for each block of data.&nbsp;Effectively using sort keys and zone maps together can help you restrict scans to the minimum required number of blocks.&nbsp;The following diagram illustrates how table sorting focuses on scanning targets for time-based queries, thereby&nbsp;improving query performance.<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2026\/blog_article_images\/26494-body-Column_SortKeys_Fig3_new.jpg\" alt=\"redshift pic 2\" \/><\/p>\n<p><strong>&nbsp;Optimal query performance best practices<\/strong><\/p>\n<p>Using the previously mentioned Amazon Redshift changes can&nbsp;improve query performance and improve cost and resource<br \/>\nefficiency. Here are some more best practices you can implement&nbsp;for further performance improvement:<\/p>\n<ul>\n<li>Use <em>`SORT<\/em>` keys on columns that are often used in <em>`WHERE<\/em>` clause filters.<\/li>\n<li>Use <em>`DISTKEY<\/em>` on columns that are often used in<em> `JOIN`<\/em> predicates.<\/li>\n<li>Compress all columns except the first sort-key column.<\/li>\n<li>Partition data in the data lake based on query filters such as <em>access patterns<\/em>.<\/li>\n<\/ul>\n<p>To explore some more best practices, take a deeper dive into the&nbsp;Amazon Redshift changes, and see an example of an in-depth query&nbsp;analysis, read the&nbsp;<a href=\"https:\/\/aws.amazon.com\/blogs\/apn\/best-practices-from-onica-for-optimizing-query-performance-on-amazon-redshift\/?utm_content=131837700&amp;utm_medium=social&amp;utm_source=linkedin&amp;hss_channel=lcp-25057969\">AWS Partner Network (APN) Blog<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Amazon Redshift: Best practices for optimizing query performance<\/p>\n","protected":false},"author":2,"featured_media":7782,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26494","content_type":"blog","created_by":"10130","authored_on":"2024-01-14T16:51:10+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rackspace Technical Staff","seo_description":"Learn the best practices to optimize your query performance on Amazon Redshift, managing large data volumes efficiently, and extracting meaningful insights.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26494-insights_image-database-technical-blog-1-insights.jpg","field_partners":"","rxt_platforms":"Amazon Web Services (AWS)","product":"","publisheddate":"2020-11-17T16:51:11","rxt_solutions":"Databases, Data Management, Data Modernization","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"Amazon Redshift Queries Best Practices | Rackspace","field_topics":"","field_slug":"amazon-redshift-best-practices-for-optimizing-query-performance","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7780","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7780","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7780"}],"version-history":[{"count":3,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7780\/revisions"}],"predecessor-version":[{"id":9669,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7780\/revisions\/9669"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7782"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7780"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7780"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7780"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}