Is it efficient to design chat/likes/relationship database in separate table per user?

The question on dba stackexchange, just posting here to get more expert’s view/opinion

it’s said that each table will use few KB per node, would it be problematic in the future if I design the tables that way?

CockroachDB is not yet optimized to support many different databases/tables. You can expect slowdowns when the total number of tables exceeds 1000-10000 tables. We would rather recommend you use tables with a “username” column that serves as prefix in the primary key and secondary indices.

Or better yet, use table partitioning by user (this is an enterprise feature).

Does this help?


1 Like

This is true for most of the database system, dynamic table creation/alter should be avoided as possible as it’s a quite intensive operations, sometime not transactional and doesn’t deal code with cache systems.

yeah thanks, i’ll try graph database now, since it’s the best for this use case.