PostgreSQL JSONB 用于视频分析仪表板
看起来您只提供了来源链接，而没有贴出需要翻译的正文内容。请把要翻译的文本（除代码块和 URL 之外）粘贴在这里，我就可以为您完成简体中文翻译并保持原有的 Markdown 格式。
刚性模式在分析中的问题
视频分析数据本质上是混乱的。一天你在按地区跟踪观看次数，下一刻你需要设备分布，然后是观看时长百分位。为每个新指标添加列会导致表中出现数十个可为空的列，并且需要不断迁移。
在 DailyWatch，我们使用 PostgreSQL 的 JSONB 列类型解决了这个问题。
架构设计
我们将结构化数据保存在普通列中，将灵活的分析数据保存在 JSONB 中：
CREATE TABLE video_analytics (
id SERIAL PRIMARY KEY,
video_id TEXT NOT NULL REFERENCES videos(video_id),
captured_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
views INTEGER NOT NULL,
likes INTEGER NOT NULL,
metrics JSONB NOT NULL DEFAULT '{}'
);metrics 列存储所有可能随时间变化结构的数据：
{
"regions": {"US": 45000, "GB": 12000, "DE": 8500, "FR": 6200},
"devices": {"mobile": 0.62, "desktop": 0.31, "tablet": 0.07},
"watch_time": {"avg_seconds": 142, "p50": 98, "p95": 340},
"engagement": {"like_rate": 0.034, "comment_rate": 0.008},
"traffic_sources": {"search": 0.35, "suggested": 0.42, "external": 0.23}
}为快速查询索引 JSONB
在 JSONB 列上创建 GIN 索引可以让 PostgreSQL 高效地在 JSON 内部搜索：
-- General-purpose GIN index
CREATE INDEX idx_analytics_metrics ON video_analytics USING GIN (metrics);
-- Targeted index for a specific path (faster, smaller)
CREATE INDEX idx_analytics_regions ON video_analytics
USING GIN ((metrics -> 'regions'));通用 GIN 索引支持包含 (@>) 和存在 (?) 操作符。当你明确最常查询的键时，针对特定路径的索引更小且更快。
Source: …
查询 JSONB 数据
获取地区观看分布
SELECT video_id,
metrics -> 'regions' ->> 'US' AS us_views,
metrics -> 'regions' ->> 'DE' AS de_views,
metrics -> 'watch_time' ->> 'avg_seconds' AS avg_watch
FROM video_analytics
WHERE captured_at > NOW() - INTERVAL '24 hours'
ORDER BY views DESC
LIMIT 20;查找移动端流量超过 70 % 的视频
SELECT v.title,
a.metrics -> 'devices' ->> 'mobile' AS mobile_share
FROM video_analytics a
JOIN videos v ON v.video_id = a.video_id
WHERE (a.metrics -> 'devices' ->> 'mobile')::float > 0.70
ORDER BY a.captured_at DESC;汇总所有视频的地区观看次数
SELECT
key AS region,
SUM(value::integer) AS total_views
FROM video_analytics,
jsonb_each_text(metrics -> 'regions')
WHERE captured_at > NOW() - INTERVAL '7 days'
GROUP BY key
ORDER BY total_views DESC;jsonb_each_text 函数将 JSONB 对象展开为行 —— 在对动态键进行聚合时非常有用。
在不完全替换的情况下追加 JSONB
PostgreSQL 的 jsonb_set 在不替换整个对象的情况下更新特定路径：
-- Add a new traffic source
UPDATE video_analytics
SET metrics = jsonb_set(metrics, '{traffic_sources,direct}', '0.15')
WHERE video_id = 'abc123';
-- Merge new data into existing object
UPDATE video_analytics
SET metrics = metrics || '{"cdn_cost": 0.0023}'::jsonb
WHERE video_id = 'abc123';|| 运算符用于合并对象。已有的键会被覆盖，新的键会被添加。这就是我们在新数据到达时逐步丰富分析记录的方式。
构建时间序列视图
对于仪表板图表，我们需要随时间变化的指标：
SELECT
date_trunc('hour', captured_at) AS hour,
AVG(views) AS avg_views,
AVG((metrics -> 'watch_time' ->> 'avg_seconds')::float) AS avg_watch_time,
AVG((metrics -> 'devices' ->> 'mobile')::float) AS mobile_share
FROM video_analytics
WHERE video_id = 'abc123'
AND captured_at > NOW() - INTERVAL '7 days'
GROUP BY hour
ORDER BY hour;这会生成每小时的数据点，用于绘制观看趋势、观看时长和设备比例的图表——全部来自单个 JSONB 列。
何时不应使用 JSONB
JSONB 并不是对正确模式设计的替代方案。对于始终需要查询的数据（例如 video_id、views、likes、captured_at），请使用普通列；而对于可选的、形状可变的或不常过滤的数据，则使用 JSONB。
- 不当用法： 当需要在
video_id或标题上进行JOIN时，却把它们存放在 JSON 中。 - 恰当用法： 存储随来源而变化且随时间演进的分析细分数据。
性能说明
在约 50,000 条分析记录且使用 GIN 索引的数据集上：
- 路径提取 (
->>) 查询：1–3 ms - 包含 (
@>) 查询：2–5 ms jsonb_each_text聚合：10–20 ms- 全表 JSONB 聚合：40–80 ms
JSONB 的灵活性使我们在初始表创建后，能够在分析仪表板上迭代，而无需进行任何迁移。