SQL-Ex blog

Пересказ статьи 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 типа данных столбца, который присутствует в представлении"

Пересказ статьи Lorenzo Uriel. Bind Parameters Make Your SQL Queries Faster

Конечно, вы уже использовали параметры привязки, но слышали ли вы такой совет для оптимизации?

Что-то типа: "Использование параметров привязки (параметризованных запросов) вместо конкатенации строки SQL улучшит производительность SQL"?

Почему это так вы поймете из этой статьи.

Продолжить чтение "Параметры привязки ускоряют выполнение SQL-запросов"

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

Топик		Сообщений	Просмотров
 780 (SELECT)		3		10
 180 (Learn)		2		8
 -15 (SELECT)		2		5
 2 (SELECT)		2		7
 32 (Learn)		2		8

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

Автор		Сообщений
 rock_4  	4
 Rujan  	3
 Paulus73	3
 pegoopik  	2

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

Пересказ статьи Christopher Jones. Python Data Classes make it easy to fetch database rows as objects

rowfactories в Python-oracledb являются мощным средством для запросов к базам данных Oracle, позволяющим изменить представление извлекаемых строк, уменьшить количество шаблонного кода приложения и копирования данных. Здесь мы покажем, как легко использовать класс данных Python с rowfactory для преобразования строк в экземпляры пользовательского класса.

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

Рассмотрим код, который не использует rowfactory:
Продолжить чтение "Классы данных Python облегчают получение строк из базы данных как объектов"

Пересказ статьи Assaf Fraenkel. SELECT and RETURN in Stored Procedures — Sql Server vs Postgres Part 1

Вы испытываете сложности при переходе от SQL Server к PostgreSQL? В этой серии статей раскрывается важное различие в механизмах двух баз данных: как операторы SELECT и RETURN ведут себя в хранимых процедурах. Давайте разберемся в этом ключевом отличии и сделаем ваш переход более гладким.

Даже при наличии некоторых продуктов, которые могут помочь вам в переходе ((Google DMS, AWS DMS, Ispirer и других), а также помощи ИИ (посмотрите для примера ссылки на Google и Ispirer выше) весьма важно глубокое понимание ключевых различий между этими движками.
Продолжить чтение "SELECT и RETURN в хранимых процедурах — сравнение Sql Server и Postgres. Часть 1"

§ Усилена проверка задачи 20 (DML) данными от saphirion.
§ В ответ на сообщение pegoopik усилена проверка задачи 191 (SELECT, обучающий этап).

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

Топик		Сообщений	Просмотров
 191 (Learn)		11		6
 8 (Learn)		2		23
 10 (Learn)		2		17
 16 (Learn)		2		25

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

Автор: Joe Celko

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

В октябре 2000 года Даррен Тафт опубликовал в группе новостей по SQL Server задачу, которая выглядит лёгкой. Приведу его слова: «У меня есть система заказов, которая выделяет номера в пределах заранее определённых диапазонов. Сейчас я делаю это так: ...» На этом месте он привёл хранимую процедуру на диалекте T-SQL. В ней был цикл, который увеличивал значение request_id до тех пор, пока не находил пропуск в нумерации или пока попытка не проваливалась. Даррен продолжил: «Это годится для первых нескольких номеров, но когда диапазоны достигают 10 000 между минимумом и максимумом, всё начинает заметно тормозить. Может ли кто-нибудь придумать лучший способ?

Продолжить чтение "Заполнение пробелов"