PostgreSQL的大对象以及空间使用 (1)

PostgreSQL对大列使用了一种很好的，非标准的TOAST机制，可以将其与Oracle中的扩展数据类型进行比较（顺便说一下，TOAST行可能更大）。

不过，传统的大对象，仍然被许多客户使用。

如果你不熟悉PostgreSQL中的大对象，请阅读此处(https://www.postgresql.org/docs/9.6/largeobjects.html)。对于TOAST，请阅读此处(https://www.postgresql.org/docs/9.6/storage-toast.html)。

在应用表中，大对象的列被定义为指向pg_largeobject表内数据块(chunks)的oid。

因为大对象是独立于引用它的表列创建的，所以当你从表中删除指向大对象的行时，大对象本身不会被删除。

此外，pg_largeobject被设计用于存储数据库中存在的所有大对象。这使得该表的管理维护对于数据库管理至关重要。（我们将在下一篇文章中看到它）

大对象是如何组织空间的？

我们将通过示例来展示。让我们从pg_largeobject为空的一个数据库开始:

lob_test=# select count(*) from pg_largeobject;
 count
-------
     0
(1 row)
 
lob_test=# vacuum full pg_largeobject;
VACUUM
 
lob_test=# select pg_total_relation_size('pg_largeobject');
 pg_total_relation_size
------------------------
                   8192
(1 row)

只有一个block。我们再来看看磁盘上对应的数据文件：

lob_test=# SELECT pg_relation_filepath('pg_largeobject');
 pg_relation_filepath
----------------------
 base/16471/16487
(1 row)

# ls -l base/16471/16487
-rw------- 1 postgres postgres 0 Jul 26 16:58 base/16471/16487

证据1:文件是空的。这意味着在表中有一些数据之前不会物理地创建第一个block(类似于Oracle中的延迟段创建，除非该文件已经存在)。

现在，让我们为我们的测试创建两个大小为1MB的文件，一个用零填充，另一个随机填充:

$ dd if=/dev/zero    of=/tmp/zeroes  bs=1024 count=1024
$ dd if=/dev/urandom of=/tmp/randoms bs=1024 count=1024
$ ls -l /tmp/zeroes /tmp/randoms
-rw-r--r-- 1 postgres postgres 1048576 Jul 26 16:56 /tmp/randoms
-rw-r--r-- 1 postgres postgres 1048576 Jul 26 16:23 /tmp/zeroes

让我们导入用0填充的文件：

lob_test=# lo_import '/tmp/zeroes';
lo_import 16491
lob_test=# select count(*) from pg_largeobject_metadata;
 count
-------
     1
(1 row)

lob_test=# select count(*) from pg_largeobject;
 count
-------
   512
(1 row)

大对象被切分成大小为每个2048bytes的chunk，因此一共有512个。那物理大小呢？

lob_test=# select pg_relation_size('pg_largeobject');
 pg_total_relation_size
------------------------
                  40960
(1 row)


bash-4.1$ ls -l 16487*
-rw------- 1 postgres postgres 40960 Jul 26 17:18 16487

只有40k。这就意味着chunk被压缩了(类似TOAST的page)。PostgreSQL使用了pglz_compress函数，其算法在源代码src/common/pg_lzcompress.c中做了很好的解释。

当我们导入随机填充的文件时会发生什么?

lob_test=# lo_import '/tmp/randoms';
lo_import 16492

lob_test=# select count(*) from pg_largeobject where loid=16492;
 count
-------
   512
(1 row)

lob_test=# select pg_relation_size('pg_largeobject');
 pg_relation_size
------------------
          1441792
(1 row)

$ ls -l 16487
-rw------- 1 postgres postgres 1441792 Jul 26 17:24 16487

段增加了超过1Mb!准确地说，1441792-40960 = 1400832字节。为什么?

这个大对象被再次拆分为512个chunk，每个都有2048个字节，PostgreSQL再次尝试压缩它们。但是，因为一个随机字符串不能被压缩，所以段仍然(平均)是2048字节大。

现在，一个数据库块的大小是8192字节。如果我们减去block header的大小，就没有足够的空间容纳4个2048字节的chunk。每个块将只包含3个未压缩的chunk。(这里block和chunk别混淆)

因此，512个chunk将分布在171个block上(CEIL(512/3.0))，得到:

lob_test=# select ceil(1024*1024/2048/3.0)*8192;
 ?column?
----------
  1400832
(1 row)

1400832 bytes!

根据可以应用于大对象的压缩率，我们可以期望在pg_largeobject表中使用更多或更少的空间。

原文：http://www.ludovicocaldara.net/dba/pgsql-lo-space-usage-part-1/