Моделирование данных и транзакции
Нормализация и денормализация схемы, ограничения целостности, суррогатные ключи, документные и реляционные хранилища, OLAP против OLTP, изоляция транзакций, блокировка строк и оптимистичная блокировка, масштабирование базы.
18 вопросов
JuniorТеорияОчень частоЧто такое пул соединений и зачем он нужен пакету database/sql в Go?
Что такое пул соединений и зачем он нужен пакету database/sql в Go?
Каждое новое соединение к PostgreSQL порождает отдельный серверный процесс — слишком дорого открывать его на каждый запрос. Пул держит набор живых соединений и выдаёт их, поэтому запросы переиспользуют их вместо переподключения. database/sql в Go пулится автоматически; настраивайте его через SetMaxOpenConns, SetMaxIdleConns и SetConnMaxLifetime.
Типичные ошибки
- ✗Считать соединение к Postgres дешёвым, поэтому пул якобы не важен
- ✗Открывать новый
*sql.DB(а значит новый пул) на каждый запрос, а не один раз при старте - ✗Путать пул соединений с кэшем запросов или результатов
Уточняющие вопросы
- →Что пойдёт не так, если каждая реплика сервиса откроет свой большой пул?
- →Чем полезен
SetConnMaxLifetimeза балансировщиком или после переключения на резерв?
JuniorТеорияОчень частоЧто такое транзакция в базе данных и что делают COMMIT и ROLLBACK?
Что такое транзакция в базе данных и что делают COMMIT и ROLLBACK?
Транзакция объединяет несколько операторов в одну атомарную единицу — применяются либо все, либо ни один. COMMIT делает её изменения постоянными и видимыми другим транзакциям; ROLLBACK отменяет всё, сделанное с момента BEGIN, оставляя базу такой, будто транзакции и не было.
Типичные ошибки
- ✗Считать, что каждый оператор коммитится сам, поэтому транзакция не может охватывать несколько операторов
- ✗Полагать, что ROLLBACK сохраняет уже прошедшие операторы
- ✗Считать COMMIT просто сбросом на диск, а не тем, что делает изменения устойчивыми и видимыми
Уточняющие вопросы
- →Какие гарантии даёт транзакция кроме «всё или ничего» — что такое свойства ACID?
- →Что станет с изменениями транзакции, если соединение оборвётся до
COMMIT?
MiddleТеорияОчень частоЧто гарантируют четыре свойства ACID?
Что гарантируют четыре свойства ACID?
Атомарность — применяются все операторы транзакции либо ни один. Согласованность — транзакция переводит базу из одного корректного состояния в другое, не нарушая ограничений. Изоляция — параллельные транзакции не видят незавершённую работу друг друга. Устойчивость — как только COMMIT вернулся, изменение переживёт сбой, за счёт WAL.
Типичные ошибки
- ✗Отождествлять Согласованность с Изоляцией — это разные гарантии
- ✗Считать, что Устойчивость требует реплики, а не локального WAL
- ✗Полагать, что Изоляция требует выполнять транзакции строго по одной
Уточняющие вопросы
- →Как PostgreSQL обеспечивает Изоляцию, не выполняя транзакции последовательно?
- →Какое свойство ACID наиболее прямо обеспечивает журнал упреждающей записи?
JuniorТеорияЧастоЧто такое нормализация и какую проблему она решает?
Что такое нормализация и какую проблему она решает?
Нормализация организует реляционную схему в корректные таблицы (1NF, 2NF, 3NF), храня каждый факт ровно один раз. Она устраняет избыточность и аномалии обновления, вставки и удаления, возникающие, когда одно значение продублировано во множестве строк.
Типичные ошибки
- ✗Путать нормализацию со сжатием данных — это дисциплина логического проектирования схемы, а не техника хранения
- ✗Думать, что нормализация про скорость запросов, тогда как она про устранение избыточности и аномалий обновления
- ✗Считать, что высшие нормальные формы всегда объединяют таблицы, тогда как они дробят данные на больше таблиц
Уточняющие вопросы
- →Что такое аномалия обновления, и приведите её конкретный пример?
- →Что требует третья нормальная форма, чего не требует вторая?
MiddleТеорияЧастоКакие существуют уровни изоляции транзакций SQL и чем они различаются?
Какие существуют уровни изоляции транзакций SQL и чем они различаются?
Четыре уровня — Read Uncommitted, Read Committed, Repeatable Read и Serializable. Каждый запрещает всё больше аномалий — грязное чтение, неповторяющееся чтение, затем фантомное чтение. Высшая изоляция стоит больше конкурентности; PostgreSQL по умолчанию использует Read Committed.
Типичные ошибки
- ✗Путать направление —
Serializableэто сильнейший уровень, а не слабейший - ✗Думать, что
Repeatable Readпо стандарту также предотвращает фантомные чтения, тогда как гарантирован лишьSerializable - ✗Полагать, что высшая изоляция бесплатна, тогда как она снижает конкурентность и повышает повторы при ошибках сериализации
Уточняющие вопросы
- →В чём разница между неповторяющимся чтением и фантомным чтением?
- →Как MVCC в PostgreSQL реализует
Repeatable Read, не блокируя читателей?
JuniorТеорияИногдаЧто такое ограничение (constraint) в реляционной базе данных?
Что такое ограничение (constraint) в реляционной базе данных?
Ограничение — это правило, которое база проверяет при каждой записи, чтобы данные оставались корректными; нарушающий оператор отклоняется. Распространённые: NOT NULL, UNIQUE, PRIMARY KEY (уникален и не NULL), FOREIGN KEY (ссылочная целостность) и CHECK (предикат должен выполняться).
Типичные ошибки
- ✗Путать ограничение
UNIQUEсPRIMARY KEY— первичный ключ ещё и не-NULL, и он в таблице один - ✗Считать, что
FOREIGN KEYпроверяется только при вставке, тогда как он ещё и блокирует удаление родительской строки на которую ссылаются - ✗Воспринимать
CHECKкак валидацию на стороне приложения, тогда как сама база отклоняет нарушающую запись
Уточняющие вопросы
- →В чём разница между
PRIMARY KEYи ограничениемUNIQUE? - →Что произойдёт при попытке удалить строку, на которую всё ещё ссылается
FOREIGN KEY?
JuniorТеорияИногдаЧто такое блокировка на уровне строки в БД и для чего она нужна?
Что такое блокировка на уровне строки в БД и для чего она нужна?
Блокировка на уровне строки охраняет одну строку, чтобы её изменяла лишь одна транзакция за раз; остальные, затронувшие эту строку, ждут, пока держатель не зафиксируется или не откатится. Она мельче блокировки таблицы, поэтому несвязанные строки остаются конкурентными, и её берут и UPDATE, и SELECT ... FOR UPDATE.
Типичные ошибки
- ✗Думать, что блокировка строки блокирует чтения — обычный
SELECTзаблокированной строки, как правило, всё равно работает - ✗Путать уровень строки с уровнем таблицы, полагая, что одна заблокированная строка блокирует всю таблицу
- ✗Считать, что блокировки снимаются вручную, а не автоматически при коммите или откате
Уточняющие вопросы
- →Чем
SELECT ... FOR UPDATEотличается от неявной блокировки, которую берёт обычныйUPDATE? - →Что происходит, когда две транзакции пытаются заблокировать одни и те же две строки в обратном порядке?
MiddleТеорияИногдаЗачем и когда вы бы денормализовали реляционную схему?
Зачем и когда вы бы денормализовали реляционную схему?
Денормализация намеренно возвращает избыточность — дублированные столбцы или предвычисленные агрегаты — чтобы сократить join на пути чтения. Её применяют, когда задержка чтения важнее простоты записи, принимая, что каждую дублированную копию надо держать согласованной при записи.
Типичные ошибки
- ✗Думать, что денормализация убирает избыточность, тогда как она намеренно возвращает её обратно
- ✗Игнорировать цену со стороны записи — каждую дублированную копию надо обновлять вместе для согласованности
- ✗Денормализовать преждевременно, не измерив, что join действительно являются узким местом чтения
Уточняющие вопросы
- →Как держать предвычисленный агрегатный столбец согласованным при изменении исходных строк?
- →Почему денормализованный столбец-счётчик становится горячей точкой при конкурентных записях?
MiddleТеорияИногдаЧто такое MVCC и почему в PostgreSQL читатели и писатели не блокируют друг друга?
Что такое MVCC и почему в PostgreSQL читатели и писатели не блокируют друг друга?
Многоверсионное управление конкурентным доступом хранит несколько версий каждой строки, помеченных транзакцией, которая её создала (xmin), и той, что удалила (xmax). Читатель видит версию, действительную для его снимка, поэтому писатель, создающий новую версию, не блокирует читателя и наоборот. Цена — мёртвые версии строк, которые потом убирает VACUUM.
Типичные ошибки
- ✗Считать, что MVCC использует блокировки чтения/записи вместо версий строк
- ✗Полагать, что UPDATE перезаписывает строку на месте, а не пишет новую версию
- ✗Забывать, что старые версии становятся мёртвыми строками, которые убирает VACUUM
Уточняющие вопросы
- →Что такое
xminиxmaxу версии строки и как снимок их использует? - →Как MVCC лежит в основе уровня изоляции
Repeatable Readв PostgreSQL?
MiddleТеорияИногдаЧто такое оптимистичная блокировка и когда она предпочтительнее пессимистичных блокировок строк?
Что такое оптимистичная блокировка и когда она предпочтительнее пессимистичных блокировок строк?
Оптимистичная блокировка не держит блокировку в БД: читается столбец version, а обновление идёт как ... WHERE id = ? AND version = ? с инкрементом версии. Если RowsAffected равен 0, другой писатель опередил — вы повторяете. Она выигрывает у пессимистичного SELECT FOR UPDATE, когда конфликты редки — нет удерживаемых блокировок, выше пропускная способность — но страдает от повторов при высокой конкуренции.
Типичные ошибки
- ✗Забыть проверить
RowsAffectedпосле условного обновления, из-за чего потерянное обновление остаётся незамеченным - ✗Думать, что оптимистичная блокировка берёт блокировку в БД — нет; конфликт обнаруживается в момент записи
- ✗Применять её при тяжёлой конкуренции записи, где постоянные повторы делают её медленнее пессимистичной блокировки
Уточняющие вопросы
- →Как ограничить число повторов, чтобы оптимистичное обновление не зациклилось при конкуренции?
- →Почему инкремент версии и обновление строки должны быть в одном запросе или транзакции?
MiddleТеорияИногдаКакие механизмы масштабируют реляционную базу данных и каковы их компромиссы?
Какие механизмы масштабируют реляционную базу данных и каковы их компромиссы?
Вертикальное масштабирование — это машина побольше: просто, но ограничено потолком и является единой точкой отказа. Read replicas масштабируют чтения, но отстают асинхронно и не помогают записям. Секционирование делит одну таблицу по диапазону, списку или хешу ради pruning. Шардирование распределяет данные по узлам по shard key, масштабируя записи, но усложняя межшардовые join.
Типичные ошибки
- ✗Считать, что read replicas помогают пропускной способности записи — репликация масштабирует только чтения, а реплики отстают
- ✗Смешивать секционирование (деление одной таблицы внутри одной базы) с шардированием (распределение данных по отдельным узлам)
- ✗Забывать, что шардирование усложняет межшардовые join и многошардовые транзакции — плата за масштабируемость записи
Уточняющие вопросы
- →Почему асинхронная репликация означает, что read replica может вернуть устаревшие данные?
- →Как выбор shard key влияет на стоимость межшардовых запросов?
MiddleТеорияИногдаКаковы компромиссы между UUID и serial в качестве первичного ключа?
Каковы компромиссы между UUID и serial в качестве первичного ключа?
Ключ serial/identity — компактное 4/8-байтное целое, чьи последовательные вставки добавляются справа в B-tree, давая хорошую локальность. Случайный UUID — 16 байт, глобально уникален и генерируется на клиенте без обращения к БД, но разбрасывает вставки — предпочитайте UUIDv7.
Типичные ошибки
- ✗Считать случайный
UUIDтаким же дружелюбным к индексу, как последовательные целые, игнорируя разбросанные вставки - ✗Игнорировать, что более широкий 16-байтный ключ раздувает каждый вторичный индекс, а не только первичный ключ
- ✗Полагать, что PostgreSQL физически кластеризует строки по первичному ключу, как MySQL InnoDB
Уточняющие вопросы
- →Как
UUIDv7устраняет проблему локальности вставок случайногоUUIDv4? - →Почему случайный первичный ключ порождает больше WAL и расщеплений страниц, чем
serial?
SeniorТеорияИногдаЧто делает SELECT FOR UPDATE, и как порядок захвата блокировок вызывает взаимоблокировки?
Что делает SELECT FOR UPDATE, и как порядок захвата блокировок вызывает взаимоблокировки?
SELECT ... FOR UPDATE берёт блокировку строки на запись для каждой выбранной строки, поэтому другие транзакции, трогающие эти строки, ждут до коммита держателя. Если две транзакции блокируют строки в противоположном порядке, каждая ждёт строку, удерживаемую другой — взаимоблокировка. Движок обнаруживает цикл и прерывает жертву; согласованный порядок захвата это устраняет.
Типичные ошибки
- ✗Думать, что
SELECT FOR UPDATEберёт разделяемую блокировку чтения — он берёт эксклюзивную блокировку строки на запись - ✗Считать, что приложение должно само обнаруживать взаимоблокировки, тогда как движок находит цикл и прерывает жертву
- ✗Игнорировать, что захват строк в согласованном глобальном порядке между транзакциями и предотвращает взаимоблокировки
Уточняющие вопросы
- →Как
SELECT FOR UPDATE SKIP LOCKEDменяет поведение для паттерна очереди задач? - →Почему долгая транзакция, удерживающая блокировки
FOR UPDATE, повышает риск взаимоблокировки и конкуренции?
MiddleТеорияРедкоДля дедупликации большого потока событий — чем ClickHouse отличается от PostgreSQL?
Для дедупликации большого потока событий — чем ClickHouse отличается от PostgreSQL?
PostgreSQL — построчный OLTP-движок: дедуп делается через UNIQUE-ограничение или INSERT ... ON CONFLICT, транзакционно на каждую строку. ClickHouse — колоночный OLAP-движок для огромных append-only сканов, и у него нет unique-ограничения — дедуп идёт через ReplacingMergeTree, который схлопывает дубликаты лениво при слиянии, либо argMax/GROUP BY на чтении.
Типичные ошибки
- ✗Ожидать в ClickHouse UNIQUE-ограничение, как в PostgreSQL
- ✗Считать, что ReplacingMergeTree дедуплицирует немедленно, а не лениво при слиянии
- ✗Считать колоночное OLAP-хранилище заменой транзакционному построчному дедупу
Уточняющие вопросы
- →Почему ReplacingMergeTree требует
FINALили агрегации, чтобы прочитать полностью дедуплицированный результат? - →Когда построчный транзакционный дедуп в PostgreSQL предпочтительнее ClickHouse?
MiddleТеорияРедкоКакие преимущества имеют документоориентированные базы данных перед реляционными?
Какие преимущества имеют документоориентированные базы данных перед реляционными?
Документные хранилища допускают гибкую схему на запись и встраивают связанные данные внутрь одного документа, поэтому чтению не нужны join. Они также легче шардируются горизонтально. Реляционные базы взамен дают join, многострочные ACID-транзакции, точечную блокировку строки и обеспеченную ссылочную целостность.
Типичные ошибки
- ✗Считать, что у документных баз вовсе нет схемы, тогда как у них гибкая схема на запись
- ✗Полагать, что документные хранилища всегда дают те же многострочные ACID-гарантии, что и реляционная база
- ✗Думать, что встраивание связанных данных требует join, тогда как встраивание именно и убирает join
Уточняющие вопросы
- →Когда встраивание связанных данных в документ становится обузой, а не преимуществом?
- →Почему горизонтальное шардирование обычно легче для документного хранилища, чем для реляционной схемы с join?
SeniorДизайнРедкоНужно выполнить разовый датафикс, обновляющий около 10 миллионов строк в одной таблице, где их примерно 1 миллиард, и которая под непрерывной тяжёлой нагрузкой чтения/записи в проде. Один UPDATE на все 10 млн строк держал бы блокировки строк и создавал бы bloat минутами, тормозил бы autovacuum, раздувал бы WAL и рисковал бы отставанием реплик плюс очень долгим откатом при сбое на середине. Опишите, как вы спроектируете и проведёте этот датафикс безопасно: как разбиваете работу на батчи, как выбираете, какие строки трогать, как избегаете долгих блокировок и отставания реплик, как делаете задачу возобновляемой и идемпотентной при прерывании и как соразмеряете её с живым трафиком.
Нужно выполнить разовый датафикс, обновляющий около 10 миллионов строк в одной таблице, где их примерно 1 миллиард, и которая под непрерывной тяжёлой нагрузкой чтения/записи в проде. Один UPDATE на все 10 млн строк держал бы блокировки строк и создавал бы bloat минутами, тормозил бы autovacuum, раздувал бы WAL и рисковал бы отставанием реплик плюс очень долгим откатом при сбое на середине. Опишите, как вы спроектируете и проведёте этот датафикс безопасно: как разбиваете работу на батчи, как выбираете, какие строки трогать, как избегаете долгих блокировок и отставания реплик, как делаете задачу возобновляемой и идемпотентной при прерывании и как соразмеряете её с живым трафиком.
Никаких одиночных гигантских UPDATE. Батчите по диапазонам первичного ключа (несколько тысяч строк на транзакцию), каждый батч — короткая транзакция, поэтому блокировки держатся недолго, а autovacuum и реплики успевают. Сделайте задачу возобновляемой через хранение последнего обработанного ключа и идемпотентной, чтобы повтор пропускал уже исправленные строки (WHERE, который больше не матчит исправленную строку). Тормозите между батчами, следите за отставанием реплик и сбавляйте под нагрузкой, запускайте вне пика.
Типичные ошибки
- ✗Делать это одной большой транзакцией, держа блокировки и раздувая таблицу минутами
- ✗Батчить через LIMIT/OFFSET, что пересканирует пропущенные строки и плывёт при конкурентных записях
- ✗Забыть про идемпотентность, из-за чего повтор после сбоя применяет фикс дважды
Уточняющие вопросы
- →Почему keyset-пагинация (
WHERE id > :last ORDER BY id LIMIT n) лучшеLIMIT/OFFSETдля батчей? - →Как мониторить отставание реплик и реагировать на него во время выполнения задачи?
SeniorТеорияРедкоЗачем внешний пулер PgBouncer, если у приложения уже есть пул database/sql?
Зачем внешний пулер PgBouncer, если у приложения уже есть пул database/sql?
Пул на стороне приложения существует на каждый экземпляр: запустите сотни реплик, и каждая откроет свой пул, так что суммарное число соединений намного превысит то, что Postgres способен обслужить (каждое соединение — отдельный серверный процесс). PgBouncer стоит впереди и мультиплексирует множество клиентских соединений в небольшой серверный пул — в режиме transaction серверное соединение занимается лишь на время одной транзакции.
Типичные ошибки
- ✗Считать, что пул приложения ограничивает суммарные соединения по всем экземплярам
- ✗Полагать, что PgBouncer добавляет CPU или ёмкость записи, а не просто делит соединения
- ✗Путать режим
transactionс режимомsession, который держит серверное соединение всю сессию
Уточняющие вопросы
- →Что ломается в режиме
transaction, если код опирается на состояние сессии вроде prepared statements? - →Как поделить фиксированный бюджет соединений Postgres между N репликами сервиса?
SeniorТеорияРедкоЧто такое виртуальное шардирование (virtual sharding)?
Что такое виртуальное шардирование (virtual sharding)?
Виртуальное шардирование отображает ключи не прямо на физические узлы, а на большое фиксированное число виртуальных шардов (бакетов); каждый узел владеет набором этих шардов. Добавление или удаление узла перемещает целые виртуальные шарды, а не перехеширует каждый ключ, поэтому ребалансировка дёшева и данные распределены равномерно. Оно отделяет логическое разбиение от физической топологии.
Типичные ошибки
- ✗Думать, что виртуальные шарды — это сами отдельные серверы, тогда как это логические бакеты, которыми узлы владеют наборами
- ✗Считать, что добавление узла перехеширует каждый ключ — перемещаются лишь целые виртуальные шарды, поэтому ребалансировка дёшева
- ✗Полагать, что число шардов равно числу узлов, тогда как виртуальное шардирование намеренно фиксирует гораздо большее число шардов
Уточняющие вопросы
- →Как это связано с consistent hashing и виртуальными узлами?
- →Почему фиксация большого числа шардов сохраняет равномерное распределение данных при добавлении узлов?
