Database Reference
In-Depth Information
unit
tells you the measurement unit reported by the settings. This is sometimes
confusing when it comes to memory because, as you can see in
Example 2-2
,
some are reported in 8 KB units and some just in KB. In
postgresql.conf
, usually,
you deliberately set these to a unit of measurement of your choice; 128 MB is a
good candidate. You can also get a more human-readable display of a particular
setting by running a statement such as
SHOW effective_cache_size;
or
SHOW
maintenance_work_mem;
, both of which display settings in MBs. If you want to
see all settings in friendly units, use
SHOW ALL
.
setting
is the current setting;
boot_val
is the default setting;
reset_val
is the
new setting if you were to restart or reload the server. Make sure that after any
change you make to
postgresql.conf
,
setting
and
reset_val
are the same. If they
are not, the server is still in need of a restart or reload.
Pay special attention to the following network settings in
postgresql.conf
; changing their
values requires a service restart.
If you are running version 9.4 or later, the same-named settings in
postgresql.auto.conf
take precedence over the ones in
postgresql.conf
.
listen_addresses
Informs PostgreSQL which IP addresses to listen on. This usually defaults to
lo
calhost
or
local
, but many people change it to
*
, meaning all available IP ad‐
dresses.
port
Defaults to 5432. If you happen to be on Red Hat or CentOS, make changes to the
PGPORT
value
/etc/sysconfig/pgsql/
your_service_name_here
to change the listening
port.
max_connections
The maximum number of concurrent connections allowed.
In our experience, we found the following three settings to affect performance across
the board and might be worthy of experimentation for your particular setup:
shared_buffers
Defines the amount of memory shared among all connections to store recently
accessed pages. This setting profoundly affects the speed of your queries. You want
this setting to be fairly high, probably as much as 25% of your onboard memory.
However, you'll generally see diminishing returns after more than 8 GB. Changes
require a restart.
