Category Reference

PostgreSQL

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

Search cards

29 cards

Search titles, summaries, tags, and subcategories.

조회와 필터링

8 cards

조회와 필터링

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;

집계와 분석

5 cards

집계와 분석

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
  ) 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;

데이터 조작

3 cards

데이터 조작

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';

스키마와 제약

5 cards

스키마와 제약

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'

인덱스와 성능

5 cards

인덱스와 성능

인덱스 기본

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

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;

트랜잭션과 안전성

3 cards

트랜잭션과 안전성

트랜잭션과 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;

SELECT *
FROM jobs
WHERE status = 'queued'
FOR UPDATE SKIP LOCKED;

-- 작업 처리 후
UPDATE jobs SET status = 'processing' WHERE id = <picked_id>;

COMMIT;

트랜잭션과 안전성

행 수준 보안 (RLS)

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

-- 1. RLS 활성화
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- 2. 정책 생성: 자신의 게시글만 읽기/쓰기
CREATE POLICY posts_owner
  ON posts
  USING     (author_id = current_user_id())   -- SELECT/UPDATE/DELETE 필터
  WITH CHECK (author_id = current_user_id());  -- INSERT/UPDATE 검증

-- 3. 세션 컨텍스트 설정 후 쿼리 — 자동으로 WHERE 절이 삽입된다
SET app.current_user_id = '42';
SELECT * FROM posts;
-- 실제 실행: SELECT * FROM posts WHERE author_id = 42

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;

-- 트리거 함수 정의 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 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;

-- 1. RLS 활성화 ALTER TABLE posts ENABLE ROW LEVEL SECURITY; -- 2. 정책 생성: 자신의 게시글만 읽기/쓰기 CREATE POLICY posts_owner ON posts USING (author_id = current_user_id()) -- SELECT/UPDATE/DELETE 필터 WITH CHECK (author_id = current_user_id()); -- INSERT/UPDATE 검증 -- 3. 세션 컨텍스트 설정 후 쿼리 — 자동으로 WHERE 절이 삽입된다 SET app.current_user_id = '42'; SELECT * FROM posts; -- 실제 실행: SELECT * FROM posts WHERE author_id = 42