Dave’s PostgreSQL Blog

Improvement in read-write transactions turning off update_process_title

Improve PostgreSQL on Windows performance by 100%

It sounds like click-bait, or one of those late night TV promotions – “Improve your database performance by 100% – by changing just this one setting!” But in this case, it’s true – you can drastically improve PostgreSQL on Windows performance by changing one configuration setting – and we made this the default in our Postgres by BigSQL distribution for 9.2 thru 9.6.

tl;dr – if you have high query load, change “update_process_title” to ‘off’ on Windows, and get 100% more throughput.

Improve postgresql performance by turning off update_process_title

Performance Improvement by turning off update_process_title

Most Postgres DBA’s already know that they need to tune settings for shared buffers, WAL segments, checkpoints, etc, to get the best performance from their database. If you are running PostgreSQL on Windows, there’s another setting that you need to look at, specifically “update_process_title”. Changing this setting from “on” to “off” can improve throughput on a heavy query load by close to 100%

We ran a series of benchmark tests in our performance lab and you can see the dramatic improvement in the graphs displayed. We tested PostgreSQL 9.5 on a 16-core Windows server with fast SSD drives using a standard pgbench run in both read-only and read-write modes. Scaling from 4 to 40 clients shows a plateau in throughput (measured by TPS) after 8 clients when the setting is set to “on”. Changing the update_process_title setting to “off” allows PostgreSQL to continue to scale throughput, showing increasing TPS up to 40 clients. The throughput at 32 read-only clients increases from 20K TPS to 58K TPS (180% higher) and at 40 clients continues to climb to 76K TPS (270% higher).

Improvement in read-write transactions turning off update_process_title

Improvement in read-write transactions turning off update_process_title

This performance gain is seen for both read-only and read-write workloads. With 32 clients, the write throughput increases from 2,700 TPS to 7,700 TPS (180% higher) and at 40 clients continues to climb to 8,200 (200% higher).

The update_process_title setting controls whether or not Postgres will update the process description that you see when querying the system list of running commands based on the current SQL statement being processed. On Linux this is done using ps, on Windows it requires the ProcessExplorer tool. Updating the process description becomes a bottleneck on Windows, and limits the throughput even on a high-end server. Not many Windows admins actually use this information on a regular basis, so unless you are actively debugging a slow or long running process using this process information, you should leave this turned off.

Takayuki Tsunakawa originally tracked down this bottleneck and created a patch for PostgreSQL 9.6 that has changed the default to be ‘off’ on Windows. We have made the same setting change in BigSQL distributions of version 9.2 thru 9.5 as well as 9.6. So even if you’re not ready to move to the new 9.6 version, when you install Postgres by BigSQL on Windows you are getting the best performance out-of-the-box.


Read environment variables from PostgreSQL using plpython

Sometimes in your PostgreSQL code you want to read an environment variable – whether to get the input file location for a data load, or check that the postgres user has the right path set, or verify that the TDSDUMP environment variable is set when configuring the tds_fdw to connect to SQL Server. Here’s a plpython based function that will do just that (or the gist):

create extension plpythonu;
create type py_environ_type as (name text, value text);

create or replace function py_environ(name varchar DEFAULT NULL)
  returns setof py_environ_type
as $$
  import os
  aev = []
  if name is None:
    for k, v in os.environ.items():
      aev.append((k, v))
    v = os.getenv(name)
    if v is not None:
  return aev;
$$ language plpythonu;

You can use it to get a list of all environment variables:

select * from py_environ();

Or to get just a single variable:

select value from py_environ('PATH');

If you want other filtering, just add a where clause:

select name, value from py_environ() where name like 'TDS%';

Inspired by an idea from Scott Mead and the pal_environ() function, tested to work on Linux, OS/X, and Windows.