개발을 하다보면 랜덤으로 데이터를 응답해줘야하는 상황이 생깁니다.
그럴 때 우리는 크게 두 가지 방법 중 하나를 선택할 수 있습니다.
1) DB Query로 랜덤으로 추출하기.
2) 로직에서 랜덤으로 추출하기.
보통은 1번에서 ORDERBY RANDOM()함수를 많이 사용합니다. 그런데 RANDOM() 함수가 아닌 테이블을 샘플링 해서 랜덤으로 가져올 수 있다는 것 알고 계셨나요?
오늘은 갯수가 크게 상관 없을 경우 고려 가능한 TABLESAMPLE 절에 대하여 정리해보려고 합니다. 추가로 PostgresSQL에서 제공해주는 샘플링 메서드도 알아볼 것입니다.
TABLESAMPLE 절
TABLESAMPLE은 16년 PostgresSQL에서 처음 도입되었습니다. (PostgresSQL 9.5 버전부터 사용이 가능) 외에 SQL Server, MariaDB에도 제공됩니다. MySQL과 SQLite 등에는 TABLESAMPLE을 지원하지 않습니다.
대규모 테이블로부터 랜덤 샘플 데이터를 추출하기 위하여 도입되었습니다.
데이터 크기가 크고, 전체 데이터를 읽지 않고 빠른 추출이 필요할 때 TABLESAMPLE을 사용한다면 성능상 이점을 얻을 수 있습니다. 그러나 테이블의 데이터가 적거나, 쿼리의 결과값과 갯수가 일정해야 하는 경우는 다른 방식을 사용하는 것이 좋습니다. (이유는 아래 주의점에서)
- TABLESAMPLE은 주로 통계 분석 및 테스트 / 러신러닝 모델의 학습용 데이터 추출 / 데이터 품질 평가 등에 활용될 수 있습니다.
사용 방법 및 주의점
TABLESAMPLE은 테이블의 데이터를 샘플링하여 랜덤한 결과를 반환합니다. 사용 방법은 다음과 같습니다.
SELECT *
FROM table_name
TABLESAMPLE sampling_method (10);
TABLESAMPLE 뒤에 샘플링 메서드(sampling_method)를 써서 사용합니다. 샘플링 방식 뒤에는 샘플링 비율(숫자)를 입력해 전체 데이터의 몇 퍼센트를 추출할지 결정합니다.
여기서 중요한 것은 '퍼센트'인데요.
SELECT * FROM employees TABLESAMPLE BERNOULLI(20) LIMIT 10;
만약 이런 쿼리를 작성했다고 생각했을 때 여러분은 어떤 결과값을 기대하시나요?
1) 10개 이하의 데이터를 가져왔으면 좋겠다.
2) 10개 데이터를 가져오면 좋겠다.
만약 1번이라면 아무런 문제가 없지만 2번을 기대하고 해당 쿼리를 작성했을 경우 문제가 발생합니다. TABLESAMPLE은 결과값의 갯수를 보장해주지 않기 때문입니다. 동일한 요청이라도 갯수가 매번 다를 수 있으므로 갯수가 중요한 상황의 경우 사용을 지양해야 합니다. 더불어 데이터가 확률적으로 선택되기 때문에 매우 작은 비율로 샘플링 할 경우 데이터가 하나도 반환되지 않을 수 있습니다.
PostgresSQL에서 제공하는 샘플링 메서드
1. SYSTEM()
블록 단위로 샘플링됩니다. 블록이란 행의 묶음이라고 이해하면 편할 것 같습니다. 블록 단위는 데이터베이스 설정, 스토리지 엔진에 의해 결정되고, PostgresSQL 기본 블록 크기는 8KB입니다.
블록 단위로 읽기 때문에 빠르지만, 데이터 균등하게 샘플링되지 않을 수 있습니다.
1부터 100까지의 숫자가 데이터 베이스에 들어있다고 가정하였을 때 1번 블록(1~10) 선택, 9번 블록(91~100) 선택이 된다면 11~90 사이의 숫자가 전혀 존재하지 않게 됩니다. 이처럼 블록 단위이기 때문에 데이터가 몰리는 상황이 발생할 수 있습니다.
하지만 데이터 균등성이 중요하지 않다면 잘 사용할 수 있는 메서드입니다.
SELECT *
FROM orders
TABLESAMPLE SYSTEM(10); -- 테이블의 10%를 블록 단위로 샘플링
2. BERNOULLI()
개별 행 단위로 샘플링 됩니다.
1, 2, 3, 4 데이터가 있을 경우 1이 포함될 확률 5%, 2가 포함될 확률 5%, 3가 포함될 확률 5%...로 행마다 독립적인 확률로 포함 여부를 결정합니다.
SELECT *
FROM customers
TABLESAMPLE BERNOULLI(5); -- 테이블의 5%를 행 단위로 샘플링샘플링이 SYSTEM() 방식보다 균등하게 되기 때문에 다양한 데이터가 필요하고 더 값이 랜덤하게 나오는 것이 필요할 경우 사용할 수 있습니다.
마무리하며
오늘은 TABLESMPLE절에 대하여 정리해보았는데요. 저도 한 번 써봐야지~ 했다가 갯수가 일정하게 나오지 않아서 당황스러웠던 경험이 있었습니다. 😅 적재적소에 잘 사용한다면 매우 유용하게 사용할 수 있을 것 같습니다. 모두 끝까지 읽어주셔서 감사합니다.
관련된 글을 하나 첨부하며 글을 마칩니다. Order by random과의 차이를 다룬 글입니다.