Поиск размера различных объектов в базе данных PostgreSQL

Недавно мне нужно было оптимизировать базу данных на портале дл яобработки данных от устройств мониторинговой сети. Данных там очень много, плюс к ним много различной служебной информации сохраняется, так сказать попутно. И база данных растет очень быстро. Было принято решение проанализировать размеры таблиц и интдексов и оптимизировать их (где то убрать ненужные поля, где то уменьшить количество вставок в таблицу и т.д.). Результатом этой работы стал небольшой список SQL запросов для PostgreSQL базы данных, который я хотел бы сохранить для себя, ну и поделиться со всеми, кому они могут понадобиться.

SQL запрос ниже покажет информацию о размере всех таблиц в базе данных, как в байтах, так и в «красивой» форме:

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

Следующий SQL запрос покажет самую большую базу данных в вашем кластере:

SELECT d.datname AS Name,  pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
        ELSE 'No Access'
    END AS SIZE
FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
        THEN pg_catalog.pg_database_size(d.datname)
        ELSE NULL
    END DESC -- nulls first
    LIMIT 20

Примечание: Базы данных, к которым пользователь не может подключиться, сортируются так, как если бы они были бесконечно большого размера.

Теперь пример, как найти размер самой большой таблицы или индекса:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;

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

Далее, пример SQL запроса для определение общего размера ваших самых больших таблиц:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC
  LIMIT 20;

Вот такой небольшой пост с полезными SQL запросами для определения размера различных обьектов базы данных.
Я создал его как шпаргалку на будущее, так проще хранить их всегда под рукой, а не исать в интернете каждый раз, как они понадобятся.
 

Добавить комментарий

Ваш e-mail не будет опубликован.