Scaling GO-FOOD - Dealing with Increasing Records in Ruby on Rails and PostgreSQL

My talk for RubyConf Indonesia 2017

Abstract:
Ruby on Rails' ActiveRecord provides query generations which can help programmer delivers faster. But when it needs to deal with separated data sources, it can cause some problems. Hear our story on scaling Order Management System from biggest food delivery service in ASEAN with RoR and PostgreSQL.

Transcript

1. Scaling GO-FOOD Dealing with Increasing Records in Ruby on Rails and PostgreSQL
2. Historical Context
3. Content Management System Order Management System
4. Content Management System Order Management System 6 services 3 services
5. Content Management System Order Management System 6 services 3 services, more will come
7. ~ 6 products > 100 tables Numerous features
8. ~ 6 products > 100 tables Numerous features >80k rpm at normal time
10. Partially, in a very small #
12. Problems
14. x ~2x or 3x
15. - Get orders by customer id - Get orders by restaurant id - Get order by id/order number
16. Table Partitioning
17. Table Partitioning apart from archival
18. Table Partitioning using pg partman
19. … …
20. … … Partition key: booking timestamp (monthly/weekly/yearly)
21. … … Partition key: booking timestamp (monthly/weekly/yearly) Impossible to use normal ids as partition key
22. - Get orders by customer id select * from orders where c_id = x - Get orders by restaurant id select * from orders where r_id = y - Get order by id/order number select * from orders where id = z
24. - Get orders by customer id select * from orders where c_id = x - Get orders by restaurant id select * from orders where r_id = y - Get order by id/order number select * from orders where id = z - Get orders by customer id select * from orders where c_id = x and ordered_at between … - Get orders by restaurant id select * from orders where r_id = y and ordered_at between … - Get order by id/order number select * from orders where id = z and ordered_at between …
26. class Query::OrdersWithMenuItem def results Order .includes(:menu_items) .order("ordered_at DESC") end end
27. class Query::OrdersWithMenuItem def results Order .includes(:menu_items) .order("ordered_at DESC") end end class Query::OrdersWithMenuItem def initialize(start_time:, end_time:) @start_time = start_time @end_time = end_time end def results Order.where(ordered_at: @start_time..@end_time) .includes(:menu_items) .where(menu_items: { ordered_at: @start_time..@end_time }) .where(‘menu_items.f_key = orders.p_key’) .order(ordered_at: :desc) end end unsafe safe
28. class Query::OrdersWithMenuItem def results Order .includes(:menu_items) .order("ordered_at DESC") end end class Query::OrdersWithMenuItem def initialize(start_time:, end_time:) @start_time = start_time @end_time = end_time end def results Order.where(ordered_at: @start_time..@end_time) .includes(:menu_items) .where(menu_items: { ordered_at: @start_time..@end_time }) .where(‘menu_items.f_key = orders.p_key’) .order(ordered_at: :desc) end end unsafe safe
29. What about these? Order.find_by(attribute: value) order_instance.save order_instance.update
30. What about these? Order.find_by(attribute: value) order_instance.save order_instance.update unsafe
31. module QueryWithPartitionKey def self.included(base) base.extend(ClassMethods) end module ClassMethods include OrderRefGeneration def lookup_by_order_ref(order_ref:, condition:) where_clause = generate_clause(order_ref, condition) self.where(where_clause).first end private def generate_clause(order_ref, condition) condition.merge( { ordered_at: determine_ordered_at_range(order_ref) } ) end def update_exception(condition, params) UpdateException.new("Unable to update #{self} by condition #{condition} with params: #{params}") end def determine_ordered_at_range(order_ref) ordered_at_date = get_ordered_at(order_ref) (ordered_at_date.beginning_of_day..ordered_at_date.end_of_day) end end end
32. module QueryWithPartitionKey def self.included(base) base.extend(ClassMethods) end module ClassMethods include OrderRefGeneration def lookup_by_order_ref(order_ref:, condition:) where_clause = generate_clause(order_ref, condition) self.where(where_clause).first end private def generate_clause(order_ref, condition) condition.merge( { ordered_at: determine_ordered_at_range(order_ref) } ) end def update_exception(condition, params) UpdateException.new("Unable to update #{self} by condition #{condition} with params: #{params}") end def determine_ordered_at_range(order_ref) ordered_at_date = get_ordered_at(order_ref) (ordered_at_date.beginning_of_day..ordered_at_date.end_of_day) end end end
33. module QueryWithPartitionKey def self.included(base) base.extend(ClassMethods) end module ClassMethods include OrderRefGeneration def lookup_by_order_ref(order_ref:, condition:) where_clause = generate_clause(order_ref, condition) self.where(where_clause).first end private def generate_clause(order_ref, condition) condition.merge( { ordered_at: determine_ordered_at_range(order_ref) } ) end def update_exception(condition, params) UpdateException.new("Unable to update #{self} by condition #{condition} with params: #{params}") end def determine_ordered_at_range(order_ref) ordered_at_date = get_ordered_at(order_ref) (ordered_at_date.beginning_of_day..ordered_at_date.end_of_day) end end end
34. module QueryWithPartitionKey def self.included(base) base.extend(ClassMethods) end module ClassMethods include OrderRefGeneration def lookup_by_order_ref(order_ref:, condition:) where_clause = generate_clause(order_ref, condition) self.where(where_clause).first end private def generate_clause(order_ref, condition) condition.merge( { ordered_at: determine_ordered_at_range(order_ref) } ) end def update_exception(condition, params) UpdateException.new("Unable to update #{self} by condition #{condition} with params: #{params}") end def determine_ordered_at_range(order_ref) ordered_at_date = get_ordered_at(order_ref) (ordered_at_date.beginning_of_day..ordered_at_date.end_of_day) end end end
35. describe '.lookup_by_order_ref' do context 'when record is within the day' do let!(:order_transaction) { create(... ordered_at: order.ordered_at) } let!(:other_transaction) { create(... ordered_at: order.ordered_at - 1.day) } it 'determines ordered_at from order_ref and returns record using condition' do expect( OrderTransaction.lookup_by_order_ref( order_ref: order.order_ref, condition: { reference_id: reference_id } ) ).to eq(order_transaction) end end context 'when record is not within the day' do let!(:older_transaction) { create(... ordered_at: now.beginning_of_day - 1.second) } let!(:newer_transaction) { create(... ordered_at: now.end_of_day + 1.second) } it 'returns nil' do expect( OrderTransaction.lookup_by_order_ref( order_ref: order.order_ref, condition: { reference_id: reference_id } ) ).to be_nil end end end
36. module QueryWithPartitionKey ... module ClassMethods include OrderRefGeneration def update_without_lock(order_ref:, condition:, params:) where_clause = generate_clause(order_ref, condition) self.where(where_clause).update_all(params) end def update_without_lock!(order_ref:, condition:, params:) updated_record_count = update_without_lock(order_ref: order_ref, condition: condition, params: params) raise update_exception(condition, params) if updated_record_count.zero? updated_record_count end ... private def generate_clause(order_ref, condition) ... end def update_exception(condition, params) UpdateException.new("Unable to update #{self} by condition #{condition} with params: #{params}") end def determine_ordered_at_range(order_ref) ... end end end
37. module QueryWithPartitionKey ... module ClassMethods include OrderRefGeneration def update_without_lock(order_ref:, condition:, params:) where_clause = generate_clause(order_ref, condition) self.where(where_clause).update_all(params) end def update_without_lock!(order_ref:, condition:, params:) updated_record_count = update_without_lock(order_ref: order_ref, condition: condition, params: params) raise update_exception(condition, params) if updated_record_count.zero? updated_record_count end ... private def generate_clause(order_ref, condition) ... end def update_exception(condition, params) UpdateException.new("Unable to update #{self} by condition #{condition} with params: #{params}") end def determine_ordered_at_range(order_ref) ... end end end
38. module QueryWithPartitionKey ... module ClassMethods include OrderRefGeneration def update_without_lock(order_ref:, condition:, params:) where_clause = generate_clause(order_ref, condition) self.where(where_clause).update_all(params) end def update_without_lock!(order_ref:, condition:, params:) updated_record_count = update_without_lock(order_ref: order_ref, condition: condition, params: params) raise update_exception(condition, params) if updated_record_count.zero? updated_record_count end ... private def generate_clause(order_ref, condition) ... end def update_exception(condition, params) UpdateException.new("Unable to update #{self} by condition #{condition} with params: #{params}") end def determine_ordered_at_range(order_ref) ... end end end
39. module QueryWithPartitionKey ... module ClassMethods include OrderRefGeneration def update_without_lock(order_ref:, condition:, params:) where_clause = generate_clause(order_ref, condition) self.where(where_clause).update_all(params) end def update_without_lock!(order_ref:, condition:, params:) updated_record_count = update_without_lock(order_ref: order_ref, condition: condition, params: params) raise update_exception(condition, params) if updated_record_count.zero? updated_record_count end ... private def generate_clause(order_ref, condition) ... end def update_exception(condition, params) UpdateException.new("Unable to update #{self} by condition #{condition} with params: #{params}") end def determine_ordered_at_range(order_ref) ... end end end
40. describe '.update_without_lock' do context 'when records are within the day' do let!(:order_transaction) { create(:order_transaction, ... ordered_at: order.ordered_at) } let!(:other_order_transaction) { create(:order_transaction, ... ordered_at: order.ordered_at - 1.day) } it 'determines ordered_at from order_ref and update the correct record' do OrderTransaction.update_without_lock( order_ref: order.order_ref, condition: { reference_id: reference_id }, params: { amount: 10 } ) expect(order_transaction.reload.amount).to eq(10) expect(other_order_transaction.reload.amount).to eq(2) end end context 'when records are not within the day' do let!(:older_transaction) { create(:order_transaction, ... ordered_at: now.beginning_of_day - 1.second) } let!(:newer_transaction) { create(:order_transaction, ... ordered_at: now.end_of_day + 1.second) } it 'does not update records' do OrderTransaction.update_without_lock( order_ref: order.order_ref, condition: { reference_id: reference_id }, params: { amount: 10 } ) expect(older_transaction.reload.amount).to eq(1) expect(newer_transaction.reload.amount).to eq(2) end end end
41. Some unanswered doubts Will model validations such as: validates :attribute, presence: true, uniqueness: true still be safe? If monthly partition is not enough, how big is the hassle of changing this? How difficult is it to maintain a non standardised convention? M O D E L V A L I D A T I O N S S C A L I N G E R R O R P R O N E
42. Some unanswered doubts Will model validations such as: validates :attribute, presence: true, uniqueness: true still be safe? If monthly partition is not enough, how big is the hassle of changing this? How difficult is it to maintain a non standardised convention? M O D E L V A L I D A T I O N S S C A L I N G E R R O R P R O N E
43. Moving Forward
45. non partitioned data sources routing normal app multiple clusters
46. non partitioned data sources routing normal app multiple clusters smart enough to find which cluster to be called
47. non partitioned data sources routing normal app multiple clusters App Level Sharding smart enough to find which cluster to be called
48. Bullet Points
49. - Consider before introducing new standards which against common conventions/syntaxes - Always understand queries that active record generates and their cost - Consider reducing rows to be read from the data source instead of tuning it
50. - Consider before introducing new standards which against common conventions/syntaxes - Always understand queries that active record generates and their cost - Consider reducing rows to be read from the data source instead of tuning it (or blaming the language)
51. Thank You @bskr_