Psycopg2在大型选择查询上用尽了内存 [英] Psycopg2 uses up memory on large select query

问题描述

我正在使用psycopg2查询Postgresql数据库，并尝试处理具有约380M行的表中的所有行。只有3列（id1，id2，count）均为整数类型。但是，当我在下面运行直接选择查询时，Python进程开始消耗越来越多的内存，直到被操作系统杀死为止。

I am using psycopg2 to query a Postgresql database and trying to process all rows from a table with about 380M rows. There are only 3 columns (id1, id2, count) all of type integer. However, when I run the straightforward select query below, the Python process starts consuming more and more memory, until it gets killed by the OS.

最小的工作示例（假设mydatabase存在并且包含一个名为mytable的表）：

Minimal working example (assuming that mydatabase exists and contains a table called mytable):

import psycopg2
conn = psycopg2.connect("dbname=mydatabase")
cur = conn.cursor()
cur.execute("SELECT * FROM mytable;")

这时程序开始消耗内存。

At this point the program starts consuming memory.

我看了看，Postgresql的运行情况很好。它使用了相当多的CPU，这很好，而且内存量非常有限。

I had a look and the Postgresql process is behaving well. It is using a fair bit of CPU, which is fine, and a very limited amount of memory.

我期望psycopg2返回一个迭代器，而不会尝试缓冲所有缓存。选择的结果。然后，我可以重复使用 cur.fetchone（）来处理所有行。

I was expecting psycopg2 to return an iterator without trying to buffer all of the results from the select. I could then use cur.fetchone() repeatedly to process all rows.

所以，我该如何选择380M行表而没有用完可用内存？

So, how do I select from a 380M row table without using up available memory?

推荐答案

您可以使用服务器端光标。

cur = conn.cursor('cursor-name') # server side cursor
cur.itersize = 10000 # how much records to buffer on a client
cur.execute("SELECT * FROM mytable;")

这篇关于Psycopg2在大型选择查询上用尽了内存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！