Share
Embed
Transcript
83 slides
-
• Ruby on Rails developer • Rails triage team •
Open source contributor -
• 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 -
• 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 -
Rails should not be the reason for you to use
composite primary keys. -
De f inition of Composite Primary Keys student_courses student_id course_id
semester status enrollment_date completion_date Composite primary key -
The best candidate for using a composite primary key is
the join table. -
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 -
• 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 -
• 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 -
1 2 3 4 6 7 5 Shop 1 Shop
2 Shop 3 Shop 4 -
1 3 2 4 6 7 5 Shop 1 Shop
2 Shop 3 Shop 4 -
1 3 2 4 6 7 5 Shop 1 Shop
2 Shop 3 Shop 4 -
3 2 4 6 1 7 5 Shop 1 Shop
2 Shop 3 Shop 4 -
1 3 2 4 6 7 5 Shop 1 Shop
2 Shop 3 Shop 4 -
3 2 4 6 1 7 5 Shop 1 Shop
2 Shop 3 Shop 4 -
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 -
• 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 -
Tenant based sharding architecture Application Shard 1 Shard 2 Sharding
solution Application Shard 1 Shard 2 Sharding solution -
Tenant based sharding architecture Application Shard 1 Shard 2 Sharding
solution select … where tenant_key=“A” and id=1 -
Tenant based sharding architecture Application Shard 1 Shard 2 Sharding
solution select … where tenant_key=“A” and id=1 -
Tenant based sharding architecture Application Shard 1 Shard 2 Vitess
select … where tenant_key=“A” and id=1 -
• 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 -
• 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 -
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 -
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 -
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' -
A special composite primary key case comments id NOT NULL
blog_id NOT NULL author body Composite primary key -
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 -
Rails treats id concept as an identi f ier and
not as an id column accessor. -
If possible, avoid having column named id as part of
the composite primary key -
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 -
Rails 7.1 extends ActiveRecord::QueryMethods.where() method to support “query by tuple”
syntax - where(keys => values) -
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"> ] -
Rails 7.1 adds query_constraints: option to associations to serve as
a composite foreign key -
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 -
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 -
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 -
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 -
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 -
A tenant key shared model class Order < ApplicationRecord query_constraints
:shop_id, :id has_many :line_items end Primary Key Tenant (sharding) key -
• 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 -
Use composite primary keys based on your database needs. Ensure
it's suitable, and Rails will support it. -
• 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 -
• 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