В прошлых постах мы довольно тщательно разобрали структуру индексов, и нам осталось разобраться, откуда происходит чтение как данных, так и индексов: из диска, или из оперативки, или «зависит»?

📚 Несколько кэшей
Представь, что ищешь нужную книгу: она может быть на твоём рабочем столе — прям под рукой, очень близко; или на полке рядом — на шаг дальше; или вообще в книжном шкафу в другой комнате — туда топать дольше всего. Память под страницы у СУБД устроена такими же уровнями, и за страницей она идёт по ним сверху вниз:

1️⃣ shared_buffers — рабочий стол. Собственный кэш СУБД, она сама им рулит. В Postgres по умолчанию всего 128 MB.
2️⃣ кэш ОС (page cache) — полка рядом. Операционка сама держит в RAM файлы, которые недавно читала; СУБД им не управляет, но пользуется.
3️⃣ диск — шкаф в другой комнате.

Глянула 1️⃣ shared_buffers → нет → 2️⃣ спросила ОС, та смотрит, что есть в RAM → нет → идем на 3️⃣ диск Найденную страницу обычно кладём обратно в shared_buffers, чтобы следующий запрос взял её уже оттуда.

🧩 Данные и индексы — в одном кэше
Тут есть контр-интуитивный момент: и куча с данными (heap — страницы, где лежат сами строки таблицы), и индексы (страницы с элементами дерева) лежат в одном shared_buffers, конкурируя за место. Отдельной «памяти под индексы» нет 🤯

🌳 Почему индекс будто всегда в RAM
Верхушка B-дерева (корень и внутренние узлы) — набор страниц, который дёргает каждый поиск. Из-за частых переходов по дереву верхние уровни почти всегда в кэше, так как с них начинается почти любой поиск, поэтому дочитывать чаще приходится только лист и/или саму страницу кучи.

🔬 Пощупаем (дикпик 1)
EXPLAIN (ANALYZE, BUFFERS): секция BUFFERS показывает, сколько страниц нашли в shared_buffers (hit), а сколько пришлось дочитывать (read).
Холодный поиск по PK на таблице в 1 млн строк, у меня в эксперименте: shared read=4 — три страницы индекса плюс одна кучи, всё мимо кэша.
Повтор: shared hit=4 — всё в shared_buffers диск не трогали, время заметно меньше.
Ищем другой далёкий id: hit=1 read=3 — корень уже в кэше с прошлого раза, поэтому он был переиспользован.

⚠️ Тонкость: read ≠ обязательно диск
«read» здесь значит лишь «не было в shared_buffers». Но страница могла прилететь мгновенно от ОС, а не из диска.
Отсюда неочевидное: два запроса с одинаковым read могут идти с разной скоростью — в одном случае страница пришла с диска, в другом уже лежала в кэше ОС.

🧑‍💻 Как это использовать в работе
1. Первый запрос после рестарта PostgreSQL (или сервера) обычно медленнее: кэш пустой, он наполняется заново. Это так называемый «холодный старт».
2. Index Only Scan (когда всё нужное есть в самом индексе и в кучу за строкой идти не надо) экономит не только чтения, но и кэш: чем меньше heap-страниц тащим в shared_buffers — тем больше места остаётся другим.

Команды из дикпика — в 👉 гисте 👈

Дальше разберём, куда девается оперативка, когда её внезапно «съели» под 99%, и как это диагностировать 👉

🧑‍💻dp 🥁
#бд #postgresql #инженерныештучки #heavywednesday