PostgreSQL tuple alignment padding (行，字段对齐) - 对齐规则，以及如何选择字段顺序-阿里云开发者社区

背景

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

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

查看这个表的对齐规则

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;  
   
 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的优化，字段顺序如下

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。

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