Re: Building a notification system.

On 15 July 2018 at 23:25, Anto Aravinth <anto(dot)aravinth(dot)cse(at)gmail(dot)com> wrote:

>
>
> On Mon, Jul 16, 2018 at 8:02 AM, Christopher Browne <cbbrowne(at)gmail(dot)com>
> wrote:
>
>> On Sun, Jul 15, 2018, 5:30 AM Anto Aravinth, <anto(dot)aravinth(dot)cse(at)gmail(dot)com>
>> wrote:
>>
>>> Hello Everyone,
>>>
>>>
>>> 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)
>>>
>>> I'm not sure, how to get started with this. Read about NOTIFY:
>>> https://www.postgresql.org/docs/current/static/sql-notify.html
>>>
>>>
>>> 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

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com