Return to Answer

added 675 characters in body

Source Link

edited Oct 22, 2016 at 18:35

To the already excellent answer of Robert Harvey, I'd like to add a couple of remarks:

It is a very bad database design to create a table for every post, or even a table for every user's post ! In a relational database, the structure should in principle be as determined as possible, and not be content dependent.
You should not worry about the size. DBMS are designed to cope with volume. They use special data structures and indexes for that. And they have optimizers to analyze the best way to execute a query based on the table structures and the exsiting indexes, and the relative size of the tables.
For instance InnoDB use b+trees for indexes. These are similar to binary trees but designed taking into account a paged access in a database file. To find any record among 37,5 billion requires maximum 11 disk accesses (if there are 10 index entries per file page, 6 accesses if there are 100 entries per page).
You need to look for an optimal structure and avoid redundant data. So it could makes sense to have a large table for the posts , and another for other events related to the posts (referring to the post's id). To be analyzed, but you could further distinguish the comments, from the likes and the shares, as these all enrighenrich the event data (user, time, related post) with different data (comment, degree of liking, etc.)

a database is stored in several tablespaces, each of which is composed of one or several files. According to the documetnation that you've referenced, the size of a table is limited by the maximum filesize of the operating system (It's smaler than the 64TB that you mention). If I understand correctly, the InnoDB has to be in a single tablespace, which puts an upper limit to all the tables in your scheme. Until you reach these limits, you'll have some time to think about a partitionning scheme (to distribute data between two or more databases), and perhaps you can then afford more powerful RDBMS (some have the limits at 64K tablespaces x 128 TB per tablespace).

To the already excellent answer of Robert Harvey, I'd like to add a couple of remarks:

It is a very bad database design to create a table for every post, or even a table for every user's post ! In a relational database, the structure should in principle be as determined as possible, and not be content dependent.
You should not worry about the size. DBMS are designed to cope with volume. They use special data structures and indexes for that. And they have optimizers to analyze the best way to execute a query based on the table structures and the exsiting indexes, and the relative size of the tables.
For instance InnoDB use b+trees for indexes. These are similar to binary trees but designed taking into account a paged access in a database file. To find any record among 37,5 billion requires maximum 11 disk accesses (if there are 10 index entries per file page, 6 accesses if there are 100 entries per page).
You need to look for an optimal structure and avoid redundant data. So it could makes sense to have a large table for the posts , and another for other events related to the posts (referring to the post's id). To be analyzed, but you could further distinguish the comments, from the likes and the shares, as these all enrigh the event data (user, time, related post) with different data (comment, degree of liking, etc.)

To the already excellent answer of Robert Harvey, I'd like to add a couple of remarks:

It is a very bad database design to create a table for every post, or even a table for every user's post ! In a relational database, the structure should in principle be as determined as possible, and not be content dependent.
You should not worry about the size. DBMS are designed to cope with volume. They use special data structures and indexes for that. And they have optimizers to analyze the best way to execute a query based on the table structures and the exsiting indexes, and the relative size of the tables.
For instance InnoDB use b+trees for indexes. These are similar to binary trees but designed taking into account a paged access in a database file. To find any record among 37,5 billion requires maximum 11 disk accesses (if there are 10 index entries per file page, 6 accesses if there are 100 entries per page).
You need to look for an optimal structure and avoid redundant data. So it could makes sense to have a large table for the posts , and another for other events related to the posts (referring to the post's id). To be analyzed, but you could further distinguish the comments, from the likes and the shares, as these all enrich the event data (user, time, related post) with different data (comment, degree of liking, etc.)

a database is stored in several tablespaces, each of which is composed of one or several files. According to the documetnation that you've referenced, the size of a table is limited by the maximum filesize of the operating system (It's smaler than the 64TB that you mention). If I understand correctly, the InnoDB has to be in a single tablespace, which puts an upper limit to all the tables in your scheme. Until you reach these limits, you'll have some time to think about a partitionning scheme (to distribute data between two or more databases), and perhaps you can then afford more powerful RDBMS (some have the limits at 64K tablespaces x 128 TB per tablespace).

Source Link

created Oct 22, 2016 at 17:54

To the already excellent answer of Robert Harvey, I'd like to add a couple of remarks:

It is a very bad database design to create a table for every post, or even a table for every user's post ! In a relational database, the structure should in principle be as determined as possible, and not be content dependent.
You should not worry about the size. DBMS are designed to cope with volume. They use special data structures and indexes for that. And they have optimizers to analyze the best way to execute a query based on the table structures and the exsiting indexes, and the relative size of the tables.
For instance InnoDB use b+trees for indexes. These are similar to binary trees but designed taking into account a paged access in a database file. To find any record among 37,5 billion requires maximum 11 disk accesses (if there are 10 index entries per file page, 6 accesses if there are 100 entries per page).
You need to look for an optimal structure and avoid redundant data. So it could makes sense to have a large table for the posts , and another for other events related to the posts (referring to the post's id). To be analyzed, but you could further distinguish the comments, from the likes and the shares, as these all enrigh the event data (user, time, related post) with different data (comment, degree of liking, etc.)