The structure of the database to store the comments?

This:
650 million comments (one table)
Id, text, time, rating, related_img, related_link communication with the user(20 million records), article(140 thousand entries), category(50 entries), the parent comment(650 million)
Peak load:
Reading 50 rps
Entry 7rps
The opportunity to leave a comment to news ischezaet after 1 month

Task: make a normal dB structure to store this

Stack:
pgsql
Symfony 3.2
Doctrine
Redis
June 14th 19 at 20:42
4 answers
June 14th 19 at 20:44
Solution
Thank you all for answers, decided to keep the comments in the radish swazie whole, old is just an indication of the storage location
it is not clear how it will help in the speed of fetching/writing and how it relates to the question?! - Kyleigh_Hills commented on June 14th 19 at 20:47
if a website news satanic, it will basically read the news 1-3 days from now. The rest are just a burden and blow on indices just.

He counted, 650mn sapsea index are integer (4 bytes) is approximately 2.5 GB. When the read speed of the hard 200-250MB\s it will be 10-12 seconds. But as I understand it they have there use polymorphic relationships in tables, so the index compound, i.e., more size. In addition, there are joini(threaded comments). The question is, why keep chasing that amount of data? - Haley0 commented on June 14th 19 at 20:50
the sample of radish much faster than classical databases in view of the fact that all the memory on this for the last week stored in the Radish, then placed in Postgres table by week number and stored in radice only the id and table name - Elinor_Dool commented on June 14th 19 at 20:53
, , cut down the energy and all the new comments - will be destroyed, because they were only in memory.
PS: how does the radish - I know...
It is easier, as I already wrote in the answer, divide huge data file in table-clusters: by days, weeks, category.
As better clustering - see based on the business logic and the data being requested.
Sampling and recording - will be faster, but new comments will always be placed on the medium.
Briefly:
Entry/change in the base - radish+store(DB)
Reading and sampling the radishes. In the absence of the cache - pull from the repository't cache on the radishes, to re-like, to the base to climb. - Kyleigh_Hills commented on June 14th 19 at 20:56
when logic, which we began writing in the event of a power outage from 2 different data centers, we could lose all the data in 1 second (10-14 reviews) in the worst case - Elinor_Dool commented on June 14th 19 at 20:59
and-and.. well, if not critical - then approx. - Kyleigh_Hills commented on June 14th 19 at 21:02
the probability of loss is minimal, and with this gain in speed is not so critical - Elinor_Dool commented on June 14th 19 at 21:05
well logically, Yes. - Kyleigh_Hills commented on June 14th 19 at 21:08
June 14th 19 at 20:46
Looks like a work order

Question specifically what?
In conjunction of parent/offspring? parent_id
- In the sample of wood? with recursive
- Want to separate the recording? sharding with the condition

UPD:
times
two
three
Want to speed up the selection.
Have regard
The selection tree also has
The separation in time will not speed up the sample in comparison with a competent repetirovali - Kyleigh_Hills commented on June 14th 19 at 20:49
, sharding is quite a speed up. And where does forgive repetirovali? - Haley0 commented on June 14th 19 at 20:52
times
two
three - Elinor_Dool commented on June 14th 19 at 20:55
June 14th 19 at 20:48
split into 2 tables? 1 news a month ago, and the rest in archive.
Sounds good, except that we first need to identify a when left a comment - Kyleigh_Hills commented on June 14th 19 at 20:51
field time - not suitable?) - Haley0 commented on June 14th 19 at 20:54
how to know which table to pull the comment from the old or new - Elinor_Dool commented on June 14th 19 at 20:57
at the date of the article? like according to the age article, the ban on comments talking?
Poidee comments such articles can be stored in any ready icon the tree. Ie ID the article | the comments in isone.
If comments are guaranteed nothing added\will not change and they are simply stored.

In an extreme case even for a long time, but you can get the comments of any user to parse logs. - Kyleigh_Hills commented on June 14th 19 at 21:00
to store comments in isone is some kind of perverted to be honest, I JSON this parse will be a lot of time

article date 05.01.17
date of first comments 05.01.17
the date of the last comment 05.02.17
first comment gets to the table storage comments for January
the latter falls into the storage table for February - Elinor_Dool commented on June 14th 19 at 21:03
MB not clearly explained.
Ready tree of comments is stored in isone. Who looks old article it just tightens the whole tree and there htmlqa or rendered, either on the client side. Without much fuss the database.

article id | json komenty, 2 columns in the database. But this is only for the old, where guaranteed no more review not write. Ie just storage. - Kyleigh_Hills commented on June 14th 19 at 21:06
and, Yes, much better, the concept is interesting, but alas the comments a month later can be changed: the rating, editing for moderators, delete - Elinor_Dool commented on June 14th 19 at 21:09
June 14th 19 at 20:50
Sampling should be in 2 stages (when you consider that all indices are correct!).
1. Select all comments for the current post using only one column Id.
2. Then work only with these comments from the interim sampling (upon receipt of data to output to the page, etc.).

3. You can add cross-connection, if the sample of comments should be not only on the part of the post, but also by a specific user, category, etc.
Now I realized, this is not optimal - Kyleigh_Hills commented on June 14th 19 at 20:53
make table-clusters of 100-500k of comments in each or for each section (1-2 level) - a separate table. - Haley0 commented on June 14th 19 at 20:56
a good option thank you - Elinor_Dool commented on June 14th 19 at 20:59

Find more questions by tags RedisSymfonyPostgreSQLDoctrine ORM