主页 / dba / 问题 / 232777
Accepted
Imanol Y.
Asked: 2019-03-22 10:30:20 +0800 CST

在 Postgres 中提取全表的最快方法

  • 772

我正在尝试将具有 50M 记录的表转储到文件中，我的目标是减少执行此操作的时间。我通常使用COPY metrics TO 'metrics.csv' DELIMITER ',' CSV;这在最好的情况下可能需要一个小时。我也有兴趣以某种纯格式导出数据（避免使用pd_dump目录）。

其中一个想法是通过条件或游标以某种方式访问​​此表，将整个表拆分为相等大小的块，因此您可以同时执行例如 2 个复制查询，将时间减少到一半。

例子：

COPY (SELECT * FROM metrics WHERE id < 25000000) TO 'metrics_1.csv' DELIMITER ',' CSV;
COPY (SELECT * FROM metrics WHERE id >= 25000000) TO 'metrics_2.csv' DELIMITER ',' CSV;

在这些条件下创建的部分索引会有所帮助吗？

有什么想法是实现表的部分复制转储的好方法吗？还有其他解决方案可以更快地转储此表吗？

Postgresql 11 / 100GB RAM / 20 核。

在与 IO 边界进行一些并行化之后，COPY似乎不再是瓶颈。

在此处输入图像描述

postgresql dump

1 个回答

  1. Best Answer

    您当前对主键使用范围查询的想法可能是最好的选择。我看不出部分索引如何对此有所帮助。您将需要一系列部分索引，这些索引总体上是一个总索引，并且 BTREE 索引用于范围查询，这将毫无意义。但是，此方法的成功可能取决于表中的行在物理上大致按主键值排序。您可以使用 CLUSTER 命令强制执行此排序，但这本身就是一项非常昂贵的操作。

    如果您想让 PostgreSQL 为您并行化，您将使用一个虚拟查询来选择所有内容：

    COPY (SELECT * FROM metrics) TO 'metrics.csv' DELIMITER ',' CSV;

    而且您可能还需要大幅降低“parallel_tuple_cost 设置”，可能一直降到零。但这不太可能给您带来实际的改进，因为瓶颈（如果不是磁盘 IO）将您的数据从其内部二进制格式转换为将由 COPY 输出的文本格式。这种转换总是在领导进程中完成，而不是并行工作者。

    您可以通过编写查询来解决此问题，以便在查询本身中显式完成繁重的工作：

    COPY (SELECT col1::text, col2::text, col3::text, ... FROM metrics) TO 'metrics.csv' DELIMITER ',' CSV;

    现在，引导过程仍然需要扫描所有文本以查找需要转义/引用的内容，但至少它不需要将所有行的 JSONB 转换为文本。在我手中，使用 7 个并行工作人员（包括领导者在内的 8 个进程）将所需时间减少了COPY...TO一半。然后，开销变成了从并行工作人员到领导进程的通信。删除它的方法是在单独的会话中返回您对索引范围查询的原始建议。

    • 4

相关问题