Top Performance Related Parameters

Table of Contents Previous Next shared_buffers
Range: 128kB to system dependent
Sets the amount of memory the database server uses for shared memory buffers. The default is typically 32 megabytes (32MB), but might be less if your kernel settings will not support it (as determined during initdb). This setting must be at least 128 kilobytes. (Non-default values of BLCKSZ change the minimum.) However, settings significantly higher than the minimum are usually needed for good performance.
If you have a dedicated database server with 1GB or more of RAM, a reasonable starting value for shared_buffers is 25% of the memory in your system. There are some workloads where even large settings for shared_buffers are effective, but because Advanced Server also relies on the operating system cache, it is unlikely that an allocation of more than 40% of RAM to shared_buffers will work better than a smaller amount.
On systems with less than 1GB of RAM, a smaller percentage of RAM is appropriate, so as to leave adequate space for the operating system (15% of memory is more typical in these situations). Also, on Windows, large values for shared_buffers aren't as effective. You may find better results keeping the setting relatively low and using the operating system cache more instead. The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB. work_mem
Range: 64kB to 2097151kB
Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. The value defaults to one megabyte (1MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries.
Range: 1024kB to 2097151kB
Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 16 megabytes (16MB). Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps.
Note that when autovacuum runs, up to autovacuum_max_workers times this memory may be allocated, so be careful not to set the default value too high. wal_buffers
Range: 32kB to system dependent
The amount of memory used in shared memory for WAL data. The default is 64 kilobytes (64kB). The setting need only be large enough to hold the amount of WAL data generated by one typical transaction, since the data is written out to disk at every transaction commit.
Although even this very small setting does not always cause a problem, there are situations where it can result in extra fsync calls, and degrade overall system throughput. Increasing this value to 1MB or so can alleviate this problem. On very busy systems, an even higher value may be needed, up to a maximum of about 16MB. Like shared_buffers, this parameter increases Advanced Server’s initial shared memory allocation, so if increasing it causes an Advanced Server start failure, you will need to increase the operating system limit.
Parameter Type: Floating point
Range: 0 to 1
Range: 30s to 3600s
Maximum time between automatic WAL checkpoints, in seconds. The default is five minutes (5min). Increasing this parameter can increase the amount of time needed for crash recovery.
Increasing checkpoint_timeout to a larger value, such as 15 minutes, can reduce the I/O load on your system, especially when using large values for shared_buffers. max_wal_size
Range: 2 to 2147483647
max_wal_size specifies the maximum size that the WAL will reach between automatic WAL checkpoints. This is a soft limit; WAL size can exceed max_wal_size under special circumstances (when under a heavy load, a failing archive_command, or a high wal_keep_segments setting). min_wal_size
Range: 2 to 2147483647
If WAL disk usage stays below the value specified by min_wal_size, old WAL files are recycled for future use at a checkpoint, rather than removed. This ensures that enough WAL space is reserved to handle spikes in WAL usage (like when running large batch jobs). This parameter can only be set in the postgresql.conf file or on the server command line. bgwriter_delay
Range: 10ms to 10000ms
Specifies the delay between activity rounds for the background writer. In each round the writer issues writes for some number of dirty buffers (controllable by the bgwriter_lru_maxpages and bgwriter_lru_multiplier parameters). It then sleeps for bgwriter_delay milliseconds, and repeats.
The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting bgwriter_delay to a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10.
Typically, when tuning bgwriter_delay, it should be reduced from its default value. This parameter is rarely increased, except perhaps to save on power consumption on a system with very low utilization. seq_page_cost
Parameter Type: Floating point
Range: 0 to 1.79769e+308 random_page_cost
Parameter Type: Floating point
Range: 0 to 1.79769e+308
Reducing this value relative to seq_page_cost will cause the system to prefer index scans; raising it will make index scans look relatively more expensive. You can raise or lower both values together to change the importance of disk I/O costs relative to CPU costs, which are described by the cpu_tuple_cost and cpu_index_tuple_cost parameters.
Although the system will let you do so, never set random_page_cost less than seq_page_cost. However, setting them equal (or very close to equal) makes sense if the database fits mostly or entirely within memory, since in that case there is no penalty for touching pages out of sequence. Also, in a heavily-cached database you should lower both values relative to the CPU parameters, since the cost of fetching a page already in RAM is much smaller than it would normally be.
Range: 8kB to 17179869176kB
If this parameter is set too low, the planner may decide not to use an index even when it would be beneficial to do so. Setting effective_cache_size to 50% of physical memory is a normal, conservative setting. A more aggressive setting would be approximately 75% of physical memory.
Range: {true | false}
Unlike fsync, setting this parameter to off does not create any risk of database inconsistency: an operating system or database crash might result in some recent allegedly-committed transactions being lost, but the database state will be just the same as if those transactions had been aborted cleanly.
So, turning synchronous_commit off can be a useful alternative when performance is more important than exact certainty about the durability of a transaction. See Section 29.3, Asynchronous Commit in the PostgreSQL Core Documentation for information.
Range: {10 | 1000}
Use edb_max_spins_per_delay to specify the maximum number of times that a session will 'spin' while waiting for a spin-lock. If a lock is not acquired, the session will sleep. If you do not specify an alternative value for edb_max_spins_per_delay, the server will enforce the default value of 1000.

Table of Contents Previous Next