|
2009/8/25 Eric Comeau <ecomeau@xxxxxxxxxxxx>
"David
Fetter" <david@xxxxxxxxxx> wrote
in message
news:20090821170259.GA6792@xxxxxxxxxxxxx
> 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
|
