geometry分区表执行重复ID识别查询时触发内存不足错误

针对你查询2432个几何分区表（每个约22万行）重复global_id时遇到的ERROR: out of memory问题，核心原因是一次性全量聚合需要的内存远超PostgreSQL默认配置，结合你的场景，这里有几个实用的优化方向：

1. 分批处理分区，避免全量加载 #

因为你的表是按geometry分区（非Range/List），无法通过分区键快速过滤，所以优先考虑逐个/分批处理分区，每次只处理部分数据，降低内存压力：

• 先获取所有分区名称：

  SELECT table_name AS partition_name
  FROM information_schema.tables
  WHERE table_schema = 'your_schema' -- 替换成你的表所在schema
    AND table_name LIKE 'your_parent_table_%'; -- 替换成父表的前缀
  

• 循环遍历每个分区，单独查询该分区内的重复ID，最后汇总结果。比如用PL/pgSQL写个简单的循环：

  CREATE OR REPLACE FUNCTION find_dups_per_partition()
  RETURNS TABLE(global_id bigint, partition_name text, dup_count int) AS $$
  DECLARE
    rec record;
  BEGIN
    FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema='your_schema' AND table_name LIKE 'your_parent_table_%' LOOP
      RETURN QUERY EXECUTE format(
        'SELECT global_id, %L AS partition_name, count(*) FROM %I GROUP BY global_id HAVING count(*) > 1',
        rec.table_name, rec.table_name
      );
    END LOOP;
  END;
  $$ LANGUAGE plpgsql;
  
  -- 调用函数获取结果
  SELECT * FROM find_dups_per_partition();
  

2. 拆分聚合步骤，用临时表过渡 #

直接全表GROUP BY会把所有global_id加载到内存做哈希聚合，对于5亿级别的数据量来说完全不现实。可以拆分两步操作：

第一步：先收集每个分区内的重复记录 #

用临时表存储每个分区的重复数据（如果直接创建临时表还是内存不足，就用上面的分批方式插入）：

CREATE TEMP TABLE temp_partition_dups AS
SELECT global_id, tableoid::regclass::text AS partition_name, count(*) AS dup_count
FROM your_parent_table
GROUP BY global_id, tableoid
HAVING count(*) > 1;

第二步：汇总跨分区的重复ID #

从临时表中找出在多个分区出现的global_id：

SELECT global_id, array_agg(DISTINCT partition_name) AS affected_partitions, sum(dup_count) AS total_occurrences
FROM temp_partition_dups
GROUP BY global_id
HAVING count(DISTINCT partition_name) > 1;

3. 临时调整PostgreSQL内存参数 #

临时调整内存相关参数，给聚合操作更多内存空间：

• 调高work_mem：这个参数控制排序、哈希聚合等操作的内存上限，默认一般是4MB/8MB，你可以根据服务器内存情况临时调高，比如：

  SET work_mem = '128MB'; -- 服务器内存32G以上可以尝试256MB
  

  注意：不要全局永久修改，避免影响其他业务，用完改回默认值即可。

• 降低并行工作进程数：如果并行查询导致内存叠加消耗，可以临时调低并行数：

  SET max_parallel_workers_per_gather = 1;
  

4. 利用索引减少数据扫描 #

虽然global_id已经建了索引，但全表聚合还是会扫描所有数据。可以尝试用索引扫描来优化：

WITH all_global_ids AS (
  SELECT global_id FROM your_parent_table
)
SELECT global_id, count(*)
FROM all_global_ids
GROUP BY global_id
HAVING count(*) > 1;

如果索引是B-tree索引，PostgreSQL可能会选择索引扫描代替全表扫描，减少内存中加载的数据量。

为什么会出现内存不足？ #

你的表总数据量约为2432 * 221000 ≈ 5.37亿行，直接做GROUP BY时，PostgreSQL需要将所有global_id放入哈希表进行聚合，即使每个global_id只占8字节，也需要约4.3GB内存，这远远超过了默认work_mem的限制，因此触发了内存不足错误。

内容的提问来源于stack exchange，提问作者Michael