Re: A few questions - Mailing list pgsql-general

On Oct 29, 2007, at 10:14 AM, Richard Huxton wrote:

> Samantha Atkins wrote:
>> First on prepared statements:
>> 1) If I am using the libpq are prepared statements tied to a
>> connection?  In other words can I prepare the statement once and
>> use it on multiple connections?
>
> Per session (connection).
>
> Temporary tables etc. are the same.
>
>> 2) What is the logical scope of prepared statement names?  Can I
>> use the same name on different tables without conflict or is the
>> scope database wide or something else?
>
> Per session.
>
>> On indices:
>> 3) same as 2 for index names.  I think they are per table but it is
>> worth asking.
>
> Per database (if you count the schema name). We don't have cross-
> table indexes, but the global naming allows it.
>
>> and last:
>> 4) Is it generally better to have more tables in one database from
>> a memory and performance point of view or divide into more
>> databases if there is a logical division.  The reason I ask is that
>> I have a situation where one app is used by multiple different
>> users each running their own copy.  The app uses on the order of 30
>> tables.  In some ways it would be convenient to have one big
>> database and specialize the table names per user.   But I am not
>> sure that is most optimal.  Is there a general answer to such a
>> question?
>
> Not really, but...
>
> 1. Do you treat them as separate logical entities?

A set of tables per a user, yes.  A app process is always for one and
only one user.

>
> Do you want to backup and restore them separately?

Not necessarily.  Although the is a possibility of wanting separate
per-user backups which would pretty much answer the question in this
specific case.

>
> Is any information shared between them?

Possible sharing of some common id numbers for common items.
Although it is not essential the common items have the same serial
number on different databases.

>
> What are the consequences of a user seeing other users' data?
>

Little likelihood unless we expose database username/passwd.   These
are "users" not necessarily represented as postgresql database users.

> 2. Are you having performance issues with the most logical design?

The first prototype has not yet been completed so no, not yet.  :-)

>
> Can you solve it by adding some more RAM/Disk?

???  There is a desire to use as little ram/disk as possible for the
application.   I would be interested in what the overhead is for
opening a second database.

>
> What are the maintenance issues with not having the most logical
> design?
>

What do you consider the most logical, one database per user?

- samantha