Как сделать большую базу данных postgres быстрее?

Не нужно писать в базу данных, только читать.

Таким образом, БД доступна только для чтения. И в комментариях:

db отлично работал с небольшим объемом данных (несколько ГБ), но после того, как я заполнил базу данных, она начала замедляться.

Таким образом, индексы были построены постепенно.

Индексы

UNIQUE CONSTRAINT на (song_id, "offset", hash)

Я бы заменил это на:

ALTER TABLE fingerprints
  DROP CONSTRAINT uq_fingerprints
, ADD CONSTRAINT uq_fingerprints UNIQUE(hash, song_id, "offset") WITH (FILLFACTOR = 100)

Это накладывает то же ограничение, но ведущий столбец hash в базовом индексе B-дерева теперь поддерживает фильтр по hash в отображаемом запросе. А тот факт, что все необходимые столбцы включены в индекс, позволяет намного быстрее сканировать только индекс. (Меньший) индекс также должен легче кэшироваться, чем (большая) таблица (плюс индекс).

Видеть:

• Подходит ли составной индекс и для запросов по первому полю?

Также перезаписывает индекс в исходном состоянии и с FILLFACTOR 100 для БД только для чтения. (Вместо 90 по умолчанию для индекса B-дерева.)

Хэш-индекс на (hash) и CLUSTER

Имя столбца «хэш» не имеет ничего общего с именем типа индекса, который также является «хэшем». (Сначала столбец, вероятно, не должен называться «хеш».)

Если (и только если) у вас также есть другие запросы, сосредоточенные вокруг одного из нескольких значений hash, которые не могут использовать сканирование только по индексу (и вы действительно видите более быстрые запросы, чем без них), дополнительно сохраните хеш-индекс. И оптимизировать его. (Иначе бросай!)

ALTER INDEX ix_fingerprints_hash SET (FILLFACTOR = 100);

Постепенно увеличивающийся индекс может привести к раздуванию или несбалансированному переполнению страниц в случае хэш-индекса. REINDEX должны позаботиться об этом. При этом увеличьте FILLFACTER до 100 (с 75 по умолчанию для хеш-индекса) для вашей БД, доступной только для чтения (!) Вы можете REINDEX сделать изменение эффективным.

REINDEX INDEX ix_fingerprints_hash;

Или вы можете CLUSTER (как уже предложил jjanes) в перестроенном индексе B-дерева сверху:

CLUSTER fingerprints USING uq_fingerprints;

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

После завершения оптимизации запустите один раз:

VACUUM ANALYZE fingerprints;

work_mem

Крошечная настройка для work_mem выделяется:

work_mem = 582kB

Даже (очень консервативно!) значение по умолчанию составляет 4 МБ. Но после повторного прочтения вашего вопроса может показаться, что у вас есть только крошечные запросы. Так что, может быть, это нормально в конце концов.

В противном случае с 16 ГБ ОЗУ вы обычно можете позволить себе в 100 раз больше. Зависит от вашей загруженности конечно.

• Много маленьких запросов, много параллельных воркеров --> сохраняйте маленький размер work_mem (например, 4 МБ?)
• Несколько больших запросов, несколько параллельных рабочих операций --> увеличение (например, 256 МБ? или больше)

Большое количество временных файлов, записанных в вашу базу данных с течением времени, и упоминание «диска» в выводе EXPLAIN ANALYZE указывает на необходимость большего количества work_mem.

Дополнительный вопрос

Будет ли база данных ускорена за счет приобретения дополнительной оперативной памяти для обработки всей БД внутри (например, 128 ГБ)?

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

И какие параметры я должен изменить, чтобы Postgres хранил БД в оперативной памяти?

Все, что считывается из базы данных, автоматически кэшируется в системном кеше и кеше Postgres вплоть до предела доступной оперативной памяти. (Слишком высокое значение work_mem конкурирует за тот же ресурс.)

Увеличение оперативной памяти, чтобы все могло оставаться в кеше (возможно, после использования pg_prewarm, чтобы сначала поместить это в кеш), безусловно, сработает. Но это дорого и не должно быть необходимым.

Наличие хеш-индекса для чего-то, что уже является хэшированным значением, вероятно, не очень полезно. Вы пробовали вместо этого просто индекс по умолчанию (btree)?

Если вы КЛАСТЕРИЗУЕТЕ таблицу в индексе по столбцу с именем «хэш» (что вы можете сделать, только если это индекс btree), тогда строки с одинаковым хэш-кодом должны в основном использовать одну и ту же страницу таблицы, что значительно сократит количество различных чтений буфера, необходимых для получения их всех.

Если бы вы могли заставить его выполнять сканирование кучи растровых изображений вместо сканирования индекса, то он должен иметь возможность одновременно обрабатывать большое количество запросов на чтение из-за Effective_io_concurrency. Но планировщик не учитывает Effective_io_concurrency при планировании, что означает, что он не будет выбирать сканирование кучи растровых изображений специально, чтобы получить это преимущество. Обычно чтение индекса, находящее сотни строк на разных страницах, автоматически выбирает метод сканирования кучи растрового изображения, но в вашем случае, вероятно, низкая настройка random_page_cost препятствует этому. Низкое значение random_page_cost, вероятно, само по себе разумно, но у него есть неприятный побочный эффект. Проблема с этой стратегией заключается в том, что она не уменьшает общее количество необходимых операций ввода-вывода, она просто позволяет им перекрываться и, таким образом, лучше использовать несколько каналов ввода-вывода. Но если во многих сеансах выполняется несколько экземпляров этого запроса одновременно, они начнут заполнять эти каналы и, таким образом, начнут конкурировать друг с другом. Таким образом, метод CLUSTER, вероятно, лучше, поскольку он дает тот же ответ с меньшим количеством операций ввода-вывода. Если вы хотите поиграть со сканированием растровых изображений, вы можете временно увеличить значение random_page_cost или временно отключить параметр enable_indexscan.