9 Configuration Parameters

Source chapter 3.7 for PostgreSQL 11.

GASource pages: PDF 61-65Original PDF on GitHub

PDF page 61

In PostgreSQL 11 the following parameters have been changed.

3.7.1 Added parameters

The following parameters have been added.

Parameter name	Description (context)	Default
data_directory_mode	Database cluster protection mode (internal)	0700
enable_parallel_append	Enabled Parallel Append feature (user)	on
enable_parallel_hash	Enabled Parallel Hash feature (user)	on
enable_partition_pruning	Enabled partition pruning (user)	on
enable_partitionwise_aggregate	Enabled Partition-Wise Join feature (user)	off
enable_partitionwise_join	Enabled Partition-Wise Aggregate feature (user)	off
jit	Enabled JIT compilation feature (user)	off1
jit_above_cost	The Cost to enable JIT compile function (user) 100000	The Cost to enable JIT compile function (user) 100000
jit_debugging_support	Enable debugger at JIT compilation (superuser-backend)	off
jit_dump_bitcode	Add LLVM bit code (superuser)	off
jit_expressions	Enable JIT expression (user)	on
jit_inline_above_cost	The cost to decide whether to make JIT inlining (user)	500000
jit_optimize_above_cost	The cost to decide whether to perform JIT optimization (user)	500000
jit_profiling_support	Enabled perf profiler (superuser-backend)	off
jit_provider	JIT provider name (postmaster)	llvmjit
jit_tuple_deforming	Enabled JIT tuple deforming (user)	on
max_parallel_maintenance_workers Maximum number of parallel workers used in	max_parallel_maintenance_workers Maximum number of parallel workers used in maintenance processing (user)	2
parallel_leader_participation	Change the behavior of the leader process (user)	on
ssl_passphrase_command	Command to get SSL connection passphrases (sighup)	''
ssl_passphrase_command_supports_reload	Whether to use ssl_passphrase_command when reloading (sighup)	off
vacuum_cleanup_index_scale_factor	The ratio of the number of INSERTs that perform index cleanup (user)	0.1

Table 16 Added parameters

1 The default value was changed during Beta.

PDF page 62

parallel_leader_participation parameter

When a parallel query is executed, the leader process which coordinates the entire query and the worker processes cooperate with each other. In PostgreSQL 10, the leader process also executed the plan on the Gather and Gather Merge nodes. If this parameter is set to “off”, the leader process will not perform the same process as the worker process. The following example searches for table data of 10 million tuples. In both examples, two worker processes are running. In the first example, the portion of Parallel Seq Scan is executed three times with 3,333,333 tuples. In the second example where the parameter parallel_leader_participation is changed to “off”, it is executed in 2 loops with 5,000,000 tuples.

Example 77 Parameter parallel_leader_participation = on

postgres=> EXPLAIN ANALYZE SELECT COUNT(*) FROM data1 ;
                         QUERY PLAN
-----------------------------------------------------------------------
Finalize Aggregate  (cost=107139.46..107139.47 rows=1 width=8) (actual
time=8296.629..8296.630 rows=1 loops=1)
   ->  Gather  (cost=107139.25..107139.46 rows=2 width=8) (actual
time=8295.776..8296.622 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=106139.25..106139.26 rows=1
width=8) (actual time=8276.140..8276.141 rows=1 loops=3)
               ->  Parallel Seq Scan on data1  (cost=0.00..95722.40
rows=4166740 width=0) (actual time=0.096..4168.115 rows=3333333
loops=3)  Planning time: 0.026 ms  Execution time: 8296.693 ms
(8 rows)

PDF page 63

Example 78 Parameter parallel_leader_participation = off

postgres=> SET parallel_leader_participation = off ;
SET
postgres=> EXPLAIN ANALYZE SELECT COUNT(*) FROM data1 ;
                      QUERY PLAN
-----------------------------------------------------------------------
Finalize Aggregate  (cost=117556.31..117556.32 rows=1 width=8) (actual
time=8448.965..8448.965 rows=1 loops=1)
   ->  Gather  (cost=117556.10..117556.30 rows=2 width=8) (actual
time=8448.
278..8448.959 rows=2 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=116556.10..116556.10 rows=1
width=8) (actual time=8441.034..8441.035 rows=1 loops=2)
               ->  Parallel Seq Scan on data1  (cost=0.00..104055.88
rows=5000088 width=0) (actual time=0.214..4423.363 rows=5000000
loops=2)  Planning time: 0.026 ms  Execution time: 8449.088 ms
(8 rows)

3.7.2 Changed parameters

The setting range and options were changed for the following configuration parameters.

log_parser_stats	setting value is
log_statement_stats	changed to “on” log_statement_stats
log_planner_stats
log_executor_stats
wal_segment_size UNIT of pg_settings catalog changed from 8kB to B	wal_segment_size UNIT of pg_settings catalog changed from 8kB to B

Table 17 Changed configuration parameters

Parameter name Changes

log_parser_stats parameter

When the value is set to “on”, the information output is increased. Additional output information varies depending on the operating system. The example below is for Linux.

PDF page 64

Example 79 Parameter log_parser_stats (Linux)

postgres=# SHOW log_parser_stats ;
 log_parser_stats
------------------
 on
(1 row)
$ tail -11 data/log/postgresql-2018-10-22_092853.log
STATEMENT:  SHOW log_parser_stats ;
LOG:  REWRITER STATISTICS
DETAIL:  ! system usage stats:
        !       0.000002 s user, 0.000001 s system, 0.000001 s elapsed
        !       [0.370726 s user, 0.064474 s system total]
        !       12840 kB max resident size
        !       0/0 [984/0] filesystem blocks in/out
        !       0/0 [1/3208] page faults/reclaims, 0 [0] swaps
        !       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
        !       0/0 [28/4] voluntary/involuntary context switches

3.7.3 Parameters with default values changed

The default values of the following configuration parameters have been changed.

version	10.4	11.0
server_version_num	100004	110000

Table 18 Parameters with default values changed

Parameter name PostgreSQL 10 PostgreSQL 11

3.7.4 Obsoleted parameter

The following parameter is obsolete.

Parameter name	Alternative
replacement_sort_tuples	None

Table 19 Obsoleted parameter

PDF page 65

3.7.5 Authentication parameter change

Instance connect string

The following parameters have been added to the instance connection string of the Libpq library.

Parameter name	Default	Description
replication	false	Specify whether to use replication protocol true: Physical replication database: logical replication

Table 20 Added parameters

The default value of ssl_compression parameter has been changed to “off”.

LDAP Authentication parameter

The ldapschema parameter has been added for LDAP authentication. Specifying “ldaps” for the parameter uses LDAP over SSL.