Small knowledge, big challenge! This article is participating in the “Essential Tips for Programmers” creation campaign. This article also participated in the “Digitalstar Project” to win the creation package and challenge the creation incentive money. We studied and learned how to set the postgresql.conf parameter with reference to ali Cloud best practices and the official PostgresQL manual. (Only the parameters that need to be modified are listed below. Those whose default values are appropriate are not listed.)

Connection Settings

Listen_addresses = '*' max_connections = 2000 superuser_reserved_connections = 10Copy the code

Listen_addresses: specifies the TCP/IP addresses on which the server listens for client connections. The default value is localhost, allowing only local connections. Max_connections: Determines the maximum number of concurrent connections for the database. The default is usually 100 connections, or less if the kernel Settings do not support this (as determined when initDB is used). Superuser_reserved_connections: indicates the number of connections reserved for the superuser. The default value is 3 and the value cannot be smaller than max_connections.

Memory Settings

Max_prepared_transactions = 2000 work_mem = 8MB maintenance_work_mem = 2GB autovacuum_work_mem = 1GB dynamic_shared_memory_type = posix effective_cache_size = 32GBCopy the code

Shared_buffers: The shared memory that the database server will use. The default is usually 128 MB, or less if kernel Settings do not support this (as determined by initDB). This setting must be at least 128 kilobytes. The recommended value for shared_buffers is 25% of system memory. Since PostgreSQL also relies on the operating system’s high-speed buffers, setting shared_buffers to more than 40% of system memory can cause a higher load. Max_prepared_transactions: Sets the maximum number of transactions that can be in the “prepared” state at the same time. Setting this parameter to zero (the default) will disable the prepared transaction feature. To use prepared transactions, max_prepareD_transactions is set to be at least as large as max_connections. Work_mem: Maximum memory that can be used BY a single query operation (such as sort or hash table). The default value is 4MB. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used for hash joins, hash-based aggregation, and hash-based IN subquery processing. Maintenance_work_mem: The maximum memory used in maintenance operations such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. The default value is 64 MB. Larger Settings can improve performance for cleaning and restoring database dumps. Autovacuum_work_mem: Maximum amount of memory that can be used by each automatic vacuum_work_mem process. The default value is -1, indicating that maintenance_WORK_mem should be used instead. It is recommended to allocate resources separately because maintenance_work_mem also uses resources for indexing. Dynamic_shared_memory_type: memory management mode used by the server. Possible values are POSIX (for POSIX shared memory allocated using SHM_open), SYSV (for System V shared memory allocated through SHMGET), Windows (for Windows shared memory), and MMAP Simulate shared memory using memory-mapped files stored in the data directory. Not all values are supported on all platforms, and the default value is the first option supported on the platform. The MMAP option is not the default on any platform and is generally discouraged because the operating system repeatedly writes modified pages back to disk, increasing the I/O load on the system. However, it is useful when the pg_dynshmem directory is stored on a RAM disk or when no other shared memory capabilities are available. Effective_cache_size: The estimated size of the valid disk buffer available to a single query by the planner. A higher value makes it more likely that index scans will be used, and a lower value makes it more likely that sequential scans will be used. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it retain kernel disk buffering. It is only used for estimation purposes. The default is 4GB, and the recommended value is 50% or more of system memory.