PostgreSQL: как узнать и оптимизировать размер базы данных

-- Получение размера конкретной базы данных
SELECT pg_size_pretty(pg_database_size('имя_базы'));

-- Получение размера всех баз данных в кластере
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Размер конкретной таблицы с индексами
SELECT pg_size_pretty(pg_total_relation_size('имя_таблицы'));

-- Размер только данных таблицы без индексов
SELECT pg_size_pretty(pg_relation_size('имя_таблицы'));

-- Размер индексов таблицы
SELECT pg_size_pretty(pg_indexes_size('имя_таблицы'));

-- TOP-20 самых больших таблиц с индексами
SELECT 
n.nspname AS schema,
c.relname AS table,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
pg_size_pretty(pg_indexes_size(c.oid)) AS index_size,
pg_size_pretty(pg_total_relation_size(c.oid) – pg_relation_size(c.oid)) AS related_objects_size
FROM 
pg_class c
LEFT JOIN 
pg_namespace n ON n.oid = c.relnamespace
WHERE 
c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 
pg_total_relation_size(c.oid) DESC
LIMIT 20;

-- Создаем схему для мониторинга
CREATE SCHEMA IF NOT EXISTS monitoring;

-- Создаем таблицу для хранения исторических данных
CREATE TABLE IF NOT EXISTS monitoring.database_size_history (
id SERIAL PRIMARY KEY,
measurement_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
database_name TEXT NOT NULL,
size_bytes BIGINT NOT NULL,
size_pretty TEXT NOT NULL
);

-- Функция для сбора данных
CREATE OR REPLACE FUNCTION monitoring.collect_database_sizes()
RETURNS VOID AS $$
BEGIN
INSERT INTO monitoring.database_size_history (database_name, size_bytes, size_pretty)
SELECT 
datname, 
pg_database_size(datname), 
pg_size_pretty(pg_database_size(datname))
FROM 
pg_database;
END;
$$ LANGUAGE plpgsql;

-- Установка расширения (если ещё не установлено)
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Запуск сбора метрик каждый день в полночь
SELECT cron.schedule('0 0 * * *', 'SELECT monitoring.collect_database_sizes()');

-- Анализ роста базы данных за последний месяц
SELECT 
database_name,
measurement_time::DATE AS date,
size_pretty,
size_bytes,
(size_bytes – LAG(size_bytes) OVER (PARTITION BY database_name ORDER BY measurement_time))::FLOAT / (1024*1024) AS growth_mb_per_day
FROM 
monitoring.database_size_history
WHERE 
database_name = 'your_database'
AND measurement_time > CURRENT_DATE – INTERVAL '30 days'
ORDER BY 
database_name, measurement_time;

-- Прогноз роста на основе линейной регрессии
WITH size_data AS (
SELECT 
measurement_time,
size_bytes / (1024*1024*1024.0) AS size_gb
FROM 
monitoring.database_size_history
WHERE 
database_name = 'your_database'
AND measurement_time > CURRENT_DATE – INTERVAL '90 days'
),
stats AS (
SELECT 
REGR_SLOPE(size_gb, EXTRACT(EPOCH FROM measurement_time)) * (86400 * 30) AS monthly_growth_gb,
MAX(size_gb) AS current_size_gb
FROM 
size_data
)
SELECT 
current_size_gb,
monthly_growth_gb,
current_size_gb + (monthly_growth_gb * 3) AS projected_size_3_months,
current_size_gb + (monthly_growth_gb * 6) AS projected_size_6_months,
current_size_gb + (monthly_growth_gb * 12) AS projected_size_12_months
FROM 
stats;

-- Определение bloat в таблицах
WITH constants AS (
SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma
),
bloat_info AS (
SELECT
ma, bs, schemaname, tablename, 
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, constants
GROUP BY 1,2,3,4,5
) AS foo
),
table_bloat AS (
SELECT
schemaname, tablename, 
ROUND(
CASE WHEN tblpages – est_tblpages_ff > 0
THEN 100 * (tblpages – est_tblpages_ff)/tblpages::float
ELSE 0
END, 2) AS bloat_pct,
CASE WHEN tblpages – est_tblpages_ff > 0
THEN (tblpages – est_tblpages_ff)*bs/1024/1024 
ELSE 0
END AS bloat_mb
FROM (
SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + 
CASE WHEN ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) = 0 THEN 1 ELSE 0 END
AS est_tblpages_ff,
tblpages, bs, schemaname, tablename
FROM (
SELECT
(tblpages * fillfactor / 100) AS tblpages, 
bs,
tblpages * (1 – fillfactor/100) AS wastedpages,
schemaname, tablename, fillfactor,
CASE WHEN tblpages > 0 AND tpl_hdr_size > 0 AND tpl_data_size > 0
THEN (tpl_hdr_size + tpl_data_size)::float
ELSE 0
END AS tpl_size
FROM (
SELECT 
schemaname, tablename, bs, fillfactor,
tblpages,
(datahdr + nullhdr2 + 4 + ma – 
CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END)::float AS tpl_hdr_size,
(datawidth + nullhdr2)::float AS tpl_data_size
FROM 
bloat_info, pg_class c, pg_namespace n
WHERE c.relname = tablename
AND n.nspname = schemaname
AND n.oid = c.relnamespace
) AS foo
) AS rs
) AS rs2
)
SELECT 
schemaname, tablename, 
bloat_pct, bloat_mb
FROM 
table_bloat
WHERE bloat_pct > 30
ORDER BY bloat_mb DESC
LIMIT 10;

-- Проверка настроек автовакуума
SELECT 
name, 
setting, 
unit, 
context
FROM 
pg_settings
WHERE 
name LIKE '%autovacuum%';

-- Поиск неиспользуемых индексов
SELECT
s.schemaname,
s.relname AS tablename,
s.indexrelname AS indexname,
pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
idx_scan AS scans
FROM 
pg_stat_user_indexes s
JOIN 
pg_index i ON s.indexrelid = i.indexrelid
WHERE 
s.idx_scan = 0 -- индекс не используется
AND 0 <> ALL(i.indkey) -- не включаем индексы, созданные неявно для ограничений
AND NOT i.indisunique -- не включаем уникальные индексы
ORDER BY 
pg_relation_size(s.indexrelid) DESC;

-- Стандартный VACUUM – освобождает пространство, но не возвращает его ОС
VACUUM имя_таблицы;

-- VACUUM FULL – полностью реорганизует таблицу, возвращая пространство ОС
VACUUM FULL имя_таблицы; -- блокирует таблицу на запись!

-- VACUUM ANALYZE – выполняет очистку и обновляет статистику
VACUUM ANALYZE имя_таблицы;

-- Освобождение пространства конкретного индекса
REINDEX INDEX имя_индекса;

-- Глобальные настройки в postgresql.conf
autovacuum_vacuum_scale_factor = 0.1 -- запускать vacuum при 10% изменений
autovacuum_analyze_scale_factor = 0.05 -- запускать analyze при 5% изменений
autovacuum_vacuum_cost_limit = 1000 -- увеличение ресурсов для автовакуума

-- Настройки на уровне таблицы для интенсивно изменяемых данных
ALTER TABLE активная_таблица SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 50,
autovacuum_vacuum_cost_delay = 10
);

-- Создание партиционированной таблицы по датам
CREATE TABLE orders (
id SERIAL,
order_date DATE NOT NULL,
customer_id INTEGER NOT NULL,
total_amount NUMERIC(10,2) NOT NULL,
PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

-- Создание партиций
CREATE TABLE orders_2023_q1 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE orders_2023_q2 PARTITION OF orders
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

-- Удаление устаревших партиций (с предварительным архивированием)
DROP TABLE orders_2021_q1;

-- Настройка метода сжатия TOAST для таблицы
ALTER TABLE большой_лог ALTER COLUMN подробное_описание SET STORAGE EXTENDED;

-- Удаление неиспользуемого индекса
DROP INDEX неиспользуемый_индекс;

-- Использование частичных индексов вместо полных
CREATE INDEX idx_active_users ON users (last_login) WHERE is_active = true;

-- Применение выражений в индексах для оптимизации запросов
CREATE INDEX idx_lower_email ON users (lower(email));

import psycopg2
import time
from datetime import datetime
import pandas as pd

def collect_db_metrics(conn_string):
"""Сбор метрик о размере базы данных"""

conn = psycopg2.connect(conn_string)
cursor = conn.cursor()

# Получение общего размера базы
cursor.execute("SELECT pg_size_pretty(pg_database_size(current_database())), pg_database_size(current_database());")
db_size = cursor.fetchone()

# Получение топ-20 таблиц по размеру
cursor.execute("""
SELECT 
n.nspname AS schema,
c.relname AS table,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
pg_total_relation_size(c.oid) AS size_bytes
FROM 
pg_class c
LEFT JOIN 
pg_namespace n ON n.oid = c.relnamespace
WHERE 
c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 
pg_total_relation_size(c.oid) DESC
LIMIT 20;
""")
tables = cursor.fetchall()

# Сохранение в CSV или базу мониторинга
now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
db_metrics = {"timestamp": now, "db_size_pretty": db_size[0], "db_size_bytes": db_size[1]}

df = pd.DataFrame(db_metrics, index=[0])
df.to_csv(f"db_size_{datetime.now().strftime('%Y%m%d')}.csv", mode='a', header=False)

# Закрываем соединение
cursor.close()
conn.close()

return db_metrics

# Пример использования
if __name__ == "__main__":
conn_string = "dbname='your_db' user='your_user' password='your_pass' host='your_host'"

# Запуск сбора метрик каждые 24 часа
while True:
metrics = collect_db_metrics(conn_string)
print(f"DB size: {metrics['db_size_pretty']}")
time.sleep(86400) # 24 часа

-- Функция проверки и оповещения
CREATE OR REPLACE FUNCTION monitoring.check_database_size_thresholds()
RETURNS VOID AS $$
DECLARE
db_size_bytes BIGINT;
threshold_warning BIGINT := 1024^3 * 500; -- 500 GB
threshold_critical BIGINT := 1024^3 * 700; -- 700 GB
message TEXT;
mail_recipient TEXT := 'dba@example.com';
BEGIN
SELECT pg_database_size(current_database()) INTO db_size_bytes;

IF db_size_bytes > threshold_critical THEN
message := 'КРИТИЧНО: размер базы данных ' || current_database() || 
' достиг ' || pg_size_pretty(db_size_bytes) || 
', что превышает критический порог ' || pg_size_pretty(threshold_critical);

PERFORM pg_notify('db_size_alert', message);
-- Отправка email через dbmail или внешний обработчик

ELSIF db_size_bytes > threshold_warning THEN
message := 'ПРЕДУПРЕЖДЕНИЕ: размер базы данных ' || current_database() || 
' достиг ' || pg_size_pretty(db_size_bytes) || 
', что превышает порог предупреждения ' || pg_size_pretty(threshold_warning);

PERFORM pg_notify('db_size_warning', message);
END IF;

-- Логируем событие
INSERT INTO monitoring.size_check_log (check_time, database_name, size_bytes, threshold_exceeded)
VALUES (now(), current_database(), db_size_bytes, 
CASE WHEN db_size_bytes > threshold_warning THEN TRUE ELSE FALSE END);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION maintenance.auto_vacuum_bloated_tables()
RETURNS VOID AS $$
DECLARE
table_record RECORD;
BEGIN
FOR table_record IN 
-- Запрос выявления таблиц с bloat > 30%
SELECT schemaname, tablename 
FROM monitoring.bloat_stats
WHERE bloat_ratio > 30
ORDER BY bloat_mb DESC
LIMIT 5
LOOP
EXECUTE 'VACUUM ANALYZE ' || quote_ident(table_record.schemaname) || '.' || quote_ident(table_record.tablename);

-- Логируем операцию
INSERT INTO maintenance.vacuum_log (vacuum_time, schema_name, table_name)
VALUES (NOW(), table_record.schemaname, table_record.tablename);
END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Создание расписания обслуживания
CREATE TABLE maintenance.schedule (
id SERIAL PRIMARY KEY,
task_name TEXT NOT NULL,
task_sql TEXT NOT NULL,
frequency INTERVAL NOT NULL,
last_run TIMESTAMP WITH TIME ZONE,
enabled BOOLEAN DEFAULT TRUE
);

-- Добавление задач в расписание
INSERT INTO maintenance.schedule (task_name, task_sql, frequency)
VALUES 
('Vacuum Bloated Tables', 'SELECT maintenance.auto_vacuum_bloated_tables()', '1 day'),
('Reindex Fragmented Indexes', 'SELECT maintenance.auto_reindex_fragmented_indexes()', '1 week'),
('Check Storage Thresholds', 'SELECT monitoring.check_database_size_thresholds()', '6 hours');