PostgreSQL 구조 이해하기

pgAdmin의 좌측 트리구조

기존에 사용하던 MariaDB와는 다르게 PostgreSQL은 데이터베이스 - Schemas - public - 테이블 이런식으로 배치가 되어있다. 

Servers
 └ test-server
    └ Databases
       └ app
          ├ Schemas
          │  └ public
          │     ├ Tables
          │     ├ Views
          │     ├ Indexes
          │     └ Sequences

기존에 사용하던 MariaDB와는 다르게 PostgreSQL은 데이터베이스 - Schemas - public - 테이블 이런식으로 배치가 되어있다. 

---

간단한 SQL 날려서 확인 해보기

Query Tool을 눌러서 직접 SQL을 쳐보자. 처음으로 간단하게 버전확인을 해본다.

select version();

지금 연결된 데이터 베이스를 조회해보자. 이것은 내가 지금 현재 어떤 디비에 명령어를 날리고 있는지가 중요하기 때문에 잘 보고 날려야한다.

select current_Database();

select current_schema();

현재 기본으로 사용하고 있는 스키마 이름을 한번 보자.

---

테이블 생성하기

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    age INTEGER CHECK (age > 0),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

한줄 한줄 뜯어서 생각해보자. 어렵지 않다.

 

CREATE TABLE users

• users 라는 테이블을 만든다.
• 유저 정보를 저장하는 공간이다.

BIGSERIAL

• 자동으로 숫자 증가
• MariaDB의 AUTO_INCREMENT랑 비슷하지만 구현방식이 다름

PRIMARY KEY

• 유일해야함
• 자동으로 인덱스 생성됨

TEXT

• 길이 제한 없는 문자열

NOT NULL

• 무조건 값이 들어가야 함

UNIQUE

• 중복 금지
• 자동으로 UNIQUE INDEX 생성

age INTEGER CHECK (age>0)

• 컬럼 이름은 age로 만들고
• age는 0보다 커야함
• 0이하로 들어오면 에러

TIMESTAMP WITH TIME ZONE

• 내부 저장: 항상 UTC
• 출력 시점: 세션 타임존 기준으로 변환

DEFAULT now()

• 값을 안넣으면 자동으로 현재시간 넣어줌

---

 

오류 발생 시켜보기

INSERT INTO users (email, age)
VALUES ('test@test.com', -10);

 

• age > 0 CHECK 조건 위반
• PostgreSQL이 저장 전에 차단

---

RETURNING

INSERT INTO users (email, age)
VALUES ('hello@test.com', 25)
RETURNING id, created_at;

 

• INSERT 결과를 바로 반환
• 다시 SELECT 안 해도 됨
• JPA 없이도 PK 획득 가능

---

EXPLAIN ANALYZE

EXPLAIN ANALYZE
select * from users where email = 'hello@test.com'

 

• DB가 어떻게 쿼리 실행했는지
• 실제 실행 시간 포함