🤖

ridgepoleでRANGEパーティショニング（したかったけどワーニングが気持ち悪いから諦めてSQL書いた件）

2021/12/29に公開

Ruby

ridgepole

tech

追記
ちょっと無理矢理だけどもう少しマシな方法を見つけたので別記事にした。
ridgepoleでRANGEパーティショニング
追記終わり

created_atでRANGEパーティショニングしたい
idはAUTO_INCREMENTのままがいい

という条件の下、ridgepoleでやろうとしたら綺麗にできなかったのでメモ。

スキーマ

partitions = []
partitions << "PARTITION p201610 VALUES LESS THAN ('2016-10-01') ENGINE = InnoDB"
partitions << "PARTITION p201611 VALUES LESS THAN ('2016-11-01') ENGINE = InnoDB"
# partitionは必要に応じて、列挙する
partitioning_sql = "/*!50500 PARTITION BY RANGE COLUMNS(created_at)\n(#{partitions.join(",\n ")}) */"

create_table "histories", primary_key: %w(id created_at), force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4\n#{partitioning_sql}" do |t|
  t.bigint   "id", null: false, auto_increment: true, unsigned: true
  t.bigint   "user_id", default: 0, null: false, unsigned: true
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

-- create_table("histories", {:primary_key=>["id", "created_at"], :options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8mb4\n/*!50500 PARTITION BY RANGE COLUMNS(created_at)\n(PARTITION p201610 VALUES LESS THAN ('2016-10-01') ENGINE = InnoDB,\n PARTITION p201611 VALUES LESS THAN ('2016-11-01') ENGINE = InnoDB) */"})
   -> 0.0275s

と出て、テーブルが作られている。うまく行ったかなぁと思って、もう一度実行してみると

[WARNING] No difference of schema configuration for table `histories` but table options differ.
  from: {:primary_key=>["id", "created_at"], :options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8mb4\n/*!50500 PARTITION BY RANGE  COLUMNS(created_at)\n(PARTITION p201610 VALUES LESS THAN ('2016-10-01') ENGINE = InnoDB,\n PARTITION p201611 VALUES LESS THAN ('2016-11-01') ENGINE = InnoDB) */"}
    to: {:primary_key=>["id", "created_at"], :options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8mb4\n/*!50500 PARTITION BY RANGE COLUMNS(created_at)\n(PARTITION p201610 VALUES LESS THAN ('2016-10-01') ENGINE = InnoDB,\n PARTITION p201611 VALUES LESS THAN ('2016-11-01') ENGINE = InnoDB) */"}
[WARNING] Table `histories`: `default: nil` is ignored when `null: false`. Please apply twice
-- change_column("histories", "id", :bigint, {:null=>false, :auto_increment=>true, :unsigned=>true, :default=>nil})
   -> 0.0270s

こんな感じで、WARNINGが出る。

1つ目のWARNING

[WARNING] No difference of schema configuration for table `histories` but table options differ.
  from: {:primary_key=>["id", "created_at"], :options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8mb4\n/*!50500 PARTITION BY RANGE  COLUMNS(created_at)\n(PARTITION p201610 VALUES LESS THAN ('2016-10-01') ENGINE = InnoDB,\n PARTITION p201611 VALUES LESS THAN ('2016-11-01') ENGINE = InnoDB) */"}
    to: {:primary_key=>["id", "created_at"], :options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8mb4\n/*!50500 PARTITION BY RANGE COLUMNS(created_at)\n(PARTITION p201610 VALUES LESS THAN ('2016-10-01') ENGINE = InnoDB,\n PARTITION p201611 VALUES LESS THAN ('2016-11-01') ENGINE = InnoDB) */"}

は、PARTITION BY RANGEの後にスペースを1つ入れれば解決するが何かモヤモヤする…。

2つ目のWARNING

[WARNING] Table `histories`: `default: nil` is ignored when `null: false`. Please apply twice

これは、現状ではどうする事もできない気配。

dry-runしてみると

ALTER TABLE `histories` CHANGE `id` `id` bigint unsigned NOT NULL AUTO_INCREMENT

が発行されてしまうらしい…。

SQL的には

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT

を指定したいけど、ridepole的にはnull: falseの場合にはdefaultを指定する必要がある。

最終的に

2つ目のWARNINGはridepole本体に手を入れないといけないような気配なので一旦保留。
(ちゃんとコード追って必要ならばPRするか！？)

そして、ridepoleの管理下から外して(--ignore-tables)生のSQLを流し混む事にした。

もやもや。

SQL

とりあえず、10年分用意しておけばきっと大丈夫ｗ

CREATE TABLE `histories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`,`created_at`),
  KEY `key_histories_1` (`user_id`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE  COLUMNS(created_at)
(PARTITION p201610 VALUES LESS THAN ('2016-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201611 VALUES LESS THAN ('2016-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201612 VALUES LESS THAN ('2016-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201701 VALUES LESS THAN ('2017-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201702 VALUES LESS THAN ('2017-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201703 VALUES LESS THAN ('2017-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201704 VALUES LESS THAN ('2017-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201705 VALUES LESS THAN ('2017-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201706 VALUES LESS THAN ('2017-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201707 VALUES LESS THAN ('2017-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201708 VALUES LESS THAN ('2017-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201709 VALUES LESS THAN ('2017-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201710 VALUES LESS THAN ('2017-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201711 VALUES LESS THAN ('2017-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201712 VALUES LESS THAN ('2017-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201801 VALUES LESS THAN ('2018-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201802 VALUES LESS THAN ('2018-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201803 VALUES LESS THAN ('2018-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201804 VALUES LESS THAN ('2018-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201805 VALUES LESS THAN ('2018-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201806 VALUES LESS THAN ('2018-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201807 VALUES LESS THAN ('2018-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201808 VALUES LESS THAN ('2018-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201809 VALUES LESS THAN ('2018-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201810 VALUES LESS THAN ('2018-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201811 VALUES LESS THAN ('2018-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201812 VALUES LESS THAN ('2018-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201901 VALUES LESS THAN ('2019-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201902 VALUES LESS THAN ('2019-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201903 VALUES LESS THAN ('2019-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201904 VALUES LESS THAN ('2019-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201905 VALUES LESS THAN ('2019-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201906 VALUES LESS THAN ('2019-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201907 VALUES LESS THAN ('2019-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201908 VALUES LESS THAN ('2019-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201909 VALUES LESS THAN ('2019-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201910 VALUES LESS THAN ('2019-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201911 VALUES LESS THAN ('2019-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION p201912 VALUES LESS THAN ('2019-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202001 VALUES LESS THAN ('2020-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202002 VALUES LESS THAN ('2020-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202003 VALUES LESS THAN ('2020-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202004 VALUES LESS THAN ('2020-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202005 VALUES LESS THAN ('2020-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202006 VALUES LESS THAN ('2020-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202007 VALUES LESS THAN ('2020-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202008 VALUES LESS THAN ('2020-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202009 VALUES LESS THAN ('2020-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202010 VALUES LESS THAN ('2020-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202011 VALUES LESS THAN ('2020-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202012 VALUES LESS THAN ('2020-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202101 VALUES LESS THAN ('2021-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202102 VALUES LESS THAN ('2021-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202103 VALUES LESS THAN ('2021-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202104 VALUES LESS THAN ('2021-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202105 VALUES LESS THAN ('2021-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202106 VALUES LESS THAN ('2021-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202107 VALUES LESS THAN ('2021-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202108 VALUES LESS THAN ('2021-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202109 VALUES LESS THAN ('2021-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202110 VALUES LESS THAN ('2021-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202111 VALUES LESS THAN ('2021-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202112 VALUES LESS THAN ('2021-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202201 VALUES LESS THAN ('2022-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202202 VALUES LESS THAN ('2022-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202203 VALUES LESS THAN ('2022-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202204 VALUES LESS THAN ('2022-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202205 VALUES LESS THAN ('2022-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202206 VALUES LESS THAN ('2022-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202207 VALUES LESS THAN ('2022-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202208 VALUES LESS THAN ('2022-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202209 VALUES LESS THAN ('2022-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202210 VALUES LESS THAN ('2022-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202211 VALUES LESS THAN ('2022-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202212 VALUES LESS THAN ('2022-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202301 VALUES LESS THAN ('2023-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202302 VALUES LESS THAN ('2023-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202303 VALUES LESS THAN ('2023-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202304 VALUES LESS THAN ('2023-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202305 VALUES LESS THAN ('2023-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202306 VALUES LESS THAN ('2023-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202307 VALUES LESS THAN ('2023-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202308 VALUES LESS THAN ('2023-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202309 VALUES LESS THAN ('2023-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202310 VALUES LESS THAN ('2023-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202311 VALUES LESS THAN ('2023-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202312 VALUES LESS THAN ('2023-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202401 VALUES LESS THAN ('2024-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202402 VALUES LESS THAN ('2024-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202403 VALUES LESS THAN ('2024-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202404 VALUES LESS THAN ('2024-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202405 VALUES LESS THAN ('2024-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202406 VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202407 VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202408 VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202409 VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202410 VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202411 VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202412 VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202501 VALUES LESS THAN ('2025-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202502 VALUES LESS THAN ('2025-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202503 VALUES LESS THAN ('2025-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202504 VALUES LESS THAN ('2025-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202505 VALUES LESS THAN ('2025-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202506 VALUES LESS THAN ('2025-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202507 VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202508 VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202509 VALUES LESS THAN ('2025-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202510 VALUES LESS THAN ('2025-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202511 VALUES LESS THAN ('2025-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202512 VALUES LESS THAN ('2025-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202601 VALUES LESS THAN ('2026-01-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202602 VALUES LESS THAN ('2026-02-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202603 VALUES LESS THAN ('2026-03-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202604 VALUES LESS THAN ('2026-04-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202605 VALUES LESS THAN ('2026-05-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202606 VALUES LESS THAN ('2026-06-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202607 VALUES LESS THAN ('2026-07-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202608 VALUES LESS THAN ('2026-08-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202609 VALUES LESS THAN ('2026-09-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202610 VALUES LESS THAN ('2026-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202611 VALUES LESS THAN ('2026-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202612 VALUES LESS THAN ('2026-12-01 00:00:00') ENGINE = InnoDB,
 PARTITION p202701 VALUES LESS THAN ('2027-01-01 00:00:00') ENGINE = InnoDB);

モデル

このままだと、 WARNING: Rails does not support composite primary key.とでるので、モデルではprimary_keyをidだけにする

class History < ApplicationRecord
  self.primary_key = :id
end

schemaファイル

name = 'histories'
partitions = []
(Date.new(2016, 10, 1) .. Date.new(2027, 1, 1)).map(&:beginning_of_month).uniq.each do |d|
  date = d.strftime('%Y%m')
  partitions << "PARTITION p#{date} VALUES LESS THAN ('#{d.strftime('%Y-%m-01 00:00:00')}') ENGINE = InnoDB"
end
partitioning_sql = "PARTITION BY RANGE COLUMNS(created_at) (#{partitions.join(',')});"

sql = <<EOD
CREATE TABLE IF NOT EXISTS `#{name}` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`,`created_at`),
  KEY `key_#{name}_1` (`user_id`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
#{partitioning_sql}
EOD

execute(sql) unless ActiveRecord::Base.connection.data_source_exists?(name)

1年運用しての知見

alterをかけたい時は手動でクエリ叩かないといけないのでスキーマが安定しているテーブルでのみ使うべき
自分以外の開発者のテスト環境でもalter叩かないといけないのでテスト落ちるとかがちょいちょい起こった