Re: New database or New Schema?

2009/8/25 Eric Comeau <ecomeau(at)signiant(dot)com>

> "David Fetter" <david(at)fetter(dot)org> wrote in message
> news:20090821170259(dot)GA6792(at)fetter(dot)org(dot)(dot)(dot)
> > On Fri, Aug 21, 2009 at 12:16:45PM -0400, Eric Comeau wrote:
> >> In the next release of our software the developers are moving to
> >> JBoss and have introduced the use of JBoss Messaging. They want to
> >> change from using the built-in hsqldb to using our PostgreSQL
> >> database.
> >>
> >> What is the best approach, create a new database or new schema
> >> with-in our current PostgreSQL database?
> >>
> >> I'm leaning toward creating a new schema as we already have built-in
> >> procedures to backup and restore the application database.
> >
> > It's difficult to tell from what you've described. If you were even
> > vaguely contemplating queries that touched both that database and
> > others you already have, it becomes much easier: use a schema.
>
> So to be so vague I was trying to keep the description short.
>
> I don't see that we will need to query data between the systems for any
> business type of operations. Potentially for troubleshooting.
>
> I was hoping to get more input on what the best-practices
> (benefits/disadvantages) of going with a new schema versus a new database.
>
> I don't really see any downside with going with a new schema in the
> application database, and since we already have built-in procedures (at the
> application layer) to back-up and restore the application database (using
> pg_dump) I don't believe they will need to change. Adding a new database, I
> need to update the backup process to either dump a second db (jboss_jms) or
> start using pg_dumpall.

New schema approach is generally OK.
But:

Take a closer look at usage patterns of this Java messaging system. I mean,
the way it uses Postgres database.
Do some testing.
Does it work by holding long-open transactions (I've seen such setups of
java based queueing services)?
Does it run huge amount of UPDATE/INSERT/DELETE queries?

These can be a PITA. For example, they can make autovacuum daemon useless,
or - in some extremal setups - they could cause transaction ID wraparound
issues.
They can also hurt if you are going to use trigger-based replication.

And finally: Postgres is a full-featured relational database with focus on
ACID. Does JMS actually need this? If it supports backend pairing (for
durability), maybe it's more efficient to use a pair of hsqldbs or mysqls.
I'd suggest some performance testing here.

So, depending on your real requirements and usage scenario, you have choices
ranging from extra schema to separate postgres instance to using something
else - because it's simply a better tool for the job.

HTH.

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/