PostgreSQL 提高OLAP性能之 黑科技 人为重组数据物理空间

PostgreSQL数据库是最好的开源数据库，这句话让很多人认为PG无所不能，这就让很多人将PG神话，其实PG这个数据库有很多地方都有自己的问题，在底层设计上的理念不同，导致PG数据库并没有自己绕开OS文件缓存的机制。

让PG和操作系统的文件缓存机制进行了强绑定，MSSQL,ORACLE,MYSQL这样的数据库产品都绕过了操作系统的缓存，将数据读取自己的缓存系统。

其实我倒是非常同意PG是一个具有设计性价比的数据库，但这样的设计也让PG在内存方面存在浪费， shared buffers + os cache的设计就让PG 浪费了内存。

上期说了一个PG存在问题导致的突发IOPS过高，导致系统性能出现问题，后面的评论区问怎么办，是指需要VACUUM 就可以解决问题吗？

我当时的回答是NO，但是我后面又说有一个黑科技，或者叫黑魔法的方案来解决这个问题，（当然是临时解决，根本解决还的依靠数据库自身重塑自己的原理设计）

其实根本问题还是MVCC在PG设计中，导致的数据页面中行存储的问题，上期已经说过了，这期就简单在重述方便后续解决问题方案的理解。

1 业务表中一些数据进入基本都是很日期有关的，一些数据在没有进行UPDATE，会基本留在原来的页面中，而一些记录在UPDATE后由于本页无法承载此行数据，数据会落在其他的数据页面中，如果在业务中有一些数据更新频繁，或是业务较为久远的数据被更新，就会导致，数据存储的太散的问题。

在OLTP中这不是问题，我们有索引来解决问题，但在OLAP或糟糕的SQL缺少有效索引的情况下.

那么下面上期我说的黑科技就要上线了。

基于PG的这个问题我们解决可以有两种方案

1 DBA 方案版本

2 融合系统方案版本

1 DBA的方案版本

对数据进行有效UPDATE

这个方案的原理就是将数据进行一个整合，通过对数据库表的分析，例如数据表中的日期字段，判断数据在进行OLAP时会被进行聚合计算，或批量结果提取。

针对这些记录进行一个update的操作，来聚合我们已经散乱的数据。

CREATE TABLE orders (
    order_id    bigint,
    user_id     bigint,
    amount      numeric,
    order_date  date,
    status      text,
    update_time timestamp
);

SELECT order_date, sum(amount)
FROM orders
where order_date > ? and  order_date < ?
GROUP BY order_date 

方案

UPDATE orders
SET update_time = update_time + interval '1 microsecond'
WHERE order data > and order_date < ?;

这样的方案仅仅适用于业务明确的情况下，比如就要计算某个部分半年的数据，或者一年的数据，可以分步骤的对这些要进行聚合的数据，或报表的数据，顺序提取的数据进行这样的UPDATE的操作，提高系统的性能，同时这样操作也能提高索引的性能（原理就不讲了），同时如果想要效果比较明显，可以在安全的情况下，先把这个表的autovacuum = false,在这个操作完毕后，在打开autovacuum，并在打开前对这个表进行一个vacuum analyze的操作。

这是DBA针对某些应用的方案

那么如果是配合应用来做的话，可以在应用系统设计中，这里我们可以针对表中添加一个字段，叫amount， 这个字段可以设置为int,只要对这行数据库进行UPDATE 就让应用在操作的时候，对这个列的值加一。

以此类推，那么在进行有规律的OLAP的操作前可以通过对比日期和amount的数字来进行粗略的预估数据有多散，有的放矢的进行这些数据的UPDATE，比如如果大量的数据amount的值都为0，少部分为递加的数字，则我们可以不进行这个工作，如果我们发现大量要进行OLAP的数据已经都有较高的AMOUNT值，那么我们就直接，对少量的没有AMOUNT的行进行有效的UPDATE将需要进行聚合和OLAP的数据进行物理的归拢。

其实内行已经看明白我的这套逻辑，这就是在继续温和的cluster表，进行认为的“半” vacuum full的工作，不过这个逻辑是建立在业务本身，而不是数据库原理上的。这样的操作比重建表的成本要低得多，且可控。

但效果在整体上一定是不如VACUUM FULL ，不过如果你的表有几百G大小，相信你也不敢vacuum full 对吧！！！