Re: Synchronized snapshots versus multiple databases

On Fri, Oct 21, 2011 at 2:06 PM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
> On Oct21, 2011, at 19:47 , Robert Haas wrote:
>> On Fri, Oct 21, 2011 at 1:40 PM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
>>> AFAIR, the performance hit we'd take by making the vacuum cutoff point
>>> (i.e. GetOldestXmin()) global instead of database-local has been repeatedly
>>> used in the past as an against against cross-database queries. I have to
>>> admit that I currently cannot seem to find an entry in the archives to
>>> back that up, though.
>
>> I haven't seen anyone explain why they really need this feature
>> anyway, and I think it's going in the wrong direction.  IMHO, anyone
>> who wants to be doing cross-database queries should be using schemas
>> instead, and if that's not workable for some reason, then we should
>> improve the schema implementation until it becomes workable.  I think
>> that the target use case for separate databases ought to be
>> multi-tenancy, but what is needed there is actually more isolation
>> (e.g. wrt/role names, cluster-wide visibility of pg_database contents,
>> etc.), not less.
>
> Agreed. I wasn't trying to argue for cross-database queries - quite the opposite,
> actually. My point was more that since we've used database isolation as an
> argument against cross-database queries in the past, we shouldn't sacrifice
> it now for synchronized snapshots.

Right, I agree. It might be nice to take a cluster-wide dump that is
guaranteed to be transactionally consistent, but I bet a lot of people
would actually be happier to see us go the opposite direction - e.g.
give each database its own XID space, so that activity in one database
doesn't accelerate the need for anti-wraparound vacuums in another
database. Not sure that could ever actually happen, but the point is
that people probably should not be relying on serializability across
databases too much, because the whole point of the multiple databases
feature is to have multiple, independent databases in one cluster that
are thoroughly isolated from each other, and any future changes we
make should probably lean in that direction.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company