关于“繁重”请求和解决问题的优雅解决方案的简短故事
最近，在晚上，警报开始唤醒我们：磁盘空间不足。 我们很快就发现问题出在ETL任务中。
ETL任务在存储二进制记录和转储的表中执行。 每天晚上，此任务是删除重复的转储并释放空间。
为了搜索重复的转储，我们使用了以下查询：
id, MIN(id) OVER (PARTITION BY blob ORDER BY id) FROM dumps
该查询通过BLOB字段合并了相同的转储。 使用窗口函数，我们获得每个转储的首次出现的标识符。 然后，根据此请求，我们删除所有重复的转储。
该请求已执行了一段时间，并且从日志中可以看出，该请求占用了大量内存。 该图显示了他每晚如何为可用磁盘空间评分：
随着时间的流逝，请求需要更多的内存，故障更加严重。 而且，浏览执行计划，我们立即看到了一切：
Buffers: shared hit=3916, temp read=3807 written=3816 -> Sort (cost=69547.50..70790.83 rows=497332 width=36) (actual time=107.607..127.485 rows=39160) Sort Key: blob, id Sort Method: external merge Disk: 30456kB Buffers: shared hit=3916, temp read=3807 written=3816 -> Seq Scan on dumps (cost=0..8889.32 rows=497332 width=36) (actual time=0.022..8.747 rows=39160) Buffers: shared hit=3916 Execution time: 159.960 ms
排序占用大量内存。 在执行方面，排序需要测试数据集中大约30 MB的内存。
为什么这样
PostgreSQL分配内存用于哈希和排序。 内存量由work_mem参数控制。 work_mem的默认大小为4 MB。 如果散列或排序所需的内存超过4 MB，则PostgreSQL会临时占用磁盘空间。
我们的查询显然消耗了4 MB以上的内存，因此数据库使用了如此多的内存。 我们决定：我们不会着急，也没有增加参数并扩大存储量。 最好寻找另一种整理内存以进行排序的方法 。
经济分类
“要吃多少排序取决于数据集和排序键的大小。您无法减少数据集，但是键的大小是可能的 。
对于参考点，我们采用排序键的平均大小：
avg ---------- 780
每个密钥的重量为780。为减少二进制密钥，可以对其进行哈希处理。 在PostgreSQL中有md5用于此操作（是的，不是安全性，但是出于我们的目的，它会这样做）。 让我们看看用md5散列的BLOB的权重是多少：
avg ----------- 36
通过md5散列的密钥的大小为36个字节。 哈希键仅占原始选项的4％ 。
接下来，我们使用哈希键启动了原始请求：
id, MIN(id) OVER ( PARTITION BY md5(array_to_string(blob, '') ) ORDER BY id) FROM dumps;
以及实施计划：
Buffers: shared hit=3916 -> Sort (cost=7490.74..7588.64 rows=39160 width=36) (actual time=349.383..353.045 rows=39160) Sort Key: (md5(array_to_string(blob, ''::text))), id Sort Method: quicksort Memory: 4005kB Buffers: shared hit=3916 -> Seq Scan on dumps (cost=0..4503.40 rows=39160 width=36) (actual time=0.055..292.070 rows=39160) Buffers: shared hit=3916 Execution time: 374.125 ms
使用散列密钥，该请求仅消耗4个额外的兆字节，即仅比前30 MB的10％多一点。 因此，排序键的大小会极大地影响排序所消耗的内存 。
更进一步
在此示例中，我们使用md5对BLOB进行了哈希处理。 用MD5创建的哈希必须重16个字节。 而且我们得到了更多：
md5_size ------------- 32
我们的哈希值恰好是哈希值的两倍，因为md5会以十六进制文本的形式生成哈希值。
在PostgreSQL中，可以将MD5用于pgcrypto扩展的哈希。 pgcrypto创建bytea类型的MD5 （二进制） ：
select pg_column_size( digest('foo', 'md5') ) as crypto_md5_size crypto_md5_size --------------- 20
散列仍然比应有的多4个字节。 只是bytea类型使用这4个字节来存储值的长度，但我们不会那样做。
事实证明，PostgreSQL中的uuid类型恰好重16个字节，并且支持任何任意值，因此我们摆脱了其余的四个字节：
uuid_size --------------- 16
仅此而已。 md5 32个字节变成uuid 16个字节。
我通过获取更大的数据集检查了更改的影响。 数据本身无法显示，但我将分享结果：
从表中可以看到，最初有问题的请求重300 MB（并在半夜唤醒了我们）。 使用uuid键，排序仅花费了7 MB。
后续注意事项
具有散列内存排序键的请求消耗较少，但运行速度慢得多：
散列使用更多的CPU，因此带有散列的请求速度较慢。 但是我们试图解决磁盘空间问题，而且该任务在晚上执行，因此时间不是问题。 我们为了节省内存而妥协。
这是一个很好的例子，您不必总是尝试加快数据库查询的速度 。 最好使用平衡的资源，并从最少的资源中挤出最大的资源。