In this articles, we will explain about How to Tuning Your PostgreSQL Server. Actually, when we Tuning a server, we will customize list of parameters and configurations that own by a Server.
PostgreSQL ships with a basic configuration tuned for wide compatibility rather than performance. Odds are good the default parameters are very undersized for your system. Rather than get dragged into the details of everything you should eventually know (which you can find if you want it at the GUC Three Hour Tour), here we're going to sprint through a simplified view of the basics, with a look at the most common things people new to PostgreSQL aren't aware of. You should click on the name of the parameter in each section to jump to the relevant documentation in the PostgreSQL manual for more details after reading the quick intro here. There is also additional information available about many of these parameters, as well as a list of parameters you shouldn't adjust, at Server Configuration Tuning.
PostgreSQL settings can be manipulated a number of different ways, but generally you will want them changed in your configuration files, either directly or, starting with PostgreSQL 9.4, through ALTER SYSTEM. The specific options available change from release to release, the definitive list is in the source code at src/backend/utils/misc/guc.c for your version of PostgreSQL (but the pg_settings view works well enough for most purposes).
The types of settings
There are several different types of configuration settings, divided up based on the possible inputs they take
- Boolean: true, false, on, off
- Integer: Whole numbers (2112)
- Float: Decimal values (21.12)
- Memory / Disk: Integers (2112) or "computer units" (512MB, 2112GB). Avoid integers--you need to know the underlying unit to figure out what they mean.
- Time: "Time units" aka d,m,s (30s). Sometimes the unit is left out; don't do that
- Strings: Single quoted text ('pg_log')
- ENUMs: Strings, but from a specific list ('WARNING', 'ERROR')
- Lists: A comma separated list of strings ('"$user",public,tsearch2)
And, When they can take effect ??
When You Do Tuning Your PostgreSQL Server with above configuration settings, will have different levels of flexibility for when they can be changed, usually related to internal code restrictions. The complete list of levels is:
- Postmaster: requires restart of server
- Sighup: requires a HUP of the server, either by kill -HUP (usually -1), pg_ctl reload, or SELECT pg_reload_conf();
- User: can be set within individual sessions, take effect only within that session
- Internal: set at compile time, can't be changed, mainly for reference
- Backend: settings which must be set before session start
- Superuser: can be set at runtime for the server by superusers
Most of the time you'll only use the first of these, but the second can be useful if you have a server you don't want to take down, while the user session settings can be helpful for some special situations. You can tell which type of parameter a setting is by looking at the "context" field in the pg_settings view.
Important notes about configuration files
- Command line options override postgresql.auto.conf settings override postgresql.conf settings.
- If the same setting is listed multiple times, the last one wins.
- You can figure out the postgresql.conf location with SHOW config_file. It will generally be $PGDATA/postgresql.conf (SHOW data_directory), but watch out for symbolic links, postmaster.opts and other trickiness
- Lines with # are comments and have no effect. For a new database, this will mean the setting is using the default, but on running systems this may not hold true! Changes to the configuration files do not take effect without a reload/restart, so it's possible for the system to be running something different from what is in the file.
Viewing the current settings
- Look at the configuration files. This is generally not definitive!
- SHOW ALL, SHOW <setting> will show you the current value of the setting. Watch out for session specific changes
- SELECT * FROM pg_settings will label session specific changes as locally modified