PostgreSQL configuration tuning

PostgreSQL's default configuration values are not suitable for the production environment; several default values are often undersized.

Tip

In developing PostgreSQL applications, it is a good idea to have a test system that is configured very closely to a production environment to get accurate performance measures.

In any PostgreSQL environment, the following configuration should be reviewed.

Maximum number of connections

The maximum number of connections is an important parameter in configuring a database. Each client connection consumes memory, thus affecting also the total amount of memory that can be allocated for other purposes. The max_connections configuration parameter's default value is 100; lowering this value allows the database administrator to increase the work_mem setting.

In general, it is good practice to use connection pooling software to reduce the amount of used memory and increase performance, as killing and establishing a connection wastes time...