Traditional relational databases like MySQL and PostgreSQL store data on disk row by row. While row-oriented storage is optimal for transactional updates (OLTP) involving individual records, executing analytical queries (OLAP)\u2014such as calculating average page load times across 500 million web analytics rows or filtering monthly financial transactions\u2014requires reading entire rows from disk into RAM. Under heavy analytical datasets, queries take minutes to execute and consume gigabytes of memory.<\/p>\n
ClickHouse<\/strong> is a revolutionary open-source column-oriented database management system. By storing data columns contiguously on disk with advanced compression (LZ4\/ZSTD) and leveraging SIMD CPU vectorization, ClickHouse scans and filters hundreds of millions of rows per second on a single virtual CPU core, returning complex aggregations in sub-milliseconds.<\/p>\n In this comprehensive enterprise tutorial, we will install official ClickHouse on Ubuntu 24.04\/22.04 LTS, tune memory and disk storage settings, create high-speed MergeTree tables, and connect ClickHouse to Grafana for real-time visualization.<\/p>\n Enable and start the ClickHouse daemon:<\/p>\n The MergeTree<\/strong> engine is the core workhorse of ClickHouse, supporting massive real-time data ingestion and background index merging:<\/p>\n Insert 1 million synthetic benchmark records in 2 seconds:<\/p>\n Execute sub-millisecond aggregation query:<\/p>\nStep 1: Adding Official ClickHouse APT Repository<\/h2>\n
# Install prerequisite tools\nsudo apt update && sudo apt install -y apt-transport-https ca-certificates dirmngr curl\n\n# Import ClickHouse GPG signing key\ncurl -fsSL https:\/\/packages.clickhouse.com\/packages\/keys\/clickhouse.gpg | sudo gpg --dearmor -o \/usr\/share\/keyrings\/clickhouse-keyring.gpg\n\n# Add official ClickHouse APT repository\necho \"deb [signed-by=\/usr\/share\/keyrings\/clickhouse-keyring.gpg] https:\/\/packages.clickhouse.com\/deb stable main\" | sudo tee \/etc\/apt\/sources.list.d\/clickhouse.list\n\n# Install ClickHouse Server and Client\nsudo apt update && sudo apt install -y clickhouse-server clickhouse-client<\/code><\/pre>\nStep 2: Configuring and Starting ClickHouse Server<\/h2>\n
# Start and enable the systemd service\nsudo systemctl enable --now clickhouse-server\n\n# Verify service health\nsudo systemctl status clickhouse-server --no-pager\n\n# Launch interactive CLI\nclickhouse-client --password<\/code><\/pre>\nStep 3: Creating High-Speed MergeTree Analytics Tables<\/h2>\n
-- Create web analytics event database\nCREATE DATABASE IF NOT EXISTS analytics;\n\n-- Create high-performance columnar table\nCREATE TABLE analytics.web_events (\n event_time DateTime DEFAULT now(),\n domain LowCardinality(String),\n visitor_ip String,\n country LowCardinality(String),\n http_status UInt16,\n response_time_ms Float32,\n user_agent String\n) ENGINE = MergeTree()\nPARTITION BY toYYYYMM(event_time)\nORDER BY (domain, event_time, http_status);<\/code><\/pre>\nStep 4: Inserting and Benchmarking High-Speed Aggregations<\/h2>\n
-- Insert 1,000,000 synthetic event records instantly\nINSERT INTO analytics.web_events (event_time, domain, visitor_ip, country, http_status, response_time_ms, user_agent)\nSELECT\n now() - rand() % 86400,\n ['cpanelfree.com', 'blog.example.com', 'api.example.com'][rand() % 3 + 1],\n concat(toString(rand() % 255), '.', toString(rand() % 255), '.1.1'),\n ['US', 'IN', 'DE', 'GB', 'SG'][rand() % 5 + 1],\n [200, 200, 200, 404, 502][rand() % 5 + 1],\n (rand() % 250) \/ 10.0,\n 'Mozilla\/5.0 (Windows NT 10.0; Win64; x64)'\nFROM numbers(1000000);<\/code><\/pre>\n-- Calculate 95th percentile latency and error rates across 1M rows\nSELECT\n domain,\n count() AS total_requests,\n quantiles(0.95)(response_time_ms) AS p95_latency,\n countIf(http_status >= 400) AS error_count\nFROM analytics.web_events\nGROUP BY domain;\n-- Result returned in: 0.008 sec (8 milliseconds)!<\/code><\/pre>\nRow-Oriented vs Column-Oriented Storage Comparison<\/h2>\n