PostgreSQL的数据存储(十二)数据存储

4.3.2 Tuple是否可以跨页面

PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code. The technique is affectionately known as TOAST (or “the best thing since sliced bread”).

【翻译】PostgreSQL使用固定页面大小&＃xff08;通常是8字节&＃xff09;&＃xff0c;并且不允许元组跨越多个页面。因此&＃xff0c;直接存储非常大的值是不可能的。为了克服这一限制&＃xff0c;大字段值进行压缩和/或分成多个物理行。这对用户透明&＃xff0c;对大多数后端代码只有小的影响。该技术是亲切地称为TOAST&＃xff08;或“有史以来最好的事情”&＃xff09;。

以上英文是PG帮助系统给出的描述&＃xff0c;中文是简单翻译。对于数据库系统来说&＃xff0c;这是处理大数据的最好的方式吗&＃xff1f;

回到本节标题提出的问题&＃xff0c;元组是否可以跨页面&＃xff1f;答案是&＃xff1a;PG存储的基本单位是tuple&＃xff08;元组&＃xff0c;或称为记录&＃xff09;&＃xff0c;一条完整的tuple是不可以跨页面分开存储的。

4.3.3 微对象存储方式

一个tuple&＃xff0c;可能由多种数据类型的“列”组成&＃xff0c;有的列可能有空值&＃xff0c;有的列可能是定长的&＃xff0c;有的列可能是变长的。

列数据信息&＃xff0c;要存储在物理存储设备&＃xff0c;使用时要被读入内存&＃xff0c;即内外存要交换数据&＃xff0c;如何交换更为合适&＃xff1f;

有个概念叫做“内存对齐”&＃xff0c;是否有外存对齐&＃xff1f;为什么要内存对齐&＃xff1f;

PG处理元组&＃xff0c;涉及读和写。

读数据块到buf&＃xff0c;从buf变为数据页&＃xff08;pg知道数据页的格式&＃xff0c;所以才能分解数据页上的信息&＃xff09;&＃xff1b;数据页上存在元组tuple&＃xff0c;读出每个tuple&＃xff0c;才能得知具体的数据&＃xff1b;每个tuple的格式&＃xff0c;也在tuple上标明&＃xff0c;这样&＃xff0c;才能识别tuple中的具体列的信息。这一步&＃xff0c;是靠函数完成的。

写数据内容到buf&＃xff0c;是先形成tuple&＃xff08;heap_form_tuple->heap_fill_tuple&＃xff09;&＃xff0c;然后把tuple写到buf&＃xff0c;由系统在某个时刻&＃xff0c;把脏的buf写到外存。

但是&＃xff0c;如何形成tuple呢&＃xff1f;不同数据类型的列&＃xff0c;在创建表的时候&＃xff0c;就已经把列存储相关的信息&＃xff0c;写到了系统表&＃xff08;pg_attribute&＃xff09;中&＃xff0c;在读取元组中的内容时&＃xff0c;要根据系统表存储的信息&＃xff0c;进行逐列读取。

PG规定了不同数据类型如何存储&＃xff0c;基本原则一&＃xff1a;如果是值存储&＃xff0c;直接存储值&＃xff1b;如果是引用存储&＃xff0c;则先存放一个指针&＃xff0c;指针指向真正数据所在的位置。可以用PG联机帮助中的话佐证&＃xff1a;

typbyval determines whether internal routines pass a value of this type by value or by reference. typbyval had better be false if typlen is not 1, 2, or 4 (or 8 on machines where Datum is 8 bytes). Variable-length types are always passed by reference. Note that typbyval can be false even if the length would allow pass-by-value

PG存储数据类型基本原则二&＃xff1a;如果是变长类型&＃xff0c;则按变长方式处理&＃xff0c;区分了一个页面存储不下的情况&＃xff08;toast方式&＃xff09;、较段的变长类型、以及其他等&＃xff08;查看heap_fill_tuple函数[1]&＃xff09;。PG联机帮助中的话&＃xff1a;

For a fixed-size type, typlen is the number of bytes in the internal representation of the type. But for a variable-length type, typlen is negative. -1 indicates a “varlena” type (one that has a length word), -2 indicates a null-terminated C string.

PG存储数据类型基本原则三&＃xff1a;对于字符串的处理&＃xff08;上段中为“-2”的情况&＃xff09;。

另外&＃xff0c;其他情况的处理&＃xff0c;如下:

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 &＃61; char alignment, i.e., no alignment needed.

s &＃61; short alignment (2 bytes on most machines).

i &＃61; int alignment (4 bytes on most machines).

d &＃61; double alignment (8 bytes on many machines, but by no means all).

Note

For types used in system tables, it is critical that the size and alignment defined in pg_type agree with the way that the compiler will lay out the column in a structure representing a table row.

这段话是在说&＃xff0c;不同类型的存储&＃xff0c;是要求有对齐格式的&＃xff0c;对齐&＃xff0c;是内存中便于操作数据的一种指令加速方式。外存存储的数据&＃xff0c;同内存中的格式一致&＃xff0c;这样&＃xff0c;就不用把外存读入的数据进行转换&＃xff0c;存储时&＃xff0c;也不用特意从内存格式转换为外存&＃xff08;非对齐&＃xff09;格式&＃xff0c;加快了操作的效率&＃xff0c;但牺牲了外存的存储空间。