PostgreSQL Helpful Commands
PostgreSQL is one of the most powerful database systems. This is my favourite database too and the with which I have to most experiences.
As I am not a DB Administrator, I need to remember some useful and lifesaver PosgreSQL commands.
This post will be updated as often as I need new useful commands.
- PostgreSQL Version
- Database Size
- List all Tables by Number of Rows
- Backup
- Query Activities
- Focus: Lock
- Terminate / Kill Queries
- Optimisation / Administration
- Tools
- References
PostgreSQL Version
Which version of PostgreSQL:
SELECT version();
Result:
PostgreSQL 11.5 on x86_64-apple-darwin19.0.0, compiled by Apple clang version 11.0.0 (clang-1100.0.33.8), 64-bit
Database Size
Most of time when working Cloud hosted database, you need to know the of your database. Here is a command that display in human readable units the size of a specified database, database_name:
SELECT pg_size_pretty(pg_database_size('database_name'));
Result:
pg_size_pretty| --------------| 11 MB |
For a detailed view by table (size, indexes, …) – from here:
select current_database() as database,
pg_size_pretty(total_database_size) as total_database_size,
schema_name,
table_name,
pg_size_pretty(total_table_size) as total_table_size,
pg_size_pretty(table_size) as table_size,
pg_size_pretty(index_size) as index_size
from ( select table_name,
table_schema as schema_name,
pg_database_size(current_database()) as total_database_size,
pg_total_relation_size(table_name) as total_table_size,
pg_relation_size(table_name) as table_size,
pg_indexes_size(table_name) as index_size
from information_schema.tables
where table_schema=current_schema() and table_name like 'table_%'
order by total_table_size
) as sizes;
Result:
database | total_database_size | schema_name | table_name | total_table_size | table_size | index_size -----------+---------------------+-------------+------------+------------------+------------+------------ vigneshdb | 1586 MB | corpdata | table_aaa | 16 kB | 0 bytes | 8192 bytes vigneshdb | 1586 MB | corpdata | table_bbb | 24 kB | 0 bytes | 16 kB vigneshdb | 1586 MB | corpdata | table_ccc | 640 kB | 112 kB | 488 kB vigneshdb | 1586 MB | corpdata | table_ddd | 9760 kB | 3152 kB | 6568 kB vigneshdb | 1586 MB | corpdata | table_eee | 1120 MB | 311 MB | 808 MB
List all Tables by Number of Rows
Listing from largest to smallest tables by number of rows :
select n.nspname as table_schema,
c.relname as table_name,
c.reltuples as rows
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname not in ('information_schema','pg_catalog')
order by c.reltuples desc;
Result:
table_schema | table_name | rows --------------+------------+----- public | foo | 100 public | bar | 42
Backup
Checking if a backup is in progress:
SELECT pg_is_in_backup();
Result:
pg_is_in_backup| ---------------| false |
More backup commands here
Query Activities
- Last queries:
SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query
FROM pg_stat_activity
WHERE datname='database_name';
Result:
datname |application_name |pid |backend_start |query_start |state_change |state|query | --------|------------------------|-----|-------------------|-------------------|-------------------|-----|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| qsmarket|DBeaver 5.3.2 - Main |69602|2019-10-31 19:31:46|2019-10-31 19:34:04|2019-10-31 19:34:04|idle |select current_database() as database,¶ pg_size_pretty(total_database_size) as total_database_size,¶ schema_name,¶ table_name,¶ pg_size_pretty(total_table_size) as total_table_size,¶ pg_size_pretty(table_size) as table_size,¶| qsmarket|DBeaver 5.3.2 - Metadata|69603|2019-10-31 19:31:46|2019-10-31 19:34:55|2019-10-31 19:34:55|idle |SELECT i.*,c.relnamespace FROM pg_catalog.pg_inherits i,pg_catalog.pg_class c WHERE i.inhrelid=$1 AND c.oid=i.inhparent ORDER BY i.inhseqno |
- Active queries:
SELECT * FROM pg_stat_activity WHERE state='active';
Result:
datid|datname |pid |usesysid|usename |application_name |client_addr|client_hostname|client_port|backend_start |xact_start |query_start |state_change |wait_event_type|wait_event|state |backend_xid|backend_xmin|query |backend_type | -----|--------|-----|--------|--------|--------------------|-----------|---------------|-----------|-------------------|-------------------|-------------------|-------------------|---------------|----------|------|-----------|------------|---------------------------------------------------|--------------| 16403|postgres|69206| 16385|postgres|DBeaver 5.3.2 - Main|127.0.0.1 | | 65255|2019-10-31 18:44:10|2019-10-31 19:49:20|2019-10-31 19:49:20|2019-10-31 19:49:20| | |active| |1761 |SELECT * FROM pg_stat_activity WHERE state='active'|client backend|
- Idle in transaction queries:
SELECT * FROM pg_stat_activity WHERE state='idle in transaction';
- Running queries with
PID
:
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
Result:
procpid|current_query | -------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 69206|SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, ¶ pg_stat_get_backend_activity(s.backendid) AS current_query¶FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s | 69207|SELECT t.oid,t.* FROM pg_catalog.pg_type t WHERE oid=$1 | 69602|select current_database() as database,¶ pg_size_pretty(total_database_size) as total_database_size,¶ schema_name,¶ table_name,¶ pg_size_pretty(total_table_size) as total_table_size,¶ pg_size_pretty(table_size) as table_size,¶| 69603|SELECT i.*,c.relnamespace FROM pg_catalog.pg_inherits i,pg_catalog.pg_class c WHERE i.inhrelid=$1 AND c.oid=i.inhparent ORDER BY i.inhseqno | 69707|select current_database() as database,¶ pg_size_pretty(total_database_size) as total_database_size,¶ schema_name,¶ table_name,¶ pg_size_pretty(total_table_size) as total_table_size,¶ pg_size_pretty(table_size) as table_size,¶| 69708|SELECT c.relname,a.*,ad.oid as attr_id,pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description¶FROM pg_catalog.pg_attribute a¶INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid)¶LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.att|
- Number of queries:
SELECT count(*) FROM pg_stat_activity;
- Number of queries grouped by state:
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
- Getting the longest running query:
SELECT max(now() - xact_start) FROM pg_stat_activity WHERE state IN ('idle in transaction', 'active');
- Getting the execution time running queries (transaction age):
SELECT pid, (now() -xact_start), state, query FROM pg_stat_activity WHERE state IN ('idle in transaction','active');
Focus: Lock
- Connections waiting for a lock:
SELECT count(distinct pid) FROM pg_locks WHERE granted = false;
- Listing locks that are currently open (or waiting) on your database rows and table:
SELECT t.relname,l.locktype,page,virtualtransaction,pid,mode,granted FROM pg_locks l, pg_stat_all_tables t WHERE l.relation=t.relid ORDER BY RELATION ASC;
Terminate / Kill Queries
A soft terminate:
SELECT pg_cancel_backend(process_pid);
If it is not enought, the kill:
SELECT pg_terminate_backend(process_pid);
Optimisation / Administration
Database Analysis
pgx_scripts is a repository full of scripts for analysing databases.
Some scripts for indexing optimisation:
- indexes/fk_no_index.sql: checking that all foreign keys are indexed.
- indexes/needed_indexes.sql: diagnosis missing indexes.
- indexes/unused_indexes.sql: unused indexes are bad for performances.
- duplicate_indexes/duplicate_indexes.sql: same as unused indexes, duplicated indexes are bad for performances
- columns/missing_PKs.sql: missing foreign keys.
Statement Timeout
Long running queries are poisonous and should be run on dedicated read only replica. For preventing from long running queries you can set a timeout:
ALTER DATABASE mydatabase SET statement_timeout = '60s';
You can do the same on not working transactions:
ALTER DATABASE mydatabase SET idle_in_transaction = '60s';
Query Monitoring
Set this for recording queries in order to understand your queries and optimise them:
CREATE EXTENSION pg_stat_statements;
You can also monitor only the slow queries:
ALTER DATABASE us SET log_min_duration_statement = '100ms';
More details here
Cleaning
After deleting a large number of data, rows and more specially BLOB, it is important to run some maintenance commands for freeing some space:
-- Updating tables statistics
VACUUM ANALYSE
By command line in a shell, deleting orphans BLOBs:
vacuumlo -v -h database_address -p data base_port -U database_user database_name
The cleaning, in SQL prompt:
VACUUM FULL
Tools
So far, the best client that I found is DBeaver.
Last releases of pgAdmin are full of bugs and missing features. I miss the version 3 which was just doing the job.
References
- A good article and many pointers to deep in database optimisation: Five Tips For a Healthier Postgres Database in the New Year