后灰岩：更新大型表

Question

我有一个包含2900万行的大型PostgreSQL表。该表的大小（根据pgAdmin中的stats选项卡，几乎为9GB）为post-gis启用了一个空的geometry列。

我想使用ST_GeomFromText更新几何体列，从存储在同一表中的X和Y坐标列（SRID:27700）读取数据。但是，一次对整个表运行此查询会导致“磁盘空间不足”和“服务器连接丢失”错误……后者的频率较低。

为了克服这个问题，我应该分批次/阶段更新2900万行吗？如何100万行（第一100万），然后执行下100万行，直到达到2900万？

或者是否有其他更有效的html" target="_blank">方法来更新像这样的大型表？

我应该补充一点，该表托管在AWS中。

我的UPDATE查询是：

UPDATE schema.table
SET geom = ST_GeomFromText('POINT(' || eastingcolumn || ' ' || northingcolumn || ')',27700);

Answer 1

从我最初的问题:

但是，一次在整个表上运行此查询会导致“磁盘空间不足”和“与服务器的连接丢失”错误……后者不太频繁。

结果是我们的Amazon AWS实例数据库空间不足，导致我最初的ST_GeomFromText查询无法完成。释放空间修复了它。

值得注意的是，正如@mlinth所建议的那样，ST_Point运行我的查询比ST_GeomFromText快得多（24分钟vs 2小时）。

我的最后一个问题是：

UPDATE schema.tablename
SET geom = ST_SetSRID(ST_Point(eastingcolumn,northingcolumn),27700);

Answer 2

你没有给出任何服务器规格，在最新的硬件上写9GB可能很快。

您应该可以进行一次长更新，除非您对该表有并发写入。

克服此问题（一个非常长的事务，锁定对表的写入）的一个常见技巧是根据主键将 UPDATE 拆分为基于主键的范围，这些范围在单独的事务中运行。

/* Use PK or any attribute with a known distribution pattern */
UPDATE schema.table SET ... WHERE id BETWEEN 0 AND 1000000;
UPDATE schema.table SET ... WHERE id BETWEEN 1000001 AND 2000000;

对于高级别的并发写入，人们使用更微妙的技巧（如：SELECT For UPDATE/NOWAIT、轻量级锁、重试逻辑等）。