postgresql - Postgres 和表的内部组织

Question

我找到了 postgresql 内部工作原理的解释。有如下图片:

以及以下解释:

Items after the headers is an array identifier composed of (offset, length) pairs pointing to the actual items.

Because an item identifier is never moved until it is freed, its index can be used on a long-term basis to reference an item, even when the item itself is moved around on the page to compact free space. A Pointer to an item is called CTID (ItemPointer), created by PostgreSQL, it consists of a page number and the index of an item identifier.

你能帮我清理一下这里的一些东西吗？

1. 页眉附近的项目本身就是 CTID，或者项目和 CTID 是不同的东西，我说得对吗？
2. CTID 永远不会移动或成行吗？
3. 根据答案，也许我会理解以下内容的确切含义“因为项目标识符在被释放之前永远不会移动，所以它的索引可以长期用于引用项目，即使项目本身在页面上四处移动以压缩可用空间。”但是，最好有更多更详细的解释。

Answer 1

图中所谓的“item”，用PostgreSQL的行话来说就是“行指针”。它在 src/include/storage/itemid.h 中定义:

/*
 * A line pointer on a buffer page.  See buffer page definitions and comments
 * for an explanation of how line pointers are used.
 *
 * In some cases a line pointer is "in use" but does not have any associated
 * storage on the page.  By convention, lp_len == 0 in every line pointer
 * that does not have storage, independently of its lp_flags state.
 */
typedef struct ItemIdData
{
    unsigned    lp_off:15,      /* offset to tuple (from start of page) */
                lp_flags:2,     /* state of line pointer, see below */
                lp_len:15;      /* byte length of tuple */
} ItemIdData;

typedef ItemIdData *ItemId;

这些行指针存储在页眉之后的数组中。

请参阅 src/include/storage/bufpage.h 中的优秀文档:

/*
 * A postgres disk page is an abstraction layered on top of a postgres
 * disk block (which is simply a unit of i/o, see block.h).
 *
 * specifically, while a disk block can be unformatted, a postgres
 * disk page is always a slotted page of the form:
 *
 * +----------------+---------------------------------+
 * | PageHeaderData | linp1 linp2 linp3 ...           |
 * +-----------+----+---------------------------------+
 * | ... linpN |                                      |
 * +-----------+--------------------------------------+
 * |           ^ pd_lower                             |
 * |                                                  |
 * |             v pd_upper                           |
 * +-------------+------------------------------------+
 * |             | tupleN ...                         |
 * +-------------+------------------+-----------------+
 * |       ... tuple3 tuple2 tuple1 | "special space" |
 * +--------------------------------+-----------------+
 *                                  ^ pd_special
 *
 * NOTES:
 *
 * linp1..N form an ItemId (line pointer) array.  ItemPointers point
 * to a physical block number and a logical offset (line pointer
 * number) within that block/page.  Note that OffsetNumbers
 * conventionally start at 1, not 0.
 *
 * tuple1..N are added "backwards" on the page.  Since an ItemPointer
 * offset is used to access an ItemId entry rather than an actual
 * byte-offset position, tuples can be physically shuffled on a page
 * whenever the need arises.  This indirection also keeps crash recovery
 * relatively simple, because the low-level details of page space
 * management can be controlled by standard buffer page code during
 * logging, and during recovery.

问题的答案:

1. 元组的ctid是物理地址，由 block 号(从0开始)和< em>行指针(从 1 开始)。您可以从表行的 ctid 中识别行指针:它是第二个数字。例如，(321,5) 将是第 322 页上的第五行指针。

2. block 中实际元组的位置不固定:它存储在 lp_off 中。这允许 PostgreSQL 在不更改元组的物理地址 (tid) 的情况下移动 block 中的数据。行指针本身永远不会改变。

3. 如上所述，实际数据可以在 block 中移动，但行指针不会改变。元组的 ctid 是存储在索引中的内容。现在声明应该很清楚了。