Советы по Postgres для Начинающих
Давно я не писал ничего про базы данных. А тут наткнулся на советы по PostgreSQL и решил пройтись по ним, а заодно повторить для себя некоторые моменты, поскольку скоро мне предстоит переход на эту СУБД. Это будет серия постов с тегом #PostgresTips, которые периодически будут появляться на канале.
1. Кортежи — это физические версии строк
Одним из основополагающих аспектов PostgreSQL, который удивляет многих новичков, является концепция кортежей. Попросту говоря, кортеж в Postgres — это физическая версия строки данных. Это означает, что при изменении данных в строке вместо изменения существующих данных Postgres добавляет новую версию этой строки, кортеж. Эта система управления версиями называется MVCC (Multiversion Concurrency Control), и важно понимать её для разработки высокопроизводительных систем.
Вот что происходит во время различных операций записи:
- Когда вы выполняете команду DELETE, она не сразу освобождает место на диске. Вместо этого старый кортеж помечается как мёртвый, но остаётся в БД до тех пор, пока VACUUM не удалит его. Если эти мёртвые кортежи накапливаются и удаляются при очистке больших объёмов, это приводит к раздуванию таблиц и индексов (они занимают гораздо больше места на диске, чем содержат данных).
- Точно так же, когда вы обновляете строку, Postgres не изменяет существующий кортеж. Вместо этого он создаёт новую версию этой строки (новый кортеж) и помечает старую как мёртвую.
- Даже отменённый INSERT создаёт мёртвый кортеж, что может удивить многих. Это означает, что, если вы попытаетесь вставить запись, а затем откатите это действие, кортеж, который должен был быть вставлен, помечается как мёртвый.
Чтобы помочь понять эти концепции, каждая таблица в Postgres имеет скрытые столбцы, которые вы можете посмотреть: ctid, xmin и xmax. ctid представляет расположение кортежа (номер страницы + смещение внутри неё), а xmin и xmax можно рассматривать как «дату (номер транзакции) рождения» и «дату смерти» для кортежей.
Поняв это поведение на раннем этапе, вы будете лучше подготовлены к решению проблем, связанных с дисковым пространством, раздуванием и процессами автоочистки, которые направлены на удаление этих мёртвых кортежей. Вот базовый пример, тривиальный, но очень важный:
pg=# create table t1 as select 1 as id;Мы создали таблицу с одной строкой, проверили расположение живого кортежа этой строки (ctid), а затем сделали обновление, которое логически ничего не делает, т.е. не меняет значение. Но местоположение изменилось с (0,1) (страница 0, смещение 1) на (0,2). Потому что физически Postgres создал новый кортеж — новую версию строки. Понимание этого поведения Postgres поможет вам проектировать системы, работающие более эффективно.
SELECT 1
pg=# select ctid, xmin, xmax, * from t1;
ctid | xmin | xmax | id
-------+-------+------+----
(0,1) | 47496 | 0 | 1
(1 row)
pg=# update t1 set id = id where id = 1;
UPDATE 1
pg=# select ctid, xmin, xmax, * from t1;
ctid | xmin | xmax | id
-------+-------+------+----
(0,2) | 47497 | 0 | 1
(1 row)
Источник: https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners