[PostgreSQL] Sequence: 정의, 활용 방법, 그리고 정렬 방식 사용 사례

이번 포스팅을 작성한 이유

일반적으로 드래그 앤드 드롭 정렬을 구현하는 데에는 Integer 방식, GreenHopper 방식, Linked List 방식 그리고 Atlassian Jira의 랭킹 알고리즘, LexoRank 정도가 있는 것으로 알고 있다(LexoRank 방식이 궁금하다면, 여기에서 자세히 알 수 있다). 나는 사이드 프로젝트에서 아이템 정렬 및 순서 변경 기능을 구현할 때, Integer와 순서가 변경되는 위치에 따른 분기 조건을 고려하는 방식의 쿼리를 사용하였다(링크).

 

하지만, 현재 회사에서는 PostgreSQL의 Sequence를 활용하여 아이템 정렬 및 순서 변경 기능을 구현하고 있다. 이에, 다소 생소했었던 PostgreSQL Sequence에 대해서 더 자세히 알아보고, 어떤 방식으로 아이템 정렬을 구현하였는지 소개하고자 글을 작성한다.

Sequence란?

Sequence는 PostgreSQL에서 자동 증가 숫자를 생성하는 독립적인 객체입니다. 테이블과는 별개로 존재하며, 여러 테이블에서 공유하거나 특정 컬럼의 값을 자동 증가시키는 데 사용할 수 있습니다. nextval, currval, setval 함수로 Sequence 값을 관리할 수 있습니다.

CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name
    [ AS data_type ]
    [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table_name.column_name | NONE } ]

• INCREMENT BY: 증가값 지정 (기본값: 1)
• MINVALUE / MAXVALUE: 최소 및 최댓값 설정 가능
• START WITH: 시작 값 설정 가능 (기본값: 1 또는 데이터 타입의 최소/최댓값)
• CACHE: 미리 생성하여 메모리에 저장할 개수 지정 (기본값: 1)
• CYCLE / NO CYCLE: 최댓값 도달 시 다시 처음부터 시작 여부 (기본값: NO CYCLE)
• OWNED BY: 특정 테이블 컬럼과 연결 (컬럼 삭제 시 Sequence도 삭제됨)
• nextval(): Sequence에서 다음 증가값을 가져옴
• setval(): 특정 값으로 수동 조정 가능
• currval(): 현재 시퀀스 값을 확인 가능

언제 사용해야 할까?

Sequence는 다음과 같은 상황에서 사용됩니다:

 

1. Primary Key 자동 증가 값:

PostgreSQL에서 SERIAL 또는 BIGSERIAL을 사용하면 내부적으로 Sequence가 자동 생성됩니다. 예를 들어:

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

위 테이블은 내부적으로 다음과 같은 시퀀스를 생성합니다:

CREATE SEQUENCE items_id_seq;
ALTER TABLE items ALTER COLUMN id SET DEFAULT nextval('items_id_seq');

 

Sequence 사용법을 보면 마치 MySQL의 AUTO_INCREMENT와 매우 유사해 보이는데, 어떤 점이 다를까요?

항목	PostgreSQL-Sequence	MySQL-AUTO_INCREMENT
기본 개념	독립적인 객체	컬럼 속성
값 조작	setval() 가능	직접 변경 불가
테이블 간 공유	여러 테이블에서 사용 가능	테이블 전용
트랜잭션 롤백	증가된 값 유지	롤백 시 증가 취소

MySQL의 AUTO_INCREMENT는 특정 테이블의 컬럼 속성으로 동작하는 반면, PostgreSQL의 Sequence는 독립적으로 존재하며 여러 테이블에서 공유 가능합니다. 또한, setval() 함수를 사용하여 값을 원하는 대로 조정할 수도 있습니다.

 

2. 아이템 정렬을 위한 순서 값 관리:

Sequence는 아이템의 정렬 순서를 지정하는 값으로도 사용할 수 있습니다. 예를 들어, 사용자가 특정 리스트에서 페이지 순서를 변경할 때 활용할 수 있습니다.

예제 코드

테이블 구조 

# Sequence 생성
CREATE SEQUENCE order_no;

# 테이블 생성
CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    order_no INTEGER
);

# Sequence 적용
ALTER TABLE 
  items ALTER COLUMN order_no 
SET 
  DEFAULT nextval('order_no'::regclass);

데이터 조회 예제

INSERT INTO items (name) VALUES
('Item A'),
('Item B'),
('Item C'),
('Item D');

SELECT *
FROM items
ORDER BY order_no

 id |  name  | order_no
----+--------+----------
  4 | Item A |        2
  5 | Item B |        3
  6 | Item C |        4
  7 | Item D |        5

위 결과에서 Item A를 가장 마지막으로 옮기기 위해서는?

WITH order_cte AS (
  SELECT 
    id, 
    nextval(‘order_no’) AS new_order_no 
  FROM 
    (
      SELECT 
        unnest(ARRAY[5, 6, 7, 4]) AS id
    ) AS subquery
) 
UPDATE 
  items 
SET 
  order_no = order_cte.new_order_no 
FROM 
  order_cte 
WHERE 
  items.id = order_cte.id;

SELECT *
FROM items
ORDER BY order_no

 id |  name  | order_no
----+--------+----------
  5 | Item B |        6
  6 | Item C |        7
  7 | Item D |        8
  4 | Item A |        9

설명

• unnest(ARRAY[5,6,7,4])를 사용하여 특정 순서대로 id 값을 생성
• nextval('order_no')를 호출하여 new_order_no를 부여
• items 테이블을 order_cte와 조인하여 id가 일치하는 행을 찾음
• order_no 값을 new_order_no로 업데이트

마무리

PostgreSQL의 Sequence는 Primary Key 자동 증가뿐만 아니라 아이템 정렬 순서를 지정하는 데 활용할 수 있습니다. 특히, PostgreSQL의 Sequence는 MySQL AUTO_INCREMENT 보다 유연한 자동 증가 값을 제공하기 때문에, 테이블 간 공유하거나 정렬 순서를 동적으로 변경하는 경우 매우 유용합니다.