Splitting DB on "Rails"

Presented on RejectKaigi 2018
https://speee.connpass.com/event/84915/

https://gist.github.com/izumin5210/8ebbf32a54dcb142d31cde9254ccd336

Transcript

1. Splitting DB on "Rails" How to split DB efficiently with Ruby "magic" RejectKaigi 2018 12.May.2018 - @izumin5210
2. izumin5210 Engineer at Wantedly, Inc. Wantedly People ‣ Web Application Engineer - Profile Data Strategy Group ‣ Interests in developer productivity on microservices Gopher, Rubyist, JavaScripter and Androider
3. Background ͳͥ
   %#
   Λ෼ׂ͢Δඞཁ͕͔͋ͬͨ Splitting DB on Rails ͍͔ʹͯ͠
   %#
   ෼ׂΛਐΊ͍͔ͯ͘ Agenda
4. ©2018 Wantedly, Inc. Background WHY did we need to split the database?
5. ©2018 Wantedly, Inc. Background History of our apps 2012 ~ 2016.01 ~ 2016.11 ~
6. ©2018 Wantedly, Inc. Background History of our apps 2012 ~ 2016.01 ~ 2016.11 ~ Monolithic Rails app
7. ©2018 Wantedly, Inc. Background History of our apps 2012 ~ 2016.01 ~ 2016.11 ~ Monolithic Rails app 2 Rails apps
 (Main, Accounts)
8. ©2018 Wantedly, Inc. Background History of our apps The Go gopher © Renée French, The Ruby logo © Yukihiro Matsumoto 2012 ~ 2016.01 ~ 2016.11 ~ Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python)
9. ©2018 Wantedly, Inc. Background Problems of our apps Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) profiles, working_histories, ... companies messages ... Main DB Read / Write
10. ©2018 Wantedly, Inc. Background Problems of our apps Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) profiles, working_histories, ... companies messages ... Main DB Read / Write GET on Kubernetes cluster
11. ©2018 Wantedly, Inc. Background Problems of our apps Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) profiles, working_histories, ... companies messages ... Main DB Read / Write GET on Kubernetes cluster
12. ©2018 Wantedly, Inc. Background Problems of our apps Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) profiles, working_histories, ... companies messages ... Main DB Read / Write GET on Kubernetes cluster ‣ ͦͦ͜͜େن໛
     ࡀ൒͘Β͍
     ىಈ΋
    $*
    ΋ʢଞʹൺ΂Ε͹ʣ஗͘ͳ͖ͬͯͨ
    ‣ ଞͷαʔϏεͰ͔ͭ͏ʮϓϩϑΟʔϧ৘ใʯ΋͜͜ʹ௥Ճ͢Δͷ͔ʁ
    ‣ ͜ͷαʔϏεࣗମ͕ѻ͏Ϣʔβ਺ɾτϥϑΟοΫ͕ͦ΋ͦ΋ଟ͍
    ͳΔ΂͘ͳΒ͜͜Λܦ༝ͤͣϓϩϑΟʔϧΛऔಘ͍ͨ͠
13. ©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) Main DB Read / Write on Kubernetes cluster
14. ©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Read / Write GET / UPDATE on Kubernetes cluster Profile DB Read / Write
15. ©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Read / Write GET / UPDATE on Kubernetes cluster Profile DB Read / Write ‣ ͦͦ͜͜େن໛
      
    DPNNJUT
      
    JTTVFT
    BOE
    QVMMSFRT
      
    SPVUJOHT
16. ©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Read / Write on Kubernetes cluster Profile DB Read / Write GET / UPDATE
17. ©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Read / Write GET / UPDATE on Kubernetes cluster Profile DB Read / Write "DUJWF3FDPSE
    ͕࢖͑ͳ͘ͳΔ
    
    ͜͜ͷҠߦ͕Ұ൪Ωπ͍
18. ©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Read / Write on Kubernetes cluster Profile DB Read / Write GET / UPDATE ͍͖ͳΓཧ૝ΛٻΊΔͷ͸ݫ͍͠ʜ
    ஈ֊తʹҠߦ͸Ͱ͖ͳ͍͔ʁ
19. ©2018 Wantedly, Inc. Background Ideal architecture - intermediate Monolithic Rails app Main DB Profile DB Read / Write Read / Write 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) GET on Kubernetes cluster
20. ©2018 Wantedly, Inc. Background Ideal architecture - intermediate Monolithic Rails app Main DB Profile DB Read / Write Read / Write 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) GET on Kubernetes cluster ͱΓ͋͑ͣ
    %#
    Λ෼͚Δͱ͜Ζ͔Β
    
    "DUJWF3FDPSE
    Λ࢖͍ଓ͚ΒΕΔ
21. ©2018 Wantedly, Inc. Background Ideal architecture - intermediate ͱΓ͋͑ͣ
    %#
    Λ෼͚Δͱ͜Ζ͔Β
22. ©2018 Wantedly, Inc. Background Ideal architecture - intermediate ‣ ͦͦ͜͜େن໛
      
    DPNNJUT
      
    JTTVFT
    BOE
    QVMMSFRT
      
    SPVUJOHT
23. ©2018 Wantedly, Inc. Background Ideal architecture - intermediate
24. ©2018 Wantedly, Inc.  )PSJ[POUBM
    
    7FSUJDBM
    w *%
    ˓˓˓˓
    Ҏ߱͸ͬͪ͜ͷ
    %#
    ʹ
    ʙ
    
    ͜ͷลΓͷςʔϒϧΛผ
    %#
    ʹʙ
    w ࠓճ͸ޙऀ
     'VODUJPO
    
    %PNBJO
    w 'VODUJPO
    FH
    ಈըΞοϓϩʔυʹؔ࿈͢ΔςʔϒϧΛʙ
    w %PNBJO
    FH
    ϒϩάαʔϏεʹؔ࿈͢ΔςʔϒϧΛʙ
    DB splitting
25. ©2018 Wantedly, Inc.  )PSJ[POUBM
    
    7FSUJDBM
     'VODUJPO
    
    %PNBJO
    w 'VODUJPO
    FH
    ಈըΞοϓϩʔυʹؔ࿈͢ΔςʔϒϧΛʙ
    w %PNBJO
    FH
    ϒϩάαʔϏεʹؔ࿈͢ΔςʔϒϧΛʙ
    
    w $PSF
    EPNBJO
     αʔϏε಺ͷ͋ΒΏΔͱ͜ΖͰར༻͞ΕΔɼαʔϏεͷ֩ʹ͍ۙςʔϒϧΛʙ
     FH
    6TFS
    "DDPVOU
    1SPpMF
    FUD DB splitting
26. ©2018 Wantedly, Inc. ίΞΛ੾Γग़͢
    ೉͠͞ ‣ Ͳ͜·Ͱ੾Γग़͔͢
     Abelongs_to :userA
    ͳϞσϧɼ͍ͭ͋͘Δʁ
    ‣ Ͳ͏΍ͬͯ੾Γग़͔͢
     αʔϏεͷ͍ͨΔͱ͜ΖͰར༻͞Ε͍ͯΔ
     मਖ਼ՕॴɾӨڹൣғͷ೺Ѳ΋ࠔ೉
    ‣ ίΞΛ੾Γग़͍ͨ͠
    ͱߟ͑Δͷ͸ଟ͘ͷ৔߹
    ผͷαʔϏεΛ࡞͍ͬͯΔνʔϜ DB splitting
27. ©2018 Wantedly, Inc. ίΞΛ੾Γग़͢
    ೉͠͞ ‣ Ͳ͜·Ͱ੾Γग़͔͢
    ‣ Ͳ͏΍ͬͯ੾Γग़͔͢
    ‣ ίΞΛ੾Γग़͍ͨ͠
    ͱߟ͑Δͷ͸ଟ͘ͷ৔߹
    ผͷαʔϏεΛ࡞͍ͬͯΔνʔϜ DB splitting
    ޮ཰తɾ࣮֬ʹίΞυϝΠϯ੾Γग़͠Λ΍Γ਱͛ΔͨΊͷ
    Ϩʔϧ
    ͕ඞཁ
28. ©2018 Wantedly, Inc. Splitting DB on "Rails" How to split DB efficiently with Ruby "magic"
29. ©2018 Wantedly, Inc. ‣ Ͳ͜·Ͱ੾Γग़͔͢
    ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ Splitting DB on "Rails"
30. ©2018 Wantedly, Inc. ‣ Ͳ͜·Ͱ੾Γग़͔͢
    ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ Splitting DB on "Rails"
31. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ 2
    ੾Γग़͍ͨ͠υϝΠϯͷத৺͸ͳʹʁ
32. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ "
    ϢʔβϓϩϑΟʔϧ
    1SPpMF
    NPEFM class Profile < ApplicationRecord # ... end
33. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ 2
    1SPpMF
    ʹؔ࿈Λ࣋ͭ
    NPEFM
    ͸ͲΕʁ
34. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ class Profile < ApplicationRecord has_many :working_histories end class WorkingHistory < ApplicationRecord belongs_to :company end class Company < ApplicationRecord end class Post < ApplicationRecord has_one :profile end
35. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ class Profile < ApplicationRecord has_many :working_histories end class WorkingHistory < ApplicationRecord belongs_to :company end class Company < ApplicationRecord end class Post < ApplicationRecord has_one :profile end ย૝͍
36. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ class Profile < ApplicationRecord has_many :working_histories end class WorkingHistory < ApplicationRecord belongs_to :company end class Company < ApplicationRecord end class Post < ApplicationRecord has_one :profile end ย૝ΘΕ
37. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ class Profile < ApplicationRecord has_many :working_histories end class WorkingHistory < ApplicationRecord belongs_to :company end class Company < ApplicationRecord end class Post < ApplicationRecord has_one :profile end ย૝͍ͷย૝͍
38. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ 2
    1SPpMF
    ʹؔ࿈Λ࣋ͭ
    NPEFM
    ͸ͲΕʁ
    
39. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ # activerecord/lib/active_record/associations.rb in rails/rails def has_many(name, scope = nil, **options, &extension) reflection = Builder::HasMany.build(self, name, scope, options, &extension) Reflection.add_reflection self, name, reflection end
40. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ # activerecord/lib/active_record/associations.rb in rails/rails def has_many(name, scope = nil, **options, &extension) reflection = Builder::HasMany.build(self, name, scope, options, &extension) Reflection.add_reflection self, name, reflection end
41. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ [2] pry(main)> Profile.methods.grep(/reflect/) => [:_reflections, :aggregate_reflections, :aggregate_reflections=, :aggregate_reflections?, :_reflections=, :_reflections?, :reflect_on_aggregation, :reflect_on_all_aggregations, :_reflect_on_association, :reflect_on_all_associations, :clear_reflections_cache, :reflections, :reflect_on_association, :reflect_on_all_autosave_associations]
42. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ [1] pry(main)> Profile.methods.grep(/reflect/) => [:_reflections, :aggregate_reflections, :aggregate_reflections=, :aggregate_reflections?, :_reflections=, :_reflections?, :reflect_on_aggregation, :reflect_on_all_aggregations, :_reflect_on_association, :reflect_on_all_associations, :clear_reflections_cache, :reflections, :reflect_on_association, :reflect_on_all_autosave_associations]
43. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ [2] pry(main)> Profile.reflect_on_all_associations.map(&:table_name).uniq => ["users", # ... "academic_records", "working_histories", # ... "posts", #... ] ย૝͍Ұཡ͕औΕͨʂ
44. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } .map(&:active_record) .map(&:table_name) ย૝ΘΕҰཡ΋ಉ͡Α͏ʹͯ͠औΕΔʂ
45. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ ͋ΔϞσϧʹؔ࿈Λ΋ͭϞσϧ ‣ Areflect_on_all_associationsAΛར༻
     ͋ΔϞσϧ͕ؔ࿈Λ࣋ͭϞσϧΛऔಘͰ͖Δ
     ٯ޲͖΋΍Ε͹ย૝͍ɼย૝ΘΕؚΊ
 ͢΂ͯݟ͔ͭΔʂ from = ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } to = Profile.reflect_on_all_associations (from + to).map(&:table_name)
46. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } .map(&:active_record) .map(&:table_name) .uniq [ "users", "posts", "academic_records", # ... "working_histories", # ... ] # => 18 tables
47. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } .map(&:active_record) .map(&:table_name) .uniq [ "users", "posts", "academic_records", # ... "working_histories", # ... ] # => 18 tables ͜ͷ΁Μ͸ϓϩϑΟʔϧ
    ʢ6*
    తʹ΋ϓϩϑΟʔϧը໘ʹग़ͯ͘Δ৘ใʣ
48. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } .map(&:active_record) .map(&:table_name) .uniq [ "users", "posts", "academic_records", # ... "working_histories", # ... ] # => 18 tables ͜ΕΒ͸ϓϩϑΟʔϧʁ
    ʢQPTUT
    ͸Ϣʔβ౤ߘςʔϒϧʣ
49. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ from = ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } to = Profile.reflect_on_all_associations (from + to) .map(&:table_name) .reject { |n| excluded_table_names.include? n } ผυϝΠϯͬΆ͍΋ͷ͸੾Γग़͠ର৅͔Β֎͢
    ʢࠓճͩͱձࣾ৘ใ΍Ϣʔβ౤ߘ͕֘౰ʣ [ "academic_records", # ... "working_histories", # ... ]
50. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ 2
    1SPpMF
    ʹؔ࿈Λ࣋ͭ
    NPEFM
    ͸ͲΕʁ
    "
    Areflect_on_all_associationsA
    Ͱػցతʹ୳͢
    ผυϝΠϯͬΆ͍΋ͷ͸আ֎
    ࣍ɼ࣍ͷ
    BTTPDJBUJPO
    ͘Β͍·ͰݟͯɼαʔϏεʹৄ͍͠ਓͱ౴͑߹Θͤ͢Δ ˞
    ࣮ࡍʹ࢖ͬͨεΫϦϓτ
    IUUQTHJTUHJUIVCDPNJ[VNJOFCCGBEDCEDEFDDE
51. ©2018 Wantedly, Inc. ‣ Ͳ͜·Ͱ੾Γग़͔͢
    ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ Splitting DB on "Rails"
52. ©2018 Wantedly, Inc. ‣ Ͳ͏΍ͬͯ੾Γग़͔͢
     มߋߦΛ࠷খʹ͢Δ
     मਖ਼ՕॴΛݟಀ͞ͳ͍
53. ©2018 Wantedly, Inc. ‣ Ͳ͏΍ͬͯ੾Γग़͔͢
     มߋߦΛ࠷খʹ͢Δ
     मਖ਼ՕॴΛݟಀ͞ͳ͍
54. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class ApplicationRecord < ActiveRecord::Base self.abstract_class = true end class Profile < ApplicationRecord # ... end class WorkingHistory < ApplicationRecord # ... end
55. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class ProfileDb::Base < ApplicationRecord self.abstract_class = true db_config = YAML::load(ERB.new(IO.read('config/profile_database.yml')).result) establish_connection(db_config[Rails.env]) end class ProfileDb::Profile < ProfileDb::Base # ... end class ProfileDb::WorkingHistory < ProfileDb::Base # ... end "DUJWF3FDPSE
    Ͱ
    ઀ଓઌΛม͑Δ
    Α͋͘Δύλʔϯ
56. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class ProfileDb::Base < ApplicationRecord self.abstract_class = true db_config = YAML::load(ERB.new(IO.read('config/profile_database.yml')).result) establish_connection(db_config[Rails.env]) end class ProfileDb::Profile < ProfileDb::Base # ... end class ProfileDb::WorkingHistory < ProfileDb::Base # ... end ৽͘͠ϕʔεΫϥεΛ࡞ͬͯɼܧঝͤ͞Δ
57. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class ProfileDb::Base < ApplicationRecord self.abstract_class = true db_config = YAML::load(ERB.new(IO.read('config/profile_database.yml')).result) establish_connection(db_config[Rails.env]) end class ProfileDb::Profile < ProfileDb::Base # ... end class ProfileDb::WorkingHistory < ProfileDb::Base # ... end ϞδϡʔϧΛ੾Δʁ
58. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class ProfileRecord < ApplicationRecord self.abstract_class = true db_config = YAML::load(ERB.new(IO.read('config/profile_database.yml')).result) establish_connection(db_config[Rails.env]) end class Profile < ProfileRecord # ... end class WorkingHistory < ProfileRecord # ... end ϞδϡʔϧΛ੾Βͳ͍ʁ
59. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ .PEVMF
    Λ੾Δʁ ‣ ੾Δʁ
     ίʔυతʹ΋ʮҟͳΔυϝΠϯͰ͋Δ͜ͱͷද໌ʯ
     Ahas_manyA
    ͷڍಈͱ͔ؾΛ͚ͭΔඞཁΞϦ
    ‣ ੾Βͳ͍ʁ
     ϕʔεΫϥεม͑Δ͚ͩͳͷͰɼมߋ͸͘͢ͳ͍
     ίʔυ্Ͱ͸ҧ͏
    %#
    Λࢀর͍ͯ͠Δ͜ͱ͕ෆ໌ྎ class ProfileDb::WorkingHistory < ProfileDb::Base # ... end # or class WorkingHistory < ProfileRecord # ... end
60. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ .PEVMF
    Λ੾Δʁ ‣ ੾Δʁ
     ίʔυతʹ΋ʮҟͳΔυϝΠϯͰ͋Δ͜ͱͷද໌ʯ
     Ahas_manyA
    ͷڍಈͱ͔ؾΛ͚ͭΔඞཁΞϦ
    ‣ ࠓճ͸੾Βͳ͍ʂ
     ϕʔεΫϥεม͑Δ͚ͩͳͷͰɼมߋ͸͘͢ͳ͍
     ίʔυ্Ͱ͸ҧ͏
    %#
    Λࢀর͍ͯ͠Δ͜ͱ͕ෆ໌ྎ class ProfileDb::WorkingHistory < ProfileDb::Base # ... end # or class WorkingHistory < ProfileRecord # ... end
61. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class User < ApplicationRecord has_one :profile end class Profile < ProfileRecord has_many :working_histories end class WorkingHistory < ProfileRecord end ͔Μ΃͖ʜʁ
62. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class User < ApplicationRecord has_one :profile end class Profile < ProfileRecord has_many :working_histories end class WorkingHistory < ProfileRecord end ͜Ε͸Կ΋ߟ͑ͣʹಈ͘ͷ͔ʁ
63. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ User.joins(profile: :working_histories).to_a User.preload(profile: :working_histories).to_a User.eager_load(profile: :working_histories).to_a User.includes(profile: :working_histories).to_a User.includes(:profile).where(profile: { location: 'Tokyo' }).to_a ΫΠζ
    ಈ͘ͷ͸ͲΕͰ͠ΐ͏ʢprofiles
    ͱ
    working_histories
    ͕ҧ͏%#ʣ
64. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ User.joins(profile: :working_histories).to_a User.preload(profile: :working_histories).to_a User.eager_load(profile: :working_histories).to_a User.includes(profile: :working_histories).to_a User.includes(:profile).where(profile: { location: 'Tokyo' }).to_a %#
    Λ·͍ͨͩ
    +0*/
    ͕૸Δͱμϝ
    1SFMPBEJOH
    ʹؔͯ͠͸
    ࢀর͢Δ
    %#
    ͕ҧ͍ͬͯͯ΋ਖ਼͘͠ಈ͘ ˞
    ݕূϦϙδτϦ
    IUUQTHJUIVCDPNBXBLJBEPVCMFEC
    
    3BJMT
    
    Ͱಈ࡞֬ೝ
65. ©2018 Wantedly, Inc. User.joins(profile: :working_histories).to_a User.preload(profile: :working_histories).to_a User.eager_load(profile: :working_histories).to_a User.includes(profile: :working_histories).to_a User.includes(:profile).where(profile: { location: 'Tokyo' }).to_a DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ %#
    Λ·͍ͨͩ
    +0*/
    ͕૸Δͱμϝ
    1SFMPBEJOH
    ʹؔͯ͠͸
    ࢀর͢Δ
    %#
    ͕ҧ͍ͬͯͯ΋ਖ਼͘͠ಈ͘ ٯʹݴ͏ͱɼ+0*/
    ͑͞ճආͰ͖Ε͹ͪΌΜͱಈ࡞͢Δʂ
66. ©2018 Wantedly, Inc. DB splitting ·ͱΊ
    
    ࠷খͷมߋͰࡁ·ͨ͢Ίʹ ࢀর͢Δ
    %#
    Λม͑Δʹ͸ʜ
    ϕʔεΫϥε͚ͩมߋ͢Ε͹
    0, ϕʔεΫϥεͷมߋ͚ͩͰਖ਼͘͠ಈ࡞ͤ͞ΔͨΊʹ͸ʜ
    %#
    Λ·͙ͨ
    +0*/
    ͚࣮ͩ֬ʹճආ͢Δʂ
67. ©2018 Wantedly, Inc. ‣ Ͳ͏΍ͬͯ੾Γग़͔͢
     มߋߦΛ࠷খʹ͢Δ
     मਖ਼ՕॴΛݟಀ͞ͳ͍
68. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ࢀর͢Δ
    %#
    Λม͑Δʹ͸ʜ
    ϕʔεΫϥε͚ͩมߋ͢Ε͹
    0, ϕʔεΫϥεͷมߋ͚ͩͰਖ਼͘͠ಈ࡞ͤ͞ΔͨΊʹ͸ʜ
    %#
    Λ·͙ͨ
    +0*/
    ͚࣮ͩ֬ʹճආ͢Δʂ
69. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ࢀর͢Δ
    %#
    Λม͑Δʹ͸ʜ
    ϕʔεΫϥε͚ͩมߋ͢Ε͹
    0, ϕʔεΫϥεͷมߋ͚ͩͰਖ਼͘͠ಈ࡞ͤ͞ΔͨΊʹ͸ʜ
    %#
    Λ·͙ͨ
    +0*/
    ͚࣮ͩ֬ʹճආ͢Δʂ
70. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ Ͳ͏΍ͬͨΒ
    %#
    ·ͨ͗
    +0*/
    Λ୳ͯ͠ࢭΊΒΕΔͷ͔ʁ
    HSFQ
    ͸ݫ͍͠
    
71. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ΞάϨογϒʹϩάऩू ඞࡴ
    PQFO
    DMBTT ‣ "DUJWF3FDPSE
    ʹ͸ɼΫΤϦ࣮ߦ࣌ʹઈରݺ͹ΕΔϝιου͕ଘࡏ͢Δ
     ͦͷλΠϛϯάͰ
    +0*/
    ͞Εͦ͏ͳΒϩάΛ࢒͢
     DPOUSPMMFS
    BDUJPO
    DBMMFS
    ؔ࿈͍ͯ͠Δςʔϒϧ
    FUD
    ‣ Ͳͷςʔϒϧ͕
    KPJOT
    
    JODMVEFT
    FBHFS@MPBE
    ͞Ε͔ͨ͸ΫΤϦϏϧμ͕஌͍ͬͯΔ
    
72. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/
    MPHHFS
73. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/
    MPHHFS ݺͼग़͠ݩΞϓϦέʔγϣϯίʔυΛ୳͢ʢOPU
    HFNʣ
74. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/
    MPHHFS KPJOT
    ΍
    FBHFS@MPBE
    ͷҾ਺Ϧετ
75. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/
    MPHHFS def flatten_recursively(arr) arr.flat_map do |i| case i when Hash then hash_to_array(i) when Array then flatten_recursively(i) when Arel::Nodes::Node then i.to_sql when ActiveRecord::Associations::JoinDependency then join_dependency_to_table_names(i) else i.to_s end end end def hash_to_array(h) h.keys + (h.values.flat_map { |v| case v when Hash then hash_to_array(v) when Array then flatten_recursively(v) when Arel::Nodes::Node then v.to_sql when ActiveRecord::Associations::JoinDependency then join_dependency_to_table_names(v) else v.to_s end }) end def join_dependency_to_table_names(jd) jd.aliases.instance_variable_get(:@tables).map { |a| a.table.table_name } end flatten_recursively
    IVNBO
    SFBEBCMF
    ͳ
    TUSJOH
    ʹ͢ΔͨΊͷ
    IFMQFS
76. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/
    MPHHFS 6OJDPSO
    ͩͬͨͷͰࡶʹάϩʔόϧม਺ʹ
    ʢDBMMFS
    ͕Θ͔Ε͹֓Ͷ໰୊ͳ͍ʣ
77. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/
    MPHHFS 5SFBTVSF
    %BUB
    ʹه࿥
78. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ select caller_line, collect_set(table) as base_tables, collect_set(t) as joined_tables_or_queries, collect_set(class) as classes, collect_set(request) as requests, collect_set(caller) as callers from ( select *, substring_index(caller, ':', 2) as caller_line, concat(request_controller, '#', request_action) as request from join_queries lateral view explode(joins) tmp as t where td_time_range(time, '2017-11-08 00:00:00', '2017-11-15 00:00:00') and ( ( table not in ('profiles', ..., 'working_histories', ...) and ( t = 'profile' or instr(t, 'profiles') != 0 -- ... or t = 'working_history' or instr(t, 'working_histories') != 0 -- ... ) ) or -- ... ) order by time desc ) queries group by caller_line ‣ ͬ͟ͱूܭ͢Δ
     DBMMFS
    Ͱ
    HSPVQ
    CZ
     +0*/
    ઌͱݩͷͲͪΒ͔͚ͩʹ෼ׂର৅͕ೖ͍ͬͯΔΫΤϦ
    ‣ ଟগࡶͰ΋0,
     ޡݕग़͸࠷ѱ໰୊ͳ͍ʢࣗ෼ͷνΣοΫର৅͕૿͑Δ͚ͩʣ
     ࿙Ε͕ແ͍Α͏ʹؾΛ͚ͭͯ
    42-
    ॻ͘
79. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ‣ ूܭ݁ՌΛ
    $47
    ग़ྗ
    ˠ
    BXL
    ͱ͔ͰνΣοΫϘοΫεʹ
    ‣ ͋ͱ͸ػցతʹ௵͍ͯ͘͠
     includes
    ͸ preload
     joins
    eager_load
     FH
    ASELECT idA
    ͱ
    AWHERE id in (?, ?, ...)A
    ʹΘ͚Δ
    ‣ ͍͍ͩͨ͸ࠜੑͱ
    3VCZ
    ྗͰ͚ͩղܾͰ͖Δ
     ͋ͱɼ΄Μͷগ͠ͷ
    42-
    ྗ
80. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ײಈͷॠؒ
81. ©2018 Wantedly, Inc. DB splitting ·ͱΊ
    
    Ͳ͏΍ͬͯ੾Γग़͔͢ ࢀর͢Δ
    %#
    Λม͑Δʹ͸ʜ
    ϕʔεΫϥε͚ͩมߋ͢Ε͹
    0,ʂ ϕʔεΫϥεͷมߋ͚ͩͰਖ਼͘͠ಈ࡞ͤ͞ΔͨΊʹ͸ʜ
    %#
    Λ·͙ͨ
    +0*/
    ͚࣮ͩ֬ʹճආ͢Δʂ %#
    Λ·͙ͨ
    +0*/
    Λճආ͢ΔͨΊʹ͸ʜ
    ΫΤϦͷϩάΛऔͬͯɼ͋Ϳͳ͍
    +0*/
    Λ୳ͯ͠௵͢ʂ
82. ©2018 Wantedly, Inc. DB splitting ·ͱΊ
    
    Ͳ͏΍ͬͯ੾Γग़͔͢ ࢀর͢Δ
    %#
    Λม͑Δʹ͸ʜ
    ϕʔεΫϥε͚ͩมߋ͢Ε͹
    0,ʂ ϕʔεΫϥεͷมߋ͚ͩͰਖ਼͘͠ಈ࡞ͤ͞ΔͨΊʹ͸ʜ
    %#
    Λ·͙ͨ
    +0*/
    ͚࣮ͩ֬ʹճආ͢Δʂ %#
    Λ·͙ͨ
    +0*/
    Λճආ͢ΔͨΊʹ͸ʜ
    ΫΤϦͷϩάΛऔͬͯɼ͋Ϳͳ͍
    +0*/
    Λ୳ͯ͠௵͢ʂ 3VCZ
    ͷྗͰ
    γεςϚνοΫʹղܾͰ͖ͨ
83. ©2018 Wantedly, Inc. Migration process
84. ©2018 Wantedly, Inc. Migration process  ϕʔεΫϥεΛ࡞੒ɾܧঝ
 PROFILE_DATABASE_URL
    ͸
    .BJO
    %#
    ʹ޲͚ͨ
     $*
    ্Ͱ͸
    PROFILE_DATABASE_URL
    Λҧ͏
    %#
    ʹ
 ޲͚ͯςετ௨͢
     ͖ͬ͞ͷνΣοΫϘοΫεΛຒΊ͍ͯ͘ ProfileRecord ApplicationRecord MainDB ProfileRecord ApplicationRecord MainDB Profile DB Local / CI QA / Prod
85. ©2018 Wantedly, Inc. Migration process  ςετ͕ͱ͓Δ
     2"
    ੾Γସ͑
    J ԼهͷҠߦϓϩηεͷϦϋʔαϧ
    JJ όάνΣοΫ
     1SPE
    ੾Γସ͑
    J શαʔϏεΛϝϯςφϯεϞʔυʹ
    JJ .BJO
    %#
    ͷϦʔυϨϓϦΧΛঢ֨
    JJJ 1SPpMF3FDPSE
    ͷ޲͖Λม͑Δ
    JW αʔϏεΠϯ ProfileRecord ApplicationRecord MainDB ProfileRecord ApplicationRecord MainDB Profile DB Local / CI QA / Prod
86. ©2018 Wantedly, Inc. Migration process  ςετ͕ͱ͓Δ
     2"
    ੾Γସ͑
    J ԼهͷҠߦϓϩηεͷϦϋʔαϧ
    JJ όάνΣοΫ
     1SPE
    ੾Γସ͑
    J શαʔϏεΛϝϯςφϯεϞʔυʹ
    JJ .BJO
    %#
    ͷϦʔυϨϓϦΧΛঢ֨
    JJJ 1SPpMF3FDPSE
    ͷ޲͖Λม͑Δ
    JW αʔϏεΠϯ ProfileRecord ApplicationRecord MainDB ProfileRecord ApplicationRecord MainDB Profile DB Local / CI QA / Prod ϓϩϑΟʔϧ৘ใͷσʔλҠߦ
    ϦεΫ΋ίετ΋ߴ͍ͷͰແఀࢭҠߦ͸͠ͳ͍
87. ©2018 Wantedly, Inc. Conclusion
88. ©2018 Wantedly, Inc. Future works ŘŵŠŠ Monolithic Rails app Main DB Profile DB Read / Write Read / Write 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) GET on Kubernetes cluster
89. ©2018 Wantedly, Inc. Future works Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Profile DB Read / Write Read / Write Read Update
90. ©2018 Wantedly, Inc. Future works Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Profile DB Read / Write Read / Write Read Update ॻ͖ࠐΈ͸͢΂ͯ
    QSPpMF
    TFSWJDF
    ʹΑͤΔ
    ʢσʔλͷ੔߹ੑΛ࣮֬ʹʣ
91. ©2018 Wantedly, Inc. Future works Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Profile DB Read / Write Read / Write Read Update ಡΈࠐΈʹؔͯ͠͸ڐ༰͢Δ
    ʢ"DUJWF3FDPSE
    Λണ͕͢ίετ͕େ͖͗͢Δʣ
92. Ͳ͜·Ͱ੾Γग़͔͢
    ʢத৺ͱͳΔςʔϒϧͷؔ࿈ʣ
    
    ʢ໌Β͔ʹผυϝΠϯͳ΋ͷʣ
    
    ࠷୹ڑ཭Λݟ͚ͭग़͢
    ϕʔεΫϥεͷมߋ
    
    %#
    ·ͨ͗
    +0*/
    ͷ๷ࢭ
    
    มߋඞཁՕॴͷߜࠐΈ
    ద੾ʹύονΛ౰ͯͯɼద੾ʹϩάΛऔΓɼద੾ʹղੳ Splitting DB on "Rails"