Date: Mon, 29 Oct 2007 10:07:19 -0700
From: David Fetter <david@fetter.org>
To: Samantha Atkins <sjatkins@mac.com>
Cc: pgsql-general@postgresql.org
Subject: Re: A few questions
Message-ID: <20071029170719.GE14638@fetter.org>
References: <D32C7D7E-6706-42A4-B957-A4019BF1FA35@mac.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <D32C7D7E-6706-42A4-B957-A4019BF1FA35@mac.com>
User-Agent: Mutt/1.5.14 (2007-02-12)

On Mon, Oct 29, 2007 at 09:52:55AM -0700, 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?

Yes they are, and no, you can't.  Because of MVCC, in general, each
connection could see a completely different database, so there's no
way for plans to cross connections.

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

What happened when you tried it?

> On indices:
> 
> 3) same as 2 for index names.  I think they are per table but it is
> worth asking.

See above question ;)

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

Just generally, get correctness first and improve performance if you
need to by finding bottlenecks empirically and figuring out what to do
once you've identified them.

> 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.
                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
That's a loud warning of a design you didn't think through ahead of
time.  As a general rule, a day not spent in design translates into at
least 10 in testing (if you're lucky enough to catch it there) or
(more usually) 100 or more in production.

> But I am not sure that is most  optimal.  Is there a general answer
> to such a question?

See above :)

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate