PostgreSQL Database Server

PostgreSQL Performance Summary

The following charts show the performance of PostgreSQL (in green) relatively to other JPA/DBMS combinations (in gray).

Position #1 on the X-Axis (the fastest) is about 100 times faster than position #33 (the slowest).
In the chart below the Y-Axis represents result score (maximum 100) in logarithmic scale - higher is better:
JPA performance benchmark comparision Hibernate, EclipseLink, OpenJPA, DataNucleus, ObjectDB
In the chart below the Y-Axis represents result score (maximum 100) in linear scale - higher is better:
JPA performance benchmark comparision Hibernate, EclipseLink, OpenJPA, DataNucleus, ObjectDB


The configurations in which PostgreSQL has been tested are listed in the following table:

 Database + JPA ProviderNormalized Score
100 - Best,  0 - Worst
Final Position
1 - Best,  33 - Worst
Failed Tests
1EclipseLink with PostgreSQL server10.58  (out of 33)0
2Hibernate with PostgreSQL server9.111  (out of 33)0
3OpenJPA with PostgreSQL server6.518  (out of 33)10
4DataNucleus with PostgreSQL server6.019  (out of 33)4

As shown above, PostreSQL was faster when used with Hibernate or EclipseLink as a JPA provider.

Comparing the results of PostreSQL in this benchmark to the results of other RDBMS servers (excluding ObjectDB and embedded database results) reveals that PostreSQL was the fastest RDBMS database server.

About PostgreSQL

PostreSQL is an object-relational database management system (ORDBMS). It evolved from the Ingres database. Its first version was released in 1989.

PostreSQL is available under the open source PostgreSQL license.

In this benchmark PostreSQL version 9.1.2 (which was released in December 2011) has been tested.

To avoid flush to the disk on every transaction by PostgreSQL (which affects the performance of insert/update/delete mainly in small transactions) the fsync configuration option has been set to off.

Solved Problems and Issues

The stack traces below demonstrate some exceptions that have been thrown when using PostreSQL database in previous runs of this benchmark and have been fixed by upgrading software and setting.

javax.persistence.RollbackException: Error while committing the transaction
	at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:93)
	at org.jpab.Test.doAction(Test.java:271)
	at org.jpab.Runner$UpdateAction.run0(Runner.java:564)
	at org.jpab.Runner$TestAction.run(Runner.java:507)
	at java.lang.Thread.run(Thread.java:619)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
	at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1235)
	at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1168)
	at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:81)
	... 4 more
Caused by: org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
	at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:262)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179)
	at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
	at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
	at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
	at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375)
	at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
	at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:76)
	... 4 more
Caused by: java.sql.BatchUpdateException: Batch entry 1 update Person set birthDate='1956-09-15 +03:00:00', city='XPCNOZUAKIJBQIVW', country='GTKNV', email='FXNXPRNO@NSVI.JEH', firstName='VPAQHJRNT', joinDate='1979-03-05 +02:00:00', lastLoginDate='2010-09-09 +03:00:00', lastName='FIKXUBCDSP', loginCount='173', middleName='QGAXGU', phone='221668502734689', state='DP', street='UPPDCMHIQXS', zip='456616781' where id='107279' was aborted.  Call getNextException to see the cause.
	at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
	at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
	... 12 more
org.apache.openjpa.persistence.RollbackException: The transaction has been rolled back.  See the nested exceptions for details on the errors that occurred.
FailedObject: org.jpab.node.Node@3a0ab1
	at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:583)
	at org.jpab.Test.persist(Test.java:216)
	at org.jpab.Test.persist(Test.java:199)
	at org.jpab.Runner$PersistAction.run0(Runner.java:530)
	at org.jpab.Runner$TestAction.run(Runner.java:509)
	at java.lang.Thread.run(Thread.java:619)
Caused by:  org.apache.openjpa.persistence.PersistenceException: The transaction has been rolled back.  See the nested exceptions for details on the errors that occurred.
FailedObject: org.jpab.node.Node@3a0ab1
	at org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2302)
	at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2139)
	at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:2037)
	at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1955)
	at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
	at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1479)
	at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:925)
	at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:559)
	... 5 more
Caused by:  org.apache.openjpa.persistence.PersistenceException: ERROR: duplicate key value violates unique constraint "node_pkey" {prepstmnt 25150543 INSERT INTO Node (id, changeCount, CHILD1_ID, CHILD2_ID) VALUES (?, ?, ?, ?) [params=(int) 110001, (int) 0, (int) 110002, (int) 110003]} [code=0, state=23505]
FailedObject: org.jpab.node.Node@3a0ab1
	at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4821)
	at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4787)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
	at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:78)
	at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:143)
	at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:81)
	at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:99)
	at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:87)
	at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:550)
	at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:106)
	at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:59)
	at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:103)
	at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:76)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:751)
	at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:131)
	... 12 more
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: ERROR: duplicate key value violates unique constraint "node_pkey" {prepstmnt 25150543 INSERT INTO Node (id, changeCount, CHILD1_ID, CHILD2_ID) VALUES (?, ?, ?, ?) [params=(int) 110001, (int) 0, (int) 110002, (int) 110003]} [code=0, state=23505]
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:257)
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:233)
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$1000(LoggingConnectionDecorator.java:70)
	at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:1079)
	at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:285)
	at org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:285)
	at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1722)
	at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:267)
	at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:118)
	... 22 more
org.apache.openjpa.persistence.ArgumentException: Failed to execute query "SELECT o FROM Person o WHERE o.lastName LIKE :pattern". Check the query syntax for correctness. See nested exception for details.
	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:870)
	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:792)
	at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:542)
	at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:288)
	at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:302)
	at org.jpab.Test.query(Test.java:314)
	at org.jpab.Runner$QueryAction.run0(Runner.java:552)
	at org.jpab.Runner$TestAction.run(Runner.java:507)
	at java.lang.Thread.run(Thread.java:619)
Caused by: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer
	at org.apache.openjpa.jdbc.kernel.PreparedSQLStoreQuery$PreparedSQLExecutor.toParameterArray(PreparedSQLStoreQuery.java:157)
	at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:855)
	... 8 more
javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.1.v20100817-r8050): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.BatchUpdateException: Batch entry 0 DELETE FROM NODE WHERE (ID = '129912') was aborted.  Call getNextException to see the cause.
Error Code: 0
Call: DELETE FROM NODE WHERE (ID = ?)
	bind => [130000]
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:102)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:63)
	at org.jpab.Test.doAction(Test.java:272)
	at org.jpab.Runner$RemoveAction.run0(Runner.java:578)
	at org.jpab.Runner$TestAction.run(Runner.java:508)
	at java.lang.Thread.run(Thread.java:619)
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.1.v20100817-r8050): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.BatchUpdateException: Batch entry 0 DELETE FROM NODE WHERE (ID = '129912') was aborted.  Call getNextException to see the cause.
Error Code: 0
Call: DELETE FROM NODE WHERE (ID = ?)
	bind => [130000]
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.processExceptionForCommError(DatabaseAccessor.java:1419)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeJDK12BatchStatement(DatabaseAccessor.java:823)
	at org.eclipse.persistence.internal.databaseaccess.ParameterizedSQLBatchWritingMechanism.executeBatchedStatements(ParameterizedSQLBatchWritingMechanism.java:139)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.writesCompleted(DatabaseAccessor.java:1632)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.commitTransaction(DatabaseAccessor.java:399)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicCommitTransaction(AbstractSession.java:504)
	at org.eclipse.persistence.sessions.server.ClientSession.basicCommitTransaction(ClientSession.java:155)
	at org.eclipse.persistence.internal.sessions.AbstractSession.commitTransaction(AbstractSession.java:686)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitTransaction(UnitOfWorkImpl.java:1581)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitTransaction(RepeatableWriteUnitOfWork.java:559)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitTransactionAfterWriteChanges(UnitOfWorkImpl.java:1604)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:207)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:1148)
	at org.eclipse.persistence.internal.jpa.transaction.EntityTransactionImpl.commitInternal(EntityTransactionImpl.java:84)
	... 5 more
Caused by: java.sql.BatchUpdateException: Batch entry 0 DELETE FROM NODE WHERE (ID = '129912') was aborted.  Call getNextException to see the cause.
	at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
	at org.postgresql.core.v3.QueryExecutorImpl$1.handleError(QueryExecutorImpl.java:459)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
	at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.executeBatch(DatabasePlatform.java:1964)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeJDK12BatchStatement(DatabaseAccessor.java:817)
	... 17 more