Реляционные БД: Интенсивный план

Содержание

Фундамент (ACID, CAP, BASE)
Транзакции и блокировки
Проектирование (Normal forms, Trees)
Производительность (Indexes, Optimisation)
Программирование в БД
Explain Analyze
Connection Pooling

Фундамент (ACID, CAP, BASE)

ACID

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

Atomicity (Атомарность): Все операции в транзакции рассматриваются как единое целое. Либо все операции успешно завершаются, либо ни одна из них не применяется.
Consistency (Согласованность): Транзакция переводит базу данных из одного согласованного состояния в другое согласованное состояние.
Isolation (Изолированность): Параллельные транзакции не влияют друг на друга. Существует несколько уровней изоляции:
- Read Uncommitted (Чтение неподтвержденных данных): Самый низкий уровень, позволяет читать неподтвержденные изменения других транзакций (dirty reads).
- Read Committed (Чтение подтвержденных данных): Транзакция видит только те изменения, которые были зафиксированы до начала транзакции. Предотвращает dirty reads.
- Repeatable Read (Повторяемость чтения): Гарантирует, что если транзакция читает одну и ту же строку несколько раз, она получит одинаковое значение. Предотвращает dirty и non-repeatable reads.
- Serializable (Сериализуемость): Высший уровень изоляции, обеспечивает полную изоляцию транзакций, как будто они выполняются последовательно. Предотвращает dirty, non-repeatable reads и phantom reads.

Проблемы, которые решают уровни изоляции:

Dirty Read: Чтение неподтвержденных изменений другой транзакции.
Non-repeatable read: При повторном чтении одной и той же строки в рамках одной транзакции, значения могут отличаться.
Phantom read: При повторном выполнении одного и того же запроса в одной транзакции, результат может содержать новые строки.

CAP-теорема

CAP-теорема утверждает, что в распределенной системе хранения данных можно одновременно достичь только двух из трех свойств:

Consistency (Согласованность): Все узлы системы видят одинаковые данные в одно и то же время.
Availability (Доступность): Система продолжает работать и отвечать на запросы, даже если некоторые узлы недоступны.
Partition Tolerance (Устойчивость к разделению): Система продолжает работать, даже если между узлами теряются сообщения или происходит сетевое разделение.

Почему нельзя получить всё сразу: При сетевом разделении (partition) система должна выбрать между согласованностью (consistency) и доступностью (availability).

Разница между SQL и NoSQL:

SQL (обычно CA/CP): Стремятся к строгой согласованности и доступности при нормальной работе (CA), либо к согласованности и устойчивости к разделению (CP).
NoSQL (AP): Часто жертвуют согласованностью в пользу доступности и устойчивости к разделению (AP).

BASE

BASE противоположно ACID и представляет собой подход к дизайну распределенных систем:

Basically Available (Базовая доступность): Система доступна большую часть времени.
Soft state (Мягкое состояние): Состояние системы может изменяться со временем, даже без входных данных.
Eventually consistent (В конечном итоге согласованность): Система достигает согласованности в течение некоторого времени после прекращения входных данных.

Транзакции и блокировки

Пессимистичные vs Оптимистичные блокировки

Пессимистичные блокировки:

Предполагают, что конфликты будут происходить, и блокируют ресурсы заранее.
Используются при высоком уровне конкурентности.
Примеры: SELECT ... FOR UPDATE, SELECT ... FOR SHARE
Преимущества: Гарантируют отсутствие конфликтов.
Недостатки: Могут привести к снижению производительности из-за блокировок.

Оптимистичные блокировки:

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

Deadlocks: как возникают и как их диагностировать

Deadlock (взаимоблокировка) - ситуация, когда два или более процесса ожидают освобождения ресурсов, занятых друг другом, создавая цикл ожидания.

Как возникают:

Процесс A захватывает ресурс X и ожидает ресурс Y
Процесс B захватывает ресурс Y и ожидает ресурс X
Оба процесса блокируются вечно

Как диагностировать:

Использование системных представлений (например, pg_stat_activity в PostgreSQL)
Анализ логов БД
Использование специализированных инструментов диагностики
Встроенные механизмы обнаружения взаимоблокировок (в PostgreSQL автоматическое обнаружение и устранение)

Методы предотвращения:

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

Проектирование (Normal forms, Trees)

Нормальные формы

Первая нормальная форма (1NF):

Каждая ячейка содержит только одно значение
Все значения в столбце одного типа
Столбцы имеют уникальные имена (и смысловое значение)
Не дублирующих строк

Вторая нормальная форма (2NF):

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

Третья нормальная форма (3NF):

Удовлетворяет требованиям 2NF
Нет транзитивных зависимостей (неключевые атрибуты не зависят от других неключевых атрибутов)

Нормальная форма Бойса - Кодда(BCNF):

Удовлетворяет требованиям 3NF
Ключевые атрибуты составного ключа не должны зависеть от неключевых атрибутов. Актуально только для таблиц у которых первичный ключ составной.

Когда осознанно пора делать денормализацию для производительности:

Когда запросы требуют частого объединения многих таблиц
Для аналитических нагрузок (OLAP)
Для кэширования часто используемых вычислений
При наличии четких требований к производительности чтения

Хранение деревьев

Adjacency List (Список смежности):

Каждый узел содержит ссылку на родителя
Плюсы: Простота вставки/обновления, компактность
Минусы: Требуется рекурсивный запрос или CTE для получения всей ветви дерева

Path Enumeration (Перечисление пути):

Каждый узел содержит путь ко всем своим предкам
Плюсы: Легко находить потомков и предков
Минусы: Длинные пути могут быть неэффективны, трудности при перемещении узлов

Nested Sets (Вложенные множества):

Каждый узел имеет левое и правое значение, определяющее диапазон
Плюсы: Легко находить всех потомков
Минусы: Сложные вставки и удаления, требуется перестройка при изменениях

Closure Table (Таблица замыкания):

Отдельная таблица хранит все связи "предок-потомок" на всех уровнях
Плюсы: Быстрый поиск потомков и предков
Минусы: Требует больше места, сложнее поддерживать целостность

Производительность (Indexes, Optimisation)

Типы индексов

B-Tree (Balanced Tree):

Стандартный тип индекса в большинстве СУБД
Эффективен для точного поиска, диапазонных запросов и сортировки
Поддерживает равенство и операторы сравнения (<, <=, >, >=)

Hash:

Эффективен только для операций равенства (=)
Быстрее B-tree для точного совпадения
Не поддерживает диапазонные запросы

GIN (Generalized Inverted Index):

Для данных с несколькими значениями в одной ячейке (массивы, JSONB)
Используется для поиска по ключам/значениям в JSONB
Подходит для полнотекстового поиска

GiST (Generalized Search Tree):

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

Index Scan vs Sequential Scan vs Index Only Scan

Sequential Scan:

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

Index Scan:

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

Index Only Scan:

Все необходимые данные находятся в самом индексе
Не требует доступа к основной таблице
Наиболее эффективный метод сканирования

Partitioning vs Sharding

Partitioning (Разбиение):

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

Sharding (Шардинг):

Физическое распределение данных по разным серверам
Каждый шард - отдельная БД на отдельном сервере
Требует управления на уровне приложения
Используется для масштабирования по горизонтали

Программирование в БД

Stored Procedures vs Functions

Stored Procedures (Хранимые процедуры):

Могут возвращать несколько значений или вообще не возвращать
Не обязательно возвращают значение
Могут содержать операторы DML (INSERT, UPDATE, DELETE)
Не могут использоваться в выражениях SQL
Не обязательно входят в транзакцию (в некоторых СУБД)

Functions (Функции):

Всегда возвращают значение
Не должны иметь побочных эффектов (в идеале)
Могут использоваться в выражениях SQL
Обычно запрещены операторы DML (в PostgreSQL, например)
Всегда входят в транзакцию

Views vs Materialized Views

Views (Представления):

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

Materialized Views (Материализованные представления):

Физически хранят результат запроса
Требуют периодического обновления (REFRESH)
Быстрее для сложных запросов
Могут использовать индексы
Используются для кэширования результатов сложных запросов

Explain Analyze

Explain Analyze - критический навык для Senior разработчика, позволяющий анализировать план выполнения запроса.

Как читать план запроса:

Seq Scan: Последовательное сканирование всей таблицы
Index Scan: Использование индекса для поиска строк
Index Only Scan: Поиск только в индексе, без обращения к таблице
Bitmap Heap Scan: Используется после Bitmap Index Scan для извлечения строк
Cost: Оценочные затраты (начальные / общие)
Actual time: Реальное время выполнения
Rows: Количество обработанных строк
Width: Средний размер строки в байтах

Ключевые показатели:

Planned vs Actual: Сравнение оценок планировщика с реальностью
Node Execution Count: Количество выполнений узла
Buffers: Использование буферов (shared, temp, hit ratio)

Connection Pooling

Connection Pooling (Пул соединений) - механизм управления соединениями с БД, позволяющий повторно использовать существующие соединения.

Зачем нужен:

Ограничение количества одновременных соединений к БД
Снижение накладных расходов на установку/закрытие соединений
Защита от перегрузки БД большим количеством соединений

Почему Node.js не должен открывать новое соединение на каждый запрос:

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

Популярные пулы соединений:

PgBouncer (для PostgreSQL): Легковесный пул с поддержкой различных режимов
pg-pool (для Node.js): Пул соединений для PostgreSQL
HikariCP (для Java): Высокопроизводительный пул соединений

Режимы работы пула:

Session pooling: Соединение используется одним клиентом за сессию
Transaction pooling: Соединение используется для одной транзакции
Statement pooling: Соединение используется для одного SQL-оператора

Q: Explain Storing of Trees structures for PostgreSQL.

A: # Storing Tree Structures in PostgreSQL

PostgreSQL provides several approaches to store and manage hierarchical tree data structures. Here are the main methods:

1. Adjacency List Model (Most Common)

The simplest approach stores each node with a reference to its parent.

Basic Table Structure

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INTEGER REFERENCES categories(id),
    level INTEGER
);

-- Sample data
INSERT INTO categories (name, parent_id) VALUES
('Electronics', NULL),
('Computers', 1),
('Laptops', 2),
('Desktops', 2),
('Phones', 1),
('Smartphones', 5),
('Feature Phones', 5);

Querying Operations

Get all children of a node:

-- Get all direct children
SELECT * FROM categories WHERE parent_id = 1;

-- Get all descendants (recursive)
WITH RECURSIVE tree AS (
    SELECT id, name, parent_id, 0 as level
    FROM categories 
    WHERE id = 1
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, t.level + 1
    FROM categories c
    INNER JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY level, name;

2. Path Enumeration Model

Store the complete path to each node as a string.

CREATE TABLE categories_path (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    path TEXT NOT NULL, -- e.g., '1/2/4'
    level INTEGER
);

-- Insert data
INSERT INTO categories_path (name, path, level) VALUES
('Electronics', '1', 1),
('Computers', '1/2', 2),
('Laptops', '1/2/3', 3),
('Desktops', '1/2/4', 3);

Querying Path-Based Trees

-- Get all descendants of Electronics (id=1)
SELECT * FROM categories_path 
WHERE path LIKE '1/%';

-- Get all ancestors of Laptops (id=3)
SELECT * FROM categories_path 
WHERE '1/2/3' LIKE CONCAT(path, '/%');

-- Get path hierarchy
SELECT 
    id, 
    name,
    path,
    array_to_string(string_to_array(path, '/')::integer[], ' > ') as hierarchy
FROM categories_path 
ORDER BY path;

3. Nested Set Model

Uses left and right values to represent tree structure.

CREATE TABLE categories_nested (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    lft INTEGER NOT NULL,
    rgt INTEGER NOT NULL,
    level INTEGER
);

-- Insert data with proper lft/rgt values
-- This requires careful insertion logic to maintain correct values

Advantages of Nested Set:

Fast ancestor/descendant queries
No recursion needed for subtree queries
Efficient for read-heavy operations

4. Materialized Path with Additional Features

Enhanced approach combining benefits of different models:

CREATE TABLE tree_nodes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INTEGER REFERENCES tree_nodes(id),
    path TEXT NOT NULL, -- Materialized path
    depth INTEGER NOT NULL, -- Depth from root
    is_leaf BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Create indexes for performance
CREATE INDEX idx_tree_nodes_parent ON tree_nodes(parent_id);
CREATE INDEX idx_tree_nodes_path ON tree_nodes(path);
CREATE INDEX idx_tree_nodes_depth ON tree_nodes(depth);

Advanced Query Examples

Get Full Tree Hierarchy

WITH RECURSIVE tree_hierarchy AS (
    -- Base case: root nodes
    SELECT 
        id, 
        name, 
        parent_id, 
        CAST(id AS TEXT) AS path,
        0 as level,
        TRUE as is_root
    FROM tree_nodes 
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Recursive case: child nodes
    SELECT 
        t.id,
        t.name,
        t.parent_id,
        CAST(th.path || '/' || t.id AS TEXT) AS path,
        th.level + 1 as level,
        FALSE as is_root
    FROM tree_nodes t
    INNER JOIN tree_hierarchy th ON t.parent_id = th.id
)
SELECT 
    id,
    name,
    parent_id,
    path,
    level,
    repeat('  ', level) || name as indented_name
FROM tree_hierarchy 
ORDER BY path;

Get Ancestors of a Node

WITH RECURSIVE ancestors AS (
    SELECT id, name, parent_id, 0 as level
    FROM tree_nodes 
    WHERE id = 3  -- Target node
    
    UNION ALL
    
    SELECT t.id, t.name, t.parent_id, a.level + 1
    FROM tree_nodes t
    INNER JOIN ancestors a ON t.id = a.parent_id
)
SELECT * FROM ancestors WHERE level > 0 ORDER BY level DESC;

Get Siblings

SELECT * FROM tree_nodes 
WHERE parent_id = (SELECT parent_id FROM tree_nodes WHERE id = 3)
AND id != 3;

Performance Considerations

Indexing Strategy

-- Essential indexes
CREATE INDEX idx_tree_parent_id ON tree_nodes(parent_id);
CREATE INDEX idx_tree_path ON tree_nodes(path);
CREATE INDEX idx_tree_depth ON tree_nodes(depth);

-- For frequent queries
CREATE INDEX idx_tree_name ON tree_nodes(name);
CREATE INDEX idx_tree_created_at ON tree_nodes(created_at);

Maintenance Operations

-- Update path when moving nodes
UPDATE tree_nodes 
SET path = REPLACE(path, '1/2', '1/5'),
    depth = CASE WHEN path LIKE '1/2/%' THEN depth - 1 ELSE depth END
WHERE path LIKE '1/2/%';

-- Update leaf status
UPDATE tree_nodes 
SET is_leaf = CASE WHEN id IN (SELECT parent_id FROM tree_nodes WHERE parent_id IS NOT NULL) THEN FALSE ELSE TRUE END;

Best Practices

Choose the right model based on query patterns
Use appropriate indexing for frequently queried columns
Consider using materialized views for complex hierarchical queries
Implement proper constraints to maintain data integrity
Use transactions for operations that modify multiple nodes
Monitor performance with EXPLAIN ANALYZE for complex queries

The adjacency list model is most commonly used due to its simplicity and flexibility, while path enumeration offers better performance for read-heavy scenarios.

SeLub/database_intensive_plan_ru.md

Select an option

No results found