DB structure for storing comments?

Question

PostgreSQL

Given:
650 million comments (one table)
Id, text, time, rating, related_img, related_link user (20 million records), article (140 thousand records), category (50 records), parent comment (650 million)
Peak load:
Reading 50 rps
Write 7rps
Opportunity to leave a comment on the news disappears after 1 month
Task: make a normal database structure to store this
Stack:
pgsql
Symfony 3.2
Doctrine
Redis

Answer 1

Thank you all for the answers, it was decided to store the comments in the radish link entirely, the old ones only an indication of the storage location

Answer 2

Looks like a work order
. What exactly is the question?
- In a parent/child relationship? parent_id
- In tree selection? with recursive
- Want to separate records by time? sharding with UPD condition
:
one
two
three

Answer 3

split into 2 tables? 1 with the news of a month ago, the rest in the archive.

Answer 4

The selection should be in 2 stages (given that all indexes in the database are correct!).
1. Select all comments for the current post using only one Id column.
2. Then - we work only with these comments from the intermediate selection (when receiving data for display on the page, etc.).
3. You can add cross-links if the selection of comments should be not only from the side of the post, but also from the side of a specific user, category, etc.