将大型PostgreSQL表读入数据帧时出现问题

Question

我在PostgreSQL中有一个相当大的表。执行时
select pg_size_pretty(pg_total_relation_size('my_schema.my_table'));
在PostgreSQL中，我得到的表大小为2048MB。我的电脑有16 GB内存，AMD CPU Ryzen 7 pro 4750G，运行Ubuntu 20.04。
我使用模块psycopg2建立了从Python到PostgreSQL的连接，并使用Pandas检索数据。这是一段简单的代码：

db_conn = psycopg2.connect(host = 'localhost', database = 'my_db', user = 'user_name', password = 'my_passwort')
stmt = "select order_name, order_timestamp::date, col1, col12 from my_schema.my_table;"
data_df = pd.io.sql.read_sql(stmt, db_conn)

一开始，我的RAM使用率大约为2.5GB。但是，当我尝试用最后一条语句检索数据时，它开始增加，最终达到16GB，然后我的Python终端关闭，并显示消息“Killed”。
有人能解释为什么会这样吗？我有大约13.5 GB的可用RAM，要读取的表约为2 GB，但我的RAM使用率最终达到100%，执行被中止。
我还尝试了data_df = pd.read_sql(stmt, db_conn)来读取该表（不确定区别是什么）。结果是一样的。
最后，在谷歌搜索之后，我找到了一个替代方法，创建了一个临时文件，上面的最后一行基本上被替换为

with tempfile.TemporaryFile() as tmpfile:
    copy_sql = "COPY ({query}) TO STDOUT WITH CSV {head}".format(query = stmt, head="HEADER")
    cur = db_conn.cursor()
    cur.copy_expert(copy_sql, tmpfile)
    tmpfile.seek(0)
    data_df = pd.read_csv(tmpfile)

这很有魅力，但我不明白为什么。数据帧数据_df仍然比预期的稍大（2.5 GB），但仍然比以前小得多。
知道为什么第二种方法有效而第一种方法失败了吗？
tmpfile.seek（0）的用途是什么？临时文件存储在何处？使用什么名称？在我看来，PostgreSQL创建了它，但没有指定名称（以“.csv”结尾），只有Python名称（这里是tmpfile）。这个例子真的让我很烦，因为我不明白发生了什么以及代码为什么工作，所以希望有人能帮我解释一下

Answer 1

Any ideas why the second method works and the first fails?

我的最佳猜测是，Postgres数据库驱动程序以低效的中间格式表示表，而数据库驱动程序在尝试将中间表示转换为numpy数组之前，正在将整个表加载到内存中。我猜这一步你的内存不足了

为了验证这个理论，您可以尝试使用chunksize以较小的块读取表，并将它们全部合并在一起

It seems to me that PostgreSQL creates it

不，Python正在创建它，并对该文件执行所有读/写操作。见tempfile module

where exactly is the temporary file stored and under what name?

临时文件通常存储在/tmp中。临时文件没有名称。Python创建文件，打开它，然后删除它。在Linux中，如果您删除了一个文件，那么在关闭该文件的所有文件描述符之前，不会真正删除该文件。因此，如果您想要一个在程序退出时自动删除的文件，无论发生什么情况，在打开该文件后删除该文件都是一种有效的方法

如果希望文件具有名称，则需要使用tempfile.NamedTemporaryFile。如果使用命名临时文件，可以按如下方式打印名称：

with tempfile.NamedTemporaryFile() as f:
    print(f.name)

What is the purpose of tmpfile.seek(0)?

当您读取或写入文件时，您在该文件中有一个“位置”。阅读或写作能提高地位。Postgres将表的内容写入该文件后，该位置位于末尾。您希望将位置设置为起始位置。因此，你寻求零。（此数字相对于文件的开头。）Documentation

您通常不会在Python代码中看到这一点，因为您通常只是读取或写入文件，而不是同时读取或写入文件

作为结束语，感谢您发布此问题。我以前从未见过处理大桌子的技巧，所以谢谢你教我一些东西