I have a table with three columns: user_id, post_id and voting. Each post in the application can get a voting by multiple users but only one voting per user.

Imagine we end up with 1 billion posts and an average of 1000 votings on each. Would it still be a good practice to store all these information in a single table? (performancewise) Is it maybe better to split the information in different tables, maybe userwise? (potentially multiple thousand users, so multiple tables)

CodePudding user response：

For best practices create a non-clustered index on user_id the reason I say this is it will help you to run operations like - get all posts of a particular user etc.

If in future record is very big like half to what you mentioned you can do DB shading it will improve overall performance. The idea is to distribute data that can’t fit on a single node onto a cluster of database nodes. You can refer to your vendor's documentation to achieve these.