POSTGRESQL 提高POSTGRESQL性能的一些习惯 （1）

PostgreSQL 是一个很有意思的数据库，在使用中有一些习惯可以在同等的硬件下，更加有效的使用硬件提供的资源，让管理和使用POSTGRESQL 获得更多的性能。下面就说说一些使用POSTGRESQL 的习惯。

1 是否需要降低文件的数量

POSTGRESQL 的文件很多，这里指的文件的数量，主要指两方面的的文件，数据文件与日志文件，降低文件的数量有两个方式

1 降低产生数据的量

2 提高单体文件的数据承受数据的量

基于第一个问题在数据库上并不可控，所以我们要讨论的是第二个问题，如何提高单个文件承载数据的量。刚才我们提高这里有两个地方 1 数据 2 日志

基于数据的文件默认POSTGRESQL 是以1G作为一个分界点，一个表如果大小超过1G 的情况下将分割为多个文件，而日志文件默认是16MB一个，超过大小后，也会将其分开。

那么我们是否应该降低这两个文件的数量，尤其在一些大型的系统中，这就需要讨论必要性了。基于POSTGRESQL 对于数据的行数并没有明确的限制，同时POSTGRESQL 在一些系统中存在这单张表可能就有 40- 50G 甚至100G 的大表的存在的情况。官方网站上对这样的情况说明是，这不会引起性能方面的风险。但并未说明这样的使用方式是对的， 首先POSTGRESQL 打开一个文件是需要句柄的，显然如果一个表很大，并且多个进程都要打开他，那么他产生句柄就会很大，而HOLD住句柄的信息是需要占用内存的。

所以针对一些较大的系统的POSTGRESQL 的数据库时，可以调整单个数据文件的大小，来降低这些大型系统中，一个表产生的数据文件的数量。调整数据文件大小限制需要在数据库编译的过程中进行限制。通过--with-segments来控制。如下图，这个系统中的单个数据文件的大小不是1G 而是 4G。

2 WAL 数据的大小

众所周知，POSTGRESQL 的WAL 带有的信息不少尤其，在有full_page的情况下，所以默认16MB一个的日志文件本身在大量进行DML的操作系统并不是一个好的设置，在数据库初始化的时候，我们可以将这个值进行变化. 我们可以通过下面的语句来查看我们当前的pg_wal 的数据目录的文件总体有多大。

select count(*) * pg_size_bytes(current_setting('wal_segment_size')) as total_size from pg_ls_dir('pg_wal') as t(fname) where fname <> 'archive_status';

那么为什么我们要调整wal 文件的大小，这个问题与我们的产生WAL文件的速度有关，尤其一些频繁OLTP的系统，产生WAL的数据量很大，导致经常要产生WAL 文件，产生新的文件是需要时间的，所以 wal 单个文件的大小在超级热的POSTGRESQL 是一个需要注意的问题。

2 是否需要INDEX 和 数据文件进行分割

这个问题是一个好问题INDEX 和 数据到底是否需要分离，从一般的情况上看不同的数据库有不同的使用方式，如果是MYSQL的情况下，一般一个表自己成为一个表空间，一个文件，PG这里与MYSQL 类似，但不类似的是表文件超过一定的尺寸会在继续分割，MYSQL不会，而其他的类型的数据库如SQL SERVER 是多个表在一个数据文件中，那么PG 的表数据已经是一个表多个数据文件了，到底有没有必要进行和索引分割的必要。

答案是建议有，原因在于两点

1 PG中对于数据是需要进行VACUUM 的，而VACUUM对于数据文件扫描是从一而终的（排除手动停止 autovacuum）,越大的文件vacuum的时间会越长。

2 性能问题，如数据是需要在内存中处理的，如果查找的数据有索引的情况下，索引是需要先load 到内存中，并且在命中数据后，在通过相关的指针指到对应的数据页面的，而数据页面如果都是数据 和 数据页面中包含索引和数据一个页面中，这对数据查找的消耗是不一样。

3 导出数据的问题，如果数据在一个表空间，而索引在另一个表空间，在使用pg_dump的情况下，应该是不需要加载索引页面到内存中。

4 如果有更快速的磁盘系统，首先将索引的表空间建立到这样的磁盘系统中，提高查询性能。

5 可以针对INDEX 自行设定与表不一致的 fillfactor 填充因子。

所以建议将数据和索引分割，甚至对于一些大表的索引本身也根据你创建的索引类型进行分割，如brin ，btree-gin ，btree , 本身不同的索引类型也建立不同的表空间，分开这些数据存储在不同的表空间中。

下面就举例

create index id_history_tid_bid_aid on pgbench_history (tid,bid,aid) with (fillfactor = 80) tablespace index;

下图可以证明的确索引和数据已经分成两个文件了

不同的文件号，已经说明了问题。

——————————————————————————

未完结