Performance: Store likes in PostgreSQL ArrayField (Django example)

Question

I have 2 models: Post and Comment, each can be liked by User.

1. For sure, total likes should be rendered somewhere near each Post or Comment

2. But also each User should have a page with all liked content.

So, the most obvious way is just do it with m2m field, which seems will lead to lots of problems in some future.

And what about this?

1. Post and Comment models should have some

   users_liked_ids = ArrayField(models.IntegerField())

2. User model should also have such fields:

   posts_liked_ids = ArrayField(models.IntegerField()) comments_liked_ids = ArrayField(models.IntegerField())

And each time User likes something, two actions are performed:

1. User's id adds to Post's/Comment's users_liked_ids field

2. Post's/Comment's id adds to User's posts_liked_ids/comments_liked_ids field

The questions are:

1. Is it a good plan?

2. Will it be efficient to make lookups in such approach to get "Is that Post/Comment` was liked but current user

3. Will it be better to store likes in some separate table, rather then in liked model, but also in ArrayField

4. Probably better stay with obvious m2m?

Answer 1

1) No.
2) Definitely not.
3) Absolutely, incredibly not. Don't split your data up even further.
4) Yes.

Here are some of the problems:

• no referential integrity, since you can't create foreign keys on array elements, meaning you could easily have garbage values in an ID array
• data duplication with posts having user ids and users having post ids means it's possible for information to get out of sync (what happens when a user or post is deleted?)
• inefficient lookups in match arrays (your #2)

Don't, under any circumstances, do this. You may want to combine your "post" and "comment" models to simplify the relationship, but this is what junction tables are for. Arrays are good for use cases that don't involve foreign keys or the potential for extreme length.