Как работает PostgreSQL #postgresql

Фантомные чтения
(подробное описание в следующем посте)

#sql #mysql #postgresql #database

Фантомные чтения
(описание к предыдущему посту)

В PostgreSQL и MySQL возможно, что одинаковые запросы SELECT в рамках одной транзакции могут возвращать разные результаты.

Рассмотрим пример с базой данных, в которой работают два клиента. Клиент A начинает транзакцию и выполняет SELECT всех заказов с общей ценой > 100 долларов. Пока он продолжает выполнять другие запросы, клиент B вставляет в таблицу новый заказ и фиксирует изменения (COMMIT). Наконец, клиент A снова запрашивает заказы с общей суммой > 100 долларов, но на этот раз видит новую строку, добавленную клиентом B!

Допускается ли такое поведение, зависит от настроенного уровня изоляции.

По умолчанию PostgreSQL использует уровень READ COMMITTED, который допускает фантомные чтения. Отдельные запросы получают согласованное представление базы данных, но между запросами в рамках одной транзакции могут наблюдаться изменения, зафиксированные другими транзакциями.

Как и PostgreSQL, MySQL имеет четыре настраиваемых пользователем уровня изоляции. Использование строгого уровня, такого как SERIALIZABLE, предотвращает фантомные чтения, в то время как более свободные уровни, такие как READ COMMITTED, допускают их (по умолчанию в MySQL используется REPEATABLE READ).

Почему бы не установить везде уровень SERIALIZABLE? Всё дело в производительности! Более строгие уровни требуют большего количества блокировок и снижают производительность, в то время как более свободные уровни обеспечивают более высокую производительность за счёт возможных несоответствий.

#sql #mysql #postgresql #database

Многие используют СУБД Postgres, но не многие знают, что Postgres предоставляет специальную утилиту для проверки производительности - explain.
Для проверки производительности работы с базой данных регулярно запускайте команды explain и explain analyze и научитесь интерпретировать их выходные данные. Это может помочь найти узкие места при запросах к БД

#sql #postgresql #database

Что происходит, когда вы добавляете строку в Postgres?
(продолжение предыдущего поста)

Postgres должен гарантировать сохранность данных, обеспечивая при этом высокую производительность записи и возможность восстановления после сбоев. Ключ к решению этой задачи — журнал упреждающей записи (Write‑Ahead Log, WAL).

(1) Postgres получает запрос и определяет, на какую страницу данных его разместить. Эта страница может уже находиться в памяти (в буферном пуле), либо её придётся загрузить с диска, либо даже создать новую.

(2) Новая запись помещается только в эту страницу в памяти. Страница помечается как «грязная» (*dirty*), что означает: её нужно будет в будущем записать на диск — но не немедленно.

(3) Новая запись добавляется в буфер памяти для WAL. Она содержит всю информацию, необходимую для восстановления операции вставки.

(4) WAL записывается на диск (с помощью fsync или аналогичного механизма), чтобы гарантировать сохранение данных в надёжном хранилище. После успешного выполнения этого шага Postgres возвращает клиенту подтверждение об успешном выполнении.

Когда клиент получает подтверждение об успехе, данные гарантированно записаны в последовательный WAL (что обеспечивает высокую производительность записи), но не обязательно — в файл данных таблицы (где шаблоны операций ввода‑вывода менее предсказуемы). Последнее происходит позже — посредством контрольных точек, фоновых заданий или принудительной записи из‑за вытеснения страниц памяти. Если сбой сервера произойдёт до того, как данные будут записаны на диск, журнал будет воспроизведён для восстановления подтверждённых данных.

WAL — это основа всего процесса! Он обеспечивает высокопроизводительный ввод‑вывод и возможность восстановления после сбоев.

#sql #database #postgresql