Comparing performance

PostgreSQL is known for features, not for speed.
It has long been established that PostgreSQL has a rich feature set, with support for transactions, views, subselects, foreign keys support, checks and lots more. To my knowledge it supports the SQL 92 and 99 standards better then any other (propriatary) database system around, and has some nice extensions too!

Early versions of PostgreSQL were slow and the speed of the PostgreSQL database has been a matter of debate ever since. The issue was never really addressed, except by the TPC-C test published by Great Bridge LLC, some three years ago. This test showed that PostgreSQL's performance was at least comparable to, or even better then, three major vendors' databases and MySQL, especially under high load. The validity of the test was questioned by several parties however, mainly because of the use of ODBC drivers.

Things change.
Our company has been using PostgreSQL for some time now, mainly to rid our customers of (expensive) licenses. One thing stood out. PostgreSQL seemed to perform really well. PostgreSQL has improved a lot over the last years and has a very nice query optimizer and some nice features like better-than-row-level-locking, so tables are only partially locked during writes. Performance enhancements like this don't show up in simple tests like just inserting 100.000 rows of data, but they do show in complex querying situations. 

Every database professional knows that comparing RDBMS's is difficult and subjective. But because our Client-Server product supports 2 major commercial rdbms's as well as PostgreSQL with the same client application, and because we are able to replicate data from one database to another, we just couldn't let this oppertunity pass.

The point of this article is explicitly NOT to demonstrate a winner, the propriatary database is used as a measure to compare PostgreSQL's speed with.

Test Setup

Note that PostgreSQL was configured to use more shared memory than default, because the default setting is *very* conservative, (runs on a 16Mb box) and having this set to low really hurts database performance. However the memory used by PostgreSQL in this test was significantly less then the memory used by Larry.

The databases
The databases used were set up with our latest database schemes and data from a live production database. The database uses 48 tables, with 179 indexes, 77 forgein keys, 30 sequences and triggers and 138 views. A total of 200.000+ records were inserted, in tables of different sizes.

Real life simulation
The queries submitted to the database were logged from our regular client application during a typical session. Those queries are compatible with both databases. Those logs were modified to reflect different user-logins and were used as input for the simulated sessions in this test setup. Below are some characteristics of the queries used.

The queries had approximately 80 queries, with 10% insert/update/delete, 74 (sub)selects, 46 using complex (cascaded) views. This resulted in the return of 2000 records per session.

The custom made client software ran 200 sessions of 6 concurrent users, started with a small startup delay to increase asynchronous querying. The real client session took about 10 minutes, a simulated session 4 seconds. The test software to simulate the sessions used the native vendors' driver libraries, not ODBC. Per database a total of 24.000.000 records were returned as a result of the 1200 simulated client sessions.

The database and queries may not be very big or complex, but many features are used, and it is a live and mature system, with not much redundancy and good integrity, used by many customers. It seems to be a fair test.

It was established that network usage never exeeded 50 % of its total capacity during the tests, and the test client's limits were never (even remotely) reached during the test runs.

The results: