postgresql - 将二进制内容附加到 PostgreSQL 数据库中的 bytea 列

Question

我的情况:

我有一些二进制数据，它被分成了很多部分。特殊表 FileParts 包含 fileId、partNo 和 data。

我需要将所有部分放在另一个表 MyFilesStorage 中。

现在我在两种实现之间做出选择:

DO $CODE$
declare
  r record;
begin
  UPDATE public.MyFilesStorage SET mainFileData = E''::bytea WHERE id = 'e14a26c0-db4b-47e1-8b66-e091fb3ba199'::uuid;

  for r in ( select data 
             from public.FileParts 
             where fileId = '89cb8598-436b-49b3-bb1c-34534c6d068e'::uuid
             order by partNo ) loop
    UPDATE public.MyFilesStorage SET mainFileData = mainFileData || r.data WHERE id = 'e14a26c0-db4b-47e1-8b66-e091fb3ba199'::uuid;
  end loop;

end;
$CODE$

我将数据设置为空，然后一个一个地读取部分并将每个部分追加到主表中。

另一种变体:

DO $CODE$
declare
  r record;
  p_result bytea;
begin
  p_result = E''::bytea;

  for r in ( select data 
             from public.FileParts 
             where fileId = '89cb8598-436b-49b3-bb1c-34534c6d068e'::uuid
             order by partNo ) loop
    p_result = p_result || r.data;
  end loop;

  UPDATE public.MyFilesStorage SET mainFileData = p_result WHERE id = 'e14a26c0-db4b-47e1-8b66-e091fb3ba199'::uuid;
end;
$CODE$

这里我使用临时变量。第二个要快得多，但我不知道哪个会占用更多内存？首先我需要内存来将所有文件加载到 RAM，然后呢？ postgre 是否会在此处加载所有内容:mainFileData = mainFileData || r.data?

也许有另一种方法可以做到这一点，因为这两种变体都非常慢？在 Oracle 中，我使用 DBMS_LOB.APPEND 进行此操作。

Answer 1

第一个版本比较慢，因为 PostgreSQL 不会在存储级别进行就地更新，它会为每个 UPDATE 创建行的新版本。因此，对于以 10Mb 为增量从 0Mb 到 100MB 的行，真正写入磁盘的不是 10x10Mb，而是:10Mb+20Mb+30Mb+...+90Mb+100Mb = 550Mb。另一方面，内存消耗将保持在较低水平，一次分配的内存不超过 10Mb。

第二个版本速度更快，只需要写入 100Mb，但需要分配 100Mb 的内存。

有序 block 的FileParts表的结构对于大内容通常更容易管理，为什么要费心将它转换成整体的其他结构？