存 X 数据：事后改不回来的表结构决策
存 X 数据：事后改不回来的表结构决策
大多数 X 数据项目的开头都一样：把 JSON 塞进一张表、一条推一行、赶紧开始分析。这套能用一周。
然后你重跑采集器，拿到了重复行。然后你发现数据库里某条推的点赞数已经三周没更新了，而你没法知道它是什么时候变的。然后你想查「哪些帖子是在被引用之后才涨起来的」——发现你的表结构在第一次 insert 时就把这个信息丢掉了。
这些问题提前想都不难。但一旦有了真实数据，修起来都很贵，因为修法是迁移。 这篇讲的是值得第一天就定对的那几个决策。
如果你还在选怎么取数，四种方案对比是更前面那个问题。这篇假设 JSON 已经在往你这儿来了，你需要给它找个地方放——有个免费 demo 端点，可以拿真实字段结构来对着设计。
代价最高的那个决策：一张表还是两张
本能写法是一条推一行、计数当列：
-- 所有人第一版都这么写
CREATE TABLE tweets (
id TEXT PRIMARY KEY,
author TEXT,
text TEXT,
created_at TIMESTAMPTZ,
like_count INTEGER, -- ← 问题在这
retweet_count INTEGER,
reply_count INTEGER
);
★问题在于推文内容是不变的，而互动数一直在变。★ 正文永远不改，点赞数时刻在动。把两者放在一行，意味着每次重新取数要么覆盖掉历史、要么产生重复行，而且你没法回答「这条帖子涨得多快」。
改成两张表：
CREATE TABLE tweets ( -- 不变: 只写一次
id TEXT PRIMARY KEY,
author_id TEXT NOT NULL,
text TEXT,
created_at TIMESTAMPTZ NOT NULL,
conversation_id TEXT,
in_reply_to TEXT,
is_retweet BOOLEAN DEFAULT FALSE,
first_seen TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE tweet_metrics ( -- 可变: 每次观测追加一行
tweet_id TEXT NOT NULL REFERENCES tweets(id),
observed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
like_count INTEGER,
retweet_count INTEGER,
reply_count INTEGER,
quote_count INTEGER,
view_count BIGINT, -- ← BIGINT, 原因见下
PRIMARY KEY (tweet_id, observed_at)
);
这样重新取数就不花代价了：内容 upsert 到同一行，指标追加一条新观测。 增长速度变成一条查询，而不是需要你提前规划的东西。
如果你确实永远只需要最新值，那就保持单表——但加一个 updated_at 列，至少让你知道它有多旧。拆两张表是为了「这东西是怎么变的」这个你以后可能会问的问题。实践中这个问题通常都会来。
字段层面会咬人的几处
推文 ID 永远是字符串。 它们是 19 位的 snowflake 值，超过双精度浮点的 2^53 上限——而那正是 JSON 解析器和 JavaScript 默认给你的东西。存成 TEXT 或 BIGINT，绝不要 FLOAT/REAL，也不要让它经过一个把数字当 double 处理的 JSON 层。
有用的副作用：因为 snowflake ID 内嵌时间戳，★它们按整数排序就是时间顺序，长度相同时按字符串排也一样★。这让你不用解析日期就得到了廉价的排序和廉价的高水位标记。
view_count 需要 BIGINT。 它经常比其他计数高好几个数量级——一条 2,000 赞的帖子可以有几百万浏览。32 位整数在约 21 亿处溢出，而一条爆款帖会到那儿。
计数会往下走。 账号被删、用户取消点赞、帖子被移除。如果你写了假设单调增长的约束或告警，它会在正常行为上触发。 不要加 CHECK (like_count >= 0) 这类对变化方向的假设。
created_at 不是 ISO 8601。 X 的格式需要显式格式串解析，不是朴素的 ISO parser。写入时转换、存成真正的时间戳类型、保持 UTC——字段结构在读懂推文对象那篇里。
Upsert：让重跑变成无害操作
一个采集器最有用的性质，就是跑第二遍什么都不发生。这就一句 SQL：
import psycopg
from psycopg.rows import dict_row
UPSERT_TWEET = """
INSERT INTO tweets (id, author_id, text, created_at, conversation_id,
in_reply_to, is_retweet)
VALUES (%(id)s, %(author_id)s, %(text)s, %(created_at)s, %(conversation_id)s,
%(in_reply_to)s, %(is_retweet)s)
ON CONFLICT (id) DO NOTHING
"""
INSERT_METRICS = """
INSERT INTO tweet_metrics (tweet_id, like_count, retweet_count,
reply_count, quote_count, view_count)
VALUES (%(id)s, %(like_count)s, %(retweet_count)s,
%(reply_count)s, %(quote_count)s, %(view_count)s)
ON CONFLICT (tweet_id, observed_at) DO NOTHING
"""
def store(conn, tweets):
with conn.cursor() as cur:
cur.executemany(UPSERT_TWEET, tweets) # 内容: 只写一次
cur.executemany(INSERT_METRICS, tweets) # 指标: 每轮一行
conn.commit()
★内容表用 DO NOTHING 而不是 DO UPDATE，这是刻意的。★ 推文正文不会变，所以 update 要么是空操作、要么说明有什么地方不对。如果你确实想追踪编辑（X 在一个时间窗内允许编辑），那是第三张版本表，不是一次把原文毁掉的覆盖写。
用 executemany 批量，并且按批提交、不要按行提交。 逐行提交会主导大任务的运行时间——你在为每一条推付一次磁盘同步。一页结果提交一次是对的粒度，而且这样被打断最多只损失一页。
大家会跳过的那部分：记录你没取到什么
只写成功记录的采集器，没法告诉你窟窿在哪。六周之后，数据里的一个缺口和一段安静期长得一模一样——和监控失效看不见是同一种歧义。
CREATE TABLE collection_runs (
id BIGSERIAL PRIMARY KEY,
target TEXT NOT NULL, -- 用户名或查询词
started_at TIMESTAMPTZ NOT NULL,
ended_at TIMESTAMPTZ,
cursor_end TEXT, -- 续跑点
rows_new INTEGER DEFAULT 0,
status TEXT NOT NULL, -- ok | partial | rate_limited | failed
error TEXT
);
这张表几乎不花成本，而且回答的是真正会出现的问题：这个账号的采集到底完成过没有、我该从哪续跑、这个缺口是真实的还是一次失败。★partial 这个状态是最重要的★——它标记出一个你知道它不完整的窗口，而不是让它看起来像已经完成了。
规模：单表什么时候不够用
大致阈值，按痛感真正开始的位置给：
| 量级 | 怎么做 |
|---|---|
| 100 万条以下 | 一张 Postgres 表，author_id 和 created_at 上建索引。不要过度设计。 |
| 100 万–5000 万 | 按月分区 tweet_metrics——涨的是它（按观测涨），不是 tweets（按推文涨） |
| 再往上 | 内容留在关系库，指标历史迁到列存（Parquet、ClickHouse） |
★涨的是 tweet_metrics，不是 tweets。★ 一条推按小时观测一年就是 8,760 行指标对 1 行内容。按推文数来估规模会低估好几个数量级——这也是为什么分区指标表比分区内容表划算得多。
负担得起的话，把原始 JSON 留着。 一个 raw JSONB 列，或者把原始响应存进对象存储——意味着一个表结构错误可以靠重新处理来救，而不是重新取数。重新取数要花 API 调用，而且对已删除的帖子根本不可能；重新处理是免费的。★这是整个设计里最便宜的保险。★
该建什么索引，不该建什么
CREATE INDEX ON tweets (author_id, created_at DESC); -- 最常见的查询
CREATE INDEX ON tweets (conversation_id); -- 还原话题串
CREATE INDEX ON tweet_metrics (tweet_id, observed_at DESC);
(author_id, created_at DESC) 这个联合索引服务的是「这个账号最近的帖子」，而这是大多数人要问的东西。在两个列上各建一个索引做不到这件事——查询规划器组合它们的效率不如一个联合索引。
不要用 btree 索引正文列。 它很大、很少被精确匹配，而且索引会比数据本身还大。要全文检索的话，那是 tsvector 上的 GIN 索引，而且只在你确认需要之后再加。
我们的 API 返回的字段结构就是这些表所围绕的，所以映射是直接的，不用你反推。用 Google 一键注册送 $0.20 额度、不用信用卡，够你采几千条推、在真正投入之前看看这套表结构合不合你要问的问题。
常见问题
推文该怎么存进数据库？ 两张表：按推文 ID 的不变内容表，和按「推文 ID + 观测时间」的只追加指标表。这个拆分正是让采集器重跑变成无害操作的原因。
互动数能不能直接当推文行上的列？ 只有在你永远不会问「它是怎么变的」时才可以。 计数时刻在动而正文永不变，放在一起会逼你在「覆盖历史」和「重复行」之间选一个。
推文 ID 该用什么数据类型？
TEXT 或 BIGINT。它们是 19 位值、超过 2^53 浮点上限，任何把它们当 double 处理的东西都会静默损坏末几位。
view_count 为什么要用 BIGINT？ 因为它经常比点赞数大好几个数量级——一条不算爆的帖子就能有几百万浏览，而 32 位列在约 21 亿处溢出。
互动数会减少吗？ 会。 取消点赞、账号被删、帖子被移除都会让计数下降。不要写假设「只增不减」的约束或告警。
tweets 表的主键用什么好？ 推文 ID 本身，它全局唯一且稳定。指标表用「推文 ID + 观测时间」的联合主键。
怎么让采集器幂等？
内容表 ON CONFLICT (id) DO NOTHING，指标表用联合主键。这样重跑是什么都不插入，而不是重复或报错。
为什么用 DO NOTHING 而不是 DO UPDATE？ 因为推文正文是不变的——update 要么没意义、要么是出问题的信号。想追踪 X 的编辑功能，用单独的版本表，不要覆盖写。
该按行提交还是按批提交？ 按批。 逐行提交为每条推付一次磁盘同步，会主导你的运行时间。一页提交一次，同时也意味着被打断最多损失一页。
作者信息存同一张表还是分开？ 分开，按作者 ID 做键，推文上放外键。资料会随时间变化，每条推重复一遍既浪费空间，又让「他当时的粉丝数是多少」变成无法回答的问题。
原始 JSON 要留吗？ 空间负担得起就留。 这意味着表结构错误可以靠重新处理修好、而不用重新取数——而对已经被删除的帖子，重新取数是不可能的。
X 的 created_at 格式怎么处理？ 用显式格式串解析，不要用 ISO parser，转成 UTC，存成真正的时间戳类型。不要留着当文本。
怎么发现采集数据里的缺口？ 把每一轮都记下来——目标、游标、行数、状态。没有这张表，缺口和安静期长得一模一样，你分不清在看哪一种。
status 里的 partial 实际意味着什么？ 意味着你知道这个窗口不完整，通常是翻页中途撞上限流或报错。标记出来，才能以后有意识地重采那个窗口。
什么时候该分区？ 几百万行左右，而且先分区指标表——它按观测增长，所以会按你的采样频率倍数超过内容表。
到底哪张表涨得最快？ 指标表。 一条推按小时观测一年是 8,760 行指标对 1 行内容。按观测数规划，不要按推文数。
Postgres、SQLite 还是列存？ 单进程采集器用 SQLite 到几百万行是真的够。有并发了换 Postgres。分析查询成为主体之后，指标历史上列存。
到底需要哪些索引？ 作者 + 日期倒序的联合索引、话题串用的 conversation ID、指标用的「推文 ID + 观测时间」。其他的只在你实测到慢查询之后再加。
要给推文正文建索引吗？
不要用 btree——索引会超过数据本身大小而收益很小。确实需要全文检索时，用 tsvector 上的 GIN 索引。
怎么从存下来的推文还原话题串？
按 conversation_id 分组、按 ID 排序。 串里每条回复都带着首条推的 conversation ID，所以一次索引查找就能重建整串。
已删除的推文怎么存？
留着你已经采到的，然后打标记。 加一个 deleted_detected_at 列而不是删行——「这东西被删了」这件事本身往往才是有意思的部分。
这套要占多少磁盘？ 内容很小，累积起来的是指标。按「观测频率 × 推文数 × 行宽」估，指标表会以很大幅度占主导。
能完全不用数据库吗？ 一次性分析用 NDJSON 或 Parquet 文件完全可以，而且能绕开这一整套。数据库是在你持续采集、并且要跨批次查询时才值回票价。
大多数人会搞错的那一件事是什么？ ★把可变的计数放进不变的内容行里。★ 这篇其他所有东西以后都能调整，只有这一个需要迁移，而且通常还要重新采集一遍。
这个需要 X 开发者账号吗？ 通过第三方 API 读公开数据不需要，你用的是服务商的 key。官方 API 是另一套安排，主要是写操作才需要。
怎么避免重复取已经有的推文？ 记下每个目标已存的最大推文 ID，每轮入库前把小于等于它的丢掉。 因为 ID 是按时间有序的，这个比较就是一个精确的「比这更新的全部」过滤器——不用解析时间戳。
两张表的拆分是这篇里唯一值得在有数据之前就定好的决策，因为它也是唯一一个事后需要迁移的。用 Google 注册送 $0.20 额度、不绑卡，够你灌一批真实样本、拿自己的查询去验证这套表结构。