Re: A few questions - Mailing list pgsql-general

Samantha  Atkins wrote:
>
> 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.

OK, so no data-sharing.

>> 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.

Yep. Or if you want to prevent other users knowing that they share a
database.

>> 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.

Ah, but with separate databases they can (and might as well be) separate
  db users. It's the simplest way to guarantee no data leakage.

If you have only one db, then you'll want to have separate tables for
each user, perhaps in their own schema or a column on each table saying
which row belongs to which user. It's easier to make a mistake here.

>> 2. Are you having performance issues with the most logical design?
>
> The first prototype has not yet been completed so no, not yet.  :-)

Good. In that case, I recommend going away and mocking up both, with
twice as many users as you expect and twice as much data. See how they
operate.

>> 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.

Don't forget there might well be a trade-off between the two. Caching
results in your application will increase requirements there but lower
them on the DB.

 > I would be interested in what the overhead is for opening
> a second database.

Not much. If you have duplicated data, that can prove wasteful.

Otherwise it's a trade off between a single 100MB index and 100 1MB
index and their overheads. Now, if only 15 of your 100 users log in at
any one time that will make a difference too. It'll all come down to
locality of data - whether your queries need more disk blocks from
separate databases than from larger tables in one database.

>> What are the maintenance issues with not having the most logical design?
>
> What do you consider the most logical, one database per user?

You're the only one who knows enough to say. You're not sharing data
between users, so you don't need one database. On the other hand, you
don't care about backing up separate users, which means you don't need
many DBs.

Here's another question: when you upgrade your application, do you want
to upgrade the db-schema for all users at once, or individually?

Write a list of all these sort of tasks - backups, installations,
upgrades, comparing users, expiring user accounts etc. Mark each for how
  often you'll have to deal with it and then how easy/difficult it is
with each design. Total it up and you'll know whether you want a single
DB or multiple.

Then come back and tell us what you decided, it'll be interesting :-)

--
   Richard Huxton
   Archonet Ltd