Derby vs PostgreSql Performance Comparison

Question

We are doing research right now on whether to switch our postgresql db to an embedded Derby db. Both would be using glassfish 3 for our data layer. Anybody have any opinions or knowledge that could help us decide?

Thanks!

edit: we are writing some performance tests ourselves right now. Looking for answers more based on experience / first hand knowledge

Pietje

My prediction: properly tuned PostgreSQL will smoke Derby at all but the most trivial queries. Why do you want to move away from PostgreSQL? Have you spent the time to properly tune your database? – Mccubbin

Answer 1

I know I'm late to post an answer here, but I want to make sure nobody makes the mistake of using Derby over any production-quality database in the future. I apologize in advance for how negative this answer is - I'm trying to capture an entire engineering team's ill feelings in a brief Q&A answer.

Our experience using Derby in many small-ish customer deployments has led us to seriously doubt how useful it is for anything but test environments. Some problems we've had:

• Deadlocks caused by lock escalations - this is the biggest one and happens to one customer about once every week or two
• Interrupted I/Os cause Derby to fail outright on Solaris (may not be an issue on other platforms) - we had to build a shim to protect it from these failures
• Can't handle complicated queries which MySQL/PostgreSQL would handle with ease
• Buggy transaction log implementation caused a table corruption which required us to export the database and then re-import it (couldn't just drop the corrupted table), and we still lost the table in the process - thank goodness we had a backup
• No LIMIT syntax
• Low performance for complicated queries
• Low performance for large datasets

Due to the fact that it's embedded, Derby is more of a competitor to SQLite than it is to PostgreSQL, which is an extremely mature production-quality database which is used to store multi-petabyte datasets by some of the largest websites in the world. If you want to be ready for growth and don't want to get caught debugging someone else's database code, I would recommend not using Derby. I don't have any experience with SQLite, but I can't imagine it being much less reliable than Derby has been for us and still being as popular as it is.

In fact, we're in the process of porting to PostgreSQL now.

Esophagus

This comment appears very biased. I have made no such experience at all. I am using it as a production database for several years without any of the problems mentioned. But then my experience is biased as well. Make of it what you will. Also, Derby implements the SQL ISO Standard for LIMIT: en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause – Cavendish

Answer 2

Derby still is relatively slow in performance, but ... where ever your Java application goes your database server goes, completely platform independent. You don't even need to think about installing a DB server where your Java app is being copied to.

I was using MySQL with Java, but having an embedded implementation of your Database server sitting right within my Java App is just stunning and unprecedented productivity, freedom and flexibility.

Always having a DB server included whenever and wherever on any platform for me is just heaven !!!

Renaldorenard

Answer 3

Have not compared Postgresql to Derby directly. However, having used both in different circumstances, I have found Derby to be highly reliable. However you will need to pay attention to Derby configuration to ensure it suits your application needs.

Traditor

+1. Rock-solid reliability, low memory footprint and deployment elasticity (embedded, network server, embedded + network server) are its best strengths indeed. – Schwab

I know that Postgresql is more mature db. However I have been using Derby for a heavy duty db processing application and can't seem to find any limitations from performance, size and reliability perspective. – Recreate

Answer 4

When looking at the H2 databases stats site, it's worth reading follow up discussion which comes out in favour of Derby compared to the H2 conclusions. http://groups.google.com/group/h2-database/browse_thread/thread/55a7558563248148?pli=1

Billposter

Answer 5

Some stats from the H2 database site here: http://www.h2database.com/html/performance.html

Longerich

What does this have to do with the question..? – Schwab

Answer 6

There are a number of performance test suites that are included as part of the Derby source code distribution itself; they are used by Derby developers to conduct their own performance testing of Derby. So if you need examples of performance tests, or want additional ones, you could consider using those. Look in the subdirectory named java/testing/org/apache/derbyTesting/perf in the Derby source distribution.

Crete

Answer 7

I'm not sure what you mean that Derby is embedded. Certainly that is an option, but you can also install it as a separate server.

Theomorphic