Thread: New database or New Schema?

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.

Thanks,
Eric

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.

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

"David Fetter" <david@fetter.org> wrote in message
news:20090821170259.GA6792@fetter.org...
> 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.

Eric

""Filip Rembiałkowski"" <plk.zuber@gmail.com> wrote in message news:92869e660908250856g62561141n557f88015cd2105c@mail.gmail.com...

2009/8/25 Eric Comeau <ecomeau@signiant.com>

"David Fetter" <david@fetter.org> wrote in message
news:20090821170259.GA6792@fetter.org...

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