운영중인 DB가 느려진 것 같아서 인덱스 리빌드를 결정했다.

대상 선정을 위한 쿼리를 만들어 보았다.

SELECT
    PSUI.schemaname
    , PSUI.relname AS table_name
    , PSUI.indexrelname AS index_name
    , PSUI.idx_scan /* 인덱스가 사용된 횟수 */
    , PSUI.idx_tup_read /* 인덱스 튜플 수 */
    , PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
    , pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
    , 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
    LEFT OUTER JOIN pg_stat_all_indexes PSAI
        ON PSAI.relname = PSUI.relname
        AND PSAI.indexrelname = PSUI.indexrelname
ORDER BY PSUI.idx_tup_read DESC, PSUI.idx_scan DESC, pg_relation_size(PSAI.indexrelid) DESC
;
------------------------------------------------------------------------------------------------------------------------
-- 거의 사용되니 않는 인덱스 -> 삭제하는 게 나을 수 있겠다
SELECT
    PSUI.schemaname
    , PSUI.relname AS table_name
    , PSUI.indexrelname AS index_name
    , PSUI.idx_scan /* 인덱스가 사용된 횟수 */
    , PSUI.idx_tup_read /* 인덱스 튜플 수 */
    , PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
    , pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
    , 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
    LEFT OUTER JOIN pg_stat_all_indexes PSAI
        ON PSAI.relname = PSUI.relname
        AND PSAI.indexrelname = PSUI.indexrelname
WHERE PSUI.idx_scan < 100
ORDER BY PSUI.idx_scan DESC
;
------------------------------------------------------------------------------------------------------------------------
-- 크기가 너무 큰 인덱스
SELECT
    PSUI.schemaname
    , PSUI.relname AS table_name
    , PSUI.indexrelname AS index_name
    , PSUI.idx_scan /* 인덱스가 사용된 횟수 */
    , PSUI.idx_tup_read /* 인덱스 튜플 수 */
    , PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
    , pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
    , 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
    LEFT OUTER JOIN pg_stat_all_indexes PSAI
        ON PSAI.relname = PSUI.relname
        AND PSAI.indexrelname = PSUI.indexrelname
ORDER BY pg_relation_size(PSAI.indexrelid) DESC
LIMIT 50
;
------------------------------------------------------------------------------------------------------------------------
-- 테이블 싸이즈에 비해 크기가 너무 큰 인덱스
SELECT
    PSUI.schemaname
    , PSUI.relname AS table_name
    , PSUI.indexrelname AS index_name
    , PSUI.idx_scan /* 인덱스가 사용된 횟수 */
    , PSUI.idx_tup_read /* 인덱스 튜플 수 */
    , PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
    , pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
    , pg_size_pretty(pg_relation_size(T.oid)) AS table_size /* 테이블의 크기 */
    , ROUND(pg_relation_size(PSAI.indexrelid)::numeric / pg_relation_size(T.oid) * 100, 2) AS INDEX_RATIO
    , 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
    INNER JOIN pg_class T
        ON PSUI.relname = T.relname
    LEFT OUTER JOIN pg_stat_all_indexes PSAI
        ON PSAI.relname = PSUI.relname
        AND PSAI.indexrelname = PSUI.indexrelname
WHERE pg_relation_size(T.oid) > 0
AND pg_relation_size(PSAI.indexrelid) > 0
AND PSUI.idx_scan > 0
AND ROUND(pg_relation_size(PSAI.indexrelid)::numeric / pg_relation_size(T.oid) * 100, 2) > 100
ORDER BY INDEX_RATIO DESC
LIMIT 50
;
------------------------------------------------------------------------------------------------------------------------
-- 오래된 인덱스
SELECT
    PSUI.schemaname
    , PSUI.relname AS table_name
    , PSUI.indexrelname AS index_name
    , PSUI.idx_scan /* 인덱스가 사용된 횟수 */
    , PSUI.idx_tup_read /* 인덱스 튜플 수 */
    , PSUI.idx_tup_fetch /* 인덱스에서 실제 검색된 튜플 수 */
    , pg_size_pretty(pg_relation_size(PSAI.indexrelid)) AS index_size /* 인덱스의 크기 */
    , pg_stat_get_last_vacuum_time(PSAI.indexrelid) AS last_vacumm
    , 'REINDEX INDEX CONCURRENTLY ' || PSUI.schemaname || '.' || PSUI.indexrelname || ';'
FROM pg_stat_user_indexes PSUI
    LEFT OUTER JOIN pg_stat_all_indexes PSAI
        ON PSAI.relname = PSUI.relname
        AND PSAI.indexrelname = PSUI.indexrelname
WHERE PSUI.idx_scan = 0
OR PSAI.idx_scan = 0
ORDER BY last_vacumm ASC NULLS FIRST
;

PostgreSQL AccessShareLock 이해 및 해제 가이드

AccessShareLock이란?

• Oracle의 TM LOCK과 유사: 데이터를 읽는 동안(SELECT) 테이블 구조 변경을 막기 위해 걸리는 락입니다.
• 성능에 미치는 영향: 일반적으로 성능 저하를 유발하지 않지만, 테이블 구조 변경 작업 시 방해가 될 수 있습니다.
• PostgreSQL에서의 역할: 데이터의 일관성 유지에 기여합니다.

Lock 조회 및 해제

1. Lock 조회:

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;

• relname: 락이 걸린 테이블 이름
• locktype: 락 종류 (AccessShareLock 등)
• pid: 프로세스 ID
• mode: 락 모드

2. Lock 해제:

• 단일 프로세스 종료:

  SELECT pg_cancel_backend(PID);

  • 해당 PID의 프로세스만 종료합니다.
• 상위 쿼리까지 종료:

  SELECT pg_terminate_backend(PID) FROM pg_stat_activity;

  • PID와 관련된 모든 상위 쿼리까지 종료합니다.

3. 실행 중인 쿼리 상태 조회:

select * from pg_stat_activity;

주의 사항

• pg_terminate_backend는 강제 종료이므로 데이터 손실 가능성이 있습니다. 신중하게 사용해야 합니다.
• 락 해제 전 반드시 원인을 파악하고 해결하는 것이 좋습니다. 락이 자주 발생하는 경우, 쿼리 최적화나 데이터베이스 설정 변경이 필요할 수 있습니다.

요약

PostgreSQL의 AccessShareLock은 데이터 일관성을 위해 필요한 락입니다. 일반적으로 문제가 되지 않지만, 테이블 구조 변경 등 특정 상황에서 방해가 될 수 있습니다. 위의 SQL 쿼리를 사용하여 Lock을 조회하고 해제할 수 있으며, pg_terminate_backend를 사용할 때는 주의가 필요합니다.

더 자세한 정보는 PostgreSQL 공식 문서를 참고하세요.

핵심:

• AccessShareLock은 SELECT 시 걸리는 락으로, 일반적으로 성능에 영향을 주지 않습니다.
• Lock을 조회하고 해제하는 방법을 알아두면 문제 해결에 도움이 됩니다.
• pg_terminate_backend는 강력한 명령어이므로 신중하게 사용해야 합니다.

array_agg is an aggregate function 에러는 pg_get_functiondef가 aggregate 함수나 window 함수에 대해서 동작하지 않을 때 발생할 수 있습니다. 이 문제를 해결하려면 함수 정의를 얻는 다른 방법을 사용하거나, pg_proc에서 특정 조건을 추가로 설정해줘야 할 수 있습니다.

아래는 이 문제를 해결할 수 있는 개선된 쿼리입니다:

1. Aggregate 및 Window 함수 제외:

PostgreSQL에서는 집계 함수와 일반 함수가 모두 pg_proc에 저장되지만, 집계 함수와 window 함수는 pg_get_functiondef로 정의를 조회할 수 없습니다. 이를 필터링하려면 proisagg와 proiswindow 컬럼을 활용할 수 있습니다.

SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_functiondef(p.oid) AS function_definition
FROM
    pg_proc p
JOIN
    pg_namespace n ON p.pronamespace = n.oid
WHERE
    NOT p.proisagg -- 집계 함수가 아닌 것
    AND NOT p.proiswindow -- 윈도우 함수가 아닌 것
    AND pg_get_functiondef(p.oid) LIKE '%검색할_문자열%';

2. PostgreSQL 11 이상 (prokind 사용):

PostgreSQL 11 이상에서는 pg_proc에 prokind 컬럼이 추가되어, 함수 유형을 더 쉽게 필터링할 수 있습니다. prokind = 'f'는 일반 함수를 의미합니다.

SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_functiondef(p.oid) AS function_definition
FROM
    pg_proc p
JOIN
    pg_namespace n ON p.pronamespace = n.oid
WHERE
    p.prokind = 'f' -- 일반 함수만 선택
    AND pg_get_functiondef(p.oid) LIKE '%검색할_문자열%';

3. 특정 스키마에 대해서만 검색 (옵션):

SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_functiondef(p.oid) AS function_definition
FROM
    pg_proc p
JOIN
    pg_namespace n ON p.pronamespace = n.oid
WHERE
    p.prokind = 'f'
    AND n.nspname = '특정_스키마명'
    AND pg_get_functiondef(p.oid) LIKE '%검색할_문자열%';

위 쿼리 중 하나를 사용하면 pg_get_functiondef에서 발생하는 문제를 방지하고, 함수 정의를 성공적으로 검색할 수 있을 것입니다.

PostgreSQL 에서 sysdate 쓰는법

PostgreSQL에서는

    SELECT SYSDATE()

라는 쿼리문으로 현재 시스템의 시간이 출력되지 않는다.
(오라클에서는 FROM DUAL이 필요하지만 PostgreSQL에서는 필요하지 않다.

PostgreSQL에서 현재 시간을 알기 위해서는

    SELECT NOW();

의 쿼리를 사용하면 된다.

시간의 형식도 가져 오고 싶다면

    TO_CHAR( NOW(), 'YYMMDD')::NUMERIC

와 같이 ::(두개의 세미콜론)과 변환할 타입을 선언해 준다.