X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 66912D919D for ; Fri, 4 Nov 2005 20:03:56 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 56016-01 for ; Sat, 5 Nov 2005 00:03:50 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by svr1.postgresql.org (Postfix) with ESMTP id 46966D9BDA for ; Fri, 4 Nov 2005 20:03:50 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id A29B63105B; Sat, 5 Nov 2005 01:03:53 +0100 (MET) From: Christopher Browne X-Newsgroups: pgsql.sql Subject: Re: Design question: Scalability and tens of thousands of tables? Date: Fri, 04 Nov 2005 18:59:08 -0500 Organization: cbbrowne Computing Inc Lines: 33 Message-ID: References: <200511041459.jA4ExAAY009623@www.commandprompt.com> <0B8DCA82-39EE-4444-9412-20779E51CCDD@socialserve.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Jumbo Shrimp, berkeley-unix) Cancel-Lock: sha1:ct31t1WzUvIr+joCzkswRJhlVNI= To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.497 required=5 tests=[AWL=-0.316, INFO_TLD=0.813] X-Spam-Score: 0.497 X-Spam-Level: X-Archive-Number: 200511/58 X-Sequence-Number: 23309 > On Nov 4, 2005, at 9:47 AM, pgsql-sql-owner@postgresql.org wrote: > >> The problem is I am very concerned about scalability with having a >> different >> table created for each custom object. I want to design to site to >> handle >> tens of thousands of users. If each user has 3-5 custom objects the >> database would have to handle tens of thousands of tables. >> >> So it would appear that this is a broken solution and will not >> scale. Has >> anyone designed a similar system or have ideas to share? > > Well, before you discount it, did you try out your design? You could > do initial segregation of user's tables into separate schemas (say, > schema 'a' -> 'z' according to username or some better hashing > routine like brute-force round-robin assignment at user creation > time). Assignment of objects -> schema would be one additional column > in your centralized user directory table(s). I don't imagine this would help much with the *true* problem, which is that a lot of queries inside the DB would have tens of thousands of tables to go thru rather than (say) hundreds. If you have 10,000 tables, that means 10,000 entries in pg_class. Associating them with 26 (or 260) namespaces does nothing to assist any queries that still have to scan through 10K pg_class entries. -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://linuxfinances.info/info/wp.html "Whenever you find that you are on the side of the majority, it is time to reform." -- Mark Twain