PostgreSQL:将二进制数据流到bytea字段非常慢

Question

我正在尝试将二进制数据流到bytea字段中。这一过程相当简单：

loop until the end of the incoming stream
  UPDATE myTable SET data = data || $chunk WHERE id = myId

• $chunk是当前流块的绑定。
• 行(myId)已经以空数据存在。
• 每块约64k。
• 最终数据约为4MB。

一切正常工作，期望连接数据随着块的存储而变得越来越慢。

由于我需要保持SQL的可移植性，所以不能使用"PostgreSQL大对象“。

是否有可能优化这一过程？

• 可能是使用空数据预先分配，然后使用overlay()来放置每个块吗？

Answer 1

更新多版本模型 (如postgres )中的行意味着使用新内容创建该行的第二个副本。物理上，没有就地更新: UPDATE类似于DELETE +插入新内容.

所以在上面的循环中，第一个块不是写一次，而是写N次，第二个块写N-1次，第三个N2次等等。

更糟糕的是，所有这些写入也必须转到WAL文件中进行日志记录，而所有中间版本的行都需要由自动真空进程获取，以便最终被丢弃。

假设这些块不能在客户端上组装，并且必须是流的，那么这样做可能会有帮助：

CREATE TEMPORARY sequence s;
CREATE TEMPORARY TABLE buffer(seq int default nextval('s'), chunk bytea);

-- buffer in temporary storage
LOOP 
   INSERT INTO buffer(chunk) VALUES ($chunk)
END LOOP

-- assemble in final storage
INSERT INTO permanent_table(data)
   SELECT string_agg(chunk,''::bytea order by seq) FROM buffer;

TRUNCATE (or DROP) TABLE buffer;

这些块至少只写两次(一次在缓冲区存储，一次在最终的持久存储中)，只有一次写在日志中，因为临时表不会被WAL-日志记录。