Re: PostgreSQL pre-fork speedup

On Thu, 6 May 2004, Tom Lane wrote:

> sdv mailer <sdvmailer(at)yahoo(dot)com> writes:
> > The point is pre-forking can *potentially* speed up
> > connections by 5x as shown in this simplistic
> > non-conclusive benchmark.
>
> I think this "benchmark" proves no such thing.
>
> The thing that pgpool is doing is not preforking connections at all, but
> re-using prior connections. The important difference is that you are
> using a "hot" backend that has already loaded a full working set of
> relcache and syscache entries --- and not just any old entries, but
> exactly those needed to process your query. (The fact that the pgbench
> test uses only a very limited set of queries probably causes this test
> to overstate the effect compared to more realistic workloads.)
>
> The profiling that I've done of backend startup shows that cache
> initialization accounts for the bulk of the startup delay. And IIRC,
> I was just measuring the time needed to be ready to accept the first
> query, not the additional effort to fetch query-specific cache entries.
> So having a hot backend would make a significant difference, but merely
> avoiding the fork wouldn't necessarily.

Wouldn't the db selection / authentication be more / as expensive as
buffer creation? Even in trust mode the backend still has to
"authenticate" it just doesn't have to do as much to do that as with
passwords. I'd expect that to be a big chunk of time too.

It appears the best place to fix this "problem" (not a problem with
postgresql, but an engineering problem in an abstract sense) is with
pooling, and once the flushing etc... in tatsuo's code is fixed up to be
zippy, pgpool would be THE answer for such issues.