What is the practical limitation of no multi-threading?

OK I admit it, I am a postgresql newbie. The developers FAQ says the
postgresql "backend" is not multi-threaded. How serious is this?

Not serious at all... PostgreSQL is not multi-threaded, but it does take
advantage of multiple processes. Each connection spawns a new backend
process that run concurrently with other backend processes.

If this means only one transaction at a time can make progress or the
entire DBMS server is blocked on every disk I/O then that is very bad.

One transaction per connection. But you can have quite a large number of
connections, each with their own process.

But if every connection gets its own backend, and the backends can run
in parallel then not being multithreaded is no big deal.

Exactly.

Here is the bottom line. Our app runs on multiple machines (we call
them sinks) hitting one DBMS server for data. We expect the sinks to
be able to all make progress at the same time. Each sink fetchs
some data, goes compute bound for .2 to .4 seconds, and then
sends a bunch of INSERT/UPDATE/DELETE to the DBMS.
Will our sinks be able to run concurrently?

Yes, breathe easy :)

Greg

Every connection gets its own backend. Concurrent connections work
fine, and transactions can progress concurrently as long as the rows
that you want to update are unique.

Basically, the fact that PostgreSQL is not multi-threaded is not a big
deal (unless you want to run it on Windows or some other operating
system with a massively expensive fork()).

Jason

Brian Beuning <bbeuning@mindspring.com> writes:

Not being multithreaded may only be a big deal if your application connects
and disconnects from the database at high rates. In that situation, such an
application would suffer the poorer performance of multiprocess servers like
Postgres since it is significantly more time consuming to copy the memory
management unit (mmu) state, or at least to load a new program image (the
backend), than with threads which share mmu state and program image.

Bob

"Robert B. Easter" <reaster@comptechnews.com> writes:

Not being multithreaded may only be a big deal if your application connects
and disconnects from the database at high rates. In that situation, such an
application would suffer the poorer performance of multiprocess servers like
Postgres since it is significantly more time consuming to copy the memory
management unit (mmu) state, or at least to load a new program image (the
backend), than with threads which share mmu state and program image.

Absolutely true, but in such a case you should be using connection
pooling on the client side anyhow, as even threaded commercial
databases (Oracle/Informix etc) have a lot of connection startup
overhead.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

Not being multithreaded may only be a big deal if your application connects
and disconnects from the database at high rates. In that situation, such an
application would suffer the poorer performance of multiprocess servers like
Postgres since it is significantly more time consuming to copy the memory
management unit (mmu) state, or at least to load a new program image (the
backend), than with threads which share mmu state and program image.

Let me mention that we don't load new executables on backend startup.
We merely fork() the postmaster. I just checked the FAQ and it seems
pretty clear. Please let me know if there is additional info I should
have there.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I see, there is a postmaster -> postgres symlink! In main/main.c it looks at
argv[0] to see how it is being started and calls the right function,
PostmasterMain() or PostgresMain() - everything is linked into postgres. I
never looked before. Next, I should try to understand its IPC, shared memory,
and other multiprocess details. The source comments and code I've seen so far
are very readable. I'll take a look at some of the FAQs too! :-)) thx

Bob