Steam用户成就存储优化：转JSON列是否可行？

问题背景 #

当前采用三张关联表存储Steam用户与成就数据：

CREATE TABLE steam_user (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    steam_id TEXT NOT NULL,
    profile_url TEXT NOT NULL,
    avatar_url TEXT NOT NULL
);

CREATE TABLE achievement (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    display_name TEXT NOT NULL,
    description TEXT NOT NULL,
    icon_url TEXT DEFAULT '' NOT NULL,
    categories SMALLINT[] DEFAULT '{}' NOT NULL
);

CREATE TABLE achievements_on_steam_users (
    id SERIAL PRIMARY KEY,
    steam_user_id INTEGER NOT NULL,  /* 外键 */
    achievement_id INTEGER NOT NULL, /* 外键 */
    achieved BOOLEAN NOT NULL DEFAULT FALSE,
    unlock_time TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00'
);

业务现状与需求：

• 每次拉取用户成就时，achievements_on_steam_users关联表会新增超1000行数据
• 核心需求：查询单个用户所有成就并与achievement表JOIN；用户重置成就时更新数据（需保留achieved=FALSE的行）
• 当前两类查询执行计划为顺序扫描+哈希右连接

现考虑将成就数据以JSON列形式存储在steam_user表中，JSON结构示例：

{
  // 键对应achievement表中的ID
  "333": {
    "achieved": true,
    "unlockTime": "1970-01-01 00:00:00"
  }
}

已知achievement表数据极少变更，ID永不修改，仅会新增ID。

方案合理性判断 #

适合采用JSON方案的场景 #

• 缓解写入压力：原来每次拉取要插入上千行关联表数据，改成JSON列后只需更新单条用户记录，写入IO次数骤减，能有效降低数据库批量插入的负载。
• 聚焦单个用户操作：如果业务几乎不需要跨用户的成就统计（比如“多少用户解锁了成就X”），仅围绕单个用户的成就查询、更新，JSON方案的聚合性更贴合业务逻辑。

需注意的潜在问题 #

• 单成就更新繁琐：用户重置成就时，若要修改部分成就的状态，需先解析整个JSON、修改对应字段再重新写入，比直接更新关联表的单条记录操作更复杂，大JSON的序列化/反序列化还会额外消耗资源。
• 存储空间膨胀：随着成就ID不断新增，每个用户的JSON列会逐渐变大，长期下来存储成本会上升。
• 复杂查询支持差：如果后续要做跨用户的成就统计，JSON方案需要全表扫描每个用户的JSON并解析，性能会大幅下降；而关联表只要添加(achievement_id, achieved)联合索引就能快速完成统计。

查询性能对比 #

单个用户成就JOIN查询 #

• 原方案（无索引）：当前执行计划是顺序扫描+哈希右连接，性能瓶颈明显，因为要扫描整个关联表定位用户的成就行。
• JSON方案：查询单个用户时，先取出JSON列解析，提取所有成就ID再和achievement表JOIN。千级成就的解析开销可控，相比无索引的原方案，性能反而可能提升；但如果原方案给achievements_on_steam_users添加(steam_user_id, achievement_id)联合索引，关联表的查询速度会比JSON方案更快，但JSON方案也不会出现“大幅下降”的情况。

跨用户统计查询 #

• JSON方案完全不占优势，这类查询会变得极其低效，必须全表扫描+JSON解析，而关联表依靠索引就能快速得到结果。

总结 #

如果你的业务核心就是单个用户的成就操作，且短期内不会有跨用户统计需求，这个JSON方案是合理的，查询性能不会大幅下降，还能解决原方案的批量写入压力。但如果有跨用户统计的可能，或者需要频繁修改单个成就状态，优先给关联表添加合适的索引（比如steam_user_id单字段索引，或者(steam_user_id, achievement_id)联合索引），比切换到JSON存储更靠谱。

内容的提问来源于stack exchange，提问作者Liryon