Re: A few questions

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?
Do you want to backup and restore them separately?
Is any information shared between them?
What are the consequences of a user seeing other users' data?

2. Are you having performance issues with the most logical design?
Can you solve it by adding some more RAM/Disk?
What are the maintenance issues with not having the most logical design?

--
Richard Huxton
Archonet Ltd