postgresql 的table、index物理存储

postgresql 是使用文件系统存储数据的，有时需要找表及索引对应的磁盘文件，就必须了解以下知识点。

非toast情况

zabbix=# create table tmp_t0(c0 varchar(100),c1 varchar(100), c2 varchar(100));
CREATE TABLE
zabbix=# 
zabbix=# create index idx_tmp_t0 on tmp_t0(c0);
CREATE INDEX
zabbix=# 
zabbix=# insert into tmp_t0 select id::varchar,md5(id::varchar),md5(md5(id::varchar)) from generate_series(1,100000) as id ;
INSERT 0 100000
zabbix=# 
zabbix=# delete from tmp_t0 where c0 > '1';
DELETE 99999

查看表对应的操作系统文件.

zabbix=# select pg_relation_filenode('tmp_t0'),pg_relation_filepath('tmp_t0');
 pg_relation_filenode | pg_relation_filepath 
----------------------+----------------------
                24583 | base/24579/24583
(1 row)

查看索引对应的操作系统文件.

zabbix=# select pg_relation_filenode('idx_tmp_t0'),pg_relation_filepath('idx_tmp_t0');
 pg_relation_filenode | pg_relation_filepath 
----------------------+----------------------
                24588 | base/24579/24588
(1 row)

使用 pg_class 查看

zabbix=# select pc.oid,pc.relname,pc.relfilenode from pg_class pc where 1=1 and pc.relname in ('tmp_t0','idx_tmp_t0');
  oid  |  relname   | relfilenode 
-------+------------+-------------
 24583 | tmp_t0     |       24583
 24588 | idx_tmp_t0 |       24588
(2 rows)

操作系统查看

$ ls -l |grep -i 24583; ls -l |grep -i 24588;
-rw------- 1 postgres postgres 10117120 Sep 19 11:18 24583
-rw------- 1 postgres postgres    24576 Sep 19 11:18 24583_fsm
-rw------- 1 postgres postgres     8192 Sep 19 11:20 24583_vm
-rw------- 1 postgres postgres  2260992 Sep 19 11:25 24588

这个时候做个truncate操作

zabbix=# truncate table tmp_t0;
TRUNCATE table
zabbix=#
zabbix=# insert into tmp_t0 select id::varchar,md5(id::varchar),md5(md5(id::varchar)) from generate_series(1,100000) as id ;
INSERT 0 100000

依次查看

zabbix=# select pg_relation_filenode('tmp_t0'),pg_relation_filepath('tmp_t0');
 pg_relation_filenode | pg_relation_filepath 
----------------------+----------------------
                24589 | base/24579/24589
(1 row)

zabbix=# select pg_relation_filenode('idx_tmp_t0'),pg_relation_filepath('idx_tmp_t0');
 pg_relation_filenode | pg_relation_filepath 
----------------------+----------------------
                24590 | base/24579/24590
(1 row)

zabbix=# select pc.oid,pc.relname,pc.relfilenode from pg_class pc where 1=1 and pc.relname in ('tmp_t0','idx_tmp_t0');
  oid  |  relname   | relfilenode 
-------+------------+-------------
 24583 | tmp_t0     |       24589
 24588 | idx_tmp_t0 |       24590
(2 rows)

$ ls -l |grep -i 24583; ls -l |grep -i 24588;
-rw------- 1 postgres postgres        0 Sep 19 11:33 24583
-rw------- 1 postgres postgres        0 Sep 19 11:33 24588
$ ls -l |grep -i 24589; ls -l |grep -i 24590;
-rw------- 1 postgres postgres 10117120 Sep 19 11:35 24589
-rw------- 1 postgres postgres    24576 Sep 19 11:35 24589_fsm
-rw------- 1 postgres postgres  3932160 Sep 19 11:35 24590

之后再查看 old relfilenode 时已经消失不见了

$ ls -l |grep -i 24583; ls -l |grep -i 24588;
$

总结如下：
1、create table、create index 时，pg_class 的 oid 与 relfilenode 相同。
1、truncate table 后，table与index的oid均没有发生变化，但是 relfilenode 发生了变化。

toast 情况

插入4千万行数据，让tmp_t0在磁盘的大小大于1G

zabbix=# insert into tmp_t0 select id::varchar,md5(id::varchar),md5(md5(id::varchar)) from generate_series(1,40000000) as id ;
INSERT 0 40000000
zabbix=#
zabbix=# create index idx_tmp_t0_1 on tmp_t0(c1);
CREATE index
zabbix=# select pc.oid,pc.relname,pc.relfilenode from pg_class pc where 1=1 and pc.relname in ('tmp_t0','idx_tmp_t0','idx_tmp_t0_1');
  oid  |   relname    | relfilenode 
-------+--------------+-------------
 24583 | tmp_t0       |       24589
 24588 | idx_tmp_t0   |       24590
 24599 | idx_tmp_t0_1 |       24599
(3 rows)

$ ls -l |grep -i 24589; ls -l |grep -i 24590;ls -l|grep -i 24599;
-rw------- 1 postgres postgres 1073741824 Sep 19 12:15 24589
-rw------- 1 postgres postgres 1073741824 Sep 19 12:17 24589.1
-rw------- 1 postgres postgres 1073741824 Sep 19 12:19 24589.2
-rw------- 1 postgres postgres 1073741824 Sep 19 12:23 24589.3
-rw------- 1 postgres postgres   81788928 Sep 19 12:25 24589.4
-rw------- 1 postgres postgres    1097728 Sep 19 12:14 24589_fsm
-rw------- 1 postgres postgres 1073741824 Sep 19 12:14 24590
-rw------- 1 postgres postgres  332496896 Sep 19 12:14 24590.1
-rw------- 1 postgres postgres 1073741824 Sep 19 12:24 24599
-rw------- 1 postgres postgres 1073741824 Sep 19 12:24 24599.1
-rw------- 1 postgres postgres  220487680 Sep 19 12:24 24599.2

下面是查看表及索引对应的存储文件

select pt.schemaname||'.'||pt.tablename,pg_relation_filepath(pt.schemaname||'.'||pt.tablename),
       pg_table_size(pt.schemaname||'.'||pt.tablename),
       pg_relation_size(pt.schemaname||'.'||pt.tablename),
       pg_total_relation_size(pt.schemaname||'.'||pt.tablename),
       pi.schemaname||'.'||pi.indexname,pg_relation_filepath(pi.schemaname||'.'||pi.indexname),
       pg_relation_size(pi.schemaname||'.'||pi.indexname),--指定的单个索引
       pg_indexes_size(pi.schemaname||'.'||pi.tablename) --表上的所有索引
 from pg_tables pt
      left outer join pg_indexes pi 
                   on pt.schemaname||'.'||pt.tablename = pi.schemaname||'.'||pi.tablename
where 1=1
  and pt.schemaname='public'
  and pt.tablename='tmp_t0'
;

   ?column?    | pg_relation_filepath | pg_table_size | pg_relation_size | pg_total_relation_size |      ?column?       | pg_relation_filepath | pg_relation_size | pg_indexes_size 
---------------+----------------------+---------------+------------------+------------------------+---------------------+----------------------+------------------+-----------------
 public.tmp_t0 | base/24579/24589     |    4377853952 |       4376756224 |             8152064000 | public.idx_tmp_t0   | base/24579/24590     |       1406238720 |      3774210048
 public.tmp_t0 | base/24579/24589     |    4377853952 |       4376756224 |             8152064000 | public.idx_tmp_t0_1 | base/24579/24599     |       2367971328 |      3774210048
(2 rows)

参考文档：

表 9-83. 数据库对象尺寸函数

名称                                           返回类型         描述 
pg_column_size(any) 						   int 			存储一个特定值（可能压缩过）所需的字节数 
pg_database_size(oid)                          bigint       指定 OID 的数据库使用的磁盘空间 
pg_database_size(name)                         bigint       指定名称的数据库使用的磁盘空间 
pg_indexes_size(regclass)                      bigint       附加到指定表的索引所占的总磁盘空间 
pg_relation_size(relation regclass, fork text) bigint       指定表或索引的指定分叉（'main'、'fsm'、'vm'或'init'）使用的磁盘空间 
pg_relation_size(relation regclass)            bigint       pg_relation_size(..., 'main')的简写 
pg_size_bytes(text)                            bigint       把人类可读格式的带有单位的尺寸转换成字节数 
pg_size_pretty(bigint)                         text         将表示成一个 64位整数的字节尺寸转换为带尺寸单位的人类可读格式 
pg_size_pretty(numeric)                        text         将表示成一个数字值的字节尺寸转换为带尺寸单位的人类可读格式 
pg_table_size(regclass)                        bigint       被指定表使用的磁盘空间，排除索引（但包括 TOAST、空闲空间映射和可见性映射） 
pg_tablespace_size(oid)                        bigint       指定 OID 的表空间使用的磁盘空间 
pg_tablespace_size(name)                       bigint       指定名称的表空间使用的磁盘空间 
pg_total_relation_size(regclass)               bigint       指定表所用的总磁盘空间，包括所有的索引和TOAST数据

pg_column_size 显示用于存储任意独立数据值的空间。

pg_total_relation_size 接受一个表或 TOAST 表的 OID 或名称，并返回该表所使用的总磁盘空间，包括所有相关的索引。这个函数等价于pg_table_size + pg_indexes_size。

pg_table_size 接受一个表的 OID 或名称，并返回该表所需的磁盘空间，但是排除索引（TOAST 空间、空闲空间映射和可见性映射包含在内）

pg_indexes_size 接受一个表的 OID 或名称，并返回附加到该表的所有索引所使用的全部磁盘空间。

pg_database_size 和 pg_tablespace_size 接受一个数据库或表空间的 OID 或名称，并且返回它们所使用的全部磁盘空间。要使用pg_database_size，你必须具有在指定数据库上的 CONNECT权限（默认会被授予）。要使用pg_tablespace_size，你必须具有指定表空间上的CREATE权限，除非它是当前数据库的默认表空间。

pg_relation_size 接受一个表、索引或 TOAST 表的 OID 或者名称，并且返回那个关系的一个分叉所占的磁盘空间的字节尺寸（注意对于大部分目的，使用更高层的函数pg_total_relation_size或者pg_table_size 会更方便，它们会合计所有分叉的尺寸）。如果只得到一个参数，它会返回该关系的主数据分叉的尺寸。提供第二个参数可以指定要检查哪个分叉：

'main’返回该关系主数据分叉的尺寸。

'fsm’返回与该关系相关的空闲空间映射（见第 65.3 节）的尺寸。

'vm’返回与该关系相关的可见性映射（见第 65.4 节）的尺寸。

'init’返回与该关系相关的初始化分叉（如果有）的尺寸。

pg_size_pretty 可以用于把其它函数之一的结果格式化成一种人类易读的格式，可以根据情况使用 KB、MB、GB 或者 TB。

pg_size_bytes 可以被用来从人类可读格式的字符串得到其中所表示的字节数。其输入可能带有的单位包括字节、kB、MB、GB 或者 TB，并且对输入进行解析时是区分大小写的。如果没有指定单位，会假定单位为字节。

注意: 函数 pg_size_pretty 和 pg_size_bytes 所使用的单位 kB、MB、GB 和 TB 是用 2 的幂而不是 10 的幂来定义，因此 1kB 是 1024 字节， 1MB 是10242 = 1048576字节，以此类推
上述操作表和索引的函数接受一个 regclass 参数，它是该表或索引在 pg_class系统目录中的 OID。你不必手工去查找该 OID，因为 regclass数据类型的输入转换器会为你代劳。只写包围在单引号内的表名，这样它看起来像一个文字常量。为了与普通SQL名称的处理相兼容，该字符串将被转换为小写形式，除非其中在表名周围包含双引号。

如果一个 OID 不表示一个已有的对象并且被作为参数传递给了上述函数，将会返回 NULL。

posted @ 2018-09-19 11:54 peiybpeiyb 阅读(344) 评论(0) 编辑收藏举报

刷新页面返回顶部

peiyb 专注于数据库

postgresql 的table、index物理存储

非toast情况

toast 情况