PG_存储结构_物理存储结构

发表于 本文字数： 2.3k 阅读时长 ≈ 2 分钟

获取数据库与对应的文件名

1
SELECT * FROM pg_database;

​ image-20231214200931227

获取数据库对应的模式（命名空间）信息

1
SELECT * from pg_namespace;
image-20231214200849461

获取数据库对应表空间的信息

1
SELECT * from pg_tablespace;
image-20231214202222890

获取数据库用户对应信息

1
SELECT * FROM pg_user;
image-20231214202247885

获取对象的类型

1
2
\x
select * from pg_class where relname = 't1';

注意看relation的oid是547041，relfilenode也是547041，我们通过pg_relation_filepath(‘t1’),获取到的节点存储文件路径当前应该是547041，但是如果我们进行 vacuum full t1;或者truncate table t1;时，会发现relfilenode是会改变的，进而节点存储路径也是会变化的；

image-20231215152346772

获取对应表的文件存储路径

1
2
3
4
5
6
7
8
9
10
11
SELECT
  t.table_name,
  pg_relation_filepath(t.table_name)
FROM
  (
  SELECT
    table_schema || '.' || table_name AS table_name
  FROM
    information_schema.TABLES
  -- WHERE table_name = 't1'
  ) AS t;
image-20231214185715122

​

获取存储路径下的对应表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select * from (
  SELECT
    t.table_name,
    pg_relation_filepath(t.table_name) as table_path
  FROM
    (
    SELECT
      table_schema || '.' || table_name AS table_name
    FROM
      information_schema.TABLES
    -- WHERE table_name = 't1'
    ) AS t
  ) t1 
where t1.table_path like '%547041%';
image-20231214201648076

数据文件类别说明

  • relation_relfilenode or relation_relfilenode.number

    每张表创建后，会产生一个数据与表绑定relfilenode号的数据文件，当每个数据文件超过1GB后，就会产生新的数据文件，新的数据文件命名采用 relfilenode.number 的形式如 547041.1；

    当然，也有可能是index产生的数据文件；

    image-20231214235204735

  • *_vm文件

    Visual map 可见性映射表，比如update或者insert新的数据行后，mvcc会将老数据xmax_id写上当前的事务号等待autovacuum执行回收删除；但是在没删除前，需要_vm文件告诉查询，那些数据允许查询显示，那些不允许的就等着被删。

  • *_fsm文件

    FSM（free space map）用于存放表文件或索引文件的所有页的空间占用情况，Postgresql根据FSM确定PG下次insert或update的元素放在哪里，可以快速有效的利用可用空间，_fsm表自动创建无需指定。

    可以通过pg_freespacemap插件查询FSM：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    CREATE EXTENSION pg_freespacemap;
    drop table t1;
    create table t1(id serial, name varchar);
    insert into t1(name) select n from generate_series(1,1000) as n;
    -- 第一次查看空闲关系映射 发现都是0，因为插入的时候只要一个块不够了 就再申请一个，多一条也申请一个8k的块
    SELECT *,round(100*avail/8192,2) as "freespace radio" from pg_freespace('t1');
    VACUUM t1;-- 一定执行vacuum 否则数据不更新；
    -- 这时候发现插入了1000行后，最后一个块并没有被写满
    SELECT *,round(100*avail/8192,2) as "freespace radio" from pg_freespace('t1');
    -- 现在我们删除第一条记录
    delete from t1 where ID = 1;
    vacuum t1;
    -- 我们发现第一个写满的块出现了free space
    SELECT *,round(100*avail/8192,2) as "freespace radio" from pg_freespace('t1');

    -- 下次插入新数据的位置可能是块后空余空间 也可能是块内删除处的空间
    insert into t1(name) select n from generate_series(1,10) as n;
    vacuum t1;
    SELECT *,round(100*avail/8192,2) as "freespace radio" from pg_freespace('t1');

    image-20250616152410010 image-20250616152417063