best performance for storing notifications in postgresql

Question

I have a question concerning the performance of 2 different options, which one is better?

Explanation: I have 5 tables in a postgresql db: users, followers, posts, likes and comments. They look like this (simplified):

users (
  user_id bigint,
  username character varying(24)
);
followers (
  follower_id bigint,
  user_id bigint,
  following_user_id bigint,
  timestamp timestamp with time zone DEFAULT NOW()
);
post (
  post_id bigint,
  user_id bigint,
  text text,
  timestamp timestamp with time zone DEFAULT NOW()
);
likes (
  like_id bigint,
  post_id bigint,
  user_id bigint,
  timestamp timestamp with time zone DEFAULT NOW()
);
comments (
  comment_id bigint,
  post_id bigint,
  user_id bigint,
  comment text,
  timestamp timestamp with time zone DEFAULT NOW()
);

I now want to make a notifications component in my app to show someone he received a new like or comment on a post.

FIRST OPTION:

The first option (I have now) is to create a sixth table named notifications:

notifications (
  notification_id bigint,
  type character varying(12),
  g_user_id bigint,
  r_user_id bigint,
  s_user_id bigint,
  post_id bigint,
  timestamp timestamp with time zone DEFAULT NOW()
);

A notification row would look like this:

+---------------------------------------------------------------------------------------+
| notification_id | type   | g_user_id | r_user_id | s_user_id | post_id | timestamp    |
+---------------------------------------------------------------------------------------+
| 1               | post   | 1         | NULL      | NUL       | 1       | 2021-01-01...|
| 2               | comm   | NULL      | 2         | 1         | 1       | 2021-01-01...|
| 3               | like   | NULL      | 1         | 2         | 1       | 2021-01-01...|
| ...                                                                                   |
+---------------------------------------------------------------------------------------+

It seems to me this is the easiest solution, a query to select and order the notifications for a user (and fetch meta-data like the username of the sender_user_id) is easy with a JOIN.

The problem I'm facing is: I have more notification types (not only likes and comments but around 20 different notification types). Some notifications are for a user specific (defined in receiving_user_id), some notifications are 'general' and are for all users following an account (I defince this in general_user_id).

My (simplified) query to fetch these notifications now look like this. I'm selecting all notifications for r_user_id (receiving_user_id) 1 with a JOIN to the table users to select all usernames from the sending_user_ids (s_user_id) (if not null) and to select all "general" notifications (all notifications with g_user_id NOT NULL where g_user_id is a user_id that user_id = 1 is following in the table followers).

SELECT
    a.notification_id,
    a.type,
    a.timestamp,
    b.username
FROM
    notifications a
LEFT JOIN
    users b
  ON
    a.s_user_id = b.user_id
WHERE
    (
      a.r_user_id = 1
      OR
      a.g_user_id IN (SELECT following_user_id FROM followers WHERE user_id = 1)
    )
ORDER BY
    a.timestamp DESC

The biggest problem I have with this solution is that it requires an extra table and extra commands in my node.JS code. When someone likes a post, I have to create a notification row, when some dislikes a post, I have to delete this notification again.

SECOND OPTION:

My second option is to just leave these 5 tables and with a UNION ALL query "create" the notifications on the fly, if possible.

I don't know how other people create a notification system, and I didn't found much tutorials about it. Is it OK to create a separate notifications table (and use postgresql trigger functions to automatically create/delete notification rows), or should I create these notifications 'on the fly' based on the rows in likes, comments and posts?

Answer 1

There's nothing wrong with creating a dedicated table for notifications, along the route your example above is choosing to take. Whether you use triggers or handle managing it with calls from the application side is up to you and equally of merit as well.

Alternatively I was thinking similar to your last proposed solution, but by using a View as a way to centralize the different notification types and stay current in real-time. You would UNION ALL your different notification types and when you select from the View, you would likely order by the timestamp descending to show the latest notifications first.

The only other part you'd need to come up with is how to manage which notifications a user has already seen. For that you'd likely need a logging table of the notification's id and type, and had columns for the user's id and timestamp of when they viewed their notifications. Then in the View above you'd OUTER JOIN to this viewed notifications log, so you can have an additional column in it called isNew and you'd know which ones are new to the current user since the last time they looked at their notifications. At least this viewed notifications log table would be written to less frequently (only whenever a user looks at their notifications) than just a pure notifications table (every new instance of a notification), so it would be much transactionally lighter.