PostgreSQL tuple alignment padding - 對(duì)齊規(guī)則，以及如何選擇字段順序

背景

PostgreSQL tuple內(nèi)部有ALIGN機(jī)制，因此字段順序選擇實(shí)際上也是有講究的，選擇不好，可能因?yàn)锳LIGN導(dǎo)致占用空間放大。

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

對(duì)齊規(guī)則

tuple對(duì)齊規(guī)則

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) \  
           ))) \  
)

通過(guò)pg_attribute系統(tǒng)表，查看對(duì)齊情況

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).

表示在這個(gè)字段前面必須已經(jīng)以以上大小對(duì)齊。

例如,

c表示，這個(gè)字段的前面已占用空間(包括24字節(jié)的tuple head)必須1字節(jié)的倍數(shù)。如果不是，則前面一個(gè)字段末尾必須有padding，使得符合這個(gè)對(duì)齊條件。

s表示，這個(gè)字段前面已占用空間(包括24字節(jié)的tuple head)必須是2字節(jié)的倍數(shù)。如果不是，則前面一個(gè)字段末尾必須有padding，使得符合這個(gè)對(duì)齊條件。

i表示，這個(gè)字段前面已占用空間(包括24字節(jié)的tuple head)必須是4字節(jié)的倍數(shù)。如果不是，則前面一個(gè)字段末尾必須有padding，使得符合這個(gè)對(duì)齊條件。

d表示，這個(gè)字段前面已占用空間(包括24字節(jié)的tuple head)必須是8字節(jié)的倍數(shù)。如果不是，則前面一個(gè)字段末尾必須有padding，使得符合這個(gè)對(duì)齊條件。

例如

1、

1,4,8

會(huì)變成

1,3(padding),4,8

2、

1,1,8

會(huì)變成

1,1,6(padding),8

3、

1,1,1,8

會(huì)變成

1,1,1,5,8

例子

1、空行

postgres=# select pg_column_size(row());  
 pg_column_size   
----------------  
             24  
(1 row)

2、PADDING

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

相關(guān)文檔舉例

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   
   
 empty | byte2 | byte8 | byte16   
-------+-------+-------+--------  
    24 |    26 |    32 |     40

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  
);

查看這個(gè)表的對(duì)齊規(guī)則

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、插入測(cè)試數(shù)據(jù)

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、調(diào)整字段順序，可以把padding消除掉，基于前面說(shuō)的規(guī)則即可。

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;  
   
 empty_row | no_val | no_dec | with_dec | col2 | col4 | round8   
-----------+--------+--------+----------+------+------+--------  
        24 |     27 |     29 |       31 |   31 |   33 |     36

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;  
   
 empty_row | no_text | min_text | two_col | round4   
-----------+---------+----------+---------+--------  
        24 |      25 |       26 |      30 |     32

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;  
   
 empty_row | int2 | int4 | int8 | padded | not_padded   
-----------+------+------+------+--------+------------  
        24 |   26 |   28 |   32 |     40 |         40

5、消除tuple padding的優(yōu)化，字段順序如下

5.1、定長(zhǎng)字段（從大到小）

5.2、變長(zhǎng)字段

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、優(yōu)化后的padding情況，可以看到已經(jīng)消除了padding，空間降低到112MB。

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 t.typlen DESC;  
   
   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

小結(jié)

消除tuple PADDING, 字段順序規(guī)則：

1、定長(zhǎng)字段（從大到小）

2、變長(zhǎng)字段

本文的例子簡(jiǎn)單的說(shuō)明了padding引入的TUPLE變大的情況，使用以上規(guī)則調(diào)整字段順序后，空間占用下降了10%左右。

參考

src/include/access/tupmacs.h

src/backend/access/common/heaptuple.c

https://blog.2ndquadrant.com/on-rocks-and-sand/

《Greenplum 優(yōu)化CASE - 對(duì)齊JOIN字段類(lèi)型，使用數(shù)組代替字符串，降低字符串處理開(kāi)銷(xiāo)，列存降低掃描開(kāi)銷(xiāo)》

《PostgreSQL 10.0 preview 性能增強(qiáng) - pg_xact align(cacheline對(duì)齊)》

《未對(duì)齊(alignment)造成SSD 寫(xiě)放大一例》

https://yq.aliyun.com/articles/237

https://www.pgcon.org/2012/schedule/attachments/258_212_Internals%20Of%20PostgreSQL%20Wal.pdf

https://www.postgresql.org/docs/devel/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS

https://www.postgresql.org/docs/devel/static/wal-reliability.html

原文地址：https://github.com/digoal/blog/blob/master/201810/20181001_01.md