Hi, schema and table metadata is kept fully in memory
I see the problem. For instance, how much memory would require (approximately) 10.000 schemes x 10 tables = 110.000 object medata? Would that magnitude order be of Megas or Gigas? Megas might be acceptable. Gigas, hmm... Regards, Pablo. 2013/9/21 Thomas Mueller <[email protected]> > Hi, > > You are right of course. The problem I see is that you might end up with a > lot of schemas and a lot of tables, if you try to keep all data in one > database. For H2, schema and table metadata is kept fully in memory, I'm > not sure about other database engines. And at some point you might want to > keep some data that applies to all users. > > Regards, > Thomas > > > On Sat, Sep 21, 2013 at 9:19 AM, Pablo Beltran <[email protected]>wrote: > >> Hi Thomas, >> >> You are right, many schemas implies many tables. >> >> What I like of the may schemas approach (one per user) is that I can >> partition the data very neatly and resolve the data isolation simply with >> the connection: >> >> public Connection getConnection(String userId){ >> Connection conn = pool.getConnection(); >> * conn.createStatement().execute("set schema USER_"+userId);* >> return conn; >> } >> >> The code above ensures that the user views only his tables (and data) by >> default. >> >> I'm not expert and you know much better the database internals, how it >> scales and how to prevent future problems... I'll change my current model >> (many schemas) to a single schema. >> >> Regards, >> Pablo >> >> 2013/9/21 Thomas Mueller <[email protected]> >> >>> Hi >>> >>> You could do both, but I would use one schema. (the title of the mail is >>> incorrect: many schemas is many tables, which is what I would avoid). >>> >>> Regards, Thomas >>> Am 12.09.2013 17:54 schrieb "Pablo Beltran" <[email protected]>: >>> >>>> Hi, >>>> >>>> I need to store lot of data (even millions of records) on an small >>>> bunch of tables for many users (thousands). What would be would be the best >>>> approach? >>>> >>>> 1. Create one schema per user, so each user would have its own private >>>> table: select * from USER_N.TABLE_X >>>> >>>> 2. Create one table shared by all the users and add a new column to >>>> identify the data by user: select * from PUBLIC.TABLE_X where user=N; >>>> >>>> Thanks! >>>> Pablo. >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "H2 Database" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> >>>> To post to this group, send email to [email protected]. >>>> Visit this group at http://groups.google.com/group/h2-database. >>>> For more options, visit https://groups.google.com/groups/opt_out. >>>> >>> -- >>> You received this message because you are subscribed to a topic in the >>> Google Groups "H2 Database" group. >>> To unsubscribe from this topic, visit >>> https://groups.google.com/d/topic/h2-database/jp_fVH9XqXU/unsubscribe. >>> To unsubscribe from this group and all its topics, send an email to >>> [email protected]. >>> >>> To post to this group, send email to [email protected]. >>> Visit this group at http://groups.google.com/group/h2-database. >>> For more options, visit https://groups.google.com/groups/opt_out. >>> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/groups/opt_out. >> > > -- > You received this message because you are subscribed to a topic in the > Google Groups "H2 Database" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/h2-database/jp_fVH9XqXU/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/groups/opt_out.
