Введение в базы данных и СУБД

Почти любая информационная система — интернет-магазин, банк, система учёта успеваемости, журнал событий веб-сервера — в конечном счёте решает одну задачу: хранить данные долго, надёжно и так, чтобы ими могли одновременно пользоваться многие программы и люди. На первом курсе данные программы на Python жили в переменных и, в лучшем случае, сохранялись в файл CSV или JSON. Для учебной задачи этого достаточно, но стоит представить, что с тем же файлом одновременно работают сотни пользователей, что питание пропадает в середине записи, что формат файла нужно изменить, не переписывая десяток программ, — и становится ясно, почему возникла отдельная область информатики и отдельный класс программ: системы управления базами данных.

Настоящая тема открывает курс и выполняет две задачи. Во-первых, она вводит основные понятия — данные, база данных, модель данных, схема, система управления базой данных — и объясняет, какие именно проблемы СУБД снимает с прикладного программиста. Во-вторых, она даёт общую картину: как развивались модели данных от иерархической до реляционной и далее, как устроена СУБД изнутри и как конкретно устроен PostgreSQL — СУБД, на которой построен весь курс. Подробности каждого механизма излагаются в последующих темах, а здесь важно понять, какие механизмы вообще существуют и зачем они нужны.

Курс состоит из четырёх разделов. Раздел I «Реляционная модель и язык SQL» (темы 1–6) вводит реляционную модель и ограничения целостности, реляционную алгебру и язык запросов SQL — от запросов к одной таблице до соединений, подзапросов, агрегирования, оконных функций, изменения данных, рекурсивных запросов и представлений. Раздел II «Проектирование баз данных» (темы 7–9) учит строить схему: концептуальная модель «сущность — связь», функциональные зависимости и нормализация, физическое проектирование — типы данных, хранение, полуструктурированные данные. Раздел III «Внутреннее устройство и производительность» (темы 10–13) раскрывает то, что скрыто за декларативным SQL: индексы, выполнение и оптимизацию запросов, транзакции и управление параллельным доступом, журнал, восстановление и резервное копирование. Раздел IV «Базы данных в приложениях» (темы 14–16) посвящён серверному программированию, работе с базой из приложения на Python и безопасности, а также масштабированию, NoSQL и аналитическим системам. Все примеры и лабораторные работы строятся на одной учебной базе — книжном интернет-магазине «Книжный узел», которая устанавливается в лабораторной работе 1.

Изложение темы построено так. Первый раздел определяет базовые понятия и перечисляет свойства, отличающие базу данных от набора файлов. Второй прослеживает историю моделей данных и языка SQL. Третий описывает трёхуровневую архитектуру ANSI/SPARC и понятие независимости данных — с демонстрацией на учебной базе. Четвёртый разбирает компоненты СУБД и путь запроса через них. Пятый посвящён архитектуре PostgreSQL: процессам сервера, кластеру, базам, схемам и ролям, а также встраиваемым СУБД. Шестой кратко характеризует профессиональные роли и рынок СУБД.

Данные, базы данных и системы управления ими

Данные, информация и база данных

Под данными (англ. data) понимают зарегистрированные факты, представленные в форме, пригодной для хранения, передачи и обработки: строка «Пикник на обочине», число 520.00, дата 2021-06-24. Информация (англ. information) — это данные вместе с их смыслом, то есть с интерпретацией: «книга „Пикник на обочине“ стоит 520 рублей». Одни и те же данные без описания смысла бесполезны: число 520 может быть ценой, числом страниц или кодом склада. Поэтому база данных хранит не только сами значения, но и их описание — какие бывают объекты, из каких свойств они состоят, какие правила должны выполняться.

База данных (англ. database) — это совместно используемый набор логически связанных данных и их описания, предназначенный для удовлетворения информационных потребностей организации ². В этом определении важно каждое слово. «Совместно используемый» — с базой работают многие программы и пользователи, а не одна программа. «Логически связанных» — данные отражают одну предметную область (англ. universe of discourse): книги, авторы, покупатели и заказы магазина связаны между собой. «И их описания» — база данных самоописательна: описание структуры, называемое схемой (англ. schema) или метаданными, хранится в самой базе, в системном каталоге (англ. system catalog), и доступно для запросов наравне с данными.

Система управления базами данных, СУБД (англ. Database Management System, DBMS), — программная система, которая позволяет определять структуру базы, создавать, читать и изменять данные и при этом контролирует доступ к ним, обеспечивает целостность, параллельную работу и восстановление после сбоев ²³. Совокупность базы данных, СУБД и прикладных программ, работающих с ними, называют системой баз данных (англ. database system). В обиходе слова «база данных» нередко употребляют и в смысле «СУБД» («поставим базу данных PostgreSQL»); в курсе эти понятия различаются: PostgreSQL — СУБД, а bookstore — база данных, которой он управляет.

Каждая СУБД реализует определённую модель данных (англ. data model) — набор понятий для описания структуры данных, операций над ними и ограничений целостности. Реляционная модель описывает данные как отношения (таблицы) и операции над ними; иерархическая — как деревья записей; документная — как вложенные документы. Описание конкретной базы в терминах модели — это её схема, а сами данные в определённый момент времени — состояние, или экземпляр (англ. instance), базы. Схема меняется редко и осознанно, состояние — при каждой операции записи. Разница между ними принципиальна: утверждение «каждая книга относится к одной рубрике» — свойство схемы, а «сейчас в базе 2010 книг» — свойство состояния. К этому различию курс будет возвращаться постоянно, начиная с темы 2, где выяснится, что ключ — свойство схемы, а не случайно уникальных данных.

Почему не набор файлов

Простейший способ хранить данные магазина — файлы: books.csv, customers.csv, orders.csv и программа на Python, которая их читает и пишет. Именно так работали информационные системы 1960-х годов, и недостатки такого подхода хорошо известны ²³. Рассмотрим их на примере.

Первая проблема — зависимость программ от формата данных. Программа, которая читает books.csv, знает, что цена — пятый столбец, разделитель — запятая, кодировка — UTF-8. Если добавить в файл столбец «год издания» перед ценой, все программы, читающие файл, придётся исправить. В СУБД программы обращаются к данным по именам, а не по положению байтов в файле, и изменение структуры хранения скрыто от них. Это свойство называется независимостью данных и подробно разбирается в третьем разделе.

Вторая проблема — дублирование и противоречивость. Если имя покупателя хранится и в файле покупателей, и в каждой строке файла заказов, то при смене фамилии одни копии исправят, а другие забудут. Избыточность (англ. redundancy) порождает несогласованность (англ. inconsistency). Устранение избыточности — предмет нормализации (тема 8).

Третья проблема — целостность. Правила «цена не может быть отрицательной», «электронный адрес покупателя уникален», «заказ ссылается на существующего покупателя» в файловой системе проверяются (или не проверяются) каждой программой отдельно. Достаточно одной программы с ошибкой — и в файле появится заказ несуществующего покупателя. СУБД хранит ограничения целостности (англ. integrity constraints) в схеме и проверяет их при каждом изменении, кто бы его ни выполнял. В учебной базе это выглядит так:

INSERT INTO order_item (order_id, book_id, quantity, price) VALUES (1, 999999, 1, 100.00);
INSERT INTO customer (email, full_name) VALUES ('user1@example.com', 'Двойник');
UPDATE stock SET quantity = quantity - 100 WHERE warehouse_id = 1 AND book_id = 1;

ERROR:  insert or update on table "order_item" violates foreign key constraint "order_item_book_id_fkey"
DETAIL:  Key (book_id)=(999999) is not present in table "book".
ERROR:  duplicate key value violates unique constraint "customer_email_key"
DETAIL:  Key (email)=(user1@example.com) already exists.
ERROR:  new row for relation "stock" violates check constraint "stock_quantity_check"
DETAIL:  Failing row contains (1, 1, -89).

Все три изменения отвергнуты сервером: позиция заказа ссылается на несуществующую книгу, адрес user1@example.com уже занят, остаток книги 1 на складе 1 (он равен 11) стал бы отрицательным. Ни одна прикладная программа не обязана помнить об этих правилах — их соблюдение гарантирует СУБД. Ограничения целостности — тема 2.

Четвёртая проблема — параллельный доступ. Две программы одновременно читают остаток книги (11 экземпляров), каждая продаёт один экземпляр и записывает 10. Продано два экземпляра, а остаток уменьшился на один — классическое потерянное обновление (англ. lost update). В файловой системе от этого защищает только ручная блокировка файла целиком, что сводит параллельную работу на нет. СУБД предоставляет транзакции (англ. transactions) — группы операций, которые выполняются как единое целое и изолированы от других транзакций; механизмы изоляции — тема 12.

Пятая проблема — сбои. Если программа записала половину файла и процесс был прерван, файл повреждён. Если в заказе успели записать позиции, но не успели записать оплату, данные противоречивы. СУБД гарантирует, что зафиксированная транзакция сохранится и после сбоя, а незафиксированная не оставит следов; для этого служит журнал упреждающей записи (тема 13). Совокупность гарантий атомарности, согласованности, изоляции и долговечности транзакций называют ACID (англ. Atomicity, Consistency, Isolation, Durability) ².

Шестая проблема — безопасность. Файл либо доступен процессу целиком, либо недоступен. СУБД разграничивает права на уровне таблиц, столбцов и даже строк: аналитик видит обезличенную статистику продаж, но не адреса покупателей (тема 15).

Наконец, седьмая проблема — доступ к данным. Чтобы ответить на вопрос «какие книги рубрики „Базы данных“ дороже 2500 рублей», с файлами нужно написать программу: открыть файл, разобрать строки, отфильтровать, отсортировать. В СУБД достаточно сформулировать вопрос на декларативном языке запросов (англ. query language) — описать, что нужно получить, а не как:

SELECT title, price
FROM book
WHERE category_id = 13 AND price > 2500
ORDER BY price DESC, title
LIMIT 3;

        title        |  price  
---------------------+---------
 Практика чисел 962  | 2979.99
 Путь Python 1363    | 2979.99
 Теория Python 291   | 2959.99
(3 rows)

Как именно найти эти строки — прочитать таблицу целиком или воспользоваться индексом, отсортировать все строки или только три лучших, — решает сама СУБД. Второй ключ сортировки title добавлен не случайно: у двух книг одинаковая цена 2979.99, и без него порядок этих строк, а при меньшем LIMIT и сам состав результата, не определён — СУБД вправе вернуть их в любом порядке (тема 3). Это разделение ответственности — центральная идея реляционного подхода ¹.

Когда СУБД не нужна

Перечисленные достоинства не бесплатны. СУБД — сложная программа, её нужно установить, настроить, администрировать; доступ к данным через сервер медленнее, чем чтение локального файла. Если данные пишет одна программа, читают редко, а их потеря не страшна (конфигурационный файл, журнал отладки, результаты одного научного расчёта), обычного файла может быть достаточно. Промежуточный вариант — встраиваемая СУБД вроде SQLite, которая работает внутри процесса программы и хранит базу в одном файле, но даёт язык SQL, транзакции и ограничения целостности; к ней вернёмся в пятом разделе. Выбор хранилища — инженерное решение, и умение обосновать его — одна из целей курса.

История моделей данных и языка SQL

Файловые системы и навигационные модели

Первые коммерческие системы обработки данных 1950–1960-х годов строились на файлах с последовательным доступом на магнитных лентах: каждая программа сама определяла формат записей, а обработка выполнялась пакетами. Появление дисков с прямым доступом позволило хранить связи между записями явно — указателями. Так возникли навигационные модели данных: программа получает запись и «перемещается» по указателям к связанным записям, то есть сама прокладывает путь доступа.

Иерархическая модель представляет данные как лес деревьев: у каждой записи, кроме корневой, ровно один родитель. Её классическая реализация — IMS (англ. Information Management System) компании IBM, созданная в 1966–1968 годах совместно с North American Rockwell для учёта деталей в программе Apollo и выпущенная как коммерческий продукт в 1969 году ²³. Иерархия хорошо описывает естественно древовидные данные (изделие — узлы — детали), но плохо — связи «многие ко многим»: книга с двумя авторами должна либо дублироваться под каждым автором, либо связываться искусственными «логическими» указателями.

Сетевая модель снимает ограничение на одного родителя: записи объединяются в наборы (англ. sets) «владелец — члены», и запись может быть членом нескольких наборов. Её прообраз — система IDS (англ. Integrated Data Store), созданная Чарльзом Бахманом в General Electric в начале 1960-х годов, а стандарт сформулирован рабочей группой по базам данных DBTG (англ. Data Base Task Group) комитета CODASYL в отчётах 1969 и 1971 годов ². В 1973 году Бахман получил премию Тьюринга; его тьюринговская лекция называлась «Программист как навигатор» и описывала программиста, путешествующего по базе данных от записи к записи ¹¹.

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

Реляционная модель Кодда и первые реляционные СУБД

В июне 1970 года сотрудник исследовательской лаборатории IBM в Сан-Хосе Эдгар Кодд опубликовал в журнале Communications of the ACM (т. 13, № 6) статью «A Relational Model of Data for Large Shared Data Banks» ¹. Кодд предложил представлять все данные единообразно — как отношения в математическом смысле, то есть множества кортежей, — а связи между ними выражать не указателями, а значениями: заказ хранит номер покупателя, и связь «заказ — покупатель» восстанавливается сравнением значений. Первая же фраза статьи формулирует цель: пользователи больших банков данных должны быть защищены от знания того, как данные организованы в машине ¹. Над отношениями определялись операции, позднее оформленные как реляционная алгебра (тема 3), результат которых — снова отношения; запрос описывает искомый результат, а способ его получения выбирает система.

Идея встретила скепсис: казалось, что система, сама выбирающая путь доступа, не может быть столь же эффективной, как ручная навигация. Ответом стали два исследовательских проекта середины 1970-х годов. Проект System R в той же лаборатории IBM (1974–1979) построил полноценную реляционную СУБД с языком SEQUEL, позднее переименованным в SQL, оптимизатором на основе стоимости, транзакциями и восстановлением ⁵. Язык SEQUEL (англ. Structured English Query Language) был описан Дональдом Чемберлином и Рэймондом Бойсом в 1974 году ⁷; оптимизатор System R, описанный Патрисией Селинджер с соавторами в 1979 году, до сих пор служит образцом для оптимизаторов реляционных СУБД (тема 11) ⁴. Параллельно в Калифорнийском университете в Беркли Майкл Стоунбрейкер и Юджин Вонг с 1973 года разрабатывали СУБД Ingres с языком QUEL ⁶. Оба проекта доказали, что реляционная СУБД может быть практичной.

Коммерческие системы последовали быстро. В 1979 году компания Relational Software (впоследствии Oracle) выпустила Oracle V2 — первую коммерчески доступную СУБД с языком SQL; IBM выпустила SQL/DS в 1981 году и DB2 в 1983 году; на основе проекта Ingres была создана одноимённая коммерческая СУБД, а выходцы из него участвовали в создании Sybase, от которой ведёт происхождение Microsoft SQL Server ². В 1981 году Кодд получил премию Тьюринга. В 1986 году Стоунбрейкер начал в Беркли проект POSTGRES («после Ingres»), нацеленный на расширяемость системы типов и правил; в 1994–1995 годах Эндрю Ю и Джолли Чен заменили язык запросов POSTQUEL на SQL (система Postgres95), а в 1996 году проект получил имя PostgreSQL и стал развиваться сообществом ⁹.

SQL и его стандарты

SQL (произносится «эс-кью-эль» или, по старому имени, «сиквел») стал общим языком реляционных СУБД благодаря стандартизации. Стандарт ведётся сначала ANSI, затем совместно ISO и IEC под номером ISO/IEC 9075 и состоит из нескольких частей: Framework, Foundation, Call-Level Interface, Persistent Stored Modules и др. ¹³. Основные редакции перечислены в таблице.

Стандарт велик, и ни одна СУБД не реализует его полностью; кроме того, у каждой СУБД есть собственные расширения. PostgreSQL стремится следовать стандарту и в документации указывает, какие возможности соответствуют ему, а какие являются расширениями; в курсе отклонения от стандарта оговариваются явно.

Объектно-реляционные расширения, NoSQL и NewSQL

В 1980–1990-х годах популярность объектно-ориентированного программирования породила объектные СУБД, хранившие объекты языка программирования непосредственно. Широкого распространения они не получили; вместо этого реляционные СУБД вобрали часть их идей — пользовательские типы, наследование таблиц, массивы, расширяемые индексы. Такие системы называют объектно-реляционными; PostgreSQL с его расширяемой системой типов, восходящей к проекту POSTGRES, — характерный пример ⁹.

В 2000-х годах крупные интернет-компании столкнулись с объёмами данных и нагрузкой, для которых один сервер был недостаточен. Распределённые хранилища Google Bigtable (2006) и Amazon Dynamo (2007) отказались от части реляционных гарантий — соединений, транзакций над несколькими записями, строгой согласованности — ради горизонтального масштабирования и доступности. С 2009 года подобные системы объединяют под условным названием NoSQL: хранилища «ключ — значение» (Redis), документные (MongoDB), с широкими столбцами (Cassandra), графовые (Neo4j) ¹². Позднее появились системы, которые называют NewSQL или распределёнными SQL-СУБД (Google Spanner, CockroachDB, YugabyteDB): они сохраняют SQL и транзакции, но распределяют данные по многим узлам. Эти модели и компромиссы между ними — тема 16. Примечательно, что реляционные СУБД, в свою очередь, переняли сильные стороны NoSQL: в PostgreSQL есть тип jsonb для документов (тема 9), а реляционная модель остаётся основной для систем учёта, где важны целостность и транзакции.

Временную шкалу ключевых событий показывает рисунок ниже.

Трёхуровневая архитектура и независимость данных

Уровни ANSI/SPARC

В 1975 году исследовательская группа по СУБД комитета ANSI/X3/SPARC опубликовала промежуточный отчёт, а в 1977–1978 годах — итоговый, в котором предложила разделять описание базы данных на три уровня ⁸. Эта архитектура не стала стандартом в строгом смысле, но её понятия — уровни и независимость данных — общеприняты ²³.

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

Концептуальный уровень (англ. conceptual level) — единое логическое описание всей базы: какие есть сущности, их атрибуты, связи и ограничения целостности, без подробностей хранения. Для реляционной СУБД это набор таблиц со столбцами, типами, ключами и ограничениями — то, что записано в schema.sql учебной базы. Концептуальная схема одна. В современной терминологии её чаще называют логической схемой.

Внутренний уровень (англ. internal level) — физическое представление данных: в каких файлах и страницах хранятся строки, в каком формате, какие построены индексы, как данные сжаты и размещены на дисках. Внутренняя схема тоже одна.

Между уровнями существуют отображения (англ. mappings): внешне-концептуальное определяет, как строки внешнего представления получаются из таблиц концептуальной схемы, концептуально-внутреннее — как таблицы хранятся физически. Именно эти отображения СУБД берёт на себя. Схема на рисунке ниже показывает уровни и отображения на примере «Книжного узла».

Физическая независимость данных

Физическая независимость данных (англ. physical data independence) — возможность изменять внутреннюю схему, не изменяя концептуальную схему и прикладные программы. Покажем её на учебной базе. Найдём книгу по названию и посмотрим план выполнения — способ, которым СУБД собирается выполнять запрос (команда EXPLAIN подробно разбирается в теме 11):

EXPLAIN SELECT book_id, title, price FROM book WHERE title = 'Пикник на обочине';

                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on book  (cost=0.00..88.12 rows=1 width=46)
   Filter: (title = 'Пикник на обочине'::text)
(2 rows)

План Seq Scan означает последовательное чтение всей таблицы с проверкой условия для каждой строки. Создадим индекс — вспомогательную структуру для быстрого поиска (тема 10) — и повторим тот же запрос:

CREATE INDEX book_title_idx ON book (title);
EXPLAIN SELECT book_id, title, price FROM book WHERE title = 'Пикник на обочине';

                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Index Scan using book_title_idx on book  (cost=0.28..8.29 rows=1 width=46)
   Index Cond: (title = 'Пикник на обочине'::text)
(2 rows)

Текст запроса не изменился, результат (книга 4, цена 520.00) тот же, но способ выполнения другой: теперь СУБД ищет строку по индексу, и оценка стоимости уменьшилась примерно в десять раз. Приложение, выполняющее этот запрос, ничего не нужно менять — оно даже не знает о существовании индекса. Точно так же без изменения программ можно перенести таблицу в другое табличное пространство на другом диске, изменить параметры хранения или сжатия. Индекс в эксперименте удаляется командой DROP INDEX book_title_idx;, поскольку вторичные индексы в учебной базе намеренно отсутствуют до темы 10.

Логическая независимость данных

Логическая независимость данных (англ. logical data independence) — возможность изменять концептуальную схему, не изменяя внешних схем и программ, которые через них работают. Её добиться труднее, и реализуется она в основном механизмом представлений (англ. views) — именованных запросов, которые ведут себя как таблицы (тема 6). Пусть программа прайс-листа работает не с таблицей book непосредственно, а с представлением:

BEGIN;
CREATE VIEW price_list AS
    SELECT isbn, title, price FROM book WHERE price > 0;
SELECT * FROM price_list WHERE isbn = '9785170878239';

     isbn      |       title       | price  
---------------+-------------------+--------
 9785170878239 | Пикник на обочине | 520.00
(1 row)

Теперь изменим концептуальную схему: переименуем столбец цены и добавим новый столбец скидки. Запрос через представление продолжает работать и возвращает тот же результат, а СУБД сама перестроила отображение — это видно по тексту представления, который возвращает функция pg_get_viewdef:

ALTER TABLE book RENAME COLUMN price TO list_price;
ALTER TABLE book ADD COLUMN discount numeric(4, 2) NOT NULL DEFAULT 0;
SELECT * FROM price_list WHERE isbn = '9785170878239';
SELECT pg_get_viewdef('price_list');
ROLLBACK;

     isbn      |       title       | price  
---------------+-------------------+--------
 9785170878239 | Пикник на обочине | 520.00
(1 row)

            pg_get_viewdef            
--------------------------------------
  SELECT isbn,                       +
     title,                          +
     list_price AS price             +
    FROM book                        +
   WHERE (list_price > (0)::numeric);
(1 row)

Внешняя схема (представление price_list со столбцом price) осталась прежней, изменилось лишь отображение. Весь эксперимент выполнен внутри транзакции и отменён командой ROLLBACK — в PostgreSQL изменения схемы транзакционны, и это позволяет безопасно экспериментировать с учебной базой. Логическая независимость не абсолютна: если удалить столбец, используемый представлением, или разделить таблицу на две, представление придётся переписать, а для изменения данных через сложное представление нужны дополнительные механизмы (тема 6 и тема 14). Тем не менее разделение «что хранится» и «что видит приложение» — основной инструмент эволюции схемы.

Где проходят границы на практике

Трёхуровневая архитектура — идеализация. В реальных системах приложения часто обращаются прямо к таблицам концептуальной схемы, а не к представлениям, и тогда логическая независимость отсутствует: переименование столбца ломает приложение. С другой стороны, физическая независимость в реляционных СУБД выполняется очень полно: SQL вообще не содержит средств сослаться на файл, страницу или указатель. Это прямое следствие реляционной модели, где доступ к данным выполняется только по значениям ¹. Цена такой независимости — необходимость оптимизатора: раз программа не указывает путь доступа, его должна выбирать СУБД, и выбирать хорошо. Устройство оптимизатора — тема 11.

Компоненты СУБД и путь запроса

Обзор архитектуры

Внутреннее устройство СУБД традиционно описывают как набор взаимодействующих компонентов ⁴². Рисунок ниже показывает их на пути одного запроса от клиента до диска.

Менеджер соединений (англ. connection manager) принимает подключения клиентов, проверяет их подлинность и создаёт для каждого клиента контекст выполнения — в PostgreSQL отдельный процесс (следующий раздел).

Обработчик запросов (англ. query processor) превращает текст SQL в результат. Анализатор (англ. parser) проверяет синтаксис и строит дерево разбора; на этом этапе по системному каталогу проверяется, что таблицы и столбцы существуют и что у пользователя есть права. Модуль переписывания (англ. rewriter) раскрывает представления, подставляя их определения. Оптимизатор, или планировщик (англ. optimizer, planner), перебирает возможные способы выполнения — порядок соединений, методы доступа к таблицам, алгоритмы соединения и сортировки — оценивает стоимость каждого по статистике о данных и выбирает самый дешёвый план (англ. execution plan). Исполнитель (англ. executor) выполняет план, обычно по конвейерной схеме: каждый узел плана по запросу выдаёт следующую строку своему родителю.

Менеджер хранения (англ. storage manager) включает методы доступа (англ. access methods) — способы читать и изменять таблицы и индексы — и менеджер буферов (англ. buffer manager), который держит в оперативной памяти копии страниц с диска. Данные на диске разбиты на страницы фиксированного размера (в PostgreSQL 8 КиБ); любое чтение или изменение строки выполняется над страницей в буферном кеше, а запись изменённых страниц на диск откладывается.

Менеджер транзакций (англ. transaction manager) обеспечивает изоляцию параллельных транзакций — блокировками, многоверсионностью или их сочетанием — и вместе с менеджером журнала (англ. log manager) гарантирует атомарность и долговечность: прежде чем изменённая страница попадёт на диск, на диск должна попасть запись журнала, описывающая изменение. Это правило упреждающей записи (англ. write-ahead logging, WAL) позволяет после сбоя восстановить зафиксированные изменения (тема 13).

Наконец, системный каталог (англ. catalog) хранит метаданные — описания таблиц, столбцов, типов, ограничений, индексов, прав, а также статистику для оптимизатора. В реляционной СУБД каталог сам состоит из таблиц, и к нему можно обращаться обычными запросами — это одно из правил Кодда, о которых пойдёт речь в теме 2.

План выполнения: что выбрал оптимизатор

Увидеть результат работы оптимизатора позволяет команда EXPLAIN. Запрос о трёх самых дорогих книгах рубрики «Базы данных» (рубрика 13) выполняется таким планом:

EXPLAIN SELECT title, price FROM book WHERE category_id = 13 ORDER BY price DESC LIMIT 3;

                             QUERY PLAN                             
--------------------------------------------------------------------
 Limit  (cost=89.60..89.61 rows=3 width=42)
   ->  Sort  (cost=89.60..89.88 rows=114 width=42)
         Sort Key: price DESC
         ->  Seq Scan on book  (cost=0.00..88.12 rows=114 width=42)
               Filter: (category_id = 13)
(5 rows)

План читается снизу вверх, от листьев к корню. Узел Seq Scan последовательно читает таблицу book и пропускает дальше только строки рубрики 13; оптимизатор ожидает около 114 таких строк, исходя из статистики (в рубрике 13 на самом деле 114 книг, оценка точна). Узел Sort сортирует их по убыванию цены, узел Limit отдаёт три первые. Числа cost — оценка стоимости в условных единицах (первое — до выдачи первой строки, второе — до выдачи всех), rows — ожидаемое число строк, width — средняя ширина строки в байтах. Детальное чтение планов — тема 11; сейчас важно, что план — это программа, которую СУБД построила по декларативному запросу сама.

Менеджер буферов в действии

Работу менеджера буферов можно наблюдать с помощью EXPLAIN (ANALYZE, BUFFERS): запрос действительно выполняется, а в плане показывается, сколько страниц было найдено в буферном кеше (hit) и сколько пришлось прочитать с диска или из кеша операционной системы (read). Выполним запрос сразу после перезапуска сервера, когда буферный кеш пуст, а затем повторим его:

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF) SELECT count(*) FROM book WHERE price > 2500;

                             QUERY PLAN                             
--------------------------------------------------------------------
 Aggregate (actual time=1.219..1.220 rows=1 loops=1)
   Buffers: shared read=63
   ->  Seq Scan on book (actual time=0.039..1.196 rows=344 loops=1)
         Filter: (price > '2500'::numeric)
         Rows Removed by Filter: 1666
         Buffers: shared read=63
 Planning:
   Buffers: shared hit=6 read=2
 Planning Time: 0.189 ms
 Execution Time: 1.243 ms
(10 rows)

При повторном выполнении того же запроса:

                             QUERY PLAN                             
--------------------------------------------------------------------
 Aggregate (actual time=0.382..0.382 rows=1 loops=1)
   Buffers: shared hit=63
   ->  Seq Scan on book (actual time=0.012..0.364 rows=344 loops=1)
         Filter: (price > '2500'::numeric)
         Rows Removed by Filter: 1666
         Buffers: shared hit=63
 Planning Time: 0.098 ms
 Execution Time: 0.401 ms
(8 rows)

Таблица book занимает 63 страницы по 8 КиБ. В первый раз все 63 страницы были прочитаны (read=63), во второй — найдены в общем буферном кеше (hit=63), и запрос выполнился втрое быстрее. Абсолютные времена у каждого читателя будут своими; важно соотношение. Размер буферного кеша задаётся параметром shared_buffers (по умолчанию 128 МиБ) ¹⁰. Буферный кеш — причина, по которой СУБД не может просто записывать каждое изменение сразу на диск и нуждается в журнале; связь кеша и журнала разбирается в теме 13.

Транзакции и журнал: обещание долговечности

Компоненты, обеспечивающие транзакции, — самые сложные в СУБД, и им посвящены темы 12 и 13. Здесь достаточно сформулировать, что они обещают. Транзакция начинается командой BEGIN и завершается COMMIT (зафиксировать) или ROLLBACK (отменить); выполненный выше эксперимент с представлением был отменён именно ROLLBACK, и в базе не осталось ни представления, ни переименованного столбца. Если сервер аварийно остановится после COMMIT, изменения сохранятся; если до — не останется ни одного из них. Если две транзакции работают с одними данными одновременно, каждая видит согласованное состояние базы. Все эти гарантии СУБД даёт любой программе без единой строчки кода с её стороны — в этом и состоит смысл выделения управления данными в отдельную систему.

Архитектура PostgreSQL

Клиент-серверная модель и процессы сервера

PostgreSQL построен по клиент-серверной архитектуре (англ. client-server architecture): сервер — отдельная программа, управляющая файлами базы, а клиенты (psql, приложение на Python с драйвером psycopg, графическая среда pgAdmin) подключаются к нему по сети или через локальный сокет и обмениваются сообщениями по протоколу PostgreSQL ¹⁰. Клиент никогда не читает файлы базы сам; все данные проходят через сервер, и это позволяет серверу контролировать целостность, права и параллельный доступ.

Сервер PostgreSQL использует модель «процесс на соединение» (англ. process-per-connection). Главный процесс, исторически называемый postmaster, при запуске выделяет общую память (буферный кеш, таблицы блокировок, буферы журнала), запускает фоновые процессы и ждёт подключений. Для каждого нового клиента он порождает отдельный обслуживающий процесс (англ. backend), который аутентифицирует клиента и затем выполняет все его запросы ¹⁰⁹. Обслуживающие процессы взаимодействуют друг с другом только через общую память. Схема на рисунке ниже показывает эту архитектуру.

Процессы сервера видны средствами операционной системы. Пока один клиент выполняет долгий запрос, команда ps показывает потомков главного процесса:

ps -o pid,ppid,cmd --ppid $(head -1 $PGDATA/postmaster.pid)

  PID  PPID CMD
 5404  5264 postgres: checkpointer 
 5405  5264 postgres: background writer 
 5601  5264 postgres: walwriter 
 5602  5264 postgres: autovacuum launcher 
 5603  5264 postgres: logical replication launcher 
16801  5264 postgres: postgres bookstore [local] SELECT

Первая строка файла postmaster.pid в каталоге данных содержит номер главного процесса (здесь 5264). Фоновые процессы выполняют служебную работу: checkpointer выполняет контрольные точки, background writer заблаговременно записывает «грязные» страницы буферного кеша на диск, walwriter сбрасывает буферы журнала, autovacuum launcher запускает очистку старых версий строк (тема 12), logical replication launcher обслуживает логическую репликацию (тема 13). Последняя строка — обслуживающий процесс клиента: пользователь postgres, база bookstore, подключение через локальный сокет ([local]), выполняется SELECT. В Ubuntu заголовки процессов дополнительно содержат имя кластера, например postgres: 16/main: checkpointer.

Ту же картину изнутри показывает системное представление pg_stat_activity:

SELECT pid, backend_type, datname, usename, state, left(query, 30) AS query
FROM pg_stat_activity ORDER BY pid;

  pid  |         backend_type         |  datname  | usename  | state  |             query              
-------+------------------------------+-----------+----------+--------+--------------------------------
  5404 | checkpointer                 |           |          |        | 
  5405 | background writer            |           |          |        | 
  5601 | walwriter                    |           |          |        | 
  5602 | autovacuum launcher          |           |          |        | 
  5603 | logical replication launcher |           | postgres |        | 
 16801 | client backend               | bookstore | postgres | active | select pg_sleep(8)
 16805 | client backend               | bookstore | postgres | active | SELECT pid, backend_type, datn
(7 rows)

Видны оба клиента: один выполняет pg_sleep(8), другой — сам этот запрос (его номер процесса возвращает функция pg_backend_pid()). Номера процессов у читателя будут другими. Модель «процесс на соединение» проста и надёжна — ошибка в одном обслуживающем процессе не портит память другого, — но каждое соединение стоит несколько мегабайт памяти и время на порождение процесса. Поэтому приложения с тысячами клиентов используют пулы соединений (тема 15). Другие СУБД используют потоки вместо процессов (MySQL, Microsoft SQL Server) — это различие реализации, а не модели.

Кластер, базы данных, схемы и роли

Один экземпляр сервера PostgreSQL управляет кластером баз данных (англ. database cluster) — набором баз, хранящихся в одном каталоге данных и обслуживаемых одним главным процессом на одном сетевом порту (по умолчанию 5432) ¹⁰. Слово «кластер» здесь не означает группу серверов; это терминология PostgreSQL. Каталог данных можно узнать командой SHOW data_directory; в Ubuntu это /var/lib/postgresql/16/main, а конфигурационные файлы вынесены в /etc/postgresql/16/main/. Внутри каталога данных — подкаталог base с файлами баз, global с общими для кластера таблицами, pg_wal с журналом и др. Устройство файлов — тема 9.

Иерархию объектов кластера показывает рисунок ниже. Кластер содержит несколько баз данных (англ. databases). Сразу после инициализации их три: postgres (база по умолчанию для подключения служебных программ) и шаблоны template0 и template1, копированием которых создаются новые базы. Базы изолированы друг от друга: запрос, выполняемый в одной базе, не может обратиться к таблицам другой.

SELECT oid, datname, pg_size_pretty(pg_database_size(oid)) AS size
FROM pg_database ORDER BY oid;

  oid  |  datname  |  size   
-------+-----------+---------
     1 | template1 | 7551 kB
     4 | template0 | 7329 kB
     5 | postgres  | 7487 kB
 16384 | bookstore | 19 MB
(4 rows)

Каждый объект каталога PostgreSQL имеет внутренний идентификатор oid; подкаталог base/16384 каталога данных содержит файлы базы bookstore. Учебная база при масштабе 1 занимает около 19 МиБ.

База данных делится на схемы (англ. schemas) — пространства имён для таблиц, представлений, функций и других объектов. Схемы позволяют держать в одной базе объекты разных подсистем или пользователей с одинаковыми именами и разграничивать к ним права. В каждой новой базе есть схема public; учебная база создаёт схему bookstore, и все её таблицы называются полностью bookstore.book, bookstore.orders и т. д. Чтобы не писать имя схемы каждый раз, задают путь поиска (англ. search path) — параметр search_path, список схем, в которых ищутся неквалифицированные имена:

SHOW search_path;
SET search_path = bookstore;

   search_path   
-----------------
 "$user", public
(1 row)

По умолчанию поиск ведётся в схеме с именем текущего пользователя (если она есть) и в public. Все запросы темы выполнены после SET search_path = bookstore. Слово «схема» оказывается многозначным: в общем смысле это описание структуры базы (концептуальная схема), а в PostgreSQL и стандарте SQL — ещё и именованное пространство имён внутри базы. Из контекста почти всегда ясно, о чём идёт речь. Схемы и search_path подробнее рассматриваются в теме 9, а их роль в безопасности — в теме 15.

Над всем кластером стоят роли (англ. roles) — учётные записи, от имени которых выполняются подключения и которым выдаются права. Роли общие для всего кластера, а не для отдельной базы. Роль с атрибутом LOGIN может подключаться (её называют пользователем), роль-суперпользователь (SUPERUSER) обходит все проверки прав. При установке создаётся суперпользователь postgres; в лабораторной работе 1 создаётся собственная роль-суперпользователь, что удобно в учебной среде, но недопустимо в промышленной. Роли, привилегии и аутентификация — тема 15.

Системный каталог

Описание всех объектов кластера хранится в системном каталоге — наборе таблиц схемы pg_catalog: pg_class (таблицы, индексы, представления, последовательности), pg_attribute (столбцы), pg_constraint (ограничения), pg_namespace (схемы), pg_database, pg_roles и др. ¹⁰. Помимо собственного каталога PostgreSQL предоставляет стандартную схему information_schema — набор представлений, определённых стандартом SQL и одинаковых (в идеале) во всех СУБД. Например, сколько в учебной базе таблиц, столбцов и ограничений каждого типа:

SELECT count(*) AS tables FROM information_schema.tables WHERE table_schema = 'bookstore';
SELECT count(*) AS columns FROM information_schema.columns WHERE table_schema = 'bookstore';
SELECT constraint_type, count(*)
FROM information_schema.table_constraints
WHERE table_schema = 'bookstore'
GROUP BY constraint_type ORDER BY constraint_type;

 tables 
--------
     12
(1 row)

 columns 
---------
      53
(1 row)

 constraint_type | count 
-----------------+-------
 CHECK           |    53
 FOREIGN KEY     |    14
 PRIMARY KEY     |    12
 UNIQUE          |     6
(4 rows)

Число ограничений CHECK (53) кажется неожиданно большим: в schema.sql их записано гораздо меньше. Объяснение — в различии двух каталогов. По стандарту ограничение NOT NULL — частный случай ограничения CHECK (столбец IS NOT NULL), и information_schema показывает каждое из 41 таких ограничений учебной базы как отдельный CHECK со служебным именем; в собственном каталоге PostgreSQL 16 признак NOT NULL хранится как свойство столбца (pg_attribute.attnotnull), а pg_constraint содержит только 12 «настоящих» проверок. Этот пример показывает, что стандартный каталог удобен для переносимых запросов, а собственный — точнее отражает устройство конкретной СУБД. К каталогу курс обращается в каждой теме; в лабораторной работе 1 с ним предстоит поработать самостоятельно.

Встраиваемые СУБД: SQLite

Клиент-серверная архитектура — не единственная возможная. Встраиваемая СУБД (англ. embedded database) — библиотека, которая работает внутри процесса прикладной программы и хранит базу в обычном файле; отдельного сервера, сетевого протокола и учётных записей нет. Самый распространённый пример — SQLite, созданная Ричардом Хиппом в 2000 году: она встроена в Android и iOS, браузеры, многие настольные программы и входит в стандартную библиотеку Python ¹⁴.

import sqlite3

con = sqlite3.connect("shop.db")          # база — один файл; сервера нет
con.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title TEXT NOT NULL, price NUMERIC)")
con.execute("INSERT INTO book (title, price) VALUES (?, ?)", ("Пикник на обочине", 520))
con.commit()
print(con.execute("SELECT id, title, price FROM book").fetchall())
con.close()

[(1, 'Пикник на обочине', 520)]

После выполнения в текущем каталоге появляется файл shop.db размером 8192 байта — вся база целиком. SQLite поддерживает SQL, транзакции, ограничения целостности, но рассчитана на одного пишущего клиента в каждый момент времени и не управляет правами доступа: кто может открыть файл, тот может всё. Разработчики SQLite формулируют её назначение так: она конкурирует не с клиент-серверными СУБД, а с функцией fopen() — то есть с хранением данных в собственных файлах приложения ¹⁴. Сравнение двух архитектур сведено в таблицу.

Люди и системы: роли специалистов и рынок СУБД

Кто работает с базой данных

С базой данных работают специалисты разных ролей, и курс затрагивает задачи каждой из них ². Администратор базы данных (англ. database administrator, DBA) отвечает за работу СУБД как системы: установку и обновление, настройку параметров, резервное копирование и восстановление, мониторинг производительности, управление ролями и правами, репликацию. Его инструменты рассматриваются в темах 10–13 и 15. Разработчик (англ. developer) проектирует схему базы для своей задачи, пишет запросы и серверный код, встраивает работу с базой в приложение; ему адресованы прежде всего разделы I, II и IV. Аналитик данных (англ. data analyst) извлекает из базы ответы на вопросы бизнеса — запросами с группировкой и оконными функциями (тема 5), часто над отдельной аналитической копией данных (тема 16). Конечные пользователи работают с базой через приложения, не зная SQL. Границы ролей условны: в небольшой команде разработчик сам администрирует базу.

Обзор рынка СУБД

Рынок СУБД разнообразен, но в сегменте реляционных систем его основу много лет составляют несколько продуктов ²¹².

Oracle Database — коммерческая СУБД компании Oracle, исторически лидер в крупных корпоративных системах; отличается богатством функций для высокой доступности и масштабирования. Microsoft SQL Server — коммерческая СУБД Microsoft, тесно интегрированная с её платформой; с 2017 года работает и под Linux. IBM Db2 — наследница System R, распространена в банковских системах на мейнфреймах. MySQL — свободная СУБД, появившаяся в 1995 году и ставшая основой веб-приложений 2000-х годов (связка LAMP); сейчас принадлежит Oracle, а её ответвление MariaDB развивается независимо. SQLite — встраиваемая СУБД, рассмотренная выше; по числу установленных копий она, вероятно, самая распространённая СУБД в мире ¹⁴.

PostgreSQL — свободная объектно-реляционная СУБД с открытым исходным кодом под либеральной лицензией PostgreSQL License, развиваемая международным сообществом ⁹. Она известна строгим следованием стандарту SQL, надёжностью, развитой системой типов (массивы, диапазоны, jsonb, геометрия) и расширяемостью: собственные типы, функции, методы доступа и целые подсистемы подключаются как расширения (PostGIS для геоданных, TimescaleDB для временных рядов, pgvector для векторного поиска). Основные версии выходят ежегодно и поддерживаются пять лет; курс использует версию 16, вышедшую в 2023 году и входящую в Ubuntu 24.04.

Либеральная лицензия позволяет создавать на основе PostgreSQL собственные продукты, и многие СУБД, включая облачные сервисы крупных провайдеров, построены на его коде. В России на основе PostgreSQL развивается ряд отечественных СУБД, наиболее известная из которых — Postgres Pro компании Postgres Professional (основана в 2015 году); её редакции добавляют к PostgreSQL возможности для крупных систем и включены в реестр российского программного обеспечения. Существуют и другие отечественные дистрибутивы и продукты на основе PostgreSQL. Знания, полученные в курсе на «чистом» PostgreSQL, переносятся на них непосредственно, а знание реляционной модели и SQL — на любую реляционную СУБД.

Почему курс построен на PostgreSQL

Для учебного курса PostgreSQL удобен по нескольким причинам. Он свободен, устанавливается одной командой и близок к стандарту. Его внутреннее устройство хорошо документировано и доступно для наблюдения: планы выполнения, статистика, версии строк, содержимое страниц, журнал — всё это можно увидеть штатными средствами и расширениями, что позволяет не просто прочитать о механизме, а наблюдать его работу. Наконец, на русском языке есть подробная литература о его устройстве, в первую очередь книга Е. В. Рогова «PostgreSQL 16 изнутри» ¹⁰. Принципы же — реляционная модель, нормализация, B-деревья, оптимизация на основе стоимости, протоколы изоляции и восстановления — общие для всех реляционных СУБД.

Итоги темы

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

История баз данных — это история отделения программ от устройства данных. Навигационные модели IMS и CODASYL требовали от программиста знать пути доступа; реляционная модель Кодда (1970) заменила указатели значениями, а навигацию — декларативными запросами. Проекты System R и Ingres доказали практичность идеи, SQL стал стандартом (от SQL-86 до SQL:2023), а последующие волны — объектно-реляционные расширения, NoSQL, NewSQL — дополнили, но не отменили реляционную модель.

Архитектура ANSI/SPARC разделяет внешний, концептуальный и внутренний уровни описания данных; отображения между ними обеспечивают физическую независимость (индекс ускорил запрос, не изменив его текст) и логическую независимость (представление скрыло переименование столбца). Платой за независимость служит необходимость оптимизатора, который сам строит план выполнения. Внутри СУБД запрос проходит разбор, переписывание, оптимизацию и выполнение; исполнитель работает со страницами через менеджер буферов, а менеджер транзакций и журнал обеспечивают изоляцию и долговечность.

PostgreSQL — клиент-серверная СУБД с моделью «процесс на соединение»: главный процесс порождает обслуживающий процесс для каждого клиента и фоновые процессы для служебной работы; все они разделяют общую память. Кластер содержит базы данных, база — схемы, схема — таблицы и другие объекты; роли общие для кластера; описание всего этого хранится в системном каталоге, доступном обычным запросам. Встраиваемые СУБД вроде SQLite занимают другую нишу — замену собственным файлам приложения.

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

Литература

1. Codd E. F. A Relational Model of Data for Large Shared Data Banks. — Communications of the ACM, 1970, Т. 13, № 6, С. 377–387, DOI: 10.1145/362384.362685.
2. Silberschatz A., Korth H. F., Sudarshan S. Database System Concepts. 7th ed. — McGraw-Hill, 2019.
3. Дейт К. Дж. Введение в системы баз данных. 8-е изд. — М.: Вильямс, 2005. (Date C. J. An Introduction to Database Systems. 8th ed. — Addison-Wesley, 2004.)
4. Hellerstein J. M., Stonebraker M., Hamilton J. Architecture of a Database System. — Foundations and Trends in Databases, 2007, Т. 1, № 2, С. 141–259, DOI: 10.1561/1900000002.
5. Astrahan M. M. et al. System R: Relational Approach to Database Management. — ACM Transactions on Database Systems, 1976, Т. 1, № 2, С. 97–137, DOI: 10.1145/320455.320457.
6. Stonebraker M., Wong E., Kreps P., Held G. The Design and Implementation of INGRES. — ACM Transactions on Database Systems, 1976, Т. 1, № 3, С. 189–222, DOI: 10.1145/320473.320476.
7. Chamberlin D. D., Boyce R. F. SEQUEL: A Structured English Query Language. — Proceedings of the 1974 ACM SIGFIDET Workshop on Data Description, Access and Control, 1974, С. 249–264, DOI: 10.1145/800296.811515.
8. Tsichritzis D., Klug A. (eds.) The ANSI/X3/SPARC DBMS Framework: Report of the Study Group on Database Management Systems. — Information Systems, 1978, Т. 3, № 3, С. 173–191.
9. PostgreSQL Global Development Group. PostgreSQL 16 Documentation. Preface, 2. A Brief History of PostgreSQL; 1.2. Architectural Fundamentals. — 2023. https://www.postgresql.org/docs/16/history.html
10. Рогов Е. В. PostgreSQL 16 изнутри. — М.: ДМК Пресс, 2024. https://postgrespro.ru/education/books/internals
11. Bachman C. W. The Programmer as Navigator. — Communications of the ACM, 1973, Т. 16, № 11, С. 653–658, DOI: 10.1145/355611.362534.
12. Клеппман М. Высоконагруженные приложения. Программирование, масштабирование, поддержка. — СПб.: Питер, 2018. (Kleppmann M. Designing Data-Intensive Applications. — O'Reilly, 2017.)
13. ISO/IEC 9075-1:2023. Information technology — Database languages SQL — Part 1: Framework (SQL/Framework). — ISO, 2023.
14. SQLite Documentation. Appropriate Uses For SQLite; About SQLite. — https://www.sqlite.org/whentouse.html

Дополнительные материалы

Вопросы для самопроверки

1. Чем данные отличаются от информации? Почему база данных должна хранить не только значения, но и их описание?
2. Перечислите проблемы хранения данных магазина в наборе файлов CSV, которые решает СУБД. Для каждой назовите механизм СУБД и тему курса, где он изучается.
3. Чем схема базы данных отличается от её состояния? Приведите по два утверждения о «Книжном узле», относящихся к схеме и к состоянию.
4. Почему в навигационных моделях (IMS, CODASYL) изменение структуры данных ломало прикладные программы, а в реляционной модели — в меньшей степени?
5. Что предложил Кодд в статье 1970 года и какие два проекта середины 1970-х годов доказали практичность его идеи? Чем они отличались?
6. Назовите три уровня архитектуры ANSI/SPARC и приведите для «Книжного узла» пример описания на каждом уровне.
7. Чем физическая независимость данных отличается от логической? Какая из них в реляционных СУБД обеспечивается полнее и почему?
8. Почему отказ от явного указания пути доступа в запросе делает оптимизатор обязательным компонентом реляционной СУБД?
9. Опишите путь запроса через компоненты СУБД от получения текста до возврата результата. На каком этапе проверяется существование таблиц и права пользователя?
10. Почему изменения нельзя сразу записывать на диск и зачем при этом нужен журнал упреждающей записи?
11. Какие процессы сервера PostgreSQL работают постоянно, а какие появляются при подключении клиентов? Что произойдёт с другими клиентами, если обслуживающий процесс одного из них аварийно завершится?
12. Что такое кластер баз данных в терминологии PostgreSQL? Что в нём общее для всех баз, а что принадлежит отдельной базе?
13. Зачем нужен параметр search_path? Что произойдёт, если в двух схемах из пути поиска есть таблицы с одинаковым именем?
14. Почему information_schema и pg_catalog дают разное число ограничений CHECK для одной схемы?
15. В каких случаях уместна встраиваемая СУБД вроде SQLite, а в каких нужна клиент-серверная?

Глоссарий темы

Шпаргалка по инструментам

Служба и кластер в Ubuntu:

sudo apt install postgresql postgresql-contrib   # PostgreSQL 16 в Ubuntu 24.04
pg_lsclusters                                    # кластеры, порты, состояние
systemctl status postgresql@16-main              # служба кластера 16/main (с systemd)
sudo systemctl restart postgresql@16-main        # перезапуск
sudo service postgresql start                    # запуск без systemd (WSL2)
sudo pg_ctlcluster 16 main start                 # то же через postgresql-common
sudo -u postgres createuser --superuser $USER    # роль для работы
createdb bookstore                               # база (владелец — текущая роль)
psql -d bookstore                                # подключение через локальный сокет
psql -h localhost -d bookstore                   # подключение по TCP (пароль или ~/.pgpass)
ps -u postgres -o pid,ppid,cmd                   # процессы сервера

Файлы Ubuntu: конфигурация — /etc/postgresql/16/main/ (postgresql.conf, pg_hba.conf), данные — /var/lib/postgresql/16/main/, журнал сервера — /var/log/postgresql/postgresql-16-main.log.

Метакоманды psql:

\?  \h КОМАНДА       справка по метакомандам и по SQL
\l  \c база          базы кластера; переключиться на другую базу
\conninfo            параметры текущего подключения
\dn  \dt схема.*     схемы; таблицы схемы
\d таблица  \d+      описание таблицы (с размерами и комментариями)
\du                  роли
\x auto              расширенный вывод для широких строк
\timing on           время выполнения
\pset null '∅'       отображение NULL
\i файл.sql          выполнить файл
\e                   редактировать запрос во внешнем редакторе
\copy t FROM 'f.csv' WITH (FORMAT csv, HEADER)      загрузка CSV клиентом
\copy (запрос) TO 'f.csv' WITH (FORMAT csv, HEADER)  выгрузка CSV
\password            сменить пароль своей роли
\q                   выход

Запросы к каталогу:

SELECT version();
SHOW data_directory;                         -- каталог данных (нужны права суперпользователя)
SHOW search_path;
SET search_path = bookstore;
SELECT datname, pg_size_pretty(pg_database_size(oid)) FROM pg_database;
SELECT pid, backend_type, usename, datname, state, query FROM pg_stat_activity;
SELECT pg_backend_pid();                     -- номер своего обслуживающего процесса
SELECT pg_terminate_backend(pid);            -- завершить чужую сессию
SELECT count(*) FROM information_schema.tables WHERE table_schema = 'bookstore';
SELECT constraint_type, count(*) FROM information_schema.table_constraints
WHERE table_schema = 'bookstore' GROUP BY 1;
SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint
WHERE conrelid = 'bookstore.book'::regclass;
SELECT pg_relation_filepath('bookstore.book'), pg_size_pretty(pg_relation_size('bookstore.book'));
EXPLAIN SELECT ...;                          -- план выполнения (тема 11)

Файл ~/.pgpass (права 600): строки вида узел:порт:база:роль:пароль, например localhost:5432:*:student:secret.

Что почитать и посмотреть

• Codd E. F. A Relational Model of Data for Large Shared Data Banks — https://doi.org/10.1145/362384.362685. Статья, с которой началась реляционная эпоха; первые разделы о зависимости программ от упорядочения, индексов и путей доступа читаются как прямой ответ на проблемы навигационных систем.
• Документация PostgreSQL 16, часть I «Tutorial» — https://www.postgresql.org/docs/16/tutorial.html. Короткое введение: архитектура, создание базы, первые запросы; хороший первый шаг после лабораторной работы 1.
• Документация PostgreSQL 16, глава «psql» — https://www.postgresql.org/docs/16/app-psql.html. Полный справочник метакоманд, переменных и настроек приглашения; к нему стоит возвращаться на протяжении всего курса.
• Рогов Е. В. «PostgreSQL 16 изнутри» — https://postgrespro.ru/education/books/internals. Подробное и точное описание внутреннего устройства PostgreSQL на русском языке; в этой теме полезно введение о процессах, памяти и структуре кластера.
• Hellerstein J. M., Stonebraker M., Hamilton J. Architecture of a Database System — https://dsf.berkeley.edu/papers/fntdb07-architecture.pdf. Обзор архитектуры СУБД от моделей процессов до менеджера транзакций; объясняет, почему разные СУБД выбирают процессы или потоки.
• Stonebraker M., Pavlo A. What Goes Around Comes Around… And Around… — SIGMOD Record, 2024. Обзор полувековой истории моделей данных (от CODASYL до векторных баз) с выводом, что реляционная модель и SQL раз за разом вбирают идеи конкурентов. Продолжение классической статьи Стоунбрейкера и Хеллерстайна 2005 года.
• Курс CMU 15-445/645 «Database Systems» (Andy Pavlo) — https://15445.courses.cs.cmu.edu/. Лекции об устройстве СУБД с видеозаписями; первая лекция — обзор реляционной модели, последующие — хранение, буферный менеджер, индексы, выполнение запросов, транзакции.
• Курсы Postgres Professional — https://postgrespro.ru/education/courses. Бесплатные учебные курсы по администрированию и разработке на PostgreSQL на русском языке с видео и презентациями; курс для администраторов начинается с установки и архитектуры.
• SQLite: Appropriate Uses For SQLite — https://www.sqlite.org/whentouse.html. Короткое и честное описание того, когда встраиваемая СУБД подходит, а когда нужна клиент-серверная.
• DB-Engines Ranking — https://db-engines.com/en/ranking. Ежемесячно обновляемый рейтинг популярности СУБД по упоминаниям, вакансиям и поисковым запросам; полезен для общего представления о рынке, но не как мера качества.
• Википедия, статья «Database» (англ.), раздел «History» — краткая хронология с навигационными, реляционными и постреляционными системами и ссылками на первоисточники.

Углубление (по желанию)

«Великий спор» 1974 года

В мае 1974 года на семинаре ACM SIGFIDET (предшественнике конференции SIGMOD) состоялась публичная дискуссия между сторонниками реляционной модели во главе с Коддом и сторонниками сетевой модели CODASYL во главе с Бахманом, вошедшая в историю как «Великий спор» (англ. The Great Debate). Сторонники CODASYL утверждали, что реляционная модель слишком математична для обычных программистов и не может быть эффективно реализована; сторонники Кодда — что навигационные системы слишком сложны, лишены формальной основы и привязывают программы к физической организации данных. Спор не завершился победой одной стороны в тот же день: его разрешили практические системы — System R, Ingres и затем коммерческие продукты, — показавшие, что оптимизатор способен находить хорошие планы, а декларативный язык удобнее навигации. Любопытно, что многие доводы этого спора повторились в 2000-х годах в дискуссиях о NoSQL и документных базах.

Процессы или потоки

PostgreSQL с самого начала использует модель «процесс на соединение», унаследованную от POSTGRES 1980-х годов, когда многопоточность в Unix не была стандартизирована. Процессы надёжно изолированы: ошибка в одном обслуживающем процессе не портит память другого, а при аварийном завершении процесса postmaster перезапускает остальные и восстанавливает общую память в согласованное состояние. Цена — накладные расходы на порождение процесса и на память каждого соединения, из-за которых PostgreSQL плохо переносит тысячи одновременных соединений без пула (тема 15). Многие другие СУБД (MySQL, Microsoft SQL Server, Oracle в многопоточном режиме) используют потоки. В сообществе PostgreSQL с 2023 года обсуждается возможный переход к потоковой модели; это долгий проект, затрагивающий практически весь код сервера, и в версии 16 модель остаётся процессной.

Один размер не подходит всем

В 2005 году Майкл Стоунбрейкер и Угур Четинтемел опубликовали статью «One Size Fits All: An Idea Whose Time Has Come and Gone», в которой утверждали, что эпоха универсальных реляционных СУБД, одинаково хорошо обслуживающих любые задачи, закончилась, и для аналитики, потоковой обработки, научных данных нужны специализированные системы. Предсказание отчасти сбылось: появились колоночные аналитические СУБД, потоковые системы, NoSQL-хранилища (тема 16). Но одновременно универсальные СУБД вроде PostgreSQL научились многому из специализированного — документам jsonb, полнотекстовому поиску, секционированию, расширениям для временных рядов и векторов. Выбор между «одной универсальной СУБД» и «зоопарком специализированных» остаётся инженерным компромиссом между удобством и простотой сопровождения, с одной стороны, и предельной производительностью — с другой.

Лабораторная работа 1. Рабочее место и учебная база

• Раздел курса: тема 1 «Введение в базы данных и СУБД»

Введение

Все последующие лабораторные работы курса выполняются на одном рабочем месте: сервер PostgreSQL 16 на Ubuntu 24.04 (физической или виртуальной машине либо в WSL2), собственная роль, база данных bookstore с учебной базой «Книжный узел» и клиент psql. Настоящая работа создаёт это рабочее место и знакомит с ним. Попутно на практике проверяются утверждения темы 1: сервер — это набор процессов, у кластера есть каталог данных, база делится на схемы, описание базы хранится в системном каталоге и доступно обычными запросами, а ограничения целостности проверяет сервер, а не клиент.

Работа состоит из основной части (части 1–7) и продвинутой (часть 8), которую можно выполнить по желанию. Если установить PostgreSQL на свою машину невозможно, часть 1 выполняется по альтернативной инструкции для Docker из README.md окружения, а в журнале работы это отмечается; остальные части от способа установки не зависят.

Цель работы

Подготовить рабочее место для курса — сервер PostgreSQL 16, собственные роль и базу с учебной базой «Книжный узел» — и освоить клиент psql, системный каталог и первые запросы SELECT.

После выполнения работы можно будет:

• установить PostgreSQL 16, проверить состояние службы и найти каталог данных и конфигурационные файлы кластера;
• создать роль и базу данных, подключиться к серверу через локальный сокет и по TCP с паролем из файла ~/.pgpass;
• пользоваться основными метакомандами psql и настроить его через ~/.psqlrc;
• загрузить учебную базу из SQL-скриптов, описать её таблицы и связи по выводу \d;
• написать запрос SELECT … FROM … WHERE … ORDER BY … LIMIT к одной таблице и проверить результат скриптом самопроверки;
• загрузить данные из CSV командой \copy, найти и исправить строки, нарушающие ограничения целостности;
• получить из information_schema и pg_catalog сведения о таблицах, столбцах и ограничениях и объяснить расхождение между ними.

Теоретический минимум

Теория изложена в теме 1: разделы «Данные, базы данных и системы управления ими» (ограничения целостности, декларативные запросы) и «Архитектура PostgreSQL» (процессы, кластер, базы, схемы, роли, системный каталог). Ниже — сжатая справка.

Учебная база «Книжный узел» (схема bookstore) описывает книжный интернет-магазин. Полное описание таблиц, связей и масштабов — в файле README.md окружения; для этой работы достаточно следующего:

Основные метакоманды psql (полный список — в документации psql):

Запрос к одной таблице, которого достаточно для этой работы (подробно синтаксис SELECT разбирается в теме 3):

SELECT столбец1, столбец2          -- какие столбцы вывести
FROM таблица                       -- откуда
WHERE условие1 AND условие2        -- какие строки отобрать
ORDER BY столбец1 DESC, столбец2   -- в каком порядке (DESC — по убыванию)
LIMIT 10;                          -- сколько первых строк вывести

Строковые константы пишутся в одинарных кавычках ('Москва'), двойные кавычки обозначают идентификаторы ("Book"). Проверка отсутствия значения — только city IS NULL, а не city = NULL (почему — тема 2).

Полезные представления каталога: information_schema.tables, information_schema.columns, information_schema.table_constraints (стандарт SQL); pg_class, pg_attribute, pg_constraint, pg_namespace, pg_database, pg_roles, pg_stat_activity (PostgreSQL).

Перечень оснащения

• ОС Ubuntu 24.04 (физическая машина, виртуальная машина или WSL2) с правами sudo; альтернатива — Docker (см. README.md окружения).
• PostgreSQL 16 из стандартного репозитория Ubuntu: sudo apt install postgresql postgresql-contrib.
• Для продвинутой части — Python 3 с драйвером psycopg 3: sudo apt install python3-psycopg.
• Архив окружения lr-env: schema.sql и data.sql (учебная база), README.md (описание базы и установки), psqlrc.example (пример настроек psql), setup.sql (рабочая схема lab01), new_books.csv (данные для части 6), check.sql (самопроверка частей 5 и 6).
• Ресурсы: около 300 МБ на диске для сервера и базы масштаба 1 (база занимает около 20 МБ), 1 ГБ оперативной памяти; доступ в Интернет — только для установки пакетов.

Порядок выполнения работы

Работа выполняется под обычной учётной записью Linux; команды, требующие прав администратора, начинаются с sudo. Далее имя учётной записи обозначается student — подставляйте своё (его выводит команда whoami). Учебная база загружается в базу данных bookstore в части 4; она понадобится во всех следующих работах. Если базу нужно вернуть в исходное состояние, достаточно повторно выполнить оба скрипта загрузки: schema.sql удаляет и заново создаёт схему bookstore, а data.sql детерминированно генерирует одинаковые данные.

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

Часть 1. Установка PostgreSQL и служба сервера

Задание

1. Установите сервер и дополнительные модули:

   sudo apt update
   sudo apt install postgresql postgresql-contrib
   psql --version
   

   В Ubuntu 24.04 пакет postgresql устанавливает PostgreSQL 16. При установке пакеты Debian/Ubuntu автоматически создают кластер 16/main (каталог данных /var/lib/postgresql/16/main, конфигурация в /etc/postgresql/16/main/, порт 5432), служебную учётную запись ОС postgres и одноимённую роль-суперпользователя, и запускают сервер.

2. Проверьте, что кластер работает. Утилита pg_lsclusters из пакета postgresql-common выводит кластеры машины:

   pg_lsclusters
   

   Ожидаемый вывод (путь к журналу может отличаться):

   Ver Cluster Port Status Owner    Data directory              Log file
   16  main    5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
   

3. Посмотрите состояние службы. Если система загружена с systemd (обычная установка Ubuntu; в WSL2 — при включённом systemd, что для современных образов Ubuntu принято по умолчанию):

   systemctl status postgresql@16-main
   

   Служба postgresql.service — объединяющая: она сама ничего не запускает и показывает состояние active (exited), а сервер конкретного кластера работает в службе postgresql@16-main.service. Остановка и запуск: sudo systemctl stop postgresql@16-main, sudo systemctl start postgresql@16-main; автозапуск при загрузке включён по умолчанию.

   Если systemd нет (команда systemctl сообщает, что система загружена без systemd, — так бывает в старых установках WSL2), сервер запускается сценарием совместимости или утилитой pg_ctlcluster:

   sudo service postgresql start      # или: sudo pg_ctlcluster 16 main start
   sudo service postgresql status
   

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

4. Найдите процессы сервера. Они работают от имени пользователя ОС postgres:

   ps -u postgres -o pid,ppid,cmd
   

   Определите номер главного процесса (postmaster) — у каких процессов он указан в столбце PPID? Сопоставьте имена фоновых процессов с описанием в разделе «Архитектура PostgreSQL» темы 1.

5. Найдите конфигурационные файлы и каталог данных: ls /etc/postgresql/16/main/ и sudo ls /var/lib/postgresql/16/main/. Почему вторая команда без sudo завершается ошибкой «Permission denied»? Найдите в файле postgresql.conf параметры port, listen_addresses и shared_buffers (grep -E '^(port|#?listen_addresses|shared_buffers)' /etc/postgresql/16/main/postgresql.conf).

Что должно получиться

Вывод psql --version, pg_lsclusters и проверки службы; список процессов сервера с указанием главного процесса и назначения каждого фонового процесса; значения трёх параметров конфигурации; ответ на вопрос о правах доступа к каталогу данных.

Часть 2. Роль, база данных и подключение

Задание

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

   sudo -u postgres psql -c '\l' -c '\du'
   

   Пароль не запрашивается. Причина — метод аутентификации peer для подключений через локальный сокет в файле /etc/postgresql/16/main/pg_hba.conf: сервер спрашивает у ядра, от имени какого пользователя ОС открыт сокет, и пускает, если имя совпадает с именем роли. Найдите соответствующую строку в pg_hba.conf (sudo grep -v '^#' /etc/postgresql/16/main/pg_hba.conf | grep -v '^$'). Аутентификация подробно разбирается в теме 15.

2. Создайте собственную роль-суперпользователя с именем, совпадающим с именем учётной записи Linux, и базу bookstore, владельцем которой она будет:

   sudo -u postgres createuser --superuser $USER
   createdb bookstore
   createdb $USER                 # база по умолчанию: psql без параметров подключается к ней
   psql -d bookstore
   

   Внутри psql выполните \conninfo и SELECT current_user, current_database(), version();. Роль-суперпользователь удобна в учебной среде (можно наблюдать служебные представления и файлы), но в промышленной эксплуатации приложения так не подключают — тема 15.

3. Предсказание. Будет ли запрошен пароль при подключении по TCP: psql -h localhost -d bookstore? Проверьте. Объясните результат, найдя в pg_hba.conf строку для адреса 127.0.0.1/32.

4. Задайте своей роли пароль (внутри psql):

   \password
   

   Метакоманда вычисляет хеш пароля на стороне клиента и отправляет серверу команду ALTER ROLE, поэтому пароль не попадает в журнал сервера и историю команд. Повторите подключение psql -h localhost -d bookstore и введите пароль.

5. Чтобы не вводить пароль каждый раз, создайте файл паролей ~/.pgpass. Каждая строка имеет формат узел:порт:база:роль:пароль, звёздочка обозначает любое значение:

   echo 'localhost:5432:*:student:ваш_пароль' > ~/.pgpass
   chmod 600 ~/.pgpass
   psql -h localhost -d bookstore -c 'SELECT 1'
   

6. Сломать и починить. Выполните chmod 644 ~/.pgpass и повторите подключение по TCP. Что сообщает psql и почему клиентская библиотека отказывается читать файл? Верните права 600.

Что должно получиться

Вывод \l и \du до и после создания роли; вывод \conninfo при подключении через сокет и по TCP; строки pg_hba.conf, объясняющие поведение в пунктах 1 и 3; предупреждение из пункта 6 и его объяснение.

Часть 3. Клиент psql

Задание

1. Подключитесь к базе bookstore и выполните \?. Найдите в справке метакоманды из таблицы теоретического минимума. Выполните \h CREATE DATABASE — откуда psql берёт этот текст?
2. Выполните SELECT * FROM pg_database; — результат неудобно читать, строки не помещаются на экране. Включите \x и повторите запрос, затем включите \x auto. Чем отличаются режимы?
3. Включите \timing on и выполните SELECT count(*) FROM pg_class; дважды. Какое время показывает psql — время выполнения на сервере или полное время от отправки запроса до получения ответа?
4. Наберите запрос SELECT datname FROM pg_database (с ошибкой — без точки с запятой) и нажмите Enter. Как изменилось приглашение? Что означают символы = и - после имени базы и чем приглашение суперпользователя (#) отличается от приглашения обычной роли (>)? Завершите запрос символом ;.
5. Выполните \e, допишите в редакторе к последнему запросу ORDER BY datname;, сохраните файл и выйдите из редактора. Что произошло? Если открылся незнакомый редактор, задайте свой: export EDITOR=nano.
6. Скопируйте пример настроек: cp psqlrc.example ~/.psqlrc. Прочитайте комментарии в файле. Перезапустите psql и проверьте, что NULL отображается символом ∅: SELECT NULL AS nothing, '' AS empty;. Почему без настройки \pset null эти два значения неразличимы на экране, хотя для СУБД это совершенно разные вещи?

Что должно получиться

Ответы на вопросы пунктов 1–6 с фрагментами вывода; итоговое содержимое ~/.psqlrc (если вносились изменения — с пояснением).

Часть 4. Загрузка учебной базы и обзор схемы

Задание

1. Распакуйте архив окружения и загрузите учебную базу:

   unzip lr-env.zip -d ~/lab01 && cd ~/lab01/lr-env
   psql -d bookstore -c "ALTER DATABASE bookstore SET timezone = 'Europe/Moscow'"
   psql -d bookstore -f schema.sql
   psql -d bookstore -v scale=1 -f data.sql
   

   Первая команда закрепляет для базы часовой пояс, в котором получены примеры курса: генератор отсчитывает даты от полуночи в поясе сеанса, и с этой настройкой даты и время в выводе совпадут с материалами курса. Скрипт schema.sql создаёт схему bookstore и двенадцать таблиц с ограничениями; data.sql заполняет их. Параметр scale задаёт масштаб: при scale=1 загрузка занимает несколько секунд. В конце data.sql печатает число строк в основных таблицах; сравните с эталоном:

   Таблица	Строк при scale=1
   book	2010
   author	210
   customer	5000
   orders	20000
   order_item	59910
   payment	18506
   review	13744

   Если числа не совпадают, проверьте, что скрипты не изменялись и data.sql выполнялся с -v scale=1 на пустых таблицах только что созданной схемы.

2. Выполните \dn и \dt. Предсказание: почему \dt без параметров не показывает таблиц учебной базы? Выполните \dt bookstore.*, затем SET search_path = bookstore; и снова \dt. Чтобы не задавать путь поиска в каждом сеансе, закрепите его для своей роли в этой базе:

   ALTER ROLE student IN DATABASE bookstore SET search_path = bookstore, public;
   

   Переподключитесь и проверьте SHOW search_path;.

3. Выполните \dt+ и \d+ book. Ответьте по выводу: какие столбцы таблицы book обязательны (not null); какие ограничения CHECK наложены; на какие таблицы ссылается book (раздел «Foreign-key constraints») и какие таблицы ссылаются на неё (раздел «Referenced by»); какая таблица самая большая по размеру.

4. Выполните \d для остальных таблиц и нарисуйте от руки (или в любом редакторе) схему учебной базы: прямоугольник на таблицу, стрелка от ссылающейся таблицы к той, на которую она ссылается. Отметьте таблицу, ссылающуюся сама на себя. Какие две таблицы реализуют связь «многие ко многим»? Нотация ER-диаграмм подробно разбирается в теме 7; здесь достаточно стрелок.

5. Предсказание. Что произойдёт при попытке удалить издательство «Северный узел» (DELETE FROM publisher WHERE publisher_id = 10;)? А при попытке вставить заказ покупателя с номером 999999? Сформулируйте ответ по выводу \d, затем проверьте внутри транзакции, чтобы не изменить базу:

   BEGIN;
   DELETE FROM publisher WHERE publisher_id = 10;
   ROLLBACK;
   

Что должно получиться

Таблица фактического числа строк; ответ на вопрос о search_path; ответы по \d+ book; схема связей учебной базы; предсказания и фактические сообщения сервера из пункта 5 с объяснением.

Часть 5. Первые запросы

Задание

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

psql -d bookstore -f setup.sql

Каждый ответ сначала отладьте как обычный запрос, затем сохраните командой CREATE OR REPLACE VIEW lab01.qN AS <запрос>;. Столбцы выводятся строго в указанном порядке. Номера издательств, рубрик, складов и книг найдите отдельными запросами к справочникам (например, SELECT publisher_id FROM publisher WHERE name = 'ДМК Пресс';), а не подбором.

1. q1. Книги издательства «ДМК Пресс», изданные в 2023 году и позже: title, pub_year, price, по году, затем по названию.
2. q2. Первые десять (по номеру) покупателей, не указавших город: customer_id, email. Предсказание до выполнения: около какой доли покупателей не указали город, если генератор данных оставляет город пустым примерно у каждого десятого? Проверьте запросом без LIMIT с count(*).
3. q3. Десять самых дорогих книг рубрики «Базы данных»: title, price. Выполните запрос несколько раз с LIMIT 2 вместо LIMIT 10. Всегда ли возвращаются одни и те же строки в одном порядке? Почему? Как сделать результат однозначным?
4. q4. Новые (статус new) заказы склада в Казани: order_id, customer_id, по номеру заказа.
5. q5. Толстые и дешёвые книги — больше 900 страниц и дешевле 200 рублей: title, pages, price, по цене.
6. q6. Остатки книги «Пикник на обочине» по складам: warehouse_id, quantity, по номеру склада. Сравните результат с выводом SELECT * FROM stock WHERE book_id = 1; — у каждой ли книги есть остаток на каждом складе?

Проверьте ответы:

psql -d bookstore -f check.sql

Скрипт сравнивает число строк и контрольную сумму результата каждого представления с эталоном (порядок строк не учитывается) и печатает «верно» или подсказку.

Найти ошибку. Каждый из запросов ниже написан с ошибкой. Для каждого до выполнения предскажите, что произойдёт (ошибка сервера или неверный результат), выполните, объясните причину и исправьте.

-- а) найти «Войну и мир»
SELECT title, price FROM book WHERE title = "Война и мир";
-- б) сколько покупателей не указали город
SELECT count(*) FROM customer WHERE city = NULL;
-- в) три самые дорогие книги
SELECT title, price FROM book ORDER BY price LIMIT 3;
-- г) книги, которые со скидкой 10 % стоят меньше 200 рублей
SELECT title, price * 0.9 AS sale FROM book WHERE sale < 200;
-- д) первая строка таблицы книг
SELECT * FROM "Book" LIMIT 1;

Что должно получиться

Тексты шести представлений и вывод check.sql (все проверки частей 5 — «верно»); ответы на вопросы пунктов 2, 3 и 6; для каждого запроса из задания «найти ошибку» — предсказание, фактический результат или сообщение сервера, объяснение и исправленный запрос.

Часть 6. Загрузка данных из CSV: сломать и починить

Задание

Отдел закупок прислал файл new_books.csv с новыми поступлениями. Перед добавлением в каталог их нужно загрузить в промежуточную таблицу lab01.book_import, созданную setup.sql, — её ограничения повторяют ограничения bookstore.book.

1. Изучите файл (cat new_books.csv) и описание таблицы (\d lab01.book_import). Обратите внимание на строку, где название содержит запятую и поэтому заключено в кавычки, и на строку с пустым полем pages. Предсказание: какие строки файла нарушают ограничения таблицы и какие именно?

2. Загрузите файл:

   \copy lab01.book_import FROM 'new_books.csv' WITH (FORMAT csv, HEADER)
   

   Команда \copy выполняется клиентом psql: он читает файл на своей машине и передаёт строки серверу командой COPY … FROM STDIN. Серверная команда COPY … FROM 'файл' читала бы файл с машины сервера от имени пользователя postgres и требовала бы особых прав.

3. Загрузка завершится ошибкой. Сколько строк попало в таблицу (SELECT count(*) FROM lab01.book_import;)? Что это говорит об атомарности команды COPY?

4. По сообщению сервера (строки DETAIL и CONTEXT) найдите ошибочную строку файла, исправьте её в текстовом редакторе так, чтобы данные стали правдоподобными (ISBN — 13 цифр, цена — неотрицательная, рубрика — существующая; подходящую рубрику подберите по таблице category), и повторите загрузку. Повторяйте, пока все шесть строк не загрузятся. Ограничения таблицы не изменяйте.

5. Для какой ошибки сервер не указал номер строки файла в CONTEXT? Предположите, почему проверка этого ограничения устроена иначе, чем проверка CHECK (подсказка: проверку внешнего ключа PostgreSQL выполняет после вставки строк, в конце оператора; подробно — в теме 2).

6. Убедитесь, что пустое поле pages загрузилось как NULL: SELECT isbn, pages FROM lab01.book_import WHERE pages IS NULL;. Запустите check.sql ещё раз.

7. Выгрузите результат запроса в CSV: все книги рубрики «Базы данных» дороже 2500 рублей — title и price:

   \copy (SELECT title, price FROM bookstore.book WHERE category_id = 13 AND price > 2500 ORDER BY price DESC, title) TO 'expensive_db_books.csv' WITH (FORMAT csv, HEADER)
   

   Сколько строк в полученном файле (wc -l)? Почему на одну больше, чем строк в результате?

Что должно получиться

Предсказания пункта 1; сообщения сервера для каждой ошибочной строки и сделанные исправления; ответы на вопросы пунктов 3, 5 и 7; вывод check.sql с итогом «7 из 7».

Часть 7. Системный каталог

Задание

1. С помощью information_schema определите: сколько таблиц в схеме bookstore; сколько в ней столбцов всего и в какой таблице столбцов больше всего; сколько ограничений каждого типа (information_schema.table_constraints, группировка по constraint_type). Образец запроса с группировкой (подробно — тема 5):

   SELECT table_name, count(*) AS columns
   FROM information_schema.columns
   WHERE table_schema = 'bookstore'
   GROUP BY table_name
   ORDER BY columns DESC;
   

2. Посчитайте ограничения той же схемы по собственному каталогу PostgreSQL:

   SELECT contype, count(*)
   FROM pg_constraint
   WHERE connamespace = 'bookstore'::regnamespace
   GROUP BY contype ORDER BY contype;
   

   Коды типов: c — CHECK, f — внешний ключ, p — первичный ключ, u — UNIQUE. Совпадает ли число ограничений CHECK с результатом пункта 1? Найдите причину расхождения: посмотрите имена и условия «лишних» ограничений в information_schema.check_constraints и посчитайте обязательные столбцы (information_schema.columns, is_nullable = 'NO').

3. Выведите все ограничения таблицы book с их определениями:

   SELECT conname, contype, pg_get_constraintdef(oid)
   FROM pg_constraint
   WHERE conrelid = 'bookstore.book'::regclass
   ORDER BY contype, conname;
   

   По какому правилу PostgreSQL генерирует имена ограничений, если они не заданы в CREATE TABLE явно?

4. Найдите в pg_class число страниц и оценку числа строк таблицы book (relpages, reltuples) и сравните с pg_relation_size('bookstore.book'). Какой размер страницы следует из этих чисел? Проверьте командой SHOW block_size;.

5. Выведите список баз кластера запросом к pg_database с размерами (pg_size_pretty(pg_database_size(oid))). Какую долю занимает учебная база?

Что должно получиться

Запросы и результаты пунктов 1–5; объяснение расхождения между information_schema и pg_constraint; правило именования ограничений; расчёт размера страницы.

Часть 8. Процессы, файлы и программный доступ (продвинутая)

Задание

1. Откройте два терминала и подключитесь к bookstore в каждом. В первом выполните SELECT pg_backend_pid(); и затем SELECT pg_sleep(60);. Во втором найдите первую сессию в pg_stat_activity (столбцы pid, backend_type, usename, datname, state, query) и тот же процесс в выводе ps -u postgres -o pid,ppid,cmd. Что показывает заголовок процесса в ps во время выполнения запроса и после него?

2. Из второй сессии завершите первую: SELECT pg_terminate_backend(<pid>);. Что увидел клиент в первом терминале? Изменилось ли что-нибудь для других сессий и фоновых процессов? Какое свойство архитектуры «процесс на соединение» это иллюстрирует?

3. Найдите файл, в котором хранится таблица book: SHOW data_directory; и SELECT pg_relation_filepath('bookstore.book');. Посмотрите его размер командой sudo ls -l и сравните с pg_relation_size. Объясните, почему копировать этот файл «вручную» для резервного копирования таблицы бессмысленно (подсказка: буферный кеш и журнал; тема 13).

4. Подключитесь к базе из Python и повторите один из запросов части 5:

   import psycopg
   
   with psycopg.connect("dbname=bookstore") as conn:
       rows = conn.execute(
           "SELECT title, price FROM bookstore.book WHERE pages > %s AND price < %s ORDER BY price",
           (900, 200),
       ).fetchall()
       for title, price in rows:
           print(title, price)
       print(conn.execute("SELECT pg_backend_pid()").fetchone())
   

   Значения передаются параметрами %s, а не подставляются в текст запроса, — почему так нужно делать, разбирается в теме 15. Какого типа значения price в Python и почему не float?

5. Создайте ту же таблицу publisher во встраиваемой СУБД SQLite средствами модуля sqlite3 стандартной библиотеки Python, вставьте две строки и выполните запрос. Сравните: где хранятся данные, есть ли отдельный процесс сервера (ps), что нужно, чтобы с базой одновременно работали два пользователя с разными правами.

Что должно получиться

Фрагменты pg_stat_activity и ps с пояснениями; ответ на вопросы о завершении сессии; путь и размер файла таблицы; код и вывод программы на Python; сравнительная таблица PostgreSQL и SQLite по итогам опыта.

Журнал работы

Запись для себя: сведения о рабочем месте (ОС, способ установки, вывод SELECT version();) и по каждой части — «предсказание — команда — фактический результат — объяснение». Полезно сохранить представления lab01.q1–lab01.q6, итоговый вывод check.sql и схему связей учебной базы, а в конце записать несколько выводов о работе.

Контрольные вопросы

1. Чем кластер баз данных PostgreSQL отличается от базы данных и от схемы? Что из перечисленного общее для всех баз кластера?
2. Почему sudo -u postgres psql подключается без пароля, а psql -h localhost запрашивает пароль? Где это настраивается?
3. Чем команда \copy отличается от серверной команды COPY? На какой машине и от имени какого пользователя ОС читается файл в каждом случае?
4. Почему загрузка CSV с одной ошибочной строкой не добавила в таблицу ни одной строки? Какое свойство транзакций это иллюстрирует?
5. Почему условие city = NULL не находит ни одной строки, а сервер не сообщает об ошибке?
6. Почему без второго ключа сортировки запрос с ORDER BY price DESC LIMIT 2 может возвращать разные строки? Является ли это ошибкой СУБД?
7. Почему information_schema и pg_constraint показывают разное число ограничений CHECK для одной и той же схемы? Когда предпочтительнее пользоваться каждым из каталогов?
8. Какие процессы сервера вы наблюдали и какой из них порождает обслуживающие процессы? Что происходит с остальными сессиями, если завершить один обслуживающий процесс?
9. Почему таблицу учебной базы удалось изменить и вернуть обратно в транзакции с ROLLBACK, а файл CSV, испорченный в редакторе, — нет?
10. Какие из гарантий СУБД, перечисленных в теме 1, вы наблюдали в этой работе непосредственно? Приведите для каждой пример из выполненных заданий.