PostgreSQL数据库表的内部结构

A page within a table contains three kinds of data described as follows:

1. heap tuple(s) – A heap tuple is a record data itself. They are stacked in order from the bottom of the page. The internal structure of tuple is described in Section 5.2 and Chapter 9 as the knowledge of both Concurrency Control(CC) and WAL in PostgreSQL are required.
2. line pointer(s) – A line pointer is 4 byte long and holds a pointer to each heap tuple. It is also called an item pointer. 
3. Line pointers form a simple array, which plays the role of index to the tuples. Each index is numbered sequentially from 1, and called offset number. When a new tuple is added to the page, a new line pointer is also pushed onto the array to point to the new one.
4. header data – A header data defined by the structure PageHeaderData is allocated in the beginning of the page. It is 24 byte long and contains general information about the page. The major variables of the structure are described below.
• pd_lsn – This variable stores the LSN of XLOG record written by the last change of this page. It is an 8-byte unsigned integer, related to the WAL (Write-Ahead Logging) mechanism. The details are described in Chapter 9.
• pd_checksum – This variable stores the checksum value of this page. (Note that this variable is supported in version 9.3 or later; in earlier versions, this part had stored the timelineId of the page.)
• pd_lower, pd_upper – pd_lower points to the end of line pointers, and pd_upper to the beginning of the newest heap tuple.
• pd_special – This variable is for indexes. In the page within tables, it points to the end of the page. (In the page within indexes, it points to the beginning of special space which is the data area held only by indexes and contains the particular data according to the kind of index types such as B-tree, GiST, GiN, etc.)

An empty space between the end of line pointers and the beginning of the newest tuple is referred to as free space or hole.

To identify a tuple within the table, tuple identifier (TID) is internally used. A TID comprises a pair of values: the block number of the page that contains the tuple, and the offset number of the line pointer that points to the tuple. A typical example of its usage is index. See more detail in Section 1.4.2.

While the HeapTupleHeaderData structure contains seven fields, four fields are required in the subsequent sections.

• t_xmin holds the txid of the transaction that inserted this tuple.
• t_xmax holds the txid of the transaction that deleted or updated this tuple. If this tuple has not been deleted or updated, t_xmax is set to 0, which means INVALID.
• t_cid holds the command id (cid), which means how many SQL commands were executed before this command was executed within the current transaction beginning from 0. For example, assume that we execute three INSERT commands within a single transaction: 'BEGIN; INSERT; INSERT; INSERT; COMMIT;'. If the first command inserts this tuple, t_cid is set to 0. If the second command inserts this, t_cid is set to 1, and so on.
• t_ctid holds the tuple identifier (tid) that points to itself or a new tuple. tid, described in Section 1.3, is used to identify a tuple within a table. When this tuple is updated, the t_ctid of this tuple points to the new tuple; otherwise, the t_ctid points to itself.

引用：http://www.interdb.jp/pg/pgsql01.html

使用pageinspect插件，查看表内部记录：

TEST=# select * from heap_page_items(get_raw_page('ITEMS', 0)) limit 10;
 LP | LP_OFF | LP_FLAGS | LP_LEN | T_XMIN | T_XMAX | T_FIELD3 | T_CTID | T_INFOMASK2 | T_INFOMASK | T_HOFF | T_BITS | T_OID |                       T_DATA                       
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+----------------------------------------------------
  1 |   8144 |        1 |     48 |   2177 |      0 |        0 | (0,1)  |           3 |       2306 |     24 |        |       | x010000001732323037382e30333036003375adb6723f0200
  2 |   8096 |        1 |     48 |   2177 |      0 |        0 | (0,2)  |           3 |       2306 |     24 |        |       | x020000001734363034322e31323331003375adb6723f0200
  3 |   8048 |        1 |     48 |   2177 |      0 |        0 | (0,3)  |           3 |       2306 |     24 |        |       | x030000001734373834302e38333135003375adb6723f0200
  4 |   8000 |        1 |     48 |   2177 |      0 |        0 | (0,4)  |           3 |       2306 |     24 |        |       | x040000001733353935312e33313137003375adb6723f0200
  5 |   7952 |        1 |     48 |   2177 |      0 |        0 | (0,5)  |           3 |       2306 |     24 |        |       | x050000001736393235322e35363536003375adb6723f0200
  6 |   7904 |        1 |     48 |   2177 |      0 |        0 | (0,6)  |           3 |       2306 |     24 |        |       | x060000001738333935342e37323239003375adb6723f0200
  7 |   7856 |        1 |     48 |   2177 |      0 |        0 | (0,7)  |           3 |       2306 |     24 |        |       | x070000001731343832382e38343435003375adb6723f0200
  8 |   7808 |        1 |     48 |   2177 |      0 |        0 | (0,8)  |           3 |       2306 |     24 |        |       | x080000001734323237382e37363233003375adb6723f0200
  9 |   7760 |        1 |     48 |   2177 |      0 |        0 | (0,9)  |           3 |       2306 |     24 |        |       | x090000001736323338382e32383638003375adb6723f0200
 10 |   7712 |        1 |     48 |   2177 |      0 |        0 | (0,10) |           3 |       2306 |     24 |        |       | x0a00000015373432392e3436393400003375adb6723f0200
(10 rows)

查看索引：

TEST=# select * from bt_page_items('ITEMS_PKEY', 1) limit 10;
 ITEMOFFSET |  CTID  | ITEMLEN | NULLS | VARS |          DATA           
------------+--------+---------+-------+------+-------------------------
          1 | (2,53) |      16 | f     | f    | 6f 01 00 00 00 00 00 00
          2 | (0,1)  |      16 | f     | f    | 01 00 00 00 00 00 00 00
          3 | (0,2)  |      16 | f     | f    | 02 00 00 00 00 00 00 00
          4 | (0,3)  |      16 | f     | f    | 03 00 00 00 00 00 00 00
          5 | (0,4)  |      16 | f     | f    | 04 00 00 00 00 00 00 00
          6 | (0,5)  |      16 | f     | f    | 05 00 00 00 00 00 00 00
          7 | (0,6)  |      16 | f     | f    | 06 00 00 00 00 00 00 00
          8 | (0,7)  |      16 | f     | f    | 07 00 00 00 00 00 00 00
          9 | (0,8)  |      16 | f     | f    | 08 00 00 00 00 00 00 00
         10 | (0,9)  |      16 | f     | f    | 09 00 00 00 00 00 00 00
(10 rows)

用linux工具查看二进制：

TEST=# select pg_relation_filepath('ITEMS');
 SYS_RELATION_FILEPATH 
-----------------------
 base/14804/16826
(1 row)

TEST=# 
[1]+  Stopped                 ksql -U SYSTEM -d TEST
[postgres@localhost ~]$ cd $PG_DATA
[postgres@localhost data]$ cd base/14804/16826
-bash: cd: base/14804/16826: Not a directory

[postgres@localhost data]$ hexdump -C base/14804/16826|head -n 10
00000000 00 00 00 00 00 74 99 0a 00 00 00 00 40 00 70 1e |.....t......@.p.|
00000010 00 20 04 20 00 00 00 00 d8 9f 4a 00 b0 9f 48 00 |. . ......J...H.|
00000020 88 9f 4a 00 60 9f 48 00 38 9f 4a 00 10 9f 4a 00 |..J.`.H.8.J...J.|
00000030 e8 9e 4a 00 c0 9e 4a 00 98 9e 4a 00 70 9e 4a 00 |..J...J...J.p.J.|
00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
00001e70 85 08 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
00001e80 0a 00 02 00 02 08 18 00 0a 00 00 00 13 74 65 73 |.............tes|
00001e90 74 37 30 36 30 00 00 00 85 08 00 00 00 00 00 00 |t7060...........|
00001ea0 00 00 00 00 00 00 00 00 09 00 02 00 02 08 18 00 |................|

postgres