Let's assume, I have two DBs, either MySQL or PostgreSQL (sorry, but we have not yet decided what DBMS to use).
Every second multiple rows are being inserted or updated on the first DB. For each inserted/updated row I want to call a Python script, which connects to another DB on another host, makes some queries there; if everything is fine the script writes these rows (which have been inserted or updated) to the second DB.
After some research I see the following options to call the script:
- Use triggers and do everything in SQL (it looks like the connection to another DB should be not a problem for PostgreSQL, but may be a problem for MySQL if not using federated storage engine)
- Use UDF (user-defined functions)
I also read a bit about different patterns of architecting databases, like Saga or publish/subscribe, but didn't find any concrete practical examples of how to do this.
Two of my questions are:
- Are there any other possibilities to implement the wanted solution?
- The two options I mentioned (triggers and UDF): do they have any hidden traps? Which option is better in terms of performance?