[postgreSQL] 써보니 유용한 쿼리 (feat. 메타 정보 쿼리)

내가 직접 만든 테이블은 아니지만 postgreSQL에 내장되어있는 테이블이 존재한다.
실제로 잘 활용하는 쿼리를 사례와 함께 적어보려고 한다.

테이블 인덱스 확인

실행계획을 볼 때 인덱스를 아예 타고 있지 않거나 잘못된 인덱스를 타고 있음을 확인할 수 있다.
이 떄 보통 테이블에 어떤 인덱스가 있는지를 보고 의도하는 인덱스를 탈 수 있게 쿼리를 튜닝해준다.

SELECT * FROM pg_indexes WHERE tablename = '소문자 테이블 명';

현재 테이블별 전체 카운트 확인

테이블 별로 전체 카운트를 수시로 확인하면 얼마나 데이터 성장세가 가파른지, 어떻게 인프라를 대비해야하는지, 어떻게 데이터를 관리하는게 더 효율적인지 고민해볼 수 있다.
테이블별로 count 쿼리를 직접 때려도 되지만 몇천만, 몇억이 넘어가는 시점에는 일일히 카운트 조회하는 것이 굉장히 시간이 오래 걸리기때문에 활용이 어렵다. 그 때 100% 아니지만 대략적인 카운트를 알 수 있는 쿼리가 있다.
(pg_stat_user_tables 에는 그 밖에도 여러 컬럼이 있으니 들여다보면 좋을 것 같다)

SELECT schemaname,relname, n_live_tup, *
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

컬럼 정보 확인

새로운 테이블을 만들때 비슷한 성격의 열을 참고하자 할때 아래의 쿼리를 활용한다.
예를 들어, USER_ID 가 varchar(100) 인지 varchar(50) 인지 애매할때 뒤져보면 결정하기 쉽다.

SELECT 
	table_name,  
	column_name,  
    data_type,  
    character_maximum_length,  
    numeric_precision,   
    numeric_scale,
    is_nullable, 
    * 
FROM information_schema.COLUMNS 
WHERE column_name ILIKE '%USER_ID%';

 

세션 & 락 & 세션 KILL

pg_stat_activity 테이블을 활용하면 현재 활성화 중인 세션, 그 세션이 붙은 ip, 쿼리 등을 알 수 있다.
pg_locks와 조인하면 테이블의 다양한 LOCK 상태 확인도 가능하다.
딜레이되거나 행이 잡힌 세션이 있다면 pg_terminate_backend 함수를 활용해 세션을 kill 할 수도 있다.

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_lock;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity LIMIT 1;

위에서 안 들여다본 테이블이 있다면 꼭 현재 운영 중인 postgreSQL 데이터베이스에 쳐보면 좋은 경험이 될 것 같다.