PostgreSQL. 10 продвинутых команд для DBA с примерами
Топ 10 самих популярных команд для управления сервером PostgreSQL для настоящих администраторов баз данных (DBA).
Большинство команд подходят как для консольной утилиты psql , так и для запуска через ваш клиент.
1. Как найти самую большую таблицу в базе данных PostgreSQL?
Результатом будет самая большая таблица (в примере testtable1 ) в страницах. Размер одной страницы равен 8KB (т.е. размер таблицы в примере — 2,3GB)
2. Как узнать размер всей базы данных PostgreSQL?
Результатом будет размер базы данных в байтах:
Если вы хотите получить размер в более читаемом («человеческом») формате — «оберните» результат в функцию pg_size_pretty() :
Ну и сразу логичным будет показать все базы данных в читаемом («человеческом») виде, отсортированные от более больших к меньшим
3. Как узнать размер таблицы в базе данных PostgreSQL?
Результатом будет размер таблицы testtable1, включая индексы. Результат будет отображен сразу в удобном для чтения формате, а не в байтах.
Если вам нужно узнать размер таблицы без индексов, тогда следует выполнить такой запрос:
4. Как узнать текущую версию сервера PostgreSQL?
Результат будет подобным этому:
5. Как выполнить SQL-файл в PostgreSQL?
Для данной цели существует специальная команда в консольной утилите:
Где /path/to/file.sql — это путь к вашему SQL-файлу. Обратите внимание, что он должен лежать в доступной для чтения пользователя postgres директории.
6. Как отобразить список всех баз данных сервера PostgreSQL?
Для данной цели существует специальная команда в консольной утилите:
7. Как отобразить список всех таблиц в базе данных PostgreSQL?
Для данной цели существует специальная команда в консольной утилите что покажет список таблиц в текущей БД.
8. Как показать структуру, индексы и прочие элементы выбранной таблицы в PostgreSQL?
Для данной цели существует специальная команда в консольной утилите:
Где testtable1 — имя таблицы
9. Как отобразить время выполнения запроса в консольной утилите PostgreSQL?
После чего все запросы станут отображаться в консольной утилите со временем выполнения.
Отключаются эти уведомления точно так же, как и включаются — вызовом:
10. Как отобразить все команды консольной утилиты PostgreSQL?
Это наверное самый важный пункт, т.к. любой DBA должен знать как вызвать эту справку ��
Топ полезных SQL-запросов для PostgreSQL
Статей о работе с PostgreSQL и её преимуществах достаточно много, но не всегда из них понятно, как следить за состоянием базы и метриками, влияющими на её оптимальную работу. В статье подробно рассмотрим SQL-запросы, которые помогут вам отслеживать эти показатели и просто могут быть полезны как пользователю.
Зачем следить за состоянием PostgreSQL?
Мониторинг базы данных также важен, как и мониторинг ваших приложений. Необходимо отслеживать процессы более детализировано, чем на системном уровне. Для этого можно отслеживать следующие метрики:
Насколько эффективен кэш базы данных?
Какой размер таблиц в вашей БД?
Используются ли ваши индексы?
Мониторинг размера БД и её элементов
1. Размер табличных пространств
После запуска запроса вы получите информацию о размере всех tablespace созданных в вашей БД. Функция pg_tablespace_size предоставляет информацию о размере tablespace в байтах, поэтому для приведения к читаемому виду мы также используем функцию pg_size_pretty. Пространство pg_global исключаем, так как оно используется для общих системных каталогов.
2. Размер баз данных
После запуска запроса вы получите информацию о размере всех баз данных, созданных в рамках вашего экземпляра PostgreSQL.
3. Размер схем в базе данных
После запуска запроса вы получите детальную информацию о каждой схеме в вашей базе данных: суммарный размер всех таблиц, суммарный размер всех индексов, общий суммарный размер схемы и суммарное количество строк во всех таблицах схемы.
4. Размер таблиц
После запуска запроса вы получите детальную информацию о каждой таблице с указанием её схемы, размера без индексов, размере индексов, суммарном размере таблицы и индексов, а также количестве строк в таблице.
Контроль блокировок
Если вашей базой данных пользуется большего одного пользователя, то всегда есть риск взаимной блокировки запросов и появления очереди с большим количеством запросов, которые будут находиться в ожидание. Чаще всего такое может возникнуть при обработке большого количества запросов, использующих одинаковые таблицы. Они будут мешать завершиться друг другу и не давать запуститься другим запросам. Больше об этом можно прочитать в официальной документации. Мы же рассмотрим способы нахождения блокировок и их снятия.
1. Мониторинг блокировок
Данный запрос показывает всю информацию о заблокированных запросах, а также информацию о том, кем они заблокированы.
2. Снятие блокировок
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)
Коэффициент кэширования — это показатель эффективности чтения, измеряемый долей операций чтения из кэша по сравнению с общим количеством операций чтения как с диска, так и из кэша. За исключением случаев использования хранилища данных, идеальный коэффициент кэширования составляет 99% или выше, что означает, что по крайней мере 99% операций чтения выполняются из кэша и не более 1% — с диска.
2. Использование индексов
Добавление индексов в вашу базу данных имеет большое значение для производительности запросов. Индексы особенно важны для больших таблиц. Этот запрос показывает количество строк в таблицах и процент времени использования индексов по сравнению с чтением без индексов. Идеальные кандидаты для добавления индекса — это таблицы размером более 10000 строк с нулевым или низким использованием индекса.
3. Коэффициент кэширования индексов (Index Cache Hit Rate)
Данный коэффициент похож на обычный коэффициент кэширования, но рассчитывается на данных использования индексов.
4. Неиспользуемые индексы
Данный запрос находит индексы, которые созданы, но не использовались в SQL-запросах.
5. Раздувание базы данных (Database bloat)
Раздувание базы данных — это дисковое пространство, которое использовалось таблицей или индексом и доступно для повторного использования базой данных, но не было освобождено. Раздувание происходит при обновлении таблиц или индексов. Если у вас загруженная база данных с большим количеством операций удаления, раздувание может оставить много неиспользуемого пространства в вашей базе данных и повлиять на производительность, если его не убрать. Показатели wastedbytes для таблиц и wastedibytes для индексов покажет вам, есть ли у вас какие-либо серьезные проблемы с раздуванием. Для борьбы с раздуванием существует команда VACUUM.
6. Проверка запусков VACUUM
Раздувание можно уменьшить с помощью команды VACUUM, но также PostgreSQL поддерживает AUTOVACUUM. О его настройке можно прочитать тут.
Ещё несколько запросов, которые могут быть вам полезны
1. Показывает количество открытых подключений
Показывает открытые подключения ко всем базам данных в вашем экземпляре PostgreSQL. Если у вас несколько баз данных в одном PostgreSQL, то в условие WHERE стоит добавить datname = ‘Ваша_база_данных’.
2. Показывает выполняющиеся запросы
Показывает выполняющиеся запросы и их длительность.
Заключение
Все запросы выше собраны мной из интернета при появлении каких-либо вопросов или проблем в моей базе данных. Если есть ещё запросы, которые могут быть полезны для пользователей PostgreSQL, буду рад, если вы поделитесь ими в комментариях. Надеюсь, статья поможет вам и сохранит ваше время.
Find Postgres Database Size?
We will go through several ways to look for the database size in the PostgreSQL environment.
Let us look into these methods in PostgreSQL by implementing them on a Test database.
By using select pg database size query:
We will use a pg database size instruction to find the database size in this method. The syntax for this instruction is written below:
The pg database size function takes a parameter, the name of the database, and then selects keyword, fetches the size in bigint and gives the size as an output. Now we will implement this query with an example in the PostgreSQL environment.
Check the output in the appended image.
As the output suggests, the size of the database named “ Test ” is “ 9044771 ” in bigint, but this makes the size of the database unclear, and we should make the result clear by converting the bigint into a more understandable datatype.
By using select pg size pretty query:
In this method, we will use the pg size pretty query in the query editor to find out the size of the database. The syntax for this query is given below:
In this query, we use the pg size pretty command, which takes the pg database size as an argument that converts the pg database size output to a “KB” datatype. Now we will implement this query with a test database to understand this query in the PostgreSQL query editor.
Check the output in the appended image.
This query gives the size of the database named “Test“ in the KB data type, which is more understandable than the bigint data type.
By using pg_database.datname query:
In this method, we will work with a query that will give us the size of all the databases present on our server in the form of Kilobytes as a datatype. We’ll use the following query for this method:
pg_size_pretty ( pg_database_size ( pg_database.datname ) ) AS size
In this query, we will be using the select command for fetching databases’ sizes. The pg database.datname will collect all the databases present in the server and conjugate them with the pg size pretty command that will fetch the size of the databases in the PostgreSQL environment. All this data will be selected from the pg database command because all the databases of PostgreSQL are present at this location. We will take a closer look at this query by inserting it into the PostgreSQL environment.
pg_size_pretty ( pg_database_size ( pg_database.datname ) ) AS size
Check the output in the appended image.
As you can see, all the databases present in PostgreSQL are being fetched and presented along with their sizes in Kilobytes in the form of a table. This query helps the user reach all the databases present and enables the user to have a clear perspective of the memory load to become manageable. The above query is highly recommended for a user if they have to get an overall view with which they can do efficient load management in the case of space and performance.
By using the statistics option in the navigation bar:
In all the above methods, we have opted for queries and coding functions, but in this method, we will take advantage of the options available in pgAdmin 4. There are several options present in the navigation bar of the pgAdmin 4 window that provides a lot of ease while handling data and processing information. So, we will also use one of the options for our benefit that is the statistics option which is the third option after “ Properties ” and “ SQL ”.
So, to use this option for finding out the size of a database is to first find your database on the left-hand side in the browser menu under the heading databases. Then we will have to click and select the certain database whose size we want to find out. After this, we will have to click on the “ Statistics “ option to get all the statistical information related to the certain database. To better grasp this method, we will try this on several databases present on our server.
First, we will open the pgAdmin 4 window, and then we will locate our database in the PostgreSQL 14 environment.
As you can see, we have two databases present in the above snippet. First, we will select the database named “Test”. Then we will select the “Statistics” option.
After this, we will scroll down and will locate the Size section as the last information available in this tab. We will be able to see the database’s size in kilobytes.
We will now choose the other database, ” postgres “.
After this, we will select the “Statistics” option and scroll down to see the size of this database.
By using SQL Shell (psql):
In this method, we will use the SQL shell for finding the size of the database. Open the SQL shell and write the following query:
The size of the database in bigint will be returned by the SQL shell. Now we will write a query for getting the size of the database in Kilobytes.
This query will generate the size of the database “ Test ” in the kilobytes data type. Now we will write the query to generate the database size on the server.
pg_size_pretty ( pg_database_size ( pg_database.datname ) ) AS size
This will give the size of the databases present in the server in the kilobytes datatype.
Conclusion:
In this guide, we discussed several methods for finding the size of the database in PostgreSQL. We discussed several query methods in PostgreSQL. First, we discussed a method in which the size of the database was given in bigint, but this output was unclear with respect to scalability. Hence, we discussed another method of converting the size from bigint to kilobytes. Then the method for getting the size of all the databases present in the environment was also discussed in this guide. After this, we explored pgAdmin 4 options to check the database size.
About the author
Aqsa Yasin
I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.
How to Get Table, Database, Indexes, Tablespace, and Value Size in PostgreSQL
Summary: in this tutorial, you will learn how to get the size of the databases, tables, indexes, tablespace using some handy functions.
PostgreSQL table size
To get the size of a specific table, you use the pg_relation_size() function. For example, you can get the size of the actor table in the dvdrental sample database as follows:
The pg_relation_size() function returns the size of a specific table in bytes:
To make the result more human readable, you use the pg_size_pretty() function. The pg_size_pretty() function takes the result of another function and format it using bytes, kB, MB, GB or TB as appropriate. For example:
The following is the output in kB
The pg_relation_size() function returns the size of the table only, not included indexes or additional objects.
To get the total size of a table, you use the pg_total_relation_size() function. For example, to get the total size of the actor table, you use the following statement:
The following shows the output:
You can use the pg_total_relation_size() function to find the size of biggest tables including indexes.
For example, the following query returns top 5 biggest tables in the dvdrental database:
Here is the output:
PostgreSQL database size
To get the size of the whole database, you use the pg_database_size() function. For example, the following statement returns the size of the dvdrental database:
The statement returns the following result:
To get the size of each database in the current database server, you use the following statement:
PostgreSQL index size
To get total size of all indexes attached to a table, you use the pg_indexes_size() function.
The pg_indexes_size() function accepts the OID or table name as the argument and returns the total disk space used by all indexes attached of that table.
For example, to get the total size of all indexes attached to the film table, you use the following statement:
Here is the output:
PostgreSQL tablespace size
To get the size of a tablespace, you use the pg_tablespace_size() function. The pg_tablespace_size() function accepts a tablespace name and returns the size in bytes.
The following statement returns the size of the pg_default tablespace:
The statement returns the following output:
PostgreSQL value size
To find how much space that needs to store a specific value, you use the pg_column_size() function, for examples:
In this tutorial, you have learned various handy functions to get the size of a database, a table, indexes, a tablespace, and a value.