Postgresql ошибка нехватка памяти

При создании резервных копий БД обнаружил, что некоторые копии не создались. При ручном запуске команды на создание файла бекапа увидел ошибку при выполнении запроса:

ОШИБКА: нехватка разделяемой памяти

Там же, собственно, есть и подсказка: следует увеличить параметр max_locks_per_transaction.

Этот параметр управляет средним числом блокировок объектов, выделяемым для каждой транзакции; отдельные транзакции могут заблокировать и больше объектов, если все они умещаются в таблице блокировок.

Значение по умолчанию = 64

В папке с данными Postgres необходимо найти файл postgresql.conf и найти строку с параметром max_locks_per_transaction. Ее следует раскомментировать и указать, например, 256.

I have a server running Postgres 9.1.15. The server has 2GB of RAM and no swap. Intermittently Postgres will start getting «out of memory» errors on some SELECTs, and will continue doing so until I restart Postgres or some of the clients that are connected to it. What’s weird is that when this happens, free still reports over 500MB of free memory.

select version();:

PostgreSQL 9.1.15 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

uname -a:

Linux db 3.2.0-23-virtual #36-Ubuntu SMP Tue Apr 10 22:29:03 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux

Postgresql.conf (everything else is commented out/default):

max_connections = 100
shared_buffers = 500MB
work_mem = 2000kB
maintenance_work_mem = 128MB
wal_buffers = 16MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9
random_page_cost = 2.0
effective_cache_size = 1000MB
default_statistics_target = 100
log_temp_files = 0

I got these values from pgtune (I chose «mixed type of applications») and have been fiddling with them based on what I’ve read, without making much real progress. At the moment there’s 68 connections, which is a typical number (I’m not using pgbouncer or any other connection poolers yet).

/etc/sysctl.conf:

kernel.shmmax=1050451968
kernel.shmall=256458

vm.overcommit_ratio=100
vm.overcommit_memory=2

I first changed overcommit_memory to 2 about a fortnight ago after the OOM killer killed the Postgres server. Prior to that the server had been running fine for a long time. The errors I get now are less catastrophic but much more annoying because they are much more frequent.

I haven’t had much luck pinpointing the first event that causes postgres to run «out of memory» — it seems to be different each time. The most recent time it crashed, the first three lines logged were:

2015-04-07 05:32:39 UTC ERROR:  out of memory
2015-04-07 05:32:39 UTC DETAIL:  Failed on request of size 125.
2015-04-07 05:32:39 UTC CONTEXT:  automatic analyze of table "xxx.public.delayed_jobs"
TopMemoryContext: 68688 total in 10 blocks; 4560 free (4 chunks); 64128 used
[... snipped heaps of lines which I can provide if they are useful ...]

---

2015-04-07 05:33:58 UTC ERROR:  out of memory
2015-04-07 05:33:58 UTC DETAIL:  Failed on request of size 16.
2015-04-07 05:33:58 UTC STATEMENT:  SELECT oid, typname, typelem, typdelim, typinput FROM pg_type
2015-04-07 05:33:59 UTC LOG:  could not fork new process for connection: Cannot allocate memory
2015-04-07 05:33:59 UTC LOG:  could not fork new process for connection: Cannot allocate memory
2015-04-07 05:33:59 UTC LOG:  could not fork new process for connection: Cannot allocate memory
TopMemoryContext: 396368 total in 50 blocks; 10160 free (28 chunks); 386208 used
[... snipped heaps of lines which I can provide if they are useful ...]

---

2015-04-07 05:33:59 UTC ERROR:  out of memory
2015-04-07 05:33:59 UTC DETAIL:  Failed on request of size 1840.
2015-04-07 05:33:59 UTC STATEMENT:  SELECT... [nested select with 4 joins, 19 ands, and 2 order bys]
TopMemoryContext: 388176 total in 49 blocks; 17264 free (55 chunks); 370912 used

The crash before that, a few hours earlier, just had three instances of that last query as the first three lines of the crash. That query gets run very often, so I’m not sure if the issues are because of this query, or if it just comes up in the error log because it’s a reasonably complex SELECT getting run all the time. That said, here’s an EXPLAIN ANALYZE of it: http://explain.depesz.com/s/r00

This is what ulimit -a for the postgres user looks like:

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 15956
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 15956
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

I’ll try and get the exact numbers from free next time there’s a crash, in the meantime this is a braindump of all the info I have.

Any ideas on where to go from here?

Обновлено: 09.04.2023

Для облегчения загрузки больших объемов данных в базу данных PostgreSQL, вам может понадобиться увеличить значения shared_buffers и max_locks_per_transaction в файле postgresql.conf.

shared_buffers

Параметр shared_buffers задает объем памяти, используемой для буферов разделяемой памяти. В документации PostgreSQL указано, что по причинам производительности вам возможно потребуется использовать большее значение параметра, чем минимальное значение в 128 КБ, либо значение в 16 KБ умноженное на число, установленное для параметра max_connections. Рекомендуется, чтобы shared_buffers был установлен на использование нескольких десятков мегабайт для боевого экземпляра.

При загрузке больших объемов данных, вы часто нуждаетесь в более высоком значении shared_buffers, чем значение установленное по умолчанию в 32 МБ. После изменения этого параметра в файле postgresql.conf, вам необходимо перезапустить кластер базы данных.

max_locks_per_transaction

Значение max_locks_per_transaction указывает число объектов базы данных, которые могут быть заблокированы одновременно. В большинстве случаев, значения по умолчанию 64 бывает достаточно. Тем не менее, при загрузке большого количества данных (например, несколько тысяч) сразу, число одновременных заблокированных для транзакции объектов может превысить 64.

Это не взаимосвязь один-к-одному между конкурентными блокировками и количеством наборов данных; другими словами, если вы загружаете 3000 наборов данных, вам не нужно увеличивать max_locks_per_transaction до 3000. Вначале раскомментируйте параметр max_locks_per_transaction и увеличьте его значение до 100 перед загрузкой в пакетном режиме.

При изменении параметра max_locks_per_transaction, вы должны перезагрузить сервер.

Увеличение значения любого из данных параметров может привести к запросу базой данных большего объема разделяемой памяти, чем допускает операционная система. Сведения о том, как можно увеличить разделяемую память в вашей операционной системе, находятся в разделе Управление ресурсами ядра (Managing Kernel Resources) в Документации PostgreSQL.

cursor_tuple_fraction

Значение cursor_tuple_fraction используется планировщиком PostgreSQL для оценки необходимого набора строк, возвращаемого запросом. По умолчанию, в файле postgresql.conf cursor_tuple_fraction имеет значение 0.1, это означает, что первые 10 процентов строк (или объектов) результата возвращаются быстро, для возвращения остальных строк требуется время.

Если вы хотите изменить процент результата, который отображается планировщиком PostgreSQL при подключении к не-ArcGIS приложениям, измените значение параметра cursor_tuple_fraction в файле postgresql.conf.

Когда для запросов данных применяется ArcGIS 10.2 или более поздней версии, всегда используется значение 1.0, это означает, что данные не отображаются до возвращения 100 процентов результатов. Измененное значение cursor_tuple_fraction в файле postgresql.conf не применяется при доступе к базе данных из ArcGIS. Чтобы изменить значение, используемое ArcGIS, необходимо задать переменную среды cursor_tuple_fraction для сеанса или всей системы.

Повышение производительности сторонних пространственных SQL-запросов

При выполнении SQL-запросов вне ArcGIS, которые возвращают пространственных столбцы ST_Geometry из бизнес-таблицы, можно повысить производительность запросов, если вы установите системную переменную среды, ST_GEOMETRY_OUTPUT_FORMAT, для вывода в тип ST_Geometry, а не в расширенное стандартное текстовое представление (WKT).

По умолчанию, ST_GEOMETRY_OUTPUT_FORMAT установлен на ST_GEOMETRY, это означает, что возвращается 16-ричное представление. Это необходимо для создания работоспособной резервной копии базы геоданных. Вы можете изменить эту переменную на TYPE, если вы хотите улучшить производительность SQL-запросов. Если вы установите эту переменную, потому что вы планируете сделать ряд пространственных запросов SQL, убедитесь, что удалили ее после того, как вы завершите ваши запросы, а затем перезапустите кластер базы данных PostgreSQL.

Переменная должна быть установлена на компьютере, где запущен PostgreSQL.

Для ОС Linux, установите переменную оболочки, из которой запускаете SQL-запросы. Для оболочки bash, синтаксис выглядит следующим образом:

Для оболочки csh, синтаксис выглядит следующим образом:

В Windows, создайте системную переменную среды в свойствах системы.

Имя переменной: ST_GEOMETRY_OUTPUT_FORMAT

Значение переменной: TYPE

После установки данной переменной, вам необходимо перезапустить кластер базы данных PostgreSQL.

Жил на Винде-7, имел забитый на 70% 500Гб SSD диск, все было хорошо. Но потребовалось установить Ubuntu. Ставил через WUBI, т.к. другие более подробные мануалы по установке ниасилил. Сейчас при старте компа появляется выбор из 2 операционок (как надо) и еще какой-то Граб (по ошибке поверил одному описанию установки, но это вроде не мешает). Параметры системы: Память 3,6 ГиБ, Диск 18,2 ГБ. По команде free -h выдает

гуглил — пишут, что можно настраивать postgres, чтобы он не был таким прожорливым. А я не знаю, может у меня настолько мало свободной памяти, что это уже не поможет? И можно ли теперь как-то добавить еще памяти в систему, или уже поздно, диск размечался при установке? Переставлять все по новой? И весь установленный уже софт и т.п.? Что скажете, доктора?

А я не знаю, может у меня настолько мало свободной памяти, что это уже не поможет?

У тебя свободно примерно 1.8 Гб, постгрес настроен на то, чтобы откушать сразу 2.2Гб.

Настроить Postgres можно, но лучше ответить на вопрос, почему у тебя так мало памяти доступно на сервере. И почему некруглое число.

Ты где сервер видишь? У него десктоп на 4 гига, доступно 3.6

Deleted ( 10.10.17 22:20:59 )
Последнее исправление: Deleted 10.10.17 22:21:22 (всего исправлений: 1)

наверни свапца, места хватает же пока

Спасибо, навернул 5 ГБ (!) свапца, теперь из 2 докер-контейнеров стартует первый, а второй валится (раньше было наоборот :)). Но сейчас я могу их оба запустить руками не через compose а через docker start (хотя может так запускается не все что нужно, буду смотреть дальше). И да, со свапцом оно стало время от времени виснуть наглухо, перезгружаюсь регулярно.

ППЦ! тебе постгрес — для одноэс?

Я поверил установщику, который по умолчанию запросил 18ГБ.

Сейчас то что делать? Имеет смысл параметры Постгреса крутить?

Действительно, куда могло место деться…

Где запускается постгрес, там и сервер. Пусть даже он развёрнут на ноутбуке под подушкой.

Со свапцом жилось тяжко, все время зависала система, и много из того что нужно вообще не стартовало. Только что докупил один модуль памяти на 8 гигов — и навскидку все стало гораздо веселее, и даже работает. Насчет все/не все буду дальше смотреть.

Вообще моя материнка умеет в 32 гига максимум (есть 4 слота, можно во все поставить по 8 гигов), есть возможность для апгрейда еще. Просто по умолчанию стояла 32 бит Винда, которая не умеет физически больше 4 гигов ОЗУ подключать, поэтому и было такое железо.

Я также попытался запустить pgtune, который дал следующую рекомендацию с более настроенными параметрами, но это ничего не изменило. Он предлагает shared_buffers размером 1/4 ОЗУ, что, по-видимому, согласуется с рекомендациями в других местах (и в частности в PG wiki).

Я попытался переиндексировать всю базу данных после изменения настроек (используя reindex database ), но это тоже не помогло. Я играл с shared_buffers и work_mem. Постепенно изменяя их с очень консервативных значений по умолчанию (128 КБ / 1 МБ), постепенно снижается производительность.

Я запустил EXPLAIN (ANALYZE,BUFFERS) несколько запросов, и виновник, похоже, в том, что Hash Join значительно медленнее. Мне не понятно почему.

Чтобы привести конкретный пример, у меня есть следующий запрос. Он работает

2100 мс в конфигурации по умолчанию и

3300 мс в конфигурации с увеличенным размером буфера:

EXPLAIN (ANALYZE,BUFFERS) для запроса выше:

Вопрос в том, почему я наблюдаю снижение производительности при увеличении размеров буфера? У машины точно не хватает памяти. Выделение, если для разделяемой памяти в ОС задано ( shmmax и shmall ) очень большое значение, это не должно быть проблемой. Я не получаю никаких ошибок в журнале Postgres либо. Я использую автовакуум в конфигурации по умолчанию, но не ожидаю, что это как-то связано с этим. Все запросы выполнялись на одной и той же машине с интервалом в несколько секунд, только с измененной конфигурацией (и перезапускали PG).

Изменить: Я только что нашел один особенно интересный факт: когда я выполняю тот же тест на моем iMac в середине 2010 года (OSX 10.7.5) также с Postgres 9.2.1 и 16 ГБ оперативной памяти, я не испытываю замедления. В частности:

Когда я делаю точно такой же запрос (тот, что выше) с точно такими же данными на сервере, я получаю 2100 мс с work_mem = 1 МБ и 3200 мс с 96 МБ.

У Mac есть SSD, так что это понятно быстрее, но он демонстрирует поведение, которое я ожидаю.

Похоже, что в более медленном случае каждый шаг постоянно медленнее. Остальные настройки остались прежними?
О, и доложите, пожалуйста, ответьте на свой вопрос, если найдете ответ! (Это разрешено, даже поощряется).
Я действительно удивляюсь, насколько Postgres похож на Oracle в этом отношении: я помню курс Джонатана Льюиса (гуру Oracle), в котором он продемонстрировал, что выделение большего количества памяти для сортировок иногда делает их медленнее. Я забыл подробности, но это было связано с тем, что Oracle выполняет частичные сортировки, а затем записывает их во временное хранилище, а затем объединяет их позже. Каким-то образом больше памяти замедлило этот процесс.

Прежде всего, имейте в виду, что work_mem выполняется для каждой операции, и поэтому он может быстро стать чрезмерным. В общем, если у вас нет проблем с медленной сортировкой, я оставляю work_mem в покое, пока она вам не понадобится.

Глядя на ваши планы запросов, меня поражает то, что обращения к буферу сильно различаются, если смотреть на эти два плана, и что даже последовательное сканирование выполняется медленнее. Я подозреваю, что проблема связана с кэшированием с упреждающим чтением и нехваткой места для этого. Это означает, что вы смещаете память для повторного использования индексов и чтения таблиц на диске.

Насколько я понимаю, PostgreSQL будет смотреть на кеш для страницы перед чтением ее с диска, потому что он не знает, будет ли кеш ОС содержать эту страницу. Поскольку страницы затем остаются в кеше и поскольку этот кеш медленнее, чем кеш ОС, это меняет виды запросов, которые выполняются быстро, по сравнению с типами, которые выполняются медленно. На самом деле, читая планы, кроме проблем с work_mem, похоже, что вся информация вашего запроса поступает из кеша, но вопрос в том, какой именно кеш.

work_mem : сколько памяти мы можем выделить для сортировки или связанной операции соединения. Это для каждой операции, а не для оператора или серверной части, поэтому один сложный запрос может многократно использовать этот объем памяти. Не ясно, что вы достигаете этого предела, но это стоит отметить и знать. если вы увеличите это значение слишком сильно, вы потеряете память, которая может быть доступна для кэша чтения и общих буферов.

shared_buffers : сколько памяти выделить для действительной очереди страниц PostgreSQL. Теперь, в идеале, интересный набор вашей базы данных должен оставаться в кеше памяти здесь и в буферах чтения. Тем не менее, это гарантирует, что наиболее часто используемая информация во всех бэкэндах кэшируется, а не сбрасывается на диск. В Linux этот кеш значительно медленнее, чем дисковый кеш ОС, но он гарантирует, что дисковый кеш ОС не работает и прозрачен для PostgreSQL. Это довольно ясно, где ваша проблема.

Так что получается, что когда у нас есть запрос, мы сначала проверяем общие буферы, так как PostgreSQL хорошо знает этот кеш, и ищем страницы. Если их там нет, мы просим ОС открыть их из файла, и если ОС кэширует результат, она возвращает кэшированную копию (это быстрее, чем общие буферы, но Pg не может определить, кэширована она или включена. диск и диск намного медленнее, поэтому PostgreSQL обычно не использует этот шанс). Имейте в виду, что это также влияет на случайный и последовательный доступ к страницам. Таким образом, вы можете получить лучшую производительность при более низких настройках shared_buffers.

У меня есть ощущение, что вы, вероятно, получите лучшую или, по крайней мере, более стабильную производительность в средах с высоким уровнем параллелизма и большими настройками shared_buffer. Также имейте в виду, что PostgreSQL захватывает эту память и удерживает ее, поэтому, если в системе выполняются другие функции, буферы чтения будут хранить файлы, прочитанные другими процессами. Это очень большая и сложная тема. Большие параметры общего буфера обеспечивают лучшие гарантии производительности, но могут в некоторых случаях обеспечивать меньшую производительность.

(0) сколько памяти на сервере и какие сейчас параметры?

max_connections
shared_buffers
effective_cache_size
maintenance_work_mem
wal_buffers
work_mem

(0) таки не хватает постгри или 1С-серверу или у вас всё на 1 сервере?
Ограничения по памяти на 1 меня сколько?

ни версии платформы 1С и ее разрадности, ни версии УНФ, ни версии постгри, ни версии ОСи — ничего! И кто-то должен оп и чего-то насоветовать.

Ну установите самый последний из существующих, так в нем обновленные уже готовые какие-то настройки будут

в настройках сервера 1с, там где локальный кластер, вызываешь свойства правой кнопкой мышью и если в поле допустимый объем памяти стоит вместо нуля какое то значение в кб то увелич его
Ошибка, грузит около 20 мин, выдает ошибку недостаточно памяти. как понять какой именно памяти ему не хватает?
при этом на жестком диске до ошибки сжирает почти 3гб
Вангую — памяти не хватает серверу предприятия. Если бы памяти было мало СУБД, то в тексте ошибки было-бы что-то указывающее на Postgres. Что-то типа ошибка SQL
(8) в диспетчере на момент перед самой ошибкой кто сжирает памяти много и сразу после ошибки ее высвобождает?
(8) На момент возникновения ошибки в логи PostgreSQL что пишется? Что-нибудь вроде «out of memory» или «Недостаточно памяти для получения результата запроса к базе данных»?
(15) Попробуй на клиенте запустить 64 бита версию платформы
(17) не все, а платформа на сервере. На клиенте может и 32 стоять вполне себе успешно. Ну в режиме толстого клиента пробуй запустить.
(17) просто сжирание на локальном клиенте временного файла до 3 Гб как бы намекает на предел в памяти, который давно существует в майкрософтовых 32бита процессах — 3 ГБ с копейками. Какие-то особенности системы. В теории:
2 ГБ — знаковое целое int.
4 ГБ — беззнаковое целое int
внутренние особенности решения на винде 4 ГБ не позволяют, а только 3 ГБ

(20) Ну вот и еще одну техническую подробность удалось вытащить клещами.
Вообще-то в терминале можно поставить ограничение

А с локальных компов доступ к серверу в принципе возможен? С клиента без использования терминала что выдает? Просто по РДП без терминала на самом сервере с его же платформой.

На всякий случай, выше указывалось проверить сколько памяти задано в настройках агента сервера 1С на каждый рабочий процесс. Вот на это тоже можно посмотреть. Но эта настройка тогда будет давать такую же ошибку при работе с сервером в клиенте с локального компа, без терминала.

но ошибка по рдп
Поменял еще раз параметры postgre перечитал конфу перезагрузил службы. Пока грузит ошибку не выдает. но база растет как на дрожжах. уже на 1гб выросла.
такими темпами каждое закрытие месяца по 2гб не очень хорошо

(22) по росту базы ничего не скажу

Если бы проблема была «на стороне сервера», а не клиента, то при любом способе подключения клиентского было бы одинаковая проблема.

Читайте также:

• Не удалось загрузить модуль regsvr32 проверьте что двоичный файл
• Digma dvr 101 обзор
• Как выпросить у родителей компьютер
• Как создать папку с именем файла
• Не время для драконов компьютерная игра прохождение

SQL Error [53200]: ОШИБКА: нехватка разделяемой памяти Подсказка: Возможно, следует увеличить параметр max_locks_per_transaction

При выполнении запросов на БД (Postgres) возникла ошибка:

24.02.21 13:50:38.219,main,ERROR,ExecSql,null
com.bssys.db.jdbc.DBSQLException: ОШИБКА: нехватка разделяемой памяти
Подсказка: Возможно, следует увеличить параметр max_locks_per_transaction.

Подробная информация по параметру здесь. Коротко ниже:
max_locks_per_transaction (integer)
Этот параметр управляет средним числом блокировок объектов, выделяемым для каждой транзакции; отдельные транзакции могут заблокировать и больше объектов, если все они умещаются в таблице блокировок.

Значение по умолчанию = 64

рядом также находится параметр max_pred_locks_per_transaction (integer)

В файле postgresql.conf (Postgres/data/) указано так:

#———————————————————————-

# LOCK MANAGEMENT

#———————————————————————-

#deadlock_timeout = 1s

#max_locks_per_transaction = 64 # min 10

# (change requires restart)

#max_pred_locks_per_transaction = 64 # min 10

# (change requires restart)

Изменил на это значение:

#———————————————————————-

# LOCK MANAGEMENT

#———————————————————————-

#deadlock_timeout = 1s

max_locks_per_transaction = 256 # min 10

# (change requires restart)

max_pred_locks_per_transaction = 1000 # min 10

# (change requires restart)

P.S. После изменения убрать символ «#» в начале строки

Популярные сообщения из этого блога

Время на прочтение
3 мин

Количество просмотров 15K

Короткая история о «тяжелом» запросе и изящном решении проблемы

Недавно нас по ночам стали будить алерты: на диске не хватает места. Мы быстро разобрались, что проблема в 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 МБ памяти.

Почему так?

PostgreSQL выделяет память для хэширования и сортировки. Объем памяти управляется параметром work_mem. Размер work_mem по умолчанию — 4 МБ. Если для хэширования или сортировки нужно больше 4 МБ, PostgreSQL временно задействует пространство на диске.

Наш запрос потребляет явно больше 4 МБ, поэтому база данных использует столько памяти. Мы решили: спешить не будем, — и не стали увеличивать параметр и расширять хранилище. Лучше поискать другой способ урезать память для сортировки.

Экономная сортировка

«Сколько сортировка съест – зависит от размера набора данных и ключа сортировки. Набор данных не уменьшишь, а вот размер ключа — можно.

За точку отсчета возьмем средний размер ключа сортировки:

   avg
----------
   780

Каждый ключ весит 780. Чтобы уменьшить двоичный ключ, его можно хэшировать. В PostgreSQL для этого есть md5 (да, не секьюрно, но для нашей цели сойдет). Посмотрим, сколько весит BLOB, хэшированный с помощью md5:

    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 лишних мегабайта, то есть чуть больше 10% от прежних 30 МБ. Значит размер ключа сортировки сильно влияет на то, сколько памяти отъедает сортировка.

Дальше — больше

В этом примере мы хэшировали BLOB с помощью md5. Хэши, созданные с MD5, должны весить 16 байт. А у нас получилось больше:

md5_size
-------------
32

Наш хэш был ровно в два раза больше, ведь md5 выдает хэш в виде шестнадцатеричного текста.

В PostgreSQL можно использовать MD5 для хэширования с расширением pgcrypto. pgcrypto создает MD5 типа bytea (в двоичном виде):

select pg_column_size( digest('foo', 'md5') ) as crypto_md5_size

crypto_md5_size
---------------
20

Хэш все равно на 4 байта больше положенного. Просто тип bytea использует эти 4 байта, чтобы хранить длину значения, но мы этого так не оставим.

Оказывается, тип uuid в PostgreSQL весит ровно 16 байт и поддерживает любое произвольное значение, так что избавляемся от оставшихся четырех байтов:

uuid_size
---------------
16

Вот и все. 32 байта с md5 превращаются в 16 с uuid.

Я проверил последствия изменения, взяв набор данных побольше. Сами данные показывать нельзя, но я поделюсь результатами:

Как видно из таблицы, исходный проблемный запрос весил 300 МБ (и будил нас среди ночи). С ключом uuid сортировке потребовалось всего 7 МБ.

Соображения вдогонку

Запрос с хэшированным ключом сортировки памяти потребляет меньше, зато работает гораздо медленнее:

Хэширование задействует больше ЦП, поэтому запрос с хэшем работает медленнее. Но мы пытались решить проблему с пространством на диске, к тому же задача выполняется ночью, так что время — не проблема. Мы пошли на компромисс, чтобы сэкономить память.

Это отличный пример того, что не всегда нужно стараться ускорить запросы к базе данных. Лучше сбалансированно использовать что есть, и выжимать максимум из минимума ресурсов.