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/