I was reading this article a while back http://www.sitepoint.com/blogs/2008/06/06/did-rails-sink-twitter/ 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? Any insight is greatly appreciated.
2009/10/19 AlwaysCharging <goodgets-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > I was reading this article a while back > http://www.sitepoint.com/blogs/2008/06/06/did-rails-sink-twitter/ > 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? > > 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. Colin
Thank you for the reply. 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? On Oct 19, 4:53 am, Colin Law <clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> 2009/10/19 AlwaysCharging <goodg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: > > > > > I was reading this article a while back > >http://www.sitepoint.com/blogs/2008/06/06/did-rails-sink-twitter/ > > 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? > > > 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. > > Colin
AlwaysCharging wrote:> Thank you for the reply. > > 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? > >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 <username>_stream table. The logic that creates the user in the database would first create the <username>_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. Anthony
On Oct 19, 10:38 am, AlwaysCharging <goodg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Thank you for the reply. > > 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 ... Fred> 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? > > On Oct 19, 4:53 am, Colin Law <clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: > > > > > 2009/10/19 AlwaysCharging <goodg...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: > > > > I was reading this article a while back > > >http://www.sitepoint.com/blogs/2008/06/06/did-rails-sink-twitter/ > > > 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? > > > > 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. > > > Colin
AlwaysCharging wrote:> I was reading this article a while back > http://www.sitepoint.com/blogs/2008/06/06/did-rails-sink-twitter/ > 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. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
@Frederick Cheung: 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 > >http://www.sitepoint.com/blogs/2008/06/06/did-rails-sink-twitter/ > > 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 ... ?
> 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 > <username>_stream table. The logic that creates the user in the database > would first create the <username>_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. Regards, Rimantas -- http://rimantas.com/
AlwaysCharging wrote: [...]> > 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 >> >http://www.sitepoint.com/blogs/2008/06/06/did-rails-sink-twitter/ >> > 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. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.