데이터베이스 구조와 메타데이터 읽기
1. 계층 구조 : 테이블은 허공에 떠 있지 않다
PostgreSQL의 객체는 다음 계층으로 구성됩니다.
PostgreSQL 서버 (클러스터)
└─ 데이터베이스 (Database)
└─ 스키마 (Schema) ← 이 층이 초보자에게 낯설다
└─ 테이블 (Table)
└─ 컬럼 (Column)
스키마는 데이터베이스 안의 "폴더" 라고 이해하면 됩니다.
덕분에 서로 다른 스키마에 tb_info라는 같은 이름의 테이블이 공존할 수 있습니다.
지금 내가 어디에 있는지 확인하는 쿼리입니다.
SELECT current_database() AS 데이터베이스,
current_schema() AS 현재스키마,
current_user AS 접속계정;
실행 결과
데이터베이스 | 현재스키마 | 접속계정
--------------+-----------+----------
000 | public | postgresDBeaver를 쓴다면 상단 툴바에 public@000처럼 표시되는데, 같은 정보입니다.
2. "속성값"이라는 말을 쪼개기
DB를 처음 볼 때 가장 많이 뭉뚱그리는 표현이 "속성값" 입니다. 이건 서로 다른 두 층위를 붙여놓은 말입니다.
화면 위치 관계형 모델 용어 정체
| 화면위치 | 관계형 모델 용어 | 정체 |
| 결과 그리드의 헤더 | 속성(attribute) = 컬럼 | 메타데이터 (구조) |
| 1, 2, 3... 각 줄 | 튜플(tuple) = 행(row) | 실제 데이터 |
| 행과 열이 만나는 칸 | 값(value) | 실제 데이터 |
관계형 모델에서 속성(attribute) = 컬럼입니다. 값(value) 은 행 안에 들어있는 실제 데이터입니다.
이 구분이 왜 중요하냐면, 바꾸는 방법이 완전히 다르기 때문입니다.
ALTER TABLE t ADD COLUMN memo text; -- 구조(속성)를 바꾼다 → DDL
UPDATE t SET memo = '내용'; -- 값을 바꾼다 → DML
3. DBeaver 트리 ≠ PostgreSQL의 실제 구조
DBeaver에서 테이블을 펼치면 이런 폴더들이 나옵니다.
tb_xxx
├─ Columns
├─ Constraints
├─ Foreign Keys
├─ Indexes
├─ Dependencies
├─ References
├─ Partitions
├─ Triggers
├─ Rules
└─ Policies
여기서 속성(컬럼)에 해당하는 건 Columns 하나뿐입니다. 나머지는 속성이 아니라 그 테이블과 연결된 별개의 데이터베이스 객체들입니다.
그리고 중요한 점: PostgreSQL 자체의 계층은 테이블 > 컬럼까지입니다. 인덱스나 제약조건은 테이블 "안에" 들어있는 게 아니라, 별도의 시스템 카탈로그에 저장된 뒤 OID로 그 테이블을 가리키는 구조입니다. DBeaver가 사람 보기 편하라고 트리로 모아 보여주는 것뿐입니다.
각 폴더의 실제 출처
DBeaver 폴더 실제 저장 위치
| DBeaver폴더 | 실제 저장위치 |
| Columns | pg_attribute / information_schema.columns |
| Constraints | pg_constraint |
| Foreign Keys | pg_constraint (contype = 'f', conrelid 기준) |
| References | pg_constraint (contype = 'f', confrelid 기준) |
| Indexes | pg_index / pg_indexes |
| Triggers | pg_trigger |
| Rules | pg_rewrite |
| Policies | pg_policy (행 수준 보안, 9.5+) |
| Dependencies | pg_depend |
🔬 실험 1 : 트리와 쿼리 결과 대조하기
-- Columns
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'tb_cd'
ORDER BY ordinal_position;
-- Constraints (contype: p=PK, f=FK, u=UNIQUE, c=CHECK, x=EXCLUDE)
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'public.tb_cd'::regclass;
-- Indexes
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'tb_cd';
DBeaver의 각 폴더를 클릭한 결과와 위 쿼리 결과가 일치하는지 대조해보세요. 일치한다는 걸 눈으로 확인하는 순간, GUI 없이도 DB를 파악할 수 있게 됩니다.
'public.tb_cd'::regclass는 테이블 이름을 OID로 변환하는 캐스팅입니다. pg_constraint.conrelid가 OID 타입이라 문자열을 그대로 비교할 수 없기 때문에 필요합니다.
4. information_schema vs pg_catalog
메타데이터를 조회하는 경로가 두 개입니다. 언제 뭘 쓰는지 정리합니다.
| 항목 | information_schema | pg_catalog |
| 표준 여부 | SQL 표준 | PostgreSQL 고유 |
| 이식성 | 다른 DBMS에서도 유사하게 동작 | PostgreSQL 전용 |
| 정보량 | 표준 범위로 제한됨 | PostgreSQL의 모든 정보 |
| 속도 | 뷰 계층이 있어 상대적으로 느림 | 빠름 |
| 추천 상황 | 컬럼/테이블 목록 등 기본 조회 | 인덱스 정의, 제약조건 원문 등 상세 조회 |
실무 감각: 기본 조회는 information_schema, PostgreSQL 특유의 상세 정보는 pg_catalog.
5. Foreign Keys와 References는 왜 따로 있나
DBeaver 트리에서 가장 헷갈리는 부분입니다. 답은 간단합니다. 방향이 반대입니다.
| 폴더 | 의미 | 화살표 |
| Foreign Keys | 이 테이블이 다른 테이블을 가리키는 관계 | 나가는 화살표 → |
| References | 다른 테이블이 이 테이블을 가리키는 관계 | 들어오는 화살표 ← |
같은 FK 하나가 양쪽 테이블에서 다른 이름의 폴더에 나타납니다.
🔬 실험 2 : 하나의 제약조건을 양방향에서 조회하기
BEGIN;
CREATE TABLE parent_t (id int PRIMARY KEY);
CREATE TABLE child_t (id int PRIMARY KEY,
parent_id int REFERENCES parent_t(id));
-- ① child_t 입장 → DBeaver의 child_t > Foreign Keys
SELECT conname,
conrelid::regclass AS "FK를 가진 쪽",
confrelid::regclass AS "가리키는 대상"
FROM pg_constraint
WHERE contype = 'f' AND conrelid = 'child_t'::regclass;
-- ② parent_t 입장 → DBeaver의 parent_t > References
SELECT conname,
conrelid::regclass AS "FK를 가진 쪽",
confrelid::regclass AS "가리키는 대상"
FROM pg_constraint
WHERE contype = 'f' AND confrelid = 'parent_t'::regclass;
ROLLBACK;
실행 결과 — ①과 ②가 완전히 똑같은 1행을 반환합니다.
conname | FK를 가진 쪽 | 가리키는 대상
------------------------+-------------+---------------
child_t_parent_id_fkey | child_t | parent_t
차이는 WHERE 절에서 conrelid를 봤느냐 confrelid를 봤느냐뿐입니다. 제약조건은 하나인데, 어느 쪽에서 바라보느냐에 따라 Foreign Keys가 되기도 하고 References가 되기도 합니다.
6. 제약조건과 인덱스가 겹쳐 보이는 이유
DBeaver에서 Constraints와 Indexes 폴더를 둘 다 열면, 이름이 비슷한 항목이 양쪽에 나타납니다. 버그가 아니라 PostgreSQL의 정상 동작입니다.
🔬 실험 3 : CREATE INDEX 없이 인덱스가 생기는 것 확인하기
BEGIN;
CREATE TABLE test_idx (
id int PRIMARY KEY,
code text UNIQUE,
age int CHECK (age >= 0),
name text
);
SELECT conname, contype FROM pg_constraint WHERE conrelid = 'test_idx'::regclass;
SELECT indexname FROM pg_indexes WHERE tablename = 'test_idx';
ROLLBACK;
제약조건 조회 결과 — 3개
conname | contype
------------------+---------
test_idx_pkey | p ← PRIMARY KEY
test_idx_code_key| u ← UNIQUE
test_idx_age_check| c ← CHECK
인덱스 조회 결과 — 2개
indexname
-------------------
test_idx_pkey
test_idx_code_key
CREATE INDEX를 단 한 번도 쓰지 않았는데 인덱스 2개가 생겼습니다.
왜 이런 차이가 나는가
| 제약조건 | 인덱스 | 자동생성 이유 |
| PRIMARY KEY | ✅ 생성 | 중복 검사를 매번 전체 스캔하면 감당 불가 |
| UNIQUE | ✅ 생성 | 위와 동일 |
| CHECK | ❌ 안 함 | 값 하나만 보면 판단 가능 → 다른 행을 뒤질 필요 없음 |
| NOT NULL | ❌ 안 함 | 위와 동일 |
| FOREIGN KEY | ❌ 안 함 | 참조 대상 쪽의 PK 인덱스를 사용 |
CHECK (age >= 0)은 제약조건 목록에는 c 타입으로 있지만 인덱스는 없습니다.
제약조건과 인덱스는 별개 개념인데 일부만 겹친다는 것이 이 결과로 증명됩니다.
🔬 실험 4 : 반례로 확인
위 코드에서 age int CHECK (age >= 0)을 age int UNIQUE로 바꿔 다시 실행해보세요.
인덱스가 2개에서 3개로 늘어납니다. UNIQUE는 인덱스를 만들고 CHECK는 안 만든다는 규칙이 이 차이로 확인됩니다.
📖 근거: PostgreSQL 문서 → SQL Commands → CREATE TABLE → UNIQUE / PRIMARY KEY 항목
7. 정리
| 개념 | 요점 |
| 계층 구조 | 서버 > 데이터베이스 > 스키마 > 테이블 > 컬럼 |
| 속성 vs 값 | 헤더 = 속성(구조), 칸 = 값(데이터). 바꾸는 방법이 다르다 (DDL vs DML) |
| DBeaver 트리 | GUI가 모아 보여주는 것. 실제로는 각각 별도 시스템 카탈로그에 저장 |
| information_schema | SQL 표준, 이식성 좋음, 기본 조회용 |
| pg_catalog | PostgreSQL 전용, 정보량 많음, 상세 조회용 |
| ::regclass | 테이블 이름 ↔ OID 변환 캐스팅 |
| FK vs References | 같은 제약조건의 양방향 뷰. conrelid vs confrelid |
| 제약조건 vs 인덱스 | 별개 개념. PK/UNIQUE만 인덱스를 자동 생성 |
