Shulou(Shulou.com)06/01 Report--

Background

There is an ALIGN mechanism within PostgreSQL tuple, so the selection of field order is actually fastidious, and the selection is not good, which may be due to the enlargement of the space occupied by 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

Alignment rule

Tuple alignment rule

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 ata 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)\))\) check the alignment through the pg_attribute system table

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

Indicates that it must be aligned to the above size before this field.

For example

C indicates that the previous occupied space of this field (including 24 bytes of tuple head) must be a multiple of 1 byte. If not, there must be a padding at the end of the previous field to meet this alignment condition.

S indicates that the previous occupied space in this field (including 24 bytes of tuple head) must be a multiple of 2 bytes. If not, there must be a padding at the end of the previous field to meet this alignment condition.

I indicates that the previous occupied space of this field (including 24 bytes of tuple head) must be a multiple of 4 bytes. If not, there must be a padding at the end of the previous field to meet this alignment condition.

D indicates that the previous occupied space in this field (including 24 bytes of tuple head) must be a multiple of 8 bytes. If not, there must be a padding at the end of the previous field to meet this alignment condition.

For example

1 、

1,4,8

Will become

1Jing 3 (padding), 4BI 8

2 、

1,1,8

Will become

1 padding 1 5 (1), 8

3 、

1,1,1,8

Will become

8 examples of 1pyrrine and 5pyrrine

1. Blank line

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

2 、 PADDING

Postgres=# select pg_column_size (row (char'a, char'baked, int4'1')); pg_column_size-32 (1 row) postgres=# select pg_column_size (row (char'a, char'baked, int8'1')) Examples of pg_column_size-40 (1 row) related documents

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

2. Table containing 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)

View the alignment rules for this table

SELECT a.attname, t.typname, t.typalign, t.typlen FROM pg_class c JOIN pg_attribute an 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. Insert test data

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', 'X5901324123479 RROIENSTBKCV4FROM 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 | 135MB

4. Adjust the order of fields to eliminate padding, based on the rules mentioned above.

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 | 36SELECT 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 | 32SELECT 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. Eliminate the optimization of tuple padding. The order of fields is as follows.

5.1, fixed length fields (from large to small)

5.2. Variable length field

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', 'X5901324123479 RROIENSTBKCV4FROM 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. In the case of optimized padding, you can see that padding has been eliminated and the space has been reduced to 112MB.

SELECT a.attname, t.typname, t.typalign, t.typlen FROM pg_class c JOIN pg_attribute an 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 summary

Eliminate tuple PADDING, field order rules:

1. Fixed-length fields (from large to small)

2. Variable length field

Referenc

Src/include/access/tupmacs.h

Src/backend/access/common/heaptuple.c

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

"Greenplum optimizes CASE-aligns JOIN field types, uses arrays instead of strings, reduces string processing overhead, and column storage reduces scanning overhead"

"PostgreSQL 10.0 preview performance enhancements-pg_xact align (cacheline alignment)"

An example of SSD write magnification caused by misalignment (alignment)

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

Original address: https://github.com/digoal/blog/blob/master/201810/20181001_01.md

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.