Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 345D2B5DBF3 for ; Sun, 7 Aug 2011 18:38:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 04349-08 for ; Sun, 7 Aug 2011 21:38:12 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pz0-f42.google.com (mail-pz0-f42.google.com [209.85.210.42]) by mail.postgresql.org (Postfix) with ESMTP id C0B8FB5DBF2 for ; Sun, 7 Aug 2011 18:38:10 -0300 (ADT) Received: by pzk37 with SMTP id 37so1890579pzk.15 for ; Sun, 07 Aug 2011 14:38:09 -0700 (PDT) Received: by 10.142.128.21 with SMTP id a21mr3893431wfd.371.1312753089617; Sun, 07 Aug 2011 14:38:09 -0700 (PDT) Received: from [10.1.1.8] (124-169-181-228.dyn.iinet.net.au [124.169.181.228]) by mx.google.com with ESMTPS id t7sm3220184wfl.14.2011.08.07.14.38.07 (version=SSLv3 cipher=OTHER); Sun, 07 Aug 2011 14:38:09 -0700 (PDT) Message-ID: <4E3F05BA.6010708@ringerc.id.au> Date: Mon, 08 Aug 2011 05:38:02 +0800 From: Craig Ringer User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:5.0) Gecko/20110624 Thunderbird/5.0 MIME-Version: 1.0 To: Ivan Sergio Borgonovo CC: pgsql-general@postgresql.org Subject: Re: Pg multi-tenanted hard? was: FREE hosting platforms with PostgreSQL, Java SDK, Tomcat, ecc.? References: <4E3E87F7.7040001@ringerc.id.au> <20110807174337.69848d17@dawn.webthatworks.it> In-Reply-To: <20110807174337.69848d17@dawn.webthatworks.it> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201108/198 X-Sequence-Number: 178480 On 7/08/2011 11:43 PM, Ivan Sergio Borgonovo wrote: > On Sun, 07 Aug 2011 20:41:27 +0800 > Craig Ringer wrote: > >> By the way, one of the reasons you're not finding much free >> hosting for PostgreSQL is that it takes a fair bit of work to run >> Pg multi-tenanted. Your additional requirement for Java and Tomcat > Why should pg be harder than anything else? Before I go on: I'm not running a hosting provider. Most of this is gathered from time on the lists, plus my own observations from using Pg in situations where I want to prevent different apps sharing a DB from affecting each other. Given that qualification: Things that contribute to making it harder to offer multi-tenanted database hosting services with Pg include: The global catalogs, in particular global user lists; relatively limited options for automatic load limitation and prioritisation; difficulty of preventing certain classes of effective denial of service by authorized users; shared WAL making it harder to isolate load and limit one DB's effects on another; difficulties completely hiding users from each other; the need to tune autovacuum to make sure it keeps up with load; etc. In Pg, DBs in a cluster affect each other in various ways, but because of the pinned-in-RAM nature of shared_buffers its hard to run one cluster per user as would be ideal. This doesn't mean you can't use Pg for multi-tenanted DB hosting. It just makes it harder, and means its more sysadmin work, therefore more time and cost. There are DBs - mostly $OMFGLOTS DBs - that offer much greater facilities for controlling access and isolating load than Pg does and are better suited to multi-tenanted use. Of course, their costs are such that it's probably cheaper to hire an army of sysadmins to run a giant cluster of Pg boxes instead... -- Craig Ringer