Пояснение разницы скорости выполнения первого и последующих запросов к БД PostgreSQL

Каждый, кто работает с PostgreSQL рано или поздно задается вопросом, почему повторный запрос работает быстрее:

Потому что срабатывает кэширование.
И это не одно кэширование, а сразу несколько уровней:

1. Кэширование плана выполнения (Query Plan Cache)

Первый раз, когда отправляется SQL-запрос, PostgreSQL анализирует его:

Понимает структуру.
Строит оптимальный план выполнения (EXPLAIN).
Выбирает, какие индексы использовать, как делать JOIN-ы и т.д.

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

2. Кэширование данных в памяти (PostgreSQL Buffer Cache)

Когда таблица, индекс или часть данных читается первый раз, PostgreSQL загружает страницы (страницы по 8Кб) в оперативную память (shared_buffers). При последующих выполнениях запроса, если нужные данные уже есть в памяти (в shared_buffers), то с диска их читать НЕ нужно, данные берутся мгновенно из RAM. Чтение из RAM многократно быстрее, чем с диска.

3. Кэширование ОС (Filesystem Cache)

Даже если PostgreSQL сама не все выгрузит из shared_buffers (например, в очень загруженной системе), операционная система держит файлы в кэше на уровне файловой системы. В таком случае файловая система" при повторном доступе становится почти как RAM.

В качестве обобщения о причинах ускорения повторных запросов

Кэш плана запроса: повторно использовать уже готовый план выполнения.
Кэш данных в PostgreSQL: данные считываются не с диска, а из памяти (shared_buffers).
Кэш данных в операционной системе: файлы/страницы остаются в RAM, быстрее доступ.

Проверка утверждений

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

-- Первый запуск (будет медленно)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM большая_таблица WHERE условие;

-- Сразу повторный запуск (будет заметно быстрее)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM большая_таблица WHERE условие;

Первый Execution Time будет больше, второй — сильно меньше,
и в BUFFERS будет видно, что чтение идет из shared hit, а не disk read.

Что делать, если нет не кэшированной БД?

PostgreSQL есть разные уровни кэша - и очищать их нужно по-разному:

План выполнения запросов: DISCARD PLANS.
Сессионный кэш (подключение): DISCARD ALL.
Кэш ОС (filesystem cache): специальной командой в Linux (ручное сбрасывание).
Буферный кэш базы (shared_buffers): нельзя очистить напрямую — только через перезапуск сервера.

Подробнее, с примерами:

Очистить только планы выполнения (DISCARD) - Удаляет все кэшированные планы запроса в текущем соединении.

DISCARD PLANS;

Очистить буферный кэш PostgreSQL (shared_buffers) - перезапуск сервера (PostgreSQL сам управляет shared_buffers, и вручную очистить нельзя).

sudo systemctl restart postgresql

или

pg_ctl restart

Очистить кэш операционной системы (Linux) (нужны привелегии)

# Сбросить только pagecache:
sudo sync
echo 1 | sudo tee /proc/sys/vm/drop_caches

# Или полностью (pagecache + dentries + inodes):
sudo sync
echo 3 | sudo tee /proc/sys/vm/drop_caches

Для полной очистки в одной команде:

sudo systemctl restart postgresql
sudo sync
echo 3 | sudo tee /proc/sys/vm/drop_caches

# Как “прогреть” базу в PostgreSQL?

Первый способ. С помощью расширения:

PostgreSQL имеет специальное расширение для прогрева кэша: pg_prewarm. Шаги:

Установить расширение, если оно не установлено ранее (по умолчанию его нет):

CREATE EXTENSION IF NOT EXISTS pg_prewarm;

Прогреть таблицу (это загрузит данные таблицы в shared_buffers.):

SELECT pg_prewarm('название_таблицы');

Прогреть индекс:

SELECT pg_prewarm('название_индекса');

Второй способ. Если нет возможности установить расширение, можно просто принудительно прочитать все строки:

SELECT COUNT(*) FROM твоя_таблица;

или

SELECT * FROM твоя_таблица;

COUNT(*)предпочтительнее, не будет использоваться трафик для передачи большого ответа.

В результате:

PostgreSQL прогрузит все страницы таблицы в shared_buffers.
После этого реальные тестовые запросы будут выполняться “из памяти”.

Третий способ. Автоматический прогрев при старте PostgreSQL Для того, чтобы при перезапуске базы данные прогревались сами можно использовать pg_prewarm в связке с настройками.

В postgresql.conf включить pg_prewarm.autoprewarm:

shared_preload_libraries = 'pg_prewarm'

Тогда база будет сохранять список прогретых страниц и автоматически загружать их в кэш при старте сервера.

Краткое сравнение способов. Как и когда их применять?

pg_prewarm вручную: при единичном тестировании или периодическом прогреве.
Чтение всех строк (COUNT(*)): при простых тестах без расширений.
Автопрогрев (pg_prewarm + autoprewarm): для продакшн-систем с контролем прогрева при рестарте.

Проверка прогрета таблица или нет:

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT count(*) AS buffers_loaded
FROM pg_buffercache
WHERE relfilenode = (SELECT relfilenode FROM pg_class WHERE relname = 'имя_таблицы');

Нужно учесть тот момент, что в таблице pg_class в поле relfilenode значение 0 указывает на то, что это виртуальное или нефизическое отношение, которое не имеет собственного файла на диске. Обычно это относится к представлениям, индексам, секционированным таблицам или другим объектам, которые не хранят данные напрямую в файлах, а скорее зависят от других физических отношений. Так что для секционированной (партицированной) таблицы такой способ проверки не применим.

Дополнительно

Включаем pg_prewarm:

CREATE EXTENSION IF NOT EXISTS pg_prewarm;

Скрипт для прогрева всех таблиц схемы (этот код НЕ прогревает индексы):

DO $$
DECLARE
    rec record;
BEGIN
    -- Для каждой таблицы в указанной схеме
    FOR rec IN
        SELECT schemaname, tablename
        FROM pg_tables
        WHERE schemaname = 'public' -- поменяй на свою схему если нужно
    LOOP
        -- Прогреваем таблицу через pg_prewarm
        RAISE NOTICE 'Prewarming table: %.%', rec.schemaname, rec.tablename;
        PERFORM pg_prewarm(quote_ident(rec.schemaname) || '.' || quote_ident(rec.tablename));
    END LOOP;
END
$$ LANGUAGE plpgsql;

Результат будет выглядеть примерно так:

Расширенный вариант с индексами (если нужно прогревать и таблицы, и их индексы):

DO $$
DECLARE
    rec record;
BEGIN
    -- Прогреваем все таблицы
    FOR rec IN
        SELECT schemaname, tablename
        FROM pg_tables
        WHERE schemaname = 'public'
    LOOP
        RAISE NOTICE 'Prewarming table: %.%', rec.schemaname, rec.tablename;
        PERFORM pg_prewarm(quote_ident(rec.schemaname) || '.' || quote_ident(rec.tablename));
    END LOOP;

    -- Прогреваем все индексы
    FOR rec IN
        SELECT schemaname, indexname
        FROM pg_indexes
        WHERE schemaname = 'public'
    LOOP
        RAISE NOTICE 'Prewarming index: %.%', rec.schemaname, rec.indexname;
        PERFORM pg_prewarm(quote_ident(rec.schemaname) || '.' || quote_ident(rec.indexname));
    END LOOP;
END
$$ LANGUAGE plpgsql;

Хранимая процедура prewarm_schema, чтобы можно было прогревать любую схему одной командой:

CREATE OR REPLACE PROCEDURE prewarm_schema(schema_name text)
LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
BEGIN
    -- Прогрев всех таблиц в указанной схеме
    FOR rec IN
        SELECT schemaname, tablename
        FROM pg_tables
        WHERE schemaname = schema_name
    LOOP
        RAISE NOTICE 'Prewarming table: %.%', rec.schemaname, rec.tablename;
        PERFORM pg_prewarm(quote_ident(rec.schemaname) || '.' || quote_ident(rec.tablename));
    END LOOP;

    -- Прогрев всех индексов в указанной схеме
    FOR rec IN
        SELECT schemaname, indexname
        FROM pg_indexes
        WHERE schemaname = schema_name
    LOOP
        RAISE NOTICE 'Prewarming index: %.%', rec.schemaname, rec.indexname;
        PERFORM pg_prewarm(quote_ident(rec.schemaname) || '.' || quote_ident(rec.indexname));
    END LOOP;
END;
$$;

Вызов процедуры:

CALL prewarm_schema('public'); -- или любое другое имя схемы

Хранимая процедура только для таблиц (без индексов):

CREATE OR REPLACE PROCEDURE prewarm_only_tables(schema_name text)
LANGUAGE plpgsql
AS $$
DECLARE
    rec record;
BEGIN
    FOR rec IN
        SELECT schemaname, tablename
        FROM pg_tables
        WHERE schemaname = schema_name
    LOOP
        RAISE NOTICE 'Prewarming table: %.%', rec.schemaname, rec.tablename;
        PERFORM pg_prewarm(quote_ident(rec.schemaname) || '.' || quote_ident(rec.tablename));
    END LOOP;
END;
$$;

Каждый, кто работает с PostgreSQL рано или поздно задается вопросом, почему повторный запрос работает быстрее:#

1. Кэширование плана выполнения (Query Plan Cache)#

2. Кэширование данных в памяти (PostgreSQL Buffer Cache)#

3. Кэширование ОС (Filesystem Cache)#

В качестве обобщения о причинах ускорения повторных запросов#

Проверка утверждений#

Что делать, если нет не кэшированной БД?#

# Как “прогреть” базу в PostgreSQL?#

Краткое сравнение способов. Как и когда их применять?#

Проверка прогрета таблица или нет:#

Дополнительно#