Мем айсберг SQL: погружение в глубины баз данных

Информация в статье взята с сайта https://www.avestura.dev/blog/explaining-the-postgres-meme и переведена на русский язык с добавлением авторских пометок

Мем айсберг SQL — это вирусное интернет-изображение, изображающее айсберг с несколькими слоями. Вершина айсберга содержит общеизвестные концепции и инструменты SQL, такие как операторы SELECT и JOIN. Однако по мере погружения под воду становятся видны более абсурдные и малоизвестные аспекты SQL.

Как создавался мем?

Мем айсберг SQL впервые появился в Твиттере в 2022 году. С тех пор он распространился по другим платформам социальных сетей и форумам, посвященным базам данных. Мем стал популярным среди разработчиков SQL как забавный способ обсудить широкий спектр возможностей SQL.

Какая польза от этого мема?

Мем айсберг SQL значим, поскольку он отражает обширную и часто пугающую сложность SQL. Он служит напоминанием о том, что даже самая простая технология может скрывать в себе слои скрытого функционала и неожиданных возможностей. А также помогает отследить нынешний уровень знаний и последовательно изучать следующие уровни данного языка программирования.

Мем также подчеркивает важность непрерывного обучения и исследования в области программирования. Он побуждает разработчиков SQL, студентов и любого заинтересованного в этом языке программирования человека выходить за пределы своей зоны комфорта и изучать более глубокие слои своих инструментов.

На какие уровни разбит мем?

• Уровень 0: Небесная зона

• Уровень 1: Поверхностная зона

• Уровень 2: Зона солнечного света

• Уровень 3: Сумеречная зона

• Уровень 4: Полуночная зона

• Уровень 5: Зона бездны

• Уровень 6: Зона Хадала

• Уровень 7: Зона кромешной тьмы

Уровень 0: Небесная зона

Data Types (Типы данных)

PostgreSQL поддерживает большое количество различных типов данных, варьирующихся от числовых, денежных, массивов, json и xml до таких вещей, как геометрические, сетевые адресные и составные типы. Вот длинный список поддерживаемых типов данных — PostgreSQL.

Этот запрос показывает типы, которые интересны разработчику приложений. Результат 87 различные типы данных в PostgreSQL версии 14.1:

select typname, typlen, nspname from pg_type t join pg_namespace n on t.typnamespace = n.oid where nspname = 'pg_catalog' and typname !~ '(^_|^pg_|^reg|_handlers$)' order by nspname, typname;

В качестве примера, если вы хотите сохранить журналы аудита действий, выполняемых пользователями-администраторами, и вам необходимо сохранить их IP-адреса, вы можете использовать inet тип в PostgreSQL вместо сохранения его как text. Это поможет вам более эффективно хранить эти данные и упростить их проверку по сравнению с системой, которая не поддерживает такой тип (например, Sqlite).

CREATE TABLE

SQL (Язык структурированных запросов) состоит из нескольких областей, и каждая из них имеет определенный подязык.

Один из этих подязыков называется DDL, что расшифровывается как язык определения данных. Он состоит из таких операторов, как CREATE, ALTER и DROP, которые используются для определения структур данных на диске.

Вот пример запроса create table:

create table "audit_log" ( id serial primary key, ip inet, action text, actor text, description text, created_at timestamp default NOW() ) 

Это создаст audit_log таблицу с такими столбцами, как id, ip, action и т.д.

SELECT, INSERT, UPDATE, DELETE

DML — это еще один из подязыков SQL, расшифровывающийся как язык манипулирования данными. Он охватывает операторы insert, update и delete, которые используются для передачи данных в систему баз данных.

select также помогает нам извлекать данные из базы данных. Вероятно, это один из самых простых запросов select в SQL:

select 0;

Вот несколько примеров таких DML-запросов:

insert into "audit_log" (ip, action, actor, description) values ( '127.0.0.1', 'delete user', 'admin', 'admin deleted the user x' )

Команда table table_name также может использоваться для выбора всей таблицы. Эта команда sql:

table users;

эквивалентно

select * from users;

ORDER BY

SQL не гарантирует никакого упорядочения результирующего набора любого запроса, если вы не укажете предложение order by.

select * from "audit_log" order by created_at desc; 

LIMIT and OFFSET

LIMIT и OFFSET позволяют извлекать только часть строк, сгенерированных остальной частью запроса. Приведенный ниже запрос возвращает журналы аудита с номерами от 100 до 109:

select * from "audit_log" offset 100 limit 109;

ВАЖНО: этот метод разбиения на страницы МОЖЕТ БЫТЬ МЕДЛЕННЫМ!

Во многих случаях использование смещения замедлит производительность вашего запроса, поскольку база данных должна подсчитывать все строки с начала, пока не достигнет запрашиваемой страницы. Для получения дополнительной информации прочитайте раздел Разбивка набора ключей на страницы.

GROUP BY

В group by предложении вводятся агрегаты (также известные как Map / Reduce) в PostgreSQL, которые позволяют нам распределять наши строки по разным группам, а затем сводить результирующий набор к одному значению.

Предполагая, что у нас есть Student определение таблицы со столбцами id, class_no и grade, мы можем найти среднюю оценку каждого класса, используя этот запрос:

select class_no, avg(grade) as class_avg from student group by class_no;

Обратите внимание, что Student таблица определена таким образом только в демонстрационных целях.

NULL

В PostgreSQL NULL означает неопределенное значение или просто незнание значения, а не отсутствие значения. Вот почему true = NULL, false = NULL и NULL = NULL все проверки приводят к NULL.

select true = NULL as a, false = NULL as b, NULL = NULL as c;  -- result -- a = NULL -- b = NULL -- c = NULL

Теперь, когда вы знаете значение NULL, вам следует быть более осторожными с его семантикой. Следующий Запрос не возвращает строк:

select x from generate_series(1, 100) as t(x) -- `generate_series(1, 100)` creates rows 1,2,3,...,99,100 where x not in (1, 2, 3, null)  -- total rows: 0

Indexes (Индексы)

При правильном использовании индексы в PostgreSQL позволяют вам получать доступ к вашим данным намного быстрее, поскольку они предотвращают необходимость последовательного сканирования при наличии индекса. Кроме того, определенные ограничения, такие как PRIMARY KEY и UNIQUE, возможны только при использовании резервного индекса.

Вот простой запрос для создания индекса в last_name столбце student таблицы с использованием GiST метода.

create index on student using gist(last_name);

Индекс не может изменить результат запроса. Он дублирует данные для оптимизации поиска, следовательно, каждый индекс увеличивает затраты на запись в ваших запросах DML. Таким образом, не является хорошей идеей проиндексировать все, даже если у вас бесконечное хранилище. Вам все равно придется оплачивать расходы на обслуживание индексов.

JOIN

Запросы могут обращаться к нескольким таблицам одновременно или к одной и той же таблице таким образом, что одновременно обрабатывается несколько строк таблицы. Запросы, которые обращаются к нескольким таблицам (или нескольким экземплярам одной и той же таблицы) одновременно, называются запросами объединения.

Мы также можем рассматривать объединения как способ создания новых отношений из пары существующих. Отношение в PostgreSQL — это набор данных, обладающих общим набором свойств.

Приведенный ниже простой запрос возвращает пользователя admin с именем его роли:

select u.username, u.email, r.role_name from "user" as u join "role" as r on u.role_id = r.role_id -- equivalent: using(role_id) where u.username = 'admin';

Существует несколько видов объединений, включая, но не ограничиваясь ими:

• Внутренние соединения: сохраняйте только те строки, которые удовлетворяют условию соединения для обеих сторон задействованных отношений (левой и правой).

• Left / Right / Full Outer Joins: Извлекает все записи из таблицы, даже те, у которых нет совпадающего значения ни в левой, ни в правой, ни в обеих сторонах отношений.

• Перекрестное соединение: декартово произведение отношений слева и справа, дающее все возможные комбинации из левых строк таблицы, соединенных с правыми строками таблицы.

Существуют также некоторые другие типы объединений, которые мы обсудим на более глубоких уровнях.

Foreign Keys

Ограничения внешнего ключа помогают вам поддерживать ссылочную целостность ваших данных. Предполагая, что у вас есть таблицы Author и Book, вы можете ссылаться на Автора из таблицы Book, и PostgreSQL убедится, что ссылающийся автор существует в таблице Author при вставке строки в таблицу Book:

create table author ( name text primary key );  create table book ( name text primary key, author text references author(name) )  insert into author values ('George Orwell');  insert into book values ('Animal Farm', 'George Orwell'); -- OK  insert into book values ('Anna Karenina', 'Leo Tolstoy'); -- NOT OK -- ERROR:  insert or update on table "book" violates foreign key constraint "book_author_fkey" -- DETAIL:  Key (author)=(Leo Tolstoy) is not present in table "author". 

В PostgreSQL обеспечивает наличие либо unique или primary key ограничения на целевой столбец в целевой таблице.

ORMs

Объектно-реляционное отображение (ORM, O / RM, также известное как O / R Mapping tool) — это метод отображения данных в реляционные базы данных и из них, а также объектно-ориентированный язык программирования. ORM помогают программисту взаимодействовать и изменять данные в базе данных, используя языковые конструкции, определенные в объектно-ориентированном языке программирования. Другими словами, ORM действует как мост между объектно-ориентированным миром и миром математических отношений.

// Java, Hibernate ORM @Entity @Table(name = "Person") public class Person {     @Id     @GeneratedValue(strategy = GenerationType.IDENTITY)     private Long id;      private String name;     private int age; }  Configuration configuration = new Configuration().configure("hibernate.cfg.xml"); SessionFactory sessionFactory = configuration.buildSessionFactory();  try (Session session = sessionFactory.openSession()) { List<Person> persons = session.createQuery("FROM Person", Person.class).list(); } catch (Exception e) { e.printStackTrace(); }

---

Уровень 1: Поверхностная зона

Транзакции

Транзакция превращает набор шагов / действий в единую операцию «все или ничего». Промежуточные шаги не видны другим одновременно выполняющимся транзакциям. Вообще говоря, транзакция представляет собой любое изменение в базе данных.

В PostgreSQL транзакция окружена командами BEGIN и COMMIT. PostgreSQL рассматривает каждую инструкцию SQL как выполняемую в рамках транзакции. Если вы не выполняете BEGIN команду, то каждый отдельный оператор имеет неявное значение BEGIN и (в случае успеха) COMMIT обернуто вокруг него. Приведенный ниже пример показывает перевод монеты от Player1 к Player2 в базе данных сервера видеоигр (пример упрощен):

BEGIN; update accounts set coins = coins - 1 where name = "Player1"; update accounts set coins = coins + 1 where name = "Player2"; COMMIT;

Здесь мы хотим удостовериться, что либо все обновления применяются к базе данных, или никто из их случиться. Мы не хотим, чтобы системный сбой уменьшал количество монет у Player1, но монеты не добавляются в инвентарь Player2. Объединение набора операций в транзакцию дает нам такую гарантию.

ACID

ACID — это аббревиатура от атомарности, согласованности, изоляции и долговечности. Это набор свойств транзакций базы данных, предназначенных для обеспечения достоверности данных, несмотря на ошибки, сбои питания и другие неполадки.

Транзакция базы данных должна быть ACID по определению:

• Атомарность: Транзакция должна либо быть завершена полностью, либо не иметь эффекта. Атомарность гарантирует, что каждая транзакция рассматривается как единое «целое».

• Согласованность: Гарантирует, что транзакция может только переводить базу данных из одного согласованного состояния в другое, и предотвращает повреждение базы данных незаконной транзакцией. В качестве примера, транзакция не должна допускать, чтобы NOT NULL столбец имел NULL значение после COMMIT.

• Изоляция: Транзакции часто выполняются одновременно (несколько операций чтения и записи одновременно). Как мы указывали в предыдущем разделе, промежуточные шаги не видны другим одновременно выполняемым транзакциям, что означает, что одновременно выполняемая транзакция не должна иметь другого результата по сравнению с тем, когда транзакции выполнялись последовательно.

• Надежность: Системе управления базами данных не разрешается пропускать ни одной зафиксированной транзакции после перезагрузки или любого другого сбоя. Все зафиксированные транзакции должны быть записаны в энергонезависимую память.

Планы запросов и EXPLAIN

Каждой системе баз данных нужен планировщик для создания плана запросов на основе ваших SQL-запросов. Хороший планировщик запросов имеет решающее значение для хорошей производительности. В PostgreSQL команда EXPLAIN используется, чтобы узнать, какой план запроса создан для входного запроса.

explain select "name", "author" from "book";  -- output: -- Seq Scan on book  (cost=0.00..18.80 rows=880 width=64) 

Для более сложного запроса, подобного этому

select w.temp_lo, w.temp_hi, w.city, c.location as city_location from weather as w join city as c on c.name = w.city;

Мы получаем больше информации о таких вещах, как то, как PostgreSQL пытается найти запись (используя последовательное сканирование или хэширование и т.д.), о затратах, сроках и производительности. Приведенная ниже информация получена с помощью EXPLAIN ANALYZE команды. Используя ANALYSE опцию рядом с EXPLAIN показывает точное количество строк и истинное время выполнения вместе с оценками, предоставленными командой. EXPLAIN:

[   {     "Plan": {       "Node Type": "Hash Join",       "Parallel Aware": false,       "Async Capable": false,       "Join Type": "Inner",       "Startup Cost": 18.10,       "Total Cost": 55.28,       "Plan Rows": 648,       "Plan Width": 202,       "Actual Startup Time": 0.024,       "Actual Total Time": 0.027,       "Actual Rows": 3,       "Actual Loops": 1,       "Inner Unique": false,       "Hash Cond": "((w.city)::text = (c.name)::text)",       "Plans": [         {           "Node Type": "Seq Scan",           "Parent Relationship": "Outer",           "Parallel Aware": false,           "Async Capable": false,           "Relation Name": "weather",           "Alias": "w",           "Startup Cost": 0.00,           "Total Cost": 13.60,           "Plan Rows": 360,           "Plan Width": 186,           "Actual Startup Time": 0.010,           "Actual Total Time": 0.010,           "Actual Rows": 3,           "Actual Loops": 1         },         {           "Node Type": "Hash",           "Parent Relationship": "Inner",           "Parallel Aware": false,           "Async Capable": false,           "Startup Cost": 13.60,           "Total Cost": 13.60,           "Plan Rows": 360,           "Plan Width": 194,           "Actual Startup Time": 0.008,           "Actual Total Time": 0.008,           "Actual Rows": 3,           "Actual Loops": 1,           "Hash Buckets": 1024,           "Original Hash Buckets": 1024,           "Hash Batches": 1,           "Original Hash Batches": 1,           "Peak Memory Usage": 9,           "Plans": [             {               "Node Type": "Seq Scan",               "Parent Relationship": "Outer",               "Parallel Aware": false,               "Async Capable": false,               "Relation Name": "city",               "Alias": "c",               "Startup Cost": 0.00,               "Total Cost": 13.60,               "Plan Rows": 360,               "Plan Width": 194,               "Actual Startup Time": 0.004,               "Actual Total Time": 0.005,               "Actual Rows": 3,               "Actual Loops": 1             }           ]         }       ]     },     "Triggers": [     ]   } ] 

Таблица объяснений:

Если у вас установлен pgAdmin, он также может отображать графический вывод:

Инвертированные индексы

Инвертированный индекс — это структура индекса, хранящая набор (key, posting list) пар, где список записей — это набор идентификаторов строк, в которых встречается ключ.

Инвертированные индексы используются, когда мы хотим проиндексировать составные значения (называемые item), где каждое значение элемента в item является ключом. В качестве примера, документ — это элемент, а слово, которое мы ищем внутри документа, является ключевым.

PostgreSQL поддерживает GIN, что расшифровывается как Generalized Inverted Index . GIN является обобщенным в том смысле, что коду метода доступа к GIN не нужно знать конкретные операции, которые он ускоряет.

Разбивка набора ключей на страницы

Существует множество способов, с помощью которых можно реализовать разбивку на страницы для чтения только части строк из базы данных. Как мы предложили в разделе OFFSET/LIMIT , во многих случаях использование смещения замедлит производительность вашего запроса, поскольку база данных должна подсчитывать все строки с начала, пока не достигнет запрашиваемой страницы. Один из способов преодолеть это — использовать разбивку набора ключей на страницы:

select * from "audit_log" where created_at < ? order by created_at desc limit 10; -- equivalent standard SQL: fetch first 10 rows only

Здесь вместо того, чтобы пропускать записи, мы просто используем, keyset_column > x где x последняя запись с предыдущей страницы, которую мы выбрали.

• Подробнее: Просмотр результатов Маркуса Винанда

Вычисляемые столбцы

Вычисляемый или сгенерированный столбец в таблице — это столбец, значение которого является функцией другого столбца в той же строке. Другими словами, вычисляемый столбец для столбцов — это то же, что представление для таблиц. Значение вычисляемого столбца можно прочитать, но его нельзя записать напрямую. Вычисляемый / сгенерированный столбец определяется с помощью GENERATED ALWAYS AS в PostgreSQL:

create table people ( ...,     height_cm numeric,     height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) -- this won't work, will explain why in the next section )

Сохраненные столбцы

Сгенерированный столбец может быть stored или virtual:

• Сохраненный: вычисляется при записи (вставке или обновлении) и занимает место в памяти, как если бы это был обычный столбец.

• Виртуальный: не занимает места в памяти и вычисляется при чтении

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

Если вы пытались выполнить предыдущий запрос, возможно, вы столкнулись с ошибкой. Это связано с тем, что на момент написания этого поста PostgreSQL реализует только сохраненные сгенерированные столбцы, поэтому вам нужно пометить столбец с помощью STORED:

create table people (     ...,     height_cm numeric,     height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED -- works fine );

ORDER BY Агрегаты

Агрегатная функция вычисляет один результат из набора входных значений. Некоторые из наиболее известных агрегатных функций — это min, max, sum, и avg которые используются для вычисления минимума, максимума, суммы и среднего значения набора результатов. Приведенный ниже запрос вычисляет среднее значение высоких и низких температур на основе данных о погоде в таблице weather:

select avg(temp_lo) as temp_lo_average, avg(temp_hi) as temp_hi_average from weather;

Входной некоторых агрегатных функций вводятся путем ORDER BY. Эти функции иногда называют “обратным распространением” функции. Как например, приведенный ниже запрос показывает средний ранг всех игроков сервера:

ПОРЯДОК По совокупнымопределениям таблиц

SELECT     "server",     percentile_cont(0.5) WITHIN GROUP (ORDER BY rank DESC) AS median_rank FROM "player" GROUP BY "server"  -- server    median_rank -- asia      2.5 -- europe    5

Оконные функции

Оконные функции — это очень мощные инструменты, которые позволяют обрабатывать несколько значений результирующего набора одновременно. Это может быть похоже на то, чего мы можем достичь с помощью агрегатных функций, однако оконные функции не заставляют строки группироваться в одну выходную строку, как это сделали бы неоконные агрегатные вызовы.

Вызов оконной функции всегда содержит OVER предложение, следующее непосредственно за именем и аргументами оконной функции:

select city, temp_lo, temp_hi, avg(temp_lo) over (partition by city) as temp_lo_average, rank() over (partition by city order by temp_hi) as temp_hi_rank from weather  -- city     temp_lo  temp_hi  temp_lo_average  temp_hi_rank -- Lahijan  10       20       10.33333333      1 -- Lahijan  11       25       10.33333333      1 -- Lahijan  10       20       10.33333333      3 -- Rasht    15       45       15               1 -- Rasht    20       35       15               2 -- Rasht    10       30       15               3

При использовании оконных функций необходимо понимать концепцию оконной рамки. Для каждой строки в ее разделе есть набор строк, называемый ее оконной рамкой. Некоторые оконные функции воздействуют только на строки оконной рамки, а не на весь раздел целиком.

В общем, это эмпирические правила для оконных рам:

• Если ORDER BY указано, то фрейм состоит из всех строк от начала раздела до текущей строки (плюс любые последующие строки, которые равны текущей строке в соответствии с ORDER BY предложением)

• Когда значение ORDER BY опущено, фрейм по умолчанию состоит из всех строк в разделе

select salary, sum(salary) over () from empsalary;  --  salary |  sum -- --------+------- --    5200 | 47100 --    5000 | 47100 --    3500 | 47100 --    4800 | 47100 --    3900 | 47100 --    4200 | 47100 --    4500 | 47100 --    4800 | 47100 --    6000 | 47100 --    5200 | 47100 -- (10 rows) 

Внешние соединения

Как мы упоминали ранее, внешнее объединение извлекает из таблицы все записи, даже те, у которых нет совпадающего значения ни в левой, ни в правой, ни в обеих сторонах отношений.

select * from weather left outer join cities ON weather.city = cities.name;  --      city      | temp_lo | temp_hi | prcp |    date    |     name      | location -- ---------------+---------+---------+------+------------+---------------+----------- --  Hayward       |      37 |      54 |      | 1994-11-29 |               | --  San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53) --  San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53) -- (3 rows)

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

В PostgreSQL, left outer join, right outer join, и full outer join используются для выполнения внешних соединений.

CTEs

WITH запросы, или обычные табличные выражения (CTE), можно рассматривать как определение временных таблиц, которые существуют только для одного запроса. Используя предложение WITH , мы можем определить вспомогательный оператор, который может быть присоединен к основному оператору.

В запросе ниже, сначала мы определяем две вспомогательные таблицы, называемые hottest_weather_of_city и not_so_hot_cities и тогда мы используем их в основной select запрос:

with hottest_weather_of_city as ( select city, max(temp_hi) as max_temp_hi from weather group by city ), not_so_hot_cities as (  select city from hottest_weather_of_city where max_temp_hi < 35 ) select * from weather where city in (select city from not_so_hot_cities)

Короче говоря, Общие табличные выражения — это просто другое название для WITH предложений.

Нормальные формы

Нормализация базы данных — это процесс структурирования реляционной базы данных в соответствии с серией так называемых нормальных форм для уменьшения избыточности данных и улучшения целостности данных.

Существует несколько уровней нормализации, и более высокий уровень нормализации базы данных не может быть достигнут, если не будут выполнены предыдущие уровни.

Вот некоторые из обычных форм:

• 1NF: Столбцы не могут содержать отношений или составных значений (каждая ячейка содержит отдельные значения), и в таблице нет повторяющихся строк

• 2NF: неключевые столбцы зависят от всего ключа (он не должен зависеть от части составного ключа). Другими словами, частичных зависимостей нет.

• 3NF: Таблица не имеет транзитивных зависимостей.

Другие формы нормалей, такие как EKNF, BCNF, 4NF, 5NF, DKNF и 6NF, не рассматриваются в этом сообщении в блоге. Подробнее о них вы можете прочитать на странице нормальных форм в Википедии.

---

Уровень 2: Зона солнечного света

Пулы подключений

Подключение к серверу базы данных состоит из нескольких трудоемких шагов (создание сокета, первоначальное подтверждение связи, анализ строки подключения, аутентификация и т.д.). Пулы подключений — это способ дальнейшего повышения производительности за счет объединения подключений пользователей к базе данных. Идея состоит в том, чтобы уменьшить общее количество подключений, которые необходимо открыть. Всякий раз, когда клиент хочет подключиться к базе данных, открытое соединение из пула используется повторно вместо создания нового.

Существует множество инструментов и библиотек для разных языков программирования, которые могут помочь вам создавать пулы подключений, а также программное обеспечение для создания пулов подключений на стороне сервера, которое работает для всех типов подключений, а не только в рамках одного программного стека. Вы можете создавать или отлаживать пулы подключений с помощью таких инструментов, как Amazon RDS Proxy, pgpool, pgbouncer, pg_crash и т.д.

The DUAL table

--- postgresql select 1 + 1;  -- oracle select 1 + 1 from dual;

Двойная таблица — это фиктивная таблица с одной строкой и одним столбцом, которая изначально была добавлена Чарльзом Вайсом в качестве базового объекта в системах баз данных Oracle. Эта таблица используется в ситуациях, когда вы хотите select что-то, но не требуется from предложение. В Oracle FROM предложение является обязательным, поэтому вам понадобится dual таблица. Однако в PostgreSQL создание такой таблицы не требуется, поскольку вы можете сделать это select без from предложения.

При этом, эта таблица может быть создана в postgres для облегчения проблем с переносом с Oracle на PostgreSQL. Это позволяет коду остаются несколько совместимых с Oracle SQL, не раздражает Парсер базы данных Postgres:

select * from weather as w join lateral ( select city.location  from city where city.name = w.city -- only possible to reference "w" because of lateral ) c on true;

LATERAL Joins (боковое соединение)

PostgreSQL добавила технику бокового соединения начиная с PostgreSQL 9.3. Используя боковые соединения, вы можете посмотреть на таблицу слева:

with recursive fib(a, b) AS (   values (0, 1)   union all     select b, a + b from fib where b < 1000 ) select a from fib;  -- 0 -- 1 -- 1 -- 2 -- 3 -- 5 -- ...

В приведенном выше запросе внутренний подзапрос стал коррелированным подзапросом с внешним select запросом. Без lateral каждый подзапрос оценивается независимо и, как следствие, не может содержать перекрестных ссылок ни на один из них. другой FROM элемент. Вы получили бы эту ошибку, если бы не использовалиLATERAL:

ERROR: invalid reference to FROM-clause entry for table "w" HINT: There is an entry for table "w", but it cannot be referenced from this part of the query.

Рекурсивные CTE

WITH предложения могут использоваться с необязательной RECURSIVE опцией. Этот модификатор превращается WITH из простого синтаксического удобства в функцию, которая выполняет функции, невозможные в стандартном SQL. Используя RECURSIVE, WITH запрос может ссылаться на свой собственный результат. Приведенный ниже запрос создает последовательность Фибоначчи:

sql

with recursive fib(a, b) AS (   values (0, 1)   union all     select b, a + b from fib where b < 1000 ) select a from fib;  -- 0 -- 1 -- 1 -- 2 -- 3 -- 5 -- ...

ORM создают неверные запросы

Как упоминалось в предыдущих разделах, ORM — это, по сути, абстракции, построенные поверх SQL для упрощения взаимодействия с вашей базой данных. Некоторые люди используют ORM для написания кода, используя языковые структуры, предоставляемые их языком программирования, вместо того, чтобы самим создавать SQL-запросы. ORM служит связующим звеном между реляционной базой данных и приложением, генерируя необходимые запросы.

С другой стороны, ORM абстрагируются от функций базы данных, отлаживать их может быть сложнее, чем исходные запросы, и иногда они генерируют неоптимальные запросы, которые значительно медленнее, чем хорошо написанный SQL для той же задачи. Одна из хорошо известных проблем — это проблема с запросом N + 1.

Предположим, мы хотим получить запись в блоге вместе с комментариями к ней. Распространенная ошибка, с которой мы часто сталкиваемся, — это проблема с N + 1 запросом: один select запрос для получения записи и n дополнительных запросов для выбора комментариев к каждой записи (всего n + 1 запрос). Это легко исправить в необработанном SQL с помощью простого объединения. Однако при использовании ORM у вас меньше контроля над генерируемыми запросами, и иногда бывает сложно определить, сталкивались ли вы с этой проблемой или нет, без использования профилировщика:

N + 1 Туда и обратноИсправлено с помощью Join

-- one query to fetch the posts select id, title from blog_post; -- N query in a loop to fetch the comments of each post: select body from comments where post_id = :post_id;

LATERAL Joins (хранимые процедуры)

Хранимые процедуры — это серверные процедуры с именами, которые обычно пишутся на разных языках, причем SQL является наиболее распространенным. Ниже показано определение процедуры в SQL:

create procedure insert_person(id integer, first_name text, last_name text, cash integer) language sql as $$ insert into person values (id, first_name, last_name); insert into cash values (id, cash); $$;

Хранимые процедуры вызываются с помощью инструкции CALL:

call insert_person(1, 'Maryam', 'Mirzakhani', 1000000);

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

Дополнительные языки могут быть легко интегрированы в сервер PostgreSQL с помощью команды CREATE LANGUAGE:

create language myLovelyLanguage   handler my_language_handler -- the function that glue postgresql with any external language   validator my_language_validator -- check syntax errors before executing function

Функции — это концепция, аналогичная хранимым процедурам, но это разные сущности. Традиционно люди использовали термин «Хранимая процедура» для обозначения обеих, но между ними есть различия. Одно из ключевых различий заключается в том, что функции могут возвращать значения, тогда как хранимые процедуры — нет. Однако это не единственное различие.

Одно из наиболее существенных различий между хранимыми процедурами и функциями заключается в том, что функции могут использоваться внутри SELECT инструкции, но они не могут запускать или фиксировать транзакции:

select my_func(last_name) from person;

С другой стороны, хранимые процедуры могут запускать и фиксировать транзакции, но их нельзя использовать внутри инструкций select .

call sp_can_commit_a_transaction();

Вкратце:

• Функции возвращают значения, а хранимые процедуры — нет.

• Функции можно использовать в select операторах, но хранимые процедуры этого не делают.

• Функции не могут запускать или фиксировать транзакции, но хранимые процедуры могут.

Существует также концепция под названием Trigger Functions, но мы поговорим о ней, когда углубимся в суть!

Курсоры

Идея, лежащая в основе CURSOR, заключается в том, что данные генерируются только при необходимости (через FETCH). Этот механизм позволяет нам использовать результирующий набор, пока база данных генерирует результаты. В отличие от этого, это позволяет избежать ожидания, пока компонент database Engine завершит свою работу и отправит все результаты сразу:

declare my_cursor scroll cursor for select * from films; -- you can read more about SCROLL in the collapsible box  fetch forward 5 from my_cursor; -- FORWARD is the direction, PostgreSQL supports many directions. -- Outputs five rows 1, 2, 3, 4, and 5 -- Cursor is now at position 5  fetch prior from my_cursor; -- outputs row number 4, PRIOR is also a direction.  close my_cursor;

Что такое курсор прокрутки? SCROLL указывает, что курсор может использоваться для извлечения строк непоследовательным образом (например, в обратном направлении). В зависимости от сложности плана выполнения запроса указание SCROLL может привести к снижению производительности.

Не существует ненулевых типов

Как упоминалось ранее, SQL null — это маркер, а не значение. SQL null означает «неизвестно», а отсутствие типов с возможностью обнуления означает, что мы можем контролировать обнуляемость столбцов исключительно с помощью not null контрольных ограничений.

Этот подход приводит к созданию гибких типов данных, при которых любой пользователь базы данных может вводить данные в систему, даже если данные для столбца с таким типом данных отсутствуют или неизвестны.

PostgreSQL разрешает создание пользовательских типов с помощью CREATE TYPE, и можно указать значение по умолчанию для типа данных на случай, если пользователь желает, чтобы столбцы этого типа данных по умолчанию принимали что-то другое, кроме null значения. Тем не менее, остается в силе установить значение столбца равным null, если не определены not null контрольные ограничения.

Оптимизаторы не работают без табличной статистики

Как упоминалось ранее, PostgreSQL стремится генерировать оптимальный план выполнения для ваших SQL-запросов. Различные планы могут давать один и тот же набор результатов, но хорошо продуманный планировщик / оптимизатор может создать более быстрый и эффективный план выполнения.

Оптимизация запросов — это целое научное искусство, и для того, чтобы разработать хороший план, PostgreSQL нужны данные. PostgreSQL использует оптимизатор, основанный на затратах, который использует статистику данных, а не статические правила. Планировщик / оптимизатор оценивает стоимость каждого шага плана и выбирает план с наименьшими затратами для системы. Кроме того, PostgreSQL переключается на генетический оптимизатор запросов, когда количество соединений превышает определенный порог (установленный geqo_threshold переменной). Это связано с тем, что среди всех реляционных операторов соединения часто являются наиболее сложными для обработки и оптимизации.

В результате PostgreSQL оснащен системой накопительной статистики, которая собирает и сообщает информацию, связанную с деятельностью сервера баз данных. Эта статистика может пригодиться оптимизатору. Подробнее о статистике, которую собирает PostgreSQL, вы можете прочитать на The Cumulative Statistics System.

Советы по планированию

Как мы уже знаем, PostgreSQL делает все возможное, чтобы обеспечить хорошее выполнение плана, используя статистику, оценки и предположения. Хотя этот подход в целом эффективен для оптимизации пользовательских запросов, бывают ситуации, когда пользователи могут захотеть предоставить ядру базы данных подсказки, позволяющие вручную влиять на определенные решения в планах выполнения.

Эти подсказки могут попасть к планировщику / оптимизатору, используя подходы, подобные pg_hint_plan project, и добавляя комментарии SQL перед запросами:

/*+ SeqScan(users) */ explain select * from users where id < 10;    --                      QUERY PLAN                         -- ------------------------------------------------------   -- Seq Scan on a  (cost=0.00..1483.00 rows=10 width=47)   

Комментарий /*+ SeqScan(users) */ предписывает планировщику использовать последовательное сканирование при поиске элементов в таблице «пользователи». Аналогичным образом, подсказки для объединений могут быть предоставлены с использованием HashJoin(weather city) синтаксиса в комментарии.

Сборка мусора MVCC

MVCC расшифровывается как Multiversion Concurrency Control. Каждый компонент database Engine должен каким-то образом управлять параллельным доступом к данным, и PostgreSQL как продвинутый компонент database Engine не является исключением. Как следует из названия, MVCC — это механизм управления параллелизмом внутри Postgres. Мультиверсия означает, что каждый оператор видит свою собственную версию базы данных (иначе snapshot), как это было иногда раньше. Это предотвращает просмотр операторами противоречивых данных.

MVCC позволяет блокировкам чтения и записи не конфликтовать друг с другом, поэтому чтение никогда не блокирует запись, а запись никогда не блокирует чтение (и PostgreSQL была первой базой данных, разработанной с этой функцией).

При хранении нескольких копий / версий данных образуются ненужные данные, которые занимают много места на диске, что в долгосрочной перспективе снижает производительность базы данных. Postgres использует VACUUM для сбора мусора в данных. VACUUM освобождает хранилище, занятое мертвыми кортежами, которые являются кортежами, удаленными или устаревшими в результате обновления, которые физически не удаляются из своей таблицы. Это необходимо делать VACUUM периодически, особенно для часто обновляемых таблиц, отсюда и то, почему PostgreSQL включает средство “autovacuum”, которое может автоматизировать рутинное обслуживание вакуума.

• Подробнее: Блоги Google Cloud: глубокое погружение в ВАКУУМ часто задаваемых вопросов

---

Уровень 3: Сумеречная зона

COUNT(*) против COUNT(1)

COUNT Функция — это агрегатная функция, которая может принимать форму либо count(*) — которая подсчитывает общее количество входных строк, — либо count(expression) — которая подсчитывает количество входных строк, в которых значение выражения отсутствует null.

-- Number of all rows, including nulls and duplicates. -- Performance Warning: PostgreSQL uses a sequential scan of the entire table, -- or the entirety of an index that includes all rows. select count(*) from person;   -- Number of all rows where `middle_name` is not null select count(middle_name) from person;  -- Number of all unique and not-null `middle_name`s  select count(distinct middle_name) from person; 

count(1) не имеет функциональной разницы с count(*), поскольку каждая строка считается как константа 1:

select count(1) from person; -- equivalent result: select count(*) from person;

Однако распространенный миф утверждает, что:

«использование count(1) лучше, чем count(*) потому что count(*) без необходимости выбирает все столбцы.»

Приведенное выше утверждение ↑ неверно. В PostgreSQL count(*) работает быстрее, потому что это специальный жестко запрограммированный синтаксис без аргументов для count агрегатной функции. count(1) выполняется медленнее, поскольку оно следует за count(expression) синтаксисом и требует проверки, не равна ли константа 1 null для каждой строки.

Уровни изоляции и фантомные чтения

Как мы упоминали ранее, буква I в ACID означает изоляцию. Транзакция должна быть изолирована от других параллельных транзакций, выполняемых в базе данных. В качестве примера, когда вы хотите создать резервную копию своей базы данных с помощью таких инструментов, как pg_dump, вы не хотите, чтобы на вашу резервную копию влияли другие операции записи в системе.

Стандарт SQL определяет 4 уровня изоляции транзакций. Этот уровень изоляции определяется в терминах явлений, и каждый из этих уровней либо запрещает эти явления, либо не гарантирует их отсутствия. Явления перечислены ниже:

• грязное чтение: транзакция считывает данные, записанные параллельной незафиксированной транзакцией.

• неповторяемое чтение: транзакция повторно считывает данные, которые она прочитала ранее, и обнаруживает, что данные были изменены другой транзакцией (которая была зафиксирована с момента первоначального чтения).

• фантомное чтение: транзакция повторно выполняет запрос, возвращающий набор строк, удовлетворяющих условию поиска, и обнаруживает, что набор строк, удовлетворяющих условию, изменился из-за другой недавно совершенной транзакции.

• Аномалия сериализации: результат успешной фиксации группы транзакций несовместим со всеми возможными порядками выполнения этих транзакций по одной за раз. Перекос записи — это простейшая форма аномалии сериализации.

Существуют четыре уровня изоляции в базах данных::

• Read uncommitted

• Read committed

• Repeatable read (aka Snapshot Isolation, or Anomaly Serializable)

• Serializable (aka Serializable Snapshot Isolation)

Важно отметить, что PostgreSQL не реализует read uncommitted уровень изоляции. Вместо этого режим чтения без фиксации в PostgreSQL ведет себя как чтение с фиксацией. Это потому, что это единственный разумный способ сопоставить стандартные уровни изоляции с архитектурой управления многоверсионным параллелизмом PostgreSQL. Этот подход соответствует стандарту SQL, потому что стандарт определяет минимальные гарантии, а не максимальные. Таким образом, PostgreSQL может запрещать и запрещает фантомные чтения даже на уровне изоляции с повторяемостью чтения:

Термин «Сериализуемое» выполнение означает, что транзакция может выполняться так, как если бы у нее было «последовательное выполнение», когда на нее не влияет никакая параллельная операция.

Сериализуемая изоляция PostgreSQL — это целое искусство науки!

Исследовательскому сообществу потребовалось около 20 лет, чтобы разработать удовлетворяющую математическую модель для эффективной реализации сериализуемой изоляции моментальных снимков, а затем один год, чтобы этот значительный прогресс был включен в! Подробнее:

• Сериализуемая изоляция моментальных снимков (SSI) в PostgreSQL по сравнению с простой изоляцией моментальных снимков (SI)

• Сериализуемая вики-страница PostgreSQL

Write skew (Искаженные записи)

Искажение записи — это простейшая форма аномалии сериализации, и сериализуемый уровень изоляции защищает вас от нее. Однако повторяемый уровень изоляции чтения не обеспечивает такой же защиты от искажения записи.

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

В таком случае каждая из двух параллельных транзакций определяет, что они записывают, на основе чтения набора данных (строк с черным / белым столбцом), и этот набор данных перекрывает то, что записывает другая. В этом случае мы можем получить состояние, которое не могло бы возникнуть, если бы одно из них было запущено раньше другого

Если эти обновления будут выполняться последовательно, все цвета будут совпадать: одна из транзакций сделает все строки белыми, а другая — черными. Если они выполняются одновременно в REPEATABLE READ режиме, значения будут переключены, что не согласуется ни с каким последовательным порядком запусков. Если они выполняются одновременно в SERIALIZABLE режиме, функция сериализуемой изоляции моментальных снимков (SSI) PostgreSQL заметит перекос записи и откатит одну из транзакций.

Подробнее:

• Сериализуемая изоляция моментальных снимков (SSI) в PostgreSQL по сравнению с простой изоляцией моментальных снимков (SI)

• Сериализуемая вики-страница PostgreSQL

Сериализуемые перезапуски требуют повторных циклов для всех операторов

Перезапуск сериализуемой транзакции требует повторной попытки для всех операторов транзакции, а не только для неудачной. И если сгенерированная транзакция на серверной части зависит от вычисленных значений за пределами кода sql, эти коды также необходимо выполнить повторно:

// This code snippet is for demonstration-purposes only let retryCount = 0;  while(retryCount <= 3) {   try {     const computedSpecies = computeSpecies("cat")     const catto = await db.transaction().execute(async (trx) => {       const armin = await trx.insertInto('person')         .values({           first_name: 'Armin'         })         .returning('id')         .executeTakeFirstOrThrow()            return await trx.insertInto('pet')         .values({           owner_id: armin.id,           name: 'Catto',           species: computedSpecies,           is_favorite: false,         })         .returningAll()         .executeTakeFirstOrThrow()     })      continue;      } catch {     retryCount++;     await delay(1000);   } } 

Partial Indexes (Частичные индексы)

Частичный индекс — это индекс, построенный над подмножеством таблицы, заданным с помощью where предложения, и он полезен, когда вы знаете, что значения столбцов уникальны только при определенных обстоятельствах. Один из основных вариантов использования частичных индексов — это когда вы не хотите помещать в индекс общие элементы, поскольку их частые изменения могут увеличить размер индекса и замедлить его работу из-за повторяющихся обновлений.

В качестве примера давайте предположим, что большинство ваших клиентов имеют одинаковую национальность (по крайней мере, 25% или около того), и в таблице всего несколько разных значений, было бы неплохо создать частичный индекс в столбце:

create index nationality_idx on person(nationality) where nationality not in ('american', 'iranian', 'indian');

Функции генератора zip при перекрестном соединении

Функции генератора, также известные как функции возврата множества (SRF), — это функции, которые могут возвращать более одной строки. В отличие от многих других баз данных, в которых в select предложениях могут отображаться только скалярные значения, PostgreSQL допускает появление функций, возвращающих множество, в select. Одна из самых известных функций генератора — это generate_series функции, которая принимает параметры start, stop и step (optional) и генерирует серию значений от начала до конца с определенным размером шага. Эта функция может генерировать различные типы серий, включая integer, bigint, numeric и даже timestamp:

select * from generate_series(2,4); -- or `select generate_series(2,4);`  --  generate_series -- ----------------- --                2 --                3 --                4 -- (3 rows)  select * from generate_series('2008-03-01 00:00'::timestamp,                               '2008-03-04 12:00', '10 hours'); --    generate_series -- --------------------- --  2008-03-01 00:00:00 --  2008-03-01 10:00:00 --  2008-03-01 20:00:00 --  2008-03-02 06:00:00 --  2008-03-02 16:00:00 --  2008-03-03 02:00:00 --  2008-03-03 12:00:00 --  2008-03-03 22:00:00 --  2008-03-04 08:00:00 -- (9 rows) 

В SQL вы можете перекрестно объединить две таблицы, используя любой из этих двух синтаксисов:

select * from table_a cross join table_b; -- with `cross join` -- or -- select * from table_a, table_b;           -- with comma 

Что касается функций, то вы можете вызывать их, используя любой из этих двух синтаксисов:

select * from generate_series(1, 3); -- with `select * from` -- or -- select generate_series(1, 3);        -- with `select` only 

Комбинируя эти два синтаксиса, когда мы выполняем один и тот же синтаксис перекрестного соединения для функций генератора, используя синтаксисыselect * from f() и select f(), один из них превращается в zip-операцию вместо перекрестного соединения:

select * from generate_series(1, 3) as a, generate_series(5, 7) as b; -- cross joins  -- ab -- 15 -- 16 -- 17 -- 25 -- 26 -- 27 -- 35 -- 36 -- 37  select generate_series(1, 3) as a, generate_series(5, 7) as b; -- zips  -- ab -- 15 -- 26 -- 37 

Во втором случае мы получаем результат из двух параллельных функций генератора (это называется zip двух результатов), вместо декартова произведения. Это потому, что join план не может быть создан без предложения from/merge (опущенное from предложение предназначено для вычисления результатов простых выражений), а PostgreSQL создает так называемый ProjectSetузел в плане проецирования(отображения) элементов, поступающих из функций генератора.

Sharding (Сегментирование)

Сегментирование в базе данных — это возможность горизонтального разделения данных еще на один сегмент базы данных. В то время как функция секционирования позволяет разбивать таблицу на несколько таблиц, сегментирование позволяет разбивать таблицу таким образом, чтобы ее части размещались на внешних серверах.

PostgreSQL использует подход Foreign Data Wrappers (FDW) для реализации сегментирования, но он все еще находится в стадии разработки.

Citus — это расширение с открытым исходным кодом для PostgreSQL, которое позволяет достичь горизонтальной масштабируемости за счет сегментирования и репликации. Одним из ключевых преимуществ Citus является то, что это не форк PostgreSQL, а расширение, которое позволяет ему оставаться синхронизированным с релизом сообщества. Напротив, многие другие форки PostgreSQL часто отстают от выпуска сообществом с точки зрения обновлений и функций.

ZigZag Join (Зигзагообразное присоединение)

Ранее мы обсуждали логические соединения, включая левое соединение, правое соединение, внутреннее соединение, перекрестное соединение и полное соединение. Эти соединения являются логическими в том смысле, что они представляют собой простые соединения, которые мы записываем в наших SQL-кодах. Существует еще одна категория соединений, называемая физическими соединениями. Физические соединения представляют собой фактические операции объединения, которые база данных выполняет для объединения ваших данных. К ним относятся соединение с вложенным циклом, хэш-соединение и объединение слиянием. Вы можете использовать explain функциональность, чтобы увидеть, какой тип физического соединения используется вашей базой данных в плане выполнения логического соединения, определенного вами в вашем SQL.

Зигзагообразное соединение — это стратегия физического соединения, и его можно рассматривать как более эффективное соединение с вложенным циклом. Предположим, у нас есть таблица, подобная этой:

create table vehicle(id integer primary key, tires integer, wings integer); create index on vehicle(tires); create index on vehicle(wings); 

И у нас есть select запрос, подобный этому:

select * from vehicle where tires = 0 and wings = 1;

Обычно это было бы сложной задачей для базы данных без зигзагообразного объединения, потому что при использовании одного из вторичных индексов и основного индекса в нашем плане объединения нам все равно нужно получить множество записывает, возникла ли у нас такая ситуация:

• есть много автомобилей с шинами = 0 или крыльями = 1

• но не так много автомобилей с обоими шинами = 0 и крыльями = 1.

Зигзагообразное соединение может использовать оба индекса, чтобы уменьшить количество извлекаемых записей:

В зигзагообразном соединении, показанном на изображении выше, мы постоянно переключаемся между вторичными индексами при сравнении с основным индексом:

1. Сначала мы посмотрим на индекс шин для значений tires = 0. Первое значение id равно 1. Следовательно, нам нужно перейти (зигзагом) к другому индексу и искать строки, где id = 1 (match) или id > 1 (skip). Итак, в общем, мы переходим к строке, где id >= 1.

2. Zig в индексе wings, и как id = 1, мы нашли совпадение. Мы можем безопасно искать следующую запись с тем же индексом.

3. Следующая запись содержит id = 2. Мы переходим к индексу шин, где id >= 2.

4. Текущая запись имеет id = 10. Это не совпадение, но мы уже пропустили много записей.

5. Мы снова переходим к индексу wings, ища записи, где id >= 10

6. И так далее…

MERGE (Слияние)

Слияние позволяет условно вставлять, обновлять или удалять строки таблицы с использованием источника данных:

merge into customer_account  as ca using recent_transactions as tx on tx.customer_id = ca.customer_id when matched then   update set balance = balance + transaction_value when not matched then   insert (customer_id, balance)   values (t.customer_id, t.transaction_value); 

Используя merge, мы упрощаем несколько инструкций процедурного языка в одну инструкцию слияния.

Триггеры

Триггеры — это механизм в PostgreSQL, который может выполнять функцию до, после или вместо операции, когда в таблице происходит определенное событие. Эти события могут быть любыми из insert, update, delete или truncate. Триггерные функции имеют доступ к специальным переменным, которые хранят данные как до, так и после редактирования, поэтому они более эффективны, чем check ограничения:

create trigger log_update     after update on system_actions     for each row     when (NEW.action_type = 'sensitive' or OLD.action_type = 'sensitive')     execute function log_sensitive_system_action_change(); 

Как вы можете видеть, предложение when может ссылаться на столбцы со старыми и / или новыми значениями строк написав OLD.column_name или NEW.column_name соответственно.

Grouping sets, Cube, Rollup (Группировка наборов, Куб, Свертка)

Представьте, что вы хотите увидеть сумму зарплат в разных отделах в зависимости от пола сотрудников. Один из способов сделать это — составить несколько group by предложений, а затем union объединить строки результатов вместе.:

  select dept_id, gender, SUM(salary) from employee group by dept_id, gender union all   select dept_id, NULL, SUM(salary)   from employee group by dept_id union all   select NULL, gender, SUM(salary)    from employee group by gender union all   select NULL, NULL, SUM(salary)      from employee;  -- dept_id  gender  sum -- -- 1        M       1000 -- 1        F       1500 -- 2        M       1700 -- 2        F       1650 -- 2        NULL    3350 -- 1        NULL    2500 -- NULL     M       2700 -- NULL     F       3150 -- NULL     NULL    5850 

Однако это было бы затруднительно, если мы хотим сообщить сумму заработной платы для разных групп данных. Наборы группировок позволяют нам определить набор группировок и написать более простой запрос. Эквивалентом приведенного выше запроса с использованием группирующих наборов было бы:

select dept_id, gender, SUM(salary) from employee  group by grouping sets ( (dept_id, gender), (dept_id), (gender), () ); 

Существует два типа наборов группировок, каждый со своим собственным синтаксическим сахаром из-за их общего использования: rollup и cube.

grouping sets (     ( e1, e2, e3, ... ),     ...     ( e1, e2 ),     ( e1 ),     ( ) )

эквивалентно:

GROUPING SETS (     ( a, b, c ),     ( a, b    ),     ( a,    c ),     ( a       ),     (    b, c ),     (    b    ),     (       c ),     (         ) )

и

cube ( a, b, ... ) 

эквивалентно:

GROUPING SETS (     ( a, b, c ),     ( a, b    ),     ( a,    c ),     ( a       ),     (    b, c ),     (    b    ),     (       c ),     (         ) )

Группирующие наборы также могут быть объединены вместе:

group by a, cube (b, c), grouping sets ((d), (e))  -- equivalent:  group by grouping sets (     (a, b, c, d), (a, b, c, e),     (a, b, d),    (a, b, e),     (a, c, d),    (a, c, e),     (a, d),       (a, e) )

---

Уровень 4: Полуночная зона

Денормализация

Одним из первых шагов разработки приложения базы данных является тщательный процесс нормализации (1NF, 2NF, …), чтобы уменьшить избыточность данных и улучшить целостность данных. Несмотря на то, что реляционные базы данных, особенно Postgres, хорошо оптимизированы для использования многих первичных и внешних ключей в таблицах и способны соединять многие таблицы и обрабатывать множество ограничений, с сильно нормализованной схемой все равно может быть сложно работать из-за снижения производительности.

В таком сценарии, если поддержание полностью нормализованной таблицы становится сложной задачей, разработчик базы данных может выполнить тщательный процесс, известный как денормализация. Другими словами, это повышает производительность чтения ваших данных за счет снижения производительности записи, поскольку вам может потребоваться записать несколько копий ваших данных.

PostgreSQL изначально поддерживает множество денормализованных типов данных, включая array, составные типы, созданные с помощью create type, enum, xml и json типы. Материализованные представления также используются для реализации компромисса между более быстрым чтением и более медленной записью. Материализованное представление — это представление, которое хранится на диске. Вы можете создать денормализованное и материализованное представление ваших данных для быстрого чтения, а затем использовать refresh materialized view my_view для обновления этого кэша.

NULLs в CHECK ограничения соответствуют действительности

Как мы упоминали ранее, null в SQL означает незнание значения, а не отсутствие значения, и такой выбор вернетnull:

select null > 7; -- null

Если мы создадим столбец с ограничением check для него следующим образом:

create table mature_person(id integer primary key, age integer check(age >= 18));

и затем мы попытаемся вставить строку, где age равно 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 мы должны указывать полную строку, потому что nulls в check ограничениях соответствуют действительности.

Конфликт транзакций

Конфликт ресурсов — это конфликт из-за доступа к общему ресурсу, такому как оперативная память, сетевой интерфейс, хранилище и т.д. В случае баз данных 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" 

• Подробнее: «Долгая, болезненная история времени» Эрика Наггама

• Смотрите: Проблема со временем и часовыми поясами — Computerphile

Star Schemas (Звездообразные схемы)

Звездообразная схема — это подход к моделированию базы данных, принятый в реляционных хранилищах данных. Он требует, чтобы разработчики моделей классифицировали свои таблицы моделей либо как измерение, либо как факт. Схема star состоит из одной или нескольких таблиц фактов, ссылающихся на любое количество таблиц измерений.

В хранилищах данных таблица фактов состоит из измерений, метрик или фактов бизнес-процесса, в то время как таблица измерений представляет собой структуру, которая классифицирует факты и меры, чтобы пользователи могли отвечать на бизнес-вопросы. Обычно используемые измерения — это люди, продукты, место и время.

SARGability

SARGability — это способ сказать, что поисковые предикаты могут использоваться для поиска по ключам индекса. В реляционных базах данных условие (или предикат) в запросе считается управляемым, если механизм СУБД может воспользоваться индексом для ускорения выполнения запроса. Идеальное условие поиска в SQL имеет общий вид:

<column> <comparison operator> <literal> 

Обычная вещь, которая может сделать запрос некорректным, — это использование индексированного столбца внутри функции , например, с помощью этого запроса:

select birthday from users where get_year(birthday) = 2008

вместо эквивалентного саркастичного:

select birthday from users where birthday >= '01-01-2008' AND birthday < '01-01-2009'

или

Не поддается сарказму

select * from   players where  SQRT(score) > 7.5

Поддается сарказму:

select * from   players where  score > 7.5 * 7.5

SARG — сокращение от Search ARGument . В первые дни исследователи IBM называли такого рода условия поиска «sargable predicates». Позже Microsoft и Sybase переопределили «sargable» на значение «можно найти через индекс».

Проблема с восходящим ключом

Предположим, что у нас есть такая таблица событий временных рядов:

create table event(t timestamp primary key, content text);

В этой ситуации первичный ключ или индекс таких таблиц постоянно увеличивается с течением времени, и строки всегда добавляются в конец таблицы. Это может привести к фрагментации данных, поскольку единственным местом записи является конец таблицы а место вставки распределено по строкам неравномерно.

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

Как упоминалось ранее, система баз данных полагается на статистику для создания более оптимальных планов выполнения. Важно отметить, что статистика для самых последних вставленных строк не включается в статистику базы данных, например, pg_stat_database.

Один из способов исправить проблему с восходящим ключом в PostgreSQL — использовать индекс диапазона блоков (BRIN index). Эти индексы повышают производительность, когда данные естественным образом упорядочиваются при добавлении в таблицу, например, в виде t timestamp столбцов или столбцов с естественным автоматическим увеличением.

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. Это означает, что он может хранить только базовые символы юникода в многоязычной плоскости (3 байта символов юникода). Если вы хотите иметь возможность хранить все символы юникода, вам нужно явно использовать utf8mb4 тип.

Начиная с MySQL 8.0.28, utf8mb3 используется исключительно в выходных данных show инструкций и в таблицах информационной схемы, когда имеется в виду этот набор символов.

Ожидается, что в какой-то момент в истории MySQL utf8 появится ссылка на utf8mb4. Чтобы избежать двусмысленности относительно значения utf8, пользователям MySQL (и пользователям MariaDB, поскольку MariaDB является форком MySQL) следует рассмотреть возможность указания utf8mb4 явно для ссылок на наборы символов вместо utf8.

---

Уровень 5: Зона бездны

Модели затрат не отражают реальность

При объяснении запроса вы можете включить опцию «стоимость», которая предоставляет вам приблизительную стоимость выполнения инструкции. Эта стоимость представляет собой оценку планировщиком того, сколько времени потребуется для выполнения инструкции, обычно измеряемую в единицах затрат, традиционно указывающих на выборку страницы диска.

Планировщик использует статистику таблицы, чтобы разработать наилучший план, какой только может, с наименьшими затратами. Однако эта вычисленная стоимость может быть совершенно неверной, поскольку это всего лишь приблизительное значение, или может быть основана на неверной статистике (как мы видели в задаче с возрастающим ключом).

'null'::jsonb IS NULL = false

NULL в SQL означает незнание значения, в то время как JSON null является JavaScript null и представляет собой намеренное отсутствие какого-либо значения. Вот почему тип данных JSON null в jsonb PostgreSQL не эквивалентен типу данных SQL null:

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 null

Для TPCC требуется время ожидания

TPC-C расшифровывается как «Совет по производительности обработки транзакций — бенчмарк C» и представляет собой онлайн-бенчмарк обработки транзакций, размещенный на tpc.org. TPC-C включает в себя сочетание пяти одновременных транзакций разного типа и сложности, либо выполняемых онлайн, либо помещаемых в очередь для отложенного выполнения. База данных состоит из девяти типов таблиц с широким диапазоном записей и размеров совокупности. TPC-C измеряется в транзакциях в минуту (tpmC).

Тесты TPC-C включают в себя два типа времени ожидания: время ввода представляет собой время, затраченное на ввод данных в терминале (нажатие клавиш на клавиатуре), а время обдумывания представляет собой время, затраченное оператором на чтение результата транзакции в терминале, прежде чем запросить другую транзакцию. Каждая транзакция требует минимального времени ввода и обдумывания.

Это время поможет приблизить бенчмарк к реальным сценариям. Бенчмаркинг транзакций без времени ожидания приведет к замедлению работы тестируемой системы, поскольку у внутренних компонентов системы не будет свободных ресурсов для работы.

pgbench это инструмент командной строки, используемый для тестирования баз данных PostgreSQL, и он поддерживает TPC и множество различных аргументов командной строки, включая время ожидания / время задержки по расписанию.

DEFERRABLE INITIALLY IMMEDIATE (откладываемое, изначально немедленное)

При создании ограничению присваивается одна из трех характеристик:

create table book ( name text primary key, author text references author(name) on delete cascade deferrable initially immediate; ) 

Как вы видите в коде SQL выше, deferrable initially immediate оговаривается при определения схемы таблицы, а не во время выполнения.

ОБЪЯСНЕНИЕ приближенно SELECT COUNT(*)

Использование explain with select count(*) может дать вам оценку того, сколько строк, по мнению PostgreSQL, находится в вашей таблице, используя статистику таблицы.

explain select count(*) from users;

Если вам не нужен точный подсчет, текущая статистика из таблицы каталога pg_class может быть достаточно хорошей, и ее гораздо быстрее получить для больших таблиц:

select reltuples as estimate_count from pg_class where relname = 'table_name';

MATCH PARTIAL Foreign Keys (Сопоставление частичных внешних ключей)

match full, match partial и match simple(по умолчанию) — это три ограничения на столбцы таблицы для внешних ключей. Предполагается, что внешние ключи гарантируют ссылочную целостность нашей базы данных, и для этого база данных должна знать, как сопоставить значение ссылочного столбца со значением ссылочного столбца в случае nulls.

• match full: не допускает, чтобы один столбец многоколоночного внешнего ключа был null если только все столбцы внешнего ключа не являются null; если они все null, строка не обязана совпадать в таблице, на которую дана ссылка.

• match simple(по умолчанию): позволяет использовать любой из столбцов внешнего ключа null; если какой-либо из них является null, соответствие строки в таблице, на которую дана ссылка, не требуется.

• match partial: если все ссылающиеся столбцы равны null, то строка ссылающейся таблицы проходит проверку ограничений. Если хотя бы один ссылочный столбец не равен null, то строка проходит проверку ограничения тогда и только тогда, когда в таблице, на которую дана ссылка, есть строка, которая соответствует всем ненулевым ссылочным столбцам. Это еще не реализовано в PostgreSQL, но обходной путь заключается в использовании not null ограничений для ссылочных столбцов, чтобы предотвратить возникновение подобных случаев.

Causal Reverse (Обратная причинно-следственная связь)

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

Вот простой пример причинно-следственной обратной аномалии:

1. Томас выполняет select * from events, ответа пока не получает.

2. Ava выполняет insert into events (id, time, content) values (1, '2023-09-01 02:01:16.679037', 'hello') и фиксирует.

3. Emma выполняет insert into events (id, time, content) values (2, '2023-09-01 02:02:56.819018', 'hi') и фиксирует.

4. Томас получает ответ на select запрос с шага 1. Он получает строку Эммы, но не Аву.

В каузальной обратной аномалии более поздняя запись, вызванная более ранней записью, перемещается во времени в точку в последовательном порядке, предшествующем более ранней записи.

• Подробнее: Аномалии корректности при сериализуемой изоляции

Уровень 6: Зона Хадала

Векторизованный не означает SIMD

SIMD расшифровывается как «Одна инструкция, несколько данных» и представляет собой тип параллельной обработки, когда одна команда процессора одновременно применяется к нескольким различным потокам данных.

В компьютерной литературе термины «Векторный» и «Векторизованный» обычно сопровождаются термином «SIMD». Векторное программирование (также известное как программирование массивов) относится к решениям, которые позволяют нам применять операции ко всему набору значений одновременно. На самом деле, набор команд расширения, который был добавлен в архитектуру набора команд x86 для perofrm SIMD, называется Advanced Vector Extensions (AVX).

SIMD — это один из подходов к использованию векторных вычислений, а не единственный. При этом, векторы PostgreSQL поддерживаются инструкциями процессора SIMD.

NULL эквивалентно DISTINCT, но не эквивалентно UNIQUE

Предположим, у вас есть таблица с именем unique_items с таким определением:

create table unique_items(item text unique);

PostgreSQL не позволит вам вставлять повторяющиеся 'hi' значения, поскольку второе значение нарушило бы ограничение уникальности:

insert into unique_items values ('hi'); -- INSERT 0 1 -- Query returned successfully in 89 msec.  insert into unique_items values ('hi'); -- ERROR:   duplicate key value violates unique constraint "unique_items_item_key" -- DETAIL:  Key (item)=(hi) already exists. 

Однако мы можем вставить столько null, сколько захотим:

insert into unique_items values (null); -- INSERT 0 1; Query returned successfully insert into unique_items values (null); -- INSERT 0 1; Query returned successfully insert into unique_items values (null); -- INSERT 0 1; Query returned successfully  table unique_items; -- item -- -- "hi" -- `null` -- `null` -- `null`

Это означает, что для SQL null значения не совпадают, поскольку они неизвестны.

Но теперь, если мы используем select distinct элементы unique_items таблицы, мы получим этот результат:

select distinct item from unique_items;  -- item --  -- `null` -- "hi"

Все null значения показаны как единый элемент, как если бы PostgreSQL сгруппировал все неизвестные значения в одно значение.

Модель Вулкана

«Volcano — расширяемая и параллельная система оценки запросов» — исследовательская работа Гетца Грефе, опубликованная в журнале IEEE Transactions on Knowledge and Data Engineering (том 6, выпуск 1) в феврале 1994 года. Эта система оценки называется Volcano Model, модель итератора Volcano, или иногда ее называют просто моделью итератора.

Каждый реляционно-алгебраический оператор создает поток кортежей, и потребитель может выполнять итерации по его входным потокам. Интерфейс потока кортежей, по сути, представляет собой: open, next и close; все операторы предлагают один и тот же интерфейс, и реализация непрозрачна.

Каждый next вызов создает новый кортеж из потока, если таковой доступен. Чтобы получить выходные данные запроса, используйте один «next-next-next» s в конечном операторе RA; этот оператор, в свою очередь, будет использовать «next» s на своих входных данных для извлечения кортежей, что позволит ему создавать выходные кортежи и т.д. Некоторые «next»-ы займут чрезвычайно много времени, поскольку потребуется много «next»-ов для предыдущих операторов, прежде чем они выдадут какой-либо вывод.

Пример: select max(v) from t возможно, потребуется просмотреть все t, чтобы найти этот максимум.

Сильно упрощенный псевдокод итерационной модели volcano:

define volcano_iterator_evaluate(root):   q = root // operator `q` is the root of the query plan   open(q)   t = next(q)   while t != null:     emit(t) // ship current row to application     t = next(q)    close(q)

• Подробнее: Volcano -расширяемая и параллельная система оценки запросов

Join ordering is NP Hard

Когда у вас есть несколько соединений в вашем SQL-запросе, компонент database Engine должен найти порядок выполнения соединений. Поиск наилучшего порядка соединения — NP-сложная задача. Вот почему движки баз данных используют оценки, статистику и подходы мягких вычислений для поиска порядка, поскольку поиск оптимального решения занял бы вечность.

Это упрощенная таблица классов задач:

NP-сложные задачи, по крайней мере, такие же сложные, как и самые сложные задачи в NP. Это означает, что если P ≠ NP (что, вероятно, имеет место, по крайней мере, на данный момент), NP-трудные задачи не могут быть решены за полиномиальное время.

Если бы P = NP, то мир был бы совершенно иным местом, чем мы обычно предполагаем. В “творческих скачках” не было бы особой ценности, не было бы фундаментального разрыва между решением проблемы и признанием решения, когда оно найдено. Каждый, кто мог оценить симфонию, был бы Моцартом; каждый, кто мог бы следовать пошаговой аргументации, был бы Гауссом; каждый, кто мог распознать хорошую инвестиционную стратегию, был бы Уорреном Баффетом.

Scott Aaronson

Database Cracking (Взлом базы данных)

Взлом — это метод, который переносит затраты на обслуживание индекса с обновлений на обработку запросов. Оптимизаторы конвейера запросов используются для изменения планов запросов на взлом и распространения этой информации. Этот метод позволяет увеличить время доступа и самоорганизовать поведение.

Другими словами, взлом базы данных — это самоорганизующийся подход к индексации данных и сопровождению индекса. В системе баз данных, где используется взлом базы данных, входящий запрос, запрашивающий все элементы, удовлетворяющие определенному условию, c не только возвращает результат, но и вызывает изменение конфигурации физической базы данных, так что все элементы, удовлетворяющие c, хранятся в непрерывном пространстве памяти. Таким образом, физическая база данных разделена на несколько частей (взломана).

С помощью этого механизма база данных реорганизует себя наиболее благоприятным образом в соответствии с нагрузкой, которая на нее возложена.

• Подробнее: Взлом базы данных Дэвидом Вернером

WCOJ

Традиционные алгоритмы бинарного соединения, такие как хэш-соединение, работают с двумя отношениями одновременно (r1 join r2); соединения между более чем двумя отношениями реализуются путем многократного применения бинарных соединений (r1 join (r2 join r3)).

WCOJ (наихудшее оптимальное объединение) — это разновидность алгоритма объединения, время выполнения которого является наихудшим оптимальным для всех запросов естественного объединения и в наихудшем случае асимптотически быстрее, чем любой алгоритм объединения, основанный на таких повторяющихся двоичных соединениях.

• Подробнее: Алгоритмы оптимального объединения в наихудшем случае

Learned Indexes (Изученные индексы)

Изученные индексы — это стратегии индексации, которые используют подходы искусственного интеллекта и модели глубокого обучения, чтобы превзойти оптимизированные для кэша B-деревья и сократить использование памяти. Инженеры Google и MIT разработали такую модель и опубликовали свою работу в качестве первого документа под названием «Аргументы в пользу изученных структур индексов». Ключевая идея заключается в том, что модель может изучать порядок сортировки или структуру ключей поиска и использовать этот сигнал для эффективного прогнозирования местоположения или существования записей.

• Подробнее: Пример изученных структур индексов

TXID Exhaustion (TXID Истощение)

Исчерпание идентификаторов транзакций, часто называемое «Всеобъемлющей проблемой», возникает из-за ограниченного количества доступных идентификаторов транзакций и отсутствия регулярного обслуживания базы данных, известного как очистка.

Семантика транзакции MVCC в PostgreSQL зависит от возможности сравнения номеров идентификаторов транзакций (XID): версия строки с XID вставки, превышающим XID текущей транзакции, находится “в будущем” и не должна быть видна текущей транзакции. Но поскольку идентификаторы транзакций имеют ограниченный размер (32 бита), кластер, который работает в течение длительного времени (более 4 миллиардов транзакций), будет иметь повторяющийся идентификатор транзакции: счетчик XID оборачивается к нулю, и внезапно транзакции, которые были в прошлом, оказываются в будущем, что означает, что их выходные данные становятся невидимыми. Короче говоря, катастрофическая потеря данных. (На самом деле данные все еще там, но это слабое утешение, если вы не можете до них добраться.)

Чтобы избежать этого, необходимо очищать каждую таблицу в каждой базе данных по крайней мере раз в два миллиарда транзакций.

• Подробнее: Рутинная очистка: предотвращение сбоев, связанных с идентификатором транзакции

---

Уровень 7: Зона кромешной тьмы

Проблема Хэллоуина

Проблема Хэллоуина — это ошибка базы данных, о которой разработчик системы баз данных должен знать.

В день Хэллоуина 1976 года пара компьютерных инженеров работала над запросом, который должен был повысить зарплату на 10% каждому сотруднику, заработавшему менее 25 000 долларов:

Повышение на 10% при зарплате < 25000:

update employee set salary = salary + (salary / 10) where salary < 25000

Определение таблицы:

create table employee(id integer primary key, salary integer); insert into employee values (1, 17000), (2, 27000)

Этот запрос будет успешно выполнен в их базе данных, но по завершении все сотрудники в базе данных заработали не менее 25 000 долларов. Это связано с тем, что обновленные строки также были видны механизму выполнения запросов, и поскольку критерии соответствия в предложении where по-прежнему были верны, база данных продолжала повышать им зарплату, пока она не превысила 25 000 долларов.

В некоторых случаях это может даже привести к бесконечному циклу, когда обновления постоянно помещают обновленную запись перед сканированием, выполняющим операцию обновления.

ВНИМАНИЕ

В PostgreSQL этой проблемы нет. Проблема Хэллоуина — это ошибка в проектировании базы данных, и любая база данных с такой проблемой ненадежна.

Dee и Dam

• Таблица dee — это таблица, в которой нет столбцов, кроме одной строки. Она играет роль True.

• Таблица dum — это таблица, в которой нет столбцов и строк. Она играет роль False.

Эти теоретические таблицы и терминология были созданы Хью Дарвеном. Вы можете прочитать больше о внедрении этих таблиц в PostgreSQL по адресу Создание таблиц Dum и Dee в PostgreSQL от Лукаса Эдера. Триггерные функции PostgreSQL используются для обеспечения соблюдения этих правил.

SERIAL не является транзакционным

Последовательные типы в PostgreSQL используются для создания автоматически добавляемых столбцов. Эти типы данных (smallserial, serial, и bigserial) не являются истинными типами, а просто синтаксическим сахаром для создания столбцов с уникальными идентификаторами:

Последовательный запрос:

create table tablename (     colname serial );

Эквивалентный запрос с последовательностью:

create sequence tablename_colname_seq as integer; create table tablename (     colname integer not null default nextval('tablename_colname_seq') ); alter sequence tablename_colname_seq owned by tablename.colname;

Поскольку serial типы реализованы с использованием последовательностей, могут быть «дыры» или пробелы в последовательности значений, которая отображается в столбце, даже если ни одна строка никогда не удалялась.

Значение, выделенное из последовательности, все еще «израсходовано», даже если строка, содержащая это значение, так и не была успешно вставлена в столбец таблицы. Это может произойти, например, при откате транзакции вставки. Вот почему serial типы считаются нетранзакционными, поскольку они не будут откатывать свое значение в случае отката транзакции.

create table counter(c serial primary key); -- CREATE TABLE; Query returned successfully.  insert into counter values (default); -- INSERT 0 1; Query returned successfully. <- uses id 1  insert into counter values (default); -- INSERT 0 1; Query returned successfully. <- uses id 2  begin; insert into counter values (default); abort; -- ROLLBACK; Query returned successfully. <- uses id 3, rollback doesn't give it back  insert into counter values (default); -- INSERT 0 1; Query returned successfully. <- uses id 4  table counter; -- c -- -- 1 -- 2 -- 4 <- the number 3 is missing

Последовательности — единственные объекты SQL с нетранзакционным поведением.

allballs

'allballs'Строка превратится в полночное время (00:00:00) при преобразовании в time. Это потому, что «allballs» на сленге означает «все нули». Этот сленг исторически использовался в военной коммуникации.

select 'allballs'::time;  -- time -- -- 00:00:00

• Подробнее: Список рассылки PostgreSQL: почему ‘allballs’ принимается как буквальное обозначение времени?

fsyncgate

fsync это системный вызов операционной системы, и в Linux он используется для синхронизации состояния файла в ядре с устройством хранения. Другими словами, этот системный вызов гарантирует, что данные, записанные в файл, действительно записаны на запоминающее устройство и сохраняются путем переноса / удаления всех измененных внутренних данных файла на диск или другое постоянное запоминающее устройство.

Термин «fsyncgate 2018» относится к скандалам и противоречиям вокруг вопросов надежности fsync системный вызов в списке рассылки PostgreSQL и в других местах (или, как говорят некоторые люди, как «PostgreSQL неправильно использовала fsync в течение 20 лет»).

Вопрос был поднят Крейгом Рингером. Цитата из списка рассылки:

Всем привет

Некоторое время назад я столкнулся с проблемой, когда пользователь столкнулся с повреждением данных после ошибки хранилища. PostgreSQL сыграл свою роль в этом повреждении, разрешив контрольную точку, которая должна была быть фатальной ошибкой.

TL; DR: Pg должен ЗАПАНИКОВАТЬ при возврате EIO fsync (). Повторная попытка fsync () недопустима, по крайней мере, в Linux. Когда fsync() возвращает успех, это означает «все записи с момента последней fsync попали на диск», но мы предполагаем, что это означает «все записи с момента последней успешной fsync попали на диск».

…

• Подробнее: Список рассылки PostgreSQL: обработка PostgreSQL ошибок fsync () небезопасна и чревата потерей данных, по крайней мере, в XFS

Каждый оператор SQL на самом деле является join

Каждый оператор SQL может быть представлен с помощью соединения. Один из способов думать о соединениях заключается в том, что они «ищут информацию» в отношении.

select age, age * age as age_squared from person;

Например, в приведенном выше запросе вместо вычисления age * age явно, мы могли бы также просто посмотреть это в в squares таблице функций (со столбцами x и xx):

select age, xx as age_squared from person join squares on age = x;

• Подробнее: ПРИСОЕДИНЯЙСЯ: окончательная проекция

NULL

NULL иногда бывает непросто. Ты так не думаешь?

---

Обсуждения

• HackerNews :: Объяснение айсберга Postgres

• Омары :: Объяснение мема Postgres

• Reddit :: r/ программирование, объясняющее мем Postgres

• Youtube :: Масштабирование Postgres / Postgres Meme

• YouTube :: Тео / Реакция на мем Postgres VOD

Ссылки

Ресурсы, которые автор статьи из ссылки использовал в блоге:

• PostgreSQL Documentation. [Online]. postgresql.org/docs

• Fontaine, D. (2019b). The Art of PostgreSQL: Turn Thousands of Lines of Code Into Simple Queries.

• Schönig, H. (2023). Mastering PostgreSQL 15: Advanced techniques to build and manage scalable, reliable, and fault-tolerant database applications. Packt Publishing Ltd.

• Dombrovskaya, H., Novikov, B., & Bailliekova, A. (2021). PostgreSQL query Optimization: The Ultimate Guide to Building Efficient Queries. Apress.

• Riggs, S., & Ciolli, G. (2022). PostgreSQL 14 Administration Cookbook: Over 175 Proven Recipes for Database Administrators to Manage Enterprise Databases Effectively.

• Use the Index, Luke! A Guide to Database Performance for Developers. [Online]. use-the-index-luke.com

• Gulutzan, P., & Pelzer, T. (2003). SQL Performance Tuning. Addison-Wesley Professional.

• Cockroach Labs Blog. [Online]. Cockroach Labs Blog

• Justin Jaffray’s Blog. [Online]. Justin Jaffray’s Blog

---

Какой итог можно сделать?

Мем айсберг SQL — это остроумный и запоминающийся способ визуализировать и обсудить различные уровни сложности SQL. Он стал ценным культурным артефактом для разработчиков баз данных, служившим источником развлечения, обучения и размышлений о возможностях этой распространенной технологии.