FILLFACTOR

PostgreSQL의 FILLFACTOR란 무엇인가요?
'FILLFACTOR'는 데이터베이스 테이블이나 인덱스 페이지가 데이터로 채워지는 양을 제어하는 PostgreSQL의 저장 매개변수이는 특히 빈번한 업데이트 또는 삽입과 관련된 작업의 경우 데이터베이스 성능에 큰 영향을 미칠 수 있는 중요한 조정 매개변수임.

 

FILLFACTOR의 주요 개념:

페이지 구조:
PostgreSQL은 고정된 크기(일반적으로 8KB)의 블록(또는 페이지)에 테이블 및 인덱스 데이터를 저장함.
각 페이지에는 여러 행의 데이터가 포함되어 있음.
FILLFACTOR는 테이블이나 인덱스가 처음 채워질 때 각 페이지의 몇 퍼센트가 데이터로 채워지는지를 결정함.

 

기본 필팩터:
기본적으로 PostgreSQL은 'FILLFACTOR' 100을 사용함.
즉, 초기 데이터 로드 중에 페이지를 완전히 채움..
이렇게 하면 저장 효율성이 극대화되지만 향후 업데이트를 위해 페이지에 추가 공간이 남지 않음.

FILLFACTOR 설정:
FILLFACTOR는 10~100 사이의 값으로 설정할 수 있음.
'FILLFACTOR'(예: 70 또는 80)가 낮을수록 향후 업데이트 또는 삽입을 위해 각 페이지에 더 많은 여유 공간이 남음..

성능에 미치는 영향:

읽기가 많은 작업 부하:
자주 읽히지만 거의 업데이트되지 않는 테이블이나 인덱스의 경우 일반적으로 더 높은 'FILLFACTOR'(100에 가까움)가 더 좋음.(스토리지 효율성을 최대화하고 필요한 페이지 수를 최소화하기 때문) 

 

업데이트가 많은 작업 부하:
자주 업데이트되는 테이블이나 인덱스의 경우 'FILLFACTOR'를 낮추는 것이 좋음.
기존 페이지가 가득 차면 데이터가 새 페이지로 이동되는 페이지 분할과 같은 비용이 많이 드는 작업의 필요성이 줄어듬.

삽입 작업:
낮은 'FILLFACTOR'는 특히 삽입되는 데이터가 자주 업데이트되는 경우 페이지 분할 가능성을 줄여 대량 삽입 작업에 도움이 됨.

페이지 분할:
업데이트나 삽입에 페이지에서 사용할 수 있는 것보다 더 많은 공간이 필요한 경우 PostgreSQL은 페이지 분할을 수행해야 함..여기에는 일부 데이터를 새 페이지로 이동하는 작업이 포함되며 이는 비용이 많이 드는 작업
잘 선택된 FILLFACTOR는 향후 성장을 위해 페이지 공간을 예약하여 페이지 분할 수를 최소화할 수 있음..

스토리지 효율성과 업데이트 효율성:
'FILLFACTOR'가 높을수록 각 페이지에 더 많은 데이터가 포함되므로 사용 가능한 저장 공간을 더 잘 활용할 수 있다.
'FILLFACTOR'가 낮을수록 업데이트 및 삽입 중 더 나은 성능을 위해 일부 저장 효율성을 희생하여 페이지 분할 가능성을 줄이고 행을 함께 유지.

모니터링 및 조정:
FILLFACTOR를 설정한 후에는 특히 페이지 분할 빈도와 I/O 작업에 미치는 영향에 중점을 두고 데이터베이스 성능을 모니터링해야 함..
성능 문제가 발견되면 관찰된 작업 부하 패턴에 따라 FILLFACTOR를 위아래로 조정해야 할 수도 있음..

 

실제 사용 사례:
과도하게 업데이트되는 테이블: 행이 자주 업데이트되는 테이블(예: 상태가 지속적으로 변경되는 세션 테이블)이 있는 경우 'FILLFACTOR'를 70-80으로 설정하면 도움이 될 수 있음.
자주 업데이트되는 열의 인덱스: 자주 업데이트되는 열(예: 타임스탬프 또는 상태 필드)에 인덱스가 생성되는 경우 인덱스의 'FILLFACTOR'가 낮을수록 페이지 분할 필요성이 줄어들고 성능이 향상될 수 있음.

성능...

데이터 대량 로드:
나중에 자주 업데이트될 테이블에 대량의 데이터를 로드하는 경우 로드하기 전에 'FILLFACTOR'를 더 낮게 설정하는 것이 도움이 될 수 있음.

 

예:
애플리케이션과 사용자의 상호 작용을 추적하는 user_activity 테이블이 있다고 가정. 사용자가 시스템과 상호 작용할 때 테이블이 자주 업데이트될때

SQL

CREATE TABLE user_activity ( user_id int, 활동_유형 텍스트, 활동_시간 타임스탬프, 세부정보 텍스트 ) WITH (FILLFACTOR = 80);

여기서 'FILLFACTOR'를 80으로 설정하면 PostgreSQL이 처음에 각 페이지를 80% 용량까지만 채우고 향후 업데이트를 위해 20%를 무료로 남겨둔다는 의미. 이렇게 하면 사용자의 활동 세부정보가 업데이트될 때 페이지를 분할할 필요성이 줄어듬..

결론:
FILLFACTOR는 PostgreSQL 성능, 특히 업데이트나 삽입이 자주 발생하는 테이블과 인덱스의 성능을 최적화하기 위한 강력한 도구로, 적절한 'FILLFACTOR'를 신중하게 선택하면 저장 효율성과 페이지 분할과 같은 비용이 많이 드는 작업을 최소화해야 하는 필요성 사이의 균형을 맞출 수 있다.

'FILLFACTOR'를 설정한 후 데이터베이스 성능을 모니터링하는 것은 원하는 효과가 있는지 확인하는 데 중요함.

 

 

아래는 이미 생성된 테이블/인덱스에 대해 적용하는 스크립트.

DO $$ 
DECLARE 
    partition RECORD;
BEGIN
    FOR partition IN 
        SELECT inhrelid::regclass AS partition_name 
        FROM pg_inherits 
        WHERE inhparent = '스키마.테이블명'::regclass 
    LOOP
        EXECUTE format('ALTER TABLE %s SET (FILLFACTOR = 80);', partition.partition_name);
    END LOOP;
END $$;




DO $$ 
DECLARE 
    index_name RECORD;
BEGIN
    FOR index_name IN 
        SELECT indexname 
        FROM pg_indexes 
        WHERE tablename LIKE '%인덱스 명%' 
          
    LOOP
        BEGIN
            EXECUTE format('ALTER INDEX %I SET (FILLFACTOR = 80);', index_name.indexname);
        EXCEPTION
            WHEN others THEN
                RAISE NOTICE 'Index % does not exist or cannot be altered.', index_name.indexname;
        END;
    END LOOP;
END $$;