A.Many Ways to set postgresql parameters

•Some parameters can be changed per session using the SETcommand.
•Some parameters can be changed at the user level using ALTER USER.
•Some parameters can be changed at the database level using ALTER DATABASE.
•The SHOW command can be used to see settings.
•The pg_settings and pg_file_settings catalog table lists settings information.

B.PostgreSQL Parameters(The following are most common parameters in postgresql.conf)

1.Connection Settings

 

Parameter	Default Value	Description
listen_addresses	localhost	Specifies the addresses on which the server is to listen for connections. Use * for all.
port	5432	The port the server listens on.
max_connections	100	Maximum number of concurrent connections the server can support.
superuser_reserved_connections	3	Number of connection slots reserved for superusers.
unix_socket_directory	/tmp	Directory to be used for UNIX socket connections to the server
unix_socket_permissions	0777	access permissions of the Unix-domain socket.

2.Security and Authentication Settings

 

Parameter	Default Value	Description
authentication_timeout	1 minute	Maximum time to complete client authentication, in seconds.
ssl	off	Enables SSL connections.
ssl_ca_file		Specifies the name of the file containing the SSL server certificate authority (CA).
ssl_cert_file		Specifies the name of the file containing the SSL server certificate.
ssl_key_file		Specifies the name of the file containing the SSL server private key.
ssl_ciphers		List of SSL ciphers that may be used for secure connections.

3.Memory Settings

 

Parameter	Default Value	Description
shared_buffers	128 MB	Size of PostgreSQL shared buffer pool for a cluster.
temp_buffers	8 MB	Amount of memory used by each backend for caching temporary table data.
work_mem	4 MB	Amount of memory used for each sort or hash operation before switching to temporary disk files.
maintenance_work_mem	64 MB	Amount of memory used for each index build or VACUUM.
temp_file_limit	1	amount of disk space that a session can use for temporary files. A transaction attempting to exceed this limit will be cancelled. Default is unlimited.

4.Query Planner Settings

 

Parameter	Default Value	Description
random_page_cost	4.0	Estimated cost of a random page fetch, in abstract cost units. May need to be reduced to account for caching effects.
seq_page_cost	1.0	Estimated cost of a sequential page fetch, in abstract cost units. May need to be reduced to account for caching effects. Must always set random_page_cost >= seq_page_cost
effective_cache_size	4 GB	Used to estimate the cost of an index scan. Rule of thumb is 75% of system memory.

5.Write Ahead Log Settings

 

Parameter	Default value	Description
wal_level	minimum	Determines how much information is written to the WAL. Change this to enable replication. Other values are archive, logical and hot_standby.
fsync	on	Turn this off to make your database much faster –and silently cause arbitrary corruption in case of a system crash.
wal_buffer	-1, autotune)	The amount of memory used in shared memory for WAL data. The default setting of -1 selects a size equal to 1/32nd (about 3%) of shared_buffers.
min_wal_size	80 MB	The WAL size to start recycling the WAL files.
max_wal_size	1 GB	The WAL size to start checkpoint.Controls the number of WAL Segments(16MB each) after which checkpoint is forced.
checkpoint_timeout	5 minutes	Maximum time between checkpoints.
wal_compression	off	The WAL of Full Page write will be compressed and written.

6.Where To Log

log_destination-Valid values are combinations of stderr, csvlog, syslog, and eventlog, depending on platform.
logging_collector-Enables advanced logging features. csvlogrequires logging_collector.
‒log_directory-Directory where log files are written. Requires logging_collector.
‒log_filename-Format of log file name (e.g. postgresql-%Y-%M-%d.log). Allows regular log rotation. Requires logging_collector.
‒log_file_mode(default 0600) -On Unix systems this parameter sets the permissions for log files when logging_collectoris enabled.
‒log_rotation_age-Automatically rotate logs after this much time. Requires logging_collector.
‒log_rotation_size-Automatically rotate logs when they get this big. Requires logging_collector.

7.When To Log

 

Parameter	Default value	Description
client_min_messages	NOTICE	Messages of this severity level or above are sent to the client
log_min_messages	WARNING	Messages of this severity level or above are sent to the server.
log_min_error_statement	ERROR	When a message of this severity or higher is written to the server log, the statement that caused it is logged along with it.
log_min_duration_statement	default -1, disabled	When a statement runs for at least this long, it is written to the server log, with its duration.

8.What to log

 

Parameter	Default value	Description
log_connections	off	Log successful connections to the server log.
log_disconnections	off	Log some information each time a session disconnects, including the duration of the session.
log_error_verbosity	default	Can also select “terse” or “verbose”.
log_duration	off	Log duration of each statement.
log_line_prefix		Additional details to log with each line.
log_statement	none	Legal values are none, ddl, mod (DDL and all other data-modifying statements), or all.
log_temp_files	-1	Log temporary files of this size or larger, in kilobytes.
log_checkpoints	off	Causes checkpoints and restartpoints to be logged in the server log.

9.Background Writer Settings

 

Parameter	Default value	Description
bgwriter_delay	200 ms	Specifies time between activity rounds for the background writer.
bgwriter_lru_maxpages	100	Maximum number of pages that the background writer may clean per activity round.
bgwriter_lru_multiplier	2.0	Multiplier on buffers scanned per round. By default, if system thinks 10 pages will be needed, it cleans 10 * bgwriter_lru_multiplierof 2.0 = 20.

10.Statement Behavior

 

Parameter	Default value	Description
search_path		This parameter specifies the order in which schemas are searched. The default value for this parameter is”$user”, public.
default_tablespace		Name of the tablespace in which to objects are created by default.
temp_tablespaces		Tablespaces name(s) in which temporary objects are created.
statement_timeout		Postgres will abort any statement that takes over the specified number of milliseconds A value of zero (the default) turns this off.

11.Vacuum Cost Settings

 

Parameter	Default value	Description
vacuum_cost_delay	0 ms	The length of time, in milliseconds, that the process will wait when the cost limit is exceeded.
vacuum_cost_page_hit	1	The estimated cost of vacuuming a buffer found in the PostgreSQL buffer pool.
vacuum_cost_page_miss	10	The estimated cost of vacuuming a buffer that must be read into the buffer pool.
vacuum_cost_page_dirty	20	The estimated cost charged when vacuum modifies a buffer that was previously clean.
vacuum_cost_limit	200	The accumulated cost that will cause the vacuuming process to sleep.

12.Autovacuum Settings

 

Parameter	Default value	Description
autovacuum	on	Controls whether the autovacuum launcher runs, and starts worker processes to vacuum and analyze tables.
log_autovacuum_min_duration	-1	Autovacuum tasks running longer than this duration are logged. Can now be specified per table.
autovacuum_max_workers	3	Maximum number of autovacuum worker processes which may be running at one time.
autovacuum_work_mem	default -1, to use maintenance_work_mem	Maximum amount of memory used by each autovacuum worker.