Database Administration - PostgreSQL: Up and Running - page 20

effective_cache_size
An estimate of how much memory you expect to be available in the OS and Post‐
greSQL buffer caches. This setting has no effect on actual allocation, but query
planner figures in this setting to guess whether intermediate steps and query output
would fit in RAM. If you set this much lower than available RAM, the planner may
forgo using indexes. With a dedicated server, setting effective_cache_size to half
or more of your onboard memory would be a good start. Changes require at least
a reload.
work_mem
Controls the maximum amount of memory allocated for operations such as sorting,
hash join, and table scans. The optimal setting depends on how you're using the
database, how much memory you have to spare, and whether your server is dedi‐
cated to PostgreSQL or not. If you have many users running simple queries, you
want this setting to be relatively low. How high you set this also depends on how
much RAM you have to begin with. A good article to read on work_mem is Under‐
standing work_mem . Changes require at least a reload.
maintenance_work_mem
The total memory allocated for housekeeping activities such as vacuuming (prun‐
ing records marked for delete). You shouldn't set it higher than about 1 GB. Reload
after changes.
These settings can also be set at the database, users, and function levels. For example,
you might want to set work_mem higher for an SQL whiz running sophisticated queries.
Similarly, if you have one function that is sort-intensive, you could raise the work_mem
setting just for it.
New in PostgreSQL 9.4 is ability to change settings using the new ALTER SYSTEM SQL
command. For example, to set the work_mem globally, enter the following:
ALTER SYSTEM set work_mem = 8192 ;
Depending on the particular setting changed, you may need to restart the service. If just
need to reload it, here's a convenient command:
SELECT pg_reload_conf ();
PostgreSQL records changes made through ALTER SYSTEM in an override file called
postgresql.auto.conf , not directly into postgresql.conf .
“I edited my postgresql.conf and now my server is broken.”
The easiest way to figure out what you screwed up is to look at the log file, located at
the root of the data folder, or in the pg_log subfolder. Open the latest file and read what
the last line says. The raised error is usually self-explanatory.