Администратор баз данных: вопросы на собеседовании

Транзакция — это последовательность операций с базой данных, которая выполняется как единое целое. Либо все операции транзакции успешно применяются (commit), либо ни одна из них не применяется (rollback), что обеспечивает целостность данных.

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

Read Uncommitted — самый низкий уровень изоляции. Позволяет видеть незакоммиченные изменения других транзакций (грязное чтение). В PostgreSQL фактически ведёт себя как Read Committed.

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

Repeatable Read — транзакция видит данные такими, какими они были на момент начала самой транзакции. Все запросы внутри транзакции видят один и тот же снимок данных, что предотвращает неповторяющиеся чтения, но возможны фантомные чтения.

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

Пример установки уровня изоляции в PostgreSQL:

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

Индексы создаются по одному или нескольким столбцам и могут быть разных типов: B-дерево, хеш-индексы и др. Основное преимущество — повышение производительности запросов SELECT.

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

Пример создания индекса в SQL:

Этот индекс ускорит поиск клиентов по фамилии.

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

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

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

Пример на SQL (PL/pgSQL):

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

Организация кластера PostgreSQL для обеспечения отказоустойчивости и репликации обычно включает следующие компоненты:

Основной сервер (Primary) — принимает все записи и изменения данных.

Реплики (Standby/Replica) — получают данные с основного сервера в режиме реального времени или с небольшой задержкой.

Схемы репликации:

Streaming Replication — потоковая репликация, где реплики получают WAL (Write-Ahead Log) записи напрямую от основного сервера.

Logical Replication — репликация на уровне логических изменений, позволяет более гибко настраивать подписки и фильтрацию.

Для отказоустойчивости обычно используют:

Failover — автоматическое или ручное переключение на реплику при сбое основного сервера.

Мониторинг и менеджеры кластера (например, Patroni, repmgr) — обеспечивают автоматизацию failover и контроль состояния.

Пример:

Основной сервер настроен с wal_level = replica

Реплики подключаются по streaming replication

Используется Patroni для автоматического failover и управления кластером

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

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

Для решения задачи я провел детальный анализ профиля запросов, выявил узкие места и предложил следующие меры:

Переработка схемы данных с нормализацией и денормализацией там, где это было оправдано.

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

Внедрение партиционирования таблиц для распределения нагрузки.

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

Использование репликации для разделения чтения и записи.

В результате время отклика уменьшилось в 3-4 раза, а система стала устойчивой к пиковым нагрузкам.

Always On High Availability в MS SQL Server — это набор технологий для обеспечения высокой доступности и отказоустойчивости баз данных. Основные компоненты:

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

Failover Cluster Instances (FCI) — обеспечивают отказоустойчивость на уровне экземпляра SQL Server с помощью кластеризации Windows.

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

Пример создания Availability Group (упрощённо):

Таким образом, Always On обеспечивает непрерывность работы приложений и защиту данных от сбоев оборудования или ПО.

CTE (Common Table Expression) — это временный именованный результат запроса, который можно использовать внутри основного SQL-запроса. Он объявляется с помощью ключевого слова WITH и позволяет структурировать сложные запросы, делая их более читаемыми и поддерживаемыми.

Преимущества CTE:

Улучшение читаемости и организации кода.

Возможность рекурсивных запросов (рекурсивные CTE).

Повторное использование результата CTE в основном запросе.

Материализуется ли CTE? В большинстве СУБД CTE не материализуется как отдельная физическая таблица, а рассматривается как подзапрос, который оптимизатор может встроить в основной запрос. Однако в некоторых случаях (например, при рекурсивных CTE или в специфичных СУБД) может происходить материализация для оптимизации.

Пример использования CTE:

Если индекс не применяется из-за использования функции на столбце в условии (например, WHERE UPPER(column) = 'VALUE'), то индекс не может быть использован напрямую, так как функция изменяет данные.

Возможные решения:

Создать функциональный (expression-based) индекс на результат функции, если СУБД это поддерживает. Например, в Oracle или PostgreSQL можно создать индекс на UPPER(column).

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

Использовать вычисляемые/виртуальные столбцы с индексами на них.

Пример создания функционального индекса в PostgreSQL:

Это позволит использовать индекс при запросах с UPPER(column).

Если в столбце мало уникальных значений и много NULL, то обычный B-Tree индекс будет неэффективен, так как он плохо справляется с низкой селективностью и большим количеством NULL.

Лучшим вариантом может быть:

Bitmap-индекс (если СУБД поддерживает), который хорошо работает при низкой кардинальности и позволяет эффективно фильтровать по нескольким таким столбцам.

Частичный индекс (partial index), индексирующий только не NULL значения, если СУБД поддерживает такую возможность.

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

Выбор зависит от конкретной СУБД и характера запросов, но ключевое — использовать индекс, оптимизированный для низкой селективности и большого количества NULL.

Для выдачи прав на выполнение хранимой процедуры в большинстве СУБД используется команда GRANT EXECUTE.

Например, в PostgreSQL или Oracle:

В некоторых СУБД можно выдавать права на весь пакет процедур или на схему целиком.

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

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

Пример на SQL (PostgreSQL):

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

Checkpointer и Background Writer — это два разных процесса в PostgreSQL, которые занимаются записью данных на диск, но с разными целями и механизмами.

Checkpointer отвечает за создание контрольных точек (checkpoints). Он периодически записывает все изменённые страницы из буфера в постоянное хранилище, чтобы сократить время восстановления после сбоя. Checkpoint гарантирует, что все данные до определённого момента времени сохранены на диске.

Background Writer работает постоянно и постепенно записывает изменённые страницы из буфера на диск, чтобы уменьшить нагрузку на систему во время чекпоинта. Его задача — поддерживать буфер в чистом состоянии, чтобы при наступлении чекпоинта не пришлось записывать слишком много данных сразу.

Итого:

Checkpointer — периодический, крупный сброс данных для обеспечения целостности.

Background Writer — постоянная, фоновая очистка буфера для равномерного распределения нагрузки.

В базах данных существуют разные типы индексов, которые помогают ускорить поиск и сортировку данных:

B-Tree индекс — самый распространённый тип, подходит для равенств и диапазонных запросов. Используется в большинстве СУБД по умолчанию.

Hash индекс — эффективен для быстрых точных совпадений, но не поддерживает диапазонные запросы.

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

Full-text индекс — для полнотекстового поиска по текстовым полям.

Spatial индекс — для географических данных и пространственных запросов.

Чаще всего я использовал B-Tree индексы, так как они универсальны и поддерживают широкий спектр запросов. Также применял полнотекстовые индексы для поиска по тексту и hash-индексы для ускорения точных совпадений в некоторых случаях.

В SQL Server транзакция — это последовательность операций, которые выполняются как единое целое. Транзакция гарантирует свойства ACID:

Atomicity (Атомарность): все операции внутри транзакции либо выполняются полностью, либо не выполняются вовсе.

Consistency (Согласованность): после выполнения транзакции база данных остается в корректном состоянии.

Isolation (Изоляция): параллельные транзакции не влияют друг на друга.

Durability (Надежность): после фиксации транзакции изменения сохраняются даже при сбоях.

Транзакции начинаются с BEGIN TRANSACTION, завершаются COMMIT (фиксируют изменения) или ROLLBACK (откатывают изменения). SQL Server использует журнал транзакций для обеспечения надежности и восстановления.

Пример:

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

shared_buffers — размер памяти, выделяемой под кэширование данных. Обычно рекомендуется ставить около 25-40% от объема оперативной памяти.

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

maintenance_work_mem — память для операций обслуживания, таких как VACUUM, CREATE INDEX.

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

max_parallel_workers_per_gather — количество параллельных воркеров для выполнения запросов.

random_page_cost — стоимость случайного чтения страницы с диска, влияет на выбор плана выполнения.

checkpoint_segments (в новых версиях заменён на max_wal_size) — размер WAL для контроля частоты контрольных точек.

autovacuum параметры — настройки автоматической очистки и анализа таблиц для предотвращения раздувания и поддержания статистики.

Настройка зависит от конкретной нагрузки и железа, поэтому важен мониторинг и постепенная корректировка.

Transaction ID Wraparound в PostgreSQL — это ситуация, связанная с ограничением на количество уникальных идентификаторов транзакций (Transaction IDs, или XID), которые PostgreSQL может использовать.

Каждая транзакция получает уникальный 32-битный XID, который инкрементируется. Из-за ограниченного диапазона (около 4 миллиардов) XID могут "обернуться" (wraparound), то есть счетчик вернется к нулю и начнет повторно использовать старые значения.

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

Для предотвращения wraparound PostgreSQL автоматически запускает autovacuum, который обновляет информацию о видимости транзакций и предотвращает повторное использование XID, пока это безопасно.

Если wraparound случится, это может привести к "заморозке" таблиц и необходимости аварийного вмешательства.

Таким образом, Transaction ID Wraparound — это ограничение архитектуры PostgreSQL, требующее регулярного обслуживания базы данных для предотвращения ошибок.

В выводе EXPLAIN:

type: ALL означает, что выполняется полное сканирование таблицы (full table scan), что обычно медленно при больших объемах данных.

rows: 200000 — количество строк, которые MySQL планирует прочитать.

Using temporary — используется временная таблица для обработки запроса, например, при сортировке или группировке.

Using filesort — сортировка выполняется не по индексу, а с помощью дополнительной операции сортировки.

Как оптимизировать:

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

Переписать запрос — возможно, изменить логику запроса, чтобы уменьшить объем обрабатываемых данных.

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

Проверить статистику и обновить индексы — чтобы оптимизатор имел актуальную информацию.

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

Это может значительно ускорить выполнение и убрать Using filesort.

В SQL Server msdb — это системная база данных, которая используется для управления задачами, планировщиком заданий (SQL Server Agent), резервным копированием, восстановлением и другими служебными функциями.

Основные функции msdb:

Хранение информации о заданиях и расписаниях SQL Server Agent.

Управление операциями резервного копирования и восстановления.

Отслеживание истории выполнения заданий и оповещений.

Управление почтовыми профилями и операторами для уведомлений.

Например, когда вы создаёте задание для автоматического выполнения скриптов или резервного копирования, информация об этом хранится в msdb. Эта база критична для администрирования и автоматизации задач в SQL Server.

Зеркалирование (mirroring) в SQL Server — это технология высокой доступности, при которой база данных синхронно или асинхронно дублируется на другом сервере (зеркале). В случае сбоя основного сервера происходит автоматическое или ручное переключение на зеркальный сервер, обеспечивая минимальное время простоя.

Основные особенности зеркалирования:

Работает на уровне одной базы данных.

Поддерживает два режима: синхронный (high safety) и асинхронный (high performance).

Имеет роль основного сервера (principal) и зеркального (mirror).

Может использовать дополнительный сервер для автоматического переключения (witness).

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

Ключевые отличия:

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

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

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

Пример использования зеркалирования:

WHERE и HAVING — это условия фильтрации в SQL, но применяются на разных этапах обработки данных.

WHERE фильтрует строки до группировки. Он ограничивает набор данных, которые попадут в агрегатные функции.

HAVING фильтрует группы после применения агрегатных функций (например, SUM, COUNT).

Пример:

Таким образом, WHERE работает с отдельными строками, а HAVING — с агрегированными группами.

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

Основные компоненты пакета процедур:

Спецификация (Specification) — объявление всех процедур, функций, типов данных и переменных, доступных извне. Это интерфейс пакета.

Тело (Body) — реализация всех объявленных в спецификации процедур и функций. Здесь описывается логика.

Пакеты позволяют:

Скрыть внутреннюю реализацию (инкапсуляция).

Объявлять глобальные переменные и константы.

Улучшать производительность за счёт компиляции и кэширования.

Пример (Oracle PL/SQL):

В Always On Availability Groups в SQL Server существует два типа репликации данных между основным и вторичными репликами: синхронная и асинхронная.

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

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

Выбор между ними зависит от требований к отказоустойчивости и производительности:

Синхронная — для критичных данных и минимальной потери.

Асинхронная — для географически распределённых систем с высокой задержкой сети или когда важнее производительность.

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

Анализ и переписывание SQL-запросов для уменьшения количества операций и повышения эффективности.

Использование индексов для ускорения выборок и соединений.

Разбиение сложных процедур на более мелкие, чтобы улучшить читаемость и повторное использование.

Кэширование часто используемых данных внутри процедур.

Профилирование выполнения процедур с помощью встроенных инструментов СУБД для выявления узких мест.

Например, в одной из процедур я заменил вложенные циклы на set-based операции, что значительно сократило время выполнения и нагрузку на сервер.

В Oracle PL/SQL для возврата набора строк из процедуры часто используют REF CURSOR. Это указатель на результат запроса, который можно открыть внутри процедуры и вернуть вызывающему коду.

Пример процедуры с OUT параметром типа REF CURSOR:

Вызов из PL/SQL блока:

Таким образом, процедура открывает REF CURSOR с нужным запросом, а вызывающий код читает из него данные.

Да, внутри триггера можно вызывать исключения (например, в PL/SQL или T-SQL). Если исключение возникает, то транзакция, в рамках которой сработал триггер, обычно откатывается, и изменения в базе данных не сохраняются.

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

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

Это связано с тем, что операции в рамках транзакции являются атомарными: либо все изменения фиксируются (commit), либо все отменяются (rollback).

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

Внешние механизмы логирования (например, запись в файл или отдельную систему логов).

Отдельные транзакции для логирования, которые коммитятся независимо.

Пример:

Таким образом, обычный INSERT в таблицу логов внутри откатываемой транзакции не сохранится.

AlwaysOn Availability Groups (AG) в SQL Server обеспечивают высокую доступность и отказоустойчивость баз данных. Настройка включает несколько ключевых шагов:

Создание Windows Server Failover Cluster (WSFC) — это основа для AG. WSFC обеспечивает кластеризацию серверов, позволяя им работать как единое целое и автоматически переключаться при сбоях.

Настройка SQL Server Instances на каждом узле кластера.

Создание Availability Group с выбором баз данных для репликации.

Настройка реплик — основная (primary) и вторичные (secondary), с выбором режима синхронизации (синхронный или асинхронный).

Настройка Listener — виртуальный сетевой адрес, через который клиенты подключаются к AG.

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

TCL (Transaction Control Language) — это подмножество SQL-команд, которые управляют транзакциями в базе данных. Они позволяют контролировать выполнение групп операций как единого целого, обеспечивая целостность данных.

Основные команды TCL:

COMMIT — фиксирует все изменения, сделанные в текущей транзакции.

ROLLBACK — отменяет все изменения, сделанные в текущей транзакции.

SAVEPOINT — устанавливает точку сохранения внутри транзакции, к которой можно откатиться.

SET TRANSACTION — задаёт свойства текущей транзакции (например, уровень изоляции).

Пример:

Директива PRAGMA AUTONOMOUS_TRANSACTION используется в Oracle PL/SQL для создания автономной транзакции внутри основной транзакции. Это значит, что код, помеченный этой директивой, выполняется в отдельной транзакции, независимой от основной.

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

Пример:

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

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

Основные типы триггеров:

По времени срабатывания:

BEFORE (до операции) — триггер срабатывает перед выполнением операции.

AFTER (после операции) — срабатывает после выполнения операции.

INSTEAD OF — заменяет операцию (обычно используется для представлений).

По типу операции:

INSERT — при вставке новых записей.

UPDATE — при обновлении существующих записей.

DELETE — при удалении записей.

По типу операции:

Например, триггер BEFORE INSERT может проверять корректность данных перед их добавлением, а AFTER UPDATE — вести журнал изменений.

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

Nested Loop Join — это один из алгоритмов соединения таблиц в реляционных базах данных. Он работает по принципу вложенных циклов: для каждой строки из первой (внешней) таблицы происходит перебор всех строк из второй (внутренней) таблицы с целью найти совпадения по условию соединения.

Когда применяется:

Когда одна из таблиц очень маленькая, и перебор по ней не слишком затратен.

Когда нет подходящих индексов для более эффективных алгоритмов соединения.

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

Недостаток — высокая вычислительная сложность (O(n*m)), поэтому для больших таблиц обычно используют более эффективные алгоритмы, например, Hash Join или Merge Join.

Пример:

Если нужно соединить таблицу сотрудников с таблицей отделов по id отдела, Nested Loop Join переберёт каждого сотрудника и для каждого — все отделы, чтобы найти совпадение.

Для поиска дубликатов в таблице harvester_tasks_queue по полям departure_station, arrival_station, departure_date, crawler_id можно использовать следующий SQL-запрос:

Это позволит выявить группы записей, которые повторяются.

План безопасного удаления дубликатов:

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

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

Использовать CTE или подзапрос для удаления всех дубликатов, кроме одной записи в каждой группе. Например, если есть уникальный идентификатор id:

Проверить результат удаления, убедиться, что остались только уникальные записи.

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

Такой подход обеспечит безопасное удаление дубликатов и сохранит целостность данных.

Для мониторинга производительности базы данных обычно используют встроенные средства СУБД и внешние инструменты:

Встроенные средства: например, в PostgreSQL — pg_stat_statements для анализа запросов, EXPLAIN ANALYZE для оценки плана выполнения.

Системные метрики: CPU, память, I/O через top, htop, iostat или специализированные системы мониторинга (Prometheus, Zabbix).

Логи медленных запросов: включение логирования медленных запросов для выявления проблемных.

При высоком CPU или медленных запросах:

Анализирую самые ресурсоёмкие запросы через pg_stat_statements или аналог.

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

Проверяю планы выполнения (EXPLAIN ANALYZE) для выявления узких мест.

Рассматриваю возможность кэширования или денормализации данных.

Если нагрузка связана с блокировками — анализирую блокировки и транзакции.

При необходимости масштабирую систему: репликация, шардирование.

Пример запроса для выявления самых затратных запросов в PostgreSQL:

Оконные функции (window functions) в SQL позволяют выполнять вычисления по набору строк, связанных с текущей строкой, без группировки результата. В отличие от агрегатных функций, которые сводят несколько строк к одной (например, SUM, AVG с GROUP BY), оконные функции сохраняют исходное количество строк, добавляя вычисленные значения как дополнительные столбцы.

Преимущества оконных функций:

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

Можно использовать вместе с обычными столбцами без группировки.

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

Пример:

Здесь для каждой строки вычисляется средняя зарплата по отделу, при этом каждая строка остаётся в результате.

В MS SQL Server существуют несколько типов индексов, основные из них:

Кластерный индекс (Clustered Index)

Некластерный индекс (Non-Clustered Index)

Индексы полнотекстового поиска

XML-индексы

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

Кластерный индекс

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

Преимущества:

Быстрый доступ к данным при запросах с диапазоном значений по ключу.

Эффективен для операций сортировки и группировки.

Недостатки:

Изменение данных может приводить к перестройке страниц, что влияет на производительность.

Некластерный индекс

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

Преимущества:

Позволяет ускорить поиск по неключевым столбцам.

Можно создавать несколько индексов для разных сценариев запросов.

Недостатки:

Дополнительное место на диске.

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

Пример создания кластерного и некластерного индексов:

Когда Patroni переключает мастер на реплику (failover), происходит смена роли узла в кластере PostgreSQL. SQL-сервер, который подключается к базе, должен узнать о новом мастере, чтобы продолжить работу с записью.

Как это происходит:

Patroni управляет состоянием кластера и хранит информацию о текущем мастере в распределённом хранилище (например, Etcd, Consul или ZooKeeper).

Клиенты (SQL-серверы или приложения) обычно не подключаются напрямую к конкретному хосту, а используют виртуальный IP, DNS-имя или прокси, который перенаправляет запросы на текущий мастер.

При переключении Patroni обновляет виртуальный IP или DNS-запись, указывая на новый мастер.

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

Если используется прокси (например, HAProxy или PgBouncer), он также обновляет маршрутизацию на новый мастер.

Таким образом, SQL-сервер узнаёт о смене мастера через инфраструктуру балансировки или обновление DNS/виртуального IP, а не напрямую от самого PostgreSQL.

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

Prometheus + Grafana — для сбора метрик и визуализации состояния серверов и баз данных.

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

Percona Monitoring and Management (PMM) — специализированный инструмент для мониторинга MySQL и MongoDB.

Oracle Enterprise Manager — для мониторинга и управления Oracle Database.

Nagios — для базового мониторинга доступности и состояния сервисов.

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

Минорное обновление PostgreSQL в кластере Patroni обычно выполняется с учётом высокой доступности и минимизации простоя. Основные шаги:

Обновить реплики поочерёдно:

Остановить Patroni на реплике.

Обновить PostgreSQL до нужной минорной версии.

Запустить Patroni и дождаться синхронизации с мастером.

После обновления всех реплик выполнить switchover, чтобы сделать одну из обновлённых реплик мастером.

Обновить бывший мастер аналогичным образом.

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

Write-Ahead Log (WAL) — это метод обеспечения надежности и целостности данных в системах управления базами данных. Суть WAL в том, что перед изменением данных в основной базе все операции сначала записываются в журнал (лог) изменений. Это позволяет при сбое или аварии восстановить базу данных до консистентного состояния, применяя или откатывая изменения из лога.

Пример: при обновлении записи сначала в WAL пишется информация об изменении, затем происходит обновление самой записи. Если система упадет во время операции, при перезапуске база прочитает WAL и завершит или отменит незавершённые транзакции.

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

Основные преимущества нормализации:

Уменьшение избыточности данных — данные хранятся в одном месте, что снижает дублирование.

Обеспечение целостности данных — изменения в одном месте автоматически отражаются во всех связанных данных.

Упрощение поддержки и обновления — легче вносить изменения без риска рассогласования.

Повышение эффективности хранения — экономия места за счёт устранения повторяющихся данных.

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

Функция и процедура — это подпрограммы, но с разными целями и поведением:

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

Процедура (часто называется подпрограммой или процедурой в некоторых СУБД) выполняет набор действий, может изменять состояние, но не обязана возвращать значение.

Пример на псевдокоде:

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

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

Read Uncommitted — самый низкий уровень изоляции. В PostgreSQL фактически ведет себя как Read Committed, то есть не позволяет видеть непроверенные (uncommitted) изменения других транзакций.

Read Committed (уровень по умолчанию) — транзакция видит только те изменения, которые были зафиксированы (committed) к моменту выполнения каждого отдельного запроса. Между запросами в одной транзакции могут быть видны разные данные, если другие транзакции успели зафиксировать изменения.

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

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

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

Чтобы защитить целевую таблицу от дубликатов при вставке данных из временной таблицы и одновременно обновить существующие записи более свежими данными (операция upsert), можно использовать следующие подходы:

Уникальные ограничения и индекс

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

Использование конструкции UPSERT

В современных СУБД (например, PostgreSQL, MySQL) есть поддержка конструкции INSERT ... ON CONFLICT ... DO UPDATE или INSERT ... ON DUPLICATE KEY UPDATE.

Пример для PostgreSQL:

Здесь:

ON CONFLICT (id) — конфликт по уникальному ключу id.

В блоке DO UPDATE обновляем поля, если данные из временной таблицы свежее (например, по полю updated_at).

Альтернативный подход — MERGE (если поддерживается)

Предварительная очистка дубликатов во временной таблице

Если во временной таблице могут быть дубликаты, стоит сначала их устранить, например, с помощью DISTINCT или агрегирующих функций.

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

Это не означает, что мы вернулись в график.

Log shipping и инкрементальное резервное копирование — это два разных подхода к обеспечению отказоустойчивости и резервному копированию баз данных.

Log shipping — это процесс автоматической передачи и применения журналов транзакций (transaction logs) с основной базы данных на вторичную (резервную) базу. Это позволяет поддерживать резервную копию базы данных почти в актуальном состоянии, с минимальной задержкой. В случае сбоя можно быстро переключиться на резервную базу.

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

Ключевые отличия:

Log shipping ориентирован на передачу и применение журналов транзакций для поддержания синхронизации между основным и резервным сервером.

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

Пример: в SQL Server log shipping автоматически копирует и восстанавливает логи транзакций на резервном сервере, а инкрементальный бэкап сохраняет только изменённые страницы данных с момента последнего бэкапа.

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

Примеры хинтов, которые часто используются:

В Oracle: /*+ INDEX(table_name index_name) */ — заставляет использовать конкретный индекс.

В SQL Server: WITH (NOLOCK) — позволяет читать данные без блокировок.

В MySQL: USE INDEX (index_name) — указывает использовать определённый индекс.

Пример в Oracle:

Здесь мы подсказываем оптимизатору использовать индекс emp_dept_idx для таблицы employees.

Использование хинтов требует понимания структуры данных и поведения оптимизатора, так как неправильное применение может ухудшить производительность.

В PL/SQL блок исключений используется для обработки ошибок, которые могут возникнуть во время выполнения кода. Он располагается в конце анонимного блока или процедуры/функции после секции BEGIN ... EXCEPTION ... END.

Пример блока с обработкой исключений:

Для создания пользовательского исключения нужно объявить переменную типа EXCEPTION, а затем вызвать её с помощью оператора RAISE.

Пример пользовательского исключения:

В Oracle существуют следующие уровни изоляции транзакций:

READ COMMITTED (по умолчанию)

SERIALIZABLE

READ COMMITTED — это уровень, при котором транзакция видит только те данные, которые были зафиксированы (committed) на момент чтения. Это предотвращает чтение «грязных» данных, но допускает неповторяющееся чтение (non-repeatable reads), когда данные могут измениться между двумя чтениями в одной транзакции.

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

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

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

Тестирование с известными входными данными: запускают процедуру с заранее подготовленными параметрами и сравнивают результат с ожидаемым.

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

Использование EXPLAIN и планов выполнения: для оценки эффективности запроса.

Сравнение с эталонными данными: проверяют, что изменения в базе соответствуют ожиданиям.

Автоматизированные тесты: пишут unit-тесты для процедур, если СУБД это поддерживает.

Например, можно выполнить процедуру и сразу сделать SELECT из таблицы, которую она должна изменить, чтобы убедиться, что данные обновились корректно.

Для поиска топ-5 маршрутов (departure_station, arrival_station) по количеству задач за январь 2025 из таблицы harvester_tasks_queue можно использовать следующий SQL-запрос:

Здесь предполагается, что в таблице есть поле с датой задачи, например task_date. Запрос группирует задачи по маршрутам, считает количество задач для каждого маршрута за январь 2025 и выводит топ-5 по убыванию количества.

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

Нормализация и денормализация: Сбалансируйте нормализацию для устранения избыточности и денормализацию для ускорения чтения.

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

Партиционирование: Разделите таблицу на партиции по дате, диапазону или хешу, чтобы ускорить запросы и упростить обслуживание.

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

Оптимизация запросов: Анализируйте планы выполнения, избегайте сканирования всей таблицы.

Использование подходящих типов данных: Минимизируйте размер строк, выбирая оптимальные типы.

Пример партиционирования в PostgreSQL:

Такой подход позволяет эффективно управлять большими объемами данных и поддерживать высокую производительность.

В PostgreSQL зависшие транзакции — это те, которые долгое время находятся в состоянии активной или idle in transaction, не завершаясь. Чтобы их определить, можно использовать запрос к системному каталогу pg_stat_activity.

Пример запроса для поиска таких транзакций:

Здесь мы смотрим транзакции, которые находятся в состоянии "idle in transaction" или "active" и длятся более 5 минут. Такие транзакции могут блокировать другие операции и приводить к зависаниям.

Для более глубокого анализа можно проверить блокировки через pg_locks и связать их с транзакциями из pg_stat_activity.

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

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

Пример использования явного курсора в PL/SQL:

Логический бэкап — это сохранение данных в виде SQL-дампа или другого формата, который отражает структуру и содержимое базы данных (например, команды CREATE TABLE, INSERT). Такой бэкап удобен для миграций, восстановления отдельных таблиц или данных, а также для переноса между разными версиями СУБД.

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

Когда использовать:

Логический бэкап подходит для небольших баз, когда нужно переносить данные между разными СУБД или версиями, или когда важна читаемость и возможность частичного восстановления.

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

В AlwaysOn Availability Groups (Microsoft SQL Server) разница между синхронным и асинхронным режимом репликации заключается в гарантии доставки и задержках:

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

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

Выбор режима зависит от требований к отказоустойчивости и производительности:

Синхронный — для критичных данных и минимальной потери.

Асинхронный — для географически распределённых систем с высокой задержкой сети.

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

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

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

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

Идентификация причин: Определение транзакций и ресурсов, участвующих в дедлоке.

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

Уменьшение времени удержания блокировок: Разбиение больших транзакций на меньшие, чтобы сократить время блокировки.

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

Рефакторинг логики: Пересмотр бизнес-логики для минимизации конкуренции за ресурсы.

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

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

Подход к нормализации обычно включает следующие шаги:

Анализ требований и данных — понимание сущностей, их атрибутов и взаимосвязей.

Приведение к первой нормальной форме (1НФ) — устранение повторяющихся групп и обеспечение атомарности данных.

Вторая нормальная форма (2НФ) — устранение частичных зависимостей, когда атрибуты зависят не от всего ключа, а от части.

Третья нормальная форма (3НФ) — устранение транзитивных зависимостей, когда атрибут зависит от другого неключевого атрибута.

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

Пример: если в таблице "Заказы" хранится информация о клиенте, лучше вынести данные клиента в отдельную таблицу "Клиенты" и связать их через внешний ключ, чтобы не дублировать информацию при каждом заказе.

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

Подготовка:

Сделайте полный бэкап данных.

Убедитесь, что у вас есть доступ к новым бинарным файлам PostgreSQL нужной версии.

Подготовка:

Обновление реплик:

Остановите реплики по одной.

Выполните обновление PostgreSQL на каждой реплике (установка новой версии).

Запустите реплику с новой версией, используя pg_upgrade или восстановление из бэкапа, если необходимо.

Подключите реплику обратно к кластеру Patroni.

Обновление реплик:

Обновление мастера:

Переключите мастер на одну из обновленных реплик (failover), чтобы мастер был на новой версии.

Остановите старый мастер.

Обновите PostgreSQL на старом мастере.

Запустите его как реплику новой версии.

Обновление мастера:

Проверка:

Убедитесь, что все ноды работают корректно и синхронизируются.

Проверка:

Важно: Patroni не поддерживает автоматическое мажорное обновление, поэтому процесс требует ручного вмешательства и тщательного тестирования.

Пример команды для обновления с помощью pg_upgrade на одной ноде:

После обновления ноды обновите конфигурацию Patroni, если менялись пути или версии.

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

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

В Oracle автономная транзакция объявляется с помощью pragma:

Пример использования:

Таким образом, даже если основная транзакция откатится, запись в журнал ошибок сохранится.