From 10,000 ft, Redshift appears like any other relational database with fairly standard SQL and entities like tables, views, stored procedures, and usual data types.
We’ll start with Tables as these are containers for persistent data storage and will allow us to dive vertically into the architecture. This is what Redshift looks like from 10,000 ft:
Redshift is a clustered warehouse and each cluster can house multiple databases. As expected, each database contains multiple objects like tables, views, stored procedures, etc.
Knowing that Redshift is a distributed and clustered service, it is logical to expect that the data tables are stored across multiple nodes.
A node is a compute unit with dedicated CPUs, memory and disk. Redshift has two types of nodes: Leader and Compute. The Leader node manages data distribution and query execution across Compute nodes. Data is stored on Compute nodes only.
To understand how Redshift distributes data, we need to know some details about compute nodes.
Slice is logical partition for disk storage. Each node has multiple slices which allow parallel access and processing across slices on each node.
The number of slices per node depends on the node instance types. Redshift currently offers 3 families of instances: Dense Compute(
dc2), Dense Storage (
ds2) , and Managed Storage(
ra3). The slices can range from 2 per node to 16 per node depending on the instance family and instance type; see this for details. The objective of this concept is to distribute the workload of queries evenly across all nodes to leverage the parallel compute and increase efficiency. As such, the default behaviour is to distribute the data evenly across all slices on all nodes when it is loaded into a table as shown below.
Each slice stores multiple tables in 1MB blocks. This system of slices and nodes achieves two objectives:
- Distribute data and compute evenly across all compute nodes.
- Colocate data and compute minimizing data transfer and increasing join efficiency across nodes.
One key feature of Redshift that influences the compute is the columnar storage of data. In addition to the architecture and design for query efficiency, the data itself is stored in a columnar format. The majority of analytical queries will utilise a small number of columns from a table for any aggregations. Without going into details, data is stored by columns rather than rows. This presents multiple advantages for Redshift.
Disk I/O is reduced significantly as only the necessary data are accessed. This means the query performance is inversely correlated with the amount of data being accessed and the number of columns in a table does not factor into disk I/O cost. A query selecting 5 columns out of 100 column table only has to access 5% of the data block space.
Each block of data contains values from a single column. This means the data type within each block is always the same. Redshift can apply specific and appropriate compression on each block increasing the amount of data being processed within the same disk and memory space. Using 1MB block size increases this efficiency in comparison with other databases which use several KB for each block.
Overall, due to compression, the large block size and columnar storage, Redshift can process data in a highly efficient manner scaling with increasing data usage. Understanding this a database developer can write optimal queries avoiding
select * as with OLTP databases.
So far, data storage and management have shown significant benefits. Now it is time to consider management of queries and workloads on Redshift. Redshift is a data warehouse and is expected to be queried by multiple users concurrently and automation processes too. Workload Management (WLM) is a way to control the compute resource allocation to groups of queries or users. Through WLM, it is possible to prioritise certain workloads and ensure the stability of processes.
WLM allows defining “queues” with specific memory allocation, concurrency limits and timeouts. Each query is executed via one of the queues. When a query is submitted, Redshift will allocate it to a specific queue based on the user or query group. There are some default queues that cannot be modified such as for superuser, vacuum maintenance and short queries (<20sec). WLM queues are configurable, however, Amazon provides an alternative which is a fully managed WLM mode called “Auto WLM”. In the “Auto WLM” mode, everything is managed by Redshift service including concurrency and memory management.