1.1Intro

SQL은 Database들과 상호작용하기 위한 언어이다. Postgres는 데이터베이스의 일종으로, PostgreSQL은 Postgres와 상호작용하기 위한 언어이다.

Postgres 학습의 목적은 다음과 같다:

• 정보를 조회(retrieve)하기 위해 효율적인 쿼리를 작성하는 방법 배우기
• 데이터베이스의 구조, 즉 스키마(schema) 설계하는 법 배우기
• 고급 기능(인덱스, 파티셔닝 등)을 어떤 상황에서 써야 하는지 이해하기
• 실제 운영 환경에서 데이터베이스 관리하기(백업, 스케일링 등)

Database Design

데이터베이스 디자인은 아래와 같은 순서로 이루어진다:

1. 무슨 정보를 저장하는지? (다루려는 대상)
2. 이 정보는 어떤 속성들을 가져야하는지? (대상의 속성들)
3. 이 속성들은 각각 어떤 데이터 종류인지? (속성들의 데이터 타입)

예를 들어, '세계에서 가장 큰 도시들(largest cities in the world)'이라는 데이터베이스를 만든다고 하자.

1. 다루려는 대상: 도시들(cities) ⇒ 테이블 이름을 ‘cities’로 지어야지
2. 어떤 속성들을 가질까?: 도시명, 국가명, 인구수, 면적 ⇒ name, country, population, area라는 컬럼(column)을 만들자
3. 데이터의 종류는?: name은 string, country도 stirng, population은 number, area도 number, … ⇒ 각 column들에 알맞은 타입(type)들을 지정하자

sql
CREATE TABLE cities (
  name VARCHAR(50),
  country VARCHAR(50),
  population INTEGER,
  area INTEGER
 );

위 쿼리에 등장하는 용어들

• Keyword: 데이터베이스에 뭔가를 하고 싶다고 말하는 구문들로, 항상 대문자로 작성한다.
  • ex: CREATE, SELECT
• Identifiers: 데이터베이스에 어떤걸 다루고 있는지 말하는 구문들로, 언제나 소문자로 쓴다.
  • ex: cities, name
• VARCHAR: Variable Character의 줄임말. 길이가 가변적인(variable) 문자열을 저장하는 타입이다.
• INTEGER : 정수 타입으로, 약 -20억 ~ 20억까지의 값을 저장할 수 있다.

Inserting Data

테이블에 데이터를 삽입할 때는 다음과 같은 방식으로 쿼리를 작성한다.

sql
INSERT INTO cities (name, country, population, area)
VALUES ('Tokyo', 'Japan', 38505000, 8223);

여러 개의 데이터를 한번에 삽입할 수도 있다.

sql
INSERT INTO cities (name, country, population, area)
VALUES 
  ('Delhi', 'India', 28125000, 2240),
  ('Shanghai','China', 22125000, 4015),
  ('Sao Paulo', 'Brazil', 20935000, 3043);

Retrieving Data

테이블 안의 모든 데이터를 조회할 때는 이렇게 쓴다.

sql
SELECT * FROM cities

Calculated Columns

조회할 때 데이터를 바로 계산해서 볼 수도 있다.

sql
SELECT name, population / area FROM cities;

• 기본 연산자: +(덧셈), -(뺄셈), *(곱셈), /(나눗셈)
• 추가 연산자:
  • ^: 거듭제곱
  • |/: 제곱근(Square root)
  • @: 절대값
  • %: 나머지(Modulo)

또한, AS를 통해 계산된 컬럼에 이름(alias)을 붙일 수도 있다.

sql
SELECT name, population/area AS population_density 
FROM cities;

String Operators and Functions

문자열을 다루는 연산자와 함수도 사용할 수 있다.

• ||: 두 개 이상의 문자열을 이어 붙인다.
• CONCAT: 문자열을 이어 붙인다.

sql
SELECT name || ', ' || country AS location FROM cities;
-- 또는,
SELECT CONCAT(name, ', ', country) AS location FROM cities;

• 위의 쿼리는 도시 이름(name)과 국가 이름(country)을 쉼표와 공백으로 구분해 합쳐서 location이라는 새 컬럼으로 보여준다.

문자열을 다루는 다른 함수들도 있다:

• LENGTH(): 문자열의 문자 수 반환한다.
• LOWER(): 문자열을 모두 소문자로 변환한다.
• UPPER(): 문자열을 모두 대문자로 변환한다.

---

🥸 개인 탐구

PostgreSQL의 이름 유래

포스트그레스큐엘, 이름이 상당히 특이하고 어렵다. 왜 이런 이름이 붙은걸까?

Postgres는 Post InGRES의 줄임말이다. InGRES는 1970년대 캘리포니아 버클리 대학(UC Berkeley)에서 개발한 INteractive Graphics and REtrieval System 데이터베이스 시스템이다.

Postgres는 이런 InGRES의 후계자(post-)를 목표로 만들어진 시스템이었기에 이런 이름이 붙었다. 나중에 Postgres는 SQL(Structured Query Language)를 공식 지원하게 되었고, 이를 반영해 이름을 PostgreSQL로 바꾼다. 하지만, 개발자 커뮤니티에선 여전히 Postgres라고 줄여 부르는 경우가 많다.

SQL의 이름 유래

그러면 한 번 더, SQL은 왜 이런 이름이 붙었을까?

SQL은 Structured Query Language의 약자다. 1970년대, IBM의 연구팀은 SEQUEL(Structured English Query Language)라는, 영어로 되어 있어 사람 친화적으로 데이터베이스에 질의할 수 있는 언어를 만들었다. 하지만 이 SEQUEL이라는 이름은 다른 회사의 상표였고, 이에 이름을 줄여 SQL이라고 쓰고 '시퀄(Sequel)'이라고 읽게 되었다.

PostgresQL의 개발 역사

Postgres는 InGRES의 후계자라고 했는데, InGRES가 어떤 문제가 있었고 어떤 부분을 개선한 것일까?

UC 버클리의 Michael Stonebraker 교수와 연구팀은 InGRES라는 관계형 데이터베이스 시스템을 개발했다. InGRES는 상용화까지 성공했지만, Stonebraker 교수는 이에 만족하지 않고 더 나은 DBMS를 만들자는 목표를 세우고 Postgres 프로젝트를 시작한다.

Postgres는 당시로썬 여러 혁신적인 개념들을 도입한다:

• 단순한 관계(Relational) 모델을 넘어 객체-관계(Object-Relational) 모델을 지원
  • 전통적인 관계형 데이터베이스(Relational Database Management System, RDBMS)는 테이블에 간단한 데이터만 들어있었다.
  • 하지만 현실 세계의 데이터는 훨씬 복잡해서, 테이블 안의 각 열(column)에 객체처럼 복합적인 데이터가 들어가야할 때가 있다.
    • 예를 들어 '주소'라는 열을 사용자 정보에 넣는다고 할 때 '도로명, 건물 번호, 우편번호' 등 여러 속성이 하나의 열에 들어가야 한다.
  • 이렇게 복합적인 데이터인 객체를 지원하는 데이터베이스기 때문에 Object-Relational Database Management System(ORDBMS)라고 부른다.
• Transaction 무결성(ACID)을 강하게 지킨다
  • ACID란, Atomicity, Consistency, Isolation, Durability의 약자다.(아래에서 보다 자세히 설명)
  • 여기서는 간단하게 정리하자면, 데이터베이스가 무슨 일이 있어도 데이터의 일관성과 신뢰성을 깨뜨리지 않도록 지켜주는 것을 의미한다.
  • 이를 위해 MVCC(Multi-Version Concurrency Control) 같은 기술을 적용하는데, 이로 인해 동시 처리 성능이 높아지면서도 데이터 무결성을 깰 위험이 없어졌다.
• 버전 관리(Versioning)를 통해 데이터 변경 기록을 관리한다.

1990년대 초반, SQL이 데이터베이스 업계의 사실상 표준이 되면서 Postgres도 SQL을 지원하게 되었고, 이때 이름이 PostgreSQL로 바뀐다. 오늘날의 PostgreSQL은 오픈 소스로 누구나 자유롭게 사용할 수 있고, 상업적 제품 못지않은 강력한 기능을 제공해 인기가 많은 DBMS로 자리잡았다.

특히, 최근에는 Supabase라는, PostgreSQL 기반으로 데이터베이스와 관련 기능들을 클라우드에서 쉽게 쓸 수 있는 강력한 플랫폼형 서비스가 나오면서 따로 PostgreSQL 서버를 설치/운영할 필요가 없이 보다 쉽게 사용이 가능해졌다.

PostgreSQL이 다른 DBMS와 다른 점

PostgreSQL을 다른 데이터베이스 시스템과 비교하면, 몇 가지 뚜렷한 차별점이 있다.

1. 표준 준수와 확장성

• SQL 표준을 충실히 따르면서도, 필요한 경우 자유롭게 확장할 수 있다.
  • 예를 들면, 사용자 정의 함수(User-defined functions), 새로운 데이터 타입(Custom types), 운영자(Operators) 등을 직접 만들어 추가해 쓸 수 있다.
• 이러한 확장성 덕에 특수 목적 프로젝트들(예: 지리정보 시스템, 과학 데이터베이스 등)에 쓰이기도 한다.

2. 오픈 소스이면서도 상용 제품 못지 않은 강력함

• 오픈 소스임에도 불구하고 상용 제품 수준의 트랜잭션, 동시성 처리, 장애 복구, 복제 등의 기능을 가지고 있다.
• 다른 클라우드 서비스인 AWS RDS, Google Cloud SQL 같은 주요 클라우드 서비스에서도 PostgreSQL을 공식 지원한다.

3. 고급 기능 지원

• JSON/JSONB 타입을 지원해서, 구조화된 문서 데이터를 다룰 수 있다.
• CTE(Common Table Expressions), Window Functions, Materialized Views 같은 복잡한 쿼리 기능도 기본 제공한다.
• Full-text search(전문 검색)기능이 내장되어 있어서, 별도 검색엔진 없이도 텍스트 기반 검색이 가능하다.

4. MVCC 기반 동시성 제어

• 여러 사용자가 동시에 같은 데이터를 쓰고 읽어도 충돌 없이 부드럽게 처리가 가능하다.
  • MySQL 같은 다른 DBMS 중엔 다른 사용자가 쓰고 있으면 Lock으로 제어하는 경우가 있다.

이 MVCC에 대해 조금 더 자세히 알아보자.

MVCC (Multi-Version Concurrency Control)

다중 버전 동시성 제어라는 뜻으로, 쉽게 말해

데이터를 수정할 때, 기존 데이터를 즉시 덮어 쓰지 않고, 새로운 버전을 만들어서 처리하는 방식이다.

덕분에,

• 다른 사람이 데이터를 읽고 있는 동안 다른 누군가가 그 데이터를 수정하거나 삭제해도 서로 방해하지 않는다.
• 읽는 쪽은 자신이 처음 조회했을 때 기준의 데이터를 계속 읽을 수 있다. 즉, 일관성이 보장된다.

그런데 이렇게 버전 데이터를 계속 쌓아두면, 오래된 버전 데이터(사실상 필요 없는)까지 전부 남게 된다. 이것을 방지하기 위해 PostgreSQL은 Vacuum 기능을 제공한다.

Vacuum은 자동으로 PostgreSQL이 백그라운드에서 수행하는 것도 있고, 필요할 때 사람이 직접 VACUUM 명령어로 실행할 수도 있는데, 디스크 공간을 회수해서 낭비를 줄이고 쿼리 성능을 유지할 수 있게 해준다.

ACID 원칙

앞서, PostgreSQL은 Transaction 무결성을 지킨다는 개선점을 가지고 있다고 했는데 이것에 대해 알아보자. 우선, 트랜잭션(transaction)은 데이터베이스에서 하나의 작업 단위를 의미한다. 이 트랜잭션들은 모두 ACID 원칙을 따라야 하는데, ACID는 아래 항목들의 앞 글자를 딴 줄임말이다.