将非常大的数据插入 PostgreSQL 表的更好方法是什么？
操作系统：Ubuntu 22.04 LTS
数据库：PostgreSQL 14
框架：Python 3.11 Django
现在我使用 insert into 语句一次 100,000 行。平均插入1,000,000行整个过程需要2分钟，在我可以接受的范围内。但我想知道是否有更好的方法来做到这一点。
它工作正常，但不知怎的，它花费了更多时间，有时会出现类似错误
OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
from django.db import connection
cursor = connection.cursor()
batch_size = 100000
offset = 0
while True:
transaction_list_query = SELECT * FROM {source_table} LIMIT {batch_size} OFFSET {offset};
cursor.execute(transaction_list_query)
transaction_list = dictfetchall(cursor)
if not transaction_list:
break
data_to_insert = []
for transaction in transaction_list:
# Some Process Intensive Calculations
insert_query = INSERT INTO {per_transaction_table} ({company_ref_id_id_column}, {rrn_column},{transaction_type_ref_id_id_column}, {transactionamount_column}) VALUES {",".join(data_to_insert)} ON CONFLICT ({rrn_column}) DO UPDATE SET {company_ref_id_id_column} = EXCLUDED.{company_ref_id_id_column};
cursor.execute(insert_query)
offset += batch_size
当前的批量插入方法对于 PostgreSQL 来说已经是一个不错的开始。但是，有一些方法可以优化它并潜在地解决“服务器意外关闭连接”错误：
1. 增加批量大小：
- 正在使用 100,000 行的批量大小。虽然这通常是一个不错的起点，但尝试增加此值（例如，500,000 或 1,000,000）并衡量性能影响可能会有所帮助。更大的批量可以减少往返于数据库的次数，从而提高效率。
2. 使用
COPY
命令（如果可能）：
-
COPY命令是将数据导入 PostgreSQL 的最快方法。如果能够以文件形式获取数据（例如，CSV 文件），则可以使用COPY命令直接将数据加载到表中。这比使用INSERT语句要快得多，尤其是在处理大型数据集时。 -
如果源数据已经在一个表中，可以使用
COPY将数据导出到一个文件，然后再将它导入到目标表中。
3. 禁用自动提交并手动管理事务：
-
默认情况下，Django 会为每个查询打开和关闭一个事务。在大型插入操作期间，这可能会导致性能下降。可以通过禁用自动提交并手动管理事务来优化此过程。 ```python from django.db import transaction
with transaction.atomic(): cursor = connection.cursor() # ... your insertion logic here ...
`` *transaction.atomic()` 将确保所有插入操作都在单个事务中执行，从而减少开销。
4. 优化
ON CONFLICT
子句：
-
虽然的
ON CONFLICT子句看起来不错，但确保实际需要它至关重要。如果不需要更新重复的行，则删除此子句可以提高性能。
5. 检查连接参数：
-
OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly错误可能表示数据库连接问题。检查的 PostgreSQL 服务器日志以获取有关此错误的更多特定信息。 -
确保的连接参数（例如
connect_timeout、keepalives和keepalives_idle）已正确配置，以防止连接在长时间运行的操作期间超时。
6. 考虑异步插入：
-
对于更高级的用例，可以探索使用异步库（如
asyncio或 Celery）在后台执行插入操作。这使的主应用程序可以继续运行，而不会被插入操作阻塞。
代码示例（使用
COPY
命令）：
import psycopg2
# ... 连接到的数据库 ...
with open("your_data.csv", "r") as file:
with connection.cursor() as cursor:
cursor.copy_expert(
"""
COPY per_transaction_table (company_ref_id_id_column, rrn_column, transaction_type_ref_id_id_column, transactionamount_column)
FROM STDIN
WITH (FORMAT CSV, HEADER);
""",
file
)
connection.commit()
请记住 在实现任何这些优化后对代码进行基准测试，以衡量它们对特定用例的影响。
标签：python,django,postgresql From： 77764734