🔤🔤🔤 Журнал транзакций в PostgreSQL и MS SQL
Во время обучения по PostgreSQL часто встречаю заблуждения по поводу лога транзакций (в PostgreSQL это Журнал предзаписи (Write-Ahead Logging) - что гораздо точнее отражает его суть.
Давайте попробуем прояснить главные отличия этого механизма у двух СУБД.
1. Как хранится журнал транзакций
MS SQL - по умолчанию хранит в одном файле на каждую базу
PostgreSQL - по умолчанию хранится в файлах по 16МБ на весь сервер
2. Нужен ли журнал транзакций для "отката" транзакции
MS SQL - да
PostgreSQL - нет
❗️Тут давайте остановимся чуть подробнее.
"Откат" транзакции в MS SQL происходит с помощью чтения Журнала транзакций с момента начала "отката" до момента начала транзакции в обратном хронологическом порядке и в базе "отыгрываются" все произведённые этой транзакцией изменения.
Соответственно, чтобы обеспечить возможность "отката" транзакции СУБД обязана сохранять весь журнал транзакций с момента её начала.
Из-за этого в MS SQL журнал может неожиданно неконтролируемо вырасти и либо занять всё место на диске либо упереться в максимальный размер файла, заданный в настройках базы.
"Откат" транзакции в PostgreSQL происходит путём смены одного бита в служебной таблице pg_xact, где для каждой транзакции есть запись со статусом committed или aborted.
Поэтому "откат" транзакции в PostgreSQL происходит мгновенно и не требует хранения журнала транзакций (WAL).
Соответственно и роста каталога с WAL по причине долгой транзакции происходить не может.
3. На что влияет операция checkpoint
MS SQL - именно эта операция делает сброс всех "изменённых" страниц с кэша в базу и ставит "метку" в файле Журнала транзакций об этом факте. Теперь журнал транзакций "левее" этой метки может быть заархивирован либо перезаписан новыми данными. (Если нет реплик)
PostgreSQL - Так же делает сброс всех "изменённых" страниц с кэша в базу и ставит "метку" в файле Журнала транзакций об этом факте. Теперь файлы в каталоге с WAL "левее" этой метки могут быть заархивированы либо удалены. (Если нет реплик)
4. Реплики и их влияние на журнал транзакций
MS SQL - будет сохранять журнал пока реплика его весь не заберёт себе. В случае если журнал прирастает быстрее чем его забирает реплика может произойти переполнение диска или максимального размера журнала транзакций и остановка сервера СУБД
PostgreSQL - будет сохранять файлы журнала предзаписи пока реплика созданная со слотом репликации не заберет себе эти файлы. Помним что файлы по 16 МБ, т.е. они и удаляться будут постоянно со скоростью, с которой реплика успевает их себе забрать.
Таким образом даже если общий объём журнала предзаписи за промежуток времени превышает объём диска по WAL, то всё равно место скорее всего не закончится.
Но, если реплика не успевает катастрофически или вообще отключилась, то у нас есть риск переполнения диска.
Для избегания этого риска в PostgreSQL есть настройка: max_slot_wal_keep_size - задаёт максимальный размер файлов WAL, который может оставаться в каталоге pg_wal для слотов репликации после выполнения контрольной точки.
Если объём файлов в каталоге превысит это значение, то слот репликации, которому нужны самые старые файлы WAL будет выключен и каталог будет очищен максимально для продолжения безопасной работы СУБД.
5. Архивирование журнала транзакций
MS SQL - заархивировать можно только журнал "левее" последнего checkpoint. Если у нас не происходит checkpoint или не работает/"завис" процесс архивирования, то сервер будет накапливать журнал и мы упрёмся или в место на диске или в ограничение размера файла журнала транзакций.
PostgreSQL - заархивировать можно любой файл WAL, кроме текущего. НО если у вас по какой-то причине процесс архивирования не работает или "завис", то сервер будет копить файлы WAL и мы придём опять к ситуации окончания места на диске.
Понимание этих основных отличий работы СУБД с журналами транзакций (предзаписи) поможет в понимании механизмов и разборе ситуаций с окончанием места на дисках СУБД.
❗️Место на дисках - САМАЯ ЧАСТАЯ проблема остановки или неадекватного поведения продуктивных серверов как СУБД так и 1С!
