开发时标识符应在代码中生成还是在PostgreSQL数据库中生成？

两种方案核心差异与选型分析 #

你提到的两种方案本质是应用层生成UUID主键和数据库层生成自增主键的选型问题，结合PostgreSQL特性和通用业务实践，具体分析如下：

方案1：Python侧生成UUID作为主键 #

优势

• 多表批量写入效率高：你当前要一次性解析全量JSON数据的场景下，可以在内存中一次性生成所有用户、交易记录的UUID，三个表直接批量写入甚至用COPY命令入库，不需要等数据库返回主键再填关联表，导入效率极高。
• 分布式场景适配性好：如果后续有分库分表、多服务并行写入的需求，UUID不需要依赖单节点的序列生成器，不会出现主键冲突。
• 无业务信息泄露：ID对外暴露时，第三方无法通过ID反推用户总量、交易总量等敏感业务数据，适合需要对外提供API的场景。
• 碰撞概率可以忽略：只要使用标准UUIDv4，每秒生成10亿个ID的情况下，连续生成100年才会有50%概率出现一次碰撞，生产环境只要加个唯一约束，冲突时重试一次即可，基本不需要考虑这个问题。

劣势

• 存储和索引性能略差：PostgreSQL原生uuid类型占16字节，是BIGINT类型的2倍，索引占用空间更大；无序的UUIDv4写入B树索引时会产生随机IO，数据量千万级以上时写入性能会比自增ID低10%-30%。
• 排查问题不便：UUID长度过长，人工核对数据、跨团队沟通问题时很难直接记忆和传递。

方案2：PostgreSQL生成自增ID作为主键 #

优势

• 绝对唯一性：数据库层面保证主键不会重复，不需要做额外的冲突校验。
• 写入和存储效率更高：自增ID是有序递增的，写入B树索引时是顺序IO，性能比随机UUID高，BIGINT类型仅占8字节，索引和数据存储占用空间更小，适合交易这类数据量较大的表。
• 你担心的性能损失基本不存在：PostgreSQL支持INSERT ... RETURNING id语法，插入主表时可以直接返回生成的主键ID，不需要额外发起查询请求；批量插入多条主表记录时也可以一次性返回所有生成的ID，关联表直接批量写入即可，性能损失微乎其微，普通业务场景完全感知不到。
• 运维更友好：短数字ID容易记忆，人工排查数据、做数据校对时效率高很多。

劣势

• 分布式场景适配性差：如果后续要做分库分表，或者多节点并行写入，自增ID会出现冲突，需要额外搭建分布式ID生成服务。
• 容易泄露业务信息：对外暴露ID时，第三方可以通过ID的增长情况估算出用户量、交易量等核心业务数据。
• 批量导入流程略繁琐：批量导入全量数据时，需要先插入用户表拿到所有用户ID，再关联到交易记录后插入交易表，比UUID多一步数据关联的流程。

适用场景与选择建议 #

• 选UUID的场景：
  • 经常需要批量导入大量用户、交易数据，对导入效率要求极高
  • 已有明确的分布式部署、分库分表规划，或者有多系统并行写入数据库的需求
  • 需要将用户ID、交易ID对外暴露给前端或者第三方合作方
• 选自增ID的场景：
  • 业务数据量增长可控，短时间内没有分库分表的计划
  • 交易表数据量极大，对写入性能、存储成本要求较高
  • 系统仅内部使用，不需要对外暴露ID，经常需要人工排查数据问题
• 通用折中方案（推荐大多数场景使用）：
  内部主键用PostgreSQL生成的BIGINT GENERATED AS IDENTITY自增ID，保证性能和存储效率；额外加一个uuid唯一字段作为对外暴露的标识，兼顾内部运维效率和外部数据安全的需求，完美解决两种方案的痛点。

额外提示：你提到的pi_trans关联表其实不需要，一笔交易仅属于一个用户，是典型的一对多关系，直接在trans表中加user_id字段关联personal_info表的主键即可，多对多关系才需要中间关联表，你当前的场景加中间表反而会增加不必要的查询成本。

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