Amazon Redshift’s Top Performance Features and Latest Capabilities

Discover Redshift’s top performance features and latest capabilities to streamline your data analysis processes

Announced in November 2012, Amazon Redshift, according to AWS, is a fully managed cloud data warehouse service designed to quickly and cost-effectively analyze large datasets using existing SQL-based tools and business intelligence applications. With optimized performance for datasets ranging from hundreds of gigabytes to petabyte-scale, Redshift offers fast query speeds regardless of data size.

By leveraging Amazon Redshift’s advanced performance features and latest capabilities, users can boost their cluster’s performance, streamline administrative tasks, and reduce cloud expenses. In this post, we’ll highlight some of Redshift’s key performance features and recently added capabilities, each with links to resources where you can learn more.

Performance Features

Amazon Redshift boasts a variety of performance features, including the following:

1. Massively Parallel Processing (MPP): MPP can distribute queries across multiple nodes and perform queries in parallel for faster processing. Multiple compute nodes handle all query processing leading up to the final result aggregation, with each node’s core running the same compiled query segments on portions of the entire data.
2. Columnar Storage: Redshift stores data in a columnar format, which allows for efficient compression and faster queries on large datasets. Columnar storage for database tables drastically reduces the overall disk I/O requirements and is essential in optimizing analytical query performance.
3. Automatic Compression: Based on their evaluation, Redshift users can manually apply compression encodings to table columns. Alternatively, users can let Redshift analyze and apply compression automatically based on sample data. Redshift automatically compresses data as it is loaded into the database, which reduces storage requirements and improves query performance. Automatic compression balances overall performance when choosing a compression encoding. Auto encoding (ENCODE AUTO) is the default for tables.
4. Advanced Compression Encodings: Redshift supports advanced column-level compression encodings such as run-length encoding, delta encoding, dictionary encoding, and Zstandard (ZSTD) to reduce storage requirements further.
5. Query Optimization: Redshift has a sophisticated query optimizer that can automatically optimize complex analytic queries to run more efficiently, including multi-table joins, subqueries, and aggregation.
6. Workload Management (WLM): WLM allows users to manage workloads and prioritize queries based on importance, ensuring that critical queries are processed quickly. WLM is used to define multiple query queues and to route queries to the appropriate queues at runtime. Users can configure Redshift to run with either automatic WLM or manual WLM.
7. Concurrency Scaling: Concurrency scaling allows Redshift to automatically scale the number of nodes to handle increased query loads, ensuring consistent query performance. As a result, Concurrency Scaling enables users to support thousands of concurrent users and concurrent queries with consistently fast query performance.
8. Advanced Query Accelerator (AQUA): AQUA is a distributed hardware-accelerated cache that uses the AWS Nitro System and custom FPGA-based acceleration. AQUA pushes the computation needed to handle reduction and aggregation queries closer to the data. This reduces network traffic, offloads work from the CPUs in the RA3 nodes, and improves query performance by up to 10x.
9. Amazon Redshift Advisor: Redshift Advisor offers specific recommendations about changes to help users improve their performance and decrease operating costs for their Redshift cluster. Advisor develops its customized recommendations by analyzing performance and usage metrics for the user’s cluster.
10. Zone Maps: Redshift uses zone maps to improve query performance by storing metadata about the data distribution on each node. A zone map exists for each 1 MB block and consists of in-memory metadata that tracks the minimum and maximum values within the block. This metadata is accessed before a disk scan to identify which blocks are relevant to the query.
11. Query Monitoring: Redshift provides detailed query monitoring and performance metrics, allowing users to identify and resolve performance issues quickly.
12. Automatic Table Optimization: Automatic table optimization is a self-tuning feature that allows Redshift to automatically optimize the design of tables by applying sort and distribution keys without the need for administrator intervention.

Additional Amazon Redshift performance features include RA3 instance types, leader node result caching, compiled query code, query plan visualization, and a choice of data distribution styles.

Latest Capabilities

Amazon Redshift is constantly evolving to meet the needs of its users and keep up with the latest technological advancements in a highly competitive market segment. Staying current with Redshift’s latest innovative features can help users enhance performance, reduce administrative overhead, and optimize cloud costs. According to Amazon Redshift What’s New, a short list of newer capabilities includes the following, in chronological order:

1. Data Sharing: For read purposes, data sharing allows users to share live data with relative security and ease across Amazon Redshift clusters, AWS accounts, and AWS Regions (March 2021).
2. Redshift-managed VPC Endpoints: VPC endpoints allow users to create a private connection between the Redshift cluster’s VPC and a VPC running a client tool, including from within another account. This approach enables users to access Redshift without using public IP addresses or routing traffic across the internet (April 2021).
3. Amazon Redshift ML: Amazon Redshift ML allows users to work with Amazon SageMaker Autopilot to automatically obtain the best model and make the prediction function available in Amazon Redshift (May 2021).
4. Support for Spatial Data: With support for spatial 3D and 4D geometries and new spatial functions, Redshift allows the representation of geographic features using geometric data. Spatial data is vital in business analytics, reporting, and forecasting (August 2021).
5. Automated Materialized Views (AutoMV): AutoVM allows users to precompute and store query results in a precomputed result set, improving query performance and reducing the amount of data scanned during queries. Similar queries don’t have to re-run the same logic each time; they can retrieve records from the existing result set (July 2022).
6. Amazon Redshift Serverless: Amazon Redshift Serverless allows users to run and scale analytics without provisioning and managing data warehouse clusters. Redshift Serverless automatically provisions and scales data warehouse capacity to deliver fast performance for even the most demanding workloads, and users only pay for what they use (July 2022).
7. Concurrency Scaling for Write Workloads: Concurrency scaling for write workloads allows users who currently use concurrency scaling for read operations to now automatically scale common write operations as well, such as COPY, INSERT, UPDATE, DELETE onto the concurrency scaling clusters (November 2022).
8. Streaming Ingestion for Kinesis and MSK: Redshift streaming ingestion allows users to natively ingest hundreds of megabytes of data per second from Amazon Kinesis Data Streams and Amazon MSK into a Redshift materialized view (November 2022).
9. Multi-AZ for RA3 Clusters (Preview): Multi-AZ for RA3 clusters, in preview, allows users to continue operating in failure scenarios where an unexpected event happens in an Availability Zone (AZ). Redshift deploys equal compute resources in two AZs that can be accessed through a single endpoint (November 2022).
10. Auto-Copy from Amazon S3 (Preview): Auto-copy from Amazon S3, in preview, allows users to set up continuous file ingestion rules to track their Amazon S3 paths and automatically load new files without the need for additional tools or custom ETL pipelines (November 2022).
11. Dynamic Data Masking (DDM): DDM allows users to simplify protecting sensitive data in Redshift. Access data through masking policies that apply custom obfuscation rules to a given user or role (April 2023).
12. MERGE SQL Command: MERGE SQL command allows users to combine a series of Data Manipulation Language (DML) statements into a single statement. Merge ensures that all operations are performed together in a single transaction (April 2023).

In this brief post, we have learned how users can enhance their cluster’s performance, simplify administrative tasks, and reduce cloud expenses by taking advantage of Amazon Redshift’s advanced performance features and the latest capabilities.

References

• Amazon Redshift Documentation
• Amazon Redshift What’s New
• Amazon Redshift AWS Blogs
• What’s new with Amazon Redshift video (re:Invent 2022)
• Deep dive on best practices for Amazon Redshift video (re:Invent 2020)
• Amazon Redshift: Ten years of continuous reinvention article (May 2022)
• Amazon Redshift re-invented research paper (June 2022)
• Redshift Research Project: White Papers

This blog represents my viewpoints and not those of my employer, Amazon Web Services (AWS). All product names, logos, and brands are the property of their respective owners.