저는 요즘 회사에서 개발.. 을 하지 않고 분석, 설계 일을 서포트하고 있습니다. 분석을 위한 데이터가 워낙 커서 여러 고민을 했습니다. 그중에서 테이블에 행 수를 어떻게 빨리 볼 수 있을까 하는 고민을 했고, 과정을 공유하고자 합니다.
2.8억 개가 맞는지 하나씩 세어보도록
제가 다루고 있는 데이터 중 가장 큰 테이블은 2.8억 개의 행을 가지고 있습니다.
-- 20분 넘게.. 걸려서 중지..
select count(*)
from very_big_table; -- 풀스캔으로 학대 중평소에는 아무 생각 없이 테이블의 행 수를 보기 위해 사용한 이 쿼리가 20분이나 넘게 돌아가고 있었습니다.
이 테이블은 21~23년도 데이터인데, 월별로 파티셔닝 했습니다. 약 7,500만의 데이터를 가진 테이블이 36개나 묶여있습니다.
csv파일에서 Database로 마이그레이션 할 때! 마침 상사가 얼마나 데이터가 들어갔냐고 물어봤을 때! 데이터 분석에 필요할 때! 등 당장에 데이터 수가 필요한데, 커피 한 잔 마시고도 데이터 수를 알 수 없는 건 힘든 일입니다.
PostgreSQL 시스템 카탈로그 테이블
이 문제를 해결하기 위해 PostgreSQL의 시스템 카탈로그 테이블을 활용하는 방법을 고려할 수 있습니다. 특히, pg_stat_all_tables, pg_inherits, pg_class 세 가지 카탈로그 테이블이 중요한 역할을 합니다.
- pg_stat_all_tables: 각 테이블에 대한 통계 정보를 포함하며, n_live_tup 필드를 통해 "살아있는(삭제되지 않은)" 행의 추정 개수를 나타냅니다.
- pg_inherits: 테이블 상속과 파티셔닝 정보를 저장하며, 파티션 테이블과 각 파티션 간의 관계를 나타냅니다.
- pg_class: 모든 테이블, 인덱스, 뷰 등의 메타데이터를 포함하며, 테이블의 OID(객체 식별자)를 저장합니다.
다음 쿼리는 very_big_table이라는 파티셔닝된 테이블의 전체 행 수를 추정합니다:
SELECT SUM(n_live_tup) AS total_estimated_row_count
FROM pg_stat_all_tables
WHERE relid IN (
SELECT inhrelid
FROM pg_inherits
WHERE inhparent IN (
SELECT oid
FROM pg_class
WHERE relname = 'very_big_table'
)
);- 이 쿼리는 먼저 pg_class에서 very_big_table테이블의 OID를 찾고
- pg_inherits를 통해 해당 테이블의 모든 파티션 OID를 찾습니다.
- 그런 다음 pg_stat_all_tables에서 이 OID들에 해당하는 n_live_tup 값을 합산하여, 각 파티션의 행 수를 추정합니다.
결론
이 방법은 파티셔닝된 테이블의 전체 행 수를 빠르고 효율적으로 추정할 수 있게 해 줍니다. 하지만 주의할 점은, 이 방법이 제공하는 것은 정확한 행 수가 아니라 추정치라는 것입니다. 따라서, 테이블의 행 수가 정확히 일치해야 하는 경우에는 실제 COUNT(*) 쿼리를 사용하는 것이 좋습니다.
참고
- 파티셔닝되지 않은 테이블에서도 pg_stat_all_tables의 n_live_tup 값을 통해 행 수를 추정할 수 있습니다.
SELECT n_live_tupAS total_estimated_row_count
FROM pg_stat_all_tables
WHERE relname = 'very_big_table';- 시스템 카탈로그 테이블은 데이터베이스 유지 관리 작업(예: VACUUM, ANALYZE)이 수행될 때 업데이트되므로, 최신의 정확한 데이터를 얻기 위해서는 이러한 유지 관리 작업을 정기적으로 실행하는 것이 중요합니다.
Share article
