Rails Conf 2023 - Beyond CRUD: the PostgreSQL techniques your Rails app is missing

Being part of gnarly production outages isn’t uncommon. In this talk, I will share short entertaining stories about a few head scratching production outages we faced and how we used efficient and simple PostgreSQL features in Rails to build scalable solutions.

I will also discuss how and when to use certain PostgreSQL concepts in Rails, such as optimistic and pessimistic locking, using real-life examples that power core customer features.

Finally, I will discuss uses of Postgres mutex to manage concurrent access to shared resources and wrap up by sharing our own experiences and lessons from other production outages. This will help you avoid common operational pitfalls and improve your application's reliability and query patterns.

Transcript

1. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Beyond CRUD: the PostgreSQL techniques your Rails app is missing 🚀 1
2. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Beyond CRUD: the PostgreSQL techniques your Rails app is missing 🚀 2 Shayon Mukherjee @shayonj @shayonj
3. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Incidents Happen 🙈 3
4. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Overview 🔎 - The time when we lost all dynamically scheduled customer jobs 🫠 - Optimistic and Pessimistic Locking 🔐 - Skip’em with SKIP LOCKED ⏭ - Synchronizing workloads with Advisory Locks 🎛 - Timeouts, Reconnects and Retries ⏳ 4
5. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 I am Shayon Mukherjee 👋 5 @shayonj @shayonj
6. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 📍Cambridge, MA Olly (O11y?)
7. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 7 www.tines.com
8. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 The time when we lost all dynamically scheduled customer jobs 🥲 8
9. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 9 How does Tines work? 🗃
10. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 10 How does Tines work? 🗃
11. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 11 How does Tines work? 🗃
12. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 12 How does Tines work? 🗃 Table: actions
13. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 13 Scheduling an Action 📽
14. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 14 Scheduling an Action 📽 Static Jobs # sidekiq.yml scheduler: schedule: "Scheduled::PopulateGravatarJob": cron: "0 * * * *" # Runs every hour description: "Populates gravatar url" "Scheduled::PoolAnalyticsEventJob": cron: "*/5 * * * *" #Runs every 5 min description: "Pool and send events to Analytics store"
15. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 15 Scheduling an Action 📽 Dynamic Jobs # sidekiq.yml scheduler: dynamic: true schedule: "Scheduled::PopulateGravatarJob": cron: "0 * * * *" # Runs every hour description: "Populates gravatar url" "Scheduled::PoolMixpanelEventJob": cron: "*/5 * * * *" #Runs every 5 min description: "Pool and send events to Analytics store"
16. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 16 Scheduling an Action 📽 Dynamic Jobs Sidekiq.set_schedule(action.unique_key, { "cron" => cron_schedule_with_timezone, "class" => AgentReceiveJob.to_s, "args" => [action.id], }, )
17. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 17 Scheduling an Action 📽 Internal Job ... def perform Scheduler.reset_schedules! Scheduler.load_default_schedules! Action .joins(:story) .scheduled .available .find_each { |action| Scheduler.populate_action_schedules(action) } Scheduler.reload_schedule! end ...
18. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 18 Scheduling an Action 📽 Internal Job ... def perform Scheduler.reset_schedules! Scheduler.load_default_schedules! Agent .joins(:story) .scheduled .available .find_each { |action| Scheduler.populate_action_schedules(action) } Scheduler.reload_schedule! end ...
19. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 19 Scheduling an Action 📽 Deploy
20. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 20 Scheduling an Action 📽 Sidekiq scheduler def update_schedule ... if schedule_changes.size > 0 ... Sidekiq.reload_schedule! schedule_changes.each do |schedule_name| if Sidekiq.schedule.keys.include?(schedule_name) ... load_schedule_job(schedule_name, Sidekiq.schedule[schedule_name]) ...
21. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 21 Scheduling an Action 📽 Sidekiq scheduler def update_schedule ... if schedule_changes.size > 0 ... Sidekiq.reload_schedule! schedule_changes.each do |schedule_name| if Sidekiq.schedule.keys.include?(schedule_name) ... load_schedule_job(schedule_name, Sidekiq.schedule[schedule_name]) ...
22. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 22 Scheduling an Action 📽
23. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 23 Back to first principles🥇
24. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 24 Back to first principles🥇 - 1 minute as lowest frequency of jobs - Avoid bi-modality - Avoid using new and complex technology - Support for self hosted - Embrace eventual consistency
25. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 25 New Scheduler System 📆
26. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 26 New Scheduler System 📆
27. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 27 New Scheduler System 📆 class NewFanOutJob ... def perform ... ActionSchedule.where("next_run_at <= ?", Time.now.utc).find_each(&:run) ... end end
28. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 28 New Scheduler System 📆 class ActionSchedule def run return if next_run_at > Time.now.utc # optimistic concurrency control: rows_updated = self.class.where(id: id, cron: cron, timezone: timezone, next_run_at: next_run_at) .update_all(next_run_at: calculate_next_run_at) enqueue_action_run if rows_updated == 1 end end
29. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 29 New Scheduler System 📆 class ActionSchedule def run return if next_run_at > Time.now.utc # optimistic concurrency control: rows_updated = self.class.where(id: id, cron: cron, timezone: timezone, next_run_at: next_run_at) .update_all(next_run_at: calculate_next_run_at) enqueue_action_run if rows_updated == 1 end end
30. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 30 New Scheduler System 📆 Types of locking - Optimistic Locking (?) / Optimistic Concurrency Control - Pessimistic Locking / Pessimistic Concurrency Control
31. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 31 New Scheduler System 📆 class ActionSchedule def run return if next_run_at > Time.now.utc # optimistic concurrency control: rows_updated = self.class.where(id: id, cron: cron, timezone: timezone, next_run_at: next_run_at) .update_all(next_run_at: calculate_next_run_at) enqueue_action_run if rows_updated == 1 end end
32. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 32 New Scheduler System 📆 class ActionSchedule def run return if next_run_at > Time.now.utc self.with_lock do self.next_run_at = calculate_next_run_at self.save! enqueue_action_run end end end
33. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 33 New Scheduler System 📆 class ActionSchedule def run return if next_run_at > Time.now.utc # optimistic concurrency control: rows_updated = self.class.where(id: id, cron: cron, timezone: timezone, next_run_at: next_run_at) .update_all(next_run_at: calculate_next_run_at) enqueue_action_run if rows_updated == 1 end end
34. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Skip’em with SKIP LOCKED ⏭ 34
35. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Skip’em with SKIP LOCKED ⏭ 35
36. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Skip’em with SKIP LOCKED ⏭ 36
37. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Skip’em with SKIP LOCKED ⏭ 37
38. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Skip’em with SKIP LOCKED ⏭ 38
39. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Skip’em with SKIP LOCKED ⏭ 39 class ApplicationRecord < ActiveRecord::Base ... def update_columns_with_skipped_lock(args) record = self.class.lock("FOR UPDATE SKIP LOCKED").find_by(id: id) return if record.nil? record.update_columns(args) end ... end
40. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Skip’em with SKIP LOCKED ⏭ 40 # action.touch(:last_event_at) action.update_columns_with_skipped_lock( last_event_at: Time.now, ) #> SELECT "agents".* FROM "agents" WHERE "agents"."id" = $1 LIMIT $1 FOR UPDATE SKIP LOCKED
41. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Skip’em with SKIP LOCKED ⏭ 41
42. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Synchronizing workloads with advisory locks 🎛 42
43. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Synchronizing workloads with Postgres advisory locks 🎛 - Application defined lock - Outside of the inbuilt MVCC (multiversion concurrency control) - Control access to a shared resource - Control access to a 3rd party API 43
44. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Synchronizing workloads with Postgres advisory locks 🎛 # acquire SELECT pg_try_advisory_lock(10); # release SELECT pg_try_advisory_unlock(10); 44
45. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Synchronizing workloads with Postgres advisory locks 🎛 - github.com/ClosureTree/with_advisory_lock - github.com/heroku/pg_lock 45
46. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Synchronizing workloads with Postgres advisory locks 🎛 User.with_advisory_lock(lock_name) do do_something_that_needs_locking end User.with_advisory_lock(lock_name, { timeout_seconds: 30 }) do do_something_that_needs_locking end 46
47. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Synchronizing workloads with Postgres advisory locks 🎛 class Scheduled::PoolAnalyticsEventJob ... def perform ... ApplicationRecord.with_advisory_lock("analytics-job", { timeout_seconds: 30 }) do # send analytics events end ... end end 47
48. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Timeouts, Reconnects and Retries ⏳ 48
49. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Timeouts, Reconnects and Retries ⏳ 49 production: adapter: postgresql encoding: utf8 ... variables: statement_timeout: 60000 # 1min lock_timeout: 5000 # 5s idle_in_transaction_session_timeout: 300000 # 5min
50. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Timeouts, Reconnects and Retries ⏳ 50 ActiveRecord Connection Pools
51. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Timeouts, Reconnects and Retries ⏳ 51 def exec_no_cache(*args) super(*args) rescue ActiveRecord::StatementInvalid, ActiveRecord::ConnectionNotEstablished => e if failover_error?(e.message) disconnect! ActiveRecord::Base.connection_pool.remove(::ActiveRecord::Base.connection) raise if in_transaction? reconnect_and_retry _with_backoff! end end
52. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Timeouts, Reconnects and Retries ⏳ 52 Rails 6.x: github.com/tines/rails-pg-adapter Rails 7 (coming soon) ❤ Rails core team and Shopify
53. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Timeouts and Reconnects⏳ 53 github.com/tines/rails-pg-adapter Auto heal from PG::UndefinedColumn SELECT "events"."id", "events"."old_message" FROM "events" WHERE "events"."agent_id" IS NOT NULL ORDER BY "events"."id" DESC LIMIT $1
54. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Timeouts and Reconnects⏳ 54 github.com/tines/rails-pg-adapter def exec_no_cache(*args) super(*args) rescue ActiveRecord::StatementInvalid=> e if column_error?(e.message) ActiveRecord::Base.connection_pool.connections.do |conn| conn.schema_cache.clear! end ActiveRecord::Base.descendants.each(&:reset_column_information) raise end end
55. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Till next time ✌ - The time when we lost all dynamically scheduled customer jobs 🫠 - Optimistic and Pessimistic Locking 🔐 - Skip’em with SKIP LOCKED ⏭ - Synchronizing workloads with Advisory Locks 🎛 - Timeouts and Reconnects ⏳ 55
56. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Special thanks 󰚤 - @noelrap - @serifritsch - @ronocod - @team-carbon Tines 56
57. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Till next time ✌ 57 tines.com/careers
58. Beyond CRUD: the PostgreSQL techniques your Rails app is missing | Rails Conf 2023 Till next time ✌ 58 @shayonj @shayonj