PostgreSQL：更新大表

Question

我有一个包含2900万行的大型PostgreSQL表格。根据pgAdmin中的统计标签显示，该表的大小几乎为9GB。该表启用了post-gis，并有一个空的几何列。

我想使用ST_GeomFromText更新几何列，读取存储在同一表格中的X和Y坐标列（SRID: 27700）。然而，在整个表格上运行此查询会导致“磁盘空间不足”和“与服务器的连接丢失”的错误......后者出现的频率较低。

为了解决这个问题，我应该分批/分阶段更新这2900万行吗？如何对前100万行进行操作，然后继续对下一百万行进行操作，直到达到2900万？

或者还有其他更有效的方法来更新像这样的大型表格吗？

我应该补充说明，该表格托管在AWS上。

我的更新查询语句是：

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

- Theo F

---

尝试对一组有限行执行更新操作..如果每行都有一个ID，则首先对前100,000个进行尝试，然后再尝试1M的集合。 - ScaisEdge

无法帮助解决磁盘空间问题，但是st_point比st_geomfromtext更快。https://gis.stackexchange.com/questions/122247/st-makepoint-or-st-pointfromtext-to-generate-points - mlinth

st_point。正如链接所解释的那样，st_pointfromtext在幕后使用st_geomfromtext。 - mlinth

ST_SetSRID(ST_Point(eastingcolumn,northingcolumn),27700); 你的列需要是浮点数（如果它们是文本，则需要转换为浮点数，例如eastingcolumn :: numeric）。 - mlinth

是的 - 应该可以工作，并且强制转换将是隐式的。有关转换的更多信息，请参见此处：https://www.postgresql.org/docs/current/typeconv.html - mlinth

Answer 1

您没有提供任何服务器规格，如果是在最近的硬件上，写入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、轻量级锁、重试逻辑等）。

- filiprem

---

感谢回复，但是我的表格没有一个自增的id列，如1、2、3、...、29000000等。我的主键列是uprn（一个介于1到12位数字之间的唯一参考编号）。简而言之，我不知道分布模式是什么。 - Theo F

我已经使用 ALTER TABLE schema.tablename ADD COLUMN id SERIAL; 添加了一个 ID 列。 - Theo F

对于一个包含100万行的测试表，一次性更新整个表中的一个列需要1分钟。但是将同一个列在4个部分中进行更新（例如WHERE id BETWEEN 0 AND 250000等），每个部分需要47秒。因此，总体来说，我们可以认为更新整个表需要大约3分钟... - Theo F

@TheoF 感谢您添加了一些细节 :-) 这证明分批更新可能不值得额外的复杂性，选择“最佳”解决方案取决于环境。 - filiprem

Answer 2

关于我的原始问题：

然而，一次在整个表上运行此查询会导致“磁盘空间不足”和“与服务器的连接丢失”错误……后者发生较少。

结果显示我们的Amazon AWS实例数据库空间不足，阻止了我的原始ST_GeomFromText查询完成。释放空间后问题得到解决。

需要注意的是，正如@mlinth建议的那样，ST_Point比ST_GeomFromText更快地运行了我的查询（24分钟对2小时）。

我的最终查询如下：

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

- Theo F