LOGO OA教程 ERP教程 模切知识交流 PMS教程 CRM教程 开发文档 其他文档  
 
网站管理员

PostgreSQL数据类型怎么选?

admin
2024年10月30日 7:57 本文热度 176

在数据库设计时,表字段的类型选择至关重要。它不仅影响数据库的存储效率和性能优化,还影响数据的完整性和扩展性。

本文分析了不同应用场景下可以使用的 PostgreSQL 数据类型,以及它们的存储需求和优缺点。

布尔值

数据类型BOOLEAN(推荐)SMALLINTBIT(不推荐)CHAR(1)
优点存储需求最小(1 字节);语义清晰;支持输入:true, yes, on, 1, false, no, off, 0。支持算术运算;可以存储真或假之外的数值。可以存储任何字符,输入灵活。
缺点只能存储真或者假。语义不够清晰;需要通过约束实现布尔逻辑;需要更多存储(2 字节)。需要更多存储(6 字节);只能存储 1/0;语义不够清晰;不易查询。需要通过约束实现布尔逻辑,需要更多存储(2 字节)。
百万行存储空间1 MB2 MB6 MB2 MB

UUID

数据类型UUID(推荐)TEXTCHAR(36)
优点存储优化(16 字节);校验 UUID 格式;索引优化。实现简单,格式灵活。固定长度。
缺点更多存储(36 字节 + 1 字节);索引低效;需要通过约束验证格式。更多存储(36 字节 + 1 字节);索引低效;需要通过约束验证格式。
百万行存储空间16 MB37 MB37 MB

备注:如果需要使用 UUID 作为主键,推荐使用 UUIDv7 这种基于时间排序的算法。随机生成的 UUID 可能导致 B-树分裂问题。

MD5

数据类型UUID(推荐)BYTEATEXTCHAR(32)
优点存储优化(16 字节);索引优化。存储优化(16 字节 + 4 字节);索引优化。实现简单,格式灵活。固定长度。
缺点MD5 显示成 UUID 格式。二进制数据不方便处理。更多存储(32 字节 + 4 字节);索引低效。更多存储(32 字节 + 4 字节);索引低效。
百万行存储空间16 MB20 MB36 MB36 MB

MD5 转换成二进制类型的方法如下:

SELECT DECODE(MD5('abc'), 'hex');

日期时间

数据类型DATE(推荐)TIMETIMESTAMPTIMESTAMPTZ(推荐)
优点高效存储日期;语义清晰存储于日前无关的时间信息同时存储日期和时间存储包含时区的日期和时间,支持全球化;兼容夏令时
缺点不包含时间信息不包含日期信息没有时区信息
百万行存储空间4 MB8 MB8 MB8 MB

二进制数据

数据类型BYTEA(推荐)TEXT
优点高效的二进制存储和处理应用程序处理简单
缺点应用程序需要编码/解码存储效率低
百万行存储空间2 MB - 2.1 PB1 MB - 1.1 PB

备注:对于文档、图像、音频、视频等二进制数据,不建议使用数据库存储,可以使用文件系统存储这些内容,并且在数据库中记录文件的访问地址。

货币数字

数据类型MONEY(不推荐)NUMERIC(15,2)(推荐)BIGINT(推荐)FLOAT(不推荐)
优点内置货币符号;方便存储财务数据;高效的存储和处理高精度存储;可以灵活设置存储精度可以将小数存储为整数;计算性能好计算性能最好
缺点只能支持一种货币符号;只能支持小数点后两位占用更多存储;计算性能不如浮点数应用程序需要进行小数转换,例如乘以 100 将小数点后的分转换为整数非精确数字;计算时存在精度损失
百万行存储空间8 MB11 MB8 MB4 MB/8 MB

枚举值

数据类型ENUMTEXTSMALLINT(推荐)SMALLINT查找表(推荐)
优点提供数据库级别校验;存储高效;可读性高使用灵活,方便增加新的枚举值存储高效;方便增加新的枚举值存储高效;方便增加新的枚举值;通过查找表可以获取更多信息
缺点删除某个枚举时比较复杂;对于动态枚举值不够灵活没有数据校验,可能导致数据不一致;可能占用更多存储没有数据校验;含义不明确,应用程序需要解释数据含义需要关联查询获取枚举值含义,增加了复杂度
百万行存储空间4 MB>=2 MB2 MB2 MB

文本

数据类型TEXT(推荐)VARCHAR(N)CHAR(N) (不推荐)
优点几乎没有长度限制;灵活易用限制了最大长度固定长度,占用固定大小
缺点需要通过检查约束限制长度需要提前定义最大长度;超长时出现错误使用空格填充,可能浪费空间;不适用于变长字符串
百万行存储空间2 MB - 1.1 PB2 MB - 1.1 PB2 MB - 1.1 PB

PostgreSQL 数据库中这三种字符串类型没有明显的性能差异,只是 CHAR 类型可能浪费一些存储空间,而指定字段的最大长度则需要消耗一些 CPU 执行长度校验。

数字

数据类型NUMERICFLOAT
优点超大范围精确数字;适合金融财务数据占用更少存储;计算速度更快,适合科学计算
缺点占用更多存储;计算更慢不够精确,可能存在舍入误差
百万行存储空间5 MB - 1 TB4 MB/8 MB

整数

数据类型SMALLINTINTEGERBIGINT
优点占用空间最少占用空间较少,支持较大范围数字支持超大范围数字
缺点支持的数字范围小占用空间最大
百万行存储空间2 MB4 MB8 MB

SAMLLINT 支持的数字范围从 -32768 到 32767,INTEGER 支持的数字范围从 -2147483648 到 2147483647,BIGINT 支持的数字范围从 -9223372036854775808 到 9223372036854775807。

JSON

数据类型JSONJSONB(推荐)
优点存储原始文本,保留空白符、顺序、重复键二进制存储,优化了查询性能;支持 GIN 索引
缺点读取速度更慢;不支持高效索引写入时需要更多解析操作;可能需要更多元数据存储
百万行存储空间2 MB - 1.1 PB2 MB - 1.1 PB

数组

数据类型ARRAYJSONB ARRAY
优点单个字段存储多个值,优化特定应用灵活存储多个值;支持索引
缺点查询和索引复杂;可能占用更多存储需要额外处理 JSON;可能占用更多存储
百万行存储空间12 MB - 1.1 PB8 MB - 1.1 PB



该文章在 2024/10/30 14:35:38 编辑过
关键字查询
相关文章
正在查询...
点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。
点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理,结合码头的业务特点,围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体,是物流码头及其他港口类企业的高效ERP管理信息系统。
点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。
点晴免费OA是一款软件和通用服务都免费,不限功能、不限时间、不限用户的免费OA协同办公管理系统。
Copyright 2010-2024 ClickSun All Rights Reserved