将PostgreSQL文本/字节列迁移到大对象? [英] Migrate PostgreSQL text/bytea column to large object?

问题描述

我有一个表(1万行)，该表在text列中存储较大的值.当前最大容量为417 MB未压缩(已烤制85 MB).这种设计的缺陷在于无法流式传输这些值(例如，通过JDBC)-使用此列的任何内容都必须将整个内容读入内存.

I've got a table (10k rows) that stores large values in a text column. The current largest is 417 MB uncompressed (85 MB toasted). The flaw in this design is that it's not possible to stream these values (e.g. over JDBC) - anything using this column must read the whole thing into memory.

是否有可用的工具或快捷方式将此列迁移到大对象?最小化所需的工作磁盘和内存.

Are there any tools or shortcuts available to migrate this column to large objects? Minimising the working disk and memory required.

如果有任何区别，我将使用lo_compat_privileges.

I'll be using lo_compat_privileges if that make any difference.

推荐答案

为什么不只使用

Why not just use lo_from_bytea?

示例:

SELECT 'test'::text::bytea;
   bytea    
------------
 \x74657374
(1 row)

SELECT lo_from_bytea(0, 'test'::text::bytea);
 lo_from_bytea 
---------------
        274052
(1 row)

SELECT lo_get(274052);
   lo_get   
------------
 \x74657374
(1 row)

因此，要实际将文本中的数据(最好有备份)移动到OID，可以执行以下操作:

So, to actually move (you better have a backup) the data from text to OID's, you can do the following:

ALTER TABLE mytable ADD COLUMN value_lo OID;
UPDATE mytable SET value_lo = lo_from_bytea(0, value::bytea), value = NULL;
ALTER TABLE mytable DROP COLUMN value;
ALTER TABLE mytable RENAME COLUMN value_lo TO value;

...最后，由于PostgreSQL是MVCC数据库，并且不会立即删除所有数据，因此您应该使用

...and finally, as PostgreSQL is an MVCC database and does not immediately delete all data, you should clean things up with either a VACUUM FULL or a CLUSTER.

这篇关于将PostgreSQL文本/字节列迁移到大对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！