DBA가 보낸 접속 정보에 database, schema, table이 따로 있습니다. MySQL에서 쓰던 방식으로 PostgreSQL에 접속했더니 "schema not found"가 납니다. 계층 구조를 이해하지 못하면 "database가 없다"와 "table이 없다"를 같은 오류로 혼동하고, 어디를 어떻게 만들어야 하는지 알 수 없습니다.

확대

5단계 계층 구조

관계형 데이터베이스는 아래 다이어그램과 같이 5단계 계층으로 구성됩니다. 가장 바깥쪽 Database Cluster는 PostgreSQL 서버 인스턴스 자체이고, 그 안에 여러 Database가 존재합니다. 각 Database 안에 Schema(네임스페이스)가 있고, Schema 안에 Table이 있습니다. Table은 열(Column)과 행(Row)으로 이루어진 2차원 구조입니다.

확대

각 계층의 역할

PostgreSQL vs MySQL — Schema 개념 차이

PostgreSQL과 MySQL은 Schema 개념이 완전히 다릅니다. PostgreSQL은 Database와 Schema를 명확히 구분합니다. 하나의 Database 안에 여러 Schema가 공존할 수 있고, 각 Schema는 독립된 네임스페이스로 테이블을 격리합니다. 반면 MySQL/MariaDB에서는 DATABASE와 SCHEMA가 완전히 같은 의미입니다. MySQL에는 PostgreSQL 방식의 중간 Schema 계층이 존재하지 않습니다.

\c ecommerce

CREATE SCHEMA analytics;

CREATE TABLE analytics.daily_stats (
    date   DATE PRIMARY KEY,
    visits INT
);

SELECT * FROM public.users;
SELECT * FROM users;

실행 완료 또는 조회 결과가 표시됩니다.

CREATE DATABASE ecommerce;
CREATE SCHEMA ecommerce;

USE ecommerce;

Schema 활용 예시 — 멀티 테넌트 구조

SaaS 서비스에서 고객사별로 Schema를 분리하면, 동일한 Database에서 고객 데이터를 논리적으로 격리할 수 있습니다. search_path를 설정하면 쿼리에서 스키마 이름을 생략할 수 있어 코드 변경 없이 테넌트를 전환할 수 있습니다.

CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_bigcorp;

CREATE TABLE tenant_acme.users (id SERIAL PRIMARY KEY, name VARCHAR(100));
CREATE TABLE tenant_bigcorp.users (id SERIAL PRIMARY KEY, name VARCHAR(100));

SET search_path = tenant_acme;
SELECT * FROM users;

운영 서버에서 컬럼 하나를 추가하려는데 ALTER TABLE이 수백만 건 테이블에서 수 분간 락을 잡습니다. 더 나쁜 경우, DROP COLUMN을 잘못 치면 롤백이 안 됩니다. DDL은 DML과 달리 대부분 즉시 커밋되고, 실수의 복구 비용이 매우 높습니다. 각 DDL 명령어가 어떻게 동작하는지 정확히 알아야 스키마 변경을 안전하게 할 수 있습니다.

확대

CREATE TABLE — 실전 예시

아래는 이커머스 상품 테이블의 완성도 높은 설계 예시입니다. 기본키에는 자동 증가 정수(SERIAL)를 사용하고, 외래키에는 삭제 정책을 명시합니다. 가격과 재고에는 CHECK 제약으로 음수를 방지하고, status 컬럼은 허용된 값 목록을 CHECK로 제한합니다. 자주 검색되는 category_id와 status 컬럼에는 별도 인덱스를 생성합니다.

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    category_id INT NOT NULL
                REFERENCES categories(id)
                ON DELETE RESTRICT,
    name        VARCHAR(255) NOT NULL,
    slug        VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    price       DECIMAL(12, 2) NOT NULL
                CHECK (price >= 0),
    stock       INT NOT NULL DEFAULT 0
                CHECK (stock >= 0),
    status      VARCHAR(20) NOT NULL DEFAULT 'active'
                CHECK (status IN ('active', 'inactive', 'discontinued')),
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_status   ON products(status);

-- PostgreSQL: 컬럼 + 인덱스 + 제약을 한 번에
\d products

-- MySQL: 컬럼 정의와 인덱스 확인
DESCRIBE products;
SHOW INDEX FROM products;

                                      Table "public.products"
   Column    |           Type           | Nullable |               Default
-------------+--------------------------+----------+--------------------------------------
 id          | integer                  | not null | nextval('products_id_seq'::regclass)
 category_id | integer                  | not null |
 price       | numeric(12,2)            | not null |
 status      | character varying(20)    | not null | 'active'::character varying
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
    "idx_products_category" btree (category_id)
    "idx_products_status" btree (status)
Check constraints:
    "products_price_check" CHECK (price >= 0)
    "products_status_check" CHECK (status::text = ANY (ARRAY['active','inactive','discontinued']))

주요 컬럼 제약조건

각 제약조건의 역할을 이해하고 적절히 조합하는 것이 중요합니다. NOT NULL은 필수 입력 필드를 보장하고, UNIQUE는 이메일처럼 중복 불가한 값을 관리합니다. DEFAULT는 생략 가능한 값에 자동으로 초기값을 넣어줍니다. CHECK는 비즈니스 규칙을 DB 레벨에서 강제합니다. NOT NULL + DEFAULT 조합이 가장 흔한 실전 패턴입니다.

email      VARCHAR(255) NOT NULL
email      VARCHAR(255) UNIQUE
created_at TIMESTAMP DEFAULT NOW()
is_active  BOOLEAN DEFAULT TRUE
age        INT CHECK (age >= 0 AND age <= 150)
price      DECIMAL CHECK (price > 0)
status     VARCHAR(20) CHECK (status IN ('active','inactive'))
stock      INT NOT NULL DEFAULT 0

ALTER TABLE — 테이블 구조 변경

운영 중인 테이블의 구조를 변경할 때는 작업 유형마다 위험도가 다릅니다. 새 컬럼 추가(ADD COLUMN)는 기존 데이터에 영향이 없어 안전합니다. 반면 컬럼 타입 변경이나 대용량 테이블에 NOT NULL 추가는 전체 테이블 재작성(Table Rewrite)을 유발해 서비스 중단을 일으킬 수 있습니다. NOT NULL 추가는 반드시 2단계(DEFAULT 설정 → NULL 제거)로 나눠 진행합니다.

ALTER TABLE products
ADD COLUMN weight_kg DECIMAL(8, 3);

ALTER TABLE products
ADD COLUMN is_featured BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE products
RENAME COLUMN description TO product_description;

ALTER TABLE products
ALTER COLUMN price TYPE DECIMAL(15, 2);

ALTER TABLE products ALTER COLUMN weight_kg SET DEFAULT 0;
UPDATE products SET weight_kg = 0 WHERE weight_kg IS NULL;
ALTER TABLE products ALTER COLUMN weight_kg SET NOT NULL;

ALTER TABLE products DROP COLUMN weight_kg;

ALTER TABLE products ADD CONSTRAINT chk_price CHECK (price >= 0);
ALTER TABLE products DROP CONSTRAINT chk_price;

DROP 명령어 주의사항

DROP TABLE은 테이블과 모든 데이터를 복구 불가능하게 삭제합니다. CASCADE 옵션은 이 테이블을 FK로 참조하는 다른 테이블의 제약조건까지 함께 삭제하므로 특히 주의가 필요합니다. TRUNCATE는 구조는 유지하면서 데이터만 전체 삭제하며, DELETE보다 훨씬 빠릅니다.

DROP TABLE products;

DROP TABLE products CASCADE;

DROP TABLE IF EXISTS products;

TRUNCATE TABLE products;
TRUNCATE TABLE products RESTART IDENTITY;

네이밍 컨벤션

일관된 네이밍 컨벤션은 팀 전체의 코드 가독성과 유지보수성을 높입니다. SQL 예약어(order, user, select)를 테이블명으로 사용하면 쿼리마다 따옴표로 감싸야 하므로 반드시 피합니다.

권장 규칙:
  테이블명:    복수형 스네이크케이스  → users, order_items, product_categories
  컬럼명:      단수형 스네이크케이스  → user_id, created_at, is_active
  PK:          id (또는 테이블명_id)  → id
  FK:          참조테이블_id          → user_id, category_id
  인덱스:      idx_테이블_컬럼        → idx_products_category_id
  제약조건:    chk_테이블_컬럼        → chk_products_price

피해야 할 것:
  ✗ 예약어 사용: order, user, select (따옴표로 감싸야 해서 불편)
  ✗ 대소문자 혼용: UserId, ProductName (DB마다 대소문자 처리 다름)
  ✗ 너무 짧은 이름: u, p, dt (가독성 저하)

CREATE TABLE은 컬럼 이름을 나열하는 선언처럼 보이지만, 실행되는 순간 DB는 그 정의를 파싱해 시스템 카탈로그에 기록하고, 저장 엔진은 이를 물리적인 행 포맷과 페이지 배치로 바꾸며, PK·제약은 인덱스와 삽입 규칙으로 반영됩니다. 이 4단계를 알면 "왜 나중에 타입 하나 바꾸는 게 그렇게 비싼지", "왜 이 INSERT가 제약 위반으로 거부되는지"를 구조적으로 이해할 수 있습니다.

[정의]  CREATE TABLE users (
          id    BIGSERIAL PRIMARY KEY,
          email VARCHAR(255) UNIQUE NOT NULL,
          ... )
   │
   ① 파싱·검증        문법·타입·참조 대상(FK 테이블)이 유효한지 확인
   │
   ② 카탈로그 등록     정의를 시스템 카탈로그(pg_class·pg_attribute)에 기록
   │                  = "이 테이블은 이런 컬럼·타입·기본값을 갖는다"
   │
   ③ 물리 구조 확정    저장 엔진이 행 포맷(컬럼 순서·NULL 비트맵)과
   │                  8KB 페이지 배치를 정하고 빈 테이블 파일을 만든다
   │
   ④ 제약·인덱스 반영  PK·UNIQUE → 자동 인덱스 생성,
   │                  NOT NULL·CHECK·DEFAULT → 이후 INSERT마다 강제될 규칙 등록
   ▼
[INSERT 시]  각 행이 ③의 포맷으로 페이지에 담기고, ④의 규칙을 통과해야 저장된다

각 단계에서 무슨 일이 일어나고, 여기서 어긋나면 무엇이 문제인가:

즉 "테이블을 만든다"는 이름을 적는 게 아니라 저장 포맷과 규칙을 확정하는 일입니다. 스키마 변경이 비싼 이유가 ②③에 있고(정의와 물리 구조를 다시 써야 하므로), 제약 위반·기본값·NULL 관련 오류는 ④에서 납니다. 그래서 컬럼 타입·제약·기본값은 실행 후 고치는 것보다 CREATE TABLE 시점에 신중히 정하는 편이 훨씬 쌉니다.

앞의 DecisionTable은 각 DDL이 "얼마나 오래 락을 잡는가"를 다뤘습니다. 그런데 현업에서 DDL로 장애가 나는 대부분은 무거운 테이블 재작성이 아니라, 밀리초면 끝날 빠른 DDL이 정작 락을 얻지 못해 대기하는 동안 벌어집니다. 한 단계 더 들어가 락의 종류와 큐가 어떻게 맞물리는지 봐야 이 함정을 피할 수 있습니다.

• AccessExclusiveLock의 파급력: ALTER TABLE은 대부분 가장 강한 락인 AccessExclusiveLock을 요구합니다. 이 락은 심지어 SELECT가 쓰는 가장 약한 락(AccessShareLock)과도 충돌합니다. 즉 DDL이 이 락을 쥐거나, 쥐려고 대기만 해도 단순 조회조차 통과하지 못합니다.
• 락 큐는 FIFO다: PostgreSQL은 락 요청을 대체로 도착 순서대로 부여합니다. 문제는 DDL이 앞선 롱 트랜잭션 때문에 락을 못 얻고 큐 맨 앞에서 기다릴 때입니다. 그 뒤로 도착한 평범한 SELECT는 "앞사람(DDL)이 원하는 락과 충돌"하므로 함께 줄을 섭니다. 롱 트랜잭션 하나 + 빠른 DDL 하나 = 테이블 전체 정지.
• 원흉은 대개 '느린 조회'나 'idle in transaction': 커밋을 깜빡한 세션, 리포트용 초장시간 SELECT, 커넥션 풀이 잡고 놓지 않은 트랜잭션이 흔한 범인입니다. DDL 자체는 죄가 없고, 앞에서 락을 놓지 않는 세션이 진짜 문제입니다.
• 안전장치는 lock_timeout: DDL을 실행하기 직전 SET lock_timeout = '3s'처럼 걸어 두면, 락을 그 시간 안에 못 얻은 DDL이 큐를 오래 막는 대신 스스로 실패합니다. 애플리케이션은 그 실패를 받아 한적한 시점에 재시도하면 됩니다. "무중단 마이그레이션"의 핵심은 CONCURRENTLY만이 아니라 이 짧은 lock_timeout + 재시도 조합입니다.

그래서 성숙한 팀은 마이그레이션 스크립트 맨 앞에 lock_timeout과 statement_timeout을 항상 심어 둡니다 — 마이그레이션이 실패해도 서비스는 살아 있어야 하니까요.