3 ms·

This article brings a very good question, let us say I have a stock news app and want to issue an alert everytime a new news item is added to the database for A

This article brings a very good question, let us say I have a stock news app and want to issue an alert everytime a new news item is added to the database for AAPL. There could be a 1000 symbols. What is the best way to implement this alerting system in postgres?

crucialfelix 3y ago
I would avoid using database triggers for business logic. In the app (Django or whatever) after inserting news item, call a service (async) that matches users, sends alerts
- eknkc 3y ago
  I generally use triggers for these kinds of things. I’d create a message queue table that would be inserted with jobs according to changes in other tables. Makes it easier to handle the message queue asynchronously. Doing it in application side in a reliable way needs some kind of a distributed transaction mechanism which might not be feasible. I don’t want to fire an insert and then fail to send an alert and find myself within an if statement thinking “what the fuck do I do now?” You could insert the job on the application code too. I just like triggers and abuse them which might not be a good idea in all cases. Depends on the requirements though.
  - crucialfelix 3y ago
    I'm using Django and Celery, so the tasks (eg. Send notifications) are scheduled on db transaction commit. The business logic is clear to read, easy to change. Ive enjoyed writing complex SQL, but when it comes to app development I want my engineers to just write python. Triggers are mostly invisible in the codebase and I would end up having to do all the maintenance myself. There is of course more than one way to skin a cat, but I want our codebase to have just one single way.
- DandyDev 3y ago
  Agreed. Triggers always feel really clever and powerful, but they live outside your regular application code and are therefor more or less invisible. This will eventually break the law of least surprise. On top of that, triggers are hard to test.
  - dventimi 3y ago
    Not if your regular application code is in the database
bennyp101 3y ago
Assuming by issue an alert you want to call an external service, I’d go with listen notify and use something like Graphile-worker to pick it up and process it. Or you could stay in pg and use a FDW with a trigger
cpursley 3y ago
The WAL CDC approach: https://github.com/cpursley/walex?tab=readme-ov-file#publication https://github.com/cpursley/walex?tab=readme-ov-file#publica... CREATE PUBLICATION news_item FOR TABLE news WHERE (topic IS "AAPL");
- vivzkestrel 3y ago
  any suggestions for node.js on this? This one seems specific to elixir
  - cpursley 3y ago
    Node is not appropriate for this sort of workload. You can use WalEx and forward the events to your Node app via webhook: https://github.com/cpursley/walex?tab=readme-ov-file#webhooks https://github.com/cpursley/walex?tab=readme-ov-file#webhook...
arjunlol 3y ago
It depends on your reliability and performance constraints. I'd recommend just doing this via hooks on the application layer if it's a simple application. If you're building a production scalable application that required reliability guarantees - you can do this with https://github.com/BemiHQ/bemi https://github.com/BemiHQ/bemi too (I'm one of the maintainers from the article). I saw you mentioned you're using Node.js, check out the Bemi github there's a few Node.js compatible libraries too. Feel free to ping me any q's!

crucialfelix 3y ago

I would avoid using database triggers for business logic. In the app (Django or whatever) after inserting news item, call a service (async) that matches users, sends alerts

eknkc 3y ago
I generally use triggers for these kinds of things. I’d create a message queue table that would be inserted with jobs according to changes in other tables. Makes it easier to handle the message queue asynchronously. Doing it in application side in a reliable way needs some kind of a distributed transaction mechanism which might not be feasible. I don’t want to fire an insert and then fail to send an alert and find myself within an if statement thinking “what the fuck do I do now?” You could insert the job on the application code too. I just like triggers and abuse them which might not be a good idea in all cases. Depends on the requirements though.
- crucialfelix 3y ago
  I'm using Django and Celery, so the tasks (eg. Send notifications) are scheduled on db transaction commit. The business logic is clear to read, easy to change. Ive enjoyed writing complex SQL, but when it comes to app development I want my engineers to just write python. Triggers are mostly invisible in the codebase and I would end up having to do all the maintenance myself. There is of course more than one way to skin a cat, but I want our codebase to have just one single way.
DandyDev 3y ago
Agreed. Triggers always feel really clever and powerful, but they live outside your regular application code and are therefor more or less invisible. This will eventually break the law of least surprise. On top of that, triggers are hard to test.
- dventimi 3y ago
  Not if your regular application code is in the database

bennyp101 3y ago

Assuming by issue an alert you want to call an external service, I’d go with listen notify and use something like Graphile-worker to pick it up and process it. Or you could stay in pg and use a FDW with a trigger

cpursley 3y ago

The WAL CDC approach: https://github.com/cpursley/walex?tab=readme-ov-file#publication https://github.com/cpursley/walex?tab=readme-ov-file#publica... CREATE PUBLICATION news_item FOR TABLE news WHERE (topic IS "AAPL");

vivzkestrel 3y ago
any suggestions for node.js on this? This one seems specific to elixir
- cpursley 3y ago
  Node is not appropriate for this sort of workload. You can use WalEx and forward the events to your Node app via webhook: https://github.com/cpursley/walex?tab=readme-ov-file#webhooks https://github.com/cpursley/walex?tab=readme-ov-file#webhook...

arjunlol 3y ago

It depends on your reliability and performance constraints. I'd recommend just doing this via hooks on the application layer if it's a simple application. If you're building a production scalable application that required reliability guarantees - you can do this with https://github.com/BemiHQ/bemi https://github.com/BemiHQ/bemi too (I'm one of the maintainers from the article). I saw you mentioned you're using Node.js, check out the Bemi github there's a few Node.js compatible libraries too. Feel free to ping me any q's!