2) «админ-скрипты как у взрослых»

-- ==============================================

-- ADMIN SCRIPTS для схемы lab_admindb (PostgreSQL)

-- Работает в песочницах: без суперпользователя и расширений

-- ==============================================

-- На всякий случай — работаем в нашей схеме

SET search_path TO lab_admindb, public;

-- 0) Общая диагностика соединения/версии (быстрый sanity check)

SELECT current_user   AS whoami,

       current_database() AS dbname,

       version()      AS pg_version,

       now()          AS ts;

-- --------------------------------------------------------------

-- 1) Инвентаризация объектов и размеры

-- --------------------------------------------------------------

-- 1.1 Список пользовательских таблиц в нашей схеме

SELECT table_schema, table_name

FROM information_schema.tables

WHERE table_schema='lab_admindb' AND table_type='BASE TABLE'

ORDER BY table_name;

-- 1.2 Размеры ключевых таблиц (heap+index+toast)

SELECT relname AS table_name,

       pg_size_pretty(pg_total_relation_size(format('%I.%I','lab_admindb',relname)::regclass)) AS total_size,

       pg_size_pretty(pg_relation_size      (format('%I.%I','lab_admindb',relname)::regclass)) AS heap_size

FROM pg_class

JOIN pg_namespace n ON n.oid = pg_class.relnamespace

WHERE n.nspname='lab_admindb' AND relkind='r'

ORDER BY pg_total_relation_size(format('%I.%I','lab_admindb',relname)::regclass) DESC;

-- 1.3 Быстрые кардинальности (примерно; для крупных таблиц полезно)

SELECT relname AS table_name,

       reltuples::bigint AS approx_rows

FROM pg_class

JOIN pg_namespace n ON n.oid=pg_class.relnamespace

WHERE n.nspname='lab_admindb' AND relkind='r'

ORDER BY approx_rows DESC;

-- --------------------------------------------------------------

-- 2) Индексы и их использование

-- --------------------------------------------------------------

-- 2.1 Индексы по пользовательским таблицам

SELECT t.relname AS table_name, i.relname AS index_name, pg_get_indexdef(x.indexrelid) AS index_def

FROM pg_index x

JOIN pg_class i ON i.oid = x.indexrelid

JOIN pg_class t ON t.oid = x.indrelid

JOIN pg_namespace n ON n.oid = t.relnamespace

WHERE n.nspname='lab_admindb'

ORDER BY t.relname, i.relname;

-- 2.2 Статистика обращений: seq_scan vs idx_scan (кандидаты на ревизию индексов)

SELECT relname AS table_name,

       seq_scan, idx_scan,

       (CASE WHEN (seq_scan+idx_scan)=0 THEN NULL

             ELSE round(100.0*idx_scan/(seq_scan+idx_scan),1) END) AS idx_ratio_pct

FROM pg_stat_user_tables

ORDER BY idx_ratio_pct NULLS FIRST, relname;

-- 2.3 Индексы без обращений (за время жизни статистики)

SELECT s.relname AS table_name, s.indexrelname AS index_name, s.idx_scan

FROM pg_stat_user_indexes s

JOIN pg_class t ON t.oid = s.relid

JOIN pg_namespace n ON n.oid=t.relnamespace

WHERE n.nspname='lab_admindb' AND s.idx_scan = 0

ORDER BY 1,2;

-- --------------------------------------------------------------

-- 3) Планы и производительность запросов (EXPLAIN/ANALYZE)

-- --------------------------------------------------------------

-- 3.1 Типовой запрос по заказам (план без выполнения)

EXPLAIN

SELECT o.order_id, c.email, o.total_cents,

       SUM(oi.qty*oi.price_cents) AS line_sum

FROM orders o

JOIN customers c  ON c.customer_id  = o.customer_id

LEFT JOIN order_items oi ON oi.order_id = o.order_id

GROUP BY o.order_id, c.email, o.total_cents

ORDER BY o.order_id;

-- 3.2 Реальный замер (в песочнице будет быстро, но форма правильная)

EXPLAIN ANALYZE

SELECT o.order_id, c.email, o.total_cents

FROM orders o

JOIN customers c ON c.customer_id = o.customer_id

WHERE o.total_cents >= 2000

ORDER BY o.order_id;

-- --------------------------------------------------------------

-- 4) Сессии, долгие транзакции, блокировки

-- --------------------------------------------------------------

-- 4.1 Активные пользовательские запросы (без idle)

SELECT pid, usename, now()-xact_start AS xact_age, state, query

FROM pg_stat_activity

WHERE backend_type='client backend' AND state <> 'idle'

ORDER BY xact_age DESC NULLS LAST;

-- 4.2 Блокировки (кто держит/ждёт)

SELECT a.pid, a.usename, l.locktype, l.mode, l.granted, a.query

FROM pg_locks l

JOIN pg_stat_activity a ON a.pid = l.pid

ORDER BY l.granted, l.mode;

-- (при необходимости) аккуратно отменить запрос:

-- SELECT pg_cancel_backend(<pid>);

-- (жёсткая остановка — крайний случай)

-- SELECT pg_terminate_backend(<pid>);

-- --------------------------------------------------------------

-- 5) «Здоровье» таблиц: мёртвые строки, автovacuum

-- --------------------------------------------------------------

-- 5.1 Кандидаты на VACUUM/ANALYZE (по доле dead tuples)

SELECT relname AS table_name,

       n_live_tup, n_dead_tup,

       round(100.0 * n_dead_tup / NULLIF(n_live_tup+n_dead_tup,0), 1) AS dead_pct,

       vacuum_count, autovacuum_count, analyze_count, autoanalyze_count

FROM pg_stat_all_tables

WHERE schemaname='lab_admindb'

ORDER BY n_dead_tup DESC;

-- 5.2 Ручное обслуживание (в песочнице допустимо)

-- VACUUM (ANALYZE, VERBOSE) lab_admindb.customers;

-- VACUUM (ANALYZE, VERBOSE) lab_admindb.products;

-- VACUUM (ANALYZE, VERBOSE) lab_admindb.orders;

-- VACUUM (ANALYZE, VERBOSE) lab_admindb.order_items;

-- 5.3 REINDEX (по необходимости; сначала на тестовом стенде!)

-- REINDEX TABLE lab_admindb.orders;

-- REINDEX INDEX lab_admindb.idx_orders_customer;

-- --------------------------------------------------------------

-- 6) Целостность и «осиротевшие» ссылки (диагностика)

-- --------------------------------------------------------------

-- 6.1 Заказы без клиента (не должно быть из-за FK)

SELECT o.order_id

FROM orders o

LEFT JOIN customers c ON c.customer_id = o.customer_id

WHERE c.customer_id IS NULL;

-- 6.2 Позиции без родительского заказа (из-за ON DELETE CASCADE тоже не должно быть)

SELECT oi.order_id, oi.product_id

FROM order_items oi

LEFT JOIN orders o ON o.order_id = oi.order_id

WHERE o.order_id IS NULL;

-- 6.3 Позиции с несуществующим продуктом

SELECT oi.order_id, oi.product_id

FROM order_items oi

LEFT JOIN products p ON p.product_id = oi.product_id

WHERE p.product_id IS NULL;

-- --------------------------------------------------------------

-- 7) Безопасность: роли и права (если делали блок с GRANT)

-- --------------------------------------------------------------

-- 7.1 Табличные права по нашей схеме

SELECT grantee, table_schema, table_name, privilege_type, is_grantable

FROM information_schema.role_table_grants

WHERE table_schema='lab_admindb'

ORDER BY grantee, table_name, privilege_type;

-- 7.2 Кто может использовать схему

SELECT n.nspname AS schema, r.rolname AS grantee, has_schema_privilege(r.oid,n.oid,'USAGE') AS has_usage

FROM pg_namespace n

CROSS JOIN pg_roles r

WHERE n.nspname='lab_admindb'

ORDER BY grantee;

-- (пример точечной выдачи прав — выполняйте осознанно)

-- GRANT SELECT ON ALL TABLES IN SCHEMA lab_admindb TO app_reader;

-- GRANT INSERT, UPDATE, DELETE ON TABLE lab_admindb.orders, lab_admindb.order_items TO app_writer;

-- --------------------------------------------------------------

-- 8) Дежурные отчёты админа по предметной области

-- --------------------------------------------------------------

-- 8.1 «Денежный» топ заказов (контроль аномалий)

SELECT o.order_id, c.email, o.total_cents

FROM orders o JOIN customers c ON c.customer_id=o.customer_id

ORDER BY o.total_cents DESC, o.order_id

LIMIT 10;

-- 8.2 Неликвид/выключенные товары (active=false)

SELECT product_id, sku, name, price_cents

FROM products

WHERE active = FALSE

ORDER BY product_id;

-- 8.3 Расхождение между total_cents и фактической суммой (проверка консистентности)

WITH calc AS (

  SELECT order_id, SUM(qty*price_cents) AS sum_from_items

  FROM order_items

  GROUP BY order_id

)

SELECT o.order_id, o.total_cents, c.sum_from_items,

       (o.total_cents - c.sum_from_items) AS delta

FROM orders o

JOIN calc c USING(order_id)

WHERE o.total_cents <> c.sum_from_items;

-- --------------------------------------------------------------

-- 9) Служебные: сброс статистики, если нужно «с нуля» (осторожно)

-- --------------------------------------------------------------

-- Сбросить счётчики только в своей БД (если разрешено политикой сервиса)

-- SELECT pg_stat_reset();

-- Примечание: в общих песочницах может быть заблокировано.

-- --------------------------------------------------------------

-- 10) Аккуратная зачистка (для перезапуска лабы)

-- --------------------------------------------------------------

-- DROP SCHEMA lab_admindb CASCADE;  -- Удалит все объекты схемы (не делать в проде!)

Что с этим делать на паре (под запись в отчёт)

1. Выполнить скрипт секциями и заскринить ключевые блоки: 1.2, 2.2, 3.2, 4.1/4.2, 5.1, 6.*, 7.1.

2. Коротко прокомментировать: есть ли «нулевые» индексы; доля dead tuples; были ли блокировки; есть ли расхождения сумм.

3. При необходимости — показать VACUUM (ANALYZE) на таблице с наибольшим n_dead_tup (в песочнице — безопасно), повторить 5.1 и сравнить.

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