OpenClinica User Manual/OptimisingPostgreSQL

PostgreSQL database configuration

The performance of OpenClinica is highly dependent on the performance of PostgreSQL. PostgreSQL is initially configured to use a small amount of memory, altering options to increase the memory used can improve the performance significantly, especially on large CRFs.

Open PostgreSQL admin. Select Tools, Server configuration, postgresql.conf

The options below are for a Windows server primarily running OpenClinica and optionally Tomcat with 2GB+ memory and plenty of disk space. This can also be used for developer machines with similar specs.

Alter the following options (remembering to tick the boxes to enable them), the instructions includes comments after the hash characters:

   max_connections = 200
   shared_buffers = 512MB #1/4 your RAM, with a max of 512MB
   effective_cache_size = 1GB #1/2 your RAM
   checkpoint_segments= 32
   checkpoint_completion_target = 0.9
   autovacuum = on
   work_mem = 16MB #1/32nd of shared_buffers above
   maintenance_work_mem= 256MB
   wal_sync_method = fsync_writethrough    # previously fsync (commented)
   wal_buffers = 16MB
   random_page_cost = 3.0

On a Linux machine with 2GB+ memory and plenty of disk space, the configuration is the same, except for two differences:

   shared_buffers = 32MB      # the Linux kernel frequently doesn't support values over this
   wal_sync_method = fdatasync    # leave as default

In Windows, typing ‘services’ into the search box above the Windows Orb allows you access to the services running on your machine to restart PostgreSQL (otherwise it will restart when you next restart your computer).

Reference: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server