I'm playing around with postgresql with SO datasets. In the process, I have dumped 60M questions data onto the postgresql. I'm trying to build a notification system on top of this, so that, when a user edits a question, I need to show a notification to the user when he/she logs in next time. So literally, trying to create the clone of SO (with very minimal feature)
Not sure that fits my use case, thanks for your help in this.
I do not think that the NOTIFY command implemented in postgreSQL is terribly likely to be useful for your application.
That command is useful for distribution of notifications to applications that are continuously connected to the database, which is not likely true for web app connections, particularly in view of your comment about notifying users "when they log in next time."
Instead, you need a table that captures a log of undelivered notifications of changes to questions. It should capture useful attributes such as..
- Who made the change
- Who is to be notified
- The time of the change
- Perhaps the nature of the change, which could be pretty open ended
- A reference to the question, e.g. its ID
- Some lifecycle attribute such as "viewed-on" or "acknowledged-on"
When a user logs in, it should be easy to query that table, providing the list of unexamined updates.
Also look at Logical Decoding for implementing Change Data Capture
