PostgreSQL10.0preview功能增強-OLAP增強向量聚集索引(列儲存擴充套件)

標籤

PostgreSQL , 10.0 , Vertical Clustered Index (columnar store extension) , 列儲存 , 向量聚集索引

---

背景

未來資料庫OLTP+OLAP逐漸模糊化，需求逐漸融合是一個大的趨勢，如果你的資料庫只支援OLTP的場景，未來可能會成為業務的絆腳石。

在這方面PostgreSQL每年釋出的新版本，都給使用者很大的驚喜，OLTP已經具備非常強大的競爭力（效能、功能、穩定性、成熟度、案例、跨行業應用等），而OLAP方面，新增的feature也是層出不窮。

《PostgreSQL 10.0 preview 效能增強 – OLAP提速框架, Faster Expression Evaluation Framework(含JIT)》

《分析加速引擎黑科技 – LLVM、列存、多核並行、運算元複用 大聯姻 – 一起來開啟PostgreSQL的百寶箱》

《PostgreSQL 向量化執行外掛(瓦片式實現) 10x提速OLAP》

PostgreSQL 10.0將要整合的一個功能：

Vertical Clustered Index (columnar store extension) , 列儲存 , 向量聚集索引。

這個模組是Fujitsu實驗室提供的，一種新增的VCI索引訪問介面，這麼做可以最小化資料庫的改動。

使用者僅需要在原來的堆表上建立VCI即可（向量聚集索引），索引將以向量聚集形式組織，提升查詢效能。

VCI有兩方面的優化，索引資料分為兩個部分：

1. 寫優化部分(WOS)

行格式儲存（類似堆表），同時攜帶xmin/xmax標記（事務號），所以如果更新WOS中的資料，和更新PostgreSQL原有的堆表一樣效率很高。

PostgreSQL backend process或者autovacuum會持續自動的將WOS中已經frozen的記錄（即對所有事務可見的記錄），轉移到ROS（讀優化部分）儲存。

ROS儲存中，沒有版本資訊（XMIN/XMAX），有tuple id，可以通過tuple id訪問ROS中的記錄。（沒有版本資訊，如何判斷可見性呢？後面講）

2. 讀優化部分(ROS)

ROS為列儲存，每列一個或一批檔案，在ROS中，記錄是以extent來組織的，每個extent儲存262,144行記錄，可以方便的建立堆表TID to ROS CRID的對映關係。

插入vci記錄，與插入索引一樣。（插入WOS，後臺自動將frozen記錄合併到ROS）

刪除vci記錄，如果資料只在WOS中，刪除和刪堆表記錄一樣，做標記，如果資料已經從WOS合併到ROS，那麼需要維護一個向量，這個向量中包含被刪除的記錄在ROS中的tuple id, 以及刪除該記錄的事務的xact id等。讀取ROS時，根據這個向量，過濾ros中對應的tuple id.

更新vci記錄，與刪除類似。

目前提供的效能測試資料

討論

Hi All,  

Fujitsu was interested in developing a columnar storage extension with  
minimal  
changes the server backend.  

The columnar store is implemented as an extension using index access  
methods.  
This can be easily enhanced with pluggable storage methods once they are  
available.  

A new index method (VCI) is added to create columnar index on the table.  

The following is the basic design idea of the columnar extension,  

This has the on-disk columnar representation. So, even after crash,  
the columnar format is recovered to the state when it was crashed.  

To provide performance benefit for both read and write operations,  
the data is stored in two formats  

1) write optimized storage (WOS)  
2) read optimized storage (ROS).  

This is useful for the users where there is a great chance of data  
modification  
that is newly added instead of the old data.  

WOS  
====  

write optimized storage is the data of all columns that are part of VCI are  
stored in a row wise format. All the newly added data is stored in WOS  
relation with xmin/xmax information also. If user wants to update/delete the  
newly added data, it doesn`t affect the performance much compared to  
deleting the data from columnar storage.  

The tuples which don`t have multiple copies or frozen data will be moved  
from WOS to ROS periodically by the background worker process or autovauum  
process. Every column data is stored separately in it`s relation file. There  
is no transaction information is present in ROS. The data in ROS can be  
referred with tuple ID.  

In this approach, the column data is present in both heap and columnar  
storage.  

ROS  
====  

This is the place, where all the column data is stored in columnar format.  
The data from WOS to ROS is converted by background workers continously  
based  
on the tuple visibility check. Whenever the tuple is frozen and it gets  
moved  
from WOS to ROS.  

The Data in ROS is stored in extents. One extent contains of 262,144 rows.  
Because  
of fixed number of records in an extent it is easy to map the heap record  
to the columnar  
record with TID to CRID map.  

Insert  
=====  

The insert operation is just like inserting a data into an index.  

Select  
=====  

Because of two storage formats, during the select operation, the data in WOS  
is converted into Local ROS for the statement to be executed. The conversion  
cost depends upon the number of tuples present in the WOS file. This  
may add some performance overhead for select statements. The life of the  
Local  
ROS is till the end of query context.  

Delete  
=====  

During the delete operation, whenever the data is deleted in heap at the  
same  
time the data in WOS file is marked as deleted similar like heap. But in  
case  
if the data is already migrated from WOS to ROS, then we will maintain some  
delete vector to store the details of tuple id, transaction information and  
etc.  
During the data read from ROS file, it is verified against delete vector  
and  
confirms whether the record is visible or not? All the delete vectors  
data is applied to ROS periodically.  

More details of internal relations and their usage is available in the  
README.  
Still it needs more updates to explain full details of the columnar index  
design.  

The concept of Vertical clustered index columnar extension is from Fujitsu  
Labs, Japan.  

Following is the brief schedule of patches that are required  
for a better performing columnar store.  

1. Minimal server changes (new relkind "CSTORE" option)  
2. Base storage patch  
3. Support for moving data from WOS to ROS  
4. Local ROS support  
5. Custom scan support to read the data from ROS and Local ROS  
6. Background worker support for data movement  
7. Expression state support in VCI  
8. Aggregation support in VCI  
9. Pg_dump support for the new type of relations  
10. psql d command support for CSTORE relations  
11. Parallelism support  
12. Compression support  
13. In-memory support with dynamic shared memory  

Currently I attached only patches for 1 and 2. These will provide the  
basic changes that are required in PostgreSQL core to the extension  
to work.  

I have to rebase/rewrite the rest of the patches to the latest master,  
and share them with community.  

Any Comments on the approach?  

Regards,  
Hari Babu  
Fujitsu Australia  

這個patch的討論，詳見郵件組，本文末尾URL。

PostgreSQL社群的作風非常嚴謹，一個patch可能在郵件組中討論幾個月甚至幾年，根據大家的意見反覆的修正，patch合併到master已經非常成熟，所以PostgreSQL的穩定性也是遠近聞名的。

參考

https://commitfest.postgresql.org/13/945/

https://www.postgresql.org/message-id/flat/CAJrrPGfaC7WC9NK6PTTy6YN-NN+hCy8xOLAh2doYhVg5d6HsAA@mail.gmail.com#CAJrrPGfaC7WC9NK6PTTy6YN-NN+hCy8xOLAh2doYhVg5d6HsAA@mail.gmail.com

《分析加速引擎黑科技 – LLVM、列存、多核並行、運算元複用 大聯姻 – 一起來開啟PostgreSQL的百寶箱》

《PostgreSQL 向量化執行外掛(瓦片式實現) 10x提速OLAP》

《PostgreSQL 10.0 preview 效能增強 – OLAP提速框架, Faster Expression Evaluation Framework(含JIT)》