Postgresql

Postgresql의 특징

1. 객체 관계형 데이터베이스 관리 시스템(ORDBMS)
2. 복잡한 쿼리
3. 트랜잭션 무결성
4. 다중 버전 동시성 제어

데이터 유형, 함수, 연산자, 인덱스 메서드, 절차적 언어등을 통해서 사용자가 여러가지 방법으로 확장 시킬 수 있다는 특징을 지닌다.

규칙

[]는 선택 사항을 나타냅니다. {}, | 는 하나의 대안을 선택해야 함을 나타난다. ... 은 앞의 요소를 반복할 수 있음을 의미합니다. () 는 다른 모든 기호 문자를 그래도 사용해야 함을 의미합니다.

명확성을 높이기 위해 SQL 명령어 (SELECT, CREATE, INSERT 등등) 앞에는 => 라는 프롬프트가, 셀 명령 앞에넌 $라는 프롬프트가 표시됩니다.

Connection Pool

데이터베이스와 클라이언트가 연결되면, 데이터베이스 서버는 클라이언트와의 연결을 위해 많은 자원을 사용합니다. 이에 대해서 자세하게 이야기 해봅시다!

우선 메모리에 대한 이야기를 해보겠습니다. 데이터베이스 서버가 클라이언트와 연결을 유지 하기 위해서는 메모리가 소비됩니다.

메모리 중에서도 CPU에서 직접 데이터를 불러 쓰고 속도가 빠른 주메모리, DRAM을 사용합니다. DRAM의 특징은 휘발성 메모리라는 것입니다. 휘발성 메모리의 특징은 무엇일까요? 정보가 영구히 저장되지 않고 전원이 끊어질 경우 데이터가 사라진다는 특징을 지니고 있습니다. DRAM은 정보를 데이터를 계속해서 유지하기 위해서는 Reflash가 필요합니다.

데이터베이스와의 연결 또한 마찬가지입니다. 클라이언트와 데이터베이스 서버가 연결되기 위해서는 클라이언트의 IP 주소, 클라이언트의 포트 번호, 세션의 ID, 연결 상태와 같은 데이터들이 DRAM에 저장되어야 합니다. 이는 초기의 연결 당시에는 많은 메모리를 소비하지 않을 수 있습니다. 하지만 이 연결이 해제 되지 않고 계속해서 연결되어 있다면 어떤 일이 일어날까요?

메모리는 현재 갖고 있는 메모리의 데이터를 유지하기 위해서 계속해서 Refresh를 시도할 것이고 또한 이미 데이터를 저장하고 있는 메모리는 재사용할 수 없기 때문에 이는 성능 저하로 연결될 수 밖에 없습니다.

성능저하가 이어지게 되면 데이터베이스는 메모리에서 DB 버퍼 캐쉬에 데이터를 저장하고 사용할 수 없기 때문에 메모리보다 성능이 느린 디스크를 통하여 데이터를 저장하고 사용하기 때문에 원하는 데이터를 읽고 쓰는데 걸리는데 경합이 발생하여 대기 시간이 길어질 수 밖에 없습니다.

그렇기 때문에 데이터베이스 서버와 클라이언트의 연결이 혹시나 계속해서 사용되지 않음에도 계속해서 사용되는 문제를 해결하기 위하여 등장한 것이 Connection Pool 입니다.

1. 데이터베이스 서버가 시작되면 일정 수의 연결을 미리 만들어 둡니다.
2. 클라이언트가 데이터베이스와 연결을 요청하면, 데이터베이스 서버는 연결 풀에서 연결을 하나 빌려줍니다.
3. 클라이언트가 연결을 사용하지 않으면 다시 데이터베이스 서버에 반납합니다.
4. 데이터베이스 서버는 연결 풀에 반납된 연결을 다시 사용 가능한 연결로 만듭니다.

Connection Pool 은 위와 같은 방식으로 서버와 클라이언트간의 연결을 다루며 서버의 자원을 효율적으로 관리하고 성능을 향상 시키기 위한 방법입니다.

메모리에 관하여

요즘 SSD와 SD카드 등에서 사용되는 메모리는 플래시 메모리로 읽고 쓰는 데 시간이 오래 걸리는 느린 메모리이지만 비휘발성이며 소자 미세화에 유리하며 매우 싸게 큰 용량을 만들 수 있다는 장점이 있다.

컴퓨터는 컴퓨터내의 연산 작업을 담당하고 있는 CPU와 CPU에서 직접 데이터를 불러 쓰고 속도가 빠른 주메모리와 장기 데이터 저장을 위해 속도는 느리지만 저렴하고 저장 용량이 큰 보조저장장치로 구분이 된다.

SRAM, DRAM Static Random Access Memory, Dynamic Random Access Memory의 약자이며 SRAM은 전원이 켜져 있는 동안 데이터를 유지할 수 있고 DRAM에 비해서 속도가 빠르지만 용량이 적고 비용이 비싸다는 특징이 있습니다. DRAM은 전원이 켜져 있는 동안에도 정기적으로 데이터를 새로고치는 작업이 필요하고 SRAM에 비해 속도는 느리지만 용량이크고 비용이 저렴하다는 특징이 있습니다.

SQL 문법

SQL의 데이터를 작성하기 위해서는 테이블을 작성하여야 합니다. 이유는 sql이 테이블을 이용하여 각 데이터 간의 관계를 표현하여 데이터간의 관계를 효율적으로 관리하고 무결성을 높이며 이해도를 높이는데 매우 효과적이기 때문입니다.

여기서 이해도를 테이블을 통하여 높인다는 말은 이해가가지만 다른 말들은 잘 이해가 가지 않습니다. 이 다른 경우들이 어째서 테이블의 형태로 구성하였을 경우 효과를 높일 수 있는지 알아보겠습니다.

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int,           -- high temperature
);

// 위의 경우 에러가 발생한다.  SQL 문장의 마지막에는 ","로 구분되는 문장으로 끝나서는 안된다.

CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- low temperature
    temp_hi         int           -- high temperature
);

CREATE TABLE IF NOT EXISTS weather (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    pin VARCHAR(4) NOT NULL CHECK (LENGTH(pin) = 4 AND pin ~ '[0-9]+$') -- 은행에 로그인시 필요한 PIN 번호
);

위의 VARCHAR과 CHAR의 차이점은 VARCHAR은 동적으로 길이가 변경될 수 있는 문자열을 테이블에 입력받을 경우 사용되고 CHAR의 경우는 길이가 정해져 있는 문자열을 테이블 내의 데이터로 입력 받을 경우 사용한다.

또한 postgresql에는 REGEXP가 존재하지 않기 때문에 대신 ~ 를 사용하여 문장이 정규표현식에 적합한지 테스트 할 수 있다.

INSERT

INSERT INTO weather (city, temp_lo, temp_hi, date)
	VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

SELECT * FROM weather;

SELECT * FROM weather
    ORDER BY city, temp_lo;

SQL WHERE 절은 SELECT, UPDATE, DELETE 문에서 사용되는 조건 절입니다. WHERE 절을 사용하여 조회할 행이나 갱신할 행을 지정할 수 있습니다.

WHERE 절의 특징은 다음과 같습니다.

• SELECT, UPDATE, DELETE 문에서 사용됩니다.
• 조회할 행이나 갱신할 행을 지정하는 데 사용됩니다.
• 비교 연산자, 논리 연산자, 집합 연산자 등을 사용하여 조건을 지정할 수 있습니다.

	client.query(`
		INSERT INTO users(username, password, date)
		VALUES ($1, $2, $3)
		ON CONFLICT (username) DO NOTHING;
	`, [username, password, date])

위의 SQL절을 살펴보자 users라는 테이블에 username, password, date 키 값에 달러($) 사인을 이용하여 데이터 값을 넣고 있습니다. 하지만 여기서 다른것보다 눈에 띄게 확인해봐야 할 것은 ON CONFLICT () DO NOTHING 입니다!

ON CONFLICT () DO NOTHING 의 역할은 무엇일까요? 이를 해석하면 ()의 값이 이미 있을 경우 아무것도 하지 않는다 라고 이해할 수 있습니다. 위의 예재를 기반으로 생각할 경우 username 값에 추가하는 값이 이미 있을 경우 아무것도 하지 않는다는 것입니다.

예를 들어 이전에 example@gmail.com 이라는 값을 id에 이미 넣었습니다. 하지만 이 후에 또 example@gmail.com 값을 ID에 입력하려고 할 경우 아무것도 하지 않습니다.

단! 여기서 중요한 조건이 있습니다.

TABLE의 데이터가 UNIQUE해야 한다는 것입니다! CREATE TABLE을 이용하여 각 키 값을 만들때 UNIQUE 를 이용하여 ROW 데이터를 작성해주어야 ON CONFLICT () DO NOTHING 을 사용할 수 있습니다.

WHERE

WHERE 절의 사용 방법은 다음과 같습니다.

SELECT *
FROM users
WHERE name = 'John Doe';

위의 예에서 WHERE 절은 name 열의 값이 'John Doe'인 행을 조회합니다. WHERE 절에서 사용할 수 있는 비교 연산자는 다음과 같습니다.

• =: 같음
• !=: 같지 않음
• >: 크다
• <: 작다
• >=: 크거나 같다
• <=: 작거나 같다

WHERE 절에서 사용할 수 있는 논리 연산자는 다음과 같습니다.

• AND: 논리곱
• OR: 논리합
• NOT: 논리부정

WHERE 절에서 사용할 수 있는 집합 연산자는 다음과 같습니다.

• IN: 포함
• NOT IN: 포함하지 않음
• BETWEEN: 사이
• NOT BETWEEN: 사이가 아님

WHERE 절을 사용하여 다양한 조건을 지정할 수 있습니다. 다음은 몇 가지 예입니다.