取消嵌套与仅包含表中所需的每一行

Question

我可以选择如何创建数据表，并且想知道哪种方法性能更高。

1. 为每个数据点制作一个包含一行的表格，
2. 制作一个包含数组列的表格，该表格将允许重复的内容被取消嵌套

也就是说，如果我有数据：

day	val1	val2
Mon	7	11
Tue	7	11
Wed	8	9
Thu	1	4

是否最好如图所示输入数据，或者改为：

day	val1	val2
(Mon,Tue)	7	11
(Wed)	8	9
(Thu)	1	4

然后在需要时使用 unnest() 将它们分解为唯一的行？

假设我们讨论的是现实中的大数据 - 每天生成 10 万行数据 x 20 列。使用数组会大大减少表中的行数，但我担心取消嵌套的性能会低于仅拥有所有行的性能。

Answer 1

“每天生成 10 万行数据 x 20 列”
还有：
“数组会大大减少行数” - 所以有很多重复项。

基于此，我建议第三种选择：
创建一个包含 20 列数据的表，并向其中添加代理 bigint PK。要强制所有 20 列的唯一性，请添加生成的哈希并使其唯一。我建议为此目的使用一个自定义函数：

-- hash function
CREATE OR REPLACE FUNCTION public.f_uniq_hash20(col1 text, col2 text, ... , col20 text)
  RETURNS uuid
  LANGUAGE sql IMMUTABLE COST 30 PARALLEL SAFE AS 
'SELECT md5(textin(record_out(($1,$2, ... ,$20))))::uuid';

-- data table
CREATE TABLE data (
  data_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, col1 text
, col2 text
, ...
, col20 text
, uniq_hash uuid GENERATED ALWAYS AS (public.f_uniq_hash20(col1, col2, ... , col20)) STORED
, CONSTRAINT data_uniq_hash_uni UNIQUE (uniq_hash)   
);

-- reference data_id in next table
CREATE TABLE day_data (
  day text 
, data_id bigint REFERENCES data ON UPDATE CASCADE -- FK to enforce referential integrity
, PRIMARY KEY (day, data_id)      -- must be unique?
);

db<>fiddle 此处

只有 text 列，该函数实际上是IMMUTABLE（我们需要它！）。对于其他数据类型（例如timestamptz）则不然。

这个密切相关的答案中的深入解释：

• 为什么我的 UNIQUE 约束没有触发？

你可以直接使用uniq_hash作为PK，但对于许多参考，bigint更有效（8字节与16字节）。

关于生成的列：

• PostgreSQL 中的计算/计算/虚拟/派生列

插入新数据时避免重复的基本技术：

INSERT INTO data (col1, col2) VALUES
('foo', 'bam')
ON CONFLICT DO NOTHING
RETURNING *;

如果可以并发写入，请参阅：

• 如何在 PostgreSQL 中使用 RETURNING 和 ON CONFLICT ？

Answer 2

我相信为每个数据点制作一个包含一行的表格将是我的选择。由于大量数据的取消嵌套会变慢。加
除非你的数据非常重复，否则 20 列需要对齐很多。