Объяснение мема Postgres - Уровень 4: Зона полуночи
Добро пожаловать в зону полуночи! На этом уровне мы еще больше погрузимся в глубины PostgreSQL.
Денормализация
Одним из первых этапов проектирования приложений баз данных является процесс нормализации (1NF, 2NF, ...) с целью уменьшения избыточности данных и улучшения их целостности. Несмотря на то, что реляционные базы данных, в частности Postgres, хорошо оптимизированы для работы с большим количеством первичных и внешних ключей в таблицах, способны объединять множество таблиц и обрабатывать множество ограничений, нормализованная схема все равно может оказаться сложной для работы из-за снижения производительности.
В таком случае, если поддержание полностью нормализованной таблицы становится проблематичным, разработчик базы данных может прибегнуть к процессу, известному как денормализация. Она призвана повысить производительность чтения данных, но при этом снижает производительность записи данных.
PostgreSQL поддерживает множество денормализованных типов данных, включая массивы, составные типы, создаваемые с помощью create type, xml и json типы. Материализованные представления также используются для реализации компромисса между более быстрым чтением и более медленной записью. Материализованное представление - это представление, которое хранится на диске. Вы можете создать денормализованное и материализованное представление данных для быстрого чтения, а затем использовать refresh materialized view my_view для обновления этого кэша.
NULL в ограничениях CHECK являются истинными
Как мы уже отмечали ранее, null в SQL означает скорее незнание значения, чем его отсутствие, и такой select вернет null:
select null > 7; -- null
Если мы создадим столбец ограничением следующим образом:
create table mature_person(id integer primary key, age integer check(age >= 18));
а затем попытаемся вставить строку, в которой возраст равен 15, то получим такую ошибку:
ERROR: new row for relation "mature_person" violates check constraint "mature_person_age_check" DETAIL: Failing row contains (1, 15).
Однако insert будет выполнена успешно:
insert into mature_person(id, age) values (1, null) -- INSERT 0 1 -- Query returned successfully in 80 msec.
Возможно, не имеет смысла предполагать, что что-то удовлетворяет ограничению, когда Вы не знаете его значения (null), но в SQL мы должны позволить строке быть полной, поскольку null в ограничениях являются истинными.
Конфликт транзакций
Конфликт ресурсов - это конфликт за доступ к общему ресурсу, такому как оперативная память, сетевой интерфейс, хранилище и т.д. В случае баз данных SQL конфликт ресурсов может проявляться в виде конфликтов транзакций, когда несколько транзакций хотят записать строку одновременно. Для устранения противоречий между транзакциями могут потребоваться задержки, повторные попытки или остановки, так как они могут вызвать блокировки. Это можно настроить с помощью параметра deadlock_timeout.
Конфликты, как правило, замедляют работу базы данных, и этот негативный эффект усиливается при наличии нескольких узлов или кластеров. Тем не менее, такие проекты, как Postgres-BDR, могут предоставлять инструменты для диагностики и устранения подобных проблем.
SELECT FOR UPDATE
Предложение select используется для чтения данных из базы, но иногда требуется выделить строки для их записи. Если указана любая из перечисленных ниже блокировок:
-
select ... for update -
select ... for no key update -
select ... for share -
select ... for key share
оператор select блокирует все выбранные строки (а не только столбцы) от одновременного обновления:
begin; select * from users WHERE group_id = 1 FOR UPDATE; update users set balance = 0.00 WHERE group_id = 1; commit;
Этот способ иногда называют пессимистической блокировкой. При использовании явной блокировки следует быть осторожным, так как при выполнении длительных работ в транзакции база данных будет блокировать строки на все время:
begin; select * from users WHERE group_id = 1 FOR UPDATE; -- rows will remain locked and cause performance degradation -- doing a lot of time consuming calculations update users set balance = 0.00 WHERE group_id = 1; commit;
В таких случаях можно перейти на оптимистическую блокировку. Оптимистическая блокировка предполагает, что другие не будут обновлять эту же запись, и проверяет это во время обновления, а не блокирует запись на все время обработки на стороне клиента.
timestamptz не хранит часовой пояс
Если мы запустим данный запрос:
select typname, typlen
from pg_type
where typname in ('timestamp', 'timestamptz');
-- typname typlen
-- timestamp 8
-- timestamptz 8
мы поймем, что типы timestamp и timestamp with time zone имеют одинаковый размер, что означает, что PostgreSQL на самом деле не хранит временной пояс для timestamptz. Все, что он делает, - это форматирует одно и то же значение с использованием другого часового пояса:
select now()::timestamp -- "2023-08-31 16:56:54.541131" select now()::timestamp with time zone -- "2023-08-31 16:56:58.541131" set timezone = 'asia/tehran' select now()::timestamp -- "2023-08-31 16:56:54.541131" select now()::timestamp with time zone -- "2023-08-31 16:56:23.73028+04:30"
Схема «Звезда»
Схема «Звезда» - это подход к моделированию баз данных, используемый в реляционных хранилищах данных, при котором необходимо классифицировать таблицы модели как размерные или фактографические. Данная схема состоит из одной или нескольких таблиц фактов, ссылающихся на любое количество таблиц измерений.
В хранилищах данных таблица фактов состоит из измерений, метрик или фактов бизнес-процесса, а таблица измерений - это структура, которая классифицирует факты и измерения, чтобы дать пользователям возможность ответить на определенные бизнес-вопросы. Обычно используются такие измерения, как люди, продукты, место и время.
Возможность SARG (Sargability)
В реляционных базах данных условие (или предикат) в запросе считается SARG пригодным, если движок СУБД может воспользоваться индексом для ускорения выполнения запроса. Идеальное условие поиска в SQL имеет общий вид:
<column> <comparison operator> <literal>
Частым явлением, которое может сделать запрос не SARG пригодным, является использование индексированного столбца внутри функции, например, в данном запросе:
select birthday from users where get_year(birthday) = 2008 вместо эквивалентного: select birthday from users where birthday >= '01-01-2008' AND birthday < '01-01-2009'
Или еще один пример:
Не SARG (non – sargible): select * from players where SQRT(score) > 7.5 SARG (sargible): select * from players where score > 7.5 * 7.5
SARG - это сокращение от Search ARGument. На заре своего существования исследователи IBM называли подобные условия поиска "sargable predicates". Позднее компании Microsoft и Sybase переопределили термин "sargable" в значение "может быть найден через индекс".
BRIN-индекс
Предположим, что у нас есть такая таблица событий временного ряда:
create table event(t timestamp primary key, content text);
В этой ситуации первичный ключ или индекс таких таблиц постоянно увеличивается с течением времени, и строки постоянно добавляются в конец таблицы. Это может привести к фрагментации данных, что в свою очередь может привести к различным проблемам, например, к возникновению противоречий в конце таблицы. А это затрудняет распределение и разбиение таблицы на части, а также приводит к замедлению поиска данных из-за отсутствия статистики в конце таблицы.
Как уже говорилось ранее, система баз данных опирается на статистику для формирования более оптимальных планов выполнения. Важно отметить, что статистика по последним вставленным строкам не включается в статистику базы данных, например, pg_stat_database.
Одним из способов решения проблемы восходящего ключа в PostgreSQL является использование индекса диапазона блоков (BRIN-индекс). Эти индексы обеспечивают увеличение производительности, когда данные естественным образом упорядочиваются по мере их добавления в таблицу:
create table event ( event_time timestamp with time zone not null, event_data jsonb not null ); create index on event using BRIN (event_time);
Сетевые ошибки
При работе с базами данных могут возникать различные типы сетевых ошибок, например, отключение от базы данных во время транзакции. В таких случаях Вы обязаны проверить, было ли Ваше взаимодействие с базой данных успешным или нет.
Сетевые ошибки могут не давать четких указаний на то, где именно был прерван процесс или существует ли какая-либо проблема в сети. Если Вы используете потоковую репликацию, то соединение в таблице pg_stat_replication может быть признаком сетевых проблем или проблем другого рода.
utf8mb4
utf8mb4 расшифровывается как "UTF-8 Multibyte 4" и является типом MySQL. Он не имеет никакого отношения к PostgreSQL или стандарту SQL.
Исторически сложилось так, что MySQL использует utf8 в качестве псевдонима для utf8mb3. Это означает, что он может хранить только символы юникода Basic Multilingual Plane. Если Вы хотите иметь возможность хранить все символы юникода, то Вам необходимо использовать тип utf8mb4.
Начиная с версии MySQL 8.0.28, utf8mb3 используется исключительно в выводе операторов show и в таблицах Information Schema.
Ожидается, что utf8 станет ссылкой на utf8mb4. Чтобы избежать двусмысленности в отношении значения utf8, пользователям MySQL (и пользователям MariaDB) следует рассмотреть возможность указания utf8mb4 вместо utf8.
Далее: Уровень 5: Зона абиссали: ключи MATCH PARTIAL , null::jsonb IS NULL = false, ...
