実例: データベース選定 — PostgreSQL vs MySQL

# db/schema.rb (現状)
create_table :shops do |t|
  t.string  :name, null: false
  t.string  :address
  t.string  :city
  t.string  :prefecture
  t.decimal :latitude,  precision: 10, scale: 6
  t.decimal :longitude, precision: 10, scale: 6
  t.string  :category
  t.string  :business_hours
  t.timestamps
end
 
add_index :shops, [:latitude, :longitude]
add_index :shops, :category

-- MySQL 8.0: 近い店舗の検索（Haversine公式を使用）
SELECT
  id,
  name,
  latitude,
  longitude,
  ST_Distance_Sphere(
    POINT(longitude, latitude),
    POINT(139.7671, 35.6812)
  ) AS distance_meters
FROM shops
WHERE ST_Distance_Sphere(
  POINT(longitude, latitude),
  POINT(139.7671, 35.6812)
) <= 3000
ORDER BY distance_meters
LIMIT 20;

-- PostgreSQL + PostGIS: 近い店舗の検索
-- GISTインデックスが使われるため高速
SELECT
  id,
  name,
  ST_Distance(
    location::geography,
    ST_SetSRID(ST_MakePoint(139.7671, 35.6812), 4326)::geography
  ) AS distance_meters
FROM shops
WHERE ST_DWithin(
  location::geography,
  ST_SetSRID(ST_MakePoint(139.7671, 35.6812), 4326)::geography,
  3000  -- 3km以内
)
ORDER BY distance_meters
LIMIT 20;

# MySQL + 手動実装の場合
class Shop < ApplicationRecord
  # Haversine公式による近傍検索（インデックス非使用）
  EARTH_RADIUS_KM = 6371.0
 
  def self.near(lat, lng, radius_km)
    radius_meters = radius_km * 1000
 
    # バウンディングボックスで絞り込んでからHaversine
    lat_delta = radius_km / 111.0
    lng_delta = radius_km / (111.0 * Math.cos(lat * Math::PI / 180))
 
    in_bbox = where(
      latitude:  (lat - lat_delta)..(lat + lat_delta),
      longitude: (lng - lng_delta)..(lng + lng_delta)
    )
 
    # さらにHaversine公式でフィルタリング（Ruby側）
    in_bbox.select do |shop|
      haversine_distance(lat, lng, shop.latitude, shop.longitude) <= radius_km
    end
  end
 
  def self.haversine_distance(lat1, lng1, lat2, lng2)
    dlat = (lat2 - lat1) * Math::PI / 180
    dlng = (lng2 - lng1) * Math::PI / 180
    a = Math.sin(dlat/2)**2 +
        Math.cos(lat1 * Math::PI/180) * Math.cos(lat2 * Math::PI/180) * Math.sin(dlng/2)**2
    c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a))
    EARTH_RADIUS_KM * c
  end
end

# Gemfile
gem 'activerecord-postgis-adapter'
gem 'rgeo-activerecord'
 
# app/models/shop.rb
class Shop < ApplicationRecord
  # PostGIS を使った位置情報スコープ
  # 詳細: ADR-008 docs/adr/008-postgresql-migration.md
  scope :near, ->(lat, lng, radius_km) {
    origin = "SRID=4326;POINT(#{lng} #{lat})"
 
    where(
      "ST_DWithin(location::geography, ST_GeomFromEWKT(?), ?)",
      origin,
      radius_km * 1000
    ).select(
      Arel.sql("shops.*, ST_Distance(location::geography, ST_GeomFromEWKT('#{origin}')) AS distance_meters")
    ).order(
      Arel.sql("ST_Distance(location::geography, ST_GeomFromEWKT('#{origin}'))")
    )
  }
 
  scope :in_category, ->(category) { where(category: category) }
 
  scope :open_now, -> {
    current_hour = Time.current.hour
    where("? BETWEEN opening_hour AND closing_hour", current_hour)
  }
end
 
# 使い方
# 渋谷から3km以内のカフェを近い順に20件取得
shops = Shop
  .near(35.6581, 139.7017, 3.0)
  .in_category("cafe")
  .limit(20)
 
shops.each do |shop|
  puts "#{shop.name}: #{(shop.distance_meters.to_f / 1000).round(2)}km"
end

# MySQL + SPATIAL INDEX を使ったバウンディングボックス + Haversine
class Shop < ApplicationRecord
  EARTH_RADIUS_KM = 6371.0
 
  def self.near_mysql(lat, lng, radius_km)
    # ステップ1: バウンディングボックスで候補を絞る（インデックス使用）
    lat_delta = radius_km / 111.0
    lng_delta = radius_km / (111.0 * Math.cos(lat.to_f * Math::PI / 180))
 
    # ステップ2: SQL内でHaversine計算（MySQLのMBRWithinと距離計算の組み合わせ）
    # ただし、この距離計算にはインデックスが使われない
    where(
      "MBRWithin(
        POINT(longitude, latitude),
        Envelope(
          LineString(
            POINT(?, ?),
            POINT(?, ?)
          )
        )
      )",
      lng - lng_delta, lat - lat_delta,
      lng + lng_delta, lat + lat_delta
    ).select(
      "*, (6371000 * acos(
        GREATEST(-1.0, LEAST(1.0,
          cos(radians(?)) * cos(radians(latitude)) *
          cos(radians(longitude) - radians(?)) +
          sin(radians(?)) * sin(radians(latitude))
        ))
      )) AS distance_meters",
      lat, lng, lat
    ).having("distance_meters <= ?", radius_km * 1000)
    .order("distance_meters")
  end
end

# ADR-008: データベースをMySQL 8.0からPostgreSQL 14に移行する
 
## Status
Accepted
 
## Context
2024年2月、店舗検索機能の追加要件が発生した。
具体的な要件:
- 現在地から半径X km以内の店舗を高速に検索
- 将来的な地図ポリゴン検索（特定エリア内の店舗）
- 店舗間の距離計算・並び替え
 
現状のデータ規模:
- 店舗テーブル: 約50,000件
- 月次成長率: 約5%（1年後には約80,000件の見込み）
 
ベンチマーク測定結果（50,000件）:
- MySQL + SPATIAL INDEX: 85ms
- PostgreSQL + PostGIS + GIST: 8ms
（詳細: benchmarks/20240215_db_comparison.md）
 
現在のインフラ:
- Amazon RDS MySQL 8.0 (db.t3.medium)
- Rails 7.1 + ActiveRecord
 
検討した選択肢:
1. MySQLのSPATIAL INDEXを使って位置情報検索を実装する
2. PostgreSQL + PostGISに移行する
3. 位置情報専用DBを別途追加する（Elasticsearch）
4. Amazon Location Serviceを使う
 
## Decision
PostgreSQL 14 + PostGIS 3.xに移行する。
Amazon RDS for PostgreSQLを使用し、
activerecord-postgis-adapterでRailsと統合する。
 
MySQLのSPATIAL INDEXを選ばない理由:
- 距離計算にHaversine公式を手動実装する必要があり、
  コードの複雑さとバグリスクが高い（コードレビューで指摘済み）
- ベンチマーク結果、GISTインデックスの方が10倍以上高速（8ms vs 85ms）
- 将来のポリゴン検索や地理的集計の拡張性がMySQLより低い
- `activerecord-postgis-adapter` に相当するMySQLのgemがなく、
  Railsとの統合コードが増える
 
ElasticsearchやAmazon Location Serviceを選ばない理由:
- 現在の規模（50,000件）ではオーバーエンジニアリング
- 追加インフラの管理コストが発生する（月$50〜$200以上）
- チームに運用経験がない（学習コスト2〜3ヶ月）
 
## Consequences
良い影響:
- PostGISのGISTインデックスにより、位置情報検索が8ms以下で動作
- activerecord-postgis-adapterでRailsらしいコードで実装できる
- PostgreSQLのJSONB, ARRAY型など、将来の機能拡張に対応しやすい
- RDSでの運用は現在のMySQLと変わらない
- PostGISの豊富な関数群（ST_DWithin, ST_Buffer, ST_Contains）が使える
 
悪い影響・移行コスト:
- 移行開発工数: 約4週間（スキーマ変換・データ移行・テスト）
- QA期間: 別途2週間
- MySQLの一部関数（GROUP_CONCAT等）はPostgreSQL方言に変更が必要
  （調査済み: 約30箇所）
- 現在の開発環境全員がPostgreSQLをローカルに設定する必要がある
 
移行方針:
1. 新しいRDS PostgreSQLインスタンスを作成（Terraform追加）
2. pgloaderでデータ移行（ステージングで検証済み後本番適用）
3. ステージング環境で全機能テスト（2週間）
4. ブルーグリーンデプロイで本番切り替え
5. MySQLインスタンスを30日間保持してからシャットダウン
 
再評価のトリガー:
- 将来、時系列データや全文検索の大規模要件が出た場合は
  専用DBの追加を検討する

# Gemfile
# gem 'mysql2'  # 削除
gem 'pg', '~> 1.5'
gem 'activerecord-postgis-adapter'
gem 'rgeo-activerecord'

# config/database.yml
default: &default
  adapter: postgis
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
 
development:
  <<: *default
  database: myapp_development
 
test:
  <<: *default
  database: myapp_test
 
production:
  <<: *default
  url: <%= ENV['DATABASE_URL'] %>

# db/migrate/20240215_add_postgis_extension.rb
class AddPostgisExtension < ActiveRecord::Migration[7.1]
  def change
    enable_extension 'postgis'
  end
end
 
# db/migrate/20240216_add_location_to_shops.rb
class AddLocationToShops < ActiveRecord::Migration[7.1]
  def change
    # 既存のlatitude/longitudeカラムをそのまま残しつつ
    # PostGIS用のgeographyカラムを追加
    add_column :shops, :location, :st_point, geographic: true
 
    # 既存データのマイグレーション
    reversible do |dir|
      dir.up do
        execute <<~SQL
          UPDATE shops
          SET location = ST_SetSRID(
            ST_MakePoint(longitude::float, latitude::float),
            4326
          )::geography
          WHERE latitude IS NOT NULL AND longitude IS NOT NULL
        SQL
      end
    end
 
    # GISTインデックスの追加（位置情報検索の核心）
    add_index :shops, :location, using: :gist
 
    # 移行完了後に確認: lat/lngは残しておく（ロールバック保険）
    # 問題なければADR-009で削除するADRを書く
  end
end

# app/models/shop.rb
class Shop < ApplicationRecord
  # ADR-008: PostGIS採用。位置情報検索のAPIについては
  # docs/adr/008-postgresql-migration.md を参照
  scope :near, ->(lat, lng, radius_km) {
    origin = RGeo::Geographic.spherical_factory(srid: 4326)
                              .point(lng.to_f, lat.to_f)
 
    where(
      "ST_DWithin(location, ST_GeomFromText(?, 4326)::geography, ?)",
      origin.as_text,
      radius_km * 1000
    ).select(
      Arel.sql(
        "shops.*, " \
        "ST_Distance(location, ST_GeomFromText('#{origin.as_text}', 4326)::geography)" \
        " AS distance_meters"
      )
    ).order(
      Arel.sql(
        "ST_Distance(location, ST_GeomFromText('#{origin.as_text}', 4326)::geography)"
      )
    )
  }
 
  scope :in_category, ->(category) { where(category: category) if category.present? }
 
  before_save :sync_location
 
  private
 
  def sync_location
    return unless latitude_changed? || longitude_changed?
    return if latitude.blank? || longitude.blank?
 
    factory = RGeo::Geographic.spherical_factory(srid: 4326)
    self.location = factory.point(longitude.to_f, latitude.to_f)
  end
end

# spec/models/shop_spec.rb
RSpec.describe Shop, type: :model do
  describe '.near' do
    let!(:shibuya_shop) do
      create(:shop,
        name: '渋谷店',
        latitude: 35.6581,
        longitude: 139.7017
      )
    end
 
    let!(:shinjuku_shop) do
      create(:shop,
        name: '新宿店',
        latitude: 35.6896,
        longitude: 139.6917
      )
    end
 
    let!(:yokohama_shop) do
      create(:shop,
        name: '横浜店',
        latitude: 35.4437,
        longitude: 139.6380
      )
    end
 
    context '渋谷から3km以内' do
      it '渋谷店と新宿店が返る（横浜は含まない）' do
        shops = Shop.near(35.6581, 139.7017, 3.0)
 
        expect(shops.map(&:name)).to include('渋谷店', '新宿店')
        expect(shops.map(&:name)).not_to include('横浜店')
      end
 
      it '距離の近い順で返る' do
        shops = Shop.near(35.6581, 139.7017, 3.0)
        distances = shops.map { |s| s.distance_meters.to_f }
 
        expect(distances).to eq(distances.sort)
      end
    end
  end
end

# pgloaderを使ったMySQLからPostgreSQLへのデータ移行
 
# pgloader設定ファイル
cat > /tmp/migrate.load << 'EOF'
LOAD DATABASE
  FROM    mysql://user:password@mysql-host/myapp_production
  INTO    postgresql://user:password@pg-host/myapp_production
 
WITH include no drop,
     create tables,
     create indexes,
     reset sequences
 
SET search_path to 'public'
 
EXCLUDING TABLE NAMES MATCHING 'schema_migrations', 'ar_internal_metadata'
 
CAST
  type tinyint to boolean using tinyint-to-boolean,
  type datetime to timestamptz
;
EOF
 
# 実行
pgloader /tmp/migrate.load
 
# 移行後の確認
psql $DATABASE_URL -c "SELECT COUNT(*) FROM shops;"
psql $DATABASE_URL -c "SELECT name, latitude, longitude FROM shops LIMIT 5;"

# ADR-008の追記（2024年5月）
 
## 実装後レビュー
移行から3ヶ月後の評価:
 
実際のパフォーマンス: P95 6ms（想定: 8ms以下 → 予測通り）
 
想定外の作業:
- GROUP_CONCATのSTRING_AGGへの変換が約35箇所（想定30箇所）
- 開発環境のPostgreSQLセットアップで新メンバー2名が詰まった
  → Dockerfileに手順追加で解消（PR#387）
 
ADRの有効性:
- 移行理由が明確だったため、途中の困難でもチームのモチベーションが維持された
- 「なぜこの移行をやっているか」という疑問がゼロだった