Re: changing MyDatabaseId

On Wed, Nov 17, 2010 at 5:04 AM, Markus Wanner <markus(at)bluegap(dot)ch> wrote:
> The question obviously is whether or not this is faster than just
> terminating one backend and starting a new one.

I agree.

> Which basically costs an
> additional termination and re-creation of a process (i.e. fork())
> AFAICS. Or what other savings do you envision?

I think the thing I'd like to do (or see somebody else do) is write a
test harness that connects to the database, runs a single SELECT *
FROM dual (one row, one column table), and then disconnects; and then
oprofile the daylights out of the backend processes. In other words,
I'd like to measure as exactly as we can the overhead of each part of
the startup process. I think that would give us a clearer picture of
where the overhead is, and then we could look more directly at which
line items might be avoidable for rebinding to a new database.

However, that test doesn't capture everything. For example, imagine a
connection pooler sitting in front of PG. Rebinding to a new database
means disconnecting a TCP connection and establishing a new one.
Switching databases might save some latency there even if we don't
actually save much in terms of CPU instructions. Maybe that's not
important, though. I don't know. I don't want to let my theorizing
get too far ahead of the data.

It also occurs to me to wonder whether there's some way that we can
speed up backend startup, period. One of the frequent complaints
levied against PostgreSQL is that our connections are too expensive.
AFAICT, this is partly because backend startup costs are high, and
partly because of internal contention, especially around
ProcArrayLock. Squeezing on the startup overhead will be valuable no
matter what we decide to do about database switching. A variant on
this theme would be to try to adjust the startup sequence in some way
so that less of it needs to be redone if we switch databases, which
might be possible even if a more general solution isn't.
Unfortunately, I'm not sure how feasible this is, but maybe there's a
way...

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