Initial Configuration Recommendations

Recommendations Based on the Database Size

Recommendations for Special Considerations

Recommendations Based on the Database Size

To implement the recommended configurations, the default postgresql.conf file (which resides in the data directory) must be updated. For information about PostgreSQL file locations, see http://www.postgresql.org/docs/9.1/static/storage-file-layout.html. After updating the file, you must restart the object spawner, middle tier, and database.

The following configuration values must be updated, based on the database size. For more information about how to determine the database size, see Database Sizes.

Note: The shared_buffers setting configures the amount of memory the database server uses for shared memory buffers.

Recommended Configuration Based on Database Size
Database Size	Setting	Recommended Value
Initial configuration for most databases	shared_buffers	256 MB
	work_mem	2 MB
	maintenance_work_mem	64 MB
	wal_buffers	16 MB
	checkpoint_segments	16
	effective_cache_size	2 GB
	checkpoint_completion_target	0.9
	max_connections	256
Small	shared_buffers	32 MB
	work_mem	1 MB
	maintenance_work_mem	16 MB
	wal_buffers	not applicable
	checkpoint_segments	8
	effective_cache_size	128 MB
	checkpoint_completion_target	0.5
	max_connections	100
	max_prepared_transactions	100
Medium	shared_buffers	256MB
	work_mem	2MB
	maintenance_work_mem	64 MB
	wal_buffers	16 MB
	checkpoint_segments	16
	effective_cache_size	2 GB
	checkpoint_completion_target	0.9
	max_connections	256
	max_prepared_transactions	256
Large Note: For additional parameters to consider, see Large Database Considerations.	shared_buffers	4 GB
	work_mem	16 GB
	maintenance_work_mem	128 MB
	wal_buffers	16 MB
	checkpoint_segments	32
	effective_cache_size	4 GB
	checkpoint_completion_target	0.9
	max_connections	512
	max_prepared_transactions	500

Recommendations for Special Considerations

Specialized solutions or use cases might require further configuration tuning. If you need to experiment with the parameters for your optimized system performance, the most important parameters are:

shared_buffers

Specifies the amount of memory to be used for caching data. PostgreSQL also benefits from the file system cache, so shared_buffers should not be so large that it interferes with the file system cache. For a large database, set this parameter between 1 GB and up to 25% of the total system memory.

work_mem

Specifies the amount of memory to be used for sorts, hashing, and materialization, before writing to temporary disk files. Several running sessions can perform operations concurrently. Therefore, the total memory used might be many times the value of work_mem. Keep this in mind when choosing the value for this parameter. Set this parameter between 16 MB and 64 MB or more, for a specialized use case (for example, frequent very large sorts).

maintenance_work_mem

Specifies the maximum amount of memory to be used for vacuuming (reclaiming storage used by rows marked for delete) and index builds. For a large database, set this parameter to 256 MB or more.

If your application can tolerate losing a transaction if the computer or storage crashes, you can set the synchronous_commit parameter to Off for faster updates.

Large Database Considerations

For a large database, also consider the following parameters:

effective_cache_size

Specifies to the query optimizer how much memory is used by the file cache. Set this parameter to 50% of the physical memory.

checkpoint_segments

Specifies how many 16 MB write-ahead log (WAL) segments are buffered on disk before writing permanently to database tables. Set this parameter to 64 MB or more.

synchronous_commit

Specifies whether transaction commits wait for WAL records to be written to disk before the command returns a successful indication to the client. If you have an application that can tolerate losing a transaction if the computer or storage crashes, this parameter can be set to Off for faster updates.