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

本文涉及的产品
云原生内存数据库 Tair,内存型 2GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 标签PostgreSQL , 对齐 , 变长 , 行 , tuple , row , alignment , padding背景PostgreSQL tuple内部有ALIGN机制,因此字段顺序选择实际上也是有讲究的,选择不好,可能因为ALIGN导致占用空间放大。

标签

PostgreSQL , 对齐 , 变长 , 行 , tuple , row , 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)  

相关文档举例

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
2月前
|
关系型数据库 PostgreSQL
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
PostgreSQL排序字段不唯一导致分页查询结果出现重复数据
67 0
|
2月前
|
SQL 关系型数据库 数据库
实时计算 Flink版操作报错之使用SQL 将 PostgreSQL 的 date 类型字段转换为 TIMESTAMP 类型时遇到报错,该如何处理
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
2月前
|
运维 Cloud Native 关系型数据库
云原生数据仓库产品使用合集之原生数据仓库AnalyticDB PostgreSQL版如果是列存表的话, adb支持通过根据某个字段做upsert吗
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
1月前
|
SQL 关系型数据库 PostgreSQL
PostgreSQL和greenplum的copy命令可以添加字段吗?
【6月更文挑战第5天】PostgreSQL和greenplum的copy命令可以添加字段吗?
16 3
|
1月前
|
关系型数据库 PostgreSQL
postgresql如何将没有关联关系的两张表的字段合并
【6月更文挑战第2天】postgresql如何将没有关联关系的两张表的字段合并
27 3
|
2月前
|
监控 关系型数据库 数据库
关系型数据库识别常查询字段
【5月更文挑战第20天】
38 4
|
2月前
|
监控 关系型数据库 数据库
|
2月前
|
关系型数据库 MySQL 数据库
实时计算 Flink版产品使用合集之使用PostgreSQL作为源时,遇到before字段为NULL该如何处理
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
2月前
|
分布式计算 关系型数据库 大数据
MaxCompute产品使用合集之怎么才可以将 PostgreSQL 中的 geometry 空间类型字段同步到 MaxCompute 或另一个 PostgreSQL 数据库
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
12月前
|
存储 关系型数据库 PostgreSQL
PostgreSQL TID及tuple slot
PostgreSQL TID及tuple slot
150 0
PostgreSQL TID及tuple slot

相关产品

  • 云原生数据库 PolarDB