F混合存储大小的ASCII数据类型

Question

我有一个数据库需要存储UN/LOCODE值，我发现，使用标准数据类型，不可能以固定大小的形式存储这些值，这样我就可以有效地播放列俄罗斯方块。

目前，我已经为它定义了一个域如下：

-- city: UN/LOCODE https://service.unece.org/trade/locode/Service/LocodeColumn.htm#LOCODE
CREATE DOMAIN t_locode AS VARCHAR
CONSTRAINT t_locode_check CHECK (
value IS NULL
OR
value ~ '^[A-Z]{2} [A-Z][A-Z0-9]{1,2}$'
);

结果是：

testing=# dT+ t_locode 
List of data types
-[ RECORD 1 ]-----+---------
Schema            | public
Name              | t_locode
Internal name     | t_locode
Size              | var
Elements          | 
Owner             | postgres
Access privileges | 
Description       | 

我还尝试使用固定长度的字符类型定义来代替VARCHAR(例如CHAR(6)(，但这根本不会改变，我怀疑这是因为数据库本身设置为UTF-8，根据定义，UTF-8是可变长度的字符编码。

为了进一步解决这个问题，该域随后被用于其他用户数据类型。

充其量，我想定义一种数据类型，它可以最大使用8字节的存储来存储这些值，以与double对齐。

有人知道解决这个问题的办法吗？

Answer 1

字符串数据类型在PostgreSQL中总是可变宽度数据类型，无论您使用text、character varying还是character。

我认为你试图优化一些不需要优化的东西。这种企图往往弊大于利。

如果您的字符串总是6个ASCII字符长，那么它们将占用存储中的7个字节：

CREATE TABLE x(id bigint, t text);
INSERT INTO x VALUES (1, '      '), (2, '000000');
CREATE EXTENSION pageinspect;
SELECT t_ctid, t_attrs FROM heap_page_item_attrs(get_raw_page('x', 0), 'x');
t_ctid |                   t_attrs                   
--------+---------------------------------------------
(0,1)  | {"\x0100000000000000","\x0f202020202020"}
(0,2)  | {"\x0200000000000000","\x0f303030303030"}
(2 rows)

这是一个小型的endian机器，正如您在bigint列中看到的那样。

您会注意到text值每个只占用7个字节。这是由TOAST引起的，它是你的朋友，而不是你的敌人：

TOAST篡夺varlena长度字的两个位(大端机器上的高阶位，小端机器的低阶位(，从而将TOAST可数据类型的任何值的逻辑大小限制为1GB(2³⁰-1字节(。[…]当设置了最高阶或最低阶位时，该值只有一个单字节标头，而不是正常的四字节标头，该字节的其余位以字节为单位给出总数据大小(包括长度字节(。

0x0F是二进制00001111：最右边的1表示我们只有一个单字节的标头，其余的0000111(十进制7(是数据的长度，包括标头。

由于您的值只有7个字节长，因此它们将很好地与double precision对齐，只丢失一个填充字节。

如果您想通过避免填充字节来进行微观优化，请将所有字符串列放在表定义的末尾。另一方面，考虑到PostgreSQL必须经过前九列才能到达第十列，因此将常用列放在第一位将是一个性能优势。

但我并不太担心这些问题：如果您发现需要更大的数字，那么使用integer而不是bigint来节省4个字节可能会在以后引发大问题，而且在性能方面，与良好的数据模型、良好的查询和正确的索引相比，列的放置是无关紧要的。