pg参数优化

连接设置

参数名	作用	使用
listen_address	指定服务器在哪些 TCP/IP 地址上监听客户端连接，默认值是localhost，只允许本地连接。	*(所有都可以连接)
port		5432
max_connections	决定数据库的最大并发连接数，默认值通常是 100 个连接，如果内核设置不支持（initdb时决定），可能会比这个数少。	500
superuser_reserved_connections	为超级用户保留的连接数，默认是 3，不能小于 max_connections

内存设置

参数名	作用	详解
shared_buffers	数据库服务器将使用的共享内存，默认通常是 128M，如果内核设置不支持（initdb时决定），那么可以会更少。 这个设置必须至少为 128 千字节。shared_buffers推荐值是系统内存的 25%。因为PostgreSQL同样依赖操作系统的高速缓冲区，将shared_buffers设置为超过系统内存40%，可能会造成更高的负载。	大多数oltp工作都是随机io，从磁盘获取非常慢，postgres将数据缓存到内存中提高性能；shared_buffers存储表数据，索引以及执行计划
temp_buffers	为每个数据库会话设置用于临时缓冲区的最大内存，仅用于临时表的会话本地缓存。	32Mb
max_prepared_transactions	设置可以同时处于“prepared”状态的事务的最大数目把这个参数设置 为零（这是默认设置）将禁用预备事务特性。若要使用预备事务，max_prepared_transactions 至少设置为同 max_connections 一样大。
work_mem	单个查询操作(例如排序或哈希表)可使用的最大内存，默认值是 4MB，ORDER BY、DISTINCT和归并连接都要用到排序操作，哈希连接、基于哈希的聚集以及基于哈希的IN子查询处理中都要用到哈希表	32MB
maintenance_work_mem	维护性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）中使用的最大的内存，其默认值是64M， 更大的设置可以改进清理和恢复数据库转储的性能。	2GB
autovacuum_work_mem	每个自动清理工作者进程能使用的最大内存量，其默认值为 -1，表示转而使用 maintenance_work_mem 的值。建议单独分配，因为 maintenance_work_mem 分配的资源建索引等操作也会使用。
dynamic_shared_memory_type	服务器使用的内存管理方式。可能的值是posix（用于使用 shm_open分配的 POSIX 共享内存）、sysv （用于通过shmget分配的 System V 共享内存）、 windows（用于 Windows 共享内存）、和mmap （使用存储在数据目录中的内存映射文件模拟共享内存）。并非所有平台上都支持所有值，平台上第一个支持的选项就是其默认值。 在任何平台上mmap选项都不是默认值，通常不鼓励使用它，因为操作系统会 反复地把修改过的页面写回到磁盘上，从而增加了系统的I/O负载。不过当 pg_dynshmem目录被存储在一个 RAM 盘时或者没有其他共享内存功能可用时， 它还是有用的	posix
max_files_per_process	设置每个服务器子进程允许同时打开的最大文件数目，默认为1000.如果发现‘too many open files’ 可以尝试减小这个值
effective_cache_size	规划器对一个单一查询可用的有效磁盘缓冲区的预估大小，更高的数值会使得索引扫描更可能被使用，更低的数值会使得顺序扫描更可能被使用。这个参数对PostgreSQL分配的共享内存尺寸没有影响，它也不会保留内核磁盘缓冲，它只用于估计的目的，默认值是 4GB，推荐值是系统内存的 50%或更大。

io设置

参数名	作用	使用
bgwriter_delay	(bg_writer 定期执行缓存刷新，写入)后台写入器活动轮次之间的延迟。在每个轮次中，写入器都会为一定数量的脏缓冲区发出写操作，之后它就休眠 bgwriter_delay 的时长， 然后重复动作。当缓冲池中没有脏缓冲区时，不管 bgwriter_delay，它都会进入更长的休眠（50*bgwriter_delay），默认值是 200ms。
bgwriter_lru_maxpages	在每个轮次中，不超过这么多个缓冲区将被后台写入器写出，把这个参数设置为零可禁用后台写出，默认值是 100 个缓冲区。
bgwriter_lru_multiplier	最近所需缓冲区的平均值乘以 bgwriter_lru_multiplier可以估算下一轮次中将会需要的缓冲区数目。脏缓冲区将被写出直到有很多干净可重用的缓冲区（然而，每一轮次中写出的缓冲区数不超过 bgwriter_lru_maxpages）。 因此，设置为 1.0 表示一种“刚刚好的”策略，这种策略会写出正好符合预测值的数目的缓冲区，更大大的值可以为需求高峰提供某种缓冲，默认值是 2.0。
effective_io_concurrency	设置了该值，磁盘的预读会失效，顺序扫描和索引扫描都可以利用磁盘预读，建议关闭，默认值为 1。	2
max_worker_processes	系统能够支持的后台进程的最大数量，默认值为 8。在更改这个值时，考虑也对 max_parallel_workers、max_parallel_maintenance_workers、max_parallel_workers_per_gather 进行调整。	32
max_parallel_workers	设置系统支持的最大并行数量，默认值为8，要注意将这个值设置得大于 max_worker_processes 将不会产生效果，因为并行工作者进程都是从 max_worker_processes 所建立的工作者进程池中取出来的。	32
max_parallel_maintenance_workers	单一工具性命令能够启动的最大并行数。当前支持使用并行工作者的工具性命令是 CREATE INDEX，并且只有在构建B-树索引时才能并行，并且 VACUUM 没有 FULL选项。并行工作者从由 max_worker_processes 创建的进程池中取出，数量由max_parallel_workers控制，默认值为2。	4
max_parallel_workers_per_gather	设置允许的最大并行查询数，并行查询可能消耗比非并行查询更多的资源，把这个值设置为0将会禁用并行查询执行，默认值是2。	16
wal_level	wal_level决定多少信息写入到 WAL 中。默认值是replica，它会写入足够的数据以支持WAL归档和复制，包括在后备服务器上运行只读查询。minimal会去掉除从崩溃或者立即关机中进行恢复所需的信息之外的所有记录。最后，logical会增加支持逻辑解码所需的信息。每个层次包括所有更低层次记录的信息。这个参数只能在服务器启动时设置	logical
wal_buffers	缓冲区的默认大小由wal_buffers设置设置—最初为16MB。如果要调优的系统有大量并发连接，那么wal_buffers的值越高，性能越好。	16MB
wal_compression	（WAL(Write Ahead Log)预写日志，是数据库系统中常见的一种手段，用于保证数据操作的原子性和持久性。）当这个参数为on时，如果 full_page_writes 为打开（默认值打开）或者处于基础备份期间，PostgreSQL服务器 会压缩写入到 WAL 中的完整页面镜像。压缩页面镜像将在 WAL 重放时 被解压。默认值为off。只有超级用户可以更改这个设置，打开这个参数可以减小 WAL 所占的空间且无需承受不可恢复的数据损坏风险， 但是代价是需要额外的 CPU 开销以便在 WAL 记录期间进行压缩以及在 WAL 重放时解压。
wal_writer_delay	指定 WAL 写入器刷写 WAL 的频繁程度，以时间为单位。 在刷写WAL之后，写入器将根据 wal_writer_delay所给出的时间长度进行睡眠，除非被一个异步提交的事务提前唤醒。 如果最近的刷写发生在 wal_writer_delay 之前，并且小于 wal_writer_flush_after WAL的值产生之后，那么WAL只会被写入操作系统，而不会被刷写到磁盘，默认值是 200ms。
commit_delay	在一次 WAL 刷写被发起之前，commit_delay 增加一个时间延迟。 如果系统负载足够高，使得在一个给定间隔内有额外的事务准备好提交，那么通过允许更多事务通过一个单次 WAL 刷写来提交能够提高组提交的吞吐量。 但是，它也把每次 WAL 刷写的潜伏期增加到了最多 commit_delay。 因为如果没有其他事务准备好提交，就会浪费一次延迟，只有在当一次刷写将要被发起时有至少 commit_siblings(默认是5个)个其他活动事务时，才会执行一次延迟。 另外，如果fsync被禁用，则将不会执行任何延迟。
checkpoint_timeout	自动 WAL 检查点之间的最长时间，合理的范围在 30 秒到 1 天之间，默认是 5min。增加这个参数的值会增加崩溃恢复所需的时间。	2min
checkpoint_completion_target	为了避免大量的页面写入对I/O造成冲击，在检查点期间写入脏缓冲区的过程会分散一段时间。该周期由checkpoint_completion_target控制，它是检查点间隔的一部分，默认为0.5，也就是说每个checkpoint需要在checkpoints间隔时间的50%内完成。	0.9
max_wal_size	在自动 WAL 检查点之间允许 WAL 增长到的最大尺寸。这是一个软限制，在特殊的情况下 WAL 尺寸可能会超过max_wal_size， 例如在重度负荷下、archive_command失败或者高的 wal_keep_size设置，默认为 1 GB。增加这个参数可能导致崩溃恢复所需的时间。	16GB
min_wal_size	只要 WAL 磁盘用量保持在这个设置之下，在检查点时旧的 WAL 文件总是 被回收以便未来使用，而不是直接被删除。这可以被用来确保有足够的 WAL 空间被保留来应付 WAL 使用的高峰，例如运行大型的批处理任务。 如果指定值时没有单位，则以兆字节为单位，默认是 80 MB。	4GB
max_wal_senders		32
wal_keep_segments	用于指定pg_wal目录中保存的过去的wal文件（wal 段）的最小数量，以防备用服务器在进行流复制时需要。	1000
wal_sender_timeout		60s
effective_cache_size	effective_cache_size参数是用来估计可以做磁盘cache的内存的大小。它只是一个指导方针，而不是分配的内存大小，也不是cache的大小。这个参数并不会实际分配内存，而是告诉优化器内核中可用的cache的大小。如果这个值设置得太低，那么查询优化器会决定放弃使用索引，尽管这些索引会非常有用。因此，通常将这个参数调大，是有好处的。	16GB
default_statistics_target	PostgreSQL进行analyze的时候，参考的生成的列的柱状图的大小，可以理解为采样颗粒度。就是说列没有通过ALTER TABLE SET STATISTICS语句进行自定义statistics target值，那么在analyze该列的时候，PostgreSQL会取default_statistics_target的值来替代。当default_statistics_target的值越高，那么PostgreSQL进行统计的约精确，当然，花费的时间也就越长了。	500
archive_mode	当启用archive_mode时，可以通过设置 archive_command命令将完成的 WAL 段发送到归档存储。除用于禁用的off之外，还有两种模式：on 和 always。在普通操作期间，这两种模式之间没有区别，但是当设置为 always 时，WAL 归档器在归档恢复或者后备模式下也会被启用。在 always 模式下，所有从归档恢复 的或者用流复制传来的文件将被（再次）归档。当wal_level被设置为minimal时，archive_mode不能被启用
max_replication_slots	服务器可以支持的复制槽最大数量，默认值为10。将它设置为一个比当前已有复制槽要少的值会阻碍服务器启动。此外，要允许使用复制槽，wal_level必须被设置为 replica或更高。	32
random_page_cost	设置规划器对一次非顺序获取磁盘页面的代价估计,默认值是 4.0,减少这个值（相对于seq_page_cost）将导致系统更倾向于索引扫描；提高它将让索引扫描看起来相对更昂贵。

synchronous_commit 参数说明
单实例环境
on：当数据库提交事务时，wal先写入 wal buffer 再写入 wal 日志文件，设置成on表示提交事务时需等待本地wal写入wal日志后才向客户端返回成功。on 为默认设置，数据库非常安全，但性能有所损耗。
local：local含义和on类似，表示提交事务时需要等待本地wal写入后才向客户端返回成功。
off：当数据库提交事务时不需要等待本地 wal buffer 写入 wal 日志，随即向客户端返回成功，设置成 off会给数据库带来一点风险：数据库宕机时最新提交的少量事务可能丢失，数据库重启后会认为这些事务异常终止，会rollback。适用对数据库准确性要求不高同时追求数据库性能的的场景。
流复制环境
on：表示流复制主库提交事务时，需等待备库接收主库发送的wal日志流并写入wal文件，之后才向客户端返回成功，简单的说on表示本地wal已落盘，备库的wal也已落盘，有两份持久化的wal，但备库此时还没有完成重做。这个选项带来的事务响应时间较高。
remote_write：表示流复制主库提交事务时，需等待备库接收主库发送的wal日志流并写入备节点操作系统缓存中，之后向客户端返回成功，这种情况下备库出现异常关闭时不会有已传送的wal日志丢失风险，但备库操作系统异常宕机就有已传送的wal丢失风险了，此时wal可能还没有完全写入备节点wal文件中，简单的说 remote_write 表示本地wal已落盘，备库的wal还在备库操作系统缓存中，也就是说只有一份持久化的wal。这个选项带来的事务响应时间较低。
remote_apply：表示流复制主库提交事务时，需等待备库接收主库发送的wal流并写入wal文件，同时备库已经完成重做，之后才向客户端返回成功，简单的说remote_apply 表示本地wal已落盘，备库wal已落盘并且已经完成重做，这个设置保证了拥有两份持久化的wal，同时备库也已经完成了重做。这个选项带来的事务响应时间最高。

日志设置

参数名	作用	使用
log_destination	有三种输出方法，stderr,csvlog,syslog；在windows上还支持eventlog。默认是stderr，如果使用csvlog的话，logging_collector必须开启。也可以同时使用csvlog和stderr，会记录两种格式的日志。	'stderr'
logging_collector	日志收集器，它是一个捕捉被发送到stderr的日志消息的后台进程，并且它会将这些消息重定向到日志文件中，默认是OFF。	on
log_directory	日志保存路径，当logging_collector被启用时，这个参数决定日志文件将被在哪个目录下创建。	'pg_log'
log_filename	日志名格式，默认是postgresql-%Y-%m-%d_%H%M%S.log	'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode	默认的权限是0600，表示只有服务器拥有者才能读取或写入日志文件。其他常用的设置是0640，它允许拥有者的组成员读取文件。不过要注意你需要修改log_directory为将文件存储在集簇数据目录之外的某个位置，才能利用这个设置。在任何情况下，让日志文件变成任何人都可读是不明智的，因为日志文件中可能包含敏感数据
log_truncate_on_rotation	当logging_collector被启用时，这个参数将导致 PostgreSQL截断（覆盖而不是追加）任何已有的同名日志文件。不过，截断只在一个新文件由于基于时间的轮转被打开时发生，在基于尺寸的轮转时不会发生。如果被关闭，在所有情况下以前存在的文件将被追加。	off
log_rotation_age	当logging_collector被启用时，这个参数决定使用一个单个日志文件的最大时间量，之后将创立一个新的日志文件，默认为24小时。 将这个参数设置为零将禁用基于时间的新日志文件创建。
log_rotation_size	当logging_collector被启用时，这个参数决定一个个体日志文件的最大尺寸。当这些数据量被发送到一个日志文件后，将创建一个新的日志文件，默认值是10M。设置为零时将禁用基于大小创建新的日志文件。
log_min_duration_statement	记录慢 sql 的时间阀值，超过这个时间的 sql 将会被记录到日志中，默认值为 -1，不记录慢sql。
log_rotation_age	单个日志文件的生存期，默认1天，在日志文件大小没有达到log_rotation_size时，一天只生成一个日志文件	7d
log_rotation_size	单个日志文件的大小，如果时间没有超过log_rotation_age，一个日志文件最大只能到10M，否则将新生成一个日志文件。	1024MB
log_min_messages	指定群集必须插入到日志中的内容，而不考虑传入用户连接及其设置。	warning
client_min_messages	决定客户端在连接期间必须向用户报告哪些日志事件。这两个设置都可以采用上述阈值列表中的值。	warning
log_min_error_statement	控制哪些导致一个错误情况的 SQL 语句被记录在服务器日志中。任何指定严重级别 或更高级别的消息的当前 SQL 语句将被包括在日志项中。有效值是DEBUG5、 DEBUG4、DEBUG3、 DEBUG2、DEBUG1、 INFO、NOTICE、 WARNING、ERROR、LOG、 FATAL和PANIC。默认值是ERROR，它表示导致错误、日志消息、致命错误或恐慌错误的语句将被记录在日志中。要有效地关闭记录失败语句，将这个参数设置为PANIC。只有超级用户可以改变这个设置	error
log_min_duration_statement	如果语句运行至少指定的毫秒数，将导致记录每一个这种完成的语句的持续时间。将这个参数设置为零将打印所有语句的执行时间。设置为 -1 （默认值）将停止记录语句持续时间。例如，如果你设置它为250ms，那么所有运行 250ms 或更久的SQL 语句将被记录。启用这个参数可以有助于追踪应用中未优化的查询。只有超级用户可以改变这个设置。	120000
log_checkpoints	控制检查点和重启点是否被记录在服务器日志中，一些统计信息也被包括在日志消息中，包括写入缓冲区的数据和写它们所花的时间，默认值是关闭。	on
log_connections	控制连接信息是否被记录，尝试对服务器的连接被记录，客户端认证的成功完成也会被记录，在会话中它不能被更改，默认为off。	on
log_disconnections	控制会话终止是否被记录。日志输出提供的信息类似于 log_connections，不过还外加会话的持续时间，在会话中它不能被更改，默认为off	on
log_line_prefix	设置日志输出格式	'%m [%p]: %u,%d,%r '
log_lock_waits		on
log_error_verbosity	有效值是TERSE、DEFAULT和VERBOSE。TERSE排除记录DETAIL、HINT、QUERY和CONTEXT错误信息。VERBOSE输出包括SQLSTATE错误码以及产生错误的源代码文件名、函数名和行号。 默认是 DEFAULT。
log_line_prefix	设置日志中记录哪些内容，默认值是’%m [%p] '，记录时间戳和进程ID。
log_statement	控制哪些 SQL 语句被记录。有效值是 none (off)、ddl、mod和 all（所有语句），mod记录所有ddl语句，外加数据修改语句例如INSERT, UPDATE、DELETE、TRUNCATE, 和COPY FROM。 如果PREPARE、EXECUTE和 EXPLAIN ANALYZE包含合适类型的命令，它们也会被记录。默认none。	mod
log_timezone	设置在服务器日志中写入的时间戳的时区，默认值是GMT。	'Asia/Shanghai'

log_filename 格式符说明
%a：星期的英文单词的缩写：如星期一， 则返回 Mon
%A：星期的英文单词的全拼：如星期一，返回 Monday
%b：月份的英文单词的缩写：如一月， 则返回 Jan
%B：月份的引文单词的缩写：如一月， 则返回 January
%c：返回datetime的字符串表示，如03/08/15 23:01:26
%d：返回的是当前时间是当前月的第几天
%f：微秒的表示： 范围: [0,999999]
%H：以24小时制表示当前小时
%I：以12小时制表示当前小时
%j：返回 当天是当年的第几天 范围[001,366]
%m：返回月份 范围[0,12]
%M：返回分钟数 范围 [0,59]
%P：返回是上午还是下午–AM or PM
%S：返回秒数 范围 [0,61]。。。手册说明的
%U：返回当周是当年的第几周 以周日为第一天
%W：返回当周是当年的第几周 以周一为第一天
%w：当天在当周的天数，范围为[0, 6]，6表示星期天
%x：日期的字符串表示 ：03/08/15
%X：时间的字符串表示 ：23:22:08
%y：两个数字表示的年份 15
%Y：四个数字表示的年份 2015
%z：与utc时间的间隔 （如果是本地时间，返回空字符串）
%Z：时区名称（如果是本地时间，返回空字符串）

log_line_prefix 格式符说明
%a：应用名
%u：用户名
%d：数据库名
%r：远程主机名或 IP 地址，以及远程端口
%h：远程主机名或 IP 地址
%b：后端类型
%p：进程 ID
%t：无毫秒的时间戳
%m：带毫秒的时间戳
%n：带毫秒的时间戳（作为 Unix 时代）
%i：命令标签：会话当前命令的类型
%e：SQLSTATE 错误代码
%c：会话 ID：见下文
%l：对每个会话或进程的日志行号，从 1 开始
%s：进程开始的时间戳
%v：虚拟事务 ID (backendID/localXID)
%x：事务 ID （如果未分配则为 0）
%q：不产生输出，但是告诉非会话进程在字符串的这一点停止；会话进程忽略
%%：纯文字%

autovacuum设置

参数名	作用	使用
autovacuum	控制服务器是否运行自动清理启动器后台进程。默认为开启，不过要自动清理正常工作还需要启用 track_counts（默认启用）。 该参数只能在postgresql.conf文件或服务器命令行中设置，通过更改表存储参数可以为表禁用自动清理。 注意即使该参数被禁用，系统也会在需要防止事务ID回卷时发起清理进程。
old_snapshot_threshold	设置可以使用查询快照的最小时间，以规避使用快照时出现“snapshot too old” 错误的风险，超过此阈值时间的数据将可以被清除，这可以有助于阻止长时间使用的快照造成的快照膨胀，默认值为 -1（禁用此功能）,实际上将快照的时限设置为无穷大。
log_autovacuum_min_duration	超过这个时间阀值的自动清理动作都会被日志记录，将该参数设置为0会记录所有的自动清理动作，默认值为 -1 （禁用对自动清理动作的记录）。 此外，当该参数被设置为除-1外的任何值时， 如果一个自动清理动作由于一个锁冲突或者被并发删除的关系而被跳过，将会为此记录一个消息。 开启这个参数对于追踪自动清理活动非常有用，但是可以通过更改表的存储 参数为个别表覆盖这个设置。
autovacuum_max_workers	设置能同时运行的自动清理进程（除了自动清理启动器之外）的最大数量，默认值为3。
autovacuum_vacuum_scale_factor	触发 vacuum 自动清理操作的 dml 比例，默认值 0.2，当表上的 dml 操作占据表数据量的 20% 时触发 vacuum 自动清理操作，为防止数据量较小的表被频繁清理，与 autovacuum_vacuum_threshold（改参数默认值为 50，表中至少有 50 条数据发成 dml 操作时，才会触发 vacuum 自动清理） 参数共同作用。
autovacuum_analyze_scale_factor	触发 vacuum 自动 analyze 操作的 dml 比例，默认值 0.1，当表上的 dml 操作占据表数据量的 10% 时触发 vacuum 自动 analyze 操作，为防止数据量较小的表被频繁 analyze，与 autovacuum_analyze_threshold（改参数默认值为 50，表中至少有 50 条数据发成 dml 操作时，才会触发 vacuum 自动 analyze） 参数共同作用。
autovacuum_freeze_max_age	某表的pg_class.relfrozenxid的最大值，如果超出此值则重置xid，默认值为2亿，注意即便自动清理被禁用，系统也将发起自动清理进程来阻止回卷。
autovacuum_multixact_freeze_max_age	某表的pg_class.relminmxid最大值，如果超出此值则重置xid，默认值为4亿，注意即便自动清理被禁用，系统也将发起自动清理进程来阻止回卷。
autovacuum_vacuum_cost_delay	指定用于自动 VACUUM 操作中的代价延迟值，如果指定-1（默认值），则使用 vacuum_cost_delay 值（默认值 2ms）。

补充说明
由于生产环境中每张业务表作用、使用频繁程度、“死元组”的增长速度等都不同，建议结合业务情况，对重要的生产业务表单独进行设置参数值。

dml 操作特别频繁的表，做类似如下设置：
ALTER TABLE mytable SET (autovacuum_vacuum_scale_factor = 0.01);
索引字段，dml 操作特别频繁的表，做类似如下设置：
ALTER TABLE mytable SET (fillfactor=80);
仅插入数据库表，做类似如下设置：
ALTER TABLE mytable SET (autovacuum_freeze_max_age = 10000000);

系统环境设置

参数名	作用	使用
datestyle		'iso, mdy'
timezone		'Asia/Shanghai'
lc_messages		'en_US.UTF-8'
lc_monetary		'en_US.UTF-8'
lc_numeric		'en_US.UTF-8'
lc_time		'en_US.UTF-8'
shared_preload_libraries		'timescaledb,pg_cron,decoderbufs,wal2json'
default_text_search_config		'pg_catalog.english'
cron.database_name		'datalake-laser'
deadlock_timeout		3s