PostgreSQL创建表和索引，磁盘会发生什么情况？

版权声明：本文为Buddy Yuan原创文章，未经允许不得转载。原文地址： PostgreSQL创建表和索引，磁盘会发生什么情况？

当我们在PostgreSQL中创建一个表或一个索引时，磁盘上的文件是怎么创建的，我们来研究一下？

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

首先我们来创建一个表。接下来我们使用pg_relation_filepath函数，获取到该表对应的文件名。

postgres=# create table t1(name varchar(30));
CREATE TABLE
postgres=# select pg_relation_filepath('t1');
 pg_relation_filepath 
----------------------
 base/13806/16384
(1 row)

或者我们也可以使用oid2name程序，指定数据库名字和表名即可。

-bash-4.2$ ./oid2name 
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  13806       postgres  pg_default
  13805      template0  pg_default
      1      template1  pg_default

-bash-4.2$ ./oid2name -d postgres -t t1
From database "postgres":
  Filenode  Table Name
----------------------
     16384          t1

此时我们可以查看该文件.

-bash-4.2$ ls -lrt $PGDATA/base/13806/16384
-rw-------. 1 postgres postgres 0 Oct 24 11:18 /var/lib/pgsql/10/data/base/13806/16384

可以看到文件字节大小是0，此时并没有任何数据，没有以“_fsm”后缀的Free Space Mapping文件和以”vm”为后缀的visibility map文件。

-bash-4.2$ ls -lrt $PGDATA/base/13806/16384*
-rw-------. 1 postgres postgres 0 Oct 24 11:18 /var/lib/pgsql/10/data/base/13806/16384

接下来我们试下在这个表上创建索引会发生什么情况。

postgres=# create index idx1 on t1 (name);
CREATE INDEX
postgres=# select pg_relation_filepath('idx1');
 pg_relation_filepath 
----------------------
 base/13806/16387
(1 row)

此时可以发现创建的文件是有大小的。可以看到这个paag是一个元数据的page。

-bash-4.2$ ls -lrt $PGDATA/base/13806/16387*
-rw-------. 1 postgres postgres 8192 Oct 24 15:38 /var/lib/pgsql/10/data/base/13806/16387

我们可以通过创建扩展包pageinspect工具来研究下page（物理文件的单位，默认大小为8K）。

postgres=# create extension pageinspect;
CREATE EXTENSION
postgres=# SELECT * FROM bt_metap('idx1');
 magic  | version | root | level | fastroot | fastlevel 
--------+---------+------+-------+----------+-----------
 340322 |       2 |    0 |     0 |        0 |         0
(1 row)

postgres=# SELECT * FROM bt_page_stats('idx1',0);
ERROR:  block 0 is a meta page

如果我们在插入一条数据看看，可以看到文件有了大小。用strings可以看到里面的内容。

postgres=# insert into t1 values('buddy');
INSERT 0 1

-bash-4.2$ ls -lrt $PGDATA/base/13806/16384*
-rw-------. 1 postgres postgres 8192 Oct 24 15:53 /var/lib/pgsql/10/data/base/13806/16384

-bash-4.2$ strings /var/lib/pgsql/10/data/base/13806/16384
buddy

此时还是没有Free Space Mapping文件和visibility map文件。需要vacuum一下。此时文件的大小变成了8k。

postgres=# vacuum t1;
VACUUM

-bash-4.2$ ls -lrt $PGDATA/base/13806/16384*
-rw-------. 1 postgres postgres  8192 Oct 24 15:54 /var/lib/pgsql/10/data/base/13806/16384
-rw-------. 1 postgres postgres  8192 Oct 24 15:57 /var/lib/pgsql/10/data/base/13806/16384_vm
-rw-------. 1 postgres postgres 24576 Oct 24 15:57 /var/lib/pgsql/10/data/base/13806/16384_fsm