postgresql – MD5字段的最佳数据类型是什么？

SELECT list,of,fields 
FROM table 
WHERE name_key IN (md5a,md5b,md5c...);

数据类型 uuid非常适合该任务.对于varchar或文本表示,它仅占用16个字节而不是RAM中的37个字节. (或者在磁盘上有33个字节,但奇数在许多情况下需要填充以使其有效地达到40个字节.)并且uuid类型具有更多优点.

例：

SELECT md5('Store hash for long string,maybe for index?')::uuid AS md5_hash

细节和更多解释：

> Convert hex in text representation to decimal number
> Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

如果你不需要md5的加密组件,你可能会考虑其他(更便宜的)散列函数,但我会使用md5作为你的用例(主要是只读的).

一句警告：对于你的情况(一旦写入不可变),功能依赖(伪自然)PK就可以了.但同样令人痛苦的是文本更新是可能的.考虑纠正一个错字：PK和所有依赖索引,其他几十个表中的FK列和其他引用也必须改变.表和索引膨胀,锁定问题,缓慢更新,丢失引用,…

如果文本在正常操作中可以改变,那么surrogate PK将是更好的选择.我建议使用bigserial列(范围-9223372036854775808到9223372036854775807 – 即nine quintillion two hundred twenty-three quadrillion three hundred seventy-two trillion thirty-six something billion)数十亿行的不同值.在任何情况下都可能是个好主意：对于数十个FK列和索引,8个而不是16个字节！).或random UUID更大的基数或分布式系统.您可以随时存储所述md5(作为uuid)以快速从原始文本中查找主表中的行.有关：

> Default value for UUID column in Postgres

至于你的查询：

> Optimizing a Postgres query with a large IN

要解决@Daniel’s comment：如果您更喜欢不带连字符的表示,请删除显示的连字符：

SELECT replace('90b7525e-84f6-4850-c2ef-b407fae3f271','-','')

但我不会打扰.默认表示就好了.这个问题真的不是这里的代表.

如果其他方应该采用不同的方法并将没有连字符的字符串放入混合中,那也不是问题. Postgres接受几个合理的文本表示作为uuid的输入. The documentation：

PostgreSQL also accepts the following alternative forms for input: use
of upper-case digits,the standard format surrounded by braces,
omitting some or all hyphens,adding a hyphen after any group of four
digits. Examples are:

06002

更重要的是,md5()函数返回文本,你将使用decode()转换为bytea,默认表示为：

SELECT decode(md5('Store hash for long string,maybe for index?'),'hex')

220267R^204366HP30235726407372343362q

您必须再次编码()以获取原始文本表示：

SELECT encode(my_md5_as_bytea,'hex');

最重要的是,由于internal varlena overhead,存储为bytea的值将占用RAM中的20个字节(以及磁盘上的17个字节,24 with padding),这对于简单索引的大小和性能特别不利.

一切都在这里支持uuid.