데이터베이스/PostgreSQL8 [PostgreSQL] 인덱스를 활용한 효율적인 페이징 처리 페이징처리에 대한 중요성웹이나 백오피스 시스템에서 대량의 데이터를 다룰 때, 사용자에게 데이터를 한번에 모두 보여주기보다는 일정 수량 단위로 나눠서(page 단위로) 제공하는 방식, 즉 페이징(Pagination)이 거의 필수적으로 사용됩니다.하지만 많은 개발자들이 단순 OFFSET 기반 페이징을 사용하고 있고, 이로 인해 다음과 같은 문제가 발생할 수 있습니다:페이지가 뒤로 갈수록 성능이 급격히 저하됨필요 없는 데이터까지 스캔하거나 정렬하면서 리소스 낭비 발생사용자 경험(UX) 측면에서도 응답 지연이 발생OFFSET 방식의 문제점예를 들어 아래와 같은 쿼리가 있다고 가정해봅시다.SELECT * FROM ordersORDER BY created_at DESCOFFSET 10000 LIMIT 20;OFFS.. 2025. 6. 3. [PostgreSQL] group by 결과를 받아볼 수 있는 함수 SQL로 데이터 분석을 하다보면, 은근히 Group by 집계 쿼리문과 Having 후 필터링 조건문을 사용할 일이 많단 말이죠. 독자분들이 저와 같은 귀차니즘이라면 한 번 사용해보세요. 특히 pk가 논리적 erd로만 표현되어 있고, 물리적으로 생성되지 않아, 중복 데이터 유무를 조회해야 할 때 유용하게 사용 중입니다. ※ 지원 가능한 버전(Supported Versions): Current (17) / 16 / 15 / 14 / 13 1. Create Function함수를 생성합니다.물론 group by문으로 조회할 테이블과 같은 데이터베이스에 생성해야 합니다.group by 컬럼이 1개 이상이므로 jsonb 타입으로 집계처리 합니다.create or replace function public.. 2025. 1. 23. [PostgreSQL] 통계 정보 수집 (ANALYZE, VACCUM) 통계 정보 수집 방법1. 전체 데이터베이스에 대해 통계 정보 수집데이터베이스 내 모든 테이블의 통계 정보를 갱신하려면 다음 명령을 실행합니다:ANAYZE; 이 명령은 데이터베이스에 있는 모든 테이블과 열의 통계를 수집합니다. 2. 특정 테이블에 대해 통계 정보 수집특정 테이블만 통계 정보를 갱신하려면 테이블 이름을 명시합니다:ANALYZE your_table_name;해당 테이블의 모든 열에 대해 통계를 수집합니다.3. 특정 열에 대해 통계 정보 수집테이블의 특정 열에 대해서만 통계를 갱신하려면 열 이름을 명시합니다:ANALYZE your_table_name (your_column_name);이 방법은 열에 대한 통계 정보만 수집하므로 빠르게 실행됩니다.4. VACUUM과 함께 통계 정보 갱신VACUU.. 2025. 1. 3. [PostgreSQL] count 함수를 쓰지 않고, 테이블 건수 조회하는 방법 개요다들 데이터 업무를 하다보면 대용량 테이블의 건수를 대략적으로 빠르게 알고 싶을 때가 생길텐데요,이때 정확한 건수는 필요없고, 대략 이정도 건수라는 것만 알고 싶은 분들은 통계 정보를 활용하시면 됩니다. 통계 정보 활용-- 통계정보 갱신ANALYZE table_name;--통계정보로 미리 수집된 테이블 건수 조회SELECT reltuples::BIGINT AS approximate_row_countFROM pg_classWHERE relname = 'table_name';건수 조회 속도 테스트1. 테이블 구조- 단순한 date, code, value 테이블이여도 건수가 많아지면 단순 count 호출 속도가 느려집니다. 2. 건수별 조회 속도 비교통계 정보 조회하는 것이 압도적으로 빠름건수count 함.. 2025. 1. 3. [PostgreSQL] 비정형 데이터를 PostgreSQL에 저장하고 정형 테이블로 변환하기 개요API 호출 결과와 같이 비정형 데이터가 반환될 때, 데이터의 필드가 가변적일 경우 PostgreSQL에 비정형 데이터를 먼저 저장한 후, 필요한 필드를 정형 데이터로 변환하는 과정을 구현해 보겠습니다.1. 문제 정의1.1 API 호출 결과API 호출 결과는 JSON 배열로 반환되며, 각 객체는 필드 구성이 다를 수 있습니다.[{"code": "a", "name": "b", "city": "seoul"},{"code": "ab", "name": "bb", "amount": "1"}]1.2 요구 사항비정형 JSON 데이터를 PostgreSQL에 그대로 저장합니다.이후 필요한 필드만 추출하여 정형 테이블에 삽입합니다.2. 구현 과정2.1 API 호출API 호출은 Python의 requests 라이브러리를.. 2024. 11. 9. [PostgreSQL] 모든 테이블 read only User / role 권한 부여 개요모든 테이블에 대해 접근, 조회 권한만 부여된 유저를 전달하기 위함PostgreSQL 14 version 부터 default roles(Predefined Roles) 중에 read_only role이 내장되어 있어서 별도 생성하지 않아도 됨유저 생성, 권한 부여 예제8.1 version 부터 User, Group 이 모두 Role로 통합되었으므로, create role로 유저 객체를 생성하는 걸 권장CREATE ROLE "read_only_user" WITH PASSWORD 'abcde#fgh';grant pg_read_all_data to read_only_user;postgresql Predefined Roles 참고- 표 - 출처: https://www.postgresql.org/docs/cu.. 2024. 8. 16. 이전 1 2 다음 반응형
