时间戳

首页 > 解决方案 > 具有大量列的唯一索引

postgresql - 具有大量列的唯一索引

问题描述

我有一个繁忙的 OLTP 表，它有 30 列和 5000 万行，我想避免其中出现重复。
我应该采取什么方法？

到目前为止，我想出了这些：

对于后者，我觉得如果表模式发生变化，重新生成该哈希列将会有很多麻烦。

也许还有其他一些我没有想到的方法？

标签： postgresqlindexingdatabase-designunique-constraint

解决方案

Postgres 14

...刚刚推出了一个用于记录的内置哈希函数，这比我的自定义函数便宜得多。特别是对于许多列！看：

这使得表达式索引比生成的列加索引更有吸引力。所以就：

CREATE UNIQUE INDEX tbl_row_uni ON tbl (hash_record_extended(tbl.*,0));

这通常也有效：

CREATE UNIQUE INDEX tbl_row_uni ON tbl (hash_record_extended(tbl,0));

但第一种变体更安全。tbl如果应该存在同名的列，则在第二个变体中将解析为该列。

Postgres 13（原始答案）

我最近在 dba.SE 上为这个问题提供了一个解决方案：

这非常接近您的第三个想法：

基本上，一个非常有效的服务器端生成的哈希放置在第 31 列，有UNIQUE约束。

CREATE OR REPLACE FUNCTION public.f_tbl_bighash(col1 text, col2 text, ... , col30 text)
  RETURNS bigint 
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS 
'SELECT hashtextextended(textin(record_out(($1,$2, ... ,$30))), 0)';

ALTER TABLE tbl
  ADD COLUMN tbl_bighash bigint NOT NULL GENERATED ALWAYS AS (public.f_tbl_bighash(col1, col2, ... , col30)) STORED  -- append column in last position
, ADD CONSTRAINT tbl_bighash_uni UNIQUE (tbl_bighash);

它的美妙之处：它可以高效地工作，而无需更改任何其他内容。（可能在您使用SELECT *或INSERT INTO不使用目标列表或类似的地方除外。）

它也适用于NULL价值观（将它们视为平等）。

如果任何列类型具有不可变的文本表示，请小心。（如timestamptz.）该解决方案使用所有text列进行测试。

如果表架构发生变化，请先删除UNIQUE约束，重新创建函数并重新创建生成的列 - 理想情况下使用单个ALTER TABLE语句，这样您就不会重写表两次。

或者，使用UNIQUE基于 的表达式索引public.f_tbl_bighash()。一样的效果。好处：没有额外的表格列。缺点：在计算上有点贵。

推荐阅读