Таблицы и индексы в PostgreSQL: полное руководство по созданию и оптимизации

-- Создание таблицы employees
CREATE TABLE employees (
    -- Целочисленный первичный ключ с автоинкрементом
    id SERIAL PRIMARY KEY,
    
    -- Текстовые поля с ограничением длины
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    
    -- Дата
    hire_date DATE NOT NULL,
    
    -- Числовое поле с точностью
    salary NUMERIC(10, 2) CHECK (salary > 0),
    
    -- Внешний ключ (будет создана позже)
    department_id INTEGER,
    
    -- Логическое поле
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    
    -- Метки времени
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    
    -- Ограничение уникальности
    CONSTRAINT uk_employees_email UNIQUE (email),
    
    -- Ограничение проверки
    CONSTRAINT chk_employees_hire_date CHECK (hire_date <= CURRENT_DATE),
    
    -- Комментарии для документации
    CONSTRAINT employees_pkey PRIMARY KEY (id)
);

-- Комментарий к таблице
COMMENT ON TABLE employees IS 'Таблица сотрудников компании';

-- Комментарии к столбцам
COMMENT ON COLUMN employees.id IS 'Уникальный идентификатор сотрудника';
COMMENT ON COLUMN employees.first_name IS 'Имя сотрудника';
COMMENT ON COLUMN employees.last_name IS 'Фамилия сотрудника';
COMMENT ON COLUMN employees.email IS 'Электронная почта сотрудника';
COMMENT ON COLUMN employees.hire_date IS 'Дата приема на работу';
COMMENT ON COLUMN employees.salary IS 'Заработная плата';
COMMENT ON COLUMN employees.is_active IS 'Статус активности (TRUE - активен)';
COMMENT ON COLUMN employees.created_at IS 'Дата создания записи';
COMMENT ON COLUMN employees.updated_at IS 'Дата последнего обновления';

-- Просмотр всех таблиц в схеме public
SELECT 
    tablename,
    tableowner,
    hasindexes,
    hasrules,
    hastriggers
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY tablename;

-- Детальная информация о таблице employees
SELECT 
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'employees'
ORDER BY ordinal_position;

-- Целочисленные
id INTEGER PRIMARY KEY,        -- -2147483648 до +2147483647
small_number SMALLINT,         -- -32768 до +32767
big_number BIGINT,             -- -9223372036854775808 до 9223372036854775807
serial_number SERIAL,          -- Автоинкремент 1 до 2147483647

-- Числа с плавающей точкой
price DECIMAL(10, 2),          -- Точные десятичные (10 цифр, 2 после запятой)
rating NUMERIC(4, 2),          -- Аналогично DECIMAL
approx_value REAL,             -- 6 десятичных цифр точности
scientific DOUBLE PRECISION,   -- 15 десятичных цифр точности

-- С фиксированной длиной
country_code CHAR(2),          -- Ровно 2 символа, дополняется пробелами

-- С переменной длиной
username VARCHAR(50),          -- До 50 символов, оптимально для текста
description TEXT,              -- Неограниченная длина (до 1 ГБ)

-- Специальные текстовые типы
xml_data XML,                  -- XML документы
json_data JSON,                -- JSON данные (валидация)
jsonb_data JSONB,              -- Бинарный JSON (индексируемый)

-- Дата
birth_date DATE,               -- Только дата (год-месяц-день)

-- Время
work_start TIME,               -- Только время (часы:минуты:секунды)
work_end TIME WITH TIME ZONE,  -- Время с часовым поясом

-- Дата и время
created_at TIMESTAMP,          -- Дата и время без часового пояса
updated_at TIMESTAMPTZ,        -- Дата и время с часовым поясом (рекомендуется)

-- Интервалы
duration INTERVAL,             -- Промежуток времени
subscription_period INTERVAL YEAR TO MONTH,

-- Специальные
tsvector_data TSVECTOR,        -- Для полнотекстового поиска
uuid_value UUID,               -- Уникальные идентификаторы

is_active BOOLEAN,             -- TRUE/FALSE/NULL
is_verified BOOLEAN DEFAULT FALSE,
requires_approval BOOLEAN NOT NULL DEFAULT TRUE,

-- Для хранения файлов
document BYTEA,                -- Бинарные данные (до 1 ГБ)
signature BYTEA,               -- Подписи, изображения
large_file LARGE OBJECT,       -- Большие бинарные объекты (до 4 ТБ)

-- Требует расширение PostGIS
CREATE EXTENSION postgis;

location GEOMETRY(POINT, 4326),      -- Точка на карте (долгота, широта)
area GEOMETRY(POLYGON, 4326),        -- Полигональная область
route GEOMETRY(LINESTRING, 4326),    -- Линия (маршрут)

-- Для сравнений: =, <, >, <=, >=, BETWEEN, IN
CREATE INDEX idx_employees_last_name ON employees(last_name);

-- Только для равенства (=)
CREATE INDEX idx_employees_email_hash ON employees USING HASH (email);

-- Для геоданных, полнотекстового поиска, массивов
CREATE INDEX idx_locations_geom ON locations USING GIST (geometry);

-- Для составных значений: массивы, JSONB, полнотекстовый поиск
CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_documents_content ON documents USING GIN (to_tsvector('english', content));

-- Для неоднородных данных: IP-адреса, геоданные
CREATE INDEX idx_network_ips ON network_logs USING SPGIST (ip_address inet_ops);

-- Для очень больших таблиц с естественной сортировкой
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (created_at);

-- Базовый индекс (B-tree по умолчанию)
CREATE INDEX idx_employees_last_name ON employees(last_name);

-- Уникальный индекс (гарантирует уникальность)
CREATE UNIQUE INDEX uk_employees_email ON employees(email);

-- Составной индекс (несколько столбцов)
CREATE INDEX idx_employees_name_department ON employees(last_name, first_name, department_id);

-- Частичный индекс (только для подмножества данных)
CREATE INDEX idx_employees_active ON employees(id) WHERE is_active = TRUE;

-- Функциональный индекс (на основе выражения)
CREATE INDEX idx_employees_lower_email ON employees(LOWER(email));
CREATE INDEX idx_employees_hire_year ON employees(EXTRACT(YEAR FROM hire_date));

-- Индекс с указанием порядка сортировки
CREATE INDEX idx_employees_salary_desc ON employees(salary DESC NULLS LAST);

-- Покрывающий индекс (INCLUDE дополнительные столбцы)
CREATE INDEX idx_employees_covering ON employees(department_id) INCLUDE (first_name, last_name, salary);

-- GIN индекс для JSONB
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- GiST индекс для геоданных
CREATE INDEX idx_buildings_location ON buildings USING GIST (location);

-- BRIN индекс для временных рядов
CREATE INDEX idx_sensor_readings_time ON sensor_readings USING BRIN (reading_time);

-- Создание индекса без блокировки (CONCURRENTLY)
CREATE INDEX CONCURRENTLY idx_employees_department_id ON employees(department_id);

-- Все индексы таблицы employees
SELECT 
    indexname,
    indexdef,
    indisunique,
    indisprimary
FROM pg_indexes
WHERE tablename = 'employees'
ORDER BY indexname;

-- Детальная информация об индексах
SELECT 
    i.relname as index_name,
    a.attname as column_name,
    am.amname as index_type,
    idx.indisunique as is_unique,
    idx.indisprimary as is_primary
FROM pg_index idx
JOIN pg_class i ON i.oid = idx.indexrelid
JOIN pg_class t ON t.oid = idx.indrelid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(idx.indkey)
JOIN pg_am am ON i.relam = am.oid
WHERE t.relname = 'employees'
ORDER BY i.relname, array_position(idx.indkey, a.attnum);

-- Размер индексов
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(schemaname||'.'||indexname)) as index_size
FROM pg_indexes
WHERE tablename = 'employees'
ORDER BY pg_relation_size(schemaname||'.'||indexname) DESC;

-- Использование индексов (требует pg_stat_statements)
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
WHERE tablename = 'employees'
ORDER BY idx_scan DESC;

-- Для часто используемых фильтров
CREATE INDEX idx_employees_department_active 
ON employees(department_id) 
WHERE is_active = TRUE;

-- Для диапазонных запросов
CREATE INDEX idx_employees_salary_range 
ON employees(salary, hire_date);

-- Внешние ключи должны быть проиндексированы
CREATE INDEX idx_employees_department_id ON employees(department_id);
CREATE INDEX idx_departments_id ON departments(id);

-- Индекс должен соответствовать порядку сортировки
CREATE INDEX idx_employees_order 
ON employees(department_id, last_name, first_name);

-- Неиспользуемые индексы
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(schemaname||'.'||indexname)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(schemaname||'.'||indexname) DESC;

-- Дублирующиеся индексы
SELECT 
    indrelid::regclass as table_name,
    array_agg(indexrelid::regclass) as duplicate_indexes,
    count(*)
FROM pg_index
GROUP BY indrelid, indkey
HAVING count(*) > 1;

-- Индексы с низкой эффективностью
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    CASE 
        WHEN idx_scan > 0 
        THEN round((idx_tup_fetch::numeric / idx_tup_read) * 100, 2)
        ELSE 0 
    END as efficiency_percent
FROM pg_stat_user_indexes
WHERE idx_scan > 0
ORDER BY efficiency_percent;

-- Перестроение индекса (для фрагментации)
REINDEX INDEX idx_employees_last_name;

-- Перестроение всех индексов таблицы
REINDEX TABLE employees;

-- Анализ использования индекса
EXPLAIN ANALYZE SELECT * FROM employees WHERE last_name = 'Иванов';

-- Параллельное создание индексов
CREATE INDEX CONCURRENTLY idx_employees_parallel ON employees(salary);

-- Таблица пользователей
CREATE TABLE users (
    -- Идентификатор
    user_id BIGSERIAL PRIMARY KEY,
    
    -- Основная информация
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    
    -- Демографические данные
    birth_date DATE CHECK (birth_date <= CURRENT_DATE - INTERVAL '18 years'),
    gender CHAR(1) CHECK (gender IN ('M', 'F', 'O')),
    country_code CHAR(2),
    
    -- Статус и активность
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_verified BOOLEAN NOT NULL DEFAULT FALSE,
    is_blocked BOOLEAN NOT NULL DEFAULT FALSE,
    
    -- Предпочтения (храним как JSONB)
    preferences JSONB DEFAULT '{}'::jsonb,
    
    -- Аудит
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ,
    last_login_at TIMESTAMPTZ,
    login_count INTEGER DEFAULT 0,
    
    -- Комментарии
    CONSTRAINT users_pkey PRIMARY KEY (user_id),
    CONSTRAINT chk_username_length CHECK (LENGTH(username) >= 3),
    CONSTRAINT chk_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

-- Индексы для таблицы users
CREATE UNIQUE INDEX uk_users_username ON users(LOWER(username));
CREATE UNIQUE INDEX uk_users_email ON users(LOWER(email));
CREATE INDEX idx_users_country_active ON users(country_code) WHERE is_active = TRUE;
CREATE INDEX idx_users_created_at ON users(created_at DESC);
CREATE INDEX idx_users_preferences ON users USING GIN (preferences);

-- Комментарии
COMMENT ON TABLE users IS 'Таблица зарегистрированных пользователей системы';
COMMENT ON COLUMN users.preferences IS 'Пользовательские настройки в формате JSON';
COMMENT ON COLUMN users.password_hash IS 'Хеш пароля (bcrypt/scrypt)';

-- Таблица заказов
CREATE TABLE orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    -- Связи
    user_id BIGINT NOT NULL REFERENCES users(user_id),
    
    -- Информация о заказе
    order_number VARCHAR(20) NOT NULL UNIQUE,
    order_status VARCHAR(20) NOT NULL DEFAULT 'pending' 
        CHECK (order_status IN ('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled')),
    
    -- Адрес доставки (храним как JSONB для гибкости)
    shipping_address JSONB NOT NULL,
    billing_address JSONB,
    
    -- Финансы
    subtotal NUMERIC(10, 2) NOT NULL CHECK (subtotal >= 0),
    tax_amount NUMERIC(10, 2) NOT NULL DEFAULT 0 CHECK (tax_amount >= 0),
    shipping_cost NUMERIC(10, 2) NOT NULL DEFAULT 0 CHECK (shipping_cost >= 0),
    discount_amount NUMERIC(10, 2) NOT NULL DEFAULT 0 CHECK (discount_amount >= 0),
    total_amount NUMERIC(10, 2) NOT NULL 
        GENERATED ALWAYS AS (subtotal + tax_amount + shipping_cost - discount_amount) STORED,
    
    -- Оплата
    payment_method VARCHAR(50),
    payment_status VARCHAR(20) DEFAULT 'unpaid'
        CHECK (payment_status IN ('unpaid', 'pending', 'paid', 'failed', 'refunded')),
    paid_at TIMESTAMPTZ,
    
    -- Доставка
    shipping_method VARCHAR(50),
    tracking_number VARCHAR(100),
    estimated_delivery DATE,
    delivered_at TIMESTAMPTZ,
    
    -- Аудит
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ,
    cancelled_at TIMESTAMPTZ,
    cancellation_reason TEXT,
    
    -- Ограничения
    CONSTRAINT orders_pkey PRIMARY KEY (order_id),
    CONSTRAINT chk_dates CHECK (
        (delivered_at IS NULL OR delivered_at >= created_at) AND
        (cancelled_at IS NULL OR cancelled_at >= created_at) AND
        (paid_at IS NULL OR paid_at >= created_at)
    )
);

-- Индексы для таблицы orders
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(order_status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
CREATE INDEX idx_orders_user_status ON orders(user_id, order_status);
CREATE INDEX idx_orders_payment_status ON orders(payment_status) WHERE payment_status != 'paid';
CREATE INDEX idx_orders_delivery_date ON orders(estimated_delivery) WHERE order_status = 'shipped';

-- Частичный индекс для активных заказов
CREATE INDEX idx_orders_active ON orders(order_id) 
WHERE order_status NOT IN ('delivered', 'cancelled');

-- Комментарии
COMMENT ON TABLE orders IS 'Таблица заказов интернет-магазина';
COMMENT ON COLUMN orders.shipping_address IS 'Адрес доставки в формате JSON';
COMMENT ON COLUMN orders.total_amount IS 'Итоговая сумма (вычисляемое поле)';

-- Таблица продуктов
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    
    -- Основная информация
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    short_description VARCHAR(500),
    
    -- Категоризация
    category_id INTEGER NOT NULL REFERENCES categories(category_id),
    brand_id INTEGER REFERENCES brands(brand_id),
    
    -- Цены и наличие
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    compare_at_price NUMERIC(10, 2) CHECK (compare_at_price >= 0),
    cost_price NUMERIC(10, 2) CHECK (cost_price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    low_stock_threshold INTEGER DEFAULT 10,
    
    -- Вариации (размер, цвет и т.д.)
    variations JSONB DEFAULT '[]'::jsonb,
    
    -- Атрибуты (вес, размеры и т.д.)
    weight_kg DECIMAL(8, 3),
    dimensions JSONB, -- {width: 10, height: 20, depth: 15}
    
    -- Медиа
    main_image_url VARCHAR(500),
    image_gallery JSONB DEFAULT '[]'::jsonb,
    
    -- SEO
    seo_title VARCHAR(200),
    seo_description VARCHAR(500),
    seo_keywords VARCHAR(300),
    slug VARCHAR(200) NOT NULL UNIQUE,
    
    -- Рейтинг и статистика
    average_rating DECIMAL(3, 2) DEFAULT 0 CHECK (average_rating >= 0 AND average_rating <= 5),
    review_count INTEGER DEFAULT 0 CHECK (review_count >= 0),
    sales_count INTEGER DEFAULT 0 CHECK (sales_count >= 0),
    
    -- Статус
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    is_featured BOOLEAN NOT NULL DEFAULT FALSE,
    is_virtual BOOLEAN NOT NULL DEFAULT FALSE, -- Цифровой товар
    
    -- Аудит
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ,
    published_at TIMESTAMPTZ,
    
    -- Полнотекстовый поиск
    search_vector TSVECTOR,
    
    -- Ограничения
    CONSTRAINT products_pkey PRIMARY KEY (product_id),
    CONSTRAINT chk_prices CHECK (price <= COALESCE(compare_at_price, price)),
    CONSTRAINT chk_stock CHECK (
        (is_virtual = TRUE AND stock_quantity = 0) OR 
        (is_virtual = FALSE AND stock_quantity >= 0)
    )
);

-- Индексы для таблицы products
CREATE UNIQUE INDEX uk_products_sku ON products(sku);
CREATE UNIQUE INDEX uk_products_slug ON products(slug);
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_brand ON products(brand_id);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_stock ON products(stock_quantity) WHERE stock_quantity > 0;
CREATE INDEX idx_products_active ON products(product_id) WHERE is_active = TRUE;
CREATE INDEX idx_products_featured ON products(product_id) WHERE is_featured = TRUE;

-- Составной индекс для частых запросов
CREATE INDEX idx_products_category_price ON products(category_id, price, is_active);

-- GIN индексы для JSONB полей
CREATE INDEX idx_products_variations ON products USING GIN (variations);
CREATE INDEX idx_products_dimensions ON products USING GIN (dimensions);
CREATE INDEX idx_products_image_gallery ON products USING GIN (image_gallery);

-- GiST индекс для полнотекстового поиска
CREATE INDEX idx_products_search ON products USING GIST (search_vector);

-- Триггер для обновления search_vector
CREATE TRIGGER trg_products_search_vector
    BEFORE INSERT OR UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION
    tsvector_update_trigger(search_vector, 'pg_catalog.russian', name, description, short_description);

-- Комментарии
COMMENT ON TABLE products IS 'Таблица товаров интернет-магазина';
COMMENT ON COLUMN products.search_vector IS 'Вектор для полнотекстового поиска';
COMMENT ON COLUMN products.variations IS 'Вариации товара (размер, цвет) в формате JSON';

-- Мониторинг производительности
-- Медленные запросы
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Наиболее активные таблицы
SELECT 
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan + idx_scan DESC;

-- Размер таблиц и индексов
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - 
                   pg_relation_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Регулярное обслуживание
-- Анализ таблиц (обновление статистики)
ANALYZE employees;

-- Очистка (удаление мертвых записей)
VACUUM ANALYZE employees;

-- Полная очистка с блокировкой
VACUUM FULL employees;

-- Перестроение индексов
REINDEX TABLE employees;

-- Проверка целостности
CHECKPOINT;