Database Reference
In-Depth Information
Getting ready
If your PostgreSQL server is only used very sporadically, or has periods of total inactivity, then
you may be able to benefit from some of the advice given here. That could be a laptop, or it
could be a somewhat inactive virtual server.
How to do it...
PostgreSQL is a server-based database, so it mostly does nothing at all if there are no active
clients. To minimize server activity, set the following parameters in the
postgresql.conf
file:
F
autovacuum
=
off
F
wal_writer_delay
=
10000
F
bgwriter_delay
=
10000
These settings are not optimal for many uses and should only be used when it is known that
the server will be quiet. They should be reset to previous values when the server becomes
busy again.
How it works...
There are a couple of processes that stay active continually, on the expectation that they will
be needed should clients become active. These processes are as follows:
F
Writer process (also known as the "Background writer")
F
WAL writer process
F
Archiver, which will be active if WAL archiving is enabled.
F
WAL receiver process, which will be active if streaming replication is in use.
F
Autovacuum process
The Background writer process wakes up by default every 200ms to do its work.
The maximum setting is 10s, which isn't very long, though the Background writer
can be disabled by the setting,
bgwriter_lru_maxpages
=
0
.
The WAL writer process wakes up by default every 200ms. The maximum setting is also 10s.
This cannot be disabled. If there is no write activity, then no work will be performed, other
than the wakeup and check.
The Archiver process will wake up every 15s and check whether any new WAL files have been
written. This will cause some activity against the filesystem directory. That time cannot be
changed by a parameter.
