Postgres 8.3 faster than 9.3?

Question

Our software product currently ships on Windows 7 with Postgres 8.3 as its database. On a busy site, there may be 24 automated systems generating 100 rows (x100 columns) per minute, with 3-10 human clients viewing subsets of around 1000 rows – all retrieved at once, with incremental updates querying pk + timestamp every minute or so and retrieving new rows that are pertinent. There are a few auxiliary tables, but this table has the primary activity.

As a first step to a limited multi-master system (to help with geographically separated teams), we implemented an upgrade to 9.3. Performance wasn't the first priority, so it wasn't really profiled. As release time has come, management has decided to drop 9.3 for now citing fear of possible performance degradation and lack of testing resources. I was sure the performance issue was preposterous so I did some PgBench testing.

Using 9.3's pgbench, I alternated between connecting to the local 8.3 and 9.3 installations (diff't port numbers). I've captured my results in this google drive spreadsheet, but the summary is that generally 8.3 beat 9.3. 9.3 only won in raw insert performance.

We have some customization of our postgresql.conf files which I generally kept from 8.3 to 9.3, I'll list the non-default settings

max_connections = 1000
shared_buffers = 320MB
temp_buffers = 80MB
max_prepared_transactions = 50    #8.3 only, 9.3 left at 0 (not sure why)
max_fsm_pages = 204800            #8.3 only, 9.3 doesn't have setting
autovacuum_max_workers = 30

So, is this just the price of progress, or is there something I should be doing in 9.3 to make it excel?

Answer 1

Usually PostgreSQL 9.3 is generally faster then 8.3 - but hard to say what is wrong. Possible sources: problems with IO, wrong PostgreSQL configuration - max_connections = 1000 is probably terribly wrong value, default work_mem is usually too small, hitting hw limits (9.1 and higher should to better use more CPU), wrong testing ...

Other problems can be changes in pgBench. Result of pgBench 8.3 should not be exactly compared with results pgBench 9.3. If you would to have a good numbers, then you have to use pgBench 9.3 for PosgreSQL 9.3 and PostgreSQL 8.3. Don't forget - test should run minimally about 20 munutes (better hours).

Second factor - probably pgBench is not important for your production. pgBench is synthetic tests with disputable value. It is good for initial hw and sw testing, but is bad for configuration precision and database tuning. Much more important is speed of your application - you should to test a speed of slow queries or most often queries. 9.3 has lot of new functionality - better monitoring, more comfortable tools, better optimization for some kind of queries, built-in replication, online comfortable physical backup, much smarted vacuum (faster on large datasets), ...

Increasing of default_statistics_target had overhead in query planning - for extremely fast and simple queries it can be significant. On second hand, higher value decrease a probability of wrong estimation and wrong optimization - that is important for usual non trivial queries - planning is little bit slower (it can be visible in synthetic tests) but usage in production is much more robust. Depends on your application (if it use simple or not simple queries) this configuration variable can be important for you or not.