PostgreSQL数据类型怎么选?
|
admin
2024年10月30日 7:57
本文热度 425
|
在数据库设计时,表字段的类型选择至关重要。它不仅影响数据库的存储效率和性能优化,还影响数据的完整性和扩展性。
本文分析了不同应用场景下可以使用的 PostgreSQL 数据类型,以及它们的存储需求和优缺点。
布尔值
数据类型 | BOOLEAN(推荐) | SMALLINT | BIT(不推荐) | CHAR(1) |
---|
优点 | 存储需求最小(1 字节);语义清晰;支持输入:true, yes, on, 1, false, no, off, 0。 | 支持算术运算;可以存储真或假之外的数值。 | 无 | 可以存储任何字符,输入灵活。 |
缺点 | 只能存储真或者假。 | 语义不够清晰;需要通过约束实现布尔逻辑;需要更多存储(2 字节)。 | 需要更多存储(6 字节);只能存储 1/0;语义不够清晰;不易查询。 | 需要通过约束实现布尔逻辑,需要更多存储(2 字节)。 |
百万行存储空间 | 1 MB | 2 MB | 6 MB | 2 MB |
UUID
数据类型 | UUID(推荐) | TEXT | CHAR(36) |
---|
优点 | 存储优化(16 字节);校验 UUID 格式;索引优化。 | 实现简单,格式灵活。 | 固定长度。 |
缺点 | 无 | 更多存储(36 字节 + 1 字节);索引低效;需要通过约束验证格式。 | 更多存储(36 字节 + 1 字节);索引低效;需要通过约束验证格式。 |
百万行存储空间 | 16 MB | 37 MB | 37 MB |
备注:如果需要使用 UUID 作为主键,推荐使用 UUIDv7 这种基于时间排序的算法。随机生成的 UUID 可能导致 B-树分裂问题。
MD5
数据类型 | UUID(推荐) | BYTEA | TEXT | CHAR(32) |
---|
优点 | 存储优化(16 字节);索引优化。 | 存储优化(16 字节 + 4 字节);索引优化。 | 实现简单,格式灵活。 | 固定长度。 |
缺点 | MD5 显示成 UUID 格式。 | 二进制数据不方便处理。 | 更多存储(32 字节 + 4 字节);索引低效。 | 更多存储(32 字节 + 4 字节);索引低效。 |
百万行存储空间 | 16 MB | 20 MB | 36 MB | 36 MB |
MD5 转换成二进制类型的方法如下:
SELECT DECODE(MD5('abc'), 'hex');
日期时间
数据类型 | DATE(推荐) | TIME | TIMESTAMP | TIMESTAMPTZ(推荐) |
---|
优点 | 高效存储日期;语义清晰 | 存储于日前无关的时间信息 | 同时存储日期和时间 | 存储包含时区的日期和时间,支持全球化;兼容夏令时 |
缺点 | 不包含时间信息 | 不包含日期信息 | 没有时区信息 | 无 |
百万行存储空间 | 4 MB | 8 MB | 8 MB | 8 MB |
二进制数据
数据类型 | BYTEA(推荐) | TEXT |
---|
优点 | 高效的二进制存储和处理 | 应用程序处理简单 |
缺点 | 应用程序需要编码/解码 | 存储效率低 |
百万行存储空间 | 2 MB - 2.1 PB | 1 MB - 1.1 PB |
备注:对于文档、图像、音频、视频等二进制数据,不建议使用数据库存储,可以使用文件系统存储这些内容,并且在数据库中记录文件的访问地址。
货币数字
数据类型 | MONEY(不推荐) | NUMERIC(15,2)(推荐) | BIGINT(推荐) | FLOAT(不推荐) |
---|
优点 | 内置货币符号;方便存储财务数据;高效的存储和处理 | 高精度存储;可以灵活设置存储精度 | 可以将小数存储为整数;计算性能好 | 计算性能最好 |
缺点 | 只能支持一种货币符号;只能支持小数点后两位 | 占用更多存储;计算性能不如浮点数 | 应用程序需要进行小数转换,例如乘以 100 将小数点后的分转换为整数 | 非精确数字;计算时存在精度损失 |
百万行存储空间 | 8 MB | 11 MB | 8 MB | 4 MB/8 MB |
枚举值
数据类型 | ENUM | TEXT | SMALLINT(推荐) | SMALLINT查找表(推荐) |
---|
优点 | 提供数据库级别校验;存储高效;可读性高 | 使用灵活,方便增加新的枚举值 | 存储高效;方便增加新的枚举值 | 存储高效;方便增加新的枚举值;通过查找表可以获取更多信息 |
缺点 | 删除某个枚举时比较复杂;对于动态枚举值不够灵活 | 没有数据校验,可能导致数据不一致;可能占用更多存储 | 没有数据校验;含义不明确,应用程序需要解释数据含义 | 需要关联查询获取枚举值含义,增加了复杂度 |
百万行存储空间 | 4 MB | >=2 MB | 2 MB | 2 MB |
文本
数据类型 | TEXT(推荐) | VARCHAR(N) | CHAR(N) (不推荐) |
---|
优点 | 几乎没有长度限制;灵活易用 | 限制了最大长度 | 固定长度,占用固定大小 |
缺点 | 需要通过检查约束限制长度 | 需要提前定义最大长度;超长时出现错误 | 使用空格填充,可能浪费空间;不适用于变长字符串 |
百万行存储空间 | 2 MB - 1.1 PB | 2 MB - 1.1 PB | 2 MB - 1.1 PB |
PostgreSQL 数据库中这三种字符串类型没有明显的性能差异,只是 CHAR 类型可能浪费一些存储空间,而指定字段的最大长度则需要消耗一些 CPU 执行长度校验。
数字
数据类型 | NUMERIC | FLOAT |
---|
优点 | 超大范围精确数字;适合金融财务数据 | 占用更少存储;计算速度更快,适合科学计算 |
缺点 | 占用更多存储;计算更慢 | 不够精确,可能存在舍入误差 |
百万行存储空间 | 5 MB - 1 TB | 4 MB/8 MB |
整数
数据类型 | SMALLINT | INTEGER | BIGINT |
---|
优点 | 占用空间最少 | 占用空间较少,支持较大范围数字 | 支持超大范围数字 |
缺点 | 支持的数字范围小 | 无 | 占用空间最大 |
百万行存储空间 | 2 MB | 4 MB | 8 MB |
SAMLLINT 支持的数字范围从 -32768 到 32767,INTEGER 支持的数字范围从 -2147483648 到 2147483647,BIGINT 支持的数字范围从 -9223372036854775808 到 9223372036854775807。
JSON
数据类型 | JSON | JSONB(推荐) |
---|
优点 | 存储原始文本,保留空白符、顺序、重复键 | 二进制存储,优化了查询性能;支持 GIN 索引 |
缺点 | 读取速度更慢;不支持高效索引 | 写入时需要更多解析操作;可能需要更多元数据存储 |
百万行存储空间 | 2 MB - 1.1 PB | 2 MB - 1.1 PB |
数组
数据类型 | ARRAY | JSONB ARRAY |
---|
优点 | 单个字段存储多个值,优化特定应用 | 灵活存储多个值;支持索引 |
缺点 | 查询和索引复杂;可能占用更多存储 | 需要额外处理 JSON;可能占用更多存储 |
百万行存储空间 | 12 MB - 1.1 PB | 8 MB - 1.1 PB |
该文章在 2024/10/30 14:35:38 编辑过