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.

Transcript

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 ifat@launchpadlab.com 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
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! ifat@launchpadlab.com github.com/inveterateliterate