Improving postgresql.conf

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Improving postgresql.conf
Date: 2004-06-09 10:19:52
Message-ID: f8b35db42df1c3af53c557da477ac2ce@biglumber.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


(warning: rehashing of issues ahead)

Sometimes when I talk to technical people about using PostgreSQL,
I get a strong reaction along the lines of "it's ugly, complex, and
hard to set up." While we have gotten better than we used to be,
some of this is still true. I usually press for further details.
One of the top items mentioned is the configuration files,
especially postgresql.conf. Specifically, it is non-standard and
cryptic.

One thing that would go a long way is to remove the confusing
"default is commented out" behavior. This is ugly and unnecessary:
simply explicitly list every value.

The second thing that would help is better documentation. It doesn't
have to be quite as verbose as the httpd.conf file, but a nice
multi-line explanation of every item in the file, perhaps with a
URL for further information, would be a very nice addition. Moving
the comments above each item, rather than trying to squeeze some of
them next to the parameter, would also make the file more readable
and more consistent.

For example, this original sample:

#shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
#work_mem = 1024 # min 64, size in KB

becomes:

## shared_buffers (kilobytes)
## Sets the number of shared memory buffers used by the database server.
## Increasing the number of buffers makes it more likely backends will find
## the information they need in the cache, thus avoiding an expensive operating
## system request. The default is typically 1000, but may be less if your
## kernel settings will not support it. Each buffer is 8192 bytes. The minimum
## value is 16, or twice the value of the 'max_connections' parameter. Settings
## significantly higher than the minimum are usually needed for good performance.
## Values of a few thousand are recommended for production installations.
## URL: http://www.postgresql.org/docs/current/static/runtime-config.html

shared_buffers = 1000


## work_mem (kilobytes)
## (Previously known as sort_mem)
## Sets the amount of memory to be used by internal sort operations and hash tables before
## switching to temporary disk files. The default value is 1024 kilobytes (1 MB).
## Note that for a complex query, several sort or hash operations might be running in parallel;
## each one will be allowed to use up to the value set here before it starts to put data
## into temporary files. Also, several running sessions could be doing sort operations
## simultaneously. So the total memory used could be many times the value set here.

work_mem = 1024



- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200406090615

-----BEGIN PGP SIGNATURE-----

iD8DBQFAxuPnvJuQZxSWSsgRAgAtAKDReW6WOREYapbWi61yacBi05im6gCeLrvd
Dj/mlTtUh97C1gHVkJTtLyY=
=J0CZ
-----END PGP SIGNATURE-----

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Grega Bremec 2004-06-09 10:33:03 Refined LC_COLLATE or multiple database clusters?
Previous Message Tommi Maekitalo 2004-06-09 08:39:33 Re: Question regarding dynamic_library_path