SQLite의 한계와 ActiveRecord 동시성 문제
서비스는 Firehose 스트림 소비자 프로세스를 통해 게시물을 저장하고 Sinatra API 서버는 저장된 데이터를 조회하는 단일 쓰기 아키텍처를 가졌습니다. 그러나 PLC 디렉토리 데이터 저장 스레드 및 다양한 Cron 작업이 추가되면서 SQLite의 동시 쓰기 제한은 SQLite3::BusyException 오류로 이어졌습니다. ActiveRecord가 트랜잭션 및 잠금 모드를 처리하는 방식의 문제로, 읽기 잠금 후 쓰기 잠금으로 전환하려 할 때 다른 쓰기가 발생하면 즉시 예외가 발생했습니다. 이를 회피하기 위해 FeedPost.where(feed_id: -1).update_all(feed_id: -1)와 같은 인위적인 쓰기 작업을 먼저 수행하거나, Post.where(id: @post.id).update_all(thread_id: root.id)와 같이 모델 객체를 직접 건드리지 않는 방식으로 코드를 수정해야 했습니다. 아이러니하게도 ActiveRecord 8.0에서 이 문제가 해결되었지만, 필자는 이미 마이그레이션을 완료한 시점이었습니다.
MySQL 및 PostgreSQL로의 마이그레이션 과정
SQLite의 한계와 성능 문제를 해결하기 위해 MySQL과 PostgreSQL로의 전환을 시도했습니다. 이 과정에서 여러 도전 과제에 직면했습니다.
-
컬럼 타입 변경: SQLite의 유연한 숫자 및 문자열 타입과 달리, MySQL에서는
smallint,bigint,text등으로 명시적인 타입 변경이 필요했으며,datetime컬럼의 정밀도 설정도 중요했습니다. -
쿼리 재작성:
+thread_id IS NULL과 같은 SQLite 특정 인덱스 힌트를 제거하고,DATETIME('now', '-7 days')를SUBDATE(CURRENT_TIMESTAMP, 7)로,DATE_SUBTRACT(CURRENT_TIMESTAMP, INTERVAL '7 days')와 같이 각 데이터베이스의 날짜 함수에 맞춰 쿼리를 수정했습니다. 또한SELECT절에 테이블 이름을 명시하거나DELETE쿼리에서 서브쿼리를 중첩하는 등 구문 오류를 해결해야 했습니다. -
데이터 마이그레이션 도구: MySQL로의 마이그레이션에는 Python 기반의
sqlite3mysql도구를 사용했으며, PostgreSQL에는pgloader를 활용했습니다. 대용량posts테이블은 배치 처리 옵션을 통해 마이그레이션했습니다. -
예상치 못한 데이터 문제: SQLite가 컬럼 길이 제한을 강제하지 않아, 일부 레코드의 문자열 길이가 예상보다 길어 새로운 데이터베이스에서 거부되는 문제가 발생했습니다. 이는 Ruby 측의
validates_length_of유효성 검사 누락 때문이었습니다. MySQL에서는 악센트 및 유니코드 정규화 규칙 차이로 인해 해시태그 테이블의 고유 인덱스 충돌 문제가 발생했고, PostgreSQL에서는 널 바이트를 포함한 문자열 처리 문제가 나타나 해당 게시물을 필터링해야 했습니다. -
시간대(Timezone) 문제: PostgreSQL에서는
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.datetime_type = :timestamptz설정을 통해timestamptz타입을 사용하도록 변경했으며, MySQL에서는ActiveRecord.default_timezone = :local또는 데이터베이스 자체의default-time-zone = '+00:00'설정을 통해 시간대 문제를 해결했습니다.
최적화 및 비교 테스트
두 데이터베이스의 성능을 공정하게 비교하기 위해 A/B 테스트 방식으로 트래픽을 분산했습니다. 초기에는 특정 쿼리에서 MySQL 또는 PostgreSQL이 지연되는 문제가 발생했으며, 이는 주로 누락된 인덱스를 추가하거나 복합 인덱스의 필드를 조정하고 쿼리 구조를 변경하여 해결했습니다. 특히 PostgreSQL 버전에서는 ‘replies feeds’의 특정 쿼리 성능 개선에 많은 시간을 할애했습니다. (user, time) 인덱스를 (user, time DESC, id)로 변경하여 ‘Index Only Scans’가 가능하도록 했으며, 빈번한 VACUUM 작업을 cron job으로 설정하여 인덱스 정리를 강제했습니다. 이러한 최적화 후, PostgreSQL은 해당 쿼리 응답 시간을 20ms 미만으로 단축하여 MySQL의 50ms보다 우수한 성능을 보였습니다.