PostgreSQL (znany również jako psql) to baza danych typu open source, która rozpoczęła swoje istnienie w 1996 roku. Od tego czasu stała się jednym z najpopularniejszych silników bazodanowych typu open source na świecie, używanym przez miliony programistów, którzy potrzebują więcej funkcji niż standardowa baza danych MySQL. W tym poście podzielę się z wami zbiorem typowych zapytań, poleceń i fragmentów przydatnych podczas konserwacji i codziennego użytkowania bazy danych PostgreSQL, które zbierałem przez lata i teraz udostępniam.

Postgresql - Show running queries (pre 9.2)

SELECT procpid, age(clock_timestamp(), query_start), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

Postgresql - Show running queries (post 9.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 - Kill running query

SELECT pg_cancel_backend(procpid);

Postgresql - Kill idle query

SELECT pg_terminate_backend(procpid);

Postgresql - Vacuum command

VACUUM (VERBOSE, ANALYZE);

Postgresql - All database users

select * from pg_stat_activity where current_query not like '<%';

Postgresql - All databases and their sizes

select * from pg_user;

Postgresql - All tables and their size, with/without indexes

select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;

Postgresql - Cache hit rates (should not be less than 0.99)

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

Postgresql - Table index usage rates (should not be less than 0.99)

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 
ORDER BY n_live_tup DESC;

Postgresql - How many indexes are in cache

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;

Postgresql - Table index usage rates

SELECT relname, 
  CASE WHEN (seq_scan + idx_scan) != 0
    THEN 100.0 * idx_scan / (seq_scan + idx_scan) 
    ELSE 0
  END AS percent_of_times_index_used,
  n_live_tup AS rows_in_table
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

Postgresql - Check the size (as in disk space) of all databases

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;

Postgresql - Check the size (as in disk space) of each table

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;

Postgresql - Check currently hold locks

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;

Postgresql - Get all table sized

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size,
  table_size

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size

       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

Postgresql - Get schemas sizes

FROM (
       SELECT
         pg_catalog.pg_namespace.nspname                AS schema_name,
         sum(pg_relation_size(pg_catalog.pg_class.oid)) AS schema_size
       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
       group by 1
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY schema_size DESC;

Postgresql - Show unused indexes

SELECT relname AS table_name, indexrelname AS index_name, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass))
FROM pg_stat_all_indexes
WHERE schemaname = 'public'
AND idx_scan = 0
AND idx_tup_read = 0
AND idx_tup_fetch = 0
ORDER BY pg_relation_size(indexrelname::regclass) DESC;

Postgresql - Kill all running connections to a current database

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()  
  AND pid <> pg_backend_pid();

Postgresql - Find cardinality of index v1

SELECT relname, relkind, reltuples as cardinality, relpages 
FROM pg_class 
WHERE relname LIKE 'tableprefix%';

Postgresql - Find cardinality of index v2

SELECT schema_name,
       object_name,
       object_type,
       cardinality,
       pages
FROM (
       SELECT pg_catalog.pg_namespace.nspname AS schema_name,
              relname                         as object_name,
              relkind                         as object_type,
              reltuples                       as cardinality,
              relpages                        as pages
       FROM pg_catalog.pg_class
              JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
  and schema_name <> 'information_schema'
  --and schema_name = '$schema_name'
  --and object_name = '$object_name'
ORDER BY pages DESC, schema_name, object_name;

Postgresql - How many indexes are in cache

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;

Postgresql - Last Vacuum and Analyze time

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

Postgresql - Total number of dead tuples need to be vacuumed per table

select n_dead_tup, schemaname, relname from pg_stat_all_tables;

Postgresql - Total number of dead tuples need to be vacuumed in DB

select sum(n_dead_tup) from pg_stat_all_tables;

Source #1 Source #2

---

Similar searches: Postgresql commands / psql queries / psql common queries