Add scheduler to delete old notifications

[kontrollanten]

Describe the current behavior

We've +5 millions rows in userNotification table and starting to see slow queries when marking notifications as read.

duration: 1238.224 ms  execute <unnamed>: UPDATE "userNotification" SET "read"=$1,"updatedAt"=$2 WHERE "userId" = $3

Steps to reproduce

No response

Describe the expected behavior

A scheduler should cleanup +4 weeks old, read notifications.

Additional information

• PeerTube instance:

  • URL:
  • Version: 6.1.0
  • NodeJS version:
  • Ffmpeg version:

• Browser name, version and platforms on which you could reproduce the bug:

• Link to browser console log if relevant:

• Link to server log if relevant (journalctl or /var/www/peertube/storage/logs/):

[Chocobozzz]

Can you try to run the query with WHERE "userId" = $3 AND "read" IS FALSE?

[kontrollanten]

It doesn't seem to affect the cost:

peertube_prod=# EXPLAIN UPDATE "userNotification" SET "read"=true,"updatedAt"='2024-05-03 17:03:28.649+00' WHERE "userId" = 1;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Update on "userNotification"  (cost=1112.06..52909.66 rows=35049 width=75)
   ->  Bitmap Heap Scan on "userNotification"  (cost=1112.06..52909.66 rows=35049 width=75)
         Recheck Cond: ("userId" = 1)
         ->  Bitmap Index Scan on user_notification_user_id  (cost=0.00..1103.30 rows=35049 width=0)
               Index Cond: ("userId" = 1)
(5 rows)

peertube_prod=# EXPLAIN UPDATE "userNotification" SET "read"=true,"updatedAt"='2024-05-03 17:03:28.649+00' WHERE "userId" = 1 AND read IS FALSE;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Update on "userNotification"  (cost=1111.05..52908.65 rows=30990 width=75)
   ->  Bitmap Heap Scan on "userNotification"  (cost=1111.05..52908.65 rows=30990 width=75)
         Recheck Cond: ("userId" = 1)
         Filter: (read IS FALSE)
         ->  Bitmap Index Scan on user_notification_user_id  (cost=0.00..1103.30 rows=35049 width=0)
               Index Cond: ("userId" = 1)
(6 rows)

[Chocobozzz]

It doesn't seem to affect the cost:

Can you try to just test the query? I think most of time is spent Updating rows and not finding them.

[kontrollanten]

I ran with EXPLAIN ANALYZE and the difference in execution time is huge:

peertube_prod=# EXPLAIN ANALYZE UPDATE "userNotification" SET "read"=true,"updatedAt"='2024-05-03 17:03:28.649+00' WHERE "userId" = 1 AND read IS FALSE;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Update on "userNotification"  (cost=1123.22..53718.19 rows=31466 width=75) (actual time=86.252..86.254 rows=0 loops=1)
   ->  Bitmap Heap Scan on "userNotification"  (cost=1123.22..53718.19 rows=31466 width=75) (actual time=86.250..86.251 rows=0 loops=1)
         Recheck Cond: ("userId" = 1)
         Filter: (read IS FALSE)
         Rows Removed by Filter: 31382
         Heap Blocks: exact=6925
         ->  Bitmap Index Scan on user_notification_user_id  (cost=0.00..1115.35 rows=35589 width=0) (actual time=12.815..12.815 rows=74747 loops=1)
               Index Cond: ("userId" = 1)
 Planning Time: 0.221 ms
 Execution Time: 86.434 ms
(10 rows)

peertube_prod=# EXPLAIN ANALYZE UPDATE "userNotification" SET "read"=true,"updatedAt"='2024-05-03 17:03:28.649+00' WHERE "userId" = 1;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Update on "userNotification"  (cost=1124.25..53719.22 rows=35589 width=75) (actual time=1360.231..1360.232 rows=0 loops=1)
   ->  Bitmap Heap Scan on "userNotification"  (cost=1124.25..53719.22 rows=35589 width=75) (actual time=12.338..88.100 rows=31382 loops=1)
         Recheck Cond: ("userId" = 1)
         Heap Blocks: exact=6925
         ->  Bitmap Index Scan on user_notification_user_id  (cost=0.00..1115.35 rows=35589 width=0) (actual time=11.143..11.143 rows=74747 loops=1)
               Index Cond: ("userId" = 1)
 Planning Time: 0.166 ms
 Execution Time: 1360.387 ms
(8 rows)

Adding AND "read" IS FALSE looks like a success.

[Chocobozzz]

Thanks! Changed in 9ee1f7b

[kontrollanten]

Thanks! For me it's okay to close this issue since the root issue has been solved.