KernelRanch
返回文章列表

PostgreSQL 类型选择速查：字节数、精度与五个常见选错

2026-11-10 #数据库

类型选错不会立刻报错，会在数据长大、精度丢失或索引变大之后才显现。下面把常用类型的实际存储和取舍列出来。

1. 常用类型的存储

类型字节备注
bool1
int2 / int4 / int82 / 4 / 8对应 smallint / integer / bigint
date4
timestamp / timestamptz8 / 8两者等宽，差别在语义
uuid16
interval16
text / varchar / numeric / json / jsonb变长typlen = -1，按内容分配

注意最后一行：varchar(n) 与 text 的存储方式相同，长度限制只多一次检查，不会让存储更省。

2. 五个常见选错

整数用 int4 做主键。自增主键用 int4 到 21 亿就到顶，迁移时改类型要重写表。直接用 bigint（GENERATED ALWAYS AS IDENTITY），多出的 4 字节换掉一次停机。

金额用 float8。浮点无法精确表示 0.1，累计后会出现 0.30000000000000004 这类结果。金额用 numeric(12,2)，代价是运算比整数慢，但正确。

varchar(255) 当万能字符串。它和 text 存储一样，255 只是约束。真正需要约束时用 CHECK (length(x) <= 255) 或 varchar(n) 都行，但不要以为它能省空间。反过来说，char(n) 会用空格补齐，几乎总是错误选择。

json 代替 jsonb。json 保留原始文本（含空白与键序），每次访问都要重新解析；jsonb 存解析后的二进制，可建 GIN 索引。除非要保留原始格式，否则用 jsonb。

时间用 timestamp 存 now()。会按会话时区转成本地墙上时间，丢掉时区信息。记录时刻用 timestamptz（见同目录另一篇）。

3. 三个需要权衡的

uuid 做主键。16 字节，比 bigint 大一倍，随机 UUID 还会让 B-tree 插入分散、索引膨胀。需要外部可见的 ID 时，可以考虑"内部 bigint 主键 + 外部 uuid 唯一列"。

numeric 做统计聚合。精确但有额外开销，海量聚合时可以先用 float8 近似、最终结果再转 numeric。精度要求由业务决定，不是由类型决定。

text 存大文本再全文检索。text 本身没问题，但要做全文检索得配 tsvector 列或表达式索引，否则 LIKE '%x%' 只能全表扫。

4. 查现有表的类型分布

SELECT table_name, column_name, data_type,
       coalesce(character_maximum_length::text, numeric_precision::text, '-') AS size
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

小结

四条默认选择能覆盖大多数场景：主键用 bigint 自增、金额用 numeric、字符串用 text、时刻用 timestamptz。

类型选择的收益往往不在单行，而在索引与 IO：int4 换成 int8 让索引变大，text 换成 varchar(20) 什么都不会变。改类型前先想清楚省的是什么。

数据来源与检索时间

检索时间 2026-11-10。字节数来自本机 18.6 的 pg_type 实查。

来源URL对应数据
PostgreSQL 18 文档《8.1 数字类型》https://www.postgresql.org/docs/18/datatype-numeric.htmlint2/int4/int8 宽度、numeric 的精确语义与性能取舍
PostgreSQL 18 文档《8.3 字符类型》https://www.postgresql.org/docs/18/datatype-character.htmltext 与 varchar(n) 存储一致、char(n) 空格补齐、长度限制不省空间
PostgreSQL 18 文档《8.14 JSON 类型》https://www.postgresql.org/docs/18/datatype-json.htmljson 保留原始文本、jsonb 解析后存储并可建索引

核验说明

  • 字节数取自本机 pg_type（typlen 列）实查，变长类型的 -1 也如实记录。
  • 未做：没有对比 numeric 与 float8 在大规模聚合上的耗时，也没有测随机 UUID 主键造成的索引膨胀幅度。这两条是常见经验，这里只说明机制，不给数字。
#数据库
返回文章列表