PostgreSQL 12优化小表存储空间

PostgreSQL 12优化小表存储空间

开始

如果您的数据库有大量小表,则可能会浪费大量存储空间。为了证明这一点,让我们创建一个包含单个记录的表:

CREATE  TABLE foo ( str text ) ;
INSERT  INTO FOO VALUES  ('A' ) ; 
VACUUM foo;
  • 现在让我们找出包含我们数据文件的路径,相对于数据目录:
SELECT pg_relation_filepath('foo');
 pg_relation_filepath
----------------------
 base/16384/16405
(1 ROW)
  • 查看与foo相关的信息
$ ls base/16384/16405*

base/16384/16405
base/16384/16405_fsm
base/16384/16405_vm

名为16405的文件是我们表的数据文件,但其他的呢?这些是辅助文件。
1. 16405_fsm是可用空间地图。它的作用是知道表中哪些页面有可用于插入记录的空间。
2. 16405_vm是可见性图。它的作用是知道哪些页面可能需要Vacuum或释放掉,以及在仅索引扫描期间必须访问哪些表页面。

  • 与此表相关的toast和toast索引
SELECT
pg_relation_filepath(c.reltoastrelid) AS toast_table_path,
pg_relation_filepath(i.indexrelid) AS toast_index_path
FROM pg_class c
LEFT OUTER JOIN pg_index i ON c.reltoastrelid=i.indrelid
WHERE c.relname = 'foo';

 toast_table_path | toast_index_path
------------------+------------------
 base/16384/16408 | base/16384/16410
(1 ROW)
  • 我们需要多少磁盘空间来存储我们的一条记录?
x
SELECT
pg_relation_size(c.oid, 'main') AS heap_size,
pg_relation_size(c.oid, 'fsm') AS fsm_size,
pg_relation_size(c.oid, 'vm') AS vm_size,
pg_relation_size(c.reltoastrelid) AS toast_table_size,
pg_relation_size(i.indexrelid) AS toast_index_size
FROM pg_class c
LEFT OUTER JOIN pg_index i ON c.reltoastrelid=i.indrelid
WHERE c.relname = 'foo';

-[ RECORD 1 ]----+------
heap_size        | 8192
fsm_size         | 24576
vm_size          | 8192
toast_table_size | 0
toast_index_size | 8192

要存储具有一个字符串值的记录,我们需要五个文件,总共48kB的空间!你可能在想“那又怎样? 我为什么要担心几KB呢?“ 好吧,考虑应用程序使用模式进行多租户的情况。这种情况可能有数千个模式,每个模式都有数百个小表,辅助文件占用的空间很快就变得非常高。

我们有什么办法可以减少消耗的空间吗?

我们可以通过使用列长度的约束来摆脱toast表及其toast索引:

CREATE TABLE foo_no_toast (str VARCHAR(500));
INSERT INTO foo_no_toast VALUES ('a');
VACUUM foo_no_toast;

SELECT
pg_relation_size(c.oid, 'main') AS heap_size,
pg_relation_size(c.oid, 'fsm') AS fsm_size,
pg_relation_size(c.oid, 'vm') AS vm_size,
pg_relation_size(c.reltoastrelid) AS toast_table_size,
pg_relation_size(i.indexrelid) AS toast_index_size
FROM pg_class c
LEFT OUTER JOIN pg_index i ON c.reltoastrelid=i.indrelid
WHERE c.relname = 'foo_no_toast';

-[ RECORD 1 ]----+------
heap_size        | 8192
fsm_size         | 24576
vm_size          | 8192
toast_table_size |
toast_index_size |

现在我们有3个文件,总共40 kB。

升级PostgreSQL 12

从PostgreSQL版本12开始,只有表达到4个页面或是32kB才会启用默认8kB的fsm空间。

CREATE TABLE foo_no_fsm (str text);
INSERT INTO foo_no_fsm VALUES ('a');
VACUUM foo_no_fsm;

SELECT
pg_relation_size(c.oid, 'main') AS heap_size,
pg_relation_size(c.oid, 'fsm') AS fsm_size,
pg_relation_size(c.oid, 'vm') AS vm_size,
pg_relation_size(c.reltoastrelid) AS toast_table_size,
pg_relation_size(i.indexrelid) AS toast_index_size
FROM pg_class c
LEFT OUTER JOIN pg_index i ON c.reltoastrelid=i.indrelid
WHERE c.relname = 'foo_no_fsm';

-[ RECORD 1 ]----+-----
heap_size        | 8192
fsm_size         | 0
vm_size          | 8192
toast_table_size | 0
toast_index_size | 8192

现在空间只有24k,是PG11的一半。

查看原文

文章浏览总量 1,690 次

要发表评论,您必须先登录