Slide 1
Slide 1 text
Nikita Vasilevsky
Implementing Native Composite
Primary Key Support in Rails 7.1
RailsWorld 2023
[email protected]
Slide 2
Slide 2 text
About me
Slide 3
Slide 3 text
• Ruby on Rails developer
• Rails triage team
• Open source contributor
Slide 4
Slide 4 text
Come to our booth!
Ruby & Rails Infrastructure team
Slide 5
Slide 5 text
• 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
Slide 6
Slide 6 text
Fundamentals of Composite
Primary Keys
Slide 7
Slide 7 text
• 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
Slide 8
Slide 8 text
Rails should not be the reason
for you to use composite
primary keys.
Slide 9
Slide 9 text
De
f
inition of Composite Primary Keys
student_courses
student_id
course_id
semester
status
enrollment_date
completion_date
Composite primary key
Slide 10
Slide 10 text
Models with composite identi
f
iers
(make, model, year)
(author, title)
Slide 11
Slide 11 text
Should I use composite primary keys
Just because I can?
Slide 12
Slide 12 text
The best candidate for using a
composite primary key is the join
table.
Slide 13
Slide 13 text
blog_posts
id
title
body
author_id
Slide 14
Slide 14 text
blog_posts
id
title
body
author_id
tags
id
name
Slide 15
Slide 15 text
blog_posts
id
title
body
author_id
tags
id
name
blog_posts_tags
tag_id
blog_post_id
Slide 16
Slide 16 text
blog_posts
id
title
body
author_id
tags
id
name
blog_posts_tags
tag_id
blog_post_id
Slide 17
Slide 17 text
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
Slide 18
Slide 18 text
• 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
Slide 19
Slide 19 text
Composite Primary Keys in
Multi-Tenant Applications
Slide 20
Slide 20 text
• 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
Slide 21
Slide 21 text
Rails convention
Slide 22
Slide 22 text
1 2
3
4 6 7
5
Slide 23
Slide 23 text
1 2
3
4 6 7
5
Shop 1
Shop 2
Shop 3
Shop 4
Slide 24
Slide 24 text
1 3
2
4 6 7
5
Shop 1
Shop 2
Shop 3
Shop 4
Slide 25
Slide 25 text
1 3
2
4 6 7
5
Shop 1
Shop 2
Shop 3
Shop 4
Slide 26
Slide 26 text
3
2
4 6
1 7
5
Shop 1
Shop 2
Shop 3
Shop 4
Slide 27
Slide 27 text
1 3
2
4 6 7
5
Shop 1
Shop 2
Shop 3
Shop 4
Slide 28
Slide 28 text
3
2
4 6 1 7
5
Shop 1
Shop 2
Shop 3
Shop 4
Slide 29
Slide 29 text
No content
Slide 30
Slide 30 text
Author A
Author B
Author C
Author D
Slide 31
Slide 31 text
Author A
Author B
Author C
Author D
Slide 32
Slide 32 text
Author A
Author B
Author C
Author D
Slide 33
Slide 33 text
Author A
Author B
Author C
Author D
Slide 34
Slide 34 text
Author A
Author B
Author C
Author D
Slide 35
Slide 35 text
Author A
Author B
Author C
Author D
Slide 36
Slide 36 text
Author A
Author B
Author C
Author D
Slide 37
Slide 37 text
Author A
Author B
Author C
Author D
Author E
Slide 38
Slide 38 text
Author A
Author B
Author C
Author D
Author E
Slide 39
Slide 39 text
Most common queries improved by
5X
Improvement in elapsed query time
Slide 40
Slide 40 text
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
Slide 41
Slide 41 text
However
10X
performance degradation on inserts
Slide 42
Slide 42 text
• 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
Slide 43
Slide 43 text
Similarities with Tenant-
Based Sharding
Slide 44
Slide 44 text
Application
Tenant 1
Tenant 3
Tenant 5
Tenant 2
Tenant 4
Tenant 6
Slide 45
Slide 45 text
Tenant based sharding designs
Slide 46
Slide 46 text
Tenant based sharding architecture
Application
Shard 1 Shard 2
Sharding solution
Application
Shard 1 Shard 2
Sharding solution
Slide 47
Slide 47 text
Tenant based sharding architecture
Application
Shard 1 Shard 2
Sharding solution
Slide 48
Slide 48 text
Tenant based sharding architecture
Application
Shard 1 Shard 2
Sharding solution
select … where tenant_key=“A” and id=1
Slide 49
Slide 49 text
Same SQL requirements
Slide 50
Slide 50 text
Tenant based sharding architecture
Application
Shard 1 Shard 2
Sharding solution
select … where tenant_key=“A” and id=1
Slide 51
Slide 51 text
Tenant based sharding architecture
Application
Shard 1 Shard 2
Vitess
select … where tenant_key=“A” and id=1
Slide 52
Slide 52 text
• 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
Slide 53
Slide 53 text
Active Record Support for
Composite Primary Keys
Slide 54
Slide 54 text
• 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
Slide 55
Slide 55 text
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
Slide 56
Slide 56 text
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
Slide 57
Slide 57 text
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'
Slide 58
Slide 58 text
A special composite primary key case
comments
id NOT NULL
blog_id NOT NULL
author
body
Composite primary key
Slide 59
Slide 59 text
A special composite primary key case
comment = Comment.create(
blog_id: 123,
author: "Nikita",
body: "I really enjoyed this post!"
)
#=> #
comment.id # => [123, 1]
blog_id, id = comment.id
Slide 60
Slide 60 text
Rails treats id concept as an
identi
f
ier and not as an id column
accessor.
Slide 61
Slide 61 text
If possible, avoid having column
named id as part of the
composite primary key
Slide 62
Slide 62 text
Rails 7.1 introduces new id
column attribute alias - id_value
Slide 63
Slide 63 text
A special composite primary key case
comment = Comment.create(
blog_id: 123,
author: "Nikita",
body: "I really enjoyed this post!"
)
#=> #
comment.id # => [123, 1]
blog_id, id = comment.id
comment.id_value # => 1
Slide 64
Slide 64 text
Bulk loading of records
with a composite primary key
Slide 65
Slide 65 text
Rails 7.1 extends
ActiveRecord::QueryMethods.where()
method to support “query by tuple” syntax -
where(keys => values)
Slide 66
Slide 66 text
clause = { [:origin, :destination] => [["A", "C"],["B", "C"],["B", "D"]] }
TravelRoute.where(clause).to_a
[
#,
#,
#
]
Slide 67
Slide 67 text
Associating a model
with a composite primary key
Slide 68
Slide 68 text
Rails 7.1 adds query_constraints:
option to associations to serve as
a composite foreign key
Slide 69
Slide 69 text
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
Slide 70
Slide 70 text
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
Slide 71
Slide 71 text
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
Slide 72
Slide 72 text
Rails 7.1 introduces “virtual
primary key” concept -
query_constraints
Slide 73
Slide 73 text
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
Slide 74
Slide 74 text
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
Slide 75
Slide 75 text
A tenant key shared model
class Order < ApplicationRecord
query_constraints :shop_id, :id
has_many :line_items
end
Primary Key
Tenant (sharding) key
Slide 76
Slide 76 text
• 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
Slide 77
Slide 77 text
Conclusion
Slide 78
Slide 78 text
Use composite primary keys
based on your database needs.
Ensure it's suitable, and Rails will
support it.
Slide 79
Slide 79 text
• 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
Slide 80
Slide 80 text
• 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
Slide 81
Slide 81 text
Bugs/Issues https://github.com/rails/rails/issues
Discussions https://discord.com/invite/d8N68BCw49
Proposals https://discuss.rubyonrails.org/
Slide 82
Slide 82 text
Paarth
Adrianna Dima Eileen Gannon
Matthew
Noah