Re: New database or New Schema?

""Filip Rembiałkowski"" <plk(dot)zuber(at)gmail(dot)com> wrote in message news:92869e660908250856g62561141n557f88015cd2105c(at)mail(dot)gmail(dot)com(dot)(dot)(dot)

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.
Thanks Filip id does, you bring up some very important issues to consider that I have run into in the "wild" with our own product.

I'll send a note off to the "PM" types that we need to allocate some more testing for this to the project plan. The problem is, they won't and unless I do it on my own, when it hits the wild and causes an issue I'll be the one holding the bag fixing the bloated DB with the customer.

Eric