lesson ── postgres-labs ── ~22 мин ── 5 шагов

Загляни в буферный кеш и поймай попадания

PostgreSQL не читает данные с диска напрямую - он держит страницы в общем буферном кеше. Сейчас ты заглянешь в этот кеш через pg_buffercache, прогреешь таблицу запросом и поймаешь разницу между чтением с диска (read) и попаданием в кеш (hit). Перед каждым шагом предскажи, что покажет счётчик.

интерактивный sandbox

Поднимется контейнер postgreslab/postgres-base с PostgreSQL 17 и psql. В браузере откроется терминал, база lab уже настроена. Каждый шаг проверяется автоматически. Сеть air-gapped, наружу контейнер не ходит.

запустить sandbox →

stack ── PostgreSQL 17 · psql · 1 GB RAM · air-gapped · самоуничтожается через 45 мин простоя

Шаги

01
Подключи pg_buffercache
sql
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
Это линза для буферного кеша: она показывает каждый занятый буфер - какой странице какого отношения он принадлежит, грязный ли он, какой у него usage_count.
✓ Линза подключена — кеш больше не чёрный ящик.
02
Прогрей таблицу запросом
sql
SELECT count(*) FROM tickets;
SELECT count(*) AS buffers
FROM pg_buffercache
WHERE relfilenode = pg_relation_filenode('tickets');
Первый запрос поднял страницы tickets в кеш. Предскажи: сколько буферов она теперь занимает - ноль или больше?
✓ Страницы таблицы в кеше — буфер занят.
03
Поймай попадания в кеш
sql
SELECT count(*) FROM tickets; -- повтор: данные уже в кеше
SELECT heap_blks_hit, heap_blks_read
FROM pg_statio_user_tables WHERE relname = 'tickets';
Повторный запрос идёт из кеша - это попадания (hit), а не чтения с диска (read). Предскажи: какой счётчик будет расти при повторных запросах?
подсказка
Статистика обновляется с небольшой задержкой — если hit ещё ноль, повтори SELECT.
✓ Попадания в кеш растут — диск не трогаем.
04
Сделай буферы грязными и посчитай их
sql
UPDATE tickets SET passenger = passenger || '*';
SELECT buffers_used, buffers_dirty
FROM pg_buffercache_summary();
UPDATE изменил страницы в памяти - они стали «грязными» и отличаются от диска. buffers_dirty - сколько таких сейчас. Их потихоньку сбрасывают bgwriter и контрольная точка, так что число живое.
✓ Ты видишь грязные буферы — изменения пока только в памяти.
05
Посмотри сводку по всему кешу
sql
SELECT buffers_used, buffers_unused, buffers_dirty,
usagecount_avg
FROM pg_buffercache_summary();
pg_buffercache_summary (с PostgreSQL 16) отдаёт агрегат одним дешёвым запросом, не сканируя кеш построчно - удобно для мониторинга.
✓ Ты снял сводку по всему кешу — видно, сколько буферов в деле.

Что ты узнал

Буферный кеш держит страницы в разделяемой памяти. pg_buffercache показывает занятые буферы, pg_statio_user_tables - попадания и чтения, pg_buffercache_summary - дешёвый агрегат. Повторный запрос идёт из кеша (hit), UPDATE оставляет грязные буферы.

команды

SELECT count(*) FROM pg_buffercache WHERE relfilenode = pg_relation_filenode('tickets');буферы конкретной таблицы
SELECT heap_blks_hit, heap_blks_read FROM pg_statio_user_tables WHERE relname='tickets';попадания против чтений
SELECT * FROM pg_buffercache_summary();дешёвая сводка по кешу (PG16)

концепции

· повторный запрос идёт из кеша — это попадание (hit), а не чтение
· UPDATE делает буферы грязными — изменения пока только в памяти
· pg_buffercache_summary даёт агрегат без построчного скана

Шаги

Подключи pg_buffercache

sql

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

Это линза для буферного кеша: она показывает каждый занятый буфер - какой странице какого отношения он принадлежит, грязный ли он, какой у него usage_count.

✓ Линза подключена — кеш больше не чёрный ящик.

Прогрей таблицу запросом

sql

SELECT count(*) FROM tickets;

SELECT count(*) AS buffers

FROM pg_buffercache

WHERE relfilenode = pg_relation_filenode('tickets');

Первый запрос поднял страницы tickets в кеш. Предскажи: сколько буферов она теперь занимает - ноль или больше?

✓ Страницы таблицы в кеше — буфер занят.

Поймай попадания в кеш

sql

SELECT count(*) FROM tickets;   -- повтор: данные уже в кеше

SELECT heap_blks_hit, heap_blks_read

FROM pg_statio_user_tables WHERE relname = 'tickets';

Повторный запрос идёт из кеша - это попадания (hit), а не чтения с диска (read). Предскажи: какой счётчик будет расти при повторных запросах?

подсказка

Статистика обновляется с небольшой задержкой — если hit ещё ноль, повтори SELECT.

✓ Попадания в кеш растут — диск не трогаем.

Сделай буферы грязными и посчитай их

sql

UPDATE tickets SET passenger = passenger || '*';

SELECT buffers_used, buffers_dirty

FROM pg_buffercache_summary();

UPDATE изменил страницы в памяти - они стали «грязными» и отличаются от диска. buffers_dirty - сколько таких сейчас. Их потихоньку сбрасывают bgwriter и контрольная точка, так что число живое.

✓ Ты видишь грязные буферы — изменения пока только в памяти.

Посмотри сводку по всему кешу

sql

SELECT buffers_used, buffers_unused, buffers_dirty,

       usagecount_avg

FROM pg_buffercache_summary();

pg_buffercache_summary (с PostgreSQL 16) отдаёт агрегат одним дешёвым запросом, не сканируя кеш построчно - удобно для мониторинга.

✓ Ты снял сводку по всему кешу — видно, сколько буферов в деле.

Что ты узнал

команды

SELECT count(*) FROM pg_buffercache WHERE relfilenode = pg_relation_filenode('tickets');буферы конкретной таблицы
SELECT heap_blks_hit, heap_blks_read FROM pg_statio_user_tables WHERE relname='tickets';попадания против чтений
SELECT * FROM pg_buffercache_summary();дешёвая сводка по кешу (PG16)

концепции

· повторный запрос идёт из кеша — это попадание (hit), а не чтение
· UPDATE делает буферы грязными — изменения пока только в памяти
· pg_buffercache_summary даёт агрегат без построчного скана