latest--parameters
shared| Type | object |
|---|---|
| Schema URL | https://catalog.lintel.tools/schemas/schemastore/kontinuous-values-yaml/_shared/latest--parameters.json |
| Parent schema | kontinuous-values-yaml |
Properties
Allows tablespaces directly inside pg_tblspc, for testing.
Allows modifications of the structure of system tables.
Only available in single-user mode; this setting is for initdb and may be used in the future for upgrade-in-place.
Sets the application name to be reported in statistics and logs.
Set this to a reasonable default for most user sessions; if in the middle of working over your application to support application names, this might be "unknown".
Sets the shell command that will be executed at every restart point.
Sets the shell command that will be called to archive a WAL file.
All of the Archiving settings are part of a Point In Time Recovery or Warm Standby configuration. Please see the Backup and Restore section for more information.
Allows archiving of WAL files using archive_command.
Requires a restart to change, so if you want to turn archiving on and off, set this to 'on' and change archive_command instead. Even better, set archive_command to a script which can be disabled by trigger or ENV variable.
Forces a switch to the next WAL file if a new file has not been started within N seconds.
Minimum: 0
Maximum: 1073741823
Dependant on your tradeoff between disk space and letting the standby get behind.
Enable input of NULL elements in arrays.
Provided for compatibility with 7.4 behavior.
Sets the maximum allowed time to complete client authentication.
Minimum: 1
Maximum: 600
For production databases, it's important that this value be synchronized with the timeout on the application server side. Most web applications will want a shorter timeout, like 20s.
Starts the autovacuum subprocess.
Starts the daemon which cleans up your tables and indexes, preventing bloat and poor response times. The only reason to set it to "off" is for databases which regularly do large batch operations like ETL. Note that you can adjust the frequency or stop autovacuum on individual tables by adding rows to the pg_autovacuum system table.
Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
Minimum: 0
Maximum: 100
This setting should be optimal for most databases. However, very large tables (1m rows or more) in which rows are added in a skewed fashion may need to be autoanalyzed at a lower percentage, such as 5% or even 1%.
Minimum number of tuple inserts, updates, or deletes prior to analyze.
Minimum: 0
Maximum: 2147483647
Age at which to autovacuum a table to prevent transaction ID wraparound.
Minimum: 100000
Maximum: 2000000000
Triggers autovacuum automatically if a table is about to suffer from XID rollover. The setting is very conservative, and should probably be increased to 500million, but not higher.
Sets the maximum number of simultaneously running autovacuum worker processes.
Minimum: 1
Maximum: 262143
If you have an installation with many tables (100's to 1000's) or with some tables which autovacuum takes hours to process, you may want to add additional autovacuum workers so that multiple tables can be vacuumed at once. Be conservative, though, as each autovacuum worker will utilize a separate CPU core, memory and I/O.
Multixact age at which to autovacuum a table to prevent multixact wraparound.
Minimum: 10000
Maximum: 2000000000
Triggers autovacuum automatically when the oldest "multixact" (a kind of lock transaction) is more than this old. Do not raise past 1billion.
Time to sleep between autovacuum runs.
Minimum: 1
Maximum: 2147483
Decrease this to 30s or 15s if you have a large number (100's) of tables, or if you otherwise see from pg_stat_user_tables that autovacuum is not keeping up.
Vacuum cost delay in milliseconds, for autovacuum.
Minimum: -1
Maximum: 100
If autovacuum is having too much of a performance impact on running queries, you might want to increase this setting to 50ms. However, this will also cause individual vacuum tasks to take longer.
Vacuum cost amount available before napping, for autovacuum.
Minimum: -1
Maximum: 10000
Number of tuple inserts prior to vacuum as a fraction of reltuples.
Minimum: 0
Maximum: 100
Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums.
Minimum: -1
Maximum: 2147483647
Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.
Minimum: 0
Maximum: 100
Minimum number of tuple updates or deletes prior to vacuum.
Minimum: 0
Maximum: 2147483647
Sets the maximum memory to be used by each autovacuum worker process.
Minimum: -1
Maximum: 2147483647
Set a limit on this which is based on the number of autovac workers you expect to have running.
Number of pages after which previously performed writes are flushed to disk.
Minimum: 0
Maximum: 256
Unless you have time to tune memory flushing behavior and test for improvements/regressions
Sets whether "'" is allowed in string literals.
If you have cleaned up your application code, you can set this to 'off' to help lock down the database. Older PHP applications will require the insecure setting of 'on'.
Log backtrace for errors in these functions.
Background writer sleep time between rounds.
Minimum: 10
Maximum: 10000
Thanks to bgwriter autotuning, it should no longer be necessary for most users to touch the bgwriter settings. Only modify these if you have a demonstrated issue shown by checkpoint spikes and monitoring pg_stat_bgwriter. Laptop PostgreSQL users may want to increase bgwriter_delay to 60s to decrease I/O activity, since it is no longer possible to turn the bgwriter off.
Number of pages after which previously performed writes are flushed to disk.
Minimum: 0
Maximum: 256
Unless you have time to tune memory flushing behavior and test for improvements/regressions
Background writer maximum number of LRU pages to flush per round.
Minimum: 0
Maximum: 1073741823
Multiple of the average buffer usage to free per round.
Minimum: 0
Maximum: 10
Shows the size of a disk block.
Minimum: 8192
Maximum: 8192
Informational: lets you know of non-standard installation or compile options.
Enables advertising the server via Bonjour.
Set to "on" if you've compiled in Bonjour support and have an application which works by autodiscovery of Postgres. Otherwise, leave off.
Sets the Bonjour service name.
Bonjour support must be compiled in and activated on the host machine to be live. You'll want alternate names if you have several instances of PostgreSQL on the same machine.
Sets the output format for bytea.
Check routine bodies during CREATE FUNCTION and CREATE PROCEDURE.
You only really want to turn this off to resolve circular dependancies, and that can be done on a per-session basis. In general, checking for syntax errors in PL/pgSQL functions is a very good idea.
Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
Minimum: 0
Maximum: 1
Defines the fraction of one checkpoint_interval over which to spread checkpoints. The default value works for most users.
Number of pages after which previously performed writes are flushed to disk.
Minimum: 0
Maximum: 256
Unless you have time to tune memory flushing behavior and test for improvements/regressions
Sets the maximum time between automatic WAL checkpoints.
Minimum: 30
Maximum: 86400
If you do really large ETL batches, you may want to increase this setting to the maximum length of a batch run.
Enables warnings if checkpoint segments are filled more frequently than this.
Minimum: 0
Maximum: 2147483647
Sets the time interval between checks for disconnection while running queries.
Minimum: 0
Maximum: 2147483647
Sets the client's character set encoding.
Should match server_encoding unless you have a really good reason why not.
Sets the message levels that are sent to the client.
Unless doing interactive debugging, then you want it set to DEBUG1-5. If you have a client application which is confused by some of PostgreSQL's WARNINGs then you may want to set this to ERROR.
Sets the name of the cluster, which is included in the process title.
Should be "postgres-1" or something else identifiable as this specific postmaster.
Sets the delay in microseconds between transaction commit and flushing WAL to disk.
Minimum: 0
Maximum: 100000
A primitive form of group commit without asynchronicity. Performance testing of this is very mixed; only set to non-zero if you have time to test the specific performance impact on your workload. Reasonable values are 200 to 1000.
Sets the minimum concurrent open transactions before performing commit_delay.
Minimum: 0
Maximum: 1000
See commit_delay. Reasonable values are 3 to 8
Compute query identifiers.
Sets the server's main configuration file.
Can only be changed via command-line switch for obvious reasons. Useful primarily for testing different configuration options, or for automated restart with different configuration options.
Enables the planner to use constraints to optimize queries.
Default of "partition" is fine for most users. Setting it to "on" can allow optimization of UNION queries as well, but deserves testing before production deployment.
Sets the planner's estimate of the cost of processing each index entry during an index scan.
Minimum: 0
Maximum: 1.79769e+308
Decrease this slightly to make your database favor indexes slightly more.
Sets the planner's estimate of the cost of processing each operator or function call.
Minimum: 0
Maximum: 1.79769e+308
Decrease this slightly to make your database favor indexes slightly more.
Sets the planner's estimate of the cost of processing each tuple (row).
Minimum: 0
Maximum: 1.79769e+308
Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.
Minimum: 0
Maximum: 1
Increase this to 0.9 if most of the time you're using cursors to step through all of the rows of a query result.
Shows whether data checksums are turned on for this cluster.
This has to be set at initdb time, and does create a significant amount of extra I/O. However, it will save you from a corrupt database down the line, so if you're not performance-constrained, always use it.
Sets the server's data directory.
Supports the ability to distribute files according to sysadmin or operating system defined schemes, or for launching multiple restart instances using the same binaries. Most of the time, it's better to use configuration options to define these locations so that all PostgreSQL binaries default to the correct paths.
Whether to continue running after a failure to sync data files.
Sets the display format for date and time values.
Should be set according to the format in which you expect to receive date information.
Enables per-database user names.
This setting is a hack to work around the lack of per-database users in PostgreSQL. Unless you desperately need it, avoid this setting as it will eventually be replaced by something more maintainable.
Sets the time to wait on a lock before checking for deadlock.
Minimum: 1
Maximum: 2147483647
Default is fine, except when you are troubleshooting/monitoring locks. In that case, you may want to lower it to as little as 50ms.
Shows whether the running server has assertion checks enabled.
Used for debugging PostgreSQL code problems; not for production use. Requires compile options.
Aggressively flush system caches for debugging purposes.
Minimum: 0
Maximum: 0
Indents parse and plan tree displays.
For debugging a testing machine. Do not set in production.
Logs each query's parse tree.
For debugging a testing machine. Do not set in production.
Logs each query's execution plan.
For debugging a testing machine. Do not set in production.
Logs each query's rewritten parse tree.
For debugging a testing machine. Do not set in production.
Sets the default statistics target.
Minimum: 1
Maximum: 10000
Most applications can use the default of 100. For very small/simple databases, decrease to 10 or 50. Data warehousing applications generally need to use 500 to 1000. Otherwise, increase statistics targets on a per-column basis.
Sets the default table access method for new tables.
Sets the default tablespace to create tables and indexes in.
Change this if you want a different tablespace for user-created tables. Generally, better set on a ROLE or session basis.
Sets default text search configuration.
Set to the most common language used by the users, so that they don't have to pass the language parameter when calling TSearch functions.
Sets the default compression method for compressible values.
Sets the default deferrable status of new transactions.
If you use serializable transactions by default, it may be also useful to set this in order to decrease the overhead of long-running transactions.
Sets the transaction isolation level of each new transaction.
Relates to transaction_isolation. Better set on a session or transaction basis as transaction_isolation in order to support specific types of transaction conflict resolution.
Sets the default read-only status of new transactions.
This setting is mainly useful for preventing yourself from accidentally changing data. It is not really a security setting, as anyone can revoke it on their own session. Better set on a session or ROLE level. Will show up as TRUE if you are on a replication standby.
Sets the path for dynamically loadable modules.
Primarily useful if you've written lots of custom C libraries for your installation and want to organize them into custom directories.
Sets the planner's assumption about the total size of the data caches.
Minimum: 1
Maximum: 2147483647
Tells the PostgreSQL query planner how much RAM is estimated to be available for caching data, in both shared_buffers and in the filesystem cache. This setting just helps the planner make good cost estimates; it does not actually allocate the memory.
Number of simultaneous requests that can be handled efficiently by the disk subsystem.
Minimum: 0
Maximum: 1000
Set to the number of disks in your RAID array or number of I/O channels. Available only for platforms with posix_fadvise support (i.e. Linux). Currently only affects the execution of parallel bitmapscan, but might affect other I/O operations in future versions.
Enables the planner's use of async append plans.
Enables the planner's use of bitmap-scan plans.
For interactive session use only when troubleshooting queries.
Enables the planner's use of gather merge plans.
Enables the planner's use of hashed aggregation plans.
For interactive session use only when troubleshooting queries.
Enables the planner's use of hash join plans.
For interactive session use only when troubleshooting queries.
Enables the planner's use of incremental sort steps.
Enables the planner's use of index-only-scan plans.
Enables the planner's use of index-scan plans.
For interactive session use only when troubleshooting queries.
Enables the planner's use of materialization.
For interactive session use only when troubleshooting queries.
Enables the planner's use of memoization.
Enables the planner's use of merge join plans.
For interactive session use only when troubleshooting queries.
Enables the planner's use of nested-loop join plans.
For interactive session use only when troubleshooting queries.
Enables the planner's use of parallel append plans.
Enables the planner's use of parallel hash plans.
Enables plan-time and execution-time partition pruning.
Enables partitionwise aggregation and grouping.
Enables partitionwise join.
Enables the planner's use of sequential-scan plans.
For interactive session use only when troubleshooting queries.
Enables the planner's use of explicit sort steps.
For interactive session use only when troubleshooting queries.
Enables the planner's use of TID scan plans.
For interactive session use only when troubleshooting queries.
Warn about backslash escapes in ordinary string literals.
Useful for providing warnings for interpreted-language applications which may be engaging in unsafe string escape behavior. Unless you are currently porting or upgrading such an application, though, these warnings are not useful and should be turned off.
Sets the application name used to identify PostgreSQL messages in the event log.
Should be "postgres-1" or something else identifiable as this specific postmaster.
Terminate session on any error.
Path to prepend for extension loading
Writes the postmaster PID to the specified file.
Creates an extra copy of the process ID. Used for server administration tools which need a copy of the process ID in a specific directory.
Sets the number of digits displayed for floating-point values.
Minimum: -15
Maximum: 3
Only significant for applications which do a lot of float calculations, like scientific databases.
Forces use of parallel query facilities.
Sets the FROM-list size beyond which subqueries are not collapsed.
Minimum: 1
Maximum: 2147483647
While it's probably true that newer CPUs could support higher collapse_limits, there's not much incremental benefit to just raising either collapse_limit to 10 or 11.
Forces synchronization of updates to disk.
Never turn off unless your data is entirely disposable. Setting fsync=off is the equivalent of saying " don't care about my data, I can recreate the database from scratch if I have to... If synch activity is a performance concern, see synchronous_commit.
Writes full pages to WAL when first modified after a checkpoint.
This is PostgreSQL's triple-check on transaction log integrity. Leave it on unless you have enough in-depth knowledge of your filesystem and hardware to be certain that torn page writes of log segments are completely prevented. Solaris/ZFS users claim to be able to turn this off, but that has not been destruction-tested.
Enables genetic query optimization.
GEQO: effort is used to set the default for other GEQO parameters.
Minimum: 1
Maximum: 10
GEQO: number of iterations of the algorithm.
Minimum: 0
Maximum: 2147483647
GEQO: number of individuals in the population.
Minimum: 0
Maximum: 2147483647
GEQO: seed for random path selection.
Minimum: 0
Maximum: 1
If you set this manually, you can force repeatable execution paths for GEQO queries.
GEQO: selective pressure within the population.
Minimum: 1.5
Maximum: 2
Sets the threshold of FROM items beyond which GEQO is used.
Minimum: 2
Maximum: 2147483647
With new, faster processors it's tempting to raise the geqo_threshold a little, such as to 16 or 18. Increasing more than that is unwise as query planning time goes up geometrically.
Sets the maximum allowed result for exact search by GIN.
Minimum: 0
Maximum: 2147483647
If you're going to use GIN queries in a web application, it's generally useful to set a limit on how many rows can be returned from the index just for response times. However, the maximum number needs to depend on your application; what do users see as an acceptable expression of "many"?
Sets the maximum size of the pending list for GIN index.
Minimum: 64
Maximum: 2147483647
Unless you have a lot of GIN indexed data and have time to test the performance of fastupdate. Even then, it's probably better to set it on individual indexes.
Multiple of work_mem to use for hash tables.
Minimum: 1
Maximum: 1000
Sets the server's "hba" configuration file.
Allows you to move the pg_hba file to a sysadmin-specified location.
Allows connections and queries during recovery.
Set to "on", unless you want to specifically prohibit people from running queries on a standby server.
Allows feedback from a hot standby to the primary that will avoid query conflicts.
Helps avoid query cancel on the replicas in most cases. Turn it off for a replica which does long-running reports and is allowed to lag.
The size of huge page that should be requested.
Minimum: 0
Maximum: 2147483647
Use of huge pages on Linux or Windows.
However, for small systems (< 2GB of RAM) may be beneficial to set to "off".
Sets the server's "ident" configuration file.
Allows you to move the pg_ident file to a sysadmin-specified location.
Sets the maximum allowed idle time between queries, when in a transaction.
Minimum: 0
Maximum: 2147483647
Set to 1 hour maximum, or as low as 1 minute if you know your query load well. Idle transactions are bad news.
Sets the maximum allowed idle time between queries, when not in a transaction.
Minimum: 0
Maximum: 2147483647
Continues processing after a checksum failure.
For rescuing a corrupt DB
Continues recovery after an invalid pages failure.
Disables reading from system indexes.
Useful for salvaging data from a corrupted database.
Shows whether hot standby is currently active.
Shows whether datetimes are integer based.
Informational: lets you know of non-standard installation or compile options.
Sets the display format for interval values.
This is just in case your applications are expecting something specific in how INTERVAL strings are output.
Allow JIT compilation.
Perform JIT compilation if query is more expensive.
Minimum: -1
Maximum: 1.79769e+308
Register JIT-compiled functions with debugger.
Write out LLVM bitcode to facilitate JIT debugging.
Allow JIT compilation of expressions.
Perform JIT inlining if query is more expensive.
Minimum: -1
Maximum: 1.79769e+308
Optimize JIT-compiled functions if query is more expensive.
Minimum: -1
Maximum: 1.79769e+308
Register JIT-compiled functions with perf profiler.
JIT provider to use.
Allow JIT compilation of tuple deforming.
Sets the FROM-list size beyond which JOIN constructs are not flattened.
Minimum: 1
Maximum: 2147483647
If for some reason you wanted to explicitly declare the join order for all of your queries, you could set this to 1. That is not recommended, though.
Sets whether Kerberos and GSSAPI user names should be treated as case-insensitive.
Speak with your sysadmin or network security about how to set the various kerberos settings to match your local kerberos setup. Kerberos support must be compiled in to PostgreSQL, and set in pg_hba.conf.
Sets the location of the Kerberos server key file.
Shows the collation order locale.
Set at initdb time. Displayed for information only.
Shows the character classification and case conversion locale.
Set at initdb time. Displayed for information only.
Sets the language in which messages are displayed.
Sets the locale for formatting monetary amounts.
Sets the locale for formatting numbers.
Sets the locale for formatting date and time values.
Sets the host name or IP address(es) to listen to.
Set your listen_address as restrictively as possible; '*' should only be used for development machines
Enables backward compatibility mode for privilege checks on large objects.
Lists unprivileged shared libraries to preload into each backend.
This is largely a convenience setting, automatically loading libraries listed without needing an explicit load command. Has no effect on performance.
Sets the maximum allowed duration of any wait for a lock.
Minimum: 0
Maximum: 2147483647
... but consider setting this per application or per query for any explicit locking attempts.
Sets the minimum execution time above which autovacuum actions will be logged.
Minimum: -1
Maximum: 2147483647
Logs all autovacuum actions which take more than the specified time. Useful for figuring out if autovacuum is bogging down your system or blocking.
Logs each checkpoint.
When doing performance analysis, it's often a good idea to turn on most of the logging options and log them to a CSVlog.
Sets the destination for server log output.
Your choice of log destination depends on your system administration plans and the status of your server. "syslog" or "eventlog" (Windows) are good choices for most development servers, because they can support centralized log monitors. For development and testing, however, "csvlog" is probably the most useful, as it allows you to run queries against the log contents.
Sets the destination directory for log files.
If you are having PostgreSQL keep its own activity logs on a production server, it's probably a good idea to locate them on separate storage from the database and transaction log.
Logs end of a session, including duration.
Useful for performance analysis.
Logs the duration of each completed SQL statement.
Useful for performance analysis.
Sets the verbosity of logged messages.
Unless doing intensive debugging. Alternately, set to TERSE if managing log volume is becoming a problem.
Writes executor performance statistics to the server log.
Used for profiling the query executor.
Sets the file permissions for log files.
Minimum: 0
Maximum: 511
... unless you need to share the log with Postgres' unix group, in which case set it to 660.
Sets the file name pattern for log files.
If you want your logs to rotate automatically without needing a cron job to delete old logs, try naming them after the days of the week or the month so they overwrite automatically (i.e. 'postgresql-%a' or 'postgresql-%d'). This also helps with log analysis.
Logs the host name in the connection logs.
As this setting requires resolution of each connecting hostname, it's pretty much always too expensive to have on, even when troubleshooting.
Controls information prefixed to each log line.
Primarily useful for providing extra information when logging to syslog or eventlog. Try "%h:%d:%u:%c %t" for this.
Sets the minimum execution time above which a sample of statements will be logged. Sampling is determined by log_statement_sample_rate.
Minimum: -1
Maximum: 2147483647
Sets the minimum execution time above which all statements will be logged.
Minimum: -1
Maximum: 2147483647
Possibly the most generally useful log setting for troubleshooting performance, especially on a production server. Records only long-running queries for analysis; since these are often your "problem" queries, these are the most useful ones to know about. Used for pg_fouine.
Causes all statements generating error at or above this level to be logged.
Logs SQL statements which error. If you have an application which routinely generates errors and can't fix it, then raise the level to FATAL or PANIC.
Sets the message levels that are logged.
Unless doing serious troubleshooting. If you want to output parses and plans, set to DEBUG1.
When logging statements, limit logged parameter values to first N bytes.
Minimum: -1
Maximum: 1073741823
When reporting an error, limit logged parameter values to first N bytes.
Minimum: -1
Maximum: 1073741823
Writes parser performance statistics to the server log.
Used for profiling the query parser.
Writes planner performance statistics to the server log.
Used for profiling the query planner.
Logs standby recovery conflict waits.
Logs each replication command.
... assuming you're monitoring replication status, which you should.
Automatic log file rotation will occur after N minutes.
Minimum: 0
Maximum: 35791394
1d is generally good for production. Set to 1h to rotate logs hourly when doing performance analysis.
Automatic log file rotation will occur after N kilobytes.
Minimum: 0
Maximum: 2097151
Default is quite small if you have any extra logging turned on at all. Increase to avoid the creation of additional log segments with hard-to-predict names.
Sets the type of statements logged.
For exhaustive performance analysis on test systems, set to 'all'. Most production setups will just want to use 'ddl' to make sure to record database-altering actions, but very secure setups may want to use 'mod' or even 'all'. Can produce a lot of log volume.
Fraction of statements exceeding log_min_duration_sample to be logged.
Minimum: 0
Maximum: 1
Writes cumulative performance statistics to the server log.
Used for full query path profiling. Exclusive of the other three options.
Log the use of temporary files larger than this number of kilobytes.
Minimum: -1
Maximum: 2147483647
This logger is used for troubleshooting sorts and other activities which are spilling to disk. If you use it at all, it's probably good to set it a something low like 1kB so that you know each query that spilled to disk, since any disk spill at all causes a dramatic slowdown in the query. Can be used to see if you need more work_mem, temp_mem or maintenance_work_mem.
Sets the time zone to use in log messages.
To avoid confusion, it's often useful to log to the timezone where the DBA or sysadmin lives.
Sets the fraction of transactions from which to log all statements.
Minimum: 0
Maximum: 1
Truncate existing log files of same name during log rotation.
Set to "on" for production with a reusable logfile name to limit log accumulation if you don't have a sysadmin script to do so.
Start a subprocess to capture stderr output and/or csvlogs into log files.
Only relevant for "csvlog" and "stderr".
Sets the maximum memory to be used for logical decoding.
Minimum: 64
Maximum: 2147483647
A variant of effective_io_concurrency that is used for maintenance work.
Minimum: 0
Maximum: 1000
Sets the maximum memory to be used for maintenance operations.
Minimum: 1024
Maximum: 2147483647
Sets the limit for the amount that autovacuum, manual vacuum, bulk index build and other maintenance routines are permitted to use. Setting it to a moderately high value will increase the efficiency of vacuum and other operations. Applications which perform large ETL operations may need to allocate up to 1/4 of RAM to support large bulk vacuums. Note that each autovacuum worker may use this much, so if using multiple autovacuum workers you may want to decrease this value so that they can't claim over 1/8 or 1/4 of available RAM.
Sets the maximum number of concurrent connections.
Minimum: 1
Maximum: 262143
Should be set to the maximum number of connections which you expect to need at peak load. Note that each connection uses shared_buffer memory, as well as additional non-shared memory, so be careful not to run the system out of memory. In general, if you need more than 200 connections, you should probably be making more use of connection pooling.
Sets the maximum number of simultaneously open files for each server process.
Minimum: 64
Maximum: 2147483647
If you have a large database with many partitioned tables, you may want to increase this. Note that you will probably have to increase ulimits for the postgres user or system as well.
Shows the maximum number of function arguments.
Minimum: 100
Maximum: 100
Informational: lets you know of non-standard installation or compile options.
Shows the maximum identifier length.
Minimum: 63
Maximum: 63
Informational: lets you know of non-standard installation or compile options.
Shows the maximum number of index keys.
Minimum: 32
Maximum: 32
Informational: lets you know of non-standard installation or compile options.
Sets the maximum number of locks per transaction.
Minimum: 10
Maximum: 2147483647
Some databases with very complex schema or with many long-running tranactions need a higher amount. This is rare though.
Maximum number of logical replication worker processes.
Minimum: 0
Maximum: 262143
... unless logical replication is falling behind and the replica isn't handling other traffic
Sets the maximum number of parallel processes per maintenance operation.
Minimum: 0
Maximum: 1024
Sets the maximum number of parallel workers that can be active at one time.
Minimum: 0
Maximum: 1024
... if you think you can benefit from parallel query, and even cores/1 for DW systems.
Sets the maximum number of parallel processes per executor node.
Minimum: 0
Maximum: 1024
Increase if you plan to use parallel query to 4 or 8, depending on cores/concurrent sessions.
Sets the maximum number of predicate-locked tuples per page.
Minimum: 0
Maximum: 2147483647
Sets the maximum number of predicate-locked pages and tuples per relation.
Minimum: -2147483648
Maximum: 2147483647
Sets the maximum number of predicate locks per transaction.
Minimum: 10
Maximum: 2147483647
Raise if you have a lot of tables and are seeing some transactions fail, but modestly as a larger transaction table is expensive.
Sets the maximum number of simultaneously prepared transactions.
Minimum: 0
Maximum: 262143
Most applications do not use XA prepared transactions, so should set this parameter to 0. If you do require prepared transactions, you should set this equal to max_connections to avoid blocking. May require increasing kernel memory parameters.
Sets the maximum number of simultaneously defined replication slots.
Minimum: 0
Maximum: 262143
Set to twice as many replicas as you ever expect to have.
Sets the maximum WAL size that can be reserved by replication slots.
Minimum: -1
Maximum: 2147483647
Sets the maximum stack depth, in kilobytes.
Minimum: 100
Maximum: 2147483647
Increase this if you have experienced the relevant error.
Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data.
Minimum: -1
Maximum: 2147483647
If you are replicating primarily for failover, set this to a very low value (like 0) in order to keep the standby as up to date as possible. If this standby is running queries as its primary role, set to the length of time of the longest-running query you want to allow.
Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data.
Minimum: -1
Maximum: 2147483647
If you are replicating primarily for failover, set this to a very low value (like 0) in order to keep the standby as up to date as possible. If this standby is running queries as its primary role, set to the length of time of the longest-running query you want to allow.
Maximum number of table synchronization workers per subscription.
Minimum: 0
Maximum: 262143
Consider raising to cores/2 when initially synchronizing logical replication for a new replica.
Sets the maximum number of simultaneously running WAL sender processes.
Minimum: 0
Maximum: 262143
If you are replicating, you want to set this to the maximum number of standby servers you might possibly have. Performance impact when set above zero, but no additional penalty for setting it higher.
Sets the WAL size that triggers a checkpoint.
Minimum: 2
Maximum: 2147483647
... except for databases that write more than 1GB/hour of data, in which case increase the size of the log so that it's at least an hour worth of logs
Maximum number of concurrent worker processes.
Minimum: 0
Maximum: 262143
Increase to max_parallel_workers + other workers, such as workers for logical replication and custom background workers. Not more than your number of cores, though.
Sets the minimum amount of index data for a parallel scan.
Minimum: 0
Maximum: 715827882
Sets the minimum amount of table data for a parallel scan.
Minimum: 0
Maximum: 715827882
... , unless doing IoT or a read-only database. Raise to 100MB or so if your traffic on the database is very bursty, to prevent the WAL from shrinking too much.
Sets the minimum size to shrink the WAL to.
Minimum: 2
Maximum: 2147483647
Time before a snapshot is too old to read pages changed after the snapshot was taken.
Minimum: -1
Maximum: 86400
... or the length of the longest transaction you expect to run + 1 hour.
Controls whether Gather and Gather Merge also run subplans.
Sets the planner's estimate of the cost of starting up worker processes for parallel query.
Minimum: 0
Maximum: 1.79769e+308
Sets the planner's estimate of the cost of passing each tuple (row) from worker to leader backend.
Minimum: 0
Maximum: 1.79769e+308
Chooses the algorithm for encrypting passwords.
There is no good reason for this to be set to "off".
Sets the maximum number of statements tracked by pg_stat_statements.
Minimum: 100
Maximum: 1073741823
Save pg_stat_statements statistics across server shutdowns.
Selects which statements are tracked by pg_stat_statements.
Selects whether planning duration is tracked by pg_stat_statements.
Selects whether utility commands are tracked by pg_stat_statements.
Controls the planner's selection of custom or generic plan.
Sets the TCP port the server listens on.
Minimum: 1
Maximum: 65535
Alternate ports are primarily useful for running several versions, or instances, of PostgreSQL on one machine. However, if you're using an alternate port to support several versions, it's often better to compile in the port number.
Waits N seconds on connection startup after authentication.
Minimum: 0
Maximum: 2147
Primarily used for attaching debuggers to sessions.
Waits N seconds on connection startup before authentication.
Minimum: 0
Maximum: 60
Primarily used for attaching debuggers to sessions.
Sets the connection string to be used to connect to the sending server.
Sets the name of the replication slot to use on the sending server.
Specifies a file name whose presence ends recovery in the standby.
When generating SQL fragments, quote all identifiers.
Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
Minimum: 0
Maximum: 1.79769e+308
Sets the ratio of seek to scan time for your database storage. Should not be altered unless you're using special storage (SSDs, high end SANs, etc.) where seek/scan ratios are actually different. If you need the database to favor indexes more, tune effective_cache_size and some of the cpu_* costs instead.
Sets the shell command that will be executed once at the end of recovery.
Sets the method for synchronizing the data directory before crash recovery.
Sets the minimum delay for applying changes during recovery.
Minimum: 0
Maximum: 2147483647
Set to "immediate" to end recovery as soon as a consistent state is reached.
Sets the action to perform upon reaching the recovery target.
Sets whether to include or exclude transaction with recovery target.
Sets the LSN of the write-ahead log location up to which recovery will proceed.
Sets the named restore point up to which recovery will proceed.
Sets the time stamp up to which recovery will proceed.
Specifies the timeline to recover into.
Sets the transaction ID up to which recovery will proceed.
Remove temporary files after backend crash.
Reinitialize server after backend crash.
... unless deliberately running postgres in "ephemral" mode
Sets the shell command that will be called to retrieve an archived WAL file.
Sets the schema search order for names that are not schema-qualified.
Most DBAs either use the default or set search_path on a ROLE or database object basis. The one reason to set it in postgresql.conf is if you are taking the security step of removing the special "public" schema in order to lock down your database.
Shows the number of pages per disk file.
Minimum: 131072
Maximum: 131072
Informational: lets you know of non-standard installation or compile options.
Sets the planner's estimate of the cost of a sequentially fetched disk page.
Minimum: 0
Maximum: 1.79769e+308
The main reason to modify seq_page_cost is to try to get planner costs to more-or-less indicate execution times in milleseconds. All other costs change relative to this cost automatically.
Shows the server (database) character set encoding.
Set at initdb time. Displayed for information only.
Shows the server version.
Informational: lets you know of non-standard installation or compile options.
Shows the server version as an integer.
Minimum: 140009
Maximum: 140009
Informational: lets you know of non-standard installation or compile options.
Lists shared libraries to preload into each backend.
Special uses for debugging or for loading application-specific extensions.
Sets the session's behavior for triggers and rewrite rules.
Only gets changed for databases which are taking part in a replication chain. In that case, "origin" servers fire replication (and other) triggers, and "replica" do not. Part of the generic replication hooks which are used by Slony and Bucardo.
Enables SSL connections.
One of several different settings to turn on SSL connections for PostgreSQL. SSL is a very good idea for highly secure setups. In addition, you must compile in SSL support and set SSL connections in pg_hba.conf, as well as configuring SSL itself.
Location of the SSL certificate authority file.
You should always use SSL connections if you can. However, this does require setting up SSL.
Location of the SSL server certificate file.
According to your SSL configuration, which maybe provided by your installer.
Sets the list of allowed SSL ciphers.
Allows DBAs to require "str" enough or preset ciphers for SSL connections. If you have not compiled SSL support, this parameter will not be available.
Location of the SSL certificate revocation list directory.
Location of the SSL certificate revocation list file.
According to your SSL configuration, which maybe provided by your installer.
Location of the SSL DH parameters file.
According to your SSL configuration, which maybe provided by your installer.
Sets the curve to use for ECDH.
According to your SSL configuration, which maybe provided by your installer.
Location of the SSL server private key file.
According to your SSL configuration, which maybe provided by your installer.
Shows the name of the SSL library.
Sets the maximum SSL/TLS protocol version to use.
Sets the minimum SSL/TLS protocol version to use.
Command to obtain passphrases for SSL.
Also use ssl_passphrase_command during server reload.
Give priority to server ciphersuite order.
According to your SSL configuration, which maybe provided by your installer.
Causes '...' strings to treat backslashes literally.
If you can clean up your application code, this disables use of as an escape character except in escaped (E' ') strings. This is both safer, and less likely to result in unexpected output for things like Windows filepaths.
Sets the maximum allowed duration of any statement.
Minimum: 0
Maximum: 2147483647
Defaults to 0, meaning no timeout. For most web applications, it's a good idea to set a default timeout, such as 60s to prevent runaway queries from bogging the server. If set, though, you need to remember to set (at the ROLE or session level) a higher statement_timeout for expected long-running maintenance or batch operations.
Writes temporary statistics files to the specified directory.
Useful for extremely high-volume databases; the stats temp directory could be set to a RAMdisk or other high-speed resource (at the cost of potentially losing some stats) as this file gets updated hundreds of times per second.
Sets the number of connection slots reserved for superusers.
Minimum: 0
Maximum: 262143
You should have at least one superuser connection open for troubleshooting at all times. So if you run more than two concurrent regular administrative tasks, you'll need more reserved connections. Note that this number is taken from max_connections, not in addition to it.
Enable synchronized sequential scans.
This new peformance enhancment can also cause rows to be returned in an order other than physical storage order. For poorly-written older applications, this may break application code; turn it off to disable.
Sets the current transaction's synchronization level.
If data integrity is less important to you than response times (for example, if you are running a social networking application or processing logs) you can turn this off, making your transaction logs asynchronous. This can result in up to wal_buffers or wal_writer_delay * 2 worth of data in an unexpected shutdown, but your database will not be corrupted. Note that you can also set this on a per-session basis, allowing you to mix "lossy" and "safe" transactions, which is a better approach for most applications.
Number of synchronous standbys and list of names of potential synchronous ones.
Special, see syntax for sync standby config. Don't get into this if you're not sure what you're doing.
Sets the syslog "facility" to be used when syslog enabled.
Change the logserver facility if you are having a conflict with other applications.
Sets the program name used to identify PostgreSQL messages in syslog.
If using a centralized logserver or if you have multiple Postgres instances, you probably want to identify your postgresql instance by hostname.
Add sequence number to syslog messages to avoid duplicate suppression.
Split messages sent to syslog by lines and to fit into 1024 bytes.
Maximum number of TCP keepalive retransmits.
Minimum: 0
Maximum: 2147483647
The three tcp_keepalive settings help manage a system which tends to have "undead" connection/query processes. For systems which support them, you can regulate checking that connections are still "live" end-to-end to kill them off. Not needed if you're not having a problem. Should be synchronized with the new TCP keepalive support in libpq on the client side.
Time between issuing TCP keepalives.
Minimum: 0
Maximum: 2147483647
Time between TCP keepalive retransmits.
Minimum: 0
Maximum: 2147483647
Sets the maximum number of temporary buffers used by each session.
Minimum: 100
Maximum: 1073741823
Currently used only for holding temporary tables in memory. If your application requires heavy use of temporary tables (many proprietary reporting engines do) then you might want to increase this substantially. However, be careful because this is non-shared RAM which is allocated per session. Otherwise, the default is fine.
Limits the total size of all temporary files used by each process.
Minimum: -1
Maximum: 2147483647
... or something which is bigger than your largest possible sort, but not big enough to run you out of disk space.
Sets the tablespace(s) to use for temporary tables and sort files.
For applications which create lots of temporary objects, this setting can be used to put the temp space on a faster/separate device, or even a ramdisk. Because it accepts a list, it can even be used to load balance temp object creation among several tablespaces.
Sets the time zone for displaying and interpreting time stamps.
To avoid a lot of confusion, make sure this is set to your local timeszone. If the server covers multiple time zones, then this should be set on a ROLE or connection basis.
Selects a file of time zone abbreviations.
See appendencies for alternatives.
Generates debugging output for LISTEN and NOTIFY.
The various TRACE options are for debugging specific behaviors interactively. Many of them require compile-time options. trace_notice is for debugging listen/notice.
Enables logging of recovery-related debugging information.
For troubleshooting replication/PITR failures.
Collects information about executing commands.
Sets the size reserved for pg_stat_activity.query, in bytes.
Minimum: 100
Maximum: 1048576
Sets the truncation threshold of queries in pg_stat_activity (and pg_stat_statements). Increase it if you have really long queries which are being cut off, but there is significant extra memory usage for keeping longer queries.
Collects transaction commit time.
Collects statistics on database activity.
Needed for autovacuum to work properly. Do not turn off.
Collects function-level statistics on database activity.
Set it to 'pl' to collect stats on user-defined functions. Very useful for stored procedure performance profiling and troubleshooting.
Collects timing statistics for database I/O activity.
Turn it on if you're monitoring disk usage per request.
Collects timing statistics for WAL I/O activity.
Whether to defer a read-only serializable transaction until it can be executed with no possible serialization failures.
Sets the current transaction's isolation level.
Set per session if you need, for example, SERIALIZABLE semantics to prevent data conflicts for multi-step transactions.
Sets the current transaction's read-only status.
Sets the current transaction to read only. Useful as part of a SQL injection prevention program. Shows as TRUE on replication standbys.
Treats "expr=NULL" as "expr IS NULL".
Provided for compatibility with Microsoft Access and similar broken applications which treat "= NULL" as the same as "IS NULL".
Sets the directories where Unix-domain sockets will be created.
Change to a more secure directory, which many installers do for you.
Sets the owning group of the Unix-domain socket.
Sets the access permissions of the Unix-domain socket.
Minimum: 0
Maximum: 511
Updates the process title to show the active SQL command.
Updates the process title on OSes which support this. Very useful for checking resource usage by currently running queries.
Vacuum cost delay in milliseconds.
Minimum: 0
Maximum: 100
Most of the time, you will want manual vacuum to execute without vacuum_delay, especially if you're using it as part of ETL. If for some reason you can't use autovacuum on an OLTP database, however, you may want to increase this to 20ms to decrease the impact vacuum has on currently running queries. Will cause vacuum to take up to twice as long to complete.
Vacuum cost amount available before napping.
Minimum: 1
Maximum: 10000
Vacuum cost for a page dirtied by vacuum.
Minimum: 0
Maximum: 10000
Vacuum cost for a page found in the buffer cache.
Minimum: 0
Maximum: 10000
Vacuum cost for a page not found in the buffer cache.
Minimum: 0
Maximum: 10000
Number of transactions by which VACUUM and HOT cleanup should be deferred, if any.
Minimum: 0
Maximum: 1000000
No longer effective thanks to hot_standby_feedback.
Age at which VACUUM should trigger failsafe to avoid a wraparound outage.
Minimum: 0
Maximum: 2100000000
Minimum age at which VACUUM should freeze a table row.
Minimum: 0
Maximum: 1000000000
Most users will want to decrease this so that rows which have been cold for a long time get frozen earlier, and avoid an autovacuum_freeze. The suggestion of 500000 is for a moderately busy database; do not set to less than a few hours worth of XIDs. Maximum setting is 1/2 of autovaccuum_max_freeze_age.
Age at which VACUUM should scan whole table to freeze tuples.
Minimum: 0
Maximum: 2000000000
Generally set to 80% of autovacuum_max_freeze age to preempt a full vacuum freeze. If you can schedule cron vacuums during application slow periods, it might be valuable to lower this value in order to encourage vacuum freezing of tables before they are triggered by autovacuum.
Multixact age at which VACUUM should trigger failsafe to avoid a wraparound outage.
Minimum: 0
Maximum: 2100000000
Minimum age at which VACUUM should freeze a MultiXactId in a table row.
Minimum: 0
Maximum: 1000000000
Like freeze_min_age, lower this to somewhere around an hour of XID burn. Try starting with 500000.
Multixact age at which VACUUM should scan whole table to freeze tuples.
Minimum: 0
Maximum: 2000000000
Set to 80% of autovaccum_multixact_freeze_max_age
Shows the block size in the write ahead log.
Minimum: 8192
Maximum: 8192
Informational: lets you know of non-standard installation or compile options.
Sets the number of disk-page buffers in shared memory for WAL.
Minimum: -1
Maximum: 262143
On very busy, high-core machines it can be useful to raise this to as much as 128MB.
Compresses full-page writes written in WAL file.
... unless your storage is less constrained than your CPU.
Sets the WAL resource managers for which WAL consistency checks are done.
Writes zeroes to new WAL files before first use.
Sets the size of WAL files held for standby servers.
Minimum: 0
Maximum: 2147483647
Sets the level of information written to the WAL.
Level replica is required for binary replication, and level logical is required for logical replication. This is a setting because raising the level adds more writes to the WAL, so if you're not doing replication or archiving at all, set it to minimal.
Writes full pages to WAL when first modified after a checkpoint, even for a non-critical modification.
Sets whether a WAL receiver should create a temporary replication slot if no permanent slot is configured.
Sets the maximum interval between WAL receiver status reports to the sending server.
Minimum: 0
Maximum: 2147483
Sets the maximum wait time to receive data from the sending server.
Minimum: 0
Maximum: 2147483647
Recycles WAL files by renaming them.
Sets the time to wait before retrying to retrieve WAL after a failed attempt.
Minimum: 1
Maximum: 2147483647
Shows the size of write ahead log segments.
Minimum: 1048576
Maximum: 1073741824
Informational: lets you know of non-standard installation or compile options.
Sets the maximum time to wait for WAL replication.
Minimum: 0
Maximum: 2147483647
Minimum size of new file to fsync instead of writing WAL.
Minimum: 0
Maximum: 2147483647
Selects the method used for forcing WAL updates to disk.
On install, PostgreSQL figures out the best method for your OS. It's pretty good at this point; don't change the default. Note that the value of "fsync" shown in your postgresql.conf file is not necessarily the setting the server is using; try SHOW instead.
Time between WAL flushes performed in the WAL writer.
Minimum: 1
Maximum: 10000
Defines the maximum data (in time) that can be lost if synchronous_commit=off and the database shuts down. Because of long transactions, actual data lost can be up to twice this time. Has no effect if synchronous_commit=on. If you are going to turn synchronous_commit=off server-wide, you should probably lower this to prevent too much data loss.
Amount of WAL written out by WAL writer that triggers a flush.
Minimum: 0
Maximum: 2147483647
Sets the maximum memory to be used for query workspaces.
Minimum: 64
Maximum: 2147483647
Sets the limit for the amount of non-shared RAM available for each query operation, including sorts and hashes. This limit acts as a primitive resource control, preventing the server from going into swap due to overallocation. Note that this is non-shared RAM per operation, which means large complex queries can use multple times this amount. Also, work_mem is allocated by powers of two, so round to the nearest binary step. The second formula is for reporting and DW servers which run a lot of complex queries.
Sets how binary values are to be encoded in XML.
Set to whatever your client application supports.
Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.
Continues processing past damaged page headers.
Used for salvaging data from a known-bad database. You should always make a binary backup before using this option, and it should not be used while users are allowed to connect. After damaged pages are erased, other kinds of data intergrity errors may persist (like broken PKs and FKs). ZDP should generally be used to get your DB to a stage where the data can be dumped and loaded into a new database.