Postgresql размер базы данных windows — Ваш верный помощник с OS Windows

Для того чтобы посмотреть размер базы данных PostgreSQL, можно использовать команду \l+ в интерактивной оболочке psql или SQL-запрос к системным каталогам базы данных. Вот несколько способов, как можно узнать размер базы данных:

Используя psql:
Запустите psql, введите команду \l+ и нажмите Enter. Это покажет список всех баз данных c их размерами.
Найдите свою базу данных в списке и прочтите столбец «размер».
Используя SQL-запрос:
```
SELECT pg_size_pretty(pg_database_size('название_базы_данных')) AS размер;
```
Замените название_базы_данных на имя вашей базы данных. Этот запрос вернет размер базы данных в удобочитаемом формате.
Используя команду du:
Откройте терминал и выполните команду:
```
du -h /путь_к_директории_pgsql
```
Замените /путь_к_директории_pgsql на путь к директории, в которой находится каталог с вашей базой данных PostgreSQL.

Надеюсь, эти способы помогут вам определить размер вашей базы данных PostgreSQL. Если у вас возникли дополнительные вопросы, не стесняйтесь их задавать.

Источник

This guide is designed to assist you in finding out the size of databases and tables on your PostgreSQL server, making it easier to manage your data effectively.

To start, you’ll need to access your server via the command line and establish a connection with the PostgreSQL server. Here’s how you can determine the sizes of your databases and tables in PostgreSQL using straightforward commands.

1. Determine PostgreSQL Database Size Using SQL Queries

To find out how much space your PostgreSQL database occupies, follow these steps:

First, open a terminal and log into the PostgreSQL command interface:
```
sudo -u postgres psql
```
Next, to check the database size, use the built-in function pg_database_size():
```
SELECT pg_database_size('mydb');
```
Картинка с сайта: tecadmin.net

Quick Tip: Enhance the readability of database size information by combining the pg_size_pretty() function. This will display the size in an easy-to-understand format such as KB, MB, and GB:
```
SELECT pg_size_pretty(pg_database_size('mydb'));
```
Картинка с сайта: tecadmin.net

2. View PostgreSQL Database Size in pgAdmin

For those using pgAdmin, you can easily view your database size through its graphical interface:

Log into pgAdmin and select your database. Navigate to the “Statistics” tab, where you’ll see the size of your database displayed in a clear and concise manner.

pgAdmin calculate postgres database size

3. Identify PostgreSQL Table Size

To find out the size of a specific table within your database:

Connect to your desired database using:
```
\c mydb;
```
Then, determine the table size with the pg_total_relation_size() function, which provides the size in a human-friendly format:
```
SELECT pg_size_pretty(pg_total_relation_size('employee'));
```
Картинка с сайта: tecadmin.net

Concusion

This guide simplifies the process of measuring the size of databases and tables in PostgreSQL, offering clear instructions for both command-line and pgAdmin users. It’s a valuable resource for anyone looking to monitor their PostgreSQL server’s data storage efficiently.

Источник

Статей о работе с PostgreSQL и её преимуществах достаточно много, но не всегда из них понятно, как следить за состоянием базы и метриками, влияющими на её оптимальную работу. В статье подробно рассмотрим SQL-запросы, которые помогут вам отслеживать эти показатели и просто могут быть полезны как пользователю.

Зачем следить за состоянием PostgreSQL?

Мониторинг базы данных также важен, как и мониторинг ваших приложений. Необходимо отслеживать процессы более детализировано, чем на системном уровне. Для этого можно отслеживать следующие метрики:

Насколько эффективен кэш базы данных?
Какой размер таблиц в вашей БД?
Используются ли ваши индексы?
И так далее.

Мониторинг размера БД и её элементов

1. Размер табличных пространств

SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) 
FROM pg_tablespace
WHERE spcname<>'pg_global';

После запуска запроса вы получите информацию о размере всех tablespace созданных в вашей БД. Функция pg_tablespace_size предоставляет информацию о размере tablespace в байтах, поэтому для приведения к читаемому виду мы также используем функцию pg_size_pretty. Пространство pg_global исключаем, так как оно используется для общих системных каталогов.

2. Размер баз данных

SELECT pg_database.datname,
       pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

После запуска запроса вы получите информацию о размере всех баз данных, созданных в рамках вашего экземпляра PostgreSQL.

3. Размер схем в базе данных

SELECT A.schemaname,
       pg_size_pretty (SUM(pg_relation_size(C.oid))) as table, 
       pg_size_pretty (SUM(pg_total_relation_size(C.oid)-pg_relation_size(C.oid))) as index, 
       pg_size_pretty (SUM(pg_total_relation_size(C.oid))) as table_index,
       SUM(n_live_tup)
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
INNER JOIN pg_stat_user_tables A ON C.relname = A.relname
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C .relkind <> 'i'
AND nspname !~ '^pg_toast'
GROUP BY A.schemaname;

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

4. Размер таблиц

SELECT schemaname,
       C.relname AS "relation",
       pg_size_pretty (pg_relation_size(C.oid)) as table,
       pg_size_pretty (pg_total_relation_size (C.oid)-pg_relation_size(C.oid)) as index,
       pg_size_pretty (pg_total_relation_size (C.oid)) as table_index,
       n_live_tup
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
LEFT JOIN pg_stat_user_tables A ON C.relname = A.relname
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

После запуска запроса вы получите детальную информацию о каждой таблице с указанием её схемы, размера без индексов, размере индексов, суммарном размере таблицы и индексов, а также количестве строк в таблице.

Контроль блокировок

Если вашей базой данных пользуется большего одного пользователя, то всегда есть риск взаимной блокировки запросов и появления очереди с большим количеством запросов, которые будут находиться в ожидание. Чаще всего такое может возникнуть при обработке большого количества запросов, использующих одинаковые таблицы. Они будут мешать завершиться друг другу и не давать запуститься другим запросам. Больше об этом можно прочитать в официальной документации. Мы же рассмотрим способы нахождения блокировок и их снятия.

1. Мониторинг блокировок

SELECT COALESCE(blockingl.relation::regclass::text, blockingl.locktype) AS locked_item,
       now() - blockeda.query_start                                     AS waiting_duration,
       blockeda.pid                                                     AS blocked_pid,
       blockeda.query                                                   AS blocked_query,
       blockedl.mode                                                    AS blocked_mode,
       blockinga.pid                                                    AS blocking_pid,
       blockinga.query                                                  AS blocking_query,
       blockingl.mode                                                   AS blocking_mode
FROM pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_locks blockingl ON (blockingl.transactionid = blockedl.transactionid OR
                            blockingl.relation = blockedl.relation AND
                            blockingl.locktype = blockedl.locktype) AND blockedl.pid <> blockingl.pid
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted AND blockinga.datname = current_database();

Данный запрос показывает всю информацию о заблокированных запросах, а также информацию о том, кем они заблокированы.

2. Снятие блокировок

SELECT pg_cancel_backend(PID_ID);
OR
SELECT pg_terminate_backend(PID_ID);

PID_ID — это ID запроса, который блокирует другие запросы. Чаще всего хватает отмены одного блокирующего запроса, чтобы снять блокировки и запустить всю накопившуюся очередь. Разница между pg_cancel_backend и pg_terminate_backend в том, что pg_cancel_backend отменяет запрос, а pg_terminate_backend завершает сеанс и, соответственно, закрывает подключение к базе данных. Команда pg_cancel_backend более щадящая и в большинстве случаев вам её хватит. Если нет, используем pg_terminate_backend.

Показатели оптимальной работы вашей БД

1. Коэффициент кэширования (Cache Hit Ratio)

SELECT sum(heap_blks_read) as heap_read,
       sum(heap_blks_hit)  as heap_hit,
       sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM 
  pg_statio_user_tables;

Коэффициент кэширования — это показатель эффективности чтения, измеряемый долей операций чтения из кэша по сравнению с общим количеством операций чтения как с диска, так и из кэша. За исключением случаев использования хранилища данных, идеальный коэффициент кэширования составляет 99% или выше, что означает, что по крайней мере 99% операций чтения выполняются из кэша и не более 1% — с диска.

2. Использование индексов

SELECT relname,   
       100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,   
       n_live_tup rows_in_table 
FROM pg_stat_user_tables 
WHERE seq_scan + idx_scan > 0 
ORDER BY n_live_tup DESC;

Добавление индексов в вашу базу данных имеет большое значение для производительности запросов. Индексы особенно важны для больших таблиц. Этот запрос показывает количество строк в таблицах и процент времени использования индексов по сравнению с чтением без индексов. Идеальные кандидаты для добавления индекса — это таблицы размером более 10000 строк с нулевым или низким использованием индекса.

3. Коэффициент кэширования индексов (Index Cache Hit Rate)

SELECT sum(idx_blks_read) as idx_read,
       sum(idx_blks_hit)  as idx_hit,
       (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM pg_statio_user_indexes;

Данный коэффициент похож на обычный коэффициент кэширования, но рассчитывается на данных использования индексов.

4. Неиспользуемые индексы

SELECT schemaname, relname, indexrelname
FROM pg_stat_all_indexes
WHERE idx_scan = 0 and schemaname <> 'pg_toast' and  schemaname <> 'pg_catalog'

Данный запрос находит индексы, которые созданы, но не использовались в SQL-запросах.

5. Раздувание базы данных (Database bloat)

SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::float/iotta END)::numeric,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
  SELECT
    schemaname, tablename, cc.reltuples, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta /* very rough approximation, assumes all cols */
  FROM (
    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, (
        SELECT
          (SELECT current_setting('block_size')::numeric) AS bs,
          CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC;

Раздувание базы данных — это дисковое пространство, которое использовалось таблицей или индексом и доступно для повторного использования базой данных, но не было освобождено. Раздувание происходит при обновлении таблиц или индексов. Если у вас загруженная база данных с большим количеством операций удаления, раздувание может оставить много неиспользуемого пространства в вашей базе данных и повлиять на производительность, если его не убрать. Показатели wastedbytes для таблиц и wastedibytes для индексов покажет вам, есть ли у вас какие-либо серьезные проблемы с раздуванием. Для борьбы с раздуванием существует команда VACUUM.

6. Проверка запусков VACUUM

SELECT relname, 
       last_vacuum, 
       last_autovacuum 
FROM pg_stat_user_tables;

Раздувание можно уменьшить с помощью команды VACUUM, но также PostgreSQL поддерживает AUTOVACUUM. О его настройке можно прочитать тут.

Ещё несколько запросов, которые могут быть вам полезны

1. Показывает количество открытых подключений

SELECT COUNT(*) as connections,
       backend_type
FROM pg_stat_activity
where state = 'active' OR state = 'idle'
GROUP BY backend_type
ORDER BY connections DESC;

Показывает открытые подключения ко всем базам данных в вашем экземпляре PostgreSQL. Если у вас несколько баз данных в одном PostgreSQL, то в условие WHERE стоит добавить datname = ‘Ваша_база_данных’.

2. Показывает выполняющиеся запросы

SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

Показывает выполняющиеся запросы и их длительность.

Заключение

Все запросы выше собраны мной из интернета при появлении каких-либо вопросов или проблем в моей базе данных. Если есть ещё запросы, которые могут быть полезны для пользователей PostgreSQL, буду рад, если вы поделитесь ими в комментариях. Надеюсь, статья поможет вам и сохранит ваше время.

Источник

PostgreSQL is a relational database management system that helps us store data for different web apps, mobile apps, etc. In Postgres, tables are the objects of a database that depict the data in the form of rows and columns. While working with databases, finding the sizes of the database objects can help us in storage management, memory optimization, resource management, etc.

This write-up will explain how to check the size of databases or tables in PostgreSQL using pgAdmin.

How to Check Database Size or Table Size in Postgres Using pgAdmin?

In Postgres, different built-in functions are used to check the size of database objects, such as the pg_relation_size(), pg_database_size(), pg_size_pretty(), etc. However, pgAdmin helps us get the size of database objects with or without using these built-in functions.

Getting Database Size Using pgAdmin

This section illustrates how to get the database size in pgAdmin:

— Using pgAdmin’s Statistics
— Using pg_database_size() Function
— Using pg_size_pretty() Function

Example 1: Getting All Databases Size Using pgAdmin’s Statistics

Open the pgAdmin, click on the “Databases” option under the “Servers” tree, and then select the pgAdmin’s statistics tab to get the size of all databases:

The databases’ sizes can be seen in the “Size” column.

Example 2: Getting the Size of a Specific Database Using pgAdmin’s Statistics

The size of a specific database can be obtained by selecting that particular database and then clicking on the «Statistics» tab:

The above snippet shows the size of the “postgres” database.

Example 3: Getting the Size of a Specific Database Using pg_database_size() Function

Alternatively, you can use the “pg_database_size()” function from the pgAdmin’s query tool to get the database size:

SELECT pg_database_size('postgres');

The stated function retrieves the database size in bytes.

Example 4: Getting the Size of a Database Using pg_size_pretty() Function

Bytes are difficult to comprehend if the databases contain enormous amounts of data. To get the data in an easily understandable format, wrap the “pg_database_size()” function within the “pg_size_pretty()” function, as follows:

SELECT pg_size_pretty(pg_database_size('postgres'));

This time, the database size is retrieved in “KBs”.

Example 5: Getting the Size of All Databases Using pg_database_size() Function

Use the pg_database_size() function with the “pg_database” catalog to get the size of all databases, including the standard system databases:

SELECT pg_database.datname, 
pg_database_size(pg_database.datname) AS all_databases_size 
FROM pg_database;

— Here, the “pg_database” is a system catalog that contains information regarding the databases.
— The “datname” is a column containing information regarding the database name.
— “pg_database_size()” is a built-in function that retrieves the database sizes.
— “AS” represents a column alias, used to assign a temporary name to the resultant column.

The output snippet retrieves the databases’ size in bytes.

Getting Tables Size Using pgAdmin

This section demonstrates how to get tables size:

— Using pgAdmin’s Statistics
— Using pg_relation_size() Function
— Using pg_size_pretty() Function

Example 1: Getting Size of All Tables Using pgAdmin’s Statistics

Expand the “Schemas” option available under the «database” of your choice. Locate the “public” schema, select the “Tables” option, and click on the “statistics” tab to see the size of all tables:

The output shows the total size for each table available in the “postgres” database.

Example 2: Getting the Size of a Specific Table Using pgAdmin’s Statistics

To get the size of only a particular table, all you need to do is select the table of your choice and click on the “statistics” tab:

The output shows that the total size of the “department_information” table is “8KB”.

Example 3: Getting Table Size Using pg_relation_size() Function

Alternatively, you can specify the table name inside the “pg_relation_size()” function to get the size of the desired table:

SELECT pg_relation_size('department_information') AS table_size;

The output shows the total number of bytes that the “department_information” table takes.

Example 4: Getting Table Size Using pg_size_pretty() Function

Wrap the “pg_relation_size()” function within the “pg_size_pretty()” function to get the table’s size in human-understandable formats:

SELECT pg_size_pretty(pg_relation_size('department_information'));

The output proves that the pg_size_pretty() function returns the table’s size in an easily understandable format.

Conclusion

In PostgreSQL, built-in functions like pg_relation_size(), pg_database_size(), pg_size_pretty(), etc. are used to get the size of the table or database. The pgAdmin helps us get the size of database objects with or without using these built-in functions. To get the size of a database or table without using any function, you need to select the database or table of your choice and then click on pgAdmin’s statistics tab. This Postgres blog explained how to check the database size and table size using pgAdmin.

Источник

Пройдите тест, узнайте какой профессии подходите

Работать самостоятельно и не зависеть от других

Работать в команде и рассчитывать на помощь коллег

Организовывать и контролировать процесс работы

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

Если вам требуется узнать размер вашей базы данных PostgreSQL в кратчайшие сроки, примените следующую команду:

Замените dbname на название базы данных, размер которой вы хотите узнать.

Адепты работы через командную строку psql могут воспользоваться удобной мета-командой:

Эта команда представит размер указанной базы данных. Если имя базы данных не подается на вход, команда выведет перечень всех баз и их размеры.

Кинга Идем в IT: пошаговый план для смены профессии

Набор инструментов для работы с размерами баз данных

Формирование запроса на размер баз данных

Для оценки габаритов всех ваших баз данных выполните следующий SQL-запрос:

Запрос представит размеры баз даных в удобном для восприятия формате и отсортирует их в порядке убывания размера. Это поможет быстро вычленить наиболее обширные базы данных.

Команды psql для быстрого анализа

С помощью следующей команды psql можно легко получить перечень всех баз данных, включая информацию о их размерах:

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

Детальный анализ размеров таблиц

Проведение глубокого анализа с помощью psql

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

Она даст вам полные данные о размере каждой таблицы в текущей схеме.

Специфический SQL-запрос для оценки размеров таблиц

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

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

Специализированные процедуры для опытных пользователей

Получение данных о размере в исходном формате

Функция pg_size_pretty облегчает восприятие информации, однако для систем мониторинга иногда необходим размер в байтах:

Данный запрос позволит получить исходные сведения о размере данных без преобразований.

Сопоставление размеров с другой статистикой

Слияние данных о размерах баз с информацией о количестве записей, активных подключениях и другой соответствующей статистикой даст вам всеобъемлющее представление о состоянии системы.

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

Размеры баз данных PostgreSQL можно представить как уникальный городской пейзаж:

Для иллюстрации величины этого пейзажа используются запросы типа следующего:

Результат даст вам ощущение впечатляющего размаха данных:

Продвинутые наработки для опытных пользователей

Мониторинг динамики роста данных

Регулярное отслеживание изменения размеров баз данных позволит вам улавливать тенденции и прогнозировать будущее развитие системы.

Управление фрагментированным пространством

С помощью команд VACUUM и переиндексации возможно эффективно сократить фрагментацию и восстановить потерянное пространство.

План техобслуживания для достижения оптимальной эффективности

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

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

PostgreSQL: Системные административные функции — заправшивая информация о функциях для определения размера баз данных в официальной документации PostgreSQL.
Количество записей во всех таблицах Postgres — Обсуждение на Stack Overflow, содержащее множество полезной информации о вопросах, связанных с размерами таблиц в PostgreSQL.
Использование дискового пространства в PostgreSQL wiki — Советы по управлению использованием диска от сообщества PostgreSQL.
Высшие нормализованные формы и их критерии — Глубокое рассмотрение нормализации в контексте управления размером баз данных.
Показать размеры баз данных в PostgreSQL — Полезное руководство для пользователей Linux о том, как проверить размер баз данных в PostgreSQL.

Источник