The Implementation of Column-Oriented Database in Postgresql for Improving Performance of Queries

Abstract

The era of Column-oriented database systems has truly begun with open source database systems like C-Store, MonetDb, LucidDb and commercial ones like Vertica. Column-oriented database stores data column-by-column which means it stores information of single attribute collectively. Row-Store database stores data row-by-row which means it stores all information of an entity together. Hence, when there is a need to access the data at the granularity of an entity, Row-Store performs well. But, in decision making applications, data is accessed in bulk at the granularity of an attribute. The need for Column-oriented database arose from the need of business intelligence needed for efficient decision making where traditional Row-oriented database gives poor performance. PostgreSql is an open source Row-oriented and most widely used relational database management system which does not have facility for storing data in Column-oriented fashion. This work discusses the best method for implementing column-store on top of Row store in PostgreSql along with successful design and implementation of the same. Performance results of our Column-Store are presented, and compared with that of traditional Row-store results with the help of TPC-H benchmark. We also discuss about the areas in which this new feature could be used so that performance will be very high as compared to Row-Stores.
Index Terms — Data Mining and Data Warehousing; Knowledge Database; Postgre SQL Database System and Database Relations