테이블 분할을 통한 PostgreSQL 대량 삭제 최적화-Golang-php.cn

테이블 분할을 통한 PostgreSQL 대량 삭제 최적화

Susan Sarandon

풀어 주다： 2024-10-08 06:08:02

원래의

706명이 탐색했습니다.

Optimizing PostgreSQL Mass Deletions with Table Partitioning

데이터베이스 관리에서는 대규모 데이터 작업을 효율적으로 처리하는 것이 중요합니다. 일반적인 과제 중 하나는 전체 성능을 저하시키지 않고 대규모 테이블에서 대량 삭제를 실행하는 것입니다. 이 기사에서는 PostgreSQL의 테이블 파티셔닝 기능이 어떻게 프로세스 속도를 크게 높이고
원활한 데이터베이스 운영을 유지하도록 도와주세요.

여기에서 더 많은 작업을 확인하세요.

대량 삭제의 과제

PostgreSQL 테이블에서 많은 수의 행을 삭제하는 작업은 시간이 많이 걸릴 수 있습니다. 여기에는 다음이 포함됩니다.

삭제할 행을 찾기 위해 테이블을 훑어보는 중
행 제거 및 색인 업데이트
테이블을 비워 공간 확보

수백만 개의 행이 있는 테이블의 경우 이 프로세스로 인해 장기 실행 트랜잭션 및 테이블 잠금이 발생하여 잠재적으로 데이터베이스 응답성에 영향을 미칠 수 있습니다.

테이블 파티셔닝 시작

테이블 파티셔닝은 큰 테이블을 파티션이라는 더 작고 관리하기 쉬운 조각으로 나누는 기술입니다. 이러한 파티션은 상위 테이블과 동일한 스키마를 공유하는 별도의 테이블입니다.

내 벤치마크 설정

파티셔닝의 이점을 수량화하기 위해 컨테이너화된 환경에서 PostgreSQL을 사용하여 세 가지 시나리오로 벤치마크를 설정했습니다.

간단한 테이블: 분할되지 않은 표준 테이블
파티션된 테이블(행 삭제): 주별로 파티션된 테이블, 첫 번째 주부터 행 삭제
파티션된 테이블(파티션 삭제): #2와 동일하지만 첫 번째 주의 파티션 전체를 삭제합니다

PostgreSQL 컨테이너 사양

PostgreSQL 버전: 16.4
도커 버전: 27.0.3
자원 제한:
- CPU 제한: 8개
- 메모리 제한: 1GB

데이터 특성

총 기록 : 400만개
분배 : 4주에 걸쳐 균등분배(주당 100만개)
인덱싱: 두 테이블(단순 테이블과 분할 테이블) 모두 시간 열에 인덱스가 있습니다

주요 결과

Scenario	Deletion Time	Table Size
Simple Table	1.26s	728 MB
Partitioned (Delete Rows)	734ms	908 MB
Partitioned (Drop Partition)	6.43ms	908 MB

획기적인 속도 개선: 파티션 삭제는 간단한 테이블에서 행을 삭제하는 것보다 196배 빠릅니다.
스토리지 장단점: 파티션을 나눈 테이블은 추가 메타데이터 및 파티션별 인덱스로 인해 약 25% 더 많은 스토리지를 사용합니다.
삽입 영향 최소화: 파티션을 나누면 데이터 입력 시간이 약간 증가합니다(약 2.8%).

작동하는 이유

대상 작업: 파티셔닝을 사용하면 데이터베이스가 데이터의 하위 집합으로 작업하여 작업 범위를 줄일 수 있습니다.
메타데이터 작업: 파티션 삭제는 기본적으로 메타데이터 작업이므로 개별 행을 검색하고 삭제할 필요가 없습니다.
잠금 경합 감소: 파티션이 작을수록 잠금 수가 적어 동시성이 향상됩니다.

구현 하이라이트

다음은 PostgreSQL에서 분할된 테이블을 설정하는 방법에 대한 간단한 예입니다.

CREATE TABLE records (<br>
    id BIGSERIAL,<br>
    time TIMESTAMPTZ NOT NULL,<br>
    body TEXT<br>
) PARTITION BY RANGE (time);

<p>CREATE TABLE records_week_1 PARTITION OF records<br>
    FOR VALUES FROM ('2023-01-01') TO ('2023-01-08');</p>

<p>-- Create index on the partition<br>
CREATE INDEX idx_records_week_1_time ON records_week_1 (time);</p>

<p>-- To delete a week's worth of data:<br>
ALTER TABLE records DETACH PARTITION records_week_1;<br>
DROP TABLE records_week_1;<br>
</p>

로그인 후 복사