Словарь данных и системные каталоги

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Архитектору

Назначение словаря данных

Словарь данных — системные таблицы и представления с метаданными: таблицы, столбцы, типы, ограничения, индексы, права. В PostgreSQL ядро каталога — схема pg_catalog; переносимый интерфейс — information_schema (стандарт SQL).

information_schema в PostgreSQL реализован поверх pg_catalog, а не как отдельное хранилище.

---

Сравнение подходов

Критерий	information_schema	pg_catalog
Переносимость	Высокая (PostgreSQL, MySQL, SQL Server)	Только PostgreSQL
Полнота	Ограничена стандартом	Полная (партиции, расширения, статистика)
Производительность	Ниже (вложенные представления)	Выше (прямой доступ)
Права	Автофильтрация по доступу	Требует учёта прав явно

Практика: прикладной переносимый код — information_schema; администрирование и тонкая диагностика — pg_catalog.

---

Запросы через information_schema

Список таблиц в схеме public:

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

Структура столбцов таблицы products:

SELECT
    column_name,
    data_type,
    character_maximum_length AS max_length,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'products'
ORDER BY ordinal_position;

Внешние ключи таблицы:

SELECT
    rc.constraint_name,
    kcu.column_name,
    ccu.table_schema AS foreign_schema,
    ccu.table_name AS foreign_table,
    ccu.column_name AS foreign_column
FROM information_schema.referential_constraints rc
JOIN information_schema.key_column_usage kcu
  ON rc.constraint_name = kcu.constraint_name
  AND rc.constraint_schema = kcu.constraint_schema
JOIN information_schema.constraint_column_usage ccu
  ON rc.unique_constraint_name = ccu.constraint_name
  AND rc.unique_constraint_schema = ccu.constraint_schema
WHERE kcu.table_schema = 'public'
  AND kcu.table_name = 'order_items';

---

Запросы через pg_catalog

Таблицы схемы с оценкой числа строк и страниц:

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    c.reltuples::bigint AS estimated_rows,
    c.relpages AS data_pages
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY c.relname;

Столбцы с типами и значениями по умолчанию:

SELECT
    a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
    a.attnotnull AS not_null,
    pg_catalog.pg_get_expr(d.adbin, d.adrelid) AS default_value
FROM pg_attribute a
LEFT JOIN pg_attrdef d
  ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attrelid = 'public.products'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

Индексы таблицы:

SELECT
    i.relname AS index_name,
    a.attname AS column_name,
    am.amname AS index_type
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_am am ON am.oid = i.relam,
LATERAL unnest(ix.indkey) WITH ORDINALITY AS k(attnum, ord)
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.attnum
WHERE t.relname = 'products'
  AND t.relnamespace = 'public'::regnamespace
ORDER BY i.relname, k.ord;

---

Комментарии к объектам

COMMENT ON TABLE products IS 'Каталог товаров';
COMMENT ON COLUMN products.price IS 'Цена в рублях с копейками';

SELECT
    obj_description('public.products'::regclass) AS table_comment,
    a.attname AS column_name,
    col_description('public.products'::regclass, a.attnum) AS column_comment
FROM pg_attribute a
WHERE a.attrelid = 'public.products'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped;

---

Дополнительные источники метаданных

• pg_stats — статистика для оптимизатора (после ANALYZE);
• pg_depend — зависимости объектов (каскадное удаление);
• pg_stat_user_tables — активность чтения/записи по таблицам.

Сравнение производительности на больших БД:

EXPLAIN ANALYZE
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

EXPLAIN ANALYZE
SELECT relname FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public' AND c.relkind = 'r';

---

Контрольные вопросы

1. Почему information_schema может показать меньше объектов, чем pg_catalog, при тех же правах?
2. В каком каталоге хранится число страниц таблицы?
3. Чем отличается pg_class.relkind = 'r' от relkind = 'v'?
4. Зачем использовать format_type(atttypid, atttypmod) вместо прямого join с pg_type?

---

См. также

• Первые шаги с SQL
• Оптимизация SQL-запросов
• Справочник по SQL — раздел метаданных

---