and it got me thinking. If I wanted to set up my app so that each
user has his/her own table, how would I go about doing that?
“create_table :users” just creates a table in the database for a list
of all the users (which I guess I’d want in addition to their
individual tables), but is there a way to make it create an new table
for each user? Would this even be the best way if my app allowed
“followers?” Admittedly there’d be a ton of tables in my database,
but wouldn’t it make it easier for indexing all of your followers
messages?
“followers?” Â Admittedly there’d be a ton of tables in my database,
but wouldn’t it make it easier for indexing all of your followers
messages?
Any insight is greatly appreciated.
Why not just put the information in the users table? If you think
this is not an appropriate solution give more details of the
requirement so we can see what you are trying to do.
I would, but that’s exactly what that article was saying to steer
clear of, and it makes sense. If you have to keep querying the same
table for everyone’s new posts and post them back to your “feed” of
the people that you follow, wouldn’t that cause serious bottlenecks
and lead to outages? (which is exactly what I think is/was happening
with twitter)
And right now I’m just at the hypothetical stage as I’m still learning
rails, so I don’t have any constraints or requirements of what I’m
trying to do. I was just saying if my app was heavily intensive on
user read/writes so that other users could follow other users,
wouldn’t it be best to give each their own table?
I would, but that’s exactly what that article was saying to steer
clear of, and it makes sense. If you have to keep querying the same
table for everyone’s new posts and post them back to your “feed” of
the people that you follow, wouldn’t that cause serious bottlenecks
and lead to outages? (which is exactly what I think is/was happening
with twitter)
I think you misread the article - I don’t think it is suggesting one
table per user.
it’s saying
select * from tweet_queue where tweet_queue.user_id = 123456 AND …
rather than
select * from tweets
inner join followings on followings.followee = tweets.user_id
where followings.follower_id = 12456 AND …
rails, so I don’t have any constraints or requirements of what I’m
trying to do. I was just saying if my app was heavily intensive on
user read/writes so that other users could follow other users,
wouldn’t it be best to give each their own table?
One thing the article made clear was that the design of having a users
table makes sense in a lot of situations, just not all. The soution
they outline is very specific to twitter.
Alto bear this in mind, their system breaks down a little when you
decide to follow another user, you then have to find all (all of the
historical, just the last day? just the last 100? what?) their posts,
and insert them into your private post stream, which could get quite
intensive. Granted it doesn’t happen as often as searching, or posting,
but it could have a big impact.
To answer your question though. You would probably have a users table,
and that would have a one to many relation to entries in a
_stream table. The logic that creates the user in the database
would first create the _stream table, and then create the
actual user. You would need a fair bit of reflection on the ruby side to
get that to work, as their wouldn’t be anything in the DB to directly
connect the two tables.
Which sounds a bit fishy to me. Although I guess you could have
<user.id>_stream as the table name, which is a little less ambiguous.
I was reading this article a while back Did Rails Sink Twitter? — SitePoint
and it got me thinking. If I wanted to set up my app so that each
user has his/her own table, how would I go about doing that?
You wouldn’t. It’s a horrible idea, and whoever wrote the article
doesn’t appear to understand good database design. There are other ways
to achieve good performance.
@Frederick C.: Yeah, I just meant that article got me thinking
about my database setup for my users and not that I was trying to
mirror it.
On Oct 19, 9:22 am, Marnen Laibow-Koser <rails-mailing-l…@andreas- s.net> wrote:
AlwaysCharging wrote:
I was reading this article a while back Did Rails Sink Twitter? — SitePoint
and it got me thinking. If I wanted to set up my app so that each
user has his/her own table, how would I go about doing that?
You wouldn’t. It’s a horrible idea, and whoever wrote the article
doesn’t appear to understand good database design. There are other ways
to achieve good performance.
One thing the article made clear was that the design of having a users
table makes sense in a lot of situations, just not all. The soution
they outline is very specific to twitter.
Alto bear this in mind, their system breaks down a little when you
decide to follow another user, you then have to find all (all of the
historical, just the last day? just the last 100? what?) their posts,
and insert them into your private post stream, which could get quite
intensive. Granted it doesn’t happen as often as searching, or posting,
but it could have a big impact.
My thought would be that you’d just be following then from that point
forward (so no need for past data). However, that user’s past data
could still be availabe on their profile if you cared to look through
it, but you’d only be subscribing to their new posts.
To answer your question though. You would probably have a users table,
and that would have a one to many relation to entries in a
_stream table. The logic that creates the user in the database
would first create the _stream table, and then create the
actual user. You would need a fair bit of reflection on the ruby side to
get that to work, as their wouldn’t be anything in the DB to directly
connect the two tables.
This is what I was afraid of.
Which sounds a bit fishy to me. Although I guess you could have
<user.id>_stream as the table name, which is a little less ambiguous.
Yeah that doesn’t seem so bad. So instead of having to query a giant
table of everyone’s posts, you’d only be querying the <user.id>_stream
tables of all the users you’re following. This makes sense in my
hypothetical mind, but the way to implement it I wasn’t sure.
Yeah that doesn’t seem so bad. Â So instead of having to query a giant
table of everyone’s posts, you’d only be querying the <user.id>_stream
tables of all the users you’re following. Â This makes sense in my
hypothetical mind, but the way to implement it I wasn’t sure.
Sorry, but it looks like you are trying to solve the problem you
do not have yet, and do that by choosing most inappropriate means.
A couple of well designed tables will do just fine for a long long long
run.
Thousands of tables will give you maintenance nightmare pretty soon
not to mention the strain this kind of solution would put on OS and
file I/O.
When (if) the need arises you may want to revise your DB design,
maybe introduce de-normalization/partitioning/sharding, maybe
take a look on solutions involving memcahced/tokyo tyrrant or similar
technologies.
Alas, one table per user is not the solution you are looking
for. I’d say it is not the solution for ANY problem. This kind of
design is a problem itself.
So for now I’d suggest to build “the most simple thing that
works”, then invest some time in learning about database design,
factors which limit DB performance, indexing strategies, for starters.
And by the way, I believe Twitter problems had nothing to do with
rails, it was their message queue system that had problems.
On Oct 19, 9:22�am, Marnen Laibow-Koser <rails-mailing-l…@andreas- s.net> wrote:
AlwaysCharging wrote:
I was reading this article a while back Did Rails Sink Twitter? — SitePoint
and it got me thinking. �If I wanted to set up my app so that each
user has his/her own table, how would I go about doing that?
You wouldn’t. �It’s a horrible idea, and whoever wrote the article
doesn’t appear to understand good database design. �There are other ways
to achieve good performance.
Umm… and examples of that would be … ?
Decently normalized tables, proper use of indices, perhaps some DB
performance tuning. Basically, remember that DB servers were designed
for problems of this type, without kludgy solutions such as we’re
discussing.
And Rimantas is right, you seem to be trying to prematurely optimize.
Learn about the standard methods of database design before you try weird
things like this.