Reference

PostgreSQL

조회, 집계, 날짜 연산, JSONB, 스키마 설계, 인덱스, CTE, 트랜잭션까지 PostgreSQL 핵심 개념과 쿼리 흐름을 카드형 레퍼런스로 정리합니다.

전체40개

현재40개

레퍼런스 검색

조회와 필터링

SELECT WHERE ORDER BY 기본

테이블에서 필요한 행을 고르고 정렬해서 읽는 PostgreSQL 조회의 가장 기본적인 패턴입니다.

SELECT id, title, created_at
FROM posts
WHERE published = true
ORDER BY created_at DESC;

JOIN 기본

여러 테이블에 나뉜 정보를 하나의 결과로 결합하는 PostgreSQL JOIN의 가장 기본적인 패턴입니다.

SELECT posts.title, users.name
FROM posts
JOIN users ON posts.user_id = users.id;

DISTINCT, LIMIT, OFFSET, 별칭

중복 제거, 결과 수 제한, 건너뛰기, 열 이름 바꾸기처럼 조회 결과를 다듬는 PostgreSQL 기본 문법을 함께 정리합니다.

SELECT DISTINCT category AS kind
FROM products
ORDER BY kind
LIMIT 10
OFFSET 20;

LEFT JOIN과 NULL 읽기

연결된 데이터가 없는 행까지 함께 보고 싶을 때 쓰는 LEFT JOIN과 결과의 NULL을 해석하는 방법을 정리합니다.

SELECT u.id, u.name, p.title
FROM users AS u
LEFT JOIN posts AS p ON p.user_id = u.id;

서브쿼리와 EXISTS

다른 쿼리의 결과를 조건이나 값으로 재사용하는 서브쿼리와, 존재 여부 판단에 강한 EXISTS 패턴을 함께 정리합니다.

SELECT name
FROM users AS u
WHERE EXISTS (
  SELECT 1
  FROM posts AS p
  WHERE p.user_id = u.id
);

WITH와 CTE 기본

복잡한 쿼리를 여러 단계로 나눠 읽기 쉽게 만드는 WITH 절과 공통 테이블 식의 기본 사용법을 정리합니다.

WITH recent_posts AS (
  SELECT id, user_id, title
  FROM posts
  WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT user_id, COUNT(*) AS post_count
FROM recent_posts
GROUP BY user_id;

날짜, 시간, interval 기본

timestamp, date, interval이 어떻게 다르고, 기간 계산과 시간 필터링을 어떻게 읽어야 하는지 정리합니다.

SELECT *
FROM events
WHERE created_at >= NOW() - INTERVAL '7 days';

NULL, COALESCE, 3값 논리

PostgreSQL에서 NULL이 단순 빈값이 아니라 별도 의미를 가지는 값이라는 점과, COALESCE 및 3값 논리를 어떻게 읽어야 하는지 정리합니다.

SELECT
  name,
  COALESCE(nickname, name) AS display_name
FROM users
WHERE deleted_at IS NULL;

집계와 분석

GROUP BY와 HAVING

행 하나씩이 아니라 묶음 단위로 집계하고, 집계 결과에 조건을 거는 PostgreSQL 기본 패턴입니다.

SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) >= 5;

윈도 함수 기본

행을 합쳐 하나로 줄이지 않으면서 순위나 누적값을 계산하는 PostgreSQL 윈도 함수의 핵심 패턴을 정리합니다.

SELECT
  user_id,
  created_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY created_at DESC, id DESC
  ) AS rn
FROM posts;

CASE WHEN과 조건부 집계

조건에 따라 값을 나누어 계산하거나, 특정 조건을 만족하는 행만 집계할 때 쓰는 CASE WHEN 패턴을 정리합니다.

SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_amount,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_count
FROM orders;

recursive CTE 기본

조직도나 카테고리 트리처럼 계층 구조를 단계적으로 따라 내려가야 할 때 쓰는 recursive CTE의 기본 흐름을 정리합니다.

WITH RECURSIVE category_tree AS (
  -- anchor: 시작점 (최상위 카테고리)
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- recursive member: 이전 단계 결과를 다음 단계 입력으로
  SELECT c.id, c.parent_id, c.name, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT *
FROM category_tree
ORDER BY depth, name;

date_trunc와 generate_series

시간 구간별 집계와 빠진 날짜 채우기에서 자주 쓰는 `date_trunc`와 `generate_series` 패턴을 정리합니다.

SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*) AS count
FROM orders
GROUP BY 1
ORDER BY 1;

aggregate FILTER와 ORDER BY

PostgreSQL aggregate 함수에서 FILTER, DISTINCT, aggregate 내부 ORDER BY를 사용해 조건부 집계와 순서 의존 집계를 안전하게 작성하는 기준입니다.

SELECT
  count(*) AS total_orders,
  count(*) FILTER (WHERE status = 'paid') AS paid_orders,
  sum(amount) FILTER (WHERE status = 'paid') AS paid_amount,
  array_agg(id ORDER BY created_at DESC) AS recent_order_ids
FROM orders;

GROUPING SETS, ROLLUP, CUBE

PostgreSQL에서 여러 집계 레벨을 한 번에 계산하는 GROUPING SETS, ROLLUP, CUBE의 차이와 NULL 판별 기준을 정리합니다.

-- 필요한 집계 조합을 직접 지정
GROUP BY GROUPING SETS ((region, product), (region), ())

-- 계층형 소계: region, region+product, 전체
GROUP BY ROLLUP (region, product)

-- 가능한 모든 조합: region, product, region+product, 전체
GROUP BY CUBE (region, product)

window frame ROWS, RANGE, GROUPS

PostgreSQL 윈도 함수에서 frame이 어떤 행을 계산 대상으로 삼는지, ROWS, RANGE, GROUPS와 기본 frame의 차이를 정리합니다.

sum(amount) OVER (
  PARTITION BY account_id
  ORDER BY occurred_at, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_amount

percentile과 ordered-set aggregate

percentile_cont, percentile_disc, mode처럼 WITHIN GROUP을 사용하는 PostgreSQL ordered-set aggregate의 쓰임과 해석 기준을 정리합니다.

SELECT
  percentile_cont(0.5) WITHIN GROUP (ORDER BY response_ms) AS p50,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY response_ms) AS p95,
  percentile_disc(0.95) WITHIN GROUP (ORDER BY response_ms) AS p95_observed
FROM request_logs
WHERE created_at >= now() - interval '1 hour';

데이터 조작

INSERT UPDATE DELETE 기본

데이터를 넣고 바꾸고 지우는 PostgreSQL의 기본 데이터 조작 흐름을 한 번에 정리한 카드입니다.

INSERT INTO posts (title, published) VALUES ('Hello SQL', false);

UPDATE posts SET published = true WHERE id = 10;

DELETE FROM posts WHERE id = 10;

RETURNING과 UPSERT

INSERT와 UPDATE 뒤에 바로 결과를 돌려받는 RETURNING과, 충돌 시 갱신으로 이어지는 UPSERT 패턴을 정리합니다.

INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name
RETURNING id, email, name;

JSONB 기본과 조회

관계형 스키마 안에서 유연한 속성을 함께 저장할 때 많이 쓰는 JSONB와, 기본 조회 연산을 어떻게 읽어야 하는지 정리합니다.

SELECT
  profile ->> 'nickname' AS nickname
FROM users
WHERE profile ->> 'country' = 'KR';

COPY와 대량 적재

CSV나 파일 데이터를 PostgreSQL에 빠르게 넣을 때 쓰는 COPY, psql \copy, 대량 적재 후 검증 기준을 정리합니다.

COPY staging_users (email, name, created_at)
FROM '/var/lib/postgresql/import/users.csv'
WITH (FORMAT csv, HEADER true);

스키마와 제약

CREATE TABLE 기본

열 이름, 자료형, 기본값, 제약 조건이 모여 테이블 구조를 만드는 CREATE TABLE의 핵심 문법을 정리합니다.

CREATE TABLE posts (
  id         BIGSERIAL PRIMARY KEY,
  title      TEXT NOT NULL,
  published  BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

PRIMARY KEY, UNIQUE, NOT NULL

PostgreSQL 스키마 설계에서 가장 자주 쓰는 기본 제약 조건인 PRIMARY KEY, UNIQUE, NOT NULL의 역할을 비교해 정리합니다.

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  nickname TEXT UNIQUE,
  name TEXT NOT NULL
);

외래 키와 삭제 규칙

테이블 사이의 관계를 안전하게 유지하는 외래 키와, 부모 행 삭제 시 어떤 동작을 할지 정하는 규칙을 정리합니다.

CREATE TABLE posts (
  id      BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title   TEXT NOT NULL
);

트리거와 함수

CREATE TRIGGER의 실행 시점(BEFORE/AFTER/INSTEAD OF), PL/pgSQL 함수 기초, NEW/OLD 레코드 참조 방법을 정리합니다.

-- 트리거 함수 정의
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

-- 트리거 연결: INSERT/UPDATE 직전에 실행
CREATE TRIGGER trg_set_updated_at
  BEFORE INSERT OR UPDATE
  ON articles
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

배열 타입

PostgreSQL 배열 선언, ANY/ALL 연산자, unnest로 행 전개, GIN 인덱스로 배열 검색 성능을 확보하는 방법을 정리합니다.

-- 배열 컬럼 선언 및 삽입
CREATE TABLE articles (
  id       bigserial PRIMARY KEY,
  title    text,
  tags     text[],
  scores   integer[]
);

INSERT INTO articles (title, tags, scores)
VALUES ('PostgreSQL 배열', ARRAY['db', 'sql', 'postgres'], ARRAY[90, 85, 92]);

-- 포함 검색 + GIN 인덱스
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

SELECT id, title FROM articles
WHERE tags @> ARRAY['postgres'];  -- 'postgres' 태그를 포함하는 행

-- 요소 접근 (1-based)
SELECT tags[1] FROM articles;  -- 'db'

VIEW와 materialized view

PostgreSQL VIEW와 materialized view의 차이, 조회 시점과 갱신 방식, 성능과 일관성 기준을 정리합니다.

IDENTITY, SERIAL, sequence

PostgreSQL 자동 증가 키를 만들 때 쓰는 IDENTITY, SERIAL, sequence의 관계와 재시작, 동시성, gap 특성을 정리합니다.

CREATE TABLE users (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

테이블 partitioning 기본

큰 테이블을 range, list, hash partition으로 나누는 이유와 partition pruning, 인덱스, 운영 작업의 경계를 정리합니다.

CREATE TABLE events (
  id BIGINT GENERATED BY DEFAULT AS IDENTITY,
  occurred_at TIMESTAMPTZ NOT NULL,
  payload JSONB NOT NULL,
  PRIMARY KEY (occurred_at, id)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_05
PARTITION OF events
FOR VALUES FROM (TIMESTAMPTZ '2026-05-01 00:00:00+00')
             TO (TIMESTAMPTZ '2026-06-01 00:00:00+00');

인덱스와 성능

인덱스 기본

조회 속도를 높이기 위해 자주 쓰는 인덱스의 개념과, 어떤 열에 왜 인덱스를 거는지의 기본 판단 기준을 정리합니다.

CREATE INDEX idx_posts_user_id
ON posts (user_id);

EXPLAIN ANALYZE 기본

PostgreSQL이 쿼리를 어떻게 실행하는지 읽는 가장 기본적인 도구인 EXPLAIN ANALYZE의 핵심 해석 포인트를 정리합니다.

EXPLAIN ANALYZE
SELECT *
FROM posts
WHERE user_id = 10;

복합, partial, covering index

인덱스를 단순히 추가하는 수준을 넘어, 복합 인덱스와 partial index, INCLUDE 컬럼을 어떤 기준으로 설계하는지 정리합니다.

-- 복합 인덱스: 자주 함께 필터링·정렬되는 열을 묶는다
CREATE INDEX idx_orders_user_created_at
ON orders (user_id, created_at DESC);

-- partial index: 자주 조회되는 조건을 인덱스 자체에 새긴다
CREATE INDEX idx_orders_paid_only
ON orders (created_at DESC)
WHERE status = 'paid';

-- covering index: 결과 열까지 인덱스에 포함해 heap 접근을 없앤다
CREATE INDEX idx_orders_covering
ON orders (user_id, created_at DESC)
INCLUDE (amount, status);

VACUUM, ANALYZE, autovacuum

PostgreSQL이 테이블 통계를 갱신하고 죽은 튜플을 정리하는 `VACUUM`, `ANALYZE`, `autovacuum`의 역할을 정리합니다.

VACUUM ANALYZE orders;

전문 검색 (Full-Text Search)

tsvector/tsquery의 동작 원리, GIN 인덱스로 전문 검색 성능을 확보하는 방법, LIKE 검색과의 성능 차이를 정리합니다.

-- tsvector 컬럼 생성 + GIN 인덱스
ALTER TABLE articles ADD COLUMN search_vec tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX idx_articles_fts ON articles USING GIN (search_vec);

-- 검색 + 관련성 정렬
SELECT id, title,
       ts_rank(search_vec, query) AS rank
FROM articles,
     to_tsquery('english', 'postgresql & index') query
WHERE search_vec @@ query
ORDER BY rank DESC
LIMIT 20;

B-tree, GIN, GiST, BRIN index

PostgreSQL 인덱스 access method인 B-tree, GIN, GiST, BRIN이 어떤 데이터와 연산에 맞는지 비교합니다.

CREATE INDEX idx_users_email ON users USING btree (email);
CREATE INDEX idx_docs_tags ON docs USING gin (tags);
CREATE INDEX idx_logs_created_at_brin ON logs USING brin (created_at);

트랜잭션과 안전성

트랜잭션과 SAVEPOINT

여러 SQL 문을 하나의 안전한 작업 단위로 묶는 트랜잭션과, 중간 지점으로 되돌아가는 SAVEPOINT를 정리합니다.

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

SAVEPOINT after_withdraw;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

isolation level과 row locking

트랜잭션을 안전하게 묶는 것에서 한 걸음 더 나아가, 동시에 일어나는 작업 사이 간섭을 어떤 수준으로 막을지 정리합니다.

BEGIN;
WITH next_job AS (
  SELECT id
  FROM jobs
  WHERE status = 'queued'
  ORDER BY created_at, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'processing'
WHERE id = (SELECT id FROM next_job)
RETURNING id;
COMMIT; -- 여기까지가 선점, 실제 작업은 transaction 밖에서 처리

행 수준 보안 (RLS)

RLS 정책으로 테이블 행 접근을 사용자별로 제한하는 방법, USING과 WITH CHECK의 차이, BYPASSRLS 역할의 위험성을 정리합니다.

-- 1. table privilege와 RLS를 각각 설정
GRANT SELECT, INSERT, UPDATE, DELETE ON posts TO app_user;
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- 2. 정책 생성: 자신의 게시글만 읽기/쓰기
CREATE POLICY posts_owner
  ON posts
  TO app_user
  USING     (author_id = current_setting('app.user_id', true)::bigint)
  WITH CHECK (author_id = current_setting('app.user_id', true)::bigint);

-- 3. pool 재사용을 막기 위해 transaction 범위에서 컨텍스트 설정
BEGIN;
SET LOCAL app.user_id = '42';
SELECT * FROM posts;
COMMIT;

lock과 deadlock

PostgreSQL에서 잠금 대기와 deadlock이 생기는 이유, `pg_locks`로 확인할 지점, 트랜잭션 순서 설계 기준을 정리합니다.

SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;

role, GRANT, privilege

PostgreSQL role과 privilege의 관계, `GRANT`/`REVOKE` 기본 문법, 애플리케이션 계정 권한을 줄이는 기준을 정리합니다.

CREATE ROLE app_user LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;

GRANT CONNECT ON DATABASE appdb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE posts TO app_user;

REVOKE DELETE ON TABLE posts FROM app_user;

Loading category cards

Preparing references and filters for this topic. 이 주제의 레퍼런스와 필터를 준비하고 있습니다.

WITH RECURSIVE category_tree AS ( -- anchor: 시작점 (최상위 카테고리) SELECT id, parent_id, name, 1 AS depth FROM categories WHERE parent_id IS NULL UNION ALL -- recursive member: 이전 단계 결과를 다음 단계 입력으로 SELECT c.id, c.parent_id, c.name, ct.depth + 1 FROM categories c JOIN category_tree ct ON c.parent_id = ct.id ) SELECT * FROM category_tree ORDER BY depth, name;

SELECT count(*) AS total_orders, count(*) FILTER (WHERE status = 'paid') AS paid_orders, sum(amount) FILTER (WHERE status = 'paid') AS paid_amount, array_agg(id ORDER BY created_at DESC) AS recent_order_ids FROM orders;

-- 필요한 집계 조합을 직접 지정 GROUP BY GROUPING SETS ((region, product), (region), ()) -- 계층형 소계: region, region+product, 전체 GROUP BY ROLLUP (region, product) -- 가능한 모든 조합: region, product, region+product, 전체 GROUP BY CUBE (region, product)

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY response_ms) AS p50, percentile_cont(0.95) WITHIN GROUP (ORDER BY response_ms) AS p95, percentile_disc(0.95) WITHIN GROUP (ORDER BY response_ms) AS p95_observed FROM request_logs WHERE created_at >= now() - interval '1 hour';

-- 트리거 함수 정의 CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN NEW.updated_at := now(); RETURN NEW; END; $$; -- 트리거 연결: INSERT/UPDATE 직전에 실행 CREATE TRIGGER trg_set_updated_at BEFORE INSERT OR UPDATE ON articles FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- 배열 컬럼 선언 및 삽입 CREATE TABLE articles ( id bigserial PRIMARY KEY, title text, tags text[], scores integer[] ); INSERT INTO articles (title, tags, scores) VALUES ('PostgreSQL 배열', ARRAY['db', 'sql', 'postgres'], ARRAY[90, 85, 92]); -- 포함 검색 + GIN 인덱스 CREATE INDEX idx_articles_tags ON articles USING GIN (tags); SELECT id, title FROM articles WHERE tags @> ARRAY['postgres']; -- 'postgres' 태그를 포함하는 행 -- 요소 접근 (1-based) SELECT tags[1] FROM articles; -- 'db'

CREATE TABLE events ( id BIGINT GENERATED BY DEFAULT AS IDENTITY, occurred_at TIMESTAMPTZ NOT NULL, payload JSONB NOT NULL, PRIMARY KEY (occurred_at, id) ) PARTITION BY RANGE (occurred_at); CREATE TABLE events_2026_05 PARTITION OF events FOR VALUES FROM (TIMESTAMPTZ '2026-05-01 00:00:00+00') TO (TIMESTAMPTZ '2026-06-01 00:00:00+00');

-- 복합 인덱스: 자주 함께 필터링·정렬되는 열을 묶는다 CREATE INDEX idx_orders_user_created_at ON orders (user_id, created_at DESC); -- partial index: 자주 조회되는 조건을 인덱스 자체에 새긴다 CREATE INDEX idx_orders_paid_only ON orders (created_at DESC) WHERE status = 'paid'; -- covering index: 결과 열까지 인덱스에 포함해 heap 접근을 없앤다 CREATE INDEX idx_orders_covering ON orders (user_id, created_at DESC) INCLUDE (amount, status);

-- tsvector 컬럼 생성 + GIN 인덱스 ALTER TABLE articles ADD COLUMN search_vec tsvector GENERATED ALWAYS AS ( to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, '')) ) STORED; CREATE INDEX idx_articles_fts ON articles USING GIN (search_vec); -- 검색 + 관련성 정렬 SELECT id, title, ts_rank(search_vec, query) AS rank FROM articles, to_tsquery('english', 'postgresql & index') query WHERE search_vec @@ query ORDER BY rank DESC LIMIT 20;

BEGIN; WITH next_job AS ( SELECT id FROM jobs WHERE status = 'queued' ORDER BY created_at, id LIMIT 1 FOR UPDATE SKIP LOCKED ) UPDATE jobs SET status = 'processing' WHERE id = (SELECT id FROM next_job) RETURNING id; COMMIT; -- 여기까지가 선점, 실제 작업은 transaction 밖에서 처리

-- 1. table privilege와 RLS를 각각 설정 GRANT SELECT, INSERT, UPDATE, DELETE ON posts TO app_user; ALTER TABLE posts ENABLE ROW LEVEL SECURITY; -- 2. 정책 생성: 자신의 게시글만 읽기/쓰기 CREATE POLICY posts_owner ON posts TO app_user USING (author_id = current_setting('app.user_id', true)::bigint) WITH CHECK (author_id = current_setting('app.user_id', true)::bigint); -- 3. pool 재사용을 막기 위해 transaction 범위에서 컨텍스트 설정 BEGIN; SET LOCAL app.user_id = '42'; SELECT * FROM posts; COMMIT;

CREATE ROLE app_user LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE; GRANT CONNECT ON DATABASE appdb TO app_user; GRANT USAGE ON SCHEMA public TO app_user; GRANT SELECT, INSERT, UPDATE ON TABLE posts TO app_user; REVOKE DELETE ON TABLE posts FROM app_user;