PostgreSQL数据类型怎么选？

admin

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

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

本文分析了不同应用场景下可以使用的 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（推荐）	TEXT	CHAR(36)
优点	存储优化（16 字节）；校验 UUID 格式；索引优化。	实现简单，格式灵活。	固定长度。
缺点	无	更多存储（36 字节 + 1 字节）；索引低效；需要通过约束验证格式。	更多存储（36 字节 + 1 字节）；索引低效；需要通过约束验证格式。
百万行存储空间	16 MB	37 MB	37 MB

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

数据类型	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

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

数据类型	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 执行长度校验。

数据类型	SMALLINT	INTEGER	BIGINT
优点	占用空间最少	占用空间较少，支持较大范围数字	支持超大范围数字
缺点	支持的数字范围小	无	占用空间最大
百万行存储空间	2 MB	4 MB	8 MB

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

该文章在 2024/10/30 14:35:38 编辑过

关键字查询

类型

sql

postgre

数据

正在查询...