On Tue, Sep 1, 2015 at 8:53 AM, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > As discussed in > https://www.postgresql.org/message-id/20150611230316.gm133...@postgresql.org > we've been working on implementing columnar storage for Postgres. > Here's some initial code to show our general idea, and to gather > comments related to what we're building. This is not a complete patch, > and we don't claim that it works! This is in very early stages, and we > have a lot of work to do to get this in working shape. > > This was proposed during the Developer's Unconference in Ottawa earlier > this year. While some questions were raised about some elements of our > design, we don't think they were outright objections, so we have pressed > forward on the expectation that any limitations can be fixed before this > is final if they are critical, or in subsequent commits if not. > > The commit messages for each patch should explain what we've done in > enough technical detail, and hopefully provide a high-level overview of > what we're developing. > > The first few pieces are "ready for comment" -- feel free to speak up > about the catalog additions, the new COLUMN STORE bits we added to the > grammar, the way we handle column stores in the relcache, or the > mechanics to create column store catalog entries. > > The later half of the patch series is much less well cooked yet; for > example, the colstore_dummy module is just a simple experiment to let us > verify that the API is working. The planner and executor code are > mostly stubs, and we are not yet sure of what are the executor nodes > that we would like to have: while we have discussed this topic > internally a lot, we haven't yet formed final opinions, and of course > the stub implementations are not doing the proper things, and in many > cases they are even not doing anything at all.
Fujitsu is also interested in implementing a columnar storage extension. First we thought of implementing this extension using index access methods The following is the basic design idea of the columnar extension, currently this may need to be redesigned according to columnar access methods, create an vertical columnar index on a table with specified columns that are needed to be stored in columnar storage format. 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. Because of two storage's, we need maintain two entries in pg_class table. one is WOS and others are all columns in columnar storage. Insert: 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, whereas with columnar access methods the column data doesn't present in the heap. 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. 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. The concept of columnar extension is from Fujitsu Labs, Japan. Any comments for further evaluation of this approach according to columnar access methods? Regards, Hari Babu Fujitsu Australia -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
