Useful PostgreSQL Query

• 테이블 용량 확인

--테이블 용량 확인
-- INDEX 미포함
select pg_relation_size('테이블명')
;

-- INDEX 포함
select pg_total_relation_size('테이블명')
;

-- 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
;

-- 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
;

-- 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;

 

• Connection 확인

-- connection 확인
select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal
from
(select count(*) used from pg_stat_activity) t1,
(select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
(select setting::int max_conn from pg_settings where name=$$max_connections$$) t3
;

 

• 실행중인 쿼리 확인

-- 실행중인 쿼리 확인
select *
from   pg_stat_activity
where  1=1
--and query like '%update%'
--and client_addr = 'IP주소'
order by client_addr
;

-- active 확인
-- show running queries (pre 9.2)
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc
;

 

• Query kill

-- query kill
SELECT pg_cancel_backend('PID')
;

--If the process cannot be killed, try:
SELECT pg_terminate_backend('PID')
;

 

• Lock 확인

-- lock 확인
SELECT relation::regclass, * FROM pg_locks WHERE NOT granted
;

-- lock 확인
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS current_statement_in_blocking_process
FROM   pg_catalog.pg_locks blocked_locks
JOIN   pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN   pg_catalog.pg_locks blocking_locks
ON     blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted
;

-- 락 쿼리 확인
WITH RECURSIVE req AS (
SELECT psa.datname
, psa.usename
, psa.pid
, pg_blocking_pids(psa.pid) AS blocking_by
, psa.application_name
, psa.wait_event_type
, psa.state
, psa.query_start
, psa.query
, ( SELECT array_agg(( SELECT relname
FROM pg_class pc
WHERE pc.oid = pl.relation ))
FROM pg_locks pl
WHERE pl.pid = psa.pid
AND locktype = 'relation'
) AS relation
, 0 AS level
, row_number() over() AS rn
FROM pg_stat_activity psa
WHERE cardinality(pg_blocking_pids(psa.pid))= 0
UNION
SELECT psa.datname
, psa.usename
, psa.pid
, pg_blocking_pids(psa.pid) AS blocking_by
, psa.application_name
, psa.wait_event_type
, psa.state
, psa.query_start
, psa.query
, ( SELECT array_agg(( SELECT relname FROM pg_class pc WHERE pc.oid = pl.relation ))
FROM pg_locks pl
WHERE pl.pid = psa.pid
AND locktype = 'relation'
) AS relation
, level + 1 as level
, req.rn
FROM pg_stat_activity psa
INNER JOIN req ON req.pid = any(pg_blocking_pids(psa.pid))
)
SELECT psa.datname
, psa.usename
, psa.pid
, psa.blocking_by
, psa.application_name
, psa.wait_event_type
, psa.state
, psa.relation
, now() - psa.query_start AS elapsed_time
, psa.query
FROM ( SELECT req.rn
, req.level
, req.datname
, req.usename
, req.pid
, req.blocking_by
, req.application_name
, req.wait_event_type
, req.state
, req.relation
, req.query_start
, req.query
, count( rn ) over( PARTITION BY rn ) AS blocked_cnt
FROM req
) AS psa
WHERE psa.blocked_cnt > 1
ORDER by psa.rn, psa.level, psa.blocking_by
;

 

• VACUUM

-- vacuum command
VACUUM (VERBOSE, ANALYZE);

 

• All databased and their sizes

-- all databases and their sizes
select * from pg_user;

 

• cache hit rates

-- 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;

 

• Dump database

-- Dump database on remote host to file
$ pg_dump -U username -h hostname databasename > dump.sql

 

 

• Import Dump

-- Import dump into existing database
$ psql -d newdb -f dump.sql