Loading...
墨滴

herish

2021/12/17  阅读:51  主题:默认主题

关于PostgreSQL数据的存储,你有必要有所了解

PostgerSQL对象标识符

OID

OID 是 PostgreSQL 内部用于标识数据库对象(数据库,表**,视图,**存储过程等等)的标识符,用4个字节的无符号整数表示。它是PostgreSQL大部分系统表的主键。

类型oid表示一个对象标识符。 也有多个oid的别名类型:regproc,regprocedure, regoper, regoperator,regclass, regtype, regrole,regnamespace, regconfig, 和regdictionary

OID的别名类型除了特定的输入和输出例程之外没有别的操作。这些例程可以接受并显示系统对象的符号名,而不是类型oid使用的原始数字值。别名类型使查找对象的OID值变得简单。例如,要检查与一个表course有关的pg_attribute行,你可以写:

SELECT * FROM pg_attribute WHERE attrelid = 'course'::regclass;

OID 在系统表中通常是作为隐藏列存在的,它是以整个PostgreSQL数据库实例(Database Cluster)的范围内统一分配。因为只有四个字节,因此,在大型数据库中它并不足以提供数据库范围内的唯一性,甚至在一些大型的表中也无法提供表范围内的唯一性。

OID 在旧版本中还可以用于标识元组,对于没有主键,重复的行,此时 OID 作为唯一 ID,则可以根据它进行删除指定行数据。我们之前创建表时,default_with_oids 默认是关闭的。在老版本中执行 create table 语句时可以指定开启 OID。

create table foo (
    id integer,
    content text
) with oids;

不过从 Postgres 12 开始,删除了将 OID 用作表上的可选系统列。将无法再使用:

  • CREATE TABLE … WITH OIDS 命令
  • default_with_oids (boolean) 相容性设定

数据类型OID保留在Postgres 12中。您可以显式创建类型的列OID

XID

事务ID:

  • 由32位组成,这就有可能造成事务ID回卷的问题,具体参考文档
  • 顺序产生,依次递增
  • 没有数据变更,如INSERT、UPDATE、DELETE等操作,在当前会话中,事务ID不会改变

数据库系统中使用的数据类型为 xminxmax

  • xmin 存储的是产生这个元组的事务ID,可能是insert或者update语句
  • xmax 存储的是删除或者锁定这个元组的XID

简单示例如下:

select id, xmin, xmax from course;

当 PostgreSQL的XID 到达40亿,会造成溢出,从而新的XID 为0。而按照 PostgreSQL的MVCC 机制实现,之前的事务就可以看到这个新事务创建的元组,而新事务不能看到之前事务创建的元组,这违反了事务的可见性。具体参考文档

CID

CID 名为命令标识符,PG 每个表都包含一些系统字段,关于 CID 用到的数据类型为 cmax 和 cmin。

  • cmin:插入该元组的命令在插入事务中的命令标识(从0开始累加)
  • cmax:删除该元组的命令在插入事务中的命令标识(从0开始累加)

cmin和cmax用于判断同一个事务内的其他命令导致的行版本变更是否可见。如果一个事务内的所有命令严格顺序执行,那么每个命令总能看到之前该事务内的所有变更,不需要使用命令标识。

简单示例如下:

select id, xmin, xmax,cmin,cmax from course;

TID

TID 称为元组标识符(行标识符),一个元组ID是一个(块号,块内元组索引)对,它标识了行在它的表中的物理位置。

简单示例如下:

select ctid,id, xmin, xmax,cmin,cmax from course;

了解完上述四大标识符后,我们接着来学习 PostgreSQL 中数据到底是怎么存储的。

PostgreSQL数据存储

关于数据存储,我们都知道数据是存在数据库中的某个数据表中,每条数据记录对应数据表中的某一行,所以我们从上至下来查看各层次结构的数据存储。

PGDATA目录结构

PGDATA 是 PostgreSQL 用来存放所有数据的地方。

关于 PGDATA 的设置,可以先执行下述命令。

postgres=# show data_directory;
       data_directory        
-----------------------------
 /Library/PostgreSQL/12/data
(1 row)

接下来我们来看一下 PGDATA 文件夹中有哪些文件,首先打开命令行窗口,然后进入到上述目录。

MacBook-Pro 12 % cd /Library/PostgreSQL/12/data
cd: permission denied: /Library/PostgreSQL/12/data

如果遇到上述问题,则执行如下命令,尝试使用 sudo 模拟 postgresql 用户登录:

MacBook-Pro 12 % sudo -u postgres -i

The default interactive shell is now zsh.
To update your account to use zsh, please run `chsh -s /bin/zsh`.
For more details, please visit https://support.apple.com/kb/HT208050.

接着执行如下命令:

tree -FL 1 /Library/PostgreSQL/12/data

/Library/PostgreSQL/12/data
├── PG_VERSION
├── base/
├── current_logfiles
├── global/
├── log/
├── pg_commit_ts/
├── pg_dynshmem/
├── pg_hba.conf
├── pg_ident.conf
├── pg_logical/
├── pg_multixact/
├── pg_notify/
├── pg_replslot/
├── pg_serial/
├── pg_snapshots/
├── pg_stat/
├── pg_stat_tmp/
├── pg_subtrans/
├── pg_tblspc/
├── pg_twophase/
├── pg_wal/
├── pg_xact/
├── postgresql.auto.conf
├── postgresql.conf
├── postmaster.opts
└── postmaster.pid

介绍几个常见的文件夹:

  • base/:存储 database 数据(除了指定其他表空间的),子目录的名字为该数据库在 pg_database里的 OID。
  • postgresql.conf:postgresql 配置文件

database数据存储

上文提到在 base/ 目录下存放着每个 database 数据,其中文件名我们叫做 dboid。

由于 OID 是系统表的隐藏列,因此查看系统表中数据库对象的OID时,必须在SELECT语句中显式指定。我们进入 postgres 命令行窗口,执行下述命令:

postgres=# select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
 13635 | postgres
     1 | template1
 13634 | template0
 16395 | mydb
 16396 | dvdrental
 16399 | testdb
(6 rows)
select oid,relname from pg_class order by oid;

我们可以在 PGDATA 文件夹下的 base 目录下看到 oid

MacBook-Pro:base postgres$ ls /Library/PostgreSQL/12/data/base
1 13634 13635 16395 16396 16399

从上述内容可知 postgres 数据库相关的数据存储在 PGDATA/base/13635 目录里面。

table数据存储

上文我们定位到数据库的存储位置,接着我们来定位数据表的位置。

每一张表的数据(大部分)又是放在 $PGDATA/base/{dboid}/{relfilenode} 这个文件里面,relfilenode一般情况下和和tboid一致,但有些情况下也会变化,如TRUNCATEREINDEXCLUSTER以及某些形式的ALTER TABLE

CREATE TABLE public.cities (
 city varchar(80NOT NULL,
 "location" point NULL,
 CONSTRAINT cities_pkey PRIMARY KEY (city)
);

postgres=# select oid,relfilenode from pg_class where relname = 'cities';
  oid  | relfilenode 
-------+-------------
 16475 |       16475
(1 row)

insert into cities values('北京',null);
insert into cities values('上海',null);

truncate cities ;

postgres=# select oid,relfilenode from pg_class where relname = 'cities';
  oid  | relfilenode 
-------+-------------
 16475 |       16480
(1 row)

SELECT * FROM pg_attribute WHERE attrelid = 'course'::regclass;

除了上述 SQL 语句,我们还可以通过系统函数 pg_relation_filepath 来查看指定表的文件存储位置。

postgres=# select pg_relation_filepath('cities');
 pg_relation_filepath 
----------------------
 base/13635/16480
(1 row)

当查看 PGDATA/base/13635/ 目录时,会发现 16480 的文件夹,除此之外还会发现有些文件命名为 relfilenode_fsmrelfilenode_vmrelfilenode_init, 关于 16480 通常会有三种文件:16480、16480_fsm、16480_vm,分别是该数据库对应表的数据或索引文件、其对应的空闲空间映射文件、其对应的可见性映射文件。

如果数据文件过大,那么会怎么命名呢?

在表或者索引超过1GB之后,它就被划分成1GB大小的段。 第一个段的文件名和文件节点相同,随后的段被命名为 filenode.1filenode.2等等。这样的安排避免了在某些有文件大小限制的平台上的问题。

postgres=# create table bigdata(id int,name varchar(64));


postgres=# insert into bigdata select generate_series(1,20000000) as key, md5(random()::text);


postgres=# select pg_relation_filepath('bigdata');
 pg_relation_filepath 
----------------------
 base/13635/16486
(1 row)

#切换命令行界面
MacBook-Pro:base postgres$ ls 13635 |grep 16486
16486
16486.1
16486_fsm  

元组数据存储

上文我们提到 table 存储时,每个数据文件(堆文件、索引文件)可存储 1G 的容量,每个文件内部又是有若干个固定的页组成。页的默认大小为8192字节(8KB)。单个表文件中的这些页(Page)从0开始进行顺序编号,这些编号也称为“块编号(Block Numbers)”。如果第一页空间已经被数据填满,则 postgres 会立刻重新在文件末尾(即已填满页的后面)添加一个新的空白页,用于继续存储数据,一直持续这个过程,直到当前表文件大小达到 1GB位置。若文件达到1GB,则重新创建一个新的表文件,然后重复上面的这个过程。

每个页的内部又由一个页文件头(Page Header)、若干行指针(Line Pointer)、若干个元组数据(Heaple Tuple)组成。因为每个文件默认大小为 1GB,页大小为 8kb,则每个文件大概有 131072 个页。

首先来看一下页面结构。

image.png
image.png

其中:

  • page header: 24 字节,存储 page 的基本信息,包括 pd_lsn、pd_checksum、pd_special...

    pd_lsn: 存储最近改变该页面的xlog。
    pd_checksum:存储页面校验和。
    pd_lower,pd_upper:pd_lower指向行指针(line pointer)的尾部,pd_upper指向最后那个元组。
    pd_special: 索引页面中使用,它指向特殊空间的开头。
    pd_flags:用以设置位标志。
    pd_pagesize_version:页面大小及页面版本号。
    pd_prune_xid:可删除的旧 XID,如果没有则为零。
  • line pointe:行指针,4 bytes,形为 (offset, length) 的二元组,指向相关 tuple

  • heap tuple: 用来存储 row 的数据,注意元组是从页面的尾部向前堆积的,元组和行指针之间的是数据页的空闲空间。

  • 空白处:未申请空间,新的 line point 从其首端申请,新的 tuple 从其尾端申请

因此我们找 row 的数据需要知道哪一个 page,page 的哪一个 item, (page_index, item_index), 通常称它为 CTID(ItemPointer), 我们可以通过下面语句查看每一列的 CTID:

select ctid,* from course;

查询结果如下所示:

关于元组结构以及数据变化的详解讲解,可以参考本文

扩展

schema

PostgreSQL 除了默认的 public schema 之外,还有两个比较重的系统 schema:information_schema 与pg_catalog。

通过查看 pg_catalog.pg_namespace 来查看当前数据库中全部的 schema。

postgres=# select * from pg_catalog.pg_namespace ;
  oid  |      nspname       | nspowner |               nspacl                
-------+--------------------+----------+-------------------------------------
    99 | pg_toast           |       10 | 
 12314 | pg_temp_1          |       10 | 
 12315 | pg_toast_temp_1    |       10 | 
    11 | pg_catalog         |       10 | {postgres=UC/postgres,=U/postgres}
  2200 | public             |       10 | {postgres=UC/postgres,=UC/postgres}
 13335 | information_schema |       10 | {postgres=UC/postgres,=U/postgres}
(6 rows)

我们创建的表、视图、索引等默认都在 public 下。

information_schema 是方便用户查看表/视图/函数信息提供的,它大多是视图。

select * from information_schema."tables";

pg_catalog 包含系统表和所有内置数据类型、函数、操作符。pg_catalog 下有很多系统表,比如说 pg_classpg_attributepg_authid等,关于这些表的详细介绍可以参考本文

参考文献

PostgreSQL中的OID

SQL,Postgres OID,它们是什么,为什么有用?

PostgreSQL系统字段cmin和cmax详解

PgSQL · 特性分析 · MVCC机制浅析

PgSQL · 特性分析 · 事务ID回卷问题

干货 | PostgreSQL数据表文件底层结构布局分析

PostgreSQL目录结构之base目录

herish

2021/12/17  阅读:51  主题:默认主题

作者介绍

herish