Umami从SQLite到ClickHouse的可扩展数据存储架构解析

引言：隐私分析工具的数据存储挑战

在数据隐私意识觉醒的时代，网站分析工具正面临前所未有的信任危机。Google Analytics 等传统工具虽然功能强大，但其数据中心部署模式和数据共享机制让越来越多开发者产生担忧。Umami 作为一款开源的隐私优先分析工具，以其轻量级设计和数据主权优势脱颖而出。

然而，真正让 Umami 在技术社区获得认可的，不仅是它的隐私保护特性，更在于其精心设计的数据存储架构。从单机 SQLite 到分布式 ClickHouse，Umami 为不同规模的应用提供了无缝的扩展路径。这种渐进式扩展的架构设计，为我们在构建可扩展分析系统时提供了宝贵经验。

核心架构：Prisma 驱动的多数据库适配

Umami 的数据访问层通过 Prisma ORM 实现了高度抽象的数据库操作封装，这是其多数据库支持的核心。src/lib/prisma.ts 中的实现解决了多数据库兼容、查询性能优化与类型安全访问等核心问题。

1. 多数据库适配策略

Prisma 层通过动态 SQL 生成技术，实现了 MySQL 与 PostgreSQL 的无缝兼容：

// 核心适配逻辑
function getDateQuery(field: string, unit: string, timezone?: string): string {
  const db = getDatabaseType();
  
  if (db === POSTGRESQL) {
    return timezone 
      ? `to_char(date_trunc('${unit}', ${field} at time zone '${timezone}'), '${POSTGRESQL_DATE_FORMATS[unit]}')`
      : `to_char(date_trunc('${unit}', ${field} ), '${POSTGRESQL_DATE_FORMATS[unit]}')`;
  }
  
  if (db === MYSQL) {
    return timezone 
      ? `date_format(convert_tz(${field},'+00:00','${tz}'), '${MYSQL_DATE_FORMATS[unit]}')`
      : `date_format(${field}, '${MYSQL_DATE_FORMATS[unit]}')`;
  }
}

这种设计允许同一套业务逻辑在不同数据库间无缝切换，开发者无需修改查询代码即可适应不同的存储后端。

2. 参数化查询构建器

通过封装 rawQuery 方法实现参数化查询，既保证了 SQL 注入防护，又保留了复杂查询的灵活性：

async function rawQuery(sql: string, data: object): Promise<any> {
  const db = getDatabaseType();
  const params = [];
  
  // 参数化查询处理
  const query = sql.replaceAll(/\{\{\s*(\w+)(::\w+)?\s*}}/g, (...args) => {
    const [, name] = args;
    params.push(data[name]);
    return db === MYSQL ? '?' : `$${params.length}`;
  });
  
  return prisma.$queryRaw(query, params);
}

渐进式扩展：三种数据架构的完整演进

第一阶段：SQLite（单机轻量级）

对于个人博客或小型项目，SQLite 提供了零配置的轻量级解决方案。

优势：

• 零配置，单文件部署
• 无需独立数据库服务
• 理想的开发和测试环境

实际部署示例：

# SQLite版本的Docker部署
docker run -d \
  --name umami \
  -p 3000:3000 \
  -e DATABASE_URL=file:./umami.db \
  -e DATABASE_TYPE=sqlite \
  docker.umami.is/umami-software/umami:latest

第二阶段：PostgreSQL（企业级单节点）

当业务规模增长时，PostgreSQL 成为理想的升级路径，提供了完整的事务支持和更强大的查询能力。

核心配置优化：

-- 为时间序列数据优化表结构
CREATE TABLE pageview (
  id BIGSERIAL PRIMARY KEY,
  website_id INTEGER NOT NULL,
  session_id VARCHAR(50),
  url VARCHAR(2048),
  referrer VARCHAR(2048),
  timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  country VARCHAR(50),
  device_type VARCHAR(20),
  browser VARCHAR(50)
);

-- 创建必要的索引
CREATE INDEX idx_pageview_website_timestamp ON pageview(website_id, timestamp);
CREATE INDEX idx_pageview_session ON pageview(session_id);

第三阶段：ClickHouse（分布式分析）

对于需要处理亿级事件的场景，ClickHouse 的列式存储和并行计算能力提供了无与伦比的性能。

ClickHouse 集成配置：

// 时间窗口划分机制
export function getMinimumUnit(startDate: number | Date, endDate: number | Date) {
  if (differenceInMinutes(endDate, startDate) <= 60) {
    return 'minute';
  } else if (differenceInHours(endDate, startDate) <= 48) {
    return 'hour';
  } else if (differenceInCalendarMonths(endDate, startDate) <= 6) {
    return 'day';
  } else if (differenceInCalendarMonths(endDate, startDate) <= 24) {
    return 'month';
  }
  return 'year';
}

实际部署：完整的多阶段迁移指南

环境准备和初始部署

1. 基础环境搭建

# 克隆仓库并安装依赖
git clone https://github.com/umami-software/umami.git
cd umami
pnpm install

# 创建环境配置文件
cat > .env << 'EOF'
DATABASE_URL=postgresql://user:password@localhost:5432/umami
CLOUD_MODE=true
PORT=3000
EOF

# 构建和启动
pnpm run build
pnpm run start

2. Docker Compose 全栈部署

# docker-compose.yml
version: '3'
services:
  database:
    image: postgres:15
    environment:
      POSTGRES_DB: umami
      POSTGRES_USER: umami
      POSTGRES_PASSWORD: umami_password
    volumes:
      - umami-db:/var/lib/postgresql/data
    
  umami:
    image: docker.umami.is/umami-software/umami:latest
    ports:
      - "3000:3000"
    environment:
      DATABASE_URL: postgresql://umami:umami_password@database:5432/umami
    depends_on:
      - database

volumes:
  umami-db:

数据库迁移和扩展

从 SQLite 迁移到 PostgreSQL：

# 1. 导出SQLite数据
sqlite3 umami.db .dump > umami_dump.sql

# 2. 转换数据格式
sed -e 's/AUTOINCREMENT/auto_increment/g' \
    -e 's/BIGSERIAL/serial/g' \
    -e 's/INTEGER PRIMARY KEY/serial PRIMARY KEY/g' \
    umami_dump.sql > umami_postgres.sql

# 3. 导入PostgreSQL
psql -h localhost -U umami -d umami -f umami_postgres.sql

从 PostgreSQL 升级到 ClickHouse 集群：

-- ClickHouse表结构设计
CREATE TABLE pageviews (
  website_id UInt32,
  session_id String,
  url String,
  referrer String,
  timestamp DateTime64(3, 'UTC'),
  country LowCardinality(String),
  device_type LowCardinality(String),
  browser LowCardinality(String)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (website_id, timestamp, session_id)
SETTINGS index_granularity = 8192;

性能优化：不同规模下的调优策略

小规模场景（<10 万日 PV）

-- 基本的查询优化
EXPLAIN SELECT 
  COUNT(*) as views,
  COUNT(DISTINCT session_id) as visitors
FROM pageview 
WHERE website_id = 1 
  AND timestamp >= CURRENT_DATE - INTERVAL 7 DAY;

-- 预期响应时间：<100ms

中等规模（10 万 - 100 万日 PV）

-- 分区表优化
CREATE TABLE pageview_partitioned (
  LIKE pageview INCLUDING ALL
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp);

-- 物化视图用于实时聚合
CREATE MATERIALIZED VIEW daily_stats
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
AS SELECT
  toDate(timestamp) as date,
  website_id,
  uniqExact(session_id) as visitors,
  count() as views
FROM pageview
GROUP BY date, website_id;

大规模场景（>1000 万日 PV）

-- ClickHouse分布式查询优化
SELECT
  toStartOfDay(timestamp) as date,
  uniqExact(session_id) as visitors,
  count() as pageviews
FROM pageviews_dist
WHERE date >= today() - 7
GROUP BY date
ORDER BY date;

-- 预期响应时间：<500ms处理数亿行数据

监控和运维：确保系统稳定运行

1. 关键性能指标监控

PostgreSQL 监控查询：

-- 实时活跃连接数
SELECT count(*) FROM pg_stat_activity 
WHERE state = 'active';

-- 慢查询分析
SELECT query, mean_time, calls, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- 表大小和索引使用情况
SELECT 
  schemaname, tablename, 
  pg_size_pretty(pg_total_relation_size(tablename::regclass)) as size,
  idx_tup_read, idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

ClickHouse 集群监控：

-- 集群状态检查
SELECT 
  host_name,
  uptime,
  count() as parts_count,
  sum(data_compressed_bytes) / 1024 / 1024 as compressed_mb
FROM system.parts
WHERE active = 1
GROUP BY host_name;

-- 查询性能分析
SELECT 
  query,
  count() as query_count,
  avg(query_duration_ms) as avg_duration,
  max(query_duration_ms) as max_duration
FROM system.query_log
WHERE event_date >= today() - 1
GROUP BY query
ORDER BY query_count DESC
LIMIT 10;

2. 自动化备份策略

PostgreSQL 备份脚本：

#!/bin/bash
# backup.sh
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups/umami"

# 创建备份
pg_dump -h localhost -U umami -d umami \
  --no-password --format=custom \
  --file="${BACKUP_DIR}/umami_${DATE}.dump"

# 保留最近7天的备份
find $BACKUP_DIR -name "umami_*.dump" -mtime +7 -delete

# 上传到云存储（可选）
aws s3 cp "${BACKUP_DIR}/umami_${DATE}.dump" s3://your-backup-bucket/umami/

总结：构建可扩展分析系统的关键经验

Umami 的数据存储架构设计体现了几个重要的工程原则：

1. 渐进式扩展：从 SQLite 到 ClickHouse 的平滑升级路径，避免了 "一刀切" 的技术选择
2. 抽象层设计：Prisma ORM 提供的数据库无关抽象，允许业务逻辑与存储细节分离
3. 性能优化导向：针对不同数据量级的特定优化策略，确保资源利用的最优化
4. 运维友好：完善的监控、备份和迁移工具链，降低了运维复杂度

这种设计哲学不仅适用于网站分析工具，也是构建任何需要处理用户行为数据的系统的宝贵参考。在数据隐私日益重要的今天，Umami 用技术实力证明，隐私保护与功能强大并非矛盾，而是可以兼得的目标。

通过学习 Umami 的架构设计，我们可以更好地理解如何构建既满足合规要求，又具备企业级扩展能力的现代数据分析平台。

---

资料来源：

1. Umami GitHub 仓库 - 官方源代码实现
2. Umami 官方文档 - 部署和配置指南
3. Prisma ORM 官方文档 - 多数据库适配技术细节