如何在postgreSQL中提高批量升级的速度？

Question

我正试图通过以下设计将数百万条数据记录从多个不同的来源加载到postgresql表中：

CREATE TABLE public.variant_fact (
    variant_id bigint NOT NULL,
    ref_allele text NOT NULL,
    allele text NOT NULL,
    variant_name text NOT NULL,
    start bigint,
    stop bigint,
    variant_attributes jsonb
);

ALTER TABLE public.variant_fact
    ADD CONSTRAINT variant_fact_unique UNIQUE (variant_name, start, stop, allele, ref_allele)
    INCLUDE (ref_allele, allele, variant_name, start, stop);

其中“start”和“stop”是外键，“variant_id”是自动递增的主键。我在加载速度方面遇到了问题，因为为了执行UPSERT，我需要检查表以查看我上传的每个元素是否存在一个元素。我使用psycopg2和execute\u values方法在python中执行该操作

insert_query = """
        INSERT INTO variant_fact AS v (variant_id, ref_allele, allele, variant_name, start, stop, variant_attributes)
            VALUES %s
        ON CONFLICT ON CONSTRAINT variant_fact_unique DO UPDATE
        SET variant_attributes = excluded.variant_attributes || v.variant_attributes
        RETURNING variant_id;
    """

inserted = psycopg2.extras.execute_values(cur=cursor, sql=sql, argslist=argslist, template=None, page_size=50000, fetch=fetch)

在我的例子中，argslist是要插入数据库的元组列表。为了提高速度，我试着压缩这个python脚本，但是这个UPSERT块的性能不是很好。除了一个不同的模式（可能没有原子元素记录）之外，有什么方法可以提高上传的性能吗？我已经为表关闭了WAL，并删除了“开始”和“停止”的外键约束。我是不是漏掉了什么明显的东西

Answer 1

关闭WAL（设置表UNLOGGED）意味着崩溃后该表将为空，因为它无法恢复。如果您考虑稍后运行ALTER TABLE将其更改为LOGGED表，请知道此操作将把整个表转储到WAL中，因此您不会赢得任何东西

对于像未标记表上的语句这样的简单语句，加快其速度的唯一方法是：

• 删除除variant_fact_unique之外的所有索引、触发器和约束–但再次创建它们将花费高昂，因此您可能无法赢得总体胜利

• 确保您有快速的存储和足够的RAM

Answer 2

通过“variant_name”和“start”（索引中的前两列）对arglist进行排序，应确保大多数索引查找将命中已缓存的页面。将表也聚集在该索引上将有助于确保表页也以缓存友好的方式访问（尽管面对新数据，它不会保持很好的聚集性）

此外，索引的大小是需要的两倍，这是免费的。对已经是索引主要部分的列执行INCLUDE没有意义。这将花费您的CPU和IO来格式化和写入数据（以及WAL），还将减少适合缓存的数据量