合 PG中的大对象lo介绍和pg_largeobject表的清理
Tags: PGpg_largeobjectpg_largeobject_metadatavacuumlo大对象
简介
PostgreSQL对大对象有很多的存储和使用方式,较常见的比如bytea字段存储、Toast和lo方式等。
PostgreSQL具有大对象的功能,它提供了对于存储在一个特殊大对象结构中的用户数据的流式访问。对于那些大得无法以一个整体处理的数据值 ,流式访问非常有用。
所有的大对象都存在一个名为pg_largeobject的系统表中。每一个大对象还在系统表pg_largeobject_metadata中有一个对应的项。大对象可以通过类似于标准文件操作的读/写API来进行创建、修改和删除。
大对象源于large object的翻译,简称lo,与普通的整型、时间类型等不同,它实际存储一些文件、图片等较大尺寸的对象。
通常程序或者一些开源工具(比如开源的cas单点登录系统等),存储大对象的方式是lo,通常lo的性能会比bytea要好一点,但在删除用户表时并不会将系统表(pg_largeobject和pg_largeobject_metadata)删除,或者创建类似的表时不会创建同步的trigger,所以需要DBA维护一下,否则会导致系统的大对象表越来越大。
PostgreSQL也支持一种称为“TOAST(存储大对象数据的一种技术)”的存储系统,每一个表都有专属的二级存储区域。这使得大对象功能显得有些陈旧。但是大对象功能仍然有一个优势是能够支持高达4TB的值,而TOAST域只能支持最大1GB。此外,读写一个大对象的片段会很高效,但是大部分在TOAST域上的操作都将把整个值作为一个单元进行读或写。
麦老师曾经维护的一套系统里,pg_largeobject达到了500GB的大小,直接把磁盘撑爆了,如下:
内置的lo系统函数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | db1=# \df lo_* 函数列表 架构模式 | 名称 | 结果数据类型 | 参数数据类型 | 类型 ------------+---------------+--------------+---------------------------+------ pg_catalog | lo_close | integer | integer | 函数 pg_catalog | lo_creat | oid | integer | 函数 pg_catalog | lo_create | oid | oid | 函数 pg_catalog | lo_export | integer | oid, text | 函数 pg_catalog | lo_from_bytea | oid | oid, bytea | 函数 pg_catalog | lo_get | bytea | oid | 函数 pg_catalog | lo_get | bytea | oid, bigint, integer | 函数 pg_catalog | lo_import | oid | text | 函数 pg_catalog | lo_import | oid | text, oid | 函数 pg_catalog | lo_lseek | integer | integer, integer, integer | 函数 pg_catalog | lo_lseek64 | bigint | integer, bigint, integer | 函数 pg_catalog | lo_open | integer | oid, integer | 函数 pg_catalog | lo_put | void | oid, bigint, bytea | 函数 pg_catalog | lo_tell | integer | integer | 函数 pg_catalog | lo_tell64 | bigint | integer | 函数 pg_catalog | lo_truncate | integer | integer, integer | 函数 pg_catalog | lo_truncate64 | integer | integer, bigint | 函数 pg_catalog | lo_unlink | integer | oid | 函数 (18 行记录) db1=# |
测试
1.创建大对象,导入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | db_big=# select lo_create(1); lo_create ----------- 1 (1 row) -- 创建一个OID是1但没数据的大对象,该值被插入pg_largeobject_metadata,OID可以随便指定,但不能与大对象元数据表的OID有重复 db_big=# select oid,* from pg_largeobject_metadata; oid | lomowner | lomacl -----+----------+-------- 1 | 10 | (1 row) -- 存储实际对象数据的表是空的 db_big=# select count(1) from pg_largeobject; count ------- 0 (1 row) db_big=# create table tab_big(vname text,souroid oid); CREATE TABLE db_big=# insert into tab_big values('passwd list',lo_import('/etc/passwd')); INSERT 0 1 db_big=# select * from tab_big ; vname | souroid -------------+--------- passwd list | 20063 (1 row) db_big=# select oid,* from pg_largeobject_metadata ; oid | lomowner | lomacl -------+----------+-------- 1 | 10 | 20063 | 10 | (2 rows) -- 实际存储的数据 db_big=# select * from pg_largeobject; loid | pageno | data ------------------------------------------------------------------------------------------------- 20063 | 0 | \x726f6f743a783a303a303a726f6f743a2f726f6f743a2f62696e2f626173680a62696e3a783a313a313a62696e3a2f62696e3a2f7362696e2f6e6f6c6f67696e0a6461656d6f6e3a783a323a323a6461656d6f6e3a2f7362696e3a2f7362696e2f6e6f6c6f67696e0a61646d3 a783a333a343a61646d3a2f7661722f61646d3a2f7362696e2f6e6f6c6f67696e0a6c703a783a343a373a6c703a2f7661722f73706f6f6c2f6c70643a2f7362696e2f6e6f6c6f67696e0a73796e633a783a353a303a73796e633a2f7362696e3a2f62696e2f73796e630a73687574646f776e3a783a36 3a303a73687574646f776e3a2f7362696e3a2f7362696e2f73687574646f776e0a68616c743a783a373a303a68616c743a2f7362696e3a2f7362696e2f68616c740a6d61696c3a783a383a31323a6d61696c3a2f7661722f73706f6f6c2f6d61696c3a2f7362696e2f6e6f6c6f67696e0a757563703a7 83a31303a31343a757563703a2f7661722f73706f6f6c2f757563703a2f7362696e2f6e6f6c6f67696e0a6f70657261746f723a783a31313a303a6f70657261746f723a2f726f6f743a2f7362696e2f6e6f6c6f67696e0a67616d65733a783a31323a3130303a67616d65733a2f7573722f67616d6573 3a2f7362696e2f6e6f6c6f67696e0a676f706865723a783a31333a33303a676f706865723a2f7661722f676f706865723a2f7362696e2f6e6f6c6f67696e0a6674703a783a31343a35303a46545020557365723a2f7661722f6674703a2f7362696e2f6e6f6c6f67696e0a6e6f626f64793a783a39393 a39393a4e6f626f64793a2f3a2f7362696e2f6e6f6c6f67696e0a646275733a783a38313a38313a53797374656d206d657373616765206275733a2f3a2f7362696e2f6e6f6c6f67696e0a766373613a783a36393a36393a7669727475616c20636f6e736f6c65206d656d6f7279206f776e65723a2f64 65763a2f7362696e2f6e6f6c6f67696e0a7361736c617574683a783a3439393a37363a225361736c61757468642075736572223a2f7661722f656d7074792f7361736c617574683a2f7362696e2f6e6f6c6f67696e0a706f73746669783a783a38393a38393a3a2f7661722f73706f6f6c2f706f73746 669783a2f7362696e2f6e6f6c6f67696e0a61766168693a783a37303a37303a4176616869206d444e532f444e532d534420537461636b3a2f7661722f72756e2f61766168692d6461656d6f6e3a2f7362696e2f6e6f6c6f67696e0a737368643a783a37343a37343a50726976696c6567652d73657061 7261746564205353483a2f7661722f656d7074792f737368643a2f7362696e2f6e6f6c6f67696e0a706f7374677265733a783a3530303a3530303a3a2f686f6d652f706f7374677265733a2f62696e2f626173680a6170616368653a783a34383a34383a4170616368653a2f7661722f7777773a2f736 2696e2f6e6f6c6f67696e0a7a61626269783a783a3530313a3530313a3a2f686f6d652f7a61626269783a2f62696e2f626173680a -- 指定oid存储 db_big=# insert into tab_big values('passwd list',lo_import('/etc/passwd',123)); INSERT 0 1 db_big=# select oid,* from pg_largeobject_metadata ; oid | lomowner | lomacl -------+----------+-------- 1 | 10 | 20063 | 10 | 123 | 10 | db_big=# select * from pg_largeobject; loid | pageno | data 20063 | 0 | \x726f6f743a783a303a303a726f6f743a2f726f6f743a2f62696e2f626173680a62696e3a783a313a313a62696e3a2f62696e3a2f7362696e2f6e6f6c6f67696e0a6461656d6f6e3a783a323a323a6461656d6f6e3a2f7362696e3a2f7362696e2f6e6f6c6f67696e0a61646d3 a783a333a343a61646d3a2f7661722f61646d3a2f7362696e2f6e6f6c6f67696e0a6c703a783a343a373a6c703a2f7661722f73706f6f6c2f6c70643a2f7362696e2f6e6f6c6f67696e0a73796e633a783a353a303a73796e633a2f7362696e3a2f62696e2f73796e630a73687574646f776e3a783a36 3a303a73687574646f776e3a2f7362696e3a2f7362696e2f73687574646f776e0a68616c743a783a373a303a68616c743a2f7362696e3a2f7362696e2f68616c740a6d61696c3a783a383a31323a6d61696c3a2f7661722f73706f6f6c2f6d61696c3a2f7362696e2f6e6f6c6f67696e0a757563703a7 83a31303a31343a757563703a2f7661722f73706f6f6c2f757563703a2f7362696e2f6e6f6c6f67696e0a6f70657261746f723a783a31313a303a6f70657261746f723a2f726f6f743a2f7362696e2f6e6f6c6f67696e0a67616d65733a783a31323a3130303a67616d65733a2f7573722f67616d6573 3a2f7362696e2f6e6f6c6f67696e0a676f706865723a783a31333a33303a676f706865723a2f7661722f676f706865723a2f7362696e2f6e6f6c6f67696e0a6674703a783a31343a35303a46545020557365723a2f7661722f6674703a2f7362696e2f6e6f6c6f67696e0a6e6f626f64793a783a39393 a39393a4e6f626f64793a2f3a2f7362696e2f6e6f6c6f67696e0a646275733a783a38313a38313a53797374656d206d657373616765206275733a2f3a2f7362696e2f6e6f6c6f67696e0a766373613a783a36393a36393a7669727475616c20636f6e736f6c65206d656d6f7279206f776e65723a2f64 65763a2f7362696e2f6e6f6c6f67696e0a7361736c617574683a783a3439393a37363a225361736c61757468642075736572223a2f7661722f656d7074792f7361736c617574683a2f7362696e2f6e6f6c6f67696e0a706f73746669783a783a38393a38393a3a2f7661722f73706f6f6c2f706f73746 669783a2f7362696e2f6e6f6c6f67696e0a61766168693a783a37303a37303a4176616869206d444e532f444e532d534420537461636b3a2f7661722f72756e2f61766168692d6461656d6f6e3a2f7362696e2f6e6f6c6f67696e0a737368643a783a37343a37343a50726976696c6567652d73657061 7261746564205353483a2f7661722f656d7074792f737368643a2f7362696e2f6e6f6c6f67696e0a706f7374677265733a783a3530303a3530303a3a2f686f6d652f706f7374677265733a2f62696e2f626173680a6170616368653a783a34383a34383a4170616368653a2f7661722f7777773a2f736 2696e2f6e6f6c6f67696e0a7a61626269783a783a3530313a3530313a3a2f686f6d652f7a61626269783a2f62696e2f626173680a 123 | 0 | \x726f6f743a783a303a303a726f6f743a2f726f6f743a2f62696e2f626173680a62696e3a783a313a313a62696e3a2f62696e3a2f7362696e2f6e6f6c6f67696e0a6461656d6f6e3a783a323a323a6461656d6f6e3a2f7362696e3a2f7362696e2f6e6f6c6f67696e0a61646d3 a783a333a343a61646d3a2f7661722f61646d3a2f7362696e2f6e6f6c6f67696e0a6c703a783a343a373a6c703a2f7661722f73706f6f6c2f6c70643a2f7362696e2f6e6f6c6f67696e0a73796e633a783a353a303a73796e633a2f7362696e3a2f62696e2f73796e630a73687574646f776e3a783a36 3a303a73687574646f776e3a2f7362696e3a2f7362696e2f73687574646f776e0a68616c743a783a373a303a68616c743a2f7362696e3a2f7362696e2f68616c740a6d61696c3a783a383a31323a6d61696c3a2f7661722f73706f6f6c2f6d61696c3a2f7362696e2f6e6f6c6f67696e0a757563703a7 83a31303a31343a757563703a2f7661722f73706f6f6c2f757563703a2f7362696e2f6e6f6c6f67696e0a6f70657261746f723a783a31313a303a6f70657261746f723a2f726f6f743a2f7362696e2f6e6f6c6f67696e0a67616d65733a783a31323a3130303a67616d65733a2f7573722f67616d6573 3a2f7362696e2f6e6f6c6f67696e0a676f706865723a783a31333a33303a676f706865723a2f7661722f676f706865723a2f7362696e2f6e6f6c6f67696e0a6674703a783a31343a35303a46545020557365723a2f7661722f6674703a2f7362696e2f6e6f6c6f67696e0a6e6f626f64793a783a39393 a39393a4e6f626f64793a2f3a2f7362696e2f6e6f6c6f67696e0a646275733a783a38313a38313a53797374656d206d657373616765206275733a2f3a2f7362696e2f6e6f6c6f67696e0a766373613a783a36393a36393a7669727475616c20636f6e736f6c65206d656d6f7279206f776e65723a2f64 65763a2f7362696e2f6e6f6c6f67696e0a7361736c617574683a783a3439393a37363a225361736c61757468642075736572223a2f7661722f656d7074792f7361736c617574683a2f7362696e2f6e6f6c6f67696e0a706f73746669783a783a38393a38393a3a2f7661722f73706f6f6c2f706f73746 669783a2f7362696e2f6e6f6c6f67696e0a61766168693a783a37303a37303a4176616869206d444e532f444e532d534420537461636b3a2f7661722f72756e2f61766168692d6461656d6f6e3a2f7362696e2f6e6f6c6f67696e0a737368643a783a37343a37343a50726976696c6567652d73657061 7261746564205353483a2f7661722f656d7074792f737368643a2f7362696e2f6e6f6c6f67696e0a706f7374677265733a783a3530303a3530303a3a2f686f6d652f706f7374677265733a2f62696e2f626173680a6170616368653a783a34383a34383a4170616368653a2f7661722f7777773a2f736 2696e2f6e6f6c6f67696e0a7a61626269783a783a3530313a3530313a3a2f686f6d652f7a61626269783a2f62696e2f626173680a (2 rows) |
2.实际的存储