选择PostgreSQL数据类型的最佳实践

当创建存储和分析大量数据的应用程序，如时间序列、日志数据或事件存储应用时，开发一个良好且具备未来展望的数据模型可能是一个困难的任务。确定存储信息的最适当的数据类型取决于多种因素，包括浮点值所需的精度、值的内容（如文本）、可压缩性和查询速度。

在PostgreSQL中选择正确的数据类型可以显著影响您的数据库的性能和效率。在本文中，我们将探讨选择与PostgreSQL建议相符的数据类型的最佳实践。无论您是为新项目设计表格还是优化现有的数据库，这些见解都将帮助您做出明智的决策。

基本数据类型 

PostgreSQL提供了一系列适用于各种用例的基本数据类型。虽然它们很通用，但PostgreSQL的最佳实践建议考虑以下几点：

可空列 

不要回避使用可空列。PostgreSQL高效地存储NULL值，使它们几乎是免费的。这在具有许多可空列的中等和宽表布局中尤其有用。Timescale的自定义压缩也是如此。由于数据以列格式存储，因此当压缩时，空行值几乎是免费的。

布尔值 

布尔值是一种逻辑数据类型，有两个可能的值：TRUE或FALSE。它通常用于记录决策或状态。你可以选择布尔数据类型或使用整数（1或0）。这两个选项都是有效的，布尔值在压缩方面没有明显优势。

浮点值 

浮点数据类型表示实数，通常是基数为十的小数浮点数。它们存储各种信息，如百分比、温度或CPU使用率等测量值或统计值。PostgreSQL中有两组浮点数：float4（即 real）、float8（双精度）和numeric。对于浮点数，建议使用Float4和float8，如果你使用Timescale，它们是针对压缩优化的。如果精度已知，考虑将它们存储为整数以获得更好的优化。另一方面，numeric作为任意精度大小的数据类型，根本没有优化。不推荐使用numeric。

整数值 

整数数据类型，包括序列数据类型，非常适合事件计数和类似的用例。所有整数类型（int2, SmallInt, int4, Integer, int8, BigInt）都是推荐的数据类型。TimescaleDB在压缩这些类型时表现出色。对于整数也适用于所有序列数据类型（serial2, SmallSerial, serial4, Serial, serial8, BigSerial），因为它们是对应整数的神奇“别名”，在插入时自动创建序列以填充其值。也就是说，它们使用其对应的整数数据类型作为列数据类型。不过，从PostgreSQL版本10开始，PostgreSQL的最佳实践建议不使用它们，而推荐使用身份列。

时间戳、时间和日期值 

时间戳和时间及日期数据类型表示一个特定的时间点，有些提供更多的信息，有些提供较少的信息。这些数据类型都有带有和不带有时区信息的版本（日期除外）。处理与时间相关的数据时，考虑以下几点：

为了存储日期，你应该考虑使用带有必要时区的时间部分设置为午夜的timestamptz（例如2023-01-01T00:00:00+00）以防止在查询时进行类型转换产生的开销。

同样，你可以使用timestamptz只存储一个时间值。在这种情况下，你将时间部分编码为一个特定的日期（如1970-01-01T15:01:44+04），并将最终值转换回时间值。或者，你可以通过将时间编码为自午夜起的（纳）秒数或你能想到的任何其他编码，将该值存储为整数。

文本值 

文本值用于存储任意大小的文本值。这些值可以包括详细的描述、日志消息、度量名称或标签。可用的数据类型包括text, char(n)和varchar(n)。PostgreSQL的最佳实践建议不使用char(n)，因为它会将短于n的值填充到该大小并浪费存储空间。建议使用text。

对于文本值，简单点：

• 选择text而不是char(n)或varchar(n)以防止不必要的填充。 

• 通过在单独的表中存储引用来去重长文本值。 

• TimescaleDB对文本字段使用字典压缩。

字节数组（bytea）值 

字节数组（在PostgreSQL中由数据类型bytea表示）存储任意大的字节序列，这些字节可以表示从编码的机器状态到二进制数据包的任何内容。

字节数组较少见，但仍有其位置：

• TimescaleDB对bytea值应用基于lz4的压缩。 

• 对于重复的大bytea值，请考虑去重并将存储移到超级表之外。 

复杂和扩展数据类型 

处理复杂数据时，请遵循以下几点：

结构类型（JSON和JSONB）：使用JSONB有效地存储和查询复杂数据。考虑将大量数据提取到单独的表中以提高性能。 

• UUID：使用UUID时要小心，因为它们可能在BTree索引中创建位置问题。

• Ltree：使用ltree去重复现有数据。 

• Hstore：hstore提供键值对，但不如JSONB常用。 

• PostGIS数据：PostGIS数据类型与TimescaleDB很好地配合使用，但不会得到特定的优化。 

结论 

在PostgreSQL中选择正确的数据类型是数据库设计和优化的关键方面。虽然具体建议可能根据项目要求而异，但本指南为你提供了关于哪些数据类型要拥抱，哪些要小心，哪些要避免的有价值的见解。遵循这些最佳实践，可以确保你的PostgreSQL数据库高效、有效地运行。

数据类型推荐 

• 很好：可空列、布尔值（布尔值或整数）、Float4和Float8、整数值、文本值（text）、字节数组（bytea）、JSONB、Ltree、PostGIS数据。 

• 好，但要警惕某些事物：时间戳、时间和日期值（没有时区）、文本值（char(n)和varchar(n)）以及UUID。 

• 避免使用：时间戳、时间和日期值（没有时区）。 

• 未知（欢迎提供经验/反馈）：XML、Hstore、PostGIS数据优化。

​

​

作者：kelvin steve

更多内容请关注公号【云原生数据库】

squids.cn，云数据库RDS，迁移工具DBMotion，云备份DBTwin等数据库生态工具。