RDBMS selection

M

Martin Gregorie

I'm about to write a database app using Java/JDBC The DB requirements
are pretty straight: transactions, foreign keys, RI, soft DBK
generation, cursors but no need for stored procedures or triggers.

The app will consist of a scheduled update that runs every day for
minutes rather than hours and an ad-hoc query that will be used rather
less often with little or no overlap with the update task. IOW, the
database will be idle much longer than it will be busy.

I'm intending to use either Postgresql or Derby and I'm interested in
the resource consumption of these two when they are idle:
- Postgresql runs as a permanent daemon, so does it absorb RAM or cycles
when its idle or does it swap out entirely?
- Derby can run as a permanent daemon but it can also be used as part of
the app, which would not use any resource when idle but startup and
shutdown time becomes an issue

Any feedback of experience round this area would be helpful.

BTW, this is posted to both c.l.j.p and u.c.o.l because both NGs have
knowledgeable members on this topic but, I suspect, little overlap
amongst their regulars.
 
S

Simon Brooke

Martin Gregorie said:
I'm about to write a database app using Java/JDBC The DB requirements
are pretty straight: transactions, foreign keys, RI, soft DBK
generation, cursors but no need for stored procedures or triggers.

The app will consist of a scheduled update that runs every day for
minutes rather than hours and an ad-hoc query that will be used rather
less often with little or no overlap with the update task. IOW, the
database will be idle much longer than it will be busy.

The answer is write your application for as near as possible ANSI 92 SQL,
and use JDBC. Then you can try both Postgres and Derby solutions and
choose which one seems better to you.

My preference is Postgres (and has been for twelve years now) - but I write
all my SQL source files using a set of text macros which cover all the
main areas where databases typically diverge from ANSI 92, so that it's
exceedingly easy to switch to a different backend.

http://pres.cvs.sourceforge.net/pres/pres/src/sql/babelql.pp?revision=1.5&view=markup

Doing anything which hardwires your dependence on a particular database
engine is a bad idea, in my opinion.

--
(e-mail address removed) (Simon Brooke) http://www.jasmine.org.uk/~simon/

;; I can't work yanks out......
;; Why do they frown upon sex yet relish violence?
;; Deep Fried Lettuce
 
M

Martin Gregorie

Simon said:
>
Doing anything which hardwires your dependence on a particular database
engine is a bad idea, in my opinion.
Thanks for your comments. I know ODBC tolerably well, but this will be
my first JDBC project.

I've been intending to do the typical ODBC trick of using a common SQL
subset from within the Java programs and accepting that DBMS management
scripts and the schema definition would probably not be particularly
portable.

I'll start off using Postgres (it was installed when I installed FC6).
I've been reading its manuals and I'm impressed with what I've seen so far.
 
T

Tim Southerwood

Martin said:
Thanks for your comments. I know ODBC tolerably well, but this will be
my first JDBC project.

I've been intending to do the typical ODBC trick of using a common SQL
subset from within the Java programs and accepting that DBMS management
scripts and the schema definition would probably not be particularly
portable.

I'll start off using Postgres (it was installed when I installed FC6).
I've been reading its manuals and I'm impressed with what I've seen so
far.

Postgres over JDBC seems to work reliably IME so it's a fairly safe bet.

Read up on pg_dump so you can get your database back when you trask it :)

Cheers

Tim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top