甜橘子，专注成都网站制作网站设计与营销型网站建设与优化
QQ :532337155
电话：028-86922220
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) \ ))) \ )
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)
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 );
查看这个表的对齐规则
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
消除tuple PADDING, 字段顺序规则：
1、定长字段（从大到小）
2、变长字段
本文的例子简单的说明了padding引入的TUPLE变大的情况，使用以上规则调整字段顺序后，空间占用下降了10%左右。
src/include/access/tupmacs.h
src/backend/access/common/heaptuple.c
https://blog.2ndquadrant.com/on-rocks-and-sand/
《Greenplum 优化CASE - 对齐JOIN字段类型，使用数组代替字符串，降低字符串处理开销，列存降低扫描开销》
《PostgreSQL 10.0 preview 性能增强 - pg_xact align(cacheline对齐)》
《未对齐(alignment)造成SSD 写放大一例》
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