0Pricing
SQL Academy · Урок

Массивы и нормализованные таблицы

Когда массивы — правильный выбор

«Массивы и нормализованные таблицы» — бесплатный урок SQL Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Два способа хранить несколько значений

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

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

Нормализованный подход

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

Это классическая реляционная модель и вариант по умолчанию в большинстве случаев.

CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE user_phones (
  id      SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  phone   TEXT NOT NULL
);

INSERT INTO users (name) VALUES ('Alice'), ('Bob');

INSERT INTO user_phones (user_id, phone) VALUES
  (1, '+1-555-0101'),
  (1, '+1-555-0102'),
  (2, '+1-555-0200');

Подход с массивами

TEXT[] в PostgreSQL (как и любой другой тип с добавленным после него []) позволяет хранить несколько значений непосредственно в одном столбце. Дополнительная таблица не требуется.

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

CREATE TABLE users_with_phones (
  id     SERIAL PRIMARY KEY,
  name   TEXT    NOT NULL,
  phones TEXT[]
);

INSERT INTO users_with_phones (name, phones) VALUES
  ('Alice', ARRAY['+1-555-0101', '+1-555-0102']),
  ('Bob',   ARRAY['+1-555-0200']);

Запрашивать данные из массивов легко

Искать внутри столбца-массива просто с помощью оператора ANY или оператора @> (содержит). Например, всех пользователей с определённым телефонным номером можно найти с помощью простого предложения WHERE.

-- Find users who have a specific phone number
SELECT name
FROM users_with_phones
WHERE '+1-555-0101' = ANY(phones);

-- Or using the array-contains operator
SELECT name
FROM users_with_phones
WHERE phones @> ARRAY['+1-555-0101'];

Когда массивы лучше: простой поиск

Массивы хорошо подходят, когда:

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

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

CREATE TABLE posts (
  id         SERIAL PRIMARY KEY,
  title      TEXT    NOT NULL,
  tags       TEXT[]
);

INSERT INTO posts (title, tags) VALUES
  ('Intro to SQL',      ARRAY['sql', 'beginner', 'database']),
  ('Advanced Indexes',  ARRAY['sql', 'performance', 'indexes']),
  ('NoSQL Overview',    ARRAY['nosql', 'beginner']);

-- Get all posts tagged 'beginner'
SELECT title FROM posts
WHERE 'beginner' = ANY(tags);

Когда нормализованные таблицы лучше: связи

Нормализованные таблицы предпочтительнее, когда:

  • отдельным значениям нужны собственные атрибуты (например, у телефонного номера есть тип: домашний или рабочий);
  • нужно соединять таблицы по отдельным значениям;
  • значения меняются независимо и часто;
  • нужна ссылочная целостность с помощью внешних ключей.
-- Phone numbers need a 'type' attribute — array can't do this cleanly
CREATE TABLE user_phones (
  id      SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  phone   TEXT NOT NULL,
  type    TEXT CHECK (type IN ('home', 'work', 'mobile'))
);

INSERT INTO user_phones (user_id, phone, type) VALUES
  (1, '+1-555-0101', 'home'),
  (1, '+1-555-0102', 'work');

Различия в индексировании

Для нормализованной таблицы можно добавить стандартный индекс B-дерева по внешнему ключу или столбцу со значением. Для массивов нужен индекс GIN (обобщённый инвертированный индекс), обеспечивающий быстрый поиск внутри массива.

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

-- Index for fast array element lookups
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- Now this query uses the index efficiently
EXPLAIN SELECT title FROM posts
WHERE tags @> ARRAY['sql'];

Агрегирование по строкам: преимущество нормализации

Если нужно подсчитывать, группировать или агрегировать отдельные значения, нормализованные таблицы гораздо удобнее. Для агрегирования внутри массивов требуется unnest(): сначала он разворачивает массив в строки, фактически воссоздавая нормализованную структуру во время выполнения запроса.

-- Count posts per tag (array approach — needs unnest)
SELECT tag, COUNT(*) AS post_count
FROM posts, unnest(tags) AS tag
GROUP BY tag
ORDER BY post_count DESC;

-- With a normalized post_tags table this would be simpler:
-- SELECT tag, COUNT(*) FROM post_tags GROUP BY tag;

Изменение элементов массива

Обновление или удаление одного элемента внутри массива требует неудобного синтаксиса: необходимо заменить весь массив или использовать array_remove(). В нормализованной таблице достаточно выполнить DELETE или UPDATE для нужной строки.

-- Remove a single tag from an array column
UPDATE posts
SET tags = array_remove(tags, 'beginner')
WHERE id = 1;

-- Append a new tag
UPDATE posts
SET tags = array_append(tags, 'tutorial')
WHERE id = 1;

SELECT title, tags FROM posts WHERE id = 1;

Обеспечение допустимости значений

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

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

-- Normalized: only valid category IDs allowed (FK enforced)
CREATE TABLE categories (
  id   SERIAL PRIMARY KEY,
  name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_categories (
  post_id     INT REFERENCES posts(id),
  category_id INT REFERENCES categories(id),
  PRIMARY KEY (post_id, category_id)
);

-- Array: no constraint possible — any text value is accepted
-- UPDATE posts SET tags = ARRAY['totally_invalid_tag'] WHERE id = 1;

Практическое руководство по выбору

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

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

-- Summary example: tags as array (good fit)
SELECT title, tags
FROM posts
WHERE tags @> ARRAY['sql']
ORDER BY title;

-- Unnest when you need row-level processing
SELECT title, unnest(tags) AS tag
FROM posts
ORDER BY title, tag;

Быстрая проверка

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

Итоги урока

В этом уроке Вы изучили основные компромиссы между массивами и нормализованными таблицами в PostgreSQL.

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

Часто задаваемые вопросы

Урок «Массивы и нормализованные таблицы» бесплатный?

Да — полный текст урока «Массивы и нормализованные таблицы» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Массивы и нормализованные таблицы»?

Когда массивы — правильный выбор Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

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

Сколько времени занимает урок «Массивы и нормализованные таблицы»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Основы столбцов-массивов
  2. Поиск внутри массивов
  3. UNNEST и агрегация
  4. Массивы и нормализованные таблицы
← Назад к SQL Academy