ClickHouse

**Essential Configuration:**

1. **Server Configuration (/etc/clickhouse-server/config.xml):**
- `<listen_host>`: IP addresses to listen on (0.0.0.0 for all)
- `<http_port>`: HTTP interface port (default 8123)
- `<tcp_port>`: Native protocol port (default 9000)
- `<max_connections>`: Maximum concurrent connections
- `<max_concurrent_queries>`: Limit parallel queries

2. **Memory and Performance Tuning:**
```xml
<max_server_memory_usage>80000000000</max_server_memory_usage> <!-- 80GB -->
<max_memory_usage>40000000000</max_memory_usage> <!-- 40GB per query -->
<max_bytes_before_external_group_by>20000000000</max_bytes_before_external_group_by>
<max_threads>8</max_threads> <!-- Parallel query threads -->
```

3. **Storage Configuration:**
```xml
<storage_configuration>
<disks>
<default>
<path>/var/lib/clickhouse/</path>
</default>
<fast_ssd>
<path>/mnt/fast_ssd/clickhouse/</path>
</fast_ssd>
<slow_hdd>
<path>/mnt/slow_hdd/clickhouse/</path>
</slow_hdd>
</disks>
<policies>
<hot_and_cold>
<volumes>
<hot>
<disk>fast_ssd</disk>
</hot>
<cold>
<disk>slow_hdd</disk>
</cold>
</volumes>
<move_factor>0.2</move_factor>
</hot_and_cold>
</policies>
</storage_configuration>
```

4. **Table Creation Examples:**
```sql
-- MergeTree (basic)
CREATE TABLE events (
event_date Date,
event_time DateTime,
user_id UInt32,
event_type String,
url String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
SETTINGS index_granularity = 8192;

-- ReplicatedMergeTree (with replication)
CREATE TABLE events_replicated (
event_date Date,
event_time DateTime,
user_id UInt32,
event_type String
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

-- Distributed table (cluster)
CREATE TABLE events_distributed AS events_replicated
ENGINE = Distributed(cluster_name, database_name, events_replicated, rand());
```

5. **Data Types and Compression:**
```sql
CREATE TABLE metrics (
timestamp DateTime CODEC(Delta, ZSTD),
metric_name LowCardinality(String),
value Float64 CODEC(Gorilla, ZSTD),
tags Array(String),
metadata Map(String, String)
) ENGINE = MergeTree()
ORDER BY (metric_name, timestamp);
```

6. **Query Optimization:**
```sql
-- Use PREWHERE for filtering (faster than WHERE)
SELECT * FROM events
PREWHERE event_date >= today() - 7
WHERE user_id IN (SELECT id FROM users WHERE premium = 1);

-- Materialized views for pre-aggregation
CREATE MATERIALIZED VIEW events_hourly
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_hour, event_type)
AS SELECT
toDate(event_time) AS event_date,
toHour(event_time) AS event_hour,
event_type,
count() AS event_count
FROM events
GROUP BY event_date, event_hour, event_type;
```

7. **Data Retention and TTL:**
```sql
-- Automatic data expiration
ALTER TABLE events
MODIFY TTL event_date + INTERVAL 90 DAY;

-- Move old data to cold storage
ALTER TABLE events
MODIFY TTL
event_date + INTERVAL 7 DAY TO VOLUME 'hot',
event_date + INTERVAL 30 DAY TO VOLUME 'cold',
event_date + INTERVAL 90 DAY DELETE;
```

8. **Monitoring Queries:**
```sql
-- System metrics
SELECT * FROM system.metrics;

-- Current queries
SELECT query_id, user, query, elapsed FROM system.processes;

-- Query log analysis
SELECT
query_duration_ms,
read_rows,
read_bytes,
memory_usage,
query
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;

-- Table sizes
SELECT
database,
table,
formatReadableSize(sum(bytes)) AS size,
sum(rows) AS rows
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes) DESC;
```

**Best Practices:**
- Use MergeTree engine family for production tables
- Choose ORDER BY carefully - it affects query performance
- Partition by date for time-series data (enables TTL and efficient deletes)
- Use LowCardinality for string columns with <10K unique values
- Enable compression codecs (Delta, Gorilla, ZSTD) for specific data types
- Create materialized views for frequently used aggregations
- Use distributed tables for multi-node deployments
- Monitor query performance with system.query_log
- Set appropriate max_memory_usage to prevent OOM
- Regular OPTIMIZE TABLE for merge operations (but use sparingly)