PG中的blob cleanup

PG中的blob cleanup

PG提供了一个很好的BLOB接口，得到了广泛应用。然而最近我们遇到了各种客户遇到的问题，有必要对PG如何处理blob进行一次思考，尤其是BLOB清理。

PG的BLOB接口

PG中可以使用各种方式存储二进制数据，最简单的方式是定义一个“bytea”（=byte array）数据类型。也就是定义一个bytea列：

test=# CREATE TABLE t_image (id int, name text, image bytea);

CREATE TABLE

test=# \d t_image

Table "public.t_image"

Column | Type | Collation | Nullable | Default

-------+---------+-----------+----------+---------

id | integer | | |

name | text | | |

image | bytea | | |

如你所见，这是一个普通列，可以像普通列一样使用。唯一值得一提的是必须在SQL级别使用的编码。PG使用一个变量来配置此行为：

test=# SHOW bytea_output;

bytea_output

--------------

hex

(1 row)

bytea_output有两个值，hex:表示PG以16进制格式发送数据，escape:表示以8进制发送。除了每个字段最大大小限制1GB外，应用程序在这里不需要担心太多。

PG还有第二种接口处理二进制数据：BLOB接口。例子：

test=# SELECT lo_import('/etc/hosts');

lo_import

-----------

80343

(1 row)

这个例子中，“/etc/hosts”内容存储到了数据库。PG有这个数据的副本而不是到文件系统的链接。需要注意，数据库将返回新条目的OID（存储到了系统表），为了跟踪这些OID，一些开发人员执行以下操作：

test=# CREATE TABLE t_file (

id int,

name text,

object_id oid

);

CREATE TABLE

test=# INSERT INTO t_file

VALUES (1, 'some_name', lo_import('/etc/hosts'))

RETURNING *;

id | name | object_id

----+---------------+-----------

1 | some_name | 80350

(1 row)

1

INSERT 0 1

除了做下面动作，其他方面都还好：

test=# DELETE FROM t_file WHERE id = 1;

DELETE 1

问题是对象的OID被遗忘了，虽然删除了t_file里的一个记录，但是二进制数据仍然存在。PG通过pg_largeobject系统表存储二进制数据。所有的lo_functions为了处理下面事情会和该系统表交互：

test=# \x

Expanded display is on.

test=# SELECT * FROM pg_largeobject WHERE loid = 80350;

-[ RECORD 1 ]------------------------------------------

loid | 80350

pageno | 0

data | ##\012# Host Database\012#\012# localhost ...

问题是什么？原因很简单，数据库会膨胀，因此删除BLOB条目正确的方法是：

test=# \x

Expanded display is off.

test=#

test=# SELECT lo_unlink(80350);

lo_unlink

-----------

1

(1 row)

test=# SELECT * FROM pg_largeobject WHERE loid = 80350;

loid | pageno | data

------+--------+------

(0 rows)

如果忘记删除object，我们经常会遇到这样的事情：你将遭受膨胀带来的痛苦。如果使用BLOB接口，这是一个主要问题。

vacuumlo:清理死大对象

然而，一旦积累了成千上万的死blob，如何解决这个问题呢？可以通过vacuumlo工具：

test=# SELECT lo_import('/etc/hosts');

lo_import

-----------

80351

(1 row)

iMac:~ hs$ vacuumlo -h localhost -v test

Connected to database "test"

Checking object_id in public.t_file

Successfully removed 2 large objects from database "test".

可以看到，死大对象被清理了。

额外函数

PG提供了不止lo_import和lo_unlink函数：

test=# \df lo_*

List of functions

Schema | Name | Result data type | Argument data types | Type

------------+---------------+------------------+---------------------------+------

pg_catalog | lo_close | integer | integer | func

pg_catalog | lo_creat | oid | integer | func

pg_catalog | lo_create | oid | oid | func

pg_catalog | lo_export | integer | oid, text | func

pg_catalog | lo_from_bytea | oid | oid, bytea | func

pg_catalog | lo_get | bytea | oid | func

pg_catalog | lo_get | bytea | oid, bigint, integer | func

pg_catalog | lo_import | oid | text | func

pg_catalog | lo_import | oid | text, oid | func

pg_catalog | lo_lseek | integer | integer, integer, integer | func

pg_catalog | lo_lseek64 | bigint | integer, bigint, integer | func

pg_catalog | lo_open | integer | oid, integer | func

pg_catalog | lo_put | void | oid, bigint, bytea | func

pg_catalog | lo_tell | integer | integer | func

pg_catalog | lo_tell64 | bigint | integer | func

pg_catalog | lo_truncate | integer | integer, integer | func

pg_catalog | lo_truncate64 | integer | integer, bigint | func

pg_catalog | lo_unlink | integer | oid | func

(18 rows)

由于历史原因，还有2个函数没有遵循命名约定：lowrite和loread：

pg_catalog | loread | bytea | integer, integer | func

pg_catalog | lowrite | integer | integer, bytea | func

最后

PG的BLOB接口非常有用。美妙之处在于它完全是事务性的，因此二进制内容和元数据sync后不会丢失。

原文

https://www.cybertec-postgresql.com/en/blob-cleanup-in-postgresql/