Подобно своим аналогам, PostgreSQL стремится к эффективной обработке запросов. Одним из способов достижения этой цели является использование общего буферного кэша. Этот кэш хранит часто запрашиваемые данные в памяти, что помогает ускорить их извлечение по сравнению с постоянным чтением с диска. Однако даже простые запросы SELECT иногда могут приводить к дисковой записи IO. Давайте разберемся, почему это происходит, и как это связано с внешней сортировкой. Мы рассмотрим два сценария: Грязные страницы в общих буферах и внешняя сортировка.
Общие буферы и чтение с диска
PostgreSQL придает большое значение целостности данных. Он обрабатывает изменения, будь то INSERT, UPDATE или DELETE, особым образом. Страница данных, затронутая этими изменениями в общем буфере, не обновляется немедленно. Вместо этого она становится «грязной», что означает различие между версией страницы данных в памяти и ее аналогом, хранящимся на диске.
Общий буферный пул — это инструмент, который PostgreSQL использует для хранения часто запрашиваемых страниц данных, удерживая их в памяти. Этот буфер работает как механизм кэширования, предлагая более быстрое извлечение информации по сравнению с постоянным чтением с диска. Однако поддержание этого кэша — это тщательный баланс между производительностью и целостностью данных.
Хотя PostgreSQL делает все возможное, чтобы часто запрашиваемые данные оставались в общем буфере, пространство ограничено. Когда буфер заполняется и нужно загрузить новую страницу для запроса на чтение, мы можем столкнуться со следующими ситуациями:
- Нет грязных страниц: Если в буфере нет грязных страниц, PostgreSQL может просто удалить наименее недавно использованную чистую страницу, чтобы освободить место для новой страницы, необходимой для запроса SELECT. Дисковые записи не требуются.
- Присутствуют грязные страницы: Если в буфере есть грязные страницы, а новая страница, необходимая для запроса SELECT, не находится в чистом состоянии, PostgreSQL должен сделать выбор:
- Удалить чистую страницу: Избавление от чистой страницы может быть быстрее, но если она скоро понадобится снова, нам придется повторно считывать те же данные с диска, что может замедлить процесс.
- Сбросить грязную страницу: Сброс грязной страницы сохраняет данные консистентными и освобождает место для новой страницы. Однако это означает дополнительную дисковую запись по сравнению с удалением только чистой страницы.
Выбор PostgreSQL: В этом сценарии PostgreSQL часто отдает приоритет консистентности данных и сбрасывает грязную страницу на диск перед загрузкой новой страницы, необходимой для запроса SELECT. Это гарантирует, что операция чтения работает с последними зафиксированными данными и избегает потенциальных несоответствий.
Практическое время
Запустите сервер Postgres локально с использованием docker compose yaml:
version: '3'
services:
postgres:
container_name: postgres
image: postgres:16.0
environment:
- POSTGRES_PASSWORD=postgres
- POSTGRES_USER=postgres
ports:
- "5432:5432"
volumes:
- ./volumes/postgres_data:/data/postgres
- ./init.sh:/docker-entrypoint-initdb.d/init.sh
В той же директории добавьте этот файл init.sh. Мы намеренно уменьшаем размер shared_buffers и увеличиваем checkpoint_timeout для нашего эксперимента:
#!/bin/bash
echo 'checkpoint_timeout=900' >> /var/lib/postgresql/data/postgresql.conf
echo 'shared_buffers=50MB' >> /var/lib/postgresql/data/postgresql.conf