데이터를 관리하다보면 SELECT한 결과에 따라 데이터를 INSERT할지 UPDATE할지 나뉘는 코드를 작성해야 할 때가 있다.
이를 위해 TypeORM은 save method를 제공한다. save는 SELECT 문을 실행한 후 데이터가 없으면 INSERT, 데이터가 있으면 UPDATE 문을 실행한다. 하지만 save를 한 번 실행할 때 두 개의 쿼리가 실행되므로 비효율적이다. (SELECT + INSERT or SELECT + UPDATE)
PostgreSQL에서는 이를 해결하기 위해 ON CONFLICT DO UPDATE 절을 사용할 수 있다. 기본적인 INSERT 문은 데이터 충돌 시 오류가 발생하는데 ON CONFLICT 절과 함께 사용하면 충돌 시 UPDATE를 수행한다. TypeORM에서는 upsert method로 제공된다.
이제 대량의 데이터를 bulk upsert할 때 어떤 문제가 발생할 수 있는지와 이에 대한 해결 방안에 대해 알아보자.
먼저 테스트에 사용할 엔티티는 아래와 같다.
@Entity('user')
export class User {
@PrimaryColumn()
userNum: number;
@Column({ type: 'varchar' })
nickname: string;
@Column({ type: 'timestamptz', nullable: true })
updated: Date | null;
}
upsert를 사용하는 방법은 두 가지가 있다.
- repository에서 upsert를 호출하는 방법
첫번째 인자로 엔티티, 두번째 인자로는 ON CONFLICT에 해당되는 컬럼을 받는다.
async upsert(): Promise<void> {
const user = new User();
user.userNum = 1;
user.nickname = 'test1'
await this.userRepository.upsert(user, ['userNum'])
}
- QueryBuilder를 생성해 insert, orUpdate를 호출하는 방법
쿼리빌더를 사용하면 좀 더 세밀한 조작이 가능하다.
첫번째 인자로 충돌 시 업데이트할 컬럼, 두번째 인자로는 ON CONFLICT에 해당되는 컬럼을 받는다.
async upsert(): Promise<void> {
const user = new User();
user.userNum = 1;
user.nickname = 'test1';
await this.userRepository
.createQueryBuilder()
.insert()
.into(User)
.values(user)
.orUpdate(['nickname'], ['userNum'])
.execute();
}
이제 각각 생성된 쿼리를 살펴보자.
INSERT INTO "user"("userNum", "nickname", "updated")
VALUES ($1, $2, DEFAULT)
ON CONFLICT ( "userNum" ) DO UPDATE
SET "userNum" = EXCLUDED."userNum", "nickname" = EXCLUDED."nickname" -- PARAMETERS: [1,"test1"]
INSERT INTO "user"("userNum", "nickname", "updated")
VALUES ($1, $2, DEFAULT)
ON CONFLICT ( "userNum" ) DO UPDATE
SET "nickname" = EXCLUDED."nickname" -- PARAMETERS: [1,"test1"]두 쿼리 모두 updated 컬럼에 값을 할당하지 않아서 values에 default 값이 들어갔다.
한 가지 차이점은 upsert를 사용한 경우 충돌 시 업데이트할 컬럼을 지정할 수 없어 값이 할당된 userNum과 nickname 컬럼이 update set에 포함되었고, 쿼리빌더를 사용한 경우에는 첫번째 인자로 넣어준 값이 포함되었다.
Bulk Upsert
다음으로 엔티티 배열을 upsert하는 경우를 살펴보자.
async upsert(): Promise<void> {
const user1 = { userNum: 1, nickname: 'test1' };
const user2 = { userNum: 2, nickname: 'test2' };
// Case 1
await this.userRepository.upsert([user1, user2], ['userNum']);
// Case 2
await this.userRepository.createQueryBuilder().insert().into(User).values([user1, user2]).orUpdate(['nickname'], ['userNum']).execute();
}
INSERT INTO "user"("userNum", "nickname", "updated")
VALUES ($1, $2, DEFAULT), ($3, $4, DEFAULT)
ON CONFLICT ( "userNum" ) DO UPDATE
SET "userNum" = EXCLUDED."userNum", "nickname" = EXCLUDED."nickname" -- PARAMETERS: [1,"test1",2,"test2"]
INSERT INTO "user"("userNum", "nickname", "updated")
VALUES ($1, $2, DEFAULT), ($3, $4, DEFAULT)
ON CONFLICT ( "userNum" ) DO UPDATE
SET "nickname" = EXCLUDED."nickname" -- PARAMETERS: [1,"test1",2,"test2"]
여기까지는 values에 값이 추가된 것 말고는 단일 엔티티를 upsert했을 때와 동일하다.
문제는 upsert하려는 엔티티들이 서로 다른 컬럼을 갖고 있는 경우이다.
문제 상황
테이블에 다음과 같은 데이터가 있다고 가정하자.
| userNum | nickname | updated | |
| user1 | 1 | test1 | Date |
| user2 | 2 | test2 | Date |
여기에서 user1은 nickname만, user2는 nickname과 updated 컬럼 모두 변경하려고 한다.
원하는 결과는 아래와 같다.
| userNum | nickname | udpated | |
| user1 | 1 | test1 -> test11 | Date |
| user2 | 2 | test2 -> test22 | Date -> new Date |
async upsert(): Promise<void> {
const user1 = { userNum: 1, nickname: 'test11' };
const user2 = { userNum: 2, nickname: 'test22', updated: new Date() };
// Case 1
await this.userRepository.upsert([user1, user2], ['userNum']);
// Case 2
await this.userRepository
.createQueryBuilder()
.insert()
.into(User)
.values([user1, user2])
.orUpdate(['nickname', 'updated'], ['userNum'])
.execute();
}
INSERT INTO "user"("userNum", "nickname", "updated")
VALUES ($1, $2, DEFAULT), ($3, $4, $5)
ON CONFLICT ( "userNum" ) DO UPDATE
SET "userNum" = EXCLUDED."userNum", "nickname" = EXCLUDED."nickname", "updated" = EXCLUDED."updated"
-- PARAMETERS: [1,"test1",2,"test2","2024-08-04T18:28:52.914Z"]
INSERT INTO "user"("userNum", "nickname", "updated")
VALUES ($1, $2, DEFAULT), ($3, $4, $5)
ON CONFLICT ( "userNum" ) DO UPDATE
SET "nickname" = EXCLUDED."nickname", "updated" = EXCLUDED."updated"
-- PARAMETERS: [1,"test1",2,"test2","2024-08-04T18:28:52.914Z"]
하지만 코드를 실행해보면 예상과 다른 결과가 나타난다.
| userNum | nickname | updated | |
| user1 | 1 | test1 -> test11 | Date -> null |
| user2 | 2 | test2 -> test22 | Date -> new Date |
user1의 updated 컬럼은 기존 데이터가 유지되는 것을 의도했지만 SQL문에서는 default 값이 전달되어 null로 바뀐 것이다.
문제가 발생하는 원인은 updated 컬럼의 변경이 필요한 데이터가 추가되면서 set에 포함시킬 컬럼이 모두 포함되기 때문이다. 이로 인해 변경이 필요없는 데이터까지 default 값으로 바뀌게 된다.
해결 방안
첫번째 방법은 save method를 사용하는 것이다.
async save(): Promise<void> {
const user1 = { userNum: 1, nickname: 'test11' };
const user2 = { userNum: 2, nickname: 'test22', updated: new Date() };
await this.userRepository.save([user1, user2]);
}SELECT "User"."userNum" AS "User_userNum", "User"."nickname" AS "User_nickname", "User"."updated" AS "User_updated" FROM "user" "User" WHERE "User"."userNum" IN ($1, $2) -- PARAMETERS: [1,2]
START TRANSACTION
UPDATE "user" SET "nickname" = $1 WHERE "userNum" IN ($2) -- PARAMETERS: ["test11",1]
UPDATE "user" SET "nickname" = $1, "updated" = $2 WHERE "userNum" IN ($3) -- PARAMETERS: ["test22","2024-08-04T18:42:15.527Z",2]
COMMIT
장점은 코드를 깔끔하게 작성할 수 있다는 점이다. 별도의 설정을 하지 않아도 의도하는대로 작동한다. 실행되는 쿼리를 살펴보면 각 엔티티에 대해 별개의 update문을 실행하기 때문에 업데이트할 컬럼이 달라도 서로 영향을 주지 않는다.
단점은 처음에 언급했듯이 upsert를 사용하는 이유가 save의 성능 문제 때문이다. 데이터 쓰기 작업이 빈번하지 않다면 큰 문제가 되지 않겠지만 5000개의 데이터 중에 2500개가 update문이 실행된다고 가정하면 select 1번 + insert 1번 + update 2500번 총 2502번의 쿼리를 실행시켜야 한다.
두번째 방법은 QueryBuilder의 onConflict method를 사용하는 것이다.
기본적으로 생성하는 쿼리는 orUpdate method와 동일하나 onConflict 뒤에 들어갈 raw query를 직접 작성할 수 있다. upsert가 의도대로 작동하지 않았던 이유는 default 값인 null 값으로 덮여쓰여지기 때문이었는데 sql level에서 coalesce 함수를 통해 null 값인 경우 기존 데이터를 유지하도록 설정할 수 있다.
async upsert(): Promise<void> {
const user1 = { userNum: 1, nickname: 'test11' };
const user2 = { userNum: 2, nickname: 'test22', updated: new Date() };
await this.userRepository
.createQueryBuilder()
.insert()
.into(User)
.values([user1, user2])
.onConflict('("userNum") do update set "nickname" = excluded."nickname", "updated" = COALESCE(excluded.updated, "user".updated)')
.execute();
}
장점은 데이터의 개수가 많아도 한번의 쿼리로 실행할 수 있다는 점이다.
단점은 orUpdate method가 추가되면서 현재 버전에서 deprecated되어버렸다. 당장은 사용해도 문제는 없지만 추후 버전 업데이트 시 삭제될 가능성이 있다. 만약 버전 업그레이드할 생각이 없다면 이 방법도 나쁘지는 않아 보인다.
여담으로 onConflict method가 deprecated된 이유는 공식 레포지토리를 봐도 찾지 못했다. 겉보기에는 onConflict method가 유연한 쿼리를 작성하는데 좀 더 강점이 있어보이는데 말이다. 아래 공식 레포지토리 이슈 탭을 살펴보면 onConflict method에 대한 글들이 아직 Open된 상태이다.
Revert deprecation of InsertQueryBuilder.onConflict · Issue #8168 · typeorm/typeorm (github.com)