The Ultimate Guide to PostgreSQL Data Change Tracking
exaspark.medium.com
The Ultimate Guide to PostgreSQL Data Change Tracking
1–10 of 27 posts
Re: The Ultimate Guide to PostgreSQL Data Change Tracking
#2This 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?
Re: The Ultimate Guide to PostgreSQL Data Change Tracking
#3This 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?
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
Re: The Ultimate Guide to PostgreSQL Data Change Tracking
#4This 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?
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
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.
Re: The Ultimate Guide to PostgreSQL Data Change Tracking
#5This 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?
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
Re: The Ultimate Guide to PostgreSQL Data Change Tracking
#6Great post, I really like the approach of adding additional context for WAL CDC. I imagine this could be implemented with something like Hasura pretty easily (you can access hasura session data in the db).
I use both the trigger + audit table approaches in my sass (for user-facing activity feeds) and subscribe to CDC WAL changes for dealing with callback-like logic (i.e., send registration email, clear cache).
I'm not a fan of the pg_notify approach due to requirement of adding triggers (performance penalty) and the 8k character limit per column (you will lose data as it will splice off anything larger than that).
Application-level tracking or callbacks makes the stack dependent on the application for data integrity, I'd rather the database be the source of truth on all things data. Especially in the age of microservices.
For something turn-key, Bemi looks like a really good option - especially if you need persistence of the changes!
For something very light wight - check out WalEx (I'm the maintainer):
Re: The Ultimate Guide to PostgreSQL Data Change Tracking
#7This 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?
The WAL CDC approach: https://github.com/cpursley/walex?tab=readme-ov-file#publica...
CREATE PUBLICATION news_item FOR TABLE news WHERE (topic IS "AAPL");
Re: The Ultimate Guide to PostgreSQL Data Change Tracking
#8Earlier quoted context omitted.
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
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 my…
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.
Re: The Ultimate Guide to PostgreSQL Data Change Tracking
#9Another technique not discussed is to create an md5 hash of each record and to keep track of added and deleted hashes. The d
hash would exist as an additional column on each table being tracked.
Re: The Ultimate Guide to PostgreSQL Data Change Tracking
#10This 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?
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
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.