Продолжение и практическое применение
Для PostgreSQL использование семантических паттернов особенно эффективно благодаря богатой экосистеме и расширяемости.
Вот конкретные применения:
1. Автоматическая оптимизация индексов
Гипотетические индексы на основе паттернов
-- Анализ частых предикатов
SELECT schemaname, tablename, attname,
count(*) as usage_count
FROM pg_stat_all_indexes
WHERE idx_scan > 1000
GROUP BY schemaname, tablename, attname;
-- Создание рекомендаций
CREATE INDEX CONCURRENTLY IF NOT EXISTS
idx_users_email_active ON users(email) WHERE active = true;
Составные индексы для частых комбинаций
-- Паттерн: WHERE tenant_id = ? AND created_at > ? ORDER BY id DESC
CREATE INDEX CONCURRENTLY idx_orders_tenant_created_id
ON orders(tenant_id, created_at DESC, id DESC);
-- Паттерн: WHERE status IN ('pending','processing') AND priority > 0
CREATE INDEX CONCURRENTLY idx_tasks_status_priority
ON tasks(status, priority)
WHERE status IN ('pending','processing') AND priority > 0;
2. Оптимизация партиционирования
Автоматическое определение ключей партиционирования
-- На основе временных паттернов
CREATE TABLE logs (
id BIGSERIAL,
created_at TIMESTAMPTZ,
level TEXT,
message TEXT
) PARTITION BY RANGE (created_at);
-- Создание партиций по месяцам
CREATE TABLE logs_2024_01 PARTITION OF logs
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
Партиционирование по хэшу для равномерного распределения
-- Паттерн: частые запросы по tenant_id с равномерным распределением
CREATE TABLE events (
id BIGSERIAL,
tenant_id INT,
event_type TEXT,
payload JSONB
) PARTITION BY HASH (tenant_id);
-- Создание 4 партиций
CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
3. Интеллектуальное материализованные представления
Автообновляемые summary-таблицы
-- Паттерн: частые агрегации по дням
CREATE MATERIALIZED VIEW daily_metrics AS
SELECT
date_trunc('day', created_at) as day,
COUNT(*) as total_orders,
SUM(amount) as total_amount,
COUNT(DISTINCT user_id) as unique_users
FROM orders
GROUP BY date_trunc('day', created_at);
-- Создание индекса для быстрого обновления
CREATE UNIQUE INDEX idx_daily_metrics_day ON daily_metrics(day);
-- Автоматическое обновление по расписанию (cron)
-- 0 2 * * * psql -c "REFRESH MATERIALIZED VIEW CONCURRENTLY daily_metrics;"
Частичные материализованные представления
-- Паттерн: запросы только к активным пользователям
CREATE MATERIALIZED VIEW active_users_summary AS
SELECT
u.id,
u.email,
COUNT(o.id) as order_count,
SUM(o.amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true AND u.last_login > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email;
CREATE UNIQUE INDEX idx_active_users_id ON active_users_summary(id);
4. Расширенная статистика для планировщика
Создание статистики по корреляциям
-- Для паттернов с множественными условиями
CREATE STATISTICS orders_status_created_corr
ON status, created_at FROM orders;
CREATE STATISTICS users_tenant_active_corr
ON tenant_id, active, last_login FROM users;
-- Обновление статистики
ANALYZE orders;
ANALYZE users;
Статистика по выражениям
-- Паттерн: частые запросы по датам (извлечение месяца/года)
CREATE STATISTICS orders_date_expr_stats
ON (EXTRACT(MONTH FROM created_at)),
(EXTRACT(YEAR FROM created_at))
FROM orders;
5. Интеллектуальное кэширование с pgpool-II
# pgpool.conf на основе паттернов
memory_cache_enabled = on
memqcache_method = 'shmem'
memqcache_total_size = 64MB
memqcache_max_num_cache = 1000000
memqcache_auto_cache_invalidation = on
# Белый список паттернов для кэширования
memqcache_cache_white_list = '^SELECT.*FROM orders.*WHERE user_id=.*'
memqcache_cache_white_list = '^SELECT.*FROM users.*WHERE email=.*'
Распознавание и маршрутизация read-only запросов
-- Паттерн: идентификация read-only workload
SELECT query,
CASE WHEN query ~* '^SELECT' THEN 'read_replica'
ELSE 'primary'
END as target_server
FROM pg_stat_statements;
6. Оптимизация работы с JSONB
-- Паттерн: частый поиск по JSONB полям
CREATE INDEX idx_users_metadata_gin ON users
USING GIN (metadata);
-- Частый доступ к конкретным JSONB путям
CREATE INDEX idx_users_metadata_email ON users
USING GIN ((metadata->>'email'));
-- Оптимизация для существования ключей
CREATE INDEX idx_users_metadata_preferences ON users
USING GIN ((metadata->'preferences'));
Частичные индексы для JSONB условий
-- Паттерн: запросы только к пользователям с определенными настройками
CREATE INDEX idx_users_premium ON users
USING GIN (metadata)
WHERE metadata->>'account_type' = 'premium';
7. Автоматическая настройка параметров
Динамическая настройка на основе паттернов
-- Анализ рабочей нагрузки
SELECT
CASE WHEN query LIKE '%INSERT%' THEN 'write_heavy'
WHEN query LIKE '%SELECT%' THEN 'read_heavy'
ELSE 'mixed'
END as workload_type,
COUNT(*) as query_count
FROM pg_stat_statements
GROUP BY workload_type;
-- Рекомендации по настройке
workload_recommendations = {
'read_heavy': {
'shared_buffers': '25% of RAM',
'work_mem': '256MB',
'maintenance_work_mem': '1GB',
'effective_cache_size': '75% of RAM'
},
'write_heavy': {
'shared_buffers': '20% of RAM',
'wal_buffers': '16MB',
'checkpoint_timeout': '30min',
'max_wal_size': '10GB'
}
}
8. Проактивное обслуживание
Авто-вакуумирование на основе паттернов
-- Мониторинг необходимости вакуумирования
SELECT
schemaname, tablename,
n_tup_ins, n_tup_upd, n_tup_del,
n_live_tup, n_dead_tup,
round(n_dead_tup::numeric / (n_live_tup + n_dead_tup), 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
AND round(n_dead_tup::numeric / (n_live_tup + n_dead_tup), 2) > 0.1;
-- Автоматическая настройка autovacuum
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.05,
autovacuum_analyze_scale_factor = 0.02,
autovacuum_vacuum_cost_limit = 1000
);
9. Расширенный мониторинг с временными рядами
Интеграция с TimescaleDB для метрик
-- Создание гипертаблицы для метрик запросов
CREATE TABLE query_metrics (
time TIMESTAMPTZ NOT NULL,
query_pattern TEXT,
execution_time DOUBLE PRECISION,
rows_returned BIGINT
);
SELECT create_hypertable('query_metrics', 'time');
-- Автоматическое агрегирование метрик
CREATE MATERIALIZED VIEW query_patterns_hourly
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 hour', time) as bucket,
query_pattern,
percentile_agg(execution_time),
COUNT(*) as execution_count
FROM query_metrics
GROUP BY bucket, query_pattern;
10. Безопасность и аудит
Обнаружение подозрительных паттернов
-- Создание политик на основе паттернов доступа
CREATE POLICY user_data_access ON users
FOR ALL TO application_user
USING (tenant_id = current_setting('app.current_tenant_id')::INT);
-- Аудит необычных паттернов доступа
CREATE TABLE audit_suspicious_queries (
id SERIAL PRIMARY KEY,
query_text TEXT,
executed_at TIMESTAMPTZ DEFAULT NOW(),
user_name TEXT,
pattern_type TEXT -- 'mass_export', 'sensitive_access', etc.
);
11. Интеллектуальные подсказки для планировщика
Принудительное использование индексов для известных паттернов
-- Для паттернов, где планировщик ошибается
SET LOCAL pg_hint_plan.enable_hint = ON;
/*+ IndexScan(users users_email_idx) */
SELECT * FROM users WHERE email = 'test@example.com';
Глобальные настройки для частых паттернов
-- Увеличение work_mem для сложных сортировок
SET work_mem = '256MB';
-- Отключение последовательного сканирования для маленьких таблиц
SET enable_seqscan = off;
12. Автоматизация с помощью расширений
Использование pg_stat_statements для анализа
-- Установка расширения
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Анализ самых частых паттернов
SELECT
queryid,
query,
calls,
total_time,
mean_time,
rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;
Интеграция с pg_qualstats для анализа предикатов
-- Установка расширения для анализа условий
CREATE EXTENSION pg_qualstats;
-- Поиск самых частых предикатов для индексации
SELECT
left(query, 100) as query_prefix,
qualnode,
count(*) as execution_count
FROM pg_qualstats
WHERE qualnode IS NOT NULL
GROUP BY query, qualnode
ORDER BY execution_count DESC;
Реализация системы мониторинга
Таблица для хранения паттернов
CREATE TABLE query_patterns (
id BIGSERIAL PRIMARY KEY,
pattern_hash TEXT UNIQUE,
pattern_template TEXT,
tables_accessed TEXT[],
filters_used JSONB,
joins_used TEXT[],
frequency INTEGER DEFAULT 0,
avg_execution_time DOUBLE PRECISION,
last_seen TIMESTAMPTZ
);
CREATE INDEX idx_patterns_hash ON query_patterns(pattern_hash);
CREATE INDEX idx_patterns_frequency ON query_patterns(frequency);
Функция для автоматической оптимизации
CREATE OR REPLACE FUNCTION auto_optimize_based_on_patterns()
RETURNS VOID AS $$
DECLARE
pattern_record RECORD;
BEGIN
FOR pattern_record IN
SELECT * FROM query_patterns
WHERE frequency > 1000
AND last_seen > NOW() - INTERVAL '7 days'
LOOP
-- Автоматическое создание индексов
IF pattern_record.filters_used IS NOT NULL THEN
EXECUTE format(
'CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_auto_%s ON %s (%s)',
md5(pattern_record.pattern_hash),
pattern_record.tables_accessed[1],
array_to_string(pattern_record.filters_used->>'columns', ',')
);
END IF;
-- Логирование действий
INSERT INTO optimization_log
VALUES (pattern_record.pattern_hash, 'INDEX_CREATED', NOW());
END LOOP;
END;
$$ LANGUAGE plpgsql;
Практический пример работы
Обнаружение паттерна:
-- В логах часто встречается:
SELECT * FROM orders
WHERE user_id = $1 AND status = $2
ORDER BY created_at DESC
LIMIT 50;
Автоматическое действие:
-- Создание оптимального индекса
CREATE INDEX CONCURRENTLY idx_orders_user_status_created
ON orders(user_id, status, created_at DESC);
-- Настройка autovacuum для таблицы
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_analyze_scale_factor = 0.005
);