software

시스템 디자인 인터뷰는 엉망이다. 그래도 통과하는 방법

발행: (2026년 2월 19일 오후 04:44 GMT+9)
11 분 소요
원문: Dev.to

Source: Dev.to

1. Generated Columns (Stop Calculating in Application Code)

first_name과 last_name을 가진 users 테이블이 있습니다. 전체 이름이 필요한 모든 쿼리는 first_name || ' ' || last_name을 수행합니다. 매번. 그렇습니다.

ALTER TABLE users
  ADD COLUMN full_name text
  GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED;

-- Now you can query, index, and search on full_name directly
CREATE INDEX idx_users_full_name
  ON users USING gin(to_tsvector('english', full_name));

Real use case: products 테이블에서 price_with_tax를 매 API 응답마다 계산하고 있었습니다. 이를 생성된 컬럼으로 옮기니 애플리케이션 수준의 계산 코드가 수천 줄 사라졌고, 쿼리 속도가 3× faster (더 이상 SELECT 절에서 계산하지 않음)로 3배 빨라졌습니다.

Other good candidates:

  • age computed from birth_date
  • slug computed from title
  • total computed from price * quantity

2. LISTEN/NOTIFY (실시간 폴링 없이)

백그라운드 워커가 5초마다 데이터베이스를 폴링하면서 새로운 작업을 찾습니다. 비효율적입니다.

-- 작업 삽입 트리거에서:
CREATE OR REPLACE FUNCTION notify_new_job()
RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify(
    'new_job',
    json_build_object(
      'id', NEW.id,
      'type', NEW.job_type,
      'priority', NEW.priority
    )::text
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER job_inserted
AFTER INSERT ON jobs
FOR EACH ROW EXECUTE FUNCTION notify_new_job();
// 워커에서 (pg 라이브러리 사용):
const { Client } = require('pg');
const client = new Client();
await client.connect();

await client.query('LISTEN new_job');

client.on('notification', (msg) => {
  const job = JSON.parse(msg.payload);
  console.log('New job:', job.id, job.type);
  processJob(job);
});

폴링이 전혀 없습니다. 데이터베이스가 이벤트를 워커에게 푸시합니다. 1분에 12번의 쿼리를 수행하던 폴링 루프를 LISTEN/NOTIFY 로 교체하면서 데이터베이스 부하가 40 % 감소했습니다.

3. CTEs (WITH Queries) – 가독성 높은 복잡한 쿼리를 위해

당신의 50줄짜리 중첩 서브쿼리는 올바르지만 아무도 읽을 수 없습니다. 당신 자신도 3개월 후에는 읽지 못할 겁니다.

-- Before: nested subquery nightmare
SELECT u.name,
       t.total_orders,
       t.total_revenue
FROM   users u
JOIN   (
  SELECT user_id,
         COUNT(*) AS total_orders,
         SUM(
           SELECT SUM(oi.price * oi.quantity)
           FROM   order_items oi
           WHERE  oi.order_id = o.id
         ) AS total_revenue
  FROM   orders o
  WHERE  o.created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
) t ON t.user_id = u.id
WHERE  t.total_revenue > 1000;
-- After: CTE version (same result, readable)
WITH recent_orders AS (
  SELECT id, user_id, created_at
  FROM   orders
  WHERE  created_at > NOW() - INTERVAL '30 days'
),
order_totals AS (
  SELECT o.user_id,
         COUNT(DISTINCT o.id)          AS total_orders,
         SUM(oi.price * oi.quantity)   AS total_revenue
  FROM   recent_orders o
  JOIN   order_items oi ON oi.order_id = o.id
  GROUP BY o.user_id
)
SELECT u.name,
       ot.total_orders,
       ot.total_revenue
FROM   users u
JOIN   order_totals ot ON ot.user_id = u.id
WHERE  ot.total_revenue > 1000;

같은 쿼리 계획입니다. 훨씬 더 가독성이 높습니다. 각 CTE 블록은 하나의 작업만 수행하며 명확한 이름을 가지고 있습니다.

4. JSONB Operations (When You Need Flexibility)

사용자마다 가변적인 메타데이터를 저장해야 할 때가 있습니다. 어떤 사용자는 company가 있고, 어떤 사용자는 department가 있으며, 예측할 수 없는 사용자 정의 필드도 있습니다. 50개의 nullable 컬럼을 만들 필요는 없습니다.

ALTER TABLE users
  ADD COLUMN metadata jsonb DEFAULT '{}';

값 설정

UPDATE users
SET    metadata = metadata || '{"company":"Acme","role":"CTO"}'
WHERE  id = 123;

특정 JSON 필드 조회 (인덱스 가능!)

SELECT *
FROM   users
WHERE  metadata->>'company' = 'Acme';

JSON 필드에 대한 부분 인덱스

CREATE INDEX idx_users_company
  ON users ((metadata->>'company'))
  WHERE metadata->>'company' IS NOT NULL;

JSON 데이터 집계

SELECT metadata->>'company' AS company,
       COUNT(*)               AS cnt
FROM   users
WHERE  metadata->>'company' IS NOT NULL
GROUP BY metadata->>'company';

규칙:

  • 구조화되고 쿼리 가능한 데이터 → 일반 컬럼.
  • 가변적이고 반구조화된 데이터 → JSONB.

모든 경우에 JSONB를 사용하지 마세요(타입 안전성과 제약 조건을 잃게 됩니다). 하지만 완전히 피할 필요도 없습니다—PostgreSQL의 강력한 기능이 바로 이것입니다.

5. 윈도우 함수 (GROUP BY 없이 분석)

SELECT o.id,
       o.user_id,
       o.total,
       o.created_at,
       -- Running total for this user
       SUM(o.total) OVER (
         PARTITION BY o.user_id
         ORDER BY o.created_at
       ) AS running_total,
       -- User's average order value
       AVG(o.total) OVER (PARTITION BY o.user_id) AS avg_order_value,
       -- This order's rank among user's orders (by value)
       RANK() OVER (
         PARTITION BY o.user_id
         ORDER BY o.total DESC
       ) AS value_rank,
       -- Percent of user's total spending
       o.total::numeric /
       SUM(o.total) OVER (PARTITION BY o.user_id) * 100 AS pct_of_total
FROM   orders o
ORDER BY o.user_id, o.created_at;

한 번의 쿼리. 서브쿼리 없음. 애플리케이션 수준의 후처리 없음. 윈도우 함수는 PostgreSQL에서 가장 과소 활용되는 기능입니다.

6. Exclusion Constraints (중복 예약 방지)

예약 시스템을 구축하고 있습니다. 같은 방에 대해 두 개 이상의 예약이 겹쳐서는 안 됩니다. 애플리케이션 코드에서 충돌을 검사하지만, 레이스 컨디션이 발생할 수 있습니다—두 요청이 동시에 검사하고, 둘 다 충돌이 없다고 판단한 뒤 삽입이 이루어질 수 있습니다.

CREATE TABLE bookings (
  id         serial PRIMARY KEY,
  room_id    int NOT NULL,
  tsrange    tsrange NOT NULL,   -- e.g. '[2024-01-01 10:00,2024-01-01 12:00)'
  EXCLUDE USING gist (
    room_id WITH =,
    tsrange WITH &&
  )
);

EXCLUDE 절은 특정 room_id에 대해 두 행이 겹치는 시간 범위(&&)를 갖지 않도록 보장합니다. 데이터베이스가 이 규칙을 원자적으로 적용하므로 레이스 컨디션이 완전히 사라집니다.

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE bookings ADD CONSTRAINT no_overlap 
  EXCLUDE USING gist (
    room_id WITH =,
    tstzrange(start_time, end_time) WITH &&
  );

-- This INSERT succeeds:
INSERT INTO bookings (room_id, start_time, end_time) 
VALUES (1, '2026-03-01 09:00', '2026-03-01 10:00');

-- This INSERT fails (overlaps):
INSERT INTO bookings (room_id, start_time, end_time) 
VALUES (1, '2026-03-01 09:30', '2026-03-01 10:30');
-- ERROR: conflicting key value violates exclusion constraint "no_overlap"

레이스 컨디션이 발생할 여지가 없습니다. 데이터베이스가 제약 조건 수준에서 이를 강제합니다. 어느 정도의 동시 요청이 있더라도 겹치는 예약이 생성되지 않습니다.

7. 행 수준 보안 (WHERE 절 없이 다중 테넌시)

다중 테넌트 애플리케이션의 모든 쿼리는 WHERE tenant_id = ?를 포함합니다. 개발자 한 명이라도 이를 빼먹으면 테넌트 간에 데이터가 유출됩니다.

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_setting('app.current_tenant')::int);

-- In your application middleware:
await db.query("SET app.current_tenant = $1", [tenantId]);

-- Now every query is automatically filtered:
SELECT * FROM documents;
-- PostgreSQL adds WHERE tenant_id = automatically
-- Impossible to accidentally see another tenant's data

이제 모든 쿼리가 자동으로 필터링됩니다:

PostgreSQL이 자동으로 WHERE tenant_id = 절을 추가합니다.
다른 테넌트의 데이터를 실수로 보는 일은 불가능합니다.

기업 SaaS 애플리케이션이 다중 테넌시를 처리하는 방식입니다. 격리는 데이터베이스가 보장하며, 애플리케이션 코드가 보장하는 것이 아닙니다.

8. 부분 인덱스 (쿼리하는 것만 인덱스)

Your orders table has 10 M rows. Only 50 K are status = 'pending'. An index on status would cover all 10 M rows for queries that only care about the 50 K pending rows.

-- Instead of this (indexes all 10 M rows):
CREATE INDEX idx_orders_status ON orders(status);

-- Do this (indexes only pending orders):
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- Your query:
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;
-- Uses the tiny partial index. Lightning fast.

Partial indexes are smaller, faster to update, and faster to scan. Use them when you frequently query a specific subset of your data.

공통된 실

이 모든 기능은 애플리케이션의 로직을 데이터베이스로 옮깁니다. 이는 의도된 설계입니다.

당신의 데이터베이스는:

  • 트랜잭션 지원 — 동시 작업을 올바르게 처리합니다
  • 최적화 — 수십 년에 걸친 쿼리 플래닝 작업이 적용됩니다
  • 영속성 — 애플리케이션 충돌 및 배포 후에도 살아남습니다
  • 단일 진실 소스 — 모든 데이터 변경이 여기서 이루어집니다

데이터베이스 기능을 중복하는 애플리케이션 코드 한 줄은 버그, 레이스 컨디션, 일관성 문제를 일으킬 수 있는 여지를 남깁니다.

PostgreSQL이 잘하는 일을 맡기세요. 애플리케이션 코드는 비즈니스 로직, 사용자 경험, 외부 서비스와의 통합을 담당해야 합니다. 데이터 무결성, 데이터에 대한 계산, 접근 제어? 바로 데이터베이스가 담당합니다.

대부분의 사람들이 사용하지 않는 당신이 가장 좋아하는 PostgreSQL 기능은 무엇인가요? 새로운 트릭을 항상 찾고 있습니다. 댓글에 남겨 주세요.

0 조회
#PostgreSQL #generated columns #SQL #database performance #indexing #application code reduction #devtips
원문 보기
Share:
Back to Blog

관련 글

더 보기 »