postgresql之物理存储结构

为了更好的学习 postgresql,从底层数据如何存储可以更直观的了解其原理。 本文以 postgresql9.6 为介绍对象。

postgresql 的数据目录

在安装完数据库,我们需要用 initdb 命令来初始化数据库:

initdb -D /var/lib/postgres/data/

这样,所有数据都存放到 -D 指定的参数文件夹中, 当启动数据库服务时,我们需要将这个目录以参数的形式提供给启动数据库开启命令:

postgres -D /var/lib/postgres/data

这个目录就是在 postgresql 术语中经常碰到的 PGDATA 变量。

当然,有可能你是从客户端连接到数据库的,并不知道数据库开启时的命令, 你可以通过超级用户登陆 postgresql,运行下面语句来获取 PGDATA

show data_directory;

PGDATA 目录结构

首先我们来看一下 PGDATA 文件夹里面都有那些文件:

$ tree -FL 1 /var/lib/postgres/data/
/var/lib/postgres/data/
├── base/
├── global/
├── pg_clog/
├── 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_VERSION
├── pg_xlog/
├── postgresql.auto.conf
├── postgresql.conf
├── postmaster.opts
└── postmaster.pid

17 directories, 7 files

其中:

  • base/:存储 database 数据(除了指定其他表空间的)
  • global/:存储 cluster-wide 表格数据
  • pg_clog/:存储事务提交的状态数据
  • pg_commit_ts/:存储事务提交的时间戳数据
  • pg_dynshmem/:存储动态共享内存子系统的文件
  • pg_hba.conf:postgresql 配置文件
  • pg_ident.conf:postgresql 配置文件
  • pg_logical/:存储 logical decoding 状态数据
  • pg_multixact/:存储多重事务状态数据的子目录(用于共享的行锁)
  • pg_notify/:存储 LISTEN/NOTIFY 状态数据
  • pg_replslot/:存储 replication slot 数据
  • pg_serial/:存储 committed serializable transactions 信息
  • pg_snapshots/:存储导出的 snapshots
  • pg_stat/:存储统计子系统的永久文件
  • pg_stat_tmp/:存储统计子系统的临时文件
  • pg_subtrans/:存储子事务状态数据
  • pg_tblspc/:存储指向表空间的符号链接
  • pg_twophase/:存储 prepared transactions 的状态文件
  • PG_VERSION:存储 postgresql 数据库的主版本号
  • pg_xlog/:存储 WAL (Write Ahead Log) 文件
  • postgresql.auto.conf:存储由 ALTER SYSTEM 设置的配置
  • postgresql.conf:postgresql 配置文件
  • postmaster.opts:存储上一次启动该数据库时用到的命令
  • postmaster.pid:锁文件,只有在 postgresql 服务运行时存在,存储当前 postmaster 的 PID,PGDATA,postmaster 启动时间,端口号,Unix-domain socket 目录,第一个有效的 listen_address,共享内存的 segment ID

database 数据存储在哪里?

postgresql 数据库中,每一个对象都对应一个 OID 唯一标识, 而每一个 database 的目录名就存储在与其对应的 OID 目录中: PGDATA/base/oid, 我们可以查询每一个 database 的 OID:

select oid, datname from pg_database;
  oid  |  datname
-------+-----------
 12410 | postgres
     1 | template1
 12409 | template0
 17045 | test

进而我们知道,与 test 数据库相关的数据都存储在 PGDATA/base/17045 目录里面。

table 数据存储在哪里?

所有的 table 数据存储在所在数据库的目录里面,table 们是分开存放的, 每一个存储 table 的文件均用 pg_class.relfilenode 命名。

-- 创建 foo table
create table foo (
    id integer,
    content text
);


-- 查看 foo 的 relfilenode
select relfilenode from pg_class where relname = 'foo';
 relfilenode
-------------
       17238

因此我们就可以推断:foo 表格数据存储在 PGDATA/base/17405/17238 文件里面, 当然我们可以通过下面语句来查询存储相关文件:

select pg_relation_filepath('foo');
 pg_relation_filepath
----------------------
 base/17045/17238

为了避免有些文件系统不支持大文件,postgresql 限制标文件大小不能超过 1G, 因此,当表文件超过 1G 时,会另建一有尾缀文件 relfilenode.1relfilenode.2, 并以此类推。

当你查看 PGDATA/base/17405/ 目录时, 会发现有些文件命名为 relfilenode_fsmrelfilenode_vmrelfilenode_init, 它们是用来存储与表格相关的 free space map 、visibility map 和 unlogged table 的 initialization fork。

row 数据是怎么存储的?

每一个 table 数据存储为一个 page 数组,每一个 page 都是固定大小(通常为 8Kb), 它们在逻辑上是等价的,因此,一个记录 row 的数据可以存放到任何一个 page 中, 一个 page 的内部结构如下:

page structure

其中:

  • page header: 24 字节,存储 page 的基本信息,包括 checksum、free space start/end pointer、...
  • item:4 bytes,形为 (offset, length) 的二元组,指向相关 tuple
  • tuple: 用来存储 row 的数据
  • special:用来存储索引访问方法的特处数据,不同方法不一样,一般为空
  • 空白处:未申请空间,新的 item 从其首端申请,新的 tuple 从其尾端申请

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

SELECT ctid, * from foo;

postgresql 限制

从上面我们知道,postgresql 中, 每一 row 的大小是不能超过 8Kb 的,因为他们是被存储在 page 里的, 因此,postgresql 限制一个表格最多有 250~1600 列(根据列类型大小而定)。

那如果列的数据超过了 8Kb 会怎么处理呢? TOAST(The Oversized-Attribute Storage Technique)机制就是专门处理这种情况的, 它会将大数据列分割成几个小块单独存储在与表格相关的 TOAST 文件中,然后对这些小块进行索引。