Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # Introduction to Amazon Redshift Welcome to the *Amazon Redshift Database Developer Guide*. This guide focuses on helping you understand how to use Amazon Redshift to create and manage a data warehouse. If you work with databases as a designer, software developer, or administrator, this guide gives you the information you need to design, build, query, and maintain your data warehouse. Amazon Redshift is a fully managed, petabyte-scale data warehouse service in the cloud. Amazon Redshift Serverless lets you access and analyze data without the usual configurations of a provisioned data warehouse. Resources are automatically provisioned and data warehouse capacity is intelligently scaled to deliver fast performance for even the most demanding and unpredictable workloads. You don't incur charges when the data warehouse is idle, so you only pay for what you use. Regardless of the size of the dataset, you can load data and start querying right away in the Amazon Redshift query editor v2 or in your favorite business intelligence (BI) tool. Enjoy the best price performance and familiar SQL features in an easy-to-use, zero administration environment. **Topics** + [ ## Prerequisites for using Amazon Redshift ](#c-dev-guide-prereqs) + [ # Amazon Redshift architecture ](c_redshift_system_overview.md) + [ # Sample database ](c_sampledb.md) ## Prerequisites for using Amazon Redshift This topic describes prerequisites you need to use Amazon Redshift. Before you use this guide, you should read [Get started with Redshift Serverless data warehouses](https://docs.aws.amazon.com/redshift/latest/gsg/new-user-serverless.html), which goes over how to complete the following tasks. + Create a data warehouse with Amazon Redshift Serverless. + Loading in sample data with Amazon Redshift query editor v2 + Loading in data from Amazon S3. You should also know how to use your SQL client and should have a fundamental understanding of the SQL language. # Amazon Redshift architecture This topic helps you understand the components that make up Amazon Redshift. An Amazon Redshift data warehouse is an enterprise-class relational database query and management system. Amazon Redshift supports client connections with many types of applications, including business intelligence (BI), reporting, data, and analytics tools. When you run analytic queries, you are retrieving, comparing, and evaluating large amounts of data in multiple-stage operations to produce a final result. Amazon Redshift achieves efficient storage and optimum query performance through a combination of massively parallel processing, columnar data storage, and very efficient, targeted data compression encoding schemes. This section presents an introduction to the Amazon Redshift system architecture. **Topics** + [ # Data warehouse system architecture ](c_high_level_system_architecture.md) + [ # Amazon Redshift Performance ](c_challenges_achieving_high_performance_queries.md) + [ # Columnar storage ](c_columnar_storage_disk_mem_mgmnt.md) + [ # Workload management ](c_workload_mngmt_classification.md) + [ # Using Amazon Redshift with other services ](using-redshift-with-other-services.md) # Data warehouse system architecture This section explains the components that make up the Amazon Redshift data warehouse architecture, as shown in the following figure. ![\[Amazon Redshift data warehouse architecture components.\]](http://docs.aws.amazon.com/redshift/latest/dg/images/architecture.png) **Client applications** Amazon Redshift integrates with various data loading and ETL (extract, transform, and load) tools and business intelligence (BI) reporting, data mining, and analytics tools. Amazon Redshift is based on open standard PostgreSQL, so most existing SQL client applications will work with only minimal changes. For information about important differences between Amazon Redshift SQL and PostgreSQL, see [Amazon Redshift and PostgreSQL](c_redshift-and-postgres-sql.md). **Clusters** The core infrastructure component of an Amazon Redshift data warehouse is a *cluster*. A cluster is composed of one or more *compute nodes*. If a cluster is provisioned with two or more compute nodes, an additional *leader node* coordinates the compute nodes and handles external communication. Your client application interacts directly only with the leader node. The compute nodes are transparent to external applications. **Leader node** The leader node manages communications with client programs and all communication with compute nodes. It parses and develops execution plans to carry out database operations, in particular, the series of steps necessary to obtain results for complex queries. Based on the execution plan, the leader node compiles code, distributes the compiled code to the compute nodes, and assigns a portion of the data to each compute node. The leader node distributes SQL statements to the compute nodes only when a query references tables that are stored on the compute nodes. All other queries run exclusively on the leader node. Amazon Redshift is designed to implement certain SQL functions only on the leader node. A query that uses any of these functions will return an error if it references tables that reside on the compute nodes. For more information, see [SQL functions supported on the leader node](c_sql-functions-leader-node.md). **Compute nodes** The leader node compiles code for individual elements of the execution plan and assigns the code to individual compute nodes. The compute nodes run the compiled code and send intermediate results back to the leader node for final aggregation. Each compute node has its own dedicated CPU and memory, which are determined by the node type. As your workload grows, you can increase the compute capacity of a cluster by increasing the number of nodes, upgrading the node type, or both. Amazon Redshift provides several node types for your compute needs. For details of each node type, see [Amazon Redshift clusters](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-clusters.html) in the *Amazon Redshift Management Guide*. **Redshift Managed Storage** Data warehouse data is stored in a separate storage tier Redshift Managed Storage (RMS). RMS provides the ability to scale your storage to petabytes using Amazon S3 storage. RMS lets you scale and pay for computing and storage independently, so that you can size your cluster based only on your computing needs. It automatically uses high-performance SSD-based local storage as tier-1 cache. It also takes advantage of optimizations, such as data block temperature, data block age, and workload patterns to deliver high performance while scaling storage automatically to Amazon S3 when needed without requiring any action. **Node slices** A compute node is partitioned into slices. Each slice is allocated a portion of the node's memory and disk space, where it processes a portion of the workload assigned to the node. The leader node manages distributing data to the slices and apportions the workload for any queries or other database operations to the slices. The slices then work in parallel to complete the operation. The number of slices per node is determined by the node size of the cluster. For more information about the number of slices for each node size, go to [About clusters and nodes](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-clusters.html#rs-about-clusters-and-nodes) in the *Amazon Redshift Management Guide*. When you create a table, you can optionally specify one column as the distribution key. When the table is loaded with data, the rows are distributed to the node slices according to the distribution key that is defined for a table. Choosing a good distribution key enables Amazon Redshift to use parallel processing to load data and run queries efficiently. For information about choosing a distribution key, see [Choose the best distribution style](c_best-practices-best-dist-key.md). **Internal network** Amazon Redshift takes advantage of high-bandwidth connections, close proximity, and custom communication protocols to provide private, very high-speed network communication between the leader node and compute nodes. The compute nodes run on a separate, isolated network that client applications never access directly. **Databases** A cluster contains one or more databases. User data is stored on the compute nodes. Your SQL client communicates with the leader node, which in turn coordinates query run with the compute nodes. Amazon Redshift is a relational database management system (RDBMS), so it is compatible with other RDBMS applications. Although it provides the same functionality as a typical RDBMS, including online transaction processing (OLTP) functions such as inserting and deleting data, Amazon Redshift is optimized for high-performance analysis and reporting of very large datasets. Amazon Redshift is based on PostgreSQL. Amazon Redshift and PostgreSQL have a number of very important differences that you need to take into account as you design and develop your data warehouse applications. For information about how Amazon Redshift SQL differs from PostgreSQL, see [Amazon Redshift and PostgreSQL](c_redshift-and-postgres-sql.md). # Amazon Redshift Performance This topic describes the Amazon Redshift components that drive performance. Understanding these components will help you tune performance and troubleshoot poor performance with Amazon Redshift. Amazon Redshift achieves extremely fast query run by employing these performance features. **Topics** + [ ## Massively parallel processing ](#massively-parallel-processing) + [ ## Columnar data storage ](#columnar-data-storage) + [ ## Data compression ](#data-compression) + [ ## Query optimizer ](#query-optimizer) + [ ## Result caching ](#result-caching) + [ ## Compiled code ](#compiled-code) ## Massively parallel processing Massively parallel processing (MPP) enables fast run of the most complex queries operating on large amounts of data. Multiple compute nodes handle all query processing leading up to final result aggregation, with each core of each node running the same compiled query segments on portions of the entire data. Amazon Redshift distributes the rows of a table to the compute nodes so that the data can be processed in parallel. By selecting an appropriate distribution key for each table, you can optimize the distribution of data to balance the workload and minimize movement of data from node to node. For more information, see [Choose the best distribution style](c_best-practices-best-dist-key.md). Loading data from flat files takes advantage of parallel processing by spreading the workload across multiple nodes while simultaneously reading from multiple files. For more information about how to load data into tables, see [Amazon Redshift best practices for loading data](c_loading-data-best-practices.md). ## Columnar data storage Columnar storage for database tables drastically reduces the overall disk I/O requirements and is an important factor in optimizing analytic query performance. For more information, see [Columnar storage](c_columnar_storage_disk_mem_mgmnt.md). When columns are sorted appropriately, the query processor is able to rapidly filter out a large subset of data blocks. For more information, see [Choose the best sort key](c_best-practices-sort-key.md). ## Data compression Data compression reduces storage requirements, thereby reducing disk I/O, which improves query performance. When you run a query, the compressed data is read into memory, then uncompressed during query run. Loading less data into memory enables Amazon Redshift to allocate more memory to analyzing the data. Because columnar storage stores similar data sequentially, Amazon Redshift is able to apply adaptive compression encodings specifically tied to columnar data types. The best way to enable data compression on table columns is by allowing Amazon Redshift to apply optimal compression encodings when you load the table with data. To learn more about using automatic data compression, see [Loading tables with automatic compression](c_Loading_tables_auto_compress.md). ## Query optimizer The Amazon Redshift query run engine incorporates a query optimizer that is MPP-aware and also takes advantage of the columnar-oriented data storage. The Amazon Redshift query optimizer implements significant enhancements and extensions for processing complex analytic queries that often include multi-table joins, subqueries, and aggregation. To learn more about optimizing queries, see [Query performance tuning](c-optimizing-query-performance.md). ## Result caching To reduce query runtime and improve system performance, Amazon Redshift caches the results of certain types of queries in memory on the leader node. When a user submits a query, Amazon Redshift checks the results cache for a valid, cached copy of the query results. If a match is found in the result cache, Amazon Redshift uses the cached results and doesn't run the query. Result caching is transparent to the user. Result caching is turned on by default. To turn off result caching for the current session, set the [enable\$1result\$1cache\$1for\$1session](r_enable_result_cache_for_session.md) parameter to `off`. Amazon Redshift uses cached results for a new query when all of the following are true: + The user submitting the query has access permission to the objects used in the query. + The table or views in the query haven't been modified. + The query doesn't use a function that must be evaluated each time it's run, such as GETDATE. + The query doesn't reference Amazon Redshift Spectrum external tables. + Configuration parameters that might affect query results are unchanged. + The query syntactically matches the cached query. To maximize cache effectiveness and efficient use of resources, Amazon Redshift doesn't cache some large query result sets. Amazon Redshift determines whether to cache query results based on a number of factors. These factors include the number of entries in the cache and the instance type of your Amazon Redshift cluster. To determine whether a query used the result cache, query the [SVL\$1QLOG](r_SVL_QLOG.md) system view. If a query used the result cache, the source\$1query column returns the query ID of the source query. If result caching wasn't used, the source\$1query column value is NULL. The following example shows that queries submitted by userid 104 and userid 102 use the result cache from queries run by userid 100. ``` select userid, query, elapsed, source_query from svl_qlog where userid > 1 order by query desc; userid | query | elapsed | source_query -------+--------+----------+------------- 104 | 629035 | 27 | 628919 104 | 629034 | 60 | 628900 104 | 629033 | 23 | 628891 102 | 629017 | 1229393 | 102 | 628942 | 28 | 628919 102 | 628941 | 57 | 628900 102 | 628940 | 26 | 628891 100 | 628919 | 84295686 | 100 | 628900 | 87015637 | 100 | 628891 | 58808694 | ``` ## Compiled code Code compilation – Amazon Redshift generates and compiles optimized code for each query execution plan. The compiled code runs faster because it eliminates the overhead of using an interpreter. To minimize latency for new queries while preserving the performance benefits of compiled code, Amazon Redshift uses a technique called composition. Composition generates a lightweight arrangement of pre-existing logic to process new queries immediately, while simultaneously compiling highly optimized, query-specific code in the background. This removes compilation from the critical path of query execution, so new queries start faster and deliver performance consistent with subsequent runs. Amazon Redshift also uses a serverless compilation service to scale query compilations beyond the compute resources of an Amazon Redshift cluster. Compiled code segments are cached both locally on the cluster and in a virtually unlimited remote cache that persists after cluster reboots. Subsequent executions of the same query run faster because they can skip the compilation phase. By using a scalable compilation service, Amazon Redshift compiles code in parallel to provide consistently fast performance. # Columnar storage This section describes columnar storage, which is the method Amazon Redshift uses to store tabular data efficiently. Columnar storage for database tables is an important factor in optimizing analytic query performance, because it drastically reduces the overall disk I/O requirements. It reduces the amount of data you need to load from disk. The following series of illustrations describe how columnar data storage implements efficiencies, and how that translates into efficiencies when retrieving data into memory. This first illustration shows how records from database tables are typically stored into disk blocks by row. ![\[Records from a database table stored in disk blocks by row.\]](http://docs.aws.amazon.com/redshift/latest/dg/images/03a-Rows-vs-Columns.png) In a typical relational database table, each row contains field values for a single record. In row-wise database storage, data blocks store values sequentially for each consecutive column making up the entire row. If block size is smaller than the size of a record, storage for an entire record may take more than one block. If block size is larger than the size of a record, storage for an entire record may take less than one block, resulting in an inefficient use of disk space. In online transaction processing (OLTP) applications, most transactions involve frequently reading and writing all of the values for entire records, typically one record or a small number of records at a time. As a result, row-wise storage is optimal for OLTP databases. The next illustration shows how with columnar storage, the values for each column are stored sequentially into disk blocks. ![\[Records from a database table stored in disk blocks by column.\]](http://docs.aws.amazon.com/redshift/latest/dg/images/03b-Rows-vs-Columns.png) Using columnar storage, each data block stores values of a single column for multiple rows. As records enter the system, Amazon Redshift transparently converts the data to columnar storage for each of the columns. In this simplified example, using columnar storage, each data block holds column field values for as many as three times as many records as row-based storage. This means that reading the same number of column field values for the same number of records requires a third of the I/O operations compared to row-wise storage. In practice, using tables with very large numbers of columns and very large row counts, storage efficiency is even greater. An added advantage is that, since each block holds the same type of data, block data can use a compression scheme selected specifically for the column data type, further reducing disk space and I/O. For more information about compression encodings based on data types, see [Compression encodings](c_Compression_encodings.md). The savings in space for storing data on disk also carries over to retrieving and then storing that data in memory. Since many database operations only need to access or operate on one or a small number of columns at a time, you can save memory space by only retrieving blocks for columns you actually need for a query. Where OLTP transactions typically involve most or all of the columns in a row for a small number of records, data warehouse queries commonly read only a few columns for a very large number of rows. This means that reading the same number of column field values for the same number of rows requires a fraction of the I/O operations. It uses a fraction of the memory that would be required for processing row-wise blocks. In practice, using tables with very large numbers of columns and very large row counts, the efficiency gains are proportionally greater. For example, suppose a table contains 100 columns. A query that uses five columns will only need to read about five percent of the data contained in the table. This savings is repeated for possibly billions or even trillions of records for large databases. In contrast, a row-wise database would read the blocks that contain the 95 unneeded columns as well. Typical database block sizes range from 2 KB to 32 KB. Amazon Redshift uses a block size of 1 MB, which is more efficient and further reduces the number of I/O requests needed to perform any database loading or other operations that are part of query run. # Workload management This section describes workload management (WLM), which helps you understand how Amazon Redshift prepares and runs queries. Amazon Redshift workload management (WLM) enables flexible management priorities within workloads so that short, fast-running queries don't get stuck in queues behind long-running queries. Amazon Redshift creates query queues at runtime according to *service classes*, which define the configuration parameters for various types of queues, including internal system queues and user-accessible queues. From a user perspective, a user-accessible service class and a queue are functionally equivalent. For consistency, this documentation uses the term *queue* to mean a user-accessible service class as well as a runtime queue. Redshift offers automatic workload management, called **automatic WLM**, which is tuned to handle varying workloads and is the recommended default. With automatic WLM, Redshift determines resource utilization as queries arrive and dynamically determines whether to run them on the main cluster, on a concurrency scaling cluster, or to send each to a queue. (When queries are queued, automatic WLM prioritizes shorter-duration queries.) Automatic WLM maximizes total throughput and enables you to maintain efficient data-warehouse resources. You run workloads without having to be concerned with their size or how they're scheduled. Automatic WLM is the default for provisioned clusters. For more information, see [Implementing automatic WLM](https://docs.aws.amazon.com/redshift/latest/dg/automatic-wlm.html). **Note** Amazon Redshift Serverless workgroups always use automatic WLM. In times where a lot of queries or resource-intensive queries are running, workload management can scale to additional compute resources when workloads queue on local resources. Concurrency scaling with automatic WLM supports consistent performance for virtually unlimited concurrent users and queries. Redshift provisioned clusters offer **manual WLM** if you need fine-grained manual optimization. Here, the customer manages resource allocation, query concurrency and queuing. When a query runs, WLM assigns the query to a queue according to the user's user group or by matching a query group that is listed in the queue configuration. This is configured with a query-group label that the user sets. For more information, see [Implementing manual WLM](https://docs.aws.amazon.com/redshift/latest/dg/cm-c-defining-query-queues.html). Though Manual WLM can be fine tuned over time to match your workload patterns, in most cases we discourage its use because its static nature can make it more difficult for you to adapt to changing workloads through the course of a day or over an extended period. It requires more monitoring and ongoing tuning. In addition, Manual WLM in many cases doesn't use compute resources as efficiently as automatic WLM, such as for example if queues are set manually to limit memory allocated to them. An important metric to measure the success of workload management configuration is system throughput, which in other words is how many queries are completed successfully. System throughput is measured in queries per second. For more information about system metrics, see [Monitoring Amazon Redshift cluster performance](https://docs.aws.amazon.com/redshift/latest/mgmt/metrics.html). The easiest way to manage your WLM configuration is by using the Amazon Redshift Management console. You can also use the [Amazon Redshift command line interface](https://docs.aws.amazon.com/cli/latest/reference/redshift/) (CLI) or the [Amazon Redshift API](https://docs.aws.amazon.com/redshift/latest/APIReference/API_Operations.html). For more information about implementing and using workload management, see [Implementing workload management](https://docs.aws.amazon.com/redshift/latest/dg/cm-c-implementing-workload-management.html). # Using Amazon Redshift with other services This section describes how you can use other services as sources and destinations for Amazon Redshift data. Amazon Redshift integrates with other AWS services to enable you to move, transform, and load your data quickly and reliably, using data security features. ------ #### [ S3 ] Amazon Simple Storage Service (Amazon S3) is a web service that stores data in the cloud. Amazon Redshift leverages parallel processing to read and load data from multiple data files stored in Amazon S3 buckets. For more information, see [Loading data from Amazon S3](t_Loading-data-from-S3.md). You can also use parallel processing to export data from your Amazon Redshift data warehouse to multiple data files on Amazon S3. For more information, see [Unloading data in Amazon Redshift](c_unloading_data.md). ------ #### [ DynamoDB ] Amazon DynamoDB is a fully managed NoSQL database service. You can use the COPY command to load an Amazon Redshift table with data from a single Amazon DynamoDB table. For more information, see [Loading data from an Amazon DynamoDB table](t_Loading-data-from-dynamodb.md). ------ #### [ SSH ] You can use the COPY command in Amazon Redshift to load data from one or more remote hosts, such as Amazon EMR clusters, Amazon EC2 instances, or other computers. COPY connects to the remote hosts using SSH and runs commands on the remote hosts to generate data. Amazon Redshift supports multiple simultaneous connections. The COPY command reads and loads the output from multiple host sources in parallel. For more information, see [Loading data from remote hosts](loading-data-from-remote-hosts.md). ------ #### [ AWS DMS ] You can migrate data to Amazon Redshift using AWS Database Migration Service. AWS DMS can migrate your data to and from most widely used commercial and open-source databases such as Oracle, PostgreSQL, Microsoft SQL Server, Amazon Redshift, Aurora DB cluster, DynamoDB, Amazon S3, MariaDB, and MySQL. For more information, see [Using an Amazon Redshift database as a target for AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Redshift.html). ------ # Sample database This section describes TICKIT, a sample database that Amazon Redshift documentation examples use. This small database consists of seven tables: two fact tables and five dimensions. You can load the TICKIT dataset by following the steps in [Step 4: Load data from Amazon S3 to Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-create-sample-db.html) in the Amazon Redshift Getting Started Guide. ![\[The seven tables in the TICKIT sample database and their relationships to one another.\]](http://docs.aws.amazon.com/redshift/latest/dg/images/tickitdb.png) This sample database application helps analysts track sales activity for the fictional TICKIT web site, where users buy and sell tickets online for sporting events, shows, and concerts. In particular, analysts can identify ticket movement over time, success rates for sellers, and the best-selling events, venues, and seasons. Analysts can use this information to provide incentives to buyers and sellers who frequent the site, to attract new users, and to drive advertising and promotions. For example, the following query finds the top five sellers in San Diego, based on the number of tickets sold in 2008: ``` select sellerid, username, (firstname ||' '|| lastname) as name, city, sum(qtysold) from sales, date, users where sales.sellerid = users.userid and sales.dateid = date.dateid and year = 2008 and city = 'San Diego' group by sellerid, username, name, city order by 5 desc limit 5; sellerid | username | name | city | sum ----------+----------+-------------------+-----------+----- 49977 | JJK84WTE | Julie Hanson | San Diego | 22 19750 | AAS23BDR | Charity Zimmerman | San Diego | 21 29069 | SVL81MEQ | Axel Grant | San Diego | 17 43632 | VAG08HKW | Griffin Dodson | San Diego | 16 36712 | RXT40MKU | Hiram Turner | San Diego | 14 (5 rows) ``` The database used for the examples in this guide contains a small data set; the two fact tables each contain less than 200,000 rows, and the dimensions range from 11 rows in the CATEGORY table up to about 50,000 rows in the USERS table. In particular, the database examples in this guide demonstrate the key features of Amazon Redshift table design: + Data distribution + Data sort + Columnar compression For information about the schemas of the tables in the TICKIT database, choose the following tabs: ------ #### [ CATEGORY table ] [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) ------ #### [ DATE table ] [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) ------ #### [ EVENT table ] [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) ------ #### [ VENUE table ] [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) ------ #### [ USERS table ] [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) ------ #### [ LISTING table ] [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) ------ #### [ SALES table ] [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) ------