Passwd

[PostgreSQL] array 데이터 타입 다루기

개요프로그래밍을 하다 보면 당연히 배열 데이터를 많이 다루게 되는데, 간혹 배열 형식 데이터 자체를 데이터베이스에 저장할 필요가 있기도 하다. MySQL을 사용할 때는 문자열 형식으로 저장한 뒤 애플리케이션 영역에서 처리하는 방식으로 사용했는데, PostgreSQL의 경우에는 배열 형식의 데이터 타입이 존재하고 있었다.이번 글에서는 PostgreSQL에서 배열 형식의 데이터를 다뤄보도록 한다. 선언배열 타입은 데이터 타입 옆에 대괄호를 붙이거나 ARRAY 키워드를 사용해 선언한다.CREATE TABLE sal_emp ( name text, pay_by_quarter integer ARRAY, schedule text[][]);예시 쿼리의 경우에는 1차..

[PostgreSQL] :: - 형변환

개요일반적으로 데이터베이스에서 데이터 형을 변환할 때 CAST(value AS type)을 사용하는데, 최근 쿼리를 보니 PostgreSQL에서 제공하는 형변환 방법이 별도로 있는 것 같다.적어둔다. ::PostgreSQL은 :: 연산자를 사용해 간결하게 데이터 형변환을 수행할 수 있다.SELECT value::type# 예시SELECT CAST('2025-10-11 09:00:00' AS date) , '2025-10-11 09:00:00'::date;보면 CAST 결과와 :: 연산자의 결과가 동일한 모습을 확인할 수 있다. 참고 문서https://brownbears.tistory.com/309

개요2025.09.19-[PostgreSQL] EXPLAIN - 1에서 EXPLAIN에 대한 기초적인 부분을 살펴봤다. 이어서 이번 글에서는 다른 절이 추가됨에 따라 달라지는 실행 계획을 살펴보려고 한다. LIMITLIMIT을 추가하면 플래너는 모든 행을 검색할 필요가 없도록 수행할 작업을 변경한다. Index Scan 노드가 완료될 때까지 실행되는 것처럼 표시되지만 Limit 노드는 해당 행 중 3개 행만 검색한 후에 중지될 것이라 예상한다. 때문에 총비용도 그만큼 감소하게 된다.EXPLAINSELECT *FROM license_integratedWHERE collect_status = 'DONE'ORDER BY license_idLIMIT 3; JOIN - Nested LoopJoin의 경우에는..

개요데이터베이스에 쿼리를 실행할 때 기대하는 성능이 나오지 않는 경우가 있다. 대표적으로 데이터 조회에 너무 오랜 시간을 소모하는 상황인데, PostgreSQL의 경우 쿼리 실행 계획을 수립하고, 실행한다. 쿼리 구조와 데이터 속성에 맞는 계획을 선택해야 하기 때문에 데이터베이스는 좋은 계획을 선택하기 위한 플래너를 포함하고 있는데, EXPLAIN 명령을 사용하면 플래너가 생성한 쿼리 계 획을 확인할 수 있다. 필요시 이 계획을 바탕으로 쿼리 성능 개선을 시도해보아야 한다. EXPLAINEXPLAIN은 실행 계획을 확인할 쿼리문 맨 앞에 추가함으로써 사용한다. 기본적으로 텍스트로 결과를 반환한다.EXPLAIN [ ( option [, ...] ) ] statement쿼리 계획은 트리 구조로 되어 있고,..

개요일반적으로 데이터베이스에서 조건에 따른 집계 함수를 사용할 땐 아래와 같은 모습으로 사용하게 된다.SELECT aggregate_function(*)FROM table_nameWHERE column = value그런데 PostgreSQL에서는 집계 함수에 조건을 적용하는데 사용되는 FILTER절을 지원한다. 어떤 개념인지 알아본다. FILTERFILTER는 집계 함수에 조건을 적용해 특정 조건에 맞는 데이터만 집계할 수 있도록 한다.SELECT aggregate_function(column) FILTER (WHERE condition)FROM table_name;WHERE절 대신 집계 함수 뒤에 FILTER 절과 함께 조건을 지정해준다. 윈도우 함수와 함께 사용할 수도 있는데, 이 경우에는 FIL..

개요2023.05.22-[MySQL] DB 내 모든 테이블 ROW 수 확인하기에서 MySQL에서 데이터베이스의 테이블 행 수를 확인하는 방법을 적었었다. 이번 글에서는 기록 목적으로 PostgreSQL에서 데이터베이스 테이블 행 수를 확인하는 방법을 적어둔다. pg_stat_all_tablesPostgreSQL은 서버 운영 상태에 대한 정보를 수집하거나 보고하기 위한 작업을 위한 통계 수집기(statistics collectors)가 동작한다. 수집기는 인덱스의 디스크 블록 단위, 개별 행 단위 접근 회수, 각 테이블에 저장되어 있는 총 행 수, 각 테이블에 대한 VACUUM 작업, ANALYZE 작업에 대한 정보 등을 수집한다. 수집된 통계 정보는 여러 뷰에서 확인할 수 있는데, 그 중 테이블 행 수..

개요프로그래밍 방식으로 INSERT나 UPDATE를 사용하면 쿼리 작업으로 인해 얼마나 많은 레코드 영향을 받았는지 비교적 쉽게 확인할 수 있다. 하지만 조건에 따라 수행하는 작업이 달라지는 UPSERT의 경우에는 각 작업을 수행한 레코드 수를 확인하기가 여의치 않은데, 구글링을 해보니 적당한 방법이 있었다.정리해 둔다. XMAX의 용도2025.09.04-[PostgreSQL] MVCC - XMIN/XMAX에서 잠깐 다뤘던 XMAX는 크게 두 가지 용도로 사용된다.1. 데이터 행의 버전인 튜플을 삭제하거나 갱신한 트랜잭션의 ID를 기록한다.2. 행 lock을 저장하는 데 사용한다. PostgreSQL에서는 lock 테이블의 오버플로우를 피하기 위해 행 lock이 튜플에 저장된다. 하나의 트랜잭션만 행에..

개요MVCC는 예전에 회사에서 데이터베이스 관련 교육을 할 때 접했던 개념이었는데, 최근 PostgreSQL 쿼리에서 관련 키워드를 보게 되어 다시 들쳐보게 되었다. PostgreSQL의 주요 동작 방식과 연관이 있으니 잘 정리해 보자. MVCCMVCC란 다중 버전 동시서 제어(Multi Version Concurrency Control)의 줄임말로, 과거부터 현재까지의 다양하 버전 데이터에 대한 관리와 제공이 가능하지를 나타내는 DBMS의 필수 기능 중 하나이다. 시시각각 변경되고 있는 데이터 중에서 사용자가 데이터를 조회한 시점의 데이터를 정확하게 제공하는지를 나타내는 것으로, DBMS 마다 다르게 구현하고 있다. PostgreSQL - MVCC 구현과 XMAX/XMINPostgreSQL은 아래와..

개요이 블로그에서 UPSERT에 대해서 적게나마 다뤘는데, PostgreSQL도 MySQL처럼 UPSERT를 지원한다. 다만 문법에 조금 차이가 있는데, 이번 글에서는 PostgreSQL에서 UPSERT를 수행하는 방법을 적어둔다. INSERT ~ ON CONFLICTON CONFLICT 절은 UNIQUE 또는 EXCLUSION 제약 위배를 해소하기 위한 대체 동작을 지정한다.INSERT INTO table_name (column1, column2, ...)VALUES (value1, value2, ...)ON CONFLICT (conflict_target)conflict_actionINSERT 하는 행에 대해 conflict_target에 지정한 제약 조건이나 인덱스가 위배되었을 때 conflict_..

개요업무에서 PostgreSQL을 사용하게 되었는데, 쿼리문에서 RETURNING이라는 모르는 키워드를 확인해서 글을 작성한다. RETURNINGPostgreSQL의 RETURNING 절은 INSERT, UPDATE, DELETE와 같은 연산을 실행한 후 데이터를 가져올 수 있도록 한다. RETURNING을 사용하면 수정된 데이터를 수집하기 위해 추가적인 쿼리를 수행할 필요가 없고, 수정된 행을 안정적으로 식별할 수 있다. 때문에 성능 최적화, 코드 정리, 효율적인 데이터베이스 상호작용을 달성할 수 있다.일반적으로는 대상 테이블의 모든 열을 순서대로 선택하는 RETURINTG *을 사용한다.INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool'..