python - 在 python 工作流中调整 Postgresql 性能和内存使用

Question

我使用 Postgresql 9.4 作为模型数据库。我的 table 看起来有点像这样:

CREATE TABLE table1 (
sid INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('table1_sid_seq'::regclass),
col1 INT, 
col2 INT,
col3 JSONB);

我的 Python 2.7 工作流程通常如下所示:

curs.execute("SELECT sid, col1, col2 FROM table1")
data = curs.fetchall()
putback = []
for i in data: 
    result = do_something(i[1], i[2])
    putback.append((sid, result))
del data
curs.execute("UPDATE table1
              SET col3 = p.result
              FROM unnest(%s) p(sid INT, result JSONB)
              WHERE sid = p.sid", (putback,))

这通常非常有效。但是，对于大型查询，Postgresql 内存使用有时会在 UPDATE 命令期间达到顶峰(>50GB)，我相信它正在被 OS X 杀死，因为我得到了 WARNING: terminating由于另一个服务器进程崩溃导致的连接。我的 Macbook Pro 有 16GB 的 RAM，所讨论的查询有 1100 万行，每行大约有 100 个字符的数据要写回。

我的postgresql.conf:

default_statistics_target = 50
maintenance_work_mem = 512MB 
constraint_exclusion = on 
checkpoint_completion_target = 0.9
effective_cache_size = 4GB 
work_mem = 256MB 
wal_buffers = 16MB 
checkpoint_segments = 128 
shared_buffers = 1024MB 
max_connections = 80

所以我想

1. 为什么我的查询有时会消耗过多的 RAM？
2. 如何控制内存使用并仍然保证良好的性能？
3. 有没有好的 Postgresql 调优指南或工具？

更新:
我很确定@wildplasser 指出了我的问题。在评论中，他建议先将数据转储到数据库中，然后从那里解压缩。不幸的是，我不知道如何实现他的建议。 如果有人知道如何做到这一点，他们的回答将很乐意被接受。

Answer 1

我的解决方法 是按照提议here 用一个简单的函数对putback 进行切片。 :

def chunk(l, n):
    n = max(1, n)
    return [l[i:i + n] for i in range(0, len(l), n)]

然后

for chunk in chunk(putback, 250000):
    curs.execute("UPDATE table1
                  SET col3 = p.result
                  FROM unnest(%s) p(sid INT, result JSONB)
                  WHERE sid = p.sid", (chunk,))

这行得通，即控制内存占用，但不是很优雅，而且比我通常做的一次转储所有数据慢。