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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *