PostgreSql表中UUID，CHAR和VARCHAR之间的性能差异?

问题描述

我将UUID v4值存储在PostgreSQL v9.4表的"id"列下.

I'm storing UUID v4 values in a PostgreSQL v9.4 table, under column "id".

当我创建表时，是否将"id"列定义为 VARCHAR(36)，CHAR(36)或 UUID 数据类型?

When I create the table, is there any difference in following write or read performance whether I define the "id" column as VARCHAR(36), CHAR(36), or UUID data type?

谢谢！

推荐答案

使用 uuid .PostgreSQL具有本机类型是有原因的.

Use uuid. PostgreSQL has the native type for a reason.

它将uuid内部存储为128位二进制字段.您建议的其他选项将其存储为十六进制，这在比较中效率很低.

It stores the uuid internally as a 128-bit binary field. Your other proposed options store it as hexadecimal, which is very inefficient in comparison.

不仅如此，而且:

• uuid 做一个简单的按字节排序的排序. text ， char 和 varchar 考虑排序规则和语言环境，这对于uuid来说是毫无意义的.

• uuid does a simple bytewise sort for ordering. text, char and varchar consider collations and locales, which is nonsensical for a uuid.

uuid 仅有一个规范表示.文字等并非如此；您必须考虑大小写十六进制，是否存在 {...-...} s等.

There is only one canonical respresentation of a uuid. The same is not true for text etc; you have to consider upper vs lower case hex, presence or absence of {...-...}s etc.

毫无疑问.使用 uuid .

唯一有意义的其他类型是 bytea ，它至少可以用于直接存储uuid的16个字节.如果我使用的系统无法处理基本集以外的数据类型(例如某种形式的愚蠢的ORM)，那么我将这样做.

The only other type that makes any sense is bytea, which at least can be used to store the 16 bytes of the uuid directly. This is what I'd do if I was using systems that couldn't cope with data types outside the basic set, like a really dumb ORM of some kind.

这篇关于PostgreSql表中UUID，CHAR和VARCHAR之间的性能差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！