SQL-Ex blog

Joe Celko, Missing Data

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

Игнорирование чего-либо говорит само за себя. В SQL мы очень часто используем NULL, когда у нас нет значения. Хотя использование NULL является спорным, они действительно дают нам единый, согласованный и чётко определённый набор правил для обработки отсутствующих значений. Но с точки зрения моделирования с ними есть проблемы. Помимо специальных вычислительных правил и общего принципа, что «NULL распространяются», который может быть сложным для изучения, более фундаментальная проблема заключается в том, что универсальный NULL в SQL охватывает по крайней мере два очень разных случая. Доктор Кодд позже исправил это в пересмотренной версии своей реляционной модели, добавив типы отсутствующих значений A и I. В одном случае значение неизвестно, но сущность обладает атрибутом. С таким типом NULL всегда возможно, что мы обнаружим (известное) значение. Пример, который мне нравится приводить: когда я ношу шляпу, всегда возможно, что у меня может быть цвет волос. Чтобы быть очень полным в выборе значений по этой шкале, я мог бы также включить «лысый» или даже «варикозные вены и пигментные пятна» в домен моих значений для цвета волос. С таким типом NULL (теоретически) возможно придумать какое-то осмысленное значение или маркер.

Второй тип NULL никогда не будет иметь значения, потому что сам атрибут отсутствует у сущности. Я могу с уверенностью сказать, как примат, что у меня не вырастут перья и не появится вариант цвета перьев. Такой тип NULL возникает в OUTER JOIN и других конструкциях, которые их генерируют. Например, конструкции GROUPING SETS, ROLLUP и CUBE должны иметь предикат для проверки NULL, который был создан, в отличие от NULL, который был в исходных данных. GROUPING (<список ссылок на столбцы>) возвращает двоичное число, в котором каждая позиция с единицей соответствует созданному NULL.

Продолжить чтение "Отсутствующие данные"

§ Популярные темы недели на форуме

Топик		Сообщений	Просмотров
 45 (DML)		8		7
 1 (Learn)		2		19
 24 (Learn)		2		12

§ Авторы недели на форуме

Автор		Сообщений
 rock_4  	8
 selber  	3
 hongweibin	3
 Канатоходец	2

Продолжить чтение "Новости за 2025-12-20 - 2025-12-26"

Пересказ статьи Vaitheeswaran L M. PostgreSQL Internals: Heaps, Pages, and CTIDs

Строки не просто "сидят" в таблице в PostgreSQL. Они живут внутри страниц, перемещаются при обновлении и всякий раз получают новые "адреса" (CTID). Давайте разберемся подробнее и посмотрим, как это работает на самом деле.

1. Куча: Движок хранилища PostgreSQL

Когда вы создаете таблицу в PostgreSQL, она хранится как куча.

Куча - это просто неупорядоченная коллекция строк (кортежей). В отличие от некоторых баз данных, PostgreSQL автоматически не сохраняет строки в неком отсортированном порядке или по первичному ключу. Вместо этого она просто добавляет их на доступное пространство внутри страниц (блоков фиксированного размера 8Кб на диске).


Продолжить чтение "PostgreSQL изнутри: кучи, страницы и CTID"

Пересказ статьи Brent Ozar. Which Should You Use VARCHAR or NVARCHAR?

Вы строите новую таблицу или добавляете столбец, и вы хотите знать, какой тип данных использовать: VARCHAR или NVARCHAR?

Если вам необходимо хранить данные Unicode, выбор сделан за вас: NVARCHAR говорит, что это буду я.

Но если вы не уверены, то можете подумать: "Я должен использовать VARCHAR, поскольку он занимает вдвое меньше места". Я это знаю, потому что чувствовал то же самое, но множество комментаторов указали мне на это, когда я опубликовал ответ в «Office Hours» о том, что по умолчанию я использую VARCHAR. Один за другим разработчики говорили мне, что я неправ и что в 2025 пришло время вместо этого по умолчанию использовать NVARCHAR. Давайте проведем эксперимент!

Чтобы выяснить это, давайте возьмем большую базу данных Stack Overflow и создадим две копии таблицы Users. Я использую здесь таблицу Users, чтобы сделать демонстрацию краткой и понятной, поскольку у меня нет возможности целый день загружать гигабайты данных (и перезагружаться, как вы сейчас увидите). Мы просто собираемся сфокусироваться на строковых столбцах, поэтому я создал одну с типами VARCHAR, а другую - с NVARCHAR. Затем для простоты мы загрузим только те данные, которые являются чисто VARCHAR (потому что некоторые чудаки могли добавить какие-нибудь необычные данные Unicode в столбец AboutMe).
Продолжить чтение "Что использовать: VARCHAR или NVARCHAR?"

Автор: Mayur B, The OOM-Killer Summoning Ritual: “Just Increase work_mem”

Вы, вероятно, видели такую картину инцидента:

1. Процессы (backend) PostgreSQL начинают исчезать.


2. dmesg / journalctl -k показывает, что OOM-Killer в ядре завершает процесс postgres.


3. Кто-то замечает «out of memory» и рефлекторно рекомендует: «Увеличь work_mem».

Эта рекомендация часто является обратной для случаев, когда OOM-Killer завершает процессы на уровне ОС.

Лингвистическая ловушка: «Out of memory» звучит как «недостаточно work_mem»

work_mem — это не «память для запроса». Это базовый бюджет на операцию для узлов исполнителя (executor nodes), таких как сортировки и хеш-таблицы, прежде чем они начнут сбрасывать данные во временные файлы (spill). В документации PostgreSQL прямо предупреждают, что сложный запрос может выполнять несколько операций сортировки/хеширования параллельно, и многие сеансы могут делать это одновременно, поэтому общий объём используемой памяти может во много раз превышать work_mem.

Если вы глобально повышаете work_mem, вы повышаете потолок для многих потенциальных одновременных потребителей памяти. Это может превратить «редкий скачок» в «частое завершение процессов OOM-Killer».

Продолжить чтение "Ритуал призвания OOM-Killer: «Просто увеличь work_mem»"

§ Усилена проверка задачи 186 (SELECT, обуч. этап) данными от Komov S.M.

§ Популярные темы недели на форуме

Топик		Сообщений	Просмотров
 779 (SELECT)		10		7
 44 (DML)		5		7
 188 (SELECT)		3		4
 780 (SELECT)		2		9
 1 (Learn)		2		9

Продолжить чтение "Новости за 2025-12-13 - 2025-12-19"

Пересказ статьи Sergey Gigoyan. SQL Index Rebuild vs Reorganize Comparison

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

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

Продолжить чтение "Сравнение перестройки и реорганизации индексов SQL"

Автор: Josef Machytka, PostgreSQL 18 Asynchronous Disk I/O - Deep Dive Into Implementation

PostgreSQL 17 представил потоковый ввод-вывод — группировку нескольких чтений страниц в один системный вызов и использование более интеллектуальных подсказок posix_fadvise(). Только это дало до ~30% ускорения последовательного просмотра в некоторых рабочих нагрузках, но операция оставалась строго синхронной: каждый фоновый процесс отправлял запрос на чтение и затем простаивал в ожидании возврата данных от ядра, прежде чем продолжить работу. До PG17 PostgreSQL обычно читал по одной 8-килобайтной странице за раз.

PostgreSQL 18 делает следующий логический шаг: полноценная подсистема асинхронного ввода-вывода (AIO), которая может держать несколько операций чтения в процессе выполнения, пока фоновые процессы занимаются другой полезной работой. Чтения теперь перекрываются по времени, а не выполняются строго последовательно. Подсистема AIO целенаправленно ориентирована на операции, которые заранее знают номера нужных блоков и могут отправить несколько запросов на чтение предварительно:

• Последовательный просмотр кучи (heap), например, простые операции SELECT и COPY, которые потоково читают большие объёмы данных


• VACUUM для больших таблиц и индексов


• Выборка данных для ANALYZE


• Сканирование кучи по битовой карте (Bitmap heap scans)

Автовакуум также выигрывает от этого изменения, поскольку его рабочие процессы используют те же пути выполнения кода для VACUUM/ANALYZE. Другие операции пока остаются синхронными:

• Сканирование по B‑tree индексу / индексное сканирование без обращения к таблице (index‑only scans)


• Восстановление & репликация


• Все операции записи: INSERT, UPDATE, DELETE, запись в WAL


• Небольшие OLTP-запросы, затрагивающие единственную страницу кучи

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

Продолжить чтение "PostgreSQL 18: Асинхронный ввод-вывод на диск — детальный разбор реализации"

Joe Celko, Cooking an old puzzle

Я только что получил письмо о головоломке №11 из моей старой книги «SQL PUZZLES & ANSWER» от Райнера Гемуллы из Дрезденского технического университета, факультет информатики, институт SyA, Дрезден, Германия. Это очень изящное решение, и стыдно смотреть, насколько без нужды сложными были другие ответы:

ГОЛОВОЛОМКА С РАБОЧИМ НАРЯДОМ

Сенк Эрсой задал этот вопрос на форуме Gupta в CompuServe. У него есть таблица, которая выглядит так. На заводе проект описывается в рабочем наряде, который должен пройти через серию этапов. Этап в рабочем наряде либо завершён, либо ожидает завершения одного или нескольких предшествующих этапов. Его таблица выглядит следующим образом:

CREATE TABLE Projects (

    workorder CHAR(5) NOT NULL,

    step INTEGER NOT NULL CHECK (step BETWEEN 0 AND 1000),

    status CHAR(1) NOT NULL CHECK (status IN ('Completed', 'Waiting')),

    PRIMARY KEY (workorder, step)

);

С примерными данными, такими как:

Projects

workorder step status

=================================

'AA100'   0    'Completed'

'AA100'   1    'Waiting'

'AA100'   2    'Waiting'

'AA200'   0    'Waiting'

'AA200'   1    'Waiting'

'AA300'   0    'Completed'

'AA300'   1    'Completed'

Он хотел бы получить рабочие наряды, где этап равен нулю и статус «Completed», но все другие этапы для этого рабочего наряда имеют статус «Waiting». Например, в примере данных запрос должен вернуть только «AA100».

Продолжить чтение "Готовим старую головоломку"

§ Популярные темы недели на форуме

Топик		Сообщений	Просмотров
 34 (Learn)		6		9
 779 (SELECT)		6		2

§ Авторы недели на форуме

Автор		Сообщений
 Baser  	6
 pegoopik  	3
 selber  	2
 ChronoMorzh  	2

Продолжить чтение "Новости за 2025-12-06 - 2025-12-12"

Автор: Greg Sabino Mullane, Postgres 18 New Default for Data Checksums and How to Deal with Upgrades

В недавнем патче для PostgreSQL, созданном Грегом Сабино Маллейном, PostgreSQL сделал новый шаг вперёд в обеспечении целостности данных: контрольные суммы данных теперь включены по умолчанию.

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

Давайте углубимся в то, что представляет собой эта функция, что означает новое значение по умолчанию для вас и как это влияет на обновления.

Продолжить чтение "Новое значение по умолчанию для контрольных сумм в PostgreSQL 18 и как обновляться"

Пересказ статьи Brandur Leach. Postgres 18: OLD and NEW Rows in the RETURNING Clause

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

"В этом релизе добавлены возможности доступа как к предыдущим (OLD), так и к текущим (NEW) значениям в предложении RETURNING для команд INSERT, UPDATE, DELETE and MERGE."

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

Простая демонстрация с UPDATE для получения всех старых и новых значений:
Продолжить чтение "PostgreSQL 18: строки OLD и NEW в предложении RETURNING"

Автор: Kendra Little, Index Bloat in Postgres: Why It Matters, How to Identify, How to Resolve

Раздувание индексов в PostgreSQL может вызывать проблемы, но его легко пропустить.

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

Эта статья рассказывает о том, что вам нужно знать о раздувании индексов: как его обнаружить и как исправить.

Продолжить чтение "Раздувание индексов в PostgreSQL: важность, выявление и решение"

§ В ответ на замечание Komov S. M. усилена проверка задачи 172 (SELECT, обуч. этап).

§ Популярные темы недели на форуме

Топик		Сообщений	Просмотров

 42 (DML)		5		5

 46 (Learn)		4		9

 780 (SELECT)		3		10

 2 (SELECT)		3		11

 5 (SELECT)		2		8

Продолжить чтение "Новости за 2025-11-29 - 2025-12-05"

Пересказ статьи Andrea Gnemmi. Steps to Change a PostgreSQL Column Datatype when Included in a View

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

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

Поведение PostgreSQL

Чтобы объяснить поведение PostgreSQL, давайте выполним небольшой тест, используя тестовую базу данных Chinook, которую можно свободно загрузить с GitHub для различных СУБД. Это имитация магазина цифровых медиа с образцами данных. Все, что вам нужно сделать, - это загрузить требуемую версию, и вы получите все скрипты создания структуры и вставки данных.

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