Объяснение мема Postgres - Уровень 5: Зона абиссали
Добро пожаловать в зону абиссали! Здесь мы будем исследовать бездну концепций PostgreSQL. То, о чем Вы, возможно, даже и не слышали!
Модели затрат не отражают реальности
При написании запроса можно включить опцию "cost", которая позволяет получить расчетную стоимость выполнения запроса. Эта стоимость представляет собой оценку времени, которое потребуется для выполнения запроса.
Планировщик использует статистику таблицы для составления наилучшего плана с наименьшей стоимостью. Однако полученная стоимость может быть совершенно неверной, так как это всего лишь оценочное значение Кроме того, она может быть основана на неверной статистике.
null::jsonb IS NULL = false
NULL в SQL означает незнание значения, в то время как null в JSON является null в JavaScript и представляет собой намеренное отсутствие какого-либо значения. Вот почему значение null в типе данных jsonb в PostgreSQL не эквивалентно значению null в SQL:
select 'null'::jsonb is null;
-- false
select '{"name": null}'::jsonb->'name' is null;
-- false, because JSON's null != SQL's null
select '{"name": null}'::jsonb->'last_name' is null;
-- true, because 'last_name' key doesn't exists in JSON, and the result is an SQL nullTPC-C
TPC-C расшифровывается как "Transaction Processing Performance Council - Benchmark C" и представляет собой онлайновый эталон обработки транзакций, размещенный на сайте tpc.org. В TPC-C участвуют пять параллельных транзакций различных типов и сложности, выполняемых в режиме реального времени или поставленных в очередь на отложенное выполнение. База данных состоит из девяти типов таблиц с широким диапазоном размеров записей и совокупностей. TPC-C измеряется в транзакциях в минуту (tpmC).
Бенчмарки TPC-C включают два типа времени ожидания: время ввода представляет собой время, затрачиваемое на ввод данных (нажатие клавиш на клавиатуре), а время ожидания представляет собой время, затрачиваемое оператором на считывание результата транзакции на терминале перед запросом другой транзакции. Каждая транзакция имеет минимальное время ввода и минимальное время обдумывания.
Бенчмаркинг транзакций без времени ожидания приведет к тому, что тестируемая система будет работать все медленнее и медленнее, так как у внутренних устройств системы не будет свободных ресурсов для работы.
pgbench - это инструмент командной строки, используемый для бенчмаркинга баз данных PostgreSQL. Он поддерживает TPC и множество различных аргументов командной строки, включая wait-time/schedule-lag-time.
ОТСРОЧЕННЫЙ ПЕРВОНАЧАЛЬНО НЕМЕДЛЕННЫЙ
Ограничения на столбцы могут быть как отложенными, так и немедленными.
Немедленные ограничения проверяются в конце каждого оператора, в то время как отложенные ограничения не проверяются до фиксации транзакции. Каждое ограничение имеет свой собственный режим IMMEDIATE или DEFERRED.
При создании ограничение наделяется одной из трех характеристик:
-
not deferrable(по умолчанию, эквивалентно immediate): ограничение проверяется сразу после каждого оператора. Это поведение НЕ может быть изменено с помощью команды set constraint. например, set constraint pk_name deferred; -
deferrable initially immediate: ограничение проверяется сразу после каждого оператора, однако впоследствии оно может быть изменено с помощью команды set constraint. -
deferrable initially deferred: ограничения не проверяются до фиксации транзакции. В дальнейшем оно может быть изменено с помощью команды set constraint.
create table book ( name text primary key, author text references author(name) on delete cascade deferrable initially immediate; )
Как видно из приведенного SQL-кода, deferrable initially immediate указывается при определении схемы таблицы, а не во время выполнения.
EXPLAIN совместно с SELECT COUNT(*)
Использование explain с select count(*) может дать Вам оценку того, сколько строк, по мнению PostgreSQL, находится в Вашей таблице:
explain select count(*) from users;
Если Вам не нужен точный подсчет, то текущая статистика из таблицы каталога pg_class может быть достаточно информативной:
pg_class estimate:
select reltuples as estimate_count from pg_class where relname = 'table_name';
pg_class estimate с точной схемой: -- Tables named "table_name" can live in multiple schemas of a database, in which case you get multiple rows for this query. To overcome ambiguity: select reltuples::bigint as estimate_count from pg_class where oid = 'schema_name.table_name'::regclass;
MATCH PARTIAL
match full, match partial и match simple(default) - это три ограничения на столбцы таблицы для внешних ключей. Внешние ключи призваны гарантировать ссылочную целостность нашей базы данных, а для этого база данных должна знать, как сопоставить значение ссылающегося столбца со значением ссылающегося столбца в случае null.
·match full: не допускает, чтобы один столбец многостолбцового внешнего ключа был null, если только все столбцы внешнего ключа таковыми не являются; если все они null, то строка не обязана иметь соответствие в ссылающейся таблице.
·match simple(default): позволяет любому из столбцов внешнего ключа быть null; если любой из них null, то строка не обязана иметь соответствие в ссылающейся таблице.
·match partial: если все ссылающиеся столбцы равны null, то строка ссылающейся таблицы проходит проверку ограничений. Если хотя бы один из ссылающихся столбцов не является null, то строка проходит проверку ограничений тогда и только тогда, когда в ссылающейся таблице есть строка, совпадающая со всеми “ненулевыми” ссылающимися столбцами. В PostgreSQL это пока не реализовано, но для предотвращения возникновения подобных ситуаций можно использовать ограничения not null на ссылающихся столбцах.
Обратная причинность (causal reverse)
Causal reverse - это аномалия транзакций, которая может возникнуть даже при использовании уровня изоляции Serializable. Для устранения этой аномалии требуется Strict Serializability.
Приведем простой пример аномалии причинно-следственной обратной связи:
- Томас выполняет select * из событий, но ответа пока не получает.
- Ава выполняет insert в события (id, время, содержание) следующие значения (1, '2023-09-01 02:01:16.679037', 'hello') и фиксирует их.
- Эмма выполняет insert в события (id, время, содержания) следующие значения (2, '2023-09-01 02:02:56.819018', 'hi') и фиксирует их.
- Томас получает ответ на запрос select- он получает строку Эммы, но не строку Авы.
В обратной каузальной аномалии более поздняя запись, вызванная более ранней записью, перемещается в точку, предшествующую более ранней записи.
Далее: Уровень 6: Зона ультраабиссали: модель Volcano, упорядочивание join – NP-трудная задача...
