My Digital Corner: Optimizing PostgreSQL for Production ...

Optimizing PostgreSQL for Production Use.

This is a reference list of things that need to be changed to PostgreSql Server prior to production use. This material is a collection from other resources but outlined in a while to simplify configuration settings for regular database administrators like me.

STEP 1 [Configurations]: Go to PostgreSql config at: file /etc/postgresql/8.*/main/postgresql.conf to change the settings.

STEP 2 [Available Memory]: Know how much memory you need to allocate for your database. If your server is solely dedicated for database use, then your physical memory shall be your available memory. If it is shared with other services such as Apache2 Web Server then, leave an ample memory for others. Generally, allocate a very liberal amount of memory for PostgreSql since databases are naturally resource hogs. My rule of thumb for this is:

75% of Physical Memory (RAM) = 'Available Memory'

Example: .75 * 2G RAM = [1.5 G RAM]

STEP 3 [Change th config file]:

Parameter	shared_buffers
Description	Sets the number of shared memory buffers used by the database server. Settings significantly higher than the minimum are usually needed for good performance.
Units	8kb
Recommended	*.25 [Available Memory]**
Special Note	You need to change the kernel parameter [SHMMAX] to twice the amount of memory allocated for shared_buffers.

Parameter	work_mem
Description	Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files.
Units	kb Default: 1024kb
Recommended	[Available Memory/max_connections] / 2

Parameter	maintenance_work_mem
Description	Specifies the maximum amount of memory to be used in maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. The value is specified in kilobytes, and defaults to 16384 kilobytes (16 MB).
Units	kb
Recommended	[Available Memory] / 8

Parameter	wal_buffers
Description	Number of disk-page buffers allocated in shared memory for Write-Ahead Log (WAL) data.
Units	kb
Recommended	8MB

Parameter	checkpoint_segments
Description	Maximum distance between automatic WAL checkpoints, in log file segments (each segment is normally 16 megabytes).
Units
Recommended	(([Disk Space Allocation for WAL] / 8) -1 ) / 2
Special Note	must be between 16 to 128 depending on how much disk space are you willing to allocate for WAL.

Parameter	effective_cache_size
Description	Sets the planner's assumption about the effective size of the disk cache that is available to a single index scan.
Units	kb
Recommended	*[Available Memory] .75**