Need some design advice for on implementing a push notification system. I am using for the backend Symfony 3, Doctrine and postgres as the database. The notification system will have two types of notifications: 1. Instant notifications 2. Scheduled Notifications Instant notifications are when an action is performed in the backend and a notification is instantly pushed. For example, a notification when a user is deleted in the database.

Scheduled notifications is for a notification that has not been triggered yet but will at some known time in the future. The notification will only be triggered when it reaches that set time. For example an Alarm clock.

Based on this I have designed simple tables as follows with some mock data:

So with above information the API concept is at user’s device such as mobile phone, it will run a GET/notifications API every 2 mins. The API will return notifications that are before the current date and has not been dismissed for that user. For example, if USER2 executes the GET/notifications with the current date at 18th April 2017 it will return record 3.

My question is this type of design acceptable if not what would be the conventional practice? Also with many users running the same GET API every 2 mins is there a potential to slow down the system? I’m guessing about at most 3000 users.

Finally, as the notifications table grows in records will postgres slow down when performing this API?

Who ever has taken the time to read it all the way through thank you, I am very grateful for any advice.