PostgreSQL tuple alignment padding (行，字段对齐) - 对齐规则，以及如何选择字段顺序

2018-10-23 2130

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB

云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB

简介： postgresql 数据库字段选择

背景
PostgreSQL tuple内部有ALIGN机制，因此字段顺序选择实际上也是有讲究的，选择不好，可能因为ALIGN导致占用空间放大。

src/backend/access/common/heaptuple.c

Before Postgres 8.3 varlenas always had a 4-byte length header, and
therefore always needed 4-byte alignment (at least). This wasted space
for short varlenas, for example CHAR(1) took 5 bytes and could need up to
3 additional padding bytes for alignment.
Now, a short varlena (up to 126 data bytes) is reduced to a 1-byte header
and we don't align it. To hide this from datatype-specific functions that
don't want to deal with it, such a datum is considered "toasted" and will
be expanded back to the normal 4-byte-header format by pg_detoast_datum.
(In performance-critical code paths we can use pg_detoast_datum_packed
and the appropriate access macros to avoid that overhead.) Note that this
conversion is performed directly in heap_form_tuple, without invoking
tuptoaster.c.
https://www.postgresql.org/docs/devel/static/catalog-pg-type.html

对齐规则
tuple对齐规则

src/include/access/tupmacs.h

att_align_datum aligns the given offset as needed for a datum of alignment
requirement attalign and typlen attlen. attdatum is the Datum variable
we intend to pack into a tuple (it's only accessed if we are dealing with
a varlena type). Note that this assumes the Datum will be stored as-is;
callers that are intending to convert non-short varlena datums to short
format have to account for that themselves.
*/

define att_align_datum(cur_offset, attalign, attlen, attdatum) \

(

    ((attlen) == -1 && VARATT_IS_SHORT(DatumGetPointer(attdatum))) ? \  
    (uintptr_t) (cur_offset) : \  
    att_align_nominal(cur_offset, attalign) \

)

att_align_pointer performs the same calculation as att_align_datum,
but is used when walking a tuple. attptr is the current actual data
pointer; when accessing a varlena field we have to "peek" to see if we
are looking at a pad byte or the first byte of a 1-byte-header datum.
(A zero byte must be either a pad byte, or the first byte of a correctly
aligned 4-byte length word; in either case we can align safely. A non-zero
byte must be either a 1-byte length word, or the first byte of a correctly
aligned 4-byte length word; in either case we need not align.)
Note: some callers pass a "char *" pointer for cur_offset. This is
a bit of a hack but should work all right as long as uintptr_t is the
correct width.
*/

define att_align_pointer(cur_offset, attalign, attlen, attptr) \

(

    ((attlen) == -1 && VARATT_NOT_PAD_BYTE(attptr)) ? \  
    (uintptr_t) (cur_offset) : \  
    att_align_nominal(cur_offset, attalign) \

)

att_align_nominal aligns the given offset as needed for a datum of alignment
requirement attalign, ignoring any consideration of packed varlena datums.
There are three main use cases for using this macro directly:
- we know that the att in question is not varlena (attlen != -1);
in this case it is cheaper than the above macros and just as good.
- we need to estimate alignment padding cost abstractly, ie without
reference to a real tuple. We must assume the worst case that
all varlenas are aligned.
- within arrays, we unconditionally align varlenas (XXX this should be
revisited, probably).
The attalign cases are tested in what is hopefully something like their
frequency of occurrence.
*/

define att_align_nominal(cur_offset, attalign) \

(

    ((attalign) == 'i') ? INTALIGN(cur_offset) : \  
     (((attalign) == 'c') ? (uintptr_t) (cur_offset) : \  
      (((attalign) == 'd') ? DOUBLEALIGN(cur_offset) : \  
       ( \  
                    AssertMacro((attalign) == 's'), \  
                    SHORTALIGN(cur_offset) \  
       ))) \

)
通过pg_attribute系统表，查看对齐情况
https://www.postgresql.org/docs/devel/static/catalog-pg-type.html

typalign is the alignment required when storing a value of this type. It applies to storage on disk as well as most representations of the value inside PostgreSQL. When multiple values are stored consecutively, such as in the representation of a complete row on disk, padding is inserted before a datum of this type so that it begins on the specified boundary. The alignment reference is the beginning of the first datum in the sequence.

Possible values are:

c = char alignment, i.e., no alignment needed.

s = short alignment (2 bytes on most machines).

i = int alignment (4 bytes on most machines).

d = double alignment (8 bytes on many machines, but by no means all).
表示在这个字段前面必须已经以以上大小对齐。

例如,

c表示，这个字段的前面已占用空间(包括24字节的tuple head)必须1字节的倍数。如果不是，则前面一个字段末尾必须有padding，使得符合这个对齐条件。

s表示，这个字段前面已占用空间(包括24字节的tuple head)必须是2字节的倍数。如果不是，则前面一个字段末尾必须有padding，使得符合这个对齐条件。

i表示，这个字段前面已占用空间(包括24字节的tuple head)必须是4字节的倍数。如果不是，则前面一个字段末尾必须有padding，使得符合这个对齐条件。

d表示，这个字段前面已占用空间(包括24字节的tuple head)必须是8字节的倍数。如果不是，则前面一个字段末尾必须有padding，使得符合这个对齐条件。

例如
1、

1,4,8
会变成

1,3(padding),4,8
2、

1,1,8
会变成

1,1,6(padding),8
3、

1,1,1,8
会变成

1,1,1,5,8
例子
1、空行

postgres=# select pg_column_size(row());

pg_column_size

(1 row)
2、PADDING

postgres=# select pg_column_size(row(char 'a', char 'b', int4 '1'));

pg_column_size

(1 row)

postgres=# select pg_column_size(row(char 'a', char 'b', int8 '1'));

pg_column_size

(1 row)
相关文档举例
https://blog.2ndquadrant.com/on-rocks-and-sand/

1、

SELECT pg_column_size(ROW()) AS empty,

   pg_column_size(ROW(0::SMALLINT)) AS byte2,  
   pg_column_size(ROW(0::BIGINT)) AS byte8,  
   pg_column_size(ROW(0::SMALLINT, 0::BIGINT)) AS byte16;  -- 24,2,6(padding),8

2、包含padding的表

CREATE TABLE user_order (
is_shipped BOOLEAN NOT NULL DEFAULT FALSE,
user_id BIGINT NOT NULL,
order_total NUMERIC NOT NULL,
order_dt TIMESTAMPTZ NOT NULL,
order_type SMALLINT NOT NULL,
ship_dt TIMESTAMPTZ,
item_ct INT NOT NULL,
ship_cost NUMERIC,
receive_dt TIMESTAMPTZ,
tracking_cd TEXT,
id BIGSERIAL PRIMARY KEY NOT NULL
);
查看这个表的对齐规则

SELECT a.attname, t.typname, t.typalign, t.typlen
FROM pg_class c
JOIN pg_attribute a ON (a.attrelid = c.oid)
JOIN pg_type t ON (t.oid = a.atttypid)
WHERE c.relname = 'user_order'
AND a.attnum >= 0
ORDER BY a.attnum;

attname	typname	typalign	typlen
is_shipped	bool	c	1
user_id	int8	d	8
order_total	NUMERIC	i	-1
order_dt	timestamptz	d	8
order_type	int2	s	2
ship_dt	timestamptz	d	8
item_ct	int4	i	4
ship_cost	NUMERIC	i	-1
receive_dt	timestamptz	d	8
tracking_cd	text	i	-1
id	int8	d	8

3、插入测试数据

135MB

INSERT INTO user_order (

is_shipped, user_id, order_total, order_dt, order_type,  
ship_dt, item_ct, ship_cost, receive_dt, tracking_cd

)
SELECT TRUE, 1000, 500.00, now() - INTERVAL '7 days',

   3, now() - INTERVAL '5 days', 10, 4.99,  
   now() - INTERVAL '3 days', 'X5901324123479RROIENSTBKCV4'

FROM generate_series(1, 1000000);

SELECT pg_relation_size('user_order') AS size_bytes,

   pg_size_pretty(pg_relation_size('user_order')) AS size_pretty;

size_bytes	size_pretty
141246464	135 MB

4、调整字段顺序，可以把padding消除掉，基于前面说的规则即可。

SELECT pg_column_size(ROW()) AS empty_row,

   pg_column_size(ROW(0::NUMERIC)) AS no_val,  
   pg_column_size(ROW(1::NUMERIC)) AS no_dec,  
   pg_column_size(ROW(9.9::NUMERIC)) AS with_dec,  
   pg_column_size(ROW(1::INT2, 1::NUMERIC)) AS col2,  
   pg_column_size(ROW(1::INT4, 1::NUMERIC)) AS col4,  
   pg_column_size(ROW(1::NUMERIC, 1::INT4)) AS round8;

SELECT pg_column_size(ROW()) AS empty_row,

   pg_column_size(ROW(''::TEXT)) AS no_text,  
   pg_column_size(ROW('a'::TEXT)) AS min_text,  
   pg_column_size(ROW(1::INT4, 'a'::TEXT)) AS two_col,  
   pg_column_size(ROW('a'::TEXT, 1::INT4)) AS round4;

SELECT pg_column_size(ROW()) AS empty_row,

   pg_column_size(ROW(1::SMALLINT)) AS int2,  
   pg_column_size(ROW(1::INT)) AS int4,  
   pg_column_size(ROW(1::BIGINT)) AS int8,  
   pg_column_size(ROW(1::SMALLINT, 1::BIGINT)) AS padded,  
   pg_column_size(ROW(1::INT, 1::INT, 1::BIGINT)) AS not_padded;

5、消除tuple padding的优化，字段顺序如下

5.1、定长字段（从大到小）

5.2、变长字段

DROP TABLE user_order;

CREATE TABLE user_order (
id BIGSERIAL PRIMARY KEY NOT NULL,
user_id BIGINT NOT NULL,
order_dt TIMESTAMPTZ NOT NULL,
ship_dt TIMESTAMPTZ,
receive_dt TIMESTAMPTZ,
item_ct INT NOT NULL,
order_type SMALLINT NOT NULL,
is_shipped BOOLEAN NOT NULL DEFAULT FALSE,
tracking_cd TEXT,
order_total NUMERIC NOT NULL,
ship_cost NUMERIC
);

INSERT INTO user_order (

is_shipped, user_id, order_total, order_dt, order_type,  
ship_dt, item_ct, ship_cost, receive_dt, tracking_cd

)
SELECT TRUE, 1000, 500.00, now() - INTERVAL '7 days',

   3, now() - INTERVAL '5 days', 10, 4.99,  
   now() - INTERVAL '3 days', 'X5901324123479RROIENSTBKCV4'

FROM generate_series(1, 1000000);

postgres=# dt+ user_order

                   List of relations

Schema	Name	Type	Owner	Size	Description
public	user_order	table	postgres	112 MB

(1 row)
6、优化后的padding情况，可以看到已经消除了padding，空间降低到112MB。

attname	typname	typalign	typlen
id	int8	d	8
user_id	int8	d	8
order_dt	timestamptz	d	8
ship_dt	timestamptz	d	8
receive_dt	timestamptz	d	8
item_ct	int4	i	4
order_type	int2	s	2
is_shipped	bool	c	1
tracking_cd	text	i	-1
ship_cost	NUMERIC	i	-1
order_total	NUMERIC	i	-1