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

2020-10-12 08:58 abce 阅读(585) 评论(1) 编辑收藏举报

如果没有关于vacuumlo的最后一篇文章，本系列将是不完整的。

在上一篇文章中，我们已经看到大对象被分成每个元组，每个元组包含2048个字节，每个chunk的行为与常规元组非常相似。

注意：在PostgreSQL中，借助TOAST技术，可以将大量数据与表一起存储。

大对象没有插入应用表中，但是以不同的方式受到威胁。使用大对象的应用常具有一个包含OID类型的列的表。当应用程序创建新的大对象时，将为其分配一个新的OID号，并将此号插入到应用表中。

现在，对于来自其他RDBMS（例如Oracle）的人来说，这是一个常见错误，认为当删除该行的引用，大对象会自动取消链接。事实并非如此，我们需要将其与应用明确断开链接(unlink)。

让我们从一个简单的示例开始，从一个空的pg_largeobject表开始：

lob_test=# vacuum full pg_largeobject;
VACUUM
lob_test=# select count(*) from pg_largeobject_metadata;
 count
-------
     0
(1 row)
 
lob_test=# select pg_relation_size('pg_largeobject')/8192 as pages;
 pages
-------
     0
(1 row)

让我们插入一个新的LOB并在表t中引用它:

lob_test=# CREATE TABLE t (id integer, file oid);
CREATE TABLE
lob_test=# \lo_import /tmp/zeroes
lo_import 16546
lob_test=# INSERT INTO t VALUES  (1, 16546);
INSERT 0 1
 
lob_test=# select generate_series as pageno,
  (select count(*) from heap_page_items(get_raw_page('pg_largeobject',generate_series))
  where t_infomask::bit(16) & x'0800'::bit(16) = x'0800'::bit(16)) from generate_series(0,4);
 pageno | count
--------+-------
      0 |   107
      1 |   107
      2 |   107
      3 |   107
      4 |    84

另一个：

lob_test=# \lo_import /tmp/zeroes
lo_import 16547
lob_test=# INSERT INTO t VALUES  (2, 16547);
INSERT 0 1
 
lob_test=# select generate_series as pageno,
  (select count(*) from heap_page_items(get_raw_page('pg_largeobject',generate_series))
  where t_infomask::bit(16) & x'0800'::bit(16) = x'0800'::bit(16)) from generate_series(0,9);
 pageno | count
--------+-------
      0 |   107
      1 |   107
      2 |   107
      3 |   107
      4 |   107
      5 |   107
      6 |   107
      7 |   107
      8 |   107
      9 |    61
(10 rows)
 
lob_test=# select * from t;
 id | file
----+-------
  1 | 16546
  2 | 16547
(2 rows)

如果我们删除第一个，它的LOB块仍然存在，且有效:

lob_test=# DELETE FROM t WHERE id=1;
DELETE 1
lob_test=# select * from t;
 id | file
----+-------
  2 | 16547
(1 row)
 
lob_test=# select generate_series as pageno,
  (select count(*) from heap_page_items(get_raw_page('pg_largeobject',generate_series))
  where t_infomask::bit(16) & x'0800'::bit(16) = x'0800'::bit(16)) from generate_series(0,9);
 pageno | count
--------+-------
      0 |   107
      1 |   107
      2 |   107
      3 |   107
      4 |   107
      5 |   107
      6 |   107
      7 |   107
      8 |   107
      9 |    61
(10 rows)

如果我们想清理掉LOB，我们必须解除它的链接，要么显式地解除，要么在删除应用程序表中的一条记录时使用触发器解除LOB的链接。

另一种方法是使用PostgreSQL中包含的二进制vacuumlo。

它扫描pg_largeobject_metadata并搜索具有OID列的表，查找是否有对lob的引用。未被引用的LOB被解除链接。

注意:这意味着，如果您使用其他方法引用OID列以外的lob，那么vacuumlo可能会断开仍然需要的lob的链接!

# vacuumlo -U postgres lob_test
 
# p_ lob_test
psql.bin (9.6.2)
Type "help" for help.
 
lob_test=# select generate_series as pageno,
  (select count(*) from heap_page_items(get_raw_page('pg_largeobject',generate_series))
  where t_infomask::bit(16) & x'0800'::bit(16) = x'0800'::bit(16)) from generate_series(0,9);
 pageno | count
--------+-------
      0 |     0
      1 |     0
      2 |     0
      3 |     0
      4 |    23
      5 |   107
      6 |   107
      7 |   107
      8 |   107
      9 |    61
(10 rows)

事实上，vacuumlo已经解除了对第一个LOB的链接，但是删除的元组在执行vacuum之前不会被释放:

lob_test=# \lo_import /tmp/zeroes
lo_import 16551
lob_test=# INSERT INTO t VALUES  (3, 16551);
INSERT 0 1
lob_test=# select generate_series as pageno,
  (select count(*) from heap_page_items(get_raw_page('pg_largeobject',generate_series))
  where t_infomask::bit(16) & x'0800'::bit(16) = x'0800'::bit(16)) from generate_series(0,14);
 pageno | count
--------+-------
      0 |     0
      1 |     0
      2 |     0
      3 |     0
      4 |    23
      5 |   107
      6 |   107
      7 |   107
      8 |   107
      9 |   107
     10 |   107
     11 |   107
     12 |   107
     13 |   107
     14 |    38
(15 rows)
 
lob_test=# vacuum pg_largeobject;
VACUUM
lob_test=# \lo_import /tmp/zeroes
lo_import 16552
lob_test=# INSERT INTO t VALUES  (4, 16552);
INSERT 0 1
lob_test=# select generate_series as pageno,
  (select count(*) from heap_page_items(get_raw_page('pg_largeobject',generate_series))
  where t_infomask::bit(16) & x'0800'::bit(16) = x'0800'::bit(16)) from generate_series(0,14);
 pageno | count
--------+-------
      0 |   107
      1 |   107
      2 |   107
      3 |   107
      4 |   107
      5 |   107
      6 |   107
      7 |   107
      8 |   107
      9 |   107
     10 |   107
     11 |   107
     12 |   107
     13 |   107
     14 |    38
(15 rows)