Как оптимизировать время доступа к базе данных PostgreSQL

Что проверяют

Начинаете ли вы с измерения или сразу крутите shared_buffers. От Java-разработчика ждут понимания, что половина медленных запросов рождается в ORM и пуле соединений, а не в самой базе. Следом обычно просят разобрать план конкретного запроса.

Как отвечать

Идите слоями, от поиска виновника к настройкам:

• найти медленное: pg_stat_statements покажет запросы с наибольшим суммарным временем, log_min_duration_statement и auto_explain поймают медленные в логах;
• разобрать план: EXPLAIN (ANALYZE, BUFFERS) даёт реальное время, число строк против оценки и чтения с диска. Расхождение оценки с фактом лечат ANALYZE и статистикой, последовательное сканирование большой таблицы обычно индексом: составным, частичным или по выражению;
• ORM: N+1 в Hibernate убирают JOIN FETCH, @EntityGraph или hibernate.default_batch_fetch_size. Для массовой вставки нужны hibernate.jdbc.batch_size и id через SEQUENCE: с IDENTITY Hibernate не батчит вставки. В pgjdbc добавьте reWriteBatchedInserts=true;
• пул: HikariCP по умолчанию держит 10 соединений, и это разумная отправная точка. Каждое соединение в PostgreSQL это отдельный процесс, пул на сотни соединений лишь добавит конкуренцию;
• код приложения: keyset-пагинация вместо большого OFFSET, setFetchSize для больших выборок (без него драйвер читает весь результат в память, а работает он только при выключенном автокоммите), короткие транзакции без HTTP-вызовов внутри @Transactional;
• конфигурация: shared_buffers, work_mem, effective_cache_size, автовакуум, но уже после того, как запросы приведены в порядок.

Где спотыкаются

Не выключают spring.jpa.open-in-view. В Spring Boot он включён по умолчанию, соединение может удерживаться до конца HTTP-запроса, а ленивые загрузки тихо плодят N+1 в контроллерах.

Смотрят EXPLAIN без ANALYZE. Это только оценка планировщика, настоящую причину она часто не показывает.

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