Сортировка таблиц в PostgreSQL по размеру: методы вывода

Быстрый ответ

Чтобы упорядочить таблицы PostgreSQL по размеру, воспользуйтесь следующим запросом:

SELECT 
    relname AS "Таблица",
    pg_size_pretty(pg_total_relation_size(oid)) AS "Размер"
FROM 
    pg_class
WHERE 
    relkind = 'r'
ORDER BY 
    pg_total_relation_size(oid) DESC;

Так, таблицы будут отсортированы по убыванию размера, включая размер данных и индексов.

Более подробно о размерах таблиц

Понимание размера таблиц crucial для оптимизации работы с данными. Рассмотрим более подробно.

Сopравниваем размер данных и индексов

Для оценки размеров данных и индексов в таблицах используйте функции pg_relation_size() и pg_indexes_size():

SELECT 
    tbl.relname AS "Таблица",
    pg_size_pretty(pg_relation_size(tbl.oid)) AS "Размер данных",
    pg_size_pretty(pg_indexes_size(tbl.oid)) AS "Размер индексов"
FROM 
    pg_class tbl
WHERE 
    tbl.relkind = 'r'
ORDER BY 
    pg_total_relation_size(tbl.oid) DESC;

Подробнее об этом расскажет наш спикер на видео

Учёт TOAST-таблиц

В PostgreSQL для хранения больших объёмов данных применяются TOAST-таблицы. Чтобы учесть их размер в общем объёме, можно использовать следующий запрос:

SELECT 
    tbl.relname AS "Таблица",
    pg_size_pretty(pg_total_relation_size(tbl.oid) – pg_relation_size(tbl.oid)) AS "Размер TOAST-таблиц и индексов"
FROM 
    pg_class tbl
WHERE 
    tbl.relkind = 'r'
ORDER BY 
    pg_total_relation_size(tbl.oid) DESC;

Таким образом, мы получим полный объём данных таблицы, включая TOAST и индексы.

Выбор таблиц из конкретной схемы

Если в вашей базе данных есть несколько схем, можете получить таблицы из определённой таким образом:

SELECT 
    ns.nspname AS "Схема",
    tbl.relname AS "Таблица",
    pg_size_pretty(pg_total_relation_size(tbl.oid)) AS "Размер"
FROM 
    pg_class tbl
JOIN 
    pg_namespace ns ON ns.oid = tbl.relnamespace
WHERE 
    tbl.relkind = 'r' 
    AND ns.nspname = 'public' -- замените на требуемое имя схемы
ORDER BY 
    pg_total_relation_size(tbl.oid) DESC;

Из запроса стоит исключить системные таблицы, расположенные обычно в схемах с префиксом pg_.

Визуализация

Представьте размеры таблиц как городской пейзаж, где каждое здание соответствует таблице, а его высота — её размеру:

Город Данных (🏙️): 

🏠 – небольшая таблица
🏢 – средняя таблица
🏦 – большая таблица 
🏬 – весьма обширная таблица

Наглядно представим изменения после сортировки:

Перед сортировкой: 🏢 🏠 🏦 🏬
После сортировки: 🏠 🏢 🏦 🏬

Обратите внимание, что порядок сортировки (указанный в ORDER BY) определяет расположение "зданий" в вашем городе данных.

Надёжные запросы

Предлагаю усовершенствовать ваш набор SQL-запросов, делая их более безопасными и надёжными.

Предохраняемся от SQL-инъекций и синтаксических ошибок

Функция quote_ident() снижает риск SQL-инъекций и помогает предотвратить синтаксические ошибки:

SELECT 
    quote_ident(tbl.relname) AS "Таблица",
    pg_size_pretty(pg_total_relation_size(tbl.oid)) AS "Размер"
FROM 
    pg_class tbl
WHERE 
    tbl.relkind = 'r'
ORDER BY 
    pg_total_relation_size(tbl.oid) DESC;

Советую быть бдительными: как в детективных фильмах noir, всегда заключайте названия в quote_ident()!

Адаптируем запрос под текущий контекст

Модифицируйте запрос с учётом текущего контекста, используя current_schema() и current_database():

SELECT 
    nspname AS "Схема",
    relname AS "Таблица",
    pg_size_pretty(pg_total_relation_size(tbl.oid)) AS "Размер"
FROM 
    pg_class tbl
INNER JOIN 
    pg_namespace ns ON ns.oid = tbl.relnamespace
WHERE 
    relkind = 'r'
    AND nspname NOT LIKE 'pg_%' -- исключаем системные схемы
    AND nspname = current_schema() -- используем текущую схему
ORDER BY 
    pg_total_relation_size(tbl.oid) DESC;

Такой подход позволит более гибко изучать "город".

Оцениваем общий размер базы данных

Для определения общего размера базы данных используйте pg_database_size():

SELECT 
    pg_size_pretty(pg_database_size(current_database())) AS "Общий размер базы данных";

Не забывайте, что PostgreSQL автоматически переключает единицы измерения на более крупные, если размер превышает определённый порог (например, 10 Кб).

Полезные материалы

1. PostgreSQL: Оценка использования дискового пространства (Документация PostgreSQL, глава 29.1.) — вот ваш гид по использованию дискового пространства в PostgreSQL.
2. Управление дисковым пространством – PostgreSQL wiki — дополнительная информация и рекомендации по управлению дисковым пространством.
3. SQL Server и функция COALESCE: анализ поведения (Database Administrators Stack Exchange) — практический урок по оптимизации запросов.
4. Важнейшие метрики для мониторинга PostgreSQL (Datadog) — Datadog поможет вам разобраться, на какие показатели следует обратить внимание при мониторинге PostgreSQL.

(Примечание: некоторые заголовки были корректированы для большей точности)