What is the optimal structure for the "likes" table?

Question

Database design

There are posts, there are users. You need to make them able to like posts.
The following structure immediately came to my mind:

create table `likes` (
  `post_id` int, 
  `user_id` int,   
  foreign key (`post_id`) references `users`(`id`) on delete cascade,
  foreign key (`user_id`) references `posts`(`id`) on delete cascade, 
)  engine=InnoDB, default character set = utf8;

1) If, purely theoretically, imagine that there are millions of users, and tens of millions of posts, is such a structure optimal?
2) do you need the id field here, or make the PK composite (post_id, user_id) or is the PK not needed at all? Does it affect the select?

Answer 1

If, purely theoretically, imagine that there are millions of users, and tens of millions of posts, is such a structure optimal?

Almost. You need to apply duplication and throw into this table at once all those fields that you may need to display, otherwise you will have to do a join every time, which would not be desirable with highload. That is, you need to add fields like user_name, post_title, post_body, etc. to the table at once (in general, everything that you planned to get using join).
On the account of "millions of users, and tens of millions of posts":
If you have such an amount of data, then in any case, at some point you will have to resort to horizontal sharding, so if you think that the project can really grow to such an amount of data, it's better to do it right away take this into account and design the database so that horizontal sharding does not become a problem.
It depends on the use cases (think about when you will need the id field), but in most cases it is not needed and such fields are introduced for peace of mind and harmony. This does not affect the select, because anyway you will make a selection either by user_id or by post_id (again, this is in most common scenarios, if you have some kind of logic where you need to select records from the likes table for some deliberately entered identifier, then enter).

Answer 2

A similar problem popped up, namely with likes.
The brakes were due to a clumsy code.
Since the number of users was already under 2 million, each post had from 1k to 30k, likes, respectively, for each post.
It was necessary to show a selection of users with accumulated likes.
We decided to do it on the client)))
The visitor selects an array of users, we quickly give him the basic data, and load ALL GARBAGE (likes, shmaiki, points) with a separate (almost flat instant request) in the background with Ajax.
What for to drag EVERYTHING at once? ...