[StudyWithMe] PostgreSQL을 처음 접해보며

 

이번 `StudyWithMe` 프로젝트에서 `PostgreSQL`을 사용하기로 했다.

 

지금까지 `MySQL`만을 사용해왔었는데, 같이 프로젝트 하는 친구의 적극 유도(?)로 `PostgreSQL`을 사용하게 되었다.

 

기존에 `PostgreSQL`은 `MySQL`과는 달리 `SEQUENCE`라고 불리는 독립 오브젝트를 제공한다고만 알고 있었다.

즉, "`AUTO_INCREMENT`가 아닌 `SEQUENCE`를 이용해 미리 ID값을 불러와서 사용하는 것이 가능하다." 까지 알고 있었다.

 

그래서, 이번 포스팅에서는 위와 같이 알고만 있던 `PostgreSQL`에서

1. `PostgreSQL`과 `MySQL`의 차이.
2. `PostgreSQL`를 조금 사용해보며 알게 된 사실.
3. 기본 키 생성 전략을 `SEQUENCE`가 아닌 `IDENTITY`를 사용한 이유.

에 관해 포스팅 하려고 한다.

 

1. `PostgreSQL`과 `MySQL`의 차이

PostgreSQL vs MySQL

Aurora MySQL vs Aurora PostgreSQL | 우아한형제들 기술블로그

 

기본적인 내용은 위의 우아한 형제들 기술 블로그를 참고했다.

 

---

우선, 위에 적혀있는 내용으로는 `MySQL`에서는 멀티 쓰레드 환경 및 제한된 `JOIN` 방식 제공으로

복잡한 쿼리나 대량 데이터 처리에서 불리한 요소로 작용한다고 한다.

 

또한, 데이터 크기가 커질수록 테이블의 구조 변경이나 인덱스 생성 작업에도 상당한 시간이 소요된다.

그렇기에 이러한 점들을 개선하기 위해 대량 데이터 처리에 특화되어있는 `PostgreSQL`를 많이 사용한다고 한다.

---

무슨 말인지 의아할 수 있다.

(복잡한 쿼리나 대량 데이터 처리에서 불리한 요소???)

(데이터 크기가 커지면 테이블의 구조 변경이나 인덱스 생성 작업에서 상당한 시간 소요??)

그래서 이해를 돕기 위해 부가 설명을 하도록 한다.

 

 

1. InnoDB의 스레드 병목 문제

 

위의 내용에 관해 부가 설명하자면, `MySQL`은 기본적으로 `InnoDB` 스토리지 엔진을 사용하는 경우가 많다.

 

`InnoDB`는 멀티 쓰레드 환경에서 버퍼 풀 관리 등에서 내부적으로 `글로벌 락(Global Mutex)`를 사용한다.

즉, 대량 데이터를 동시에 처리하려고 하면 위의 `글로벌 락`이 성능 병목으로 작용할 수 있다고 한다.

 

특히, 고성능의 멀티코어 CPU를 사용하는 경우에도 `InnoDB`가 충분히 병렬 처리를 활용하지 못할 때가 있다고 한다.

 

 

2. 쿼리 실행의 병렬 처리 제한

 

`MySQL`은 특정 복잡한 쿼리(특히 JOIN, GROUP BY, ORDER BY, 집계 쿼리)에 대해 병렬 처리를 수행하는 능력이 제한적이다.

 

• `PostgreSQL`은 `Parallel Query` 기능을 제공하므로 대량 데이터 작업에서 병렬 처리를 통해
  성능을 개선할 수 있다.
  • MySQL에서는 해당 `Parallel Query` 기능이 부분적으로만 구현되어 있다.

 

즉, 단일 쿼리가 멀티코어 CPU를 효율적으로 활용하지 못해 대량 데이터 작업이 느려진다.

 

 

3. 테이블 락과 인덱스 관리

 

`MySQL은 대량 데이터 삽입이나 테이블 구조 변경(인덱스 추가 또는 테이블 변경) 작업에서 `Table Lock`이 발생할 수 있다.

 

`Table Lock`은 멀티쓰레드 환경에서 다른 작업의 병렬 수행을 제한하며,

대규모 테이블에서는 작업 시간이 길어질수록 병목이 심해질 수 있다.

 

즉, 간단 정리하자면 `글로벌 락`, `테이블 락`, `Parallel Query`, `멀티 쓰레드`로 인해
`MySQL`은 `PostgreSQL`보다 대량 데이터 처리 작업에서 성능적으로 떨어진다.

 

 

그럼 PostgreSQL은?

PostgreSQL과의 차이점

• PostgreSQL은 멀티코어를 적절히 활용해 병렬 쿼리 실행, 인덱스 생성, 그리고 대량 데이터 처리에서 더 나은 성능을 제공한다.
• PostgreSQL의 `MVCC(다중 버전 동시성 제어)` 모델은 데이터 수정 작업 중에도 읽기 작업이 락에 영향을 받지 않도록 설계되어, 대량 데이터 작업 시에도 동시성을 더 잘 처리할 수 있다.

 

 

오... 그럼 `MySQL`이 무조건 `PostgreSQL` 보다 성능이 안좋은 걸까?

 

그렇지 않다. 단순, CRUD에서는 `MySQL`의 성능이 더 좋다.

 

 

기능비교

	Aurora MySQL(5.7)	Aurora PostgreSQL(11)	comment
DB특성	RDBMS	ORDBMS	PostgreSQL은 객체관계형 DBMS로 개발자는 기존 데이터 type에서 확장된 type형태를 자유롭게 정의하여 사용할 수 있다. 또한 테이블 상속기능이 제공되어 자식 테이블은 부모 테이블로부터 열을 받아 사용할 수 있다.
방식	멀티쓰레드	멀티프로세스
사용환경	OLTP에 적절	OLTP, OLAP에 적절	단순 CRUD시에는 MySQL에 비해 PostgreSQL의 성능이 조금 떨어진다. PostgreSQL은 복잡한 쿼리를 요구하고 대규모 서비스인 경우에 특화되어 있다.
MVCC지원	Undo Segment 방식	MGA(Multi Generation Architecture) 방식	– Undo segment 방식: update 된 최신 데이터는 기존 데이터 블록의 레코드에 반영하고 변경 전 값을 undo 영역이라는 별도의 공간에 저장하여 갱신에 대한 버전관리를 하는 방식이다. – MGA 방식: 튜플을 update할 때 새로운 값으로 replace 처리하는 것이 아니라, 새로운 튜플을 추가하고 이전 튜플은 유효 범위를 마킹하여 처리하는 방식이다.
UPDATE 방식	UPDATE	INSERT & DELETE (check)	PostgreSQL UPDATE시 내부적으로는 새 행이 INSERT되고 이전 데이터는 삭제 표시가 된다. 모든 인덱스에는 행의 실제 위치값에 대한 링크가 표기되어 있는데, 행이 업데이트되면 변경된 위치값에 대한 인덱스 정보도 업데이트가 필요하다. 이런 과정 때문에 UPDATE시에는 MySQL보다 성능이 떨어진다.
지원되는 JOIN	NL JOIN HASH JOIN (5.7 2.06 ~)	NL JOIN HASH JOIN SORT JOIN
Parallel Query for SELECT	지원됨 (5.7 2.09.2~)	지원됨 (9.6 ~)
Default Transaction Isolation	REPEATABLE READ	READ COMMITTED
테이블 기본 구성 인덱스	CLUSTERD INDEX	NON-CLUSTERED INDEX

 

앞서, 언급한 `JOIN`, `Parallel Query` 기능이 `MySQL 8.0`에서 정식 지원이 되긴 하지만, 많은 부분이 제한되어있다고 한다.

 

성능 비교

1. 단순 CRUD 쿼리

 

아래 표를 보면 알 수 있듯이 `MySQL`이 성능결과가 더 좋다.

 

앞의 기능비교에 적혀있듯이 `PostgreSQL`은 Update 시 `MySQL`과는 조금 달리

변경 전 값을 삭제마크처리 후 변경 후 값을 새행으로 추가하는 방식으로 작업이 진행된다.

 

위 구조가 단순 `CRUD` 성능차이의 큰 요인으로 작용하는 것으로 볼 수 있다.

 

그래서, `PostgreSQL`은 보통 Insert, Select 위주의 서비스에 사용하는 것이 선호되고 있다고 한다.

 

 

`My` -> `MySQL`

`Pg` -> `PostgreSQL`

 

 

실제로 Update된 값이 `id = 22`번 보다 아래에 위치하는 걸 볼 수 있다.

 

 

2. 복잡한 쿼리

 

1000만 건 데이터의 조인쿼리를 `HASH JOIN`으로 비교 실행해보면 어떻게 될까?

결론부터 말하면 `Aurora MySQL`에서는 22초 정도 소요되고
`Aurora PostgreSQL`에서는 3초로 7배 이상 빠른 속도 차이가 있다고 한다.

 

HASH JOIN이 이 정도라면 `Aurora MySQL`의 일반적인 방식인 `Nested loop Join`에서는 더 극심해질 것이다.

 

https://techblog.woowahan.com/6550/

 

 

 

3. PostgreSQL의 Partial Index(참고)

 

`PostgreSQL`에는 전체 데이터의 부분집합에 대해서만 인덱스를 생성하는 `Partial Index`라는 기능이 있다고 한다.

 

따라서, 특정 범위에 대해서만 인덱싱이 가능해 대량 데이터의 일부 값에 대해 인덱스를 생성할 경우,

인덱스 크기도 작고 관리하는 리소스도 줄일 수 있다고 한다.

 

위의 우아한 기술 블로그에서 설명하길 `Aurora MySQL(755M) < Aurora PostgreSQL(57M)`로

10배 정도의 인덱스 크기 차이가 있다고 한다.

 

즉, 필요한 부분만 인덱스를 생성해 저장공간에 대한 이점이 아주 크고 나아가

데이터 삭제, 추가, 갱신에 따른 인덱스 유지관리 비용도 절약된다고 한다. 

 

 

4. Secondary Index 생성(참고)

 

`Aurora MySQL`에서는 대량 테이블에 새롭게 인덱스를 생성하거나 컬럼을 추가하는 경우

100G를 넘으면 인덱스 생성에 1시간이 넘어간다고 한다.

 

하지만, `Aurora PostgreSQL`에서는 40여 분 만에 인덱스가 생성이 된다고 한다.

(`PostgreSQL`의 online ddl 컬럼 추가는 카탈로그에 추가된 정보만을 반영하기 때문이라 한다.)

 

---

 

지금까지, `PostgreSQL`과 `MySQL`의 차이에 대해 알아보았다.

 

후후,,,

블로그 포스팅을 위해 많은 정보를 찾아보니까 내가 지금까지 써온 "`MySQL`에 대해서도 모르는게 많았구나" 라는 생각이 들고 두 DB를 사용해보며 알게 모르게 궁금했더 사실들에 대해 조금 알게 된 것 같다.

 

 

`PostgreSQL vs MySQL`에 대해 많은 것을 작성했지만, 사실 내가 `Spring Boot`를 활용해 개발함에 있어

`MySQL`에서는 되고 `PostgreSQL`에서는 안되는 차이점은 사실 크지는 않다.

 

 

2. PostgreSQL를  조금 사용해보며 알게 된 사실

 

@Param의 사용

 

`MySQL`이나 `H2`에서는 `@Param`를 사용하지 않아도 파라미터의 이름이 같으면 자동으로 바인딩 되었었다.

하지만, `PostgreSQL`에서는 파라미터의 이름으로 접근하려면 꼭 `@Param`을 사용해줘야 한다.

 

@Repository
public interface StudyRoomRepository extends JpaRepository<StudyRoom, Long>, CustomStudyRoomRepository {

    @Query("select s from StudyRoom s join fetch s.user " +
            "where s.id = :studyRoomId and s.user.id = :userId")
    Optional<StudyRoom> findByIdAndUserId(@Param("studyRoomId") Long studyRoomId, @Param("userId") UUID userId);

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("select s from StudyRoom s where s.id = :studyRoomId")
    Optional<StudyRoom> findByIdWithLock(@Param("studyRoomId") Long studyRoomId);

    @Query("select s from StudyRoom s join fetch s.user " +
            "where s.id = ?1 and s.deletedAt is null")
    Optional<StudyRoom> findByIdWithUser(Long studyRoomId);
}

 

`?1`과 같이 사용하면 꼭 `@Param`을 사용하지 않아도 된다.

 

왜 그런지 알아본 결과 이유는 아래와 같다.

 

• MySQL
  • JPA에서는 기본적으로 변수명과 쿼리에서 사용하는 파라미터 이름이 같다면 자동으로 바인딩한다.
  • 예를 들어, 메서드 파라미터 이름이 `username`이면, JPA가 자동으로 `username`을 SQL 쿼리의 파라미터로 바인딩한다.
• PostgreSQL
  • `PostgreSQL`에서는 SQL 쿼리에서 파라미터를 명확히 지정해줘야 한다.
    • `PostgreSQL`에서는 JPA가 메서드 파라미터와 SQL 쿼리 파라미터의 매칭을 명확히 하기 위해,
      명시적인 매핑을 요구하는 경우가 많다.
      • 즉, 메서드의 파라미터 이름이 SQL 쿼리 내에서 사용되는 파라미터 이름과 일치하더라도, PostgreSQL에서는 이를 자동으로 바인딩하지 않는 경우가 발생할 수 있다고 한다.

 

그래서 나는 현재 위의 사실을 알게 된 이후 `?1`과 같은 형식을 사용하고 있다.

 

(기존에 MySQL에서 `?1`이 아닌 파라미터 이름을 사용한건 가독성을 위해서였지만,)

(모든 파라미터에 `@Param`을 붙이면 오히려 가독성이 떨어질 거 같아서 이다.)

 

Enum의 생성

`PostgreSQL`과 `MySQL`에서 ENUM 타입 생성에 있어 차이가 있다.

 

MySQL의 경우

CREATE TABLE study_room (
    id INT AUTO_INCREMENT PRIMARY KEY,
    option ENUM('WIFI', 'ELECTRICAL', 'CHAIR_DESK', 'MART', 'PRINTING', 'FULL_MIRROR',
                'FOODS', 'INTERNAL_TOILET', 'NO_SMOKE', 'PARKING', 'PC_NOTEBOOK', 
                'TV_BEAM_PROJECT', 'WATER', 'WHITEBOARD', 'ALCOHOL', 'SCREEN', 'MIKE')
);

 

PostgreSQL의 경우

create TYPE study_room_option as enum (
    'WIFI', 'ELECTRICAL', 'CHAIR_DESK',
    'MART', 'PRINTING', 'FULL_MIRROR',
    'FOODS', 'INTERNAL_TOILET', 'NO_SMOKE',
    'PARKING', 'PC_NOTEBOOK', 'TV_BEAM_PROJECT',
    'WATER', 'WHITEBOARD', 'ALCOHOL',
    'SCREEN', 'MIKE'
);

create table if not exists study_room_option_info(
    id bigserial primary key,
    study_room_id bigint references study_room (id) not null,
    option study_room_option not null,
    deleted_at timestamp null,
    created_at timestamp not null,
    updated_at timestamp not null
)

 

`MySQL`에서 `ENUM`은 각 컬럼에 대해 정의되며, 값 목록을 컬럼 정의 안에서 직접 나열한다.

 

하지만, `PostgreSQL`은 별도의 `CREATE TYPE`으로 `ENUM`을 정의한 뒤

컬럼 정의 안에 선언해줘야 한다.(즉, 테이블에서 참조한다.)

 

그리고 실제 `Entity` 필드에 선언할 때도 `MySQL`처럼 `@Enumerated`가 아닌 아래와 같이 해줘야 한다.

@Entity
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@AllArgsConstructor(access = AccessLevel.PRIVATE)
@Builder
@Table(name = "study_room_option_info")
public class StudyRoomOptionInfo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Enumerated(EnumType.STRING) 
    @JdbcType(value = PostgreSQLEnumJdbcType.class) // 이 부분
    @Column(name = "option", nullable = false)
    private StudyRoomOption option;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "study_room_id", nullable = false)
    private StudyRoom studyRoom;

    public void modifyOption(StudyRoomOption studyRoomOption) {
        this.option = studyRoomOption;
    }
}

 

위와 같이 설정해야 정상 동작한다.

 

여기까지가 내가 피부로 느낀 `PostgreSQL`을 사용하며 느낀 `MySQL`과의 차이점이다.

 

추가된다면 또 작성할 예정이다.

 

---

3.  기본 키 생성 전략을 `SEQUENCE`가 아닌 `IDENTITY`를 사용한 이유

 

`StudyWithMe` 프로젝트를 진행하며 초기에는 기본 키 생성 전략을 `SEQUENCE`로 하려고 하였다.

(스터디 룸 생성, 스터디 생성 등 다양한 부분에서 `Bulk Insert` 사용이 필요했기 때문이다.)

 

그래서, 초기에는 아래와 같이 설정해주었다.

 

@Entity
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@AllArgsConstructor(access = AccessLevel.PRIVATE)
@Builder
@Table(name = "study_room_option_info")
public class StudyRoomOptionInfo {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "study_room_option_info_seq")
    @SequenceGenerator(name = "study_room_option_info_seq", sequenceName = "study_room_option_info_seq", allocationSize = 100)
    private Long id;

    @Enumerated(EnumType.STRING)
    @JdbcType(value = PostgreSQLEnumJdbcType.class)
    @Column(name = "option", nullable = false)
    private StudyRoomOption option;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "study_room_id", nullable = false)
    private StudyRoom studyRoom;

    public void modifyOption(StudyRoomOption studyRoomOption) {
        this.option = studyRoomOption;
    }
}

 

그런데, 띠용 실행하니까 오류가 발생했다.

 

왜일까? 하고 조사해 본 결과

 

문제는 우리가 코드로 작성한 `allocationSize`와 테이블 설정의 불일치로 인함이였다.

 

 

• Cache Size: 한번에 Cache 하는 양 즉, `allocationSize`
• Step: ID의 증가정도

우선, `allocationSize`를 100으로 하고 `Cache Size`를 1로 하면 `JPA`는 100개씩 요청을 하는데,

`Cache Size`는 1이므로, 내부적으로 `nextVal()`을 100번 호출한다고 한다.

 

이래서, 오류가 발생한 것으로 보인다.

 

근데, 이래저래 알아보면서 의문점이 생겼다.

 

무작정 예상으로 `SEQUENCE`의 `Cache Size`를 결정하면 경우에 따라 계속 내부적으로 `nextVal()`를 호출할텐데

지금같은 프로젝트 초기에 미리 100씩 잡아놓는게 맞는 판단인가? 오히려 이게 더 성능 저하를 유발하지 않을까? 라는 생각이 들었다.

또한, 엔티티마다 특성이 다 다른데 이걸 지금 미리 정할 수 있을까? 라는 생각도 들었다.

 

위의 의문점을 갖고 더 찾아보던 중 아래 블로그를 발견하였다.

 

 

https://medium.com/@taesulee93/postgresql-pk-%EC%9E%90%EB%A3%8C%ED%98%95%EC%97%90-%EB%94%B0%EB%A5%B8-spring-data-jpa-hibernate-entity-id-%EB%A7%A4%ED%95%91-%ED%8C%81-ca7b1f6da2ac

PostgreSQL PK 자료형에 따른 Spring Data JPA(Hibernate) Entity ID 매핑 팁

 

위의 글을 보면 아래와 같은 말이 있다.

 

---

---

 

"새로운 Application", "PostgreSQL 10 버전 이상", "새로운 서비스".. 우리잖아?

 

음.. 고민하던 중 `Postgresql wiki`에서 명쾌한 답을 주었다. 맞는 말이라고 생각한다.

새로운 서비스에서 실제 트래픽도 모르고 무작정 `SEQUENCE`값을 설정하는 것은 오히려 성능 저하를 유발할 수도 있다.

(물론, 많은 트래픽이 있진 않겠지만..)

 

그래서 우리는 그냥 기본 키 생성 전략을 `IDENTITY`으로 하는 것으로 최종 판단을 했고,

다수의 `INSERT`에 대해서는 `JdbcTemplate`를 활용한 `Batch`처리를 하는것으로 하였다.

 

 

JdbcTemplate 활용 예

@RequiredArgsConstructor
public class JdbcOptionInfoRepositoryImpl implements JdbcOptionInfoRepository {

    private final JdbcTemplate jdbcTemplate;

    @Override
    public void batchInsert(List<StudyRoomOption> options, StudyRoom studyRoom) {
        String sql = "insert into study_room_option_info (study_room_id, option) values (?, ?)";

        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setLong(1, studyRoom.getId());
                ps.setObject(2, options.get(i).name(), Types.OTHER);
            }

            @Override
            public int getBatchSize() {
                return options.size();
            }
        });
    }
}

 

 

---

 

정리

이번 포스팅에서는 `StudyWithMe` 프로젝트를 진행하며 처음 접해본 `PostgreSQL`에 관해 포스팅하였다.

 

`StudyWithMe` 프로젝트를 진행 간에는 항상 "왜?"를 생각하기로 다짐 또 다짐을 하였다.(정답은 아닐 수 있지만)

 

그래서, 기본 키 생성 전략을 함에 있어도 왜?를 고민했다.

이건 추후에도 중요한 부분으로 작용될 것으로 생각되었기 때문이다.