Database size
To get the physical size of the database files (storage), we use the following query:
SELECT pg_database_size (current_database ());
The result will be represented as a number of type 41809016.
current_database () is a function that returns the name of the current database. Instead, you can enter a name in the text:
SELECT pg_database_size (‘my_database’);
To get human-readable information, use the pg_size_pretty function:
SELECT pg_size_pretty (pg_database_size (current_database ()));
As a result, we obtain information of the form 40 Mb.
List of tables
Sometimes you need to get a list of database tables. To do this, use the following query:
SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN (‘information_schema’, ‘pg_catalog’);
information_schema – a standard database schema that contains collections of views, such as tables, fields, etc. Table views contain information about all database tables.
The query described below will select all tables from the specified schema of the current database:
SELECT table_name FROM information_schema.tables
WHERE table_schema NOT IN (‘information_schema’, ‘pg_catalog’)
AND table_schema IN (‘public’, ‘myschema’);
In the last IN condition, you can specify the name of a specific schema.
Table size
By analogy with obtaining a database size, the table data size can be calculated using the appropriate function:
SELECT pg_relation_size (‘accounts’);
The pg_relation_size function returns the amount that the specified layer of the specified table or index occupies on disk.
The name of the largest table
To display a list of tables in the current database, sorted by table size, run the following query:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
To display information about the largest table, limit the query with LIMIT:
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 1;
relname – the name of the table, index, view, etc.
relpages – the size of the representation of this table on the disk in the number of pages (by default, one page is 8 KB).
pg_class is a system table that contains information about database table relationships.
List of connected users
To find out the name, IP, and port of the connected users, run the following query:
SELECT datname, usename, client_addr, client_port FROM pg_stat_activity;
User activity
To find out the connection activity of a specific user, use the following query:
SELECT datname FROM pg_stat_activity WHERE usename = ‘devuser’;