Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Call Me Back, Postgres

May 31, 2022

Call Me Back, Postgres

Rails' Active Record callbacks provide an interface for executing a process when something happens to a database record. However, sometimes Active Record callbacks aren’t the best solution available. For those cases, this talk introduces a great alternative: Postgres' trigger functionality, a way of implementing callbacks at the database level. Coupled with Postgres' listen and notify features, you can develop simple, creative solutions for making your Rails app the center of an otherwise complex system, managing data syncing and other processes seamlessly, regardless of consumers of the app.


May 31, 2022

Other Decks in Technology


  1. Call Me Back, Postgres Using Postgres Triggers with Listen /

    Notify to Implement a Data Sync Process Ifat Ribon @i_ribon9 github.com/inveterateliterate
  2. Ifat Ribon Principal Architect LaunchPad Lab ⛲ 🏃 🥍 github.com/inveterateliterate

    [email protected] About Me
  3. Case Study Agenda 01 Sync Approaches 02 Key Concepts 03

    System Design 04 Resources 05 Appendix 06
  4. 01 Case Study: Salesforce Syncing with Heroku Connect

  5. Core Legacy App Heroku Connect Salesforce Instance Core Postgres Database

    Core Rails API Main Players
  6. Key Connections

  7. 02 Sync Approaches

  8. Rails Callbacks • What: Rails ActiveRecord object life cycle hooks

    such as after_create, after_update, after_delete • Why: Use Rails DSL and conventions to trigger additional logic based on a change to an object • Why not: Direct consumers of the app database will not reach the Rails callbacks
  9. Polling • What: Chron job to periodically look at the

    database and identify changes • Why: Accessible to all consumers of the database, flexibility in timing and considerations of the data syncing process • Why not: Expensive to frequently query the database; lower reliability writing the checks by hand
  10. PG Triggers with Listen/Notify • What: Database-level callbacks that tap

    into a pub/ sub scheme • Why: Any event from any client connected to the database will be captured • Why Not: Requires Postgres-specific knowledge and writing SQL; may be less discoverable or obvious in a conventional Rails application
  11. 03 Key Concepts

  12. PG Triggers Callback at the database level that executes a

    defined function before, after, or instead of identified operations (i.e., INSERT, UPDATE, DELETE) Surfaces metadata of the event, such as the operation (event name), schema, table name, and OLD and NEW versions of the record’s attributes Icons courtesy of the noun project: Gregor Cresnar and darwis
  13. Define a Function

  14. Apply Trigger Function

  15. PG Notify and Listen NOTIFY sends a notification event with

    a defined payload string through defined channels LISTEN establishes sessions on defined channels to capture notifications sent on those channels Icons courtesy of the noun project: Siipkan Creati, Ragal Kartidev
  16. Broadcast a Notification

  17. Listen for Notifications

  18. None
  19. Perform pg_notify

  20. Using Rails Migrations

  21. Using Rails Migrations

  22. 04 System Design

  23. Syncing Process Design

  24. Maintaining a Connection web: rails s worker: bundle exec sidekiq

    listener: bundle exec rake database_listeners:listener
  25. Logging Triggers

  26. Avoiding Continuous Trigger Loops

  27. Solving for Edge Cases Identify dependencies or conflicts

  28. Solving for Edge Cases Write Automated Tests!

  29. 05 Resources

  30. Resources • PG Notify Documentation • PG Listen Documentation •

    PG Trigger Documentation • fx Gem • HairTrigger Gem • Heroku Connect • PostgreSQL Listen / Notify
  31. 06 Appendix

  32. Rails Migrations: With fx Gem

  33. Thank You! [email protected] github.com/inveterateliterate