Postgresql相关的内存设置参数

lenf

　　关于PostgreSQL有六个主要的参数设置。
第一：share_buffers
第二：temp_buffers
第三：max_prepared_transactions
第四：work_mem
第五：maintenance_work_mem
第六：max_stack_depth

share_buffers
Sets the number of shared memory buffers used by the database server.
该参数主要是跟操作系统的共享内存有关，简单的说，就是不要超过物理内存的总量。
相关操作系统的(linux)下，使用 cat /etc/sysctl.conf 命令后，查看两个属性kernel.shmall和kernel.shmmax

temp_buffers
Sets the maximum number of temporary buffers used by each database session.
这个参数很简单，就是设置连接数据库的session大小。

max_prepared_transactions
Sets the maximum number of transactions that can be in the "prepared" state simultaneously.
这个参数与设置的事务提交有关，有很高的可靠性。

work_mem
Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files.Sort operations are used for ORDER BY、distinct、and merge joins、Hash tables are used in hash joins,hash-bse aggregation,and hash-based processing of IN subqueries.
Also, serveral running sessions could be doing such operations concurrently.So the total memory used could be many times the value of work_mem.
这个参数主要用于查询条件带排序的，或者是hash表等相关的操作。work_mem是一个连接session独享的，不是多个连接session共享的。

maintenance_work_mem
Specifies the maximum amount of memory to be used in maintenance operations,such as VACUUM,CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.
该参数主要是一个database session在执行相关操作时所需要的空间容量。

max_stack_depth
Specifies the maximum safe depth of the server's execution stack.