I don't hold myself out as being as a professional database administrator, however I recently upgraded a non-trivial size database (about 100 gigs on disk) from Postgres 9.5 to 9.6.

The database was handling OK under load in Postgres 9.5, however it becomes unusably slow in 9.6. The only thing that changed was the version of Postgres (which was upgraded using the pg_upgrade tool) - same Postgresql.conf file.

We have run a VACUUM ANALYZE, and this does not seem to have made any difference.

When I analyze a typical query which was taking > 5 seconds under load, it executes in 50ms. When the system is under load, uptime (on an 8 core cpu) reports values of > 30 and VMSTAT shows all available CPU is being used (with no swap and negligible disk IO)

On the advice of "experts" the Postgres configuration (in 9.5) was tuned with the following options - which are in the 9.6 config as well -

autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 100
autovacuum_max_workers = 10
autovacuum_naptime = 5min
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_scale_factor = 0.05
autovacuum_vacuum_threshold = 100
vacuum_cost_limit = 800

bgwriter_delay = 100ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4

checkpoint_timeout = 15min
checkpoint_warning = 5min
cpu_tuple_cost = 0.03
effective_cache_size = 50GB
maintenance_work_mem = 1GB
shared_buffers = 8GB
wal_buffers = 64MB
work_mem = 60MB

Are there any settings I can change to make Postgres 9.6 behave more like 9.5, so that I can work out what is causing the issue?

Does anyone have an insight into what might be causing Postgres to eat up all the available CPU ?

How can I go about debugging/getting a grip on the problem in a production environment ?

  • How is disk space?
    – Vérace
    Oct 17, 2017 at 1:32
  • You need to show an explain analyze on 9.5 and 9.6 that is running differently. Oct 17, 2017 at 2:11
  • @Vérace There is ample free diskspace - just under 100 gigs free.
    – davidgo
    Oct 17, 2017 at 2:22
  • @EvanCarrollQWERHJKL I can't run an explain analyze on 9.5 because I no longer have an instance of it. Also, the explain analyze done on 9.6 showed a very low cost (47u) on the query which took over 5000 secs when the system was under stress.
    – davidgo
    Oct 17, 2017 at 2:24
  • so show the result when the system is under stress. Oct 17, 2017 at 2:26

1 Answer 1


The issue we were having turned out to be Transparent Huge pages. I disabled this in the kernel and on Postgres, and things went back to how they were prior to the upgrade to Postgres 9.6

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.