Rails World 2023 - Composite Primary Keys in Rails 7.1

Transcript

1. Nikita Vasilevsky Implementing Native Composite Primary Key Support in Rails 7.1 RailsWorld 2023 nikita@hey.com
2. About me
3. • Ruby on Rails developer • Rails triage team • Open source contributor
4. Come to our booth! Ruby & Rails Infrastructure team
5. • Fundamentals of composite primary keys • Composite Primary Keys in Multi-Tenant Applications • Similarities with Tenant-Based Sharding • Active Record 7.1 Support for Composite Primary Keys Agenda
6. Fundamentals of Composite Primary Keys
7. • De f inition: Understand what composite primary keys are. • Examples: Real-world use cases of composite primary keys. • Considerations: When and why to use them in your application. Fundamentals of Composite Primary Keys
8. Rails should not be the reason for you to use composite primary keys.
9. De f inition of Composite Primary Keys student_courses student_id course_id semester status enrollment_date completion_date Composite primary key
10. Models with composite identi f iers (make, model, year) (author, title)
11. Should I use composite primary keys Just because I can?
12. The best candidate for using a composite primary key is the join table.
13. blog_posts id title body author_id
14. blog_posts id title body author_id tags id name
15. blog_posts id title body author_id tags id name blog_posts_tags tag_id blog_post_id
16. blog_posts id title body author_id tags id name blog_posts_tags tag_id blog_post_id
17. blog_posts id title body author_id tags id name blog_posts_tags tag_id blog_post_id PRIMARY KEY (`blog_post_id`, `tag_id`) PRIMARY KEY (`tag_id`, `blog_post_id`) OR
18. • Join tables are the most common real-world use case for a composite primary key, e ff ectively representing many-to-many relationships. • Not all concepts are suitable for composite primary keys in a relational database. • Use of composite primary keys should be driven by database schema design, not just Rails' support. Recap
19. Composite Primary Keys in Multi-Tenant Applications
20. • Shopify monolith as a multi tenant application • Impact on Performance: How altering the primary key of a table to a composite primary key can improve performance of certain operations. • Trade-o ff s involved in using composite primary keys. Composite Primary Keys in Multi-Tenant Applications
21. Rails convention
22. 1 2 3 4 6 7 5
23. 1 2 3 4 6 7 5 Shop 1 Shop 2 Shop 3 Shop 4
24. 1 3 2 4 6 7 5 Shop 1 Shop 2 Shop 3 Shop 4
25. 1 3 2 4 6 7 5 Shop 1 Shop 2 Shop 3 Shop 4
26. 3 2 4 6 1 7 5 Shop 1 Shop 2 Shop 3 Shop 4
27. 1 3 2 4 6 7 5 Shop 1 Shop 2 Shop 3 Shop 4
28. 3 2 4 6 1 7 5 Shop 1 Shop 2 Shop 3 Shop 4
30. Author A Author B Author C Author D
31. Author A Author B Author C Author D
32. Author A Author B Author C Author D
33. Author A Author B Author C Author D
34. Author A Author B Author C Author D
35. Author A Author B Author C Author D
36. Author A Author B Author C Author D
37. Author A Author B Author C Author D Author E
38. Author A Author B Author C Author D Author E
39. Most common queries improved by 5X Improvement in elapsed query time
40. 80% 60% Slow query logs e ff ects: Reduction in slow queries Reduction in the overall time taken by slow queries 500X 10X Improvement for some queries with low volume Improvement for remaining slow queries
41. However 10X performance degradation on inserts
42. • Database tables in multi-tenant applications can often bene f it from the use of composite primary keys by including the tenant key in the primary key • Impact on Performance: Altering the primary key of a table to a composite primary key can enhance the speed of bulk reads and updates. However, this comes at the cost of a slower insertion rate. • Consideration for Insertion Speed: For applications where the speed of data insertion is essential, using composite primary keys might not be the best option. Recap
43. Similarities with Tenant- Based Sharding
44. Application Tenant 1 Tenant 3 Tenant 5 Tenant 2 Tenant 4 Tenant 6
45. Tenant based sharding designs
46. Tenant based sharding architecture Application Shard 1 Shard 2 Sharding solution Application Shard 1 Shard 2 Sharding solution
47. Tenant based sharding architecture Application Shard 1 Shard 2 Sharding solution
48. Tenant based sharding architecture Application Shard 1 Shard 2 Sharding solution select … where tenant_key=“A” and id=1
49. Same SQL requirements
50. Tenant based sharding architecture Application Shard 1 Shard 2 Sharding solution select … where tenant_key=“A” and id=1
51. Tenant based sharding architecture Application Shard 1 Shard 2 Vitess select … where tenant_key=“A” and id=1
52. • Shared SQL Expectation: Models using composite primary keys and tenant-sharded models both typically include multiple columns in queries. • Implementing composite primary keys can be a foundational, but not necessary, step towards a full sharding strategy. Recap
53. Active Record Support for Composite Primary Keys
54. • How to de f ine composite primary keys in Rails models. • How to work with a composite primary key model. • How to de f ine a virtual primary key. • Limitations: What not to do when de f ining composite primary keys. Active Record Support for Composite Primary Keys
55. A composite primary key model create_table :travel_routes, primary_key: [:origin, :destination] do |t| t.string :origin, null: false t.string :destination, null: false t.text :description t.timestamps end class TravelRoute < ApplicationRecord end
56. TravelRoute.primary_key # => ["origin", "destination"] route = TravelRoute.create(origin: "A", destination: "B") route.id # => ["A", "B"] TravelRoute.find(["A", "B"]) route.reload # => SELECT "travel_routes".* FROM "travel_routes" WHERE "travel_routes"."origin" = ‘A' AND "travel_routes"."destination" = 'B' LIMIT 1
57. route.update(description: "from A to B") # => UPDATE "travel_routes" SET "description" = 'from A to B’ WHERE "travel_routes"."origin" = 'A' AND "travel_routes"."destination" = 'B' route.delete # => DELETE FROM "travel_routes" WHERE "travel_routes"."origin" = 'A' AND “travel_routes"."destination" = ‘B'
58. A special composite primary key case comments id NOT NULL blog_id NOT NULL author body Composite primary key
59. A special composite primary key case comment = Comment.create( blog_id: 123, author: "Nikita", body: "I really enjoyed this post!" ) #=> #<Comment:0x00007f41fb44fdd8 id: 1, blog_id: 123> comment.id # => [123, 1] blog_id, id = comment.id
60. Rails treats id concept as an identi f ier and not as an id column accessor.
61. If possible, avoid having column named id as part of the composite primary key
62. Rails 7.1 introduces new id column attribute alias - id_value
63. A special composite primary key case comment = Comment.create( blog_id: 123, author: "Nikita", body: "I really enjoyed this post!" ) #=> #<Comment:0x00007f41fb44fdd8 id: 1, blog_id: 123> comment.id # => [123, 1] blog_id, id = comment.id comment.id_value # => 1
64. Bulk loading of records with a composite primary key
65. Rails 7.1 extends ActiveRecord::QueryMethods.where() method to support “query by tuple” syntax - where(keys => values)
66. clause = { [:origin, :destination] => [["A", "C"],["B", "C"],["B", "D"]] } TravelRoute.where(clause).to_a [ #<TravelRoute:0x00007f8b274bfa00, origin: "A", destination: "C">, #<TravelRoute:0x00007f8b274bfa00, origin: "B", destination: "C">, #<TravelRoute:0x00007f8b274bfa00, origin: "B", destination: "D"> ]
67. Associating a model with a composite primary key
68. Rails 7.1 adds query_constraints: option to associations to serve as a composite foreign key
69. class TravelRouteReview < ApplicationRecord belongs_to :travel_route, query_constraints: [:route_origin, :route_destination] end create_table :travel_route_reviews do |t| t.string :route_origin, null: false t.string :route_destination, null: false t.text :body t.timestamps end
70. route = TravelRoute.create(origin: "A", destination: "B") review = TravelRouteReview.create( travel_route: route, body: "This is my favorite route!” ) review.travel_route # => SELECT "travel_routes".* FROM "travel_routes" WHERE "travel_routes"."origin" = ‘A' AND "travel_routes"."destination" = 'B' LIMIT 1
71. A tenant key shared model create_table :orders do |t| t.bigint :shop_id, null: false t.decimal :total_price t.string :status t.text :note t.timestamps end class Order < ApplicationRecord has_many :line_items end create_table :line_items do |t| t.bigint :shop_id, null: false t.bigint :order_id, null: false t.bigint :variant_id t.decimal :price t.text :properties t.timestamps end class LineItem < ApplicationRecord belongs_to :order end
72. Rails 7.1 introduces “virtual primary key” concept - query_constraints
73. A tenant key shared model class Order < ApplicationRecord query_constraints :shop_id, :id has_many :line_items end class LineItem < ApplicationRecord query_constraints :shop_id, :id belongs_to :order end
74. A tenant key sharded model order = Order.create(shop_id: 123) order.line_items.create( [ { product_name: "Green Pants", price: 10 }, { product_name: "Green T-Shirt", price: 5 } ] ) order.line_items.to_a # => SELECT "line_items".* FROM "line_items" WHERE "line_items"."shop_id" = 123 AND "line_items"."order_id" = 1 LineItem.first.order # => SELECT "orders".* FROM "orders" WHERE "orders"."shop_id" = 123 AND "orders"."id" = 1
75. A tenant key shared model class Order < ApplicationRecord query_constraints :shop_id, :id has_many :line_items end Primary Key Tenant (sharding) key
76. • Active Record 7.1 Support: Active Record 7.1 provides native support for composite primary keys. • Usage of 'id' Column: Preferably, the column named id should be used only for single-column primary keys. • Virtual Primary Key: The query_constraints option can be used as a "virtual primary key" to mimic primary key behavior without altering the database schema. This can be particularly useful in certain tenant-based sharding implementations. Recap
77. Conclusion
78. Use composite primary keys based on your database needs. Ensure it's suitable, and Rails will support it.
79. • When having a separate single-column identi f ier is unnecessary and excessive. Example: Join tables • Multi tenant applications with a lot of bulk reads scoped per single tenant. When to consider using composite primary keys
80. • Multi tenant applications to include the tenant_key column in SQL queries • To use columns other than the primary_key in record.update, record.reload, record.delete When to consider using query_constraints
81. Bugs/Issues https://github.com/rails/rails/issues Discussions https://discord.com/invite/d8N68BCw49 Proposals https://discuss.rubyonrails.org/
82. Paarth Adrianna Dima Eileen Gannon Matthew Noah
83. Thank you! Nikita Vasilevsky nikita@hey.com