1. 为什么数据类型值得花半小时认真选PG数据类型解析听起来像是教科书第一章的扫盲内容但恰恰是这个最基础的话题我见过太多团队在上面栽跟头。建表的时候赶进度随手选了顺手的数据类型等业务跑了三个月数据量上来之后才发现类型选错带来的代价远超预期——有的是存储翻了好几倍有的是精度悄悄丢了有的是SQL怎么优化都走不上索引排查到最后问题根源居然只是当初一个不起眼的类型声明。拿我自己的经验来说数据类型的选型本质上是在做一道权衡题它在决定数据怎么存储、怎么比较、怎么计算、怎么索引每一项都直接影响查询性能和运维成本。PG的数据类型体系相比其他数据库要丰富得多光是数值类就有十几种形态时间类也有精度和时区之分还有jsonb、数组、范围类型等高级货。类型选对了后续的分区、归档、统计查询都是顺水推舟选错了轻则多花几倍的磁盘重则业务逻辑出bug甚至等到数据量大了才意识到要改表结构那才叫骑虎难下。这篇文章想做的是把PG里最常用、也最容易踩坑的类型从原理到实操梳理一遍每个类型我都尽量讲清楚它在磁盘上长什么样、适合什么场景、有什么坑以及我实际测试和项目中验证过的建议。适合刚接触PG的开发者也适合那些已经用了好几年PG、但很少回头审视类型设计的老手。看完再去建表你会下意识多花几秒钟想一想要不要换个更优的类型这短短几秒钟往往能省下后面无数的返工。2. 数值类型从整数到精确小数区别不只是字节数2.1 整数三兄弟别拿integer硬扛全部PG里的整数类型主要有smallint、integer和bigint三种分别占2字节、4字节和8字节。很多人以为这只是取值范围的区别实际使用中真正重要的是理解每个类型的边界在哪里以及类型溢出会带来什么后果。smallint的取值区间是-32768到32767integer是-2147483648到2147483647bigint的上限大约是922亿亿。建表时如果拿不准很多人习惯直接上bigint图省心但bigint占用的空间是integer的两倍。你别小看这4个字节的差距对一张几亿行的表来说就差了差不多400MB的存储再加上索引翻倍都不止。我在实际项目里见过一张订单表订单号字段用的bigint其实业务永远不可能超过21亿硬生生多占了几个GB的磁盘和内存。我的习惯是能预估的就按业务上限来预估不了的才用bigint兜底。这里有一个很容易被忽略的坑PG的integer运算如果结果溢出会直接报错。比如一个integer字段乘以10的结果超过21亿PG会抛出一个“integer out of range”的异常。这个行为其实比MySQL那种静默截断要安全得多至少不会产生错误数据但如果你在存储过程或触发器里做了类似的计算一定要预先想到溢出的可能必要时先转成bigint再运算。我在某个统计项目里就吃过这个亏月度汇总乘个系数就炸了查了半天才发现是中间结果溢出。2.2 numeric和float精度敏感的场合绝不妥协浮点类型float4和float8别名real和double precision在PG里遵循IEEE 754标准它们的优势是运算速度快能够表示非常大和非常小的数值但代价是精度不精确。这个“不精确”不是随机的误差而是二进制无法精确表达很多十进制小数比如0.1在二进制里就是无限循环。所以涉及金额、税价、库存数量这类需要精确计算的场景我一律不用浮点而是选numeric。numeric是个定点“任意精度”类型它可以精确存储小数同时支持指定精度和小数位数语法是numeric(p, s)p是总位数s是小数位数。比如numeric(10, 2)表示最多8位整数加2位小数范围是-99999999.99到99999999.99。很多人会问那到底选多大合适我的建议是金额字段如果涉及分一般numeric(12, 2)起步应对亿级金额没问题如果之后可能扩展到千亿直接上numeric(18, 2)一步到位。但numeric也不是没有代价它的运算比浮点慢而且存储不是定长的每一组数字都有额外的头信息开销。在建表时如果只是存用户评分这类精度要求不高的小数用float4甚至integer比如满分10分直接存10、8、6都比numeric划算。选型的核心原则是精度敏感用numeric性能敏感且精度无所谓的用float两者不需要也不可能同时完美。关于序列类型serial、bigserial本质是integer/bigint加上一个默认的序列生成器。我在新项目里更推荐直接用generated always as identity这是标准的SQL语法比serial的约束更严格、更安全运维上也能少一些麻烦。3. 字符类型char、varchar、text的选择恐惧症3.1 三兄弟的真实差别PG的字符类型有三个character(n)也就是char(n)、character varying(n)varchar(n)和text。很多从MySQL转过来的朋友会对text存在偏见觉得它“性能差、不能建索引”这个认知在PG里是完全错误的。PG的text类型没有长度限制存储机制和varchar几乎一模一样都是变长的在磁盘上的表示方式相同索引性能也没有区别。你完全可以把text当作一个“不带长度校验的varchar”来用。而varchar(n)与text的真正区别仅仅在于varchar(n)会校验长度超了会报错。char(n)则是个有点闹心的类型它会把不足n位的字符串用空格补齐到n位取出来的值如果不做处理还带着尾部空格跟其他字符串比较时会出各种莫名其妙的坑。我几乎不在业务表里用char(n)它唯一的用处大概是存储固定长度的编号比如身份证号、银行卡号、流水号但这类数据在现代PG里用text或varchar也完全没毛病。选择建议很简单如果字符串没有明确的长度上限直接text如果有上限而且业务上希望数据库强制校验用varchar(n)char(n)能不碰就不碰。某开发者的项目里用户昵称字段一开始用varchar(20)后来运营搞活动昵称最长可以到30个字他只能做一次耗时数亿行的表重建。如果当初用text这种改动就是零成本无非在应用层加个长度校验。3.2 隐式转换和编码教训往往藏在看不见的地方字符类型真正容易踩的坑不在存储而在比较和转换。PG里如果varchar列和text列直接比较PG会尝试做隐式转换一般情况下可以正常走索引。但如果你在varchar列上用了函数比如where lower(name) abc这个索引就废了需要改成表达式索引。类似的还有字段类型和参数类型不一致导致的类型转换比如varchar列被传入了integer参数PG会尝试把参数转成varchar这时候索引可能还能用但转换规则和预期不一致时性能会莫名变差。另一个值得说的是编码问题。PG的字符集是在创建数据库或表时确定的不同字符集之间的连接、拼接、排序都受collation排序规则影响尤其是在做中文排序的时候。默认的collation可能不是你想要的中文拼音顺序这个要在建库时就明确。我在项目里曾经因为排序规则不一致导致两个表的关联查询走了不同的排序语义结果统计出来的排名对不上排查了很久才发现是两个表用了不同的collation。text类型还有一个隐含好处是可以存放任意长度的JSON字符串这就引出了另一个话题当你有JSON数据时到底该存成text还是直接用PG的原生JSON类型很多人习惯把一个对象stringify之后塞进text这在PG里其实属于浪费了它强大的半结构化能力下一节就专门聊这个。4. 时间类型时区永远是最大的坑4.1 timestamp和timestamptz别再傻傻分不清PG的时间类型里最常用的是timestamp和timestamptz。两者的名字看起来只是一个“带时区”一个“不带时区”但本质区别比想象中大得多。timestamp不带时区只存储一个墙面时钟时间本身不携带任何时区信息而timestamptz在存储时会把输入时间按会话的时区转换成UTC再存起来查询输出时再按会话时区转换回本地时间。这意味着什么假设你的业务系统部署在国内服务器时区是东八区客户端连上来的会话默认时区也是东八区那么同一时刻存进timestamp和timestamptz查出来的本地时间是一样的。但如果系统的客户端分布在不同时区或者服务器换机房、改了系统时区差别就会立刻显现timestamp字段的值不会跟着时区变它就是一个“死的”墙上时间timestamptz则会根据当前会话时区重新换算。因此我的原则很简单只要你的数据可能被不同时区的客户端读取或者将来系统有出海、多地部署的可能一律用timestamptz。PG文档里也反复强调这一点实际项目里因为时区问题导致统计错乱、报表时间差8小时之类的故障几乎都是因为用了timestamp而没考虑时区。如果当前只做国内单时区的项目用timestamptz也不会多花什么成本反而给未来留了余地。还有一个容易被忽视的细节timestamptz的存储是UTC但索引排序是基于UTC的也就是说如果你按“本地时间”做分区或范围查询必须时刻记得把查询条件的时区先转成UTC再比较否则会出现看似正确但结果少了几个小时边界的问题。4.2 interval和date用到精妙处很省心除了时间点PG还有interval类型表示一段时间的长度比如interval 3 days、interval 2 hours。计算业务里的“最近30天”“有效期内”这些需求用interval直接在SQL里做加减法比在应用层算好时间戳再传进来要优雅得多也能避免时区转换的各种边界问题。比如查询“创建时间在过去24小时内的记录”直接写成created_at now() - interval 24 hours简洁明了。interval的精度还可以精细化interval 1 day 2 hours 30 minutes完全合法。但是需要注意interval在加减运算中如果和timestamp相减结果就是interval这个类型之间互相转换偶尔会因为精度的表示方式产生微小误差极小概率让你在做时间差排序时觉得结果不对劲实际上是因为interval内部是用月、日、微秒三段式存储的。date类型存的是年月日没有时间部分适合生日、节假日、账期这类时间粒度到天的场景。date与timestamp的转换也很方便cast(x as date)就能截断时间部分。在按天做统计报表时直接用date类型做group by比用timestamp再to_char要更有效率。但要注意date类型本身不携带时区如果业务里有全球多地跨零点统计的需求还是需要谨慎处理归属日期的问题。5. JSON与数组半结构化数据的正确打开方式5.1 jsonb带来的不仅是存储空间的改变PG的json类型有两种json和jsonb。json是原样存文本的存进去什么样查出来什么样不做解析也不去重jsonb则会把JSON数据解析成一种二进制格式来存储键会被排序重复的键只保留最后一个值查询时可以直接用操作符过滤、提取、修改内部字段并且支持GIN索引。几乎可以无脑地说凡是能用jsonb的场景就不要用json。json的保留原格式优势在绝大多数业务面前不值一提jsonb的查询能力、索引支持、更新灵活性都远胜于它。我唯一想到用json的场景是把某些需要精确保存原始字节的日志原样归档且根本不去数据库里查询否则jsonb都是更优解。用一个例子说明jsonb的实操心法。假设一张用户表中有一个attributes jsonb字段存放用户的偏好设置。查询”喜欢运动且等级大于5的用户“可以写成SELECT * FROM users WHERE attributes-interest sports AND (attributes-level)::int 5;-返回JSON对象或JSON数组元素结果是JSON类型-返回文本类型#和#是按路径提取比如attributes # {address, city}。如果这个查询频繁执行可以创建GIN索引CREATE INDEX idx_users_attr ON users USING gin (attributes jsonb_path_ops);这对含数组、嵌套对象的灵活查询很管用。但要是想查询(attributes-level)::int 5这种表达式GIN索引是帮不上忙的只能改用表达式索引CREATE INDEX idx_users_level ON users ((attributes-level)::int);注意一个重要的行为差异json和jsonb在字段提取后返回的类型不同json返回的是json类型jsonb返回的可能是jsonb或文本。操作符混用可能导致类型转换错误比如在jsonb字段上用-得到文本后直接做数字比较必须显式转成int或numeric否则PG会报“operator does not exist”的错误。5.2 数组类型利用好能让查询变简洁PG支持真正的数组类型比如integer[]、text[]、date[]这让在单行里存一组值成为可能。数组类型和JSONB有些重叠的场景但更适合纯标量元素的集合查询语义也更明确。常见用法比如文章表的标签字段tags text[]查询包含“数据库”这个标签的文章SELECT * FROM articles WHERE 数据库 ANY(tags);给数组字段建GIN索引后这类查询可以走索引。还有一个很实用的函数是unnest它把数组展开成多行用于统计和聚合。比如统计所有文章标签的出现次数SELECT tag, count(*) FROM articles, unnest(tags) AS tag GROUP BY tag ORDER BY count(*) DESC;数组类型要注意的是长度和越界问题如果插入的元素个数超过数组定义时的最大维度会报错。还有一个容易忽略的点是数组元素类型隐式转换的陷阱比如插入字符串到integer[]数组PG会尝试把字符串转成integer转不进去就报错。正确的做法是提前在应用层把类型处理好别依赖数据库把字符串转成其他类型。数组和JSONB怎么选我的判断标准是数据形态是固定模式的集合元素比如标签、选项、白名单用数组如果是嵌套的、结构不固定的对象用jsonb。数组在等值查询、包含查询上写起来更直接JSONB在提取和修改内部字段时更强。二者可以混合使用比如jsonb里存数组在索引和查询上会有联合操作但那是进阶玩法新手先把基础操作符用熟就够了。6. 布尔、枚举与网络类型小而美的实用派6.1 布尔别用整数更别用字符串PG的boolean类型只有true、false和NULL三种值。很多从其他数据库转过来的人习惯用tinyint的0和1或者varchar的“Y”和“N“来表达布尔语义这在PG里完全没必要而且隐患不少。0和1看起来直观但一旦你需要在SQL里做语义判断比如where is_active0和1可没有原生boolean来得自然如果用了字符串那更是给自己制造脏数据的入口一个不小心就会混进”y“”yes“”1“等各种变体。PG里boolean类型可以直接用于条件判断比如where is_active取反用where not is_active布尔值还能参与聚合用bool_and或bool_or。一个有用的技巧是统计”满足某个布尔条件的记录数“可以直接用count(*) filter (where is_active)不需要sum(case when ...)这么绕。还有一个容易踩的坑boolean列建索引在PG里是有可能的但对真实业务的查询帮助不大因为布尔值的区分度太低。真正要优化这类查询时改成部分索引更有效比如只对false的记录建索引CREATE INDEX idx_users_inactive ON users (id) WHERE is_active false;这种部分索引体积小、写入开销低是处理低区分度布尔字段的好办法。6.2 枚举、网络类型和bytea什么时候用PG的枚举类型可以自定义一组有序的合法取值比如create type mood as enum (sad, ok, happy)。枚举在写入时自带校验避免脏数据排序顺序按定义的顺序而非字母顺序。但它有两大硬伤一是修改枚举需要执行alter type ... add value而且在PG 12之前这个操作不能在事务块里做运维上会麻烦一些二是枚举的底层存储实际是oid编号如果你在应用层硬编码了枚举的次序未来加值时很容易造成逻辑错乱。我的建议是如果枚举值的集合极小且高度稳定比如性别、状态机的顶层状态用枚举很舒服只要感觉以后会经常增删值直接改用varchar加check约束更省心。网络类型cidr、inet、macaddr是PG的特色存IP和MAC地址时会做严格校验和二进制压缩比存字符串省空间也能直接用、这些运算符做子网判断。如果你的业务有IP归属、网段筛选的需求用inet是很实惠的选择但日常业务用的概率不大。bytea是字节流类型适合存小文件、二进制内容比如图片缩略图、加密签名等。建议不要把大文件直接塞进数据库PG单行默认有1.6TB的理论上限但大对象会让表的膨胀和备份成本剧增更合理的做法是文件放对象存储库里只存路径或哈希值。bytea比较适合存的是一些小块二进制比如AES加密后的密钥、几KB的证书文件之类这也要控制好单行大小。7. 常见问题与排查实录7.1 这些坑我踩过你也可能踩把这几年在PG类型上遇到的问题整理成一张速查表方便你对照排查现象根因解决方案查询很慢explain显示没走索引字段类型和参数类型不一致PG做了隐式转换调整参数类型或建表达式索引金额字段求和结果偶尔差几分用了float/double precision存钱改numeric重建相关统计按日期范围查询结果少了一部分timestamp存的是不带时区的本地时间跨时区坐到错误边界改用timestamptzJSON字段查出来键的顺序变了用的是jsonb存储时会按键排序接受该语义或改用json保序插入JSON后数值被转成字符串用-取出的值一定是text手动cast成目标类型表体积涨得飞快大量长字符串存在varchar(n)但n设得太宽改成text并应用层校验长度序列号出现空洞中断序列本身不保证连续事务回滚也会消耗序号业务如果要求连续改用其他方案布尔字段大量重复值导致索引效率低低区分度索引用部分索引7.2 排查思路和常用SQL遇到类型引起的疑难杂症我最先做的事情是用下面的SQL检查字段类型SELECT table_name, column_name, data_type, udt_name FROM information_schema.columns WHERE table_name 你的表名;再看查询计划重点看有没有::text、::numeric这类隐式转换出现在过滤条件里。如果explain里出现Filter: ((xxx)::numeric 5)那基本就是类型转换导致索引失效的信号。还有个非常实用的函数pg_typeof()可以直接查表达式返回的数据类型做调试时特别好用SELECT pg_typeof(attributes-level) FROM users LIMIT 1;返回的结果一眼就能看出取出来的是text还是别的类型省去频繁试错的时间。实际排查时我发现一个很常见的现象很多报错的本质不是业务逻辑错了而是应用层传入的参数类型和数据库字段类型对不上PG大多数时候能自动转换但也有它转不了、或者能转但转的方式和你想的不一样的情况。比如一个text字段传入了NULL和空字符串的混合体还有人习惯在ORM模型里把时间字段定义为字符串最终把字符串传给timestamptz参数PG转换规则五花八门很难猜。遇到类似问题建议先看看ORM生成的SQL到底把参数转成了什么类型然后去PG的客户端里手动执行同样类型参数的SQL复现后再针对性地调整。建立表的时候多花五分钟想清楚每个字段的类型能为之后的两年省下难以估量的维护时间。我见过太多项目被慢查询、诡异排序、数据精度偏差折磨到最后都定位到数据类型上。我自己现在建表的习惯是整数字段先想会不会超21亿字符字段直接text时间一律timestamptz金额统一numeric(18,2)JSON无脑jsonb布尔直接boolean。这套默认值不一定适合所有项目但能帮我快速把低风险的决策先定下来然后把精力放在真正需要思考的业务关键字段上。最后再多说一句PG的数据类型体系是它相对其他数据库的一个真正的差异化优势花时间把这些类型吃透建出来的表、写的SQL、调的性能都会是另外一个质量水平。建议你新建表或重构表时专门找个时间做一次数据字典review把这张表里每个字段的“类型选择理由”写出来不需要多正式哪怕只是批注一句“这里用integer是因为上限不超过XX”都能让后续维护的人由衷感谢你。
