postgresql - 在 Postgres 中存储固定长度小数组的最佳数据模型(使用 SqlAlchemy)

Question

我想存储一组每小时设定点。 24 个 float (0. 到 1.)，将一天中的小时数映射到强度值。

我可以:

1. 有 24 个名为 _0 到 _23 的列。(如何在 Sqlalchemy 中扩展 db.Model 以使用数字作为列名？)
2. 有一个值表(id, value, hour_of_the_day)。
3. 使用postgres array type . (但似乎我不能拥有固定长度的数组，我需要一些应用程序逻辑来获取/设置值。)

大部分值将一起查询并缩放(以便 0 到 1 的范围映射)自定义范围。

哪种模型更可取？

Answer 1

这在很大程度上取决于用例和要求。

1.

24 个单独的 float 列，可能为空(您的第一个选项)是存储大小(因此速度)的最佳选择。每个 float 8 个字节(默认为 float8 a.k.a. double precision)或每个 real 4 个字节(float4 ) 并且没有开销。由于恰好有 24 个给定列，因此您永远不会遇到设计问题，就像使用可变数字时那样。

SQL 中的合法标识符不能以数字开头。你必须总是双引号。容易出错且令人困惑。使用字符前缀，例如 h0 .. h23。

2.

一对多表，对主表的 id 有 FK 约束。你会制作 (id int, hour_of_the_day int, value float8)，而不是 (id, value, hour_of_the_day)，不会浪费对齐填充的空间。添加 CHECK 约束以仅允许 hour_of_the_day 的值为 0 - 23。

与 1 相比，这增加了存储大小。:每个单独的行占用大约 44 个字节。每行 28 字节的开销 + 16 字节的数据。您至少在 (id, hour_of_the_day) 上添加一个 PK 约束，它是使用 btree 索引(与表类似的页面和行结构)实现的。这每行又增加了 36 个字节。所以我们最终每个值至少有 80 个字节(加上每个数据页和表的开销)。大约 10 倍的存储大小 1.。

3.

数组？不是。存储比 1.(24 字节的数组开销)更昂贵，处理最不方便，强制完整性(最大/精确长度)并不那么简单。

• Does it make sense to store a couple of Boolean values as array?

更多优点和缺点

使用 2. 时，您需要连接两个表来读取数据，这会更加昂贵。但也有各种优点。如果您经常需要读取或写入没有列 h0 - h23 的主表，那么这样做会更便宜一些。更新单个值可能更便宜，因为主表保持不变。 OTOH，一次插入或更新相同 id 的所有 24 个值通常更昂贵(1 个新行版本与 24 个新行版本)。删除单个值对 2. 来说成本更低。

The values will mostly be queried together ...

1. 便宜多了。

... and scaled

2 可能会更简单。:将相同的操作应用于一列而不是 24 个不同的列。

等等。正如我所说，这在很大程度上取决于用例和要求。

相关:

• PostgreSQL: performance impact of extra columns
• Making sense of Postgres row sizes
• Is there any difference between integer and bit(n) data types for a bitmask?