Learn about columnar database systems like Amazon Redshift and ClickHouse. Understand columnar storage architecture, optimization techniques, and implementation patterns for analytical workloads.
Learn about columnar database systems like Amazon Redshift and ClickHouse. Understand columnar storage architecture, optimization techniques, and implementation patterns for analytical workloads.
Columnar databases represent a significant architectural shift from traditional row-oriented databases,
optimizing for analytical workloads by storing data in a column-wise rather than row-wise format.
This approach provides substantial performance advantages for data warehouse and analytics applications
where queries typically involve aggregating large datasets across relatively few columns.
To understand columnar databases, it helps to contrast them with traditional row-oriented storage:
Row-Oriented Storage:
Stores all column values for a single row together.
This is efficient for retrieving complete records but requires reading all columns even when
only a subset is needed.
Columnar Storage:
Stores all values for a single column together across many rows.
This allows for reading only the columns required for a query and enables better compression and
performance for analytical workloads.
The following diagram illustrates the difference between row and columnar storage:
Row-Oriented Storage: ┌──────────────────────────────────────────────────┐ │ Row 1 │ CustomerID_1 │ Name_1 │ Email_1 │ Age_1 │ ├──────────────────────────────────────────────────┤ │ Row 2 │ CustomerID_2 │ Name_2 │ Email_2 │ Age_2 │ ├──────────────────────────────────────────────────┤ │ Row 3 │ CustomerID_3 │ Name_3 │ Email_3 │ Age_3 │ └──────────────────────────────────────────────────┘ Columnar Storage: ┌─────────────┐ ┌─────────┐ ┌─────────┐ ┌───────┐ │ CustomerID_1│ │ Name_1 │ │ Email_1 │ │ Age_1 │ │ CustomerID_2│ │ Name_2 │ │ Email_2 │ │ Age_2 │ │ CustomerID_3│ │ Name_3 │ │ Email_3 │ │ Age_3 │ └─────────────┘ └─────────┘ └─────────┘ └───────┘
Columnar databases offer several advantages for analytical workloads:
I/O Efficiency:
Queries can read only the columns they need, reducing disk I/O dramatically
for analytical queries that select few columns but scan many rows.
Improved Compression:
Data in a single column tends to be homogeneous and repetitive, allowing
for much higher compression ratios than row-oriented storage.
Vectorized Processing:
Operations can be performed on entire columns at once, enabling CPU
optimization through Single Instruction Multiple Data (SIMD) operations.
Parallel Processing:
Column-oriented data can be more easily distributed across multiple
nodes and processed in parallel.
Late Materialization:
Only constructing complete rows at the final stage of query processing,
after filtering and aggregation have reduced the dataset.
Columnar databases are particularly well-suited for:
Data warehousing and business intelligence
Ad-hoc analytical queries on large datasets
Time-series analytics
Real-time analytics dashboards
Log analysis and monitoring
Market data analysis and financial analytics
Learn about Columnar Storage Techniques and Optimizations.
One of the major advantages of columnar storage is its amenability to compression.
Column values tend to have similar data types and often contain repeated values or patterns,
enabling significant storage savings.
Common Compression Techniques
Dictionary Encoding:
Replaces column values with smaller integer references to a dictionary
of unique values. Particularly effective for columns with low cardinality (few distinct values).
Run-length Encoding (RLE):
Compresses sequences of identical values by storing the value
and the count of consecutive occurrences.
Delta Encoding:
Stores differences between consecutive values rather than the values themselves.
Effective for sorted columns with small increments.
Bit-packing:
Uses the minimum number of bits needed to represent values in a column,
especially effective for integers with a limited range.
1-- Conceptual example of dictionary encoding
2-- Original column (stored as strings)
3'United States'
4'Canada'
5'United Kingdom'
6'United States'
7'Mexico'
8'Canada'
9'United States'
10
11-- Dictionary:
120: 'United States'
131: 'Canada'
142: 'United Kingdom'
153: 'Mexico'
16
17-- Encoded column (stored as integers)
180
191
202
210
223
231
2401-- Conceptual example of run-length encoding
2-- Original column
31
41
51
61
72
82
93
103
113
121
13
14-- RLE encoded
15(1,4), (2,2), (3,3), (1,1)1-- Conceptual example of delta encoding
2-- Original column (timestamps)
31646092800
41646092860
51646092920
61646092980
71646093040
81646093100
9
10-- Base value: 1646092800
11-- Delta encoded (store differences)
120
1360
14120
15180
16240
17300Columnar databases often implement data skipping techniques to avoid reading blocks of
data that don't match query conditions.
Zone Maps:
Store metadata about each block of column data, such as minimum and
maximum values, NULL counts, and distinct value counts. These allow the query engine to skip entire
blocks that cannot contain matches for query conditions.
Late Materialization:
Delays the reconstruction of complete rows until the final stages of query processing,
working with compressed column data for as long as possible.
Predicate Pushdown:
Applies filter conditions at the storage level before data is loaded into memory,
dramatically reducing the amount of data that needs to be processed.
1-- Example of how zone maps work conceptually
2
3-- Query:
4SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';
5
6-- Zone map for the sale_date column might contain:
7Block 1: min='2022-10-01', max='2022-12-31' -- Skip this block
8Block 2: min='2023-01-01', max='2023-01-15' -- Read this block
9Block 3: min='2023-01-16', max='2023-01-31' -- Read this block
10Block 4: min='2023-02-01', max='2023-03-31' -- Skip this blockPartitioning divides data into smaller, more manageable chunks based on specific column values,
further enhancing query performance:
Time-based Partitioning:
Divides data by time periods, commonly used for time-series data.
Range Partitioning:
Divides data based on ranges of column values, ideal for numeric data.
List Partitioning:
Divides data based on specific values in a column, like countries or categories.
Hash Partitioning:
Distributes data evenly across partitions based on a hash function, useful for parallelization.
1-- Amazon Redshift partitioning example
2CREATE TABLE sales (
3 sale_id INT,
4 product_id INT,
5 sale_date DATE,
6 amount DECIMAL(10,2)
7)
8PARTITION BY RANGE (sale_date) (
9 PARTITION p1 VALUES LESS THAN ('2023-01-01'),
10 PARTITION p2 VALUES LESS THAN ('2023-02-01'),
11 PARTITION p3 VALUES LESS THAN ('2023-03-01'),
12 PARTITION p4 VALUES LESS THAN (MAXVALUE)
13);The physical organization of data in columnar databases significantly impacts query performance:
Sort Keys:
Define the order in which data is stored, improving compression and enabling
more efficient range scans.
Clustering:
Groups similar data together to improve data locality and compression.
1-- Amazon Redshift sort key example
2CREATE TABLE customer_transactions (
3 customer_id INT,
4 transaction_date TIMESTAMP,
5 product_id INT,
6 amount DECIMAL(10,2),
7 ...
8)
9SORTKEY (transaction_date, customer_id);1-- ClickHouse clustering example
2CREATE TABLE web_events (
3 event_date Date,
4 user_id UInt64,
5 event_type String,
6 url String,
7 ...
8)
9ENGINE = MergeTree()
10PARTITION BY toYYYYMM(event_date)
11ORDER BY (event_date, user_id);Amazon Redshift is a fully managed, petabyte-scale data warehouse service built on columnar storage
technology. It's designed for analyzing large datasets using standard SQL and business intelligence tools.
Redshift employs a massively parallel processing (MPP) architecture:
Leader Node:
Coordinates query execution, develops execution plans, and manages
communication with client applications.
Compute Nodes:
Execute queries and store data. Each compute node is divided into
slices, with each slice allocated a portion of the node's memory and disk space.
Redshift Spectrum:
Extends Redshift's capabilities to query data directly from S3
using the same SQL interface, enabling separation of storage and compute resources.
Here's a simplified diagram of Redshift's architecture:
┌───────────────┐ │ Client │ │ Applications │ └───────┬───────┘ │ ▼ ┌───────────────┐ ┌───────────────┐ ┌───────────────┐ │ Leader Node │────▶│ Compute Node 1│────▶│ Slice 1 │ └───────────────┘ │ │ └───────────────┘ │ │ ┌───────────────┐ │ │────▶│ Slice 2 │ └───────────────┘ └───────────────┘ ┌───────────────┐ ┌───────────────┐ │ Compute Node 2│────▶│ Slice 1 │ │ │ └───────────────┘ │ │ ┌───────────────┐ │ │────▶│ Slice 2 │ └───────────────┘ └───────────────┘
Distribution Styles
Redshift offers several distribution styles that determine how data is distributed across compute nodes:
Redshift automatically assigns a distribution style based on the data.
Distributes data evenly across nodes in a round-robin fashion.
Distributes data based on the values in one column, ensuring rows with the same
value are stored on the same node.
Stores a complete copy of the table on each compute node, useful for smaller
dimension tables.
Sort keys determine the order in which data is physically stored on disk, affecting query performance:
Compound Sort Key:
Sorts data based on the order of columns in the sort key, most
effective when queries filter or join on a prefix of the sort key columns.
Interleaved Sort Key:
Gives equal weight to each column in the sort key, useful
for queries that filter on different combinations of columns.
Data Loading and Maintenance
Redshift provides specific commands and best practices for efficient data loading:
COPY Command:
The recommended and fastest way to load data into Redshift from S3,
EMR, DynamoDB, or remote hosts.
Reclaims space and re-sorts rows to match the specified sort key.
Updates statistics that the query planner uses to build optimal execution plans.
Query Optimization Techniques
Materialized Views:
Pre-compute and store the results of complex queries for faster access.
Result Caching:
Redshift caches the results of queries, returning cached results
for identical queries to improve performance.
Short Query Acceleration (SQA):
Prioritizes short-running queries over longer ones,
improving the user experience for dashboard and interactive queries.
1-- EVEN distribution example
2CREATE TABLE web_logs (
3 request_id INT,
4 timestamp TIMESTAMP,
5 user_id INT,
6 url VARCHAR(255),
7 ...
8)
9DISTSTYLE EVEN;1-- KEY distribution example
2CREATE TABLE orders (
3 order_id INT,
4 customer_id INT,
5 order_date TIMESTAMP,
6 ...
7)
8DISTSTYLE KEY
9DISTKEY (customer_id);1-- ALL distribution example
2CREATE TABLE products (
3 product_id INT PRIMARY KEY,
4 product_name VARCHAR(100),
5 category VARCHAR(50),
6 ...
7)
8DISTSTYLE ALL;1-- Compound sort key example
2CREATE TABLE customer_orders (
3 order_id INT,
4 customer_id INT,
5 order_date TIMESTAMP,
6 ...
7)
8SORTKEY (customer_id, order_date);1-- Interleaved sort key example
2CREATE TABLE marketing_data (
3 campaign_id INT,
4 channel VARCHAR(50),
5 region VARCHAR(50),
6 date TIMESTAMP,
7 ...
8)
9INTERLEAVED SORTKEY (date, channel, region);1-- Example of loading data using the COPY command
2COPY sales
3FROM 's3://mybucket/sales-data/'
4IAM_ROLE 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
5DELIMITER '|'
6GZIP
7REGION 'us-west-2';1-- Example of a VACUUM command
2VACUUM DELETE ONLY sales; -- Reclaim space from deleted rows
3VACUUM SORT ONLY sales; -- Resort rows according to sort key
4VACUUM FULL sales; -- Both delete and sort1-- Example of an ANALYZE command
2ANALYZE; -- Analyze all tables
3ANALYZE sales; -- Analyze just the sales table
4ANALYZE sales(order_date); -- Analyze just the order_date column1-- Creating a materialized view
2CREATE MATERIALIZED VIEW daily_sales_summary AS
3SELECT
4 date_trunc('day', sale_time) as sale_date,
5 product_category,
6 SUM(amount) as total_sales,
7 COUNT(DISTINCT customer_id) as unique_customers
8FROM sales
9GROUP BY 1, 2;
10
11-- Refreshing the materialized view
12REFRESH MATERIALIZED VIEW daily_sales_summary;Redshift extends standard PostgreSQL with specialized functions for analytics:
1-- Window functions example
2SELECT
3 customer_id,
4 order_date,
5 order_amount,
6 SUM(order_amount) OVER(PARTITION BY customer_id ORDER BY order_date
7 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as customer_running_total,
8 AVG(order_amount) OVER(PARTITION BY customer_id ORDER BY order_date
9 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_average_3_orders
10FROM orders;
11
12-- Time-series functions example
13SELECT
14 date_trunc('month', order_date) as month,
15 category,
16 SUM(sales_amount) as monthly_sales,
17 LAG(SUM(sales_amount), 1) OVER(PARTITION BY category ORDER BY date_trunc('month', order_date)) as prev_month_sales,
18 SUM(sales_amount) - LAG(SUM(sales_amount), 1) OVER(PARTITION BY category ORDER BY date_trunc('month', order_date)) as month_over_month_change
19FROM sales
20GROUP BY 1, 2
21ORDER BY 2, 1;ClickHouse is an open-source columnar database management system designed for online analytical
processing (OLAP). It's particularly known for its high performance on analytical queries and
real-time data processing capabilities.
ClickHouse is built around a columnar storage engine with several distinctive architectural features:
MergeTree Engine:
The primary table engine family in ClickHouse, optimized for
insertion and query performance with automatic data merging.
Vectorized Query Execution:
Processes data in chunks (vectors) instead of
row-by-row, taking advantage of modern CPU capabilities.
Shared-Nothing Architecture:
Designed for horizontal scalability with data
sharded across multiple nodes.
Column-Oriented Storage:
Data is stored by columns with extensive compression,
making it highly efficient for analytical queries.
Table Engines
ClickHouse offers various table engines optimized for different use cases:
MergeTree Family:
The most versatile and performant engine family, supporting
primary keys, sampling, partitioning, and more.
ReplacingMergeTree:
Removes duplicate entries with the same sorting key during merges.
AggregatingMergeTree:
Pre-aggregates data during insertion and merges for faster
aggregation queries.
Distributed:
Allows working with data across multiple servers.
Data Types and Optimizations
ClickHouse offers specialized data types optimized for analytical workloads:
Low Cardinality:
Optimizes storage and processing for columns with few distinct values.
Nested Data Structures:
Support for arrays and nested structures allows for
flexible schema design.
Real-Time Data Ingestion
ClickHouse excels at real-time data ingestion for analytics:
Asynchronous Inserts:
Allow high throughput data ingestion without waiting for confirmation.
Buffer Tables:
Accumulate data in memory before flushing to disk, improving insert performance.
Data Skipping Indexes:
Allow the query engine to skip blocks of data that don't match query conditions.
Materialized Views
ClickHouse's materialized views automatically update when source data changes, enabling real-time analytics:
ClickHouse SQL Extensions
ClickHouse extends SQL with functions specialized for analytics:
1-- Basic MergeTree table
2CREATE TABLE web_events (
3 event_date Date,
4 event_time DateTime,
5 user_id UInt64,
6 event_type String,
7 url String,
8 browser String
9)
10ENGINE = MergeTree()
11PARTITION BY toYYYYMM(event_date)
12ORDER BY (event_date, user_id);1-- ReplacingMergeTree for deduplication
2CREATE TABLE user_sessions (
3 user_id UInt64,
4 session_date Date,
5 session_start DateTime,
6 session_duration UInt32,
7 source String,
8 version UInt32 -- Higher version replaces lower versions
9)
10ENGINE = ReplacingMergeTree(version)
11PARTITION BY session_date
12ORDER BY (user_id, session_start);1-- AggregatingMergeTree for real-time analytics
2CREATE TABLE daily_user_metrics (
3 date Date,
4 user_id UInt64,
5 views AggregateFunction(sum, UInt64),
6 clicks AggregateFunction(sum, UInt64),
7 duration AggregateFunction(avg, Float64)
8)
9ENGINE = AggregatingMergeTree()
10PARTITION BY toYYYYMM(date)
11ORDER BY (date, user_id);
12
13-- Inserting data with aggregation functions
14INSERT INTO daily_user_metrics
15SELECT
16 date,
17 user_id,
18 sumState(view_count),
19 sumState(click_count),
20 avgState(session_duration)
21FROM raw_user_events
22GROUP BY date, user_id;
23
24-- Querying with aggregation functions
25SELECT
26 date,
27 count(DISTINCT user_id) AS unique_users,
28 sumMerge(views) AS total_views,
29 sumMerge(clicks) AS total_clicks,
30 avgMerge(duration) AS average_duration
31FROM daily_user_metrics
32GROUP BY date;1-- Distributed table across a cluster
2CREATE TABLE events_distributed
3(
4 event_date Date,
5 event_time DateTime,
6 user_id UInt64,
7 event_type String
8)
9ENGINE = Distributed(cluster_name, database, events_local, rand());1-- Low cardinality optimization
2CREATE TABLE user_events (
3 event_id UInt64,
4 user_id UInt64,
5 event_type LowCardinality(String), -- Few distinct event types
6 country LowCardinality(String), -- Few distinct countries
7 browser LowCardinality(String), -- Few distinct browsers
8 event_time DateTime
9)
10ENGINE = MergeTree()
11PARTITION BY toYYYYMM(event_time)
12ORDER BY (user_id, event_time);1-- Nested data structures
2CREATE TABLE product_views (
3 user_id UInt64,
4 view_time DateTime,
5 product_id UInt32,
6 product_categories Array(String), -- Array of categories
7 product_tags Array(String), -- Array of tags
8 page_attributes Nested( -- Nested structure
9 name String,
10 value String
11 )
12)
13ENGINE = MergeTree()
14ORDER BY (user_id, view_time);
15
16-- Querying arrays
17SELECT
18 user_id,
19 view_time,
20 product_id,
21 product_categories,
22 arrayElement(product_categories, 1) AS primary_category
23FROM product_views
24WHERE has(product_categories, 'Electronics')
25 AND has(product_tags, 'Discount');
26
27-- Querying nested structures
28SELECT
29 user_id,
30 view_time,
31 product_id,
32 page_attributes.name,
33 page_attributes.value
34FROM product_views
35WHERE page_attributes.name = 'source'
36 AND page_attributes.value = 'google';1-- Asynchronous insert example
2INSERT INTO events VALUES (1, 'click', '2023-01-01 12:00:00') SETTINGS async_insert=1;1-- Buffer table for high-throughput inserts
2CREATE TABLE events_buffer AS events
3ENGINE = Buffer(database, events, 16, 10, 100, 10000, 1000000, 10000000, 100000000);
4
5-- Insert into buffer table
6INSERT INTO events_buffer VALUES (1, 'click', '2023-01-01 12:00:00');
7-- Data will be periodically flushed to the target table (events)1-- Data skipping indexes
2CREATE TABLE web_logs (
3 request_id UInt64,
4 url String,
5 status_code UInt16,
6 response_time_ms UInt32,
7 user_agent String,
8 ip_address String,
9 timestamp DateTime
10)
11ENGINE = MergeTree()
12PARTITION BY toYYYYMM(timestamp)
13ORDER BY (timestamp, request_id)
14SETTINGS index_granularity = 8192
15-- Create skipping indexes
16INDEX status_index status_code TYPE minmax GRANULARITY 4,
17INDEX response_time_index response_time_ms TYPE minmax GRANULARITY 4;1-- Creating a materialized view for real-time aggregation
2CREATE MATERIALIZED VIEW hourly_stats
3ENGINE = SummingMergeTree()
4PARTITION BY toYYYYMM(hour)
5ORDER BY (hour, event_type)
6AS
7SELECT
8 toStartOfHour(timestamp) AS hour,
9 event_type,
10 count() AS event_count,
11 uniqExact(user_id) AS unique_users,
12 sum(duration_ms) AS total_duration
13FROM events
14GROUP BY hour, event_type;
15
16-- Data inserted into the 'events' table will automatically
17-- update the materialized view1-- Approximate aggregation functions
2SELECT
3 toStartOfDay(timestamp) AS day,
4 uniqCombined(user_id) AS approx_unique_users, -- Uses HyperLogLog for efficiency
5 topK(10)(search_query) AS top_searches -- Top 10 search queries
6FROM search_events
7GROUP BY day;
8
9-- Array and map functions
10SELECT
11 user_id,
12 arraySort(product_ids) AS sorted_products,
13 arrayReverseSort(arrayMap(x -> x * 1.1, prices)) AS reverse_sorted_adjusted_prices,
14 arraySum(quantities) AS total_quantity,
15 arrayFirst(x -> x > 100, prices) AS first_expensive_product_price
16FROM user_purchases;
17
18-- Window functions
19SELECT
20 date,
21 category,
22 revenue,
23 runningAccumulate(sum_state, toUInt32(date), category) OVER(PARTITION BY category ORDER BY date) AS running_total,
24 revenue / lagInFrame(revenue, 1) OVER(PARTITION BY category ORDER BY date) AS day_over_day_growth
25FROM daily_sales;Optimizing performance in columnar databases requires understanding their unique characteristics
and designing schemas, queries, and data models accordingly.
Denormalization:
Unlike in OLTP databases, denormalizing data can improve query
performance by reducing the need for joins.
Column Selection:
Include only necessary columns in your tables, as columnar databases
excel when working with fewer columns.
Sort Key Selection:
Choose sort keys that match common query patterns, particularly
for range-based filters.
Partition Design:
Create partitions that align with how data is queried, typically
by date ranges for time-series data.
1-- Inefficient: Selecting all columns
2SELECT * FROM web_events WHERE event_date = '2023-01-15';
3
4-- Efficient: Selecting only required columns
5SELECT event_time, user_id, event_type
6FROM web_events WHERE event_date = '2023-01-15';1-- Without partition filter
2SELECT COUNT(*) FROM web_events
3WHERE event_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59';
4
5-- With partition filter (much more efficient)
6SELECT COUNT(*) FROM web_events
7WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';1-- Exact counting (expensive)
2SELECT COUNT(DISTINCT user_id) FROM web_events;
3
4-- Approximate counting (much faster, slight accuracy trade-off)
5-- Redshift
6SELECT APPROXIMATE COUNT(DISTINCT user_id) FROM web_events;
7
8-- ClickHouse
9SELECT uniqCombined(user_id) FROM web_events;1-- Create a pre-aggregated table or view
2CREATE TABLE daily_metrics AS
3SELECT
4 event_date,
5 event_type,
6 COUNT(*) AS event_count,
7 COUNT(DISTINCT user_id) AS unique_users
8FROM web_events
9GROUP BY event_date, event_type;
10
11-- Query the pre-aggregated data
12SELECT
13 event_date,
14 SUM(event_count) AS total_events,
15 SUM(unique_users) AS total_unique_users
16FROM daily_metrics
17WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31'
18GROUP BY event_date;1-- Redshift: Ensure distribution keys match join columns
2CREATE TABLE users (
3 user_id INT PRIMARY KEY,
4 name VARCHAR(100),
5 email VARCHAR(100)
6)
7DISTSTYLE KEY
8DISTKEY (user_id);
9
10CREATE TABLE orders (
11 order_id INT,
12 user_id INT,
13 order_date TIMESTAMP,
14 amount DECIMAL(10,2)
15)
16DISTSTYLE KEY
17DISTKEY (user_id);
18
19-- This join will be efficient because matching rows are on the same node
20SELECT u.name, COUNT(o.order_id) AS order_count, SUM(o.amount) AS total_spent
21FROM users u JOIN orders o ON u.user_id = o.user_id
22GROUP BY u.name;Efficient data loading is critical for columnar database performance:
Pre-sorting data according to your sort keys before loading can significantly improve loading performance
and subsequent query performance.
1-- Redshift: Efficient batch loading
2COPY sales
3FROM 's3://mybucket/sales-data/'
4IAM_ROLE 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
5DELIMITER '|'
6GZIP
7REGION 'us-west-2';
8
9-- ClickHouse: Efficient batch loading
10INSERT INTO sales
11FROM INFILE '/path/to/sales_data.csv'
12FORMAT CSV;1-- Redshift: Regular table maintenance
2VACUUM sales; -- Reclaim space and resort data
3ANALYZE sales; -- Update statistics for query optimization
4
5-- ClickHouse: Regular table maintenance
6OPTIMIZE TABLE sales FINAL; -- Force merges of all parts
7SYSTEM SYNC REPLICA sales; -- Ensure replicas are in syncContinuous monitoring and tuning are essential for optimal performance:
Query Analysis:
Regularly review slow queries using system tables or monitoring tools.
Resource Utilization:
Monitor CPU, memory, disk I/O, and network usage to identify bottlenecks.
Distribution Skew:
Check for uneven data distribution across nodes that might impact performance.
Compression Ratios:
Monitor and optimize compression to reduce storage costs and improve I/O performance.
1-- Redshift: Query for table sizes and compression
2SELECT
3 "schema",
4 "table",
5 size,
6 tbl_rows,
7 encoded_size_MB,
8 (encoded_size_MB::float / size_MB::float) * 100 AS compression_ratio
9FROM svv_table_info
10ORDER BY size_MB DESC;
11
12-- ClickHouse: Query for table sizes and compression
13SELECT
14 database,
15 table,
16 formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
17 formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
18 sum(data_compressed_bytes) / sum(data_uncompressed_bytes) AS compression_ratio,
19 sum(rows) AS total_rows
20FROM system.parts
21WHERE active
22GROUP BY database, table
23ORDER BY sum(data_compressed_bytes) DESC;Learn about Real-World Use Cases and Implementation Patterns.
Columnar databases excel at time-series analytics due to their efficient storage and query capabilities:
1-- ClickHouse: Efficient time-series analytics example
2-- Table design for IoT sensor data
3CREATE TABLE sensor_readings (
4 device_id UInt32,
5 sensor_type LowCardinality(String),
6 reading_time DateTime,
7 reading_date Date DEFAULT toDate(reading_time),
8 temperature Float32,
9 humidity Float32,
10 pressure Float32
11)
12ENGINE = MergeTree()
13PARTITION BY toYYYYMM(reading_date)
14ORDER BY (device_id, sensor_type, reading_time);
15
16-- Materialized view for real-time aggregations
17CREATE MATERIALIZED VIEW sensor_hourly_stats
18ENGINE = SummingMergeTree()
19PARTITION BY toYYYYMM(hour)
20ORDER BY (device_id, sensor_type, hour)
21AS
22SELECT
23 device_id,
24 sensor_type,
25 toStartOfHour(reading_time) AS hour,
26 avg(temperature) AS avg_temperature,
27 min(temperature) AS min_temperature,
28 max(temperature) AS max_temperature,
29 avg(humidity) AS avg_humidity,
30 avg(pressure) AS avg_pressure,
31 count() AS reading_count
32FROM sensor_readings
33GROUP BY device_id, sensor_type, hour;
34
35-- Example time-series analytics query
36SELECT
37 toStartOfDay(hour) AS day,
38 device_id,
39 avg(avg_temperature) AS daily_avg_temp,
40 min(min_temperature) AS daily_min_temp,
41 max(max_temperature) AS daily_max_temp,
42 sum(reading_count) AS total_readings
43FROM sensor_hourly_stats
44WHERE hour BETWEEN '2023-01-01 00:00:00' AND '2023-01-31 23:59:59'
45 AND device_id IN (1001, 1002, 1003)
46GROUP BY day, device_id
47ORDER BY device_id, day;Implementing a real-time analytics dashboard with columnar databases:
1-- Redshift: Real-time dashboard queries
2-- Table design for web analytics
3CREATE TABLE pageviews (
4 view_id BIGINT,
5 user_id BIGINT,
6 session_id VARCHAR(64),
7 page_url VARCHAR(1024),
8 referrer_url VARCHAR(1024),
9 user_agent VARCHAR(1024),
10 browser VARCHAR(100),
11 device_type VARCHAR(50),
12 country VARCHAR(50),
13 view_timestamp TIMESTAMP,
14 view_date DATE,
15 page_load_time INT,
16 is_bounce BOOLEAN
17)
18DISTKEY (view_date)
19SORTKEY (view_date, user_id);
20
21-- Create a materialized view for dashboard metrics
22CREATE MATERIALIZED VIEW dashboard_metrics AS
23SELECT
24 view_date,
25 date_trunc('hour', view_timestamp) AS hour,
26 country,
27 device_type,
28 COUNT(*) AS pageviews,
29 COUNT(DISTINCT user_id) AS unique_visitors,
30 COUNT(DISTINCT session_id) AS sessions,
31 SUM(CASE WHEN is_bounce THEN 1 ELSE 0 END)::FLOAT / COUNT(DISTINCT session_id) AS bounce_rate,
32 AVG(page_load_time) AS avg_load_time
33FROM pageviews
34GROUP BY view_date, hour, country, device_type;
35
36-- Dashboard query for real-time metrics
37SELECT
38 hour,
39 SUM(pageviews) AS total_pageviews,
40 SUM(unique_visitors) AS total_visitors,
41 SUM(sessions) AS total_sessions,
42 SUM(pageviews) / SUM(sessions) AS pages_per_session,
43 SUM(CASE WHEN sessions > 0 THEN sessions * bounce_rate ELSE 0 END) / SUM(sessions) AS overall_bounce_rate,
44 AVG(avg_load_time) AS overall_avg_load_time
45FROM dashboard_metrics
46WHERE view_date = CURRENT_DATE
47GROUP BY hour
48ORDER BY hour DESC;
49
50-- Real-time breakdown by device type
51SELECT
52 device_type,
53 SUM(pageviews) AS total_pageviews,
54 SUM(unique_visitors) AS unique_visitors,
55 ROUND(SUM(pageviews)::FLOAT / SUM(SUM(pageviews)) OVER() * 100, 2) AS pageview_percentage
56FROM dashboard_metrics
57WHERE view_date = CURRENT_DATE
58 AND hour >= date_trunc('hour', CURRENT_TIMESTAMP - INTERVAL '3 hours')
59GROUP BY device_type
60ORDER BY total_pageviews DESC;Handling high-cardinality dimensions in analytical queries:
1-- ClickHouse: Handling high-cardinality analytics
2-- Table design for user event tracking with high-cardinality dimensions
3CREATE TABLE user_events (
4 event_id UUID,
5 user_id UInt64,
6 session_id String,
7 event_time DateTime,
8 event_date Date DEFAULT toDate(event_time),
9 event_type LowCardinality(String), -- Low cardinality
10 page_url String, -- High cardinality
11 search_query String, -- High cardinality
12 item_id String, -- High cardinality
13 properties Nested( -- Dynamic properties
14 name String,
15 value String
16 )
17)
18ENGINE = MergeTree()
19PARTITION BY toYYYYMM(event_date)
20ORDER BY (event_date, user_id, event_time);
21
22-- Create a dictionary for URL normalization
23CREATE DICTIONARY url_categories (
24 url_pattern String,
25 category String
26)
27PRIMARY KEY url_pattern
28SOURCE(HTTP(URL 'http://example.com/url_categories.csv' FORMAT CSV))
29LIFETIME(MIN 300 MAX 360)
30LAYOUT(HASHED());
31
32-- Create a materialized view for aggregated analytics
33CREATE MATERIALIZED VIEW event_analytics
34ENGINE = SummingMergeTree()
35PARTITION BY toYYYYMM(date)
36ORDER BY (date, event_type, url_category)
37AS
38SELECT
39 event_date AS date,
40 event_type,
41 dictGetOrDefault('url_categories', 'category', page_url, 'Other') AS url_category,
42 count() AS event_count,
43 uniqCombined(user_id) AS unique_users,
44 uniqCombined(session_id) AS unique_sessions
45FROM user_events
46GROUP BY date, event_type, url_category;
47
48-- Query for high-cardinality dimension analytics
49-- Instead of querying by individual URLs (high cardinality), we query by URL category
50SELECT
51 date,
52 url_category,
53 sum(event_count) AS total_events,
54 sum(unique_users) AS total_unique_users
55FROM event_analytics
56WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
57GROUP BY date, url_category
58ORDER BY date, total_events DESC;
59
60-- For direct high-cardinality analysis, use approximation functions
61SELECT
62 event_date,
63 event_type,
64 topK(10)(page_url) AS top_pages, -- Top 10 pages
65 topK(10)(search_query) AS top_searches, -- Top 10 searches
66 uniqCombined(user_id) AS unique_users -- Approximate unique users
67FROM user_events
68WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31'
69GROUP BY event_date, event_type
70ORDER BY event_date, event_type;Many real-world systems combine row and columnar storage for different workloads:
OLTP in Row-Oriented Database:
Capture transactions and user interactions in a traditional row-oriented database.
Real-time Data Pipeline:
Stream changes to a columnar database for analytics.
OLAP in Columnar Database:
Perform analytics and reporting on the columnar store.
Example architectural pattern:
┌─────────────────┐ ┌───────────────────┐ ┌───────────────────┐ │ Row-Based │ │ Data Pipeline │ │ Columnar Store │ │ OLTP Database ├───▶│ (Kafka/Kinesis) ├───▶│ (Redshift/ │ │ (PostgreSQL) │ │ │ │ ClickHouse) │ └─────────────────┘ └───────────────────┘ └───────────────────┘ │ │ │ │ ┌───────▼───────┐ ┌────────▼──────────┐ │ Transactional │ │ Analytics │ │ Applications │ │ Applications │ └───────────────┘ └───────────────────┘
Columnar databases represent a fundamental shift in database architecture, optimized specifically for analytical
workloads that process large volumes of data. Their column-oriented storage format, compression capabilities,
and analytical optimizations make them the backbone of modern data warehousing and business intelligence systems.
Key takeaways from this lesson:
Storage Model:
Columnar databases store data by column rather than by row, enabling
efficient compression and analytical query performance.
Use Cases:
These databases excel at analytical workloads, especially those involving
large datasets with queries that touch few columns.
Performance Techniques:
Columnar systems leverage compression, predicate pushdown,
late materialization, and parallelism for high-performance analytics.
Implementation Options:
Amazon Redshift and ClickHouse represent two popular columnar
database systems with different architectural approaches and optimization techniques.
Best Practices:
Optimal use of columnar databases requires careful schema design,
query optimization, and maintenance practices tailored to their unique characteristics.
Whether you're building a data warehouse for business intelligence, implementing real-time analytics dashboards,
or processing time-series data from IoT devices, understanding and effectively using columnar databases
is essential for modern data professionals.
"The Data Warehouse Toolkit" by Ralph Kimball and Margy Ross
"Designing Data-Intensive Applications" by Martin Kleppmann
"Star Schema: The Complete Reference" by Christopher Adamson
(Google Research paper on columnar processing)
Your progress is saved in this browser, on this device only.