Thread: Query regarding PostGre database

On 08/17/11 8:52 PM, Navin Chandra wrote:
> I am an application developer, want to use ‘PostGre’ as backend.

Please note, there is no such thing as PostGre. There is PostgreSQL,
sometimes called Postgres for short.

> May I know what is the maximum possible number of concurrent users?

A better question is, whats the optimal number of concurrent query
operations that can be executed simultaneously to achieve maximum
throughput. Under most conditions, you'll get MORE queries per second
total throughput if you only try and execute between N and 2*N queries
at a time. where N is the number of CPU cores or hardware threads you
have, assuming your storage subsystem has sufficient performance for
your I/O workload.

With connecting pooling, like pg_bouncer, you could have 1000s of
concurrent users on a reasonable sized server, as long as most of them
are idle most of the time. I don't typically allow more than 100 or so
actual connections to a given SQL server if I can avoid it by the use of
pooling.


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

On Wed, Aug 17, 2011 at 9:52 PM, Navin Chandra
<navin.pandit@ilfstechnologies.com> wrote:
> Hi,
>
> I am an application developer, want to use ‘PostGre’ as backend. May I know
> what is the maximum possible number of concurrent users?
> Your acknowledgement will be highly appreciated.

FYI, we call it PostgreSQL or pgsql around here.

The maximum number you can create is much more than the maximum number
you like want to create.  What are you looking at doing?  Might
connection pooling be a good match for that?

I have session db servers that keep ~800 persistent connections open
for small single table queries and they do just fine.  I've tested
thousands of connections with decent performance.  However having lots
of open connections can lead to "thundering herd" problems and is
generally suboptimal.

On 18/08/2011 11:52 AM, Navin Chandra wrote:
> Hi,
>
> I am an application developer, want to use ‘PostGre’ as backend. May I
> know what is the maximum possible number of concurrent users?
> Your acknowledgement will be highly appreciated.

You seem to have replied to yourself. Eh?

There's no such thing as "PostGre". You probably meant PostgreSQL or
just Postgres.

The maximum number of concurrent users depends on the hardware and the
database workload. It is impossible to answer without more information.

There is a difference between the maximum number of connections and the
maximum number of actively working queries. You can have lots of
connections, but much fewer actively running queries if you want the
system to perform well. As a rule of thumb, we usually say that the
number of connections that should be doing work is roughly

   (number of CPU cores) + (number of hard drives)

... but of course that's incredibly vague and depends a huge amount on
your workload and hardware specifics. For example, in some workloads the
hard drives don't matter at all, it's only the CPUs and amount of memory.

If you find that your PostgreSQL instance cannot handle enough
connections, you can add a connection pooler like PgPool-II, or use your
application server's connection pooler. Using fewer connections and a
pooler is usually faster than using lots of direct connections to
PostgreSQL anyway.

Perhaps you can explain a bit more about what you want to do so we can
help you in more detail?

--
Craig Ringer