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:
In the chart below the Y-Axis represents result score (maximum 100) in linear scale - higher is better:
The configurations in which PostgreSQL has been tested are listed in the following table:
| Database + JPA Provider | Normalized Score 100 - Best, 0 - Worst | Final Position 1 - Best, 33 - Worst | Failed Tests | |
|---|---|---|---|---|
| 1 | EclipseLink with PostgreSQL server | 10.5 | 8 (out of 33) | 0 |
| 2 | Hibernate with PostgreSQL server | 9.1 | 11 (out of 33) | 0 |
| 3 | OpenJPA with PostgreSQL server | 6.5 | 18 (out of 33) | 10 |
| 4 | DataNucleus with PostgreSQL server | 6.0 | 19 (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='[email protected]', 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
