Analyst Perspectives

This is the second in a series of posts on the architectures of analytic databases. The first post addressed massively parallel processing (MPP) and database technology. In this post, we’ll look at columnar database technology. Given the recent announcement of HP’s plan to acquire Vertica, a columnar database vendor, there is likely to be even more interest in columnar database technology, how it operates and what benefits it offers.

Fundamentally, columnar database technology offers two primary benefits, increased speed and reduced storage requirements. We repeatedly emphasize the importance of speed to end users. Our benchmark research on business intelligence and performance management show that performance is a key consideration among those seeking improvement in this area. Besides speed and reduced storage other benefits may exist in particular implementations, but these are the two most significant – and most directly attributable to columnar technology.

Columnar technology was made popular by Sybase, now part of SAP, with its IQ product. Today, many other vendors have brought columnar database products to market, among them 1010data, Calpont, Infobright, ParAccel, Sand Technology, SenSage and Vertica who was just acquired by HP. Recently, traditional row-oriented database vendors – among them Aster Data now being acquired by Teradata, EMC Greenplum and Oracle – have added some columnar capabilities to their products including. Additionally, in-memory database technologies frequently utilize column-based architectures.

Columnar database technology typically includes columnar storage and/or columnar database execution. We’ll talk about both and why each is important.

Columnar storage turns the traditional relational (row-oriented) database on its side. Instead of storing data in rows, which is the norm for databases such as IBM DB2, Microsoft SQLServer, MySQL and Oracle, data is stored by column. Whereas a row would consist of customer name, order date, amount of the order, order number, shipment date, method of shipment, and so on, a column of data would consist of all the customer names. A separate column would contain all the order dates. Another would contain all the order amounts, and so on. Row-oriented storage is more efficient (that is, faster) when recording or retrieving the data necessary to process a transaction. The disk only needs to go to one location for either operation. Column-oriented storage, on the other hand, is more efficient (that is, faster) when querying the same item across many rows of data, which is common in most business intelligence (BI) queries. Since all the similar items are grouped together on disk the database can scan them more rapidly than if it had to retrieve all the fields in a record just to get at one or two of them. The difference is magnified when querying hundreds of millions or billions of rows.

Columnar organization yields several storage-related benefits as well. First, columnar databases make little or no use of user-defined indexes, so they require no additional storage other than that for the data. Second, once the data is sorted and stored by column, the potential for compression increases dramatically. One simple example I like to use is to imagine storing billions of stock quotes, instances of web activity, network activity or any other type of data that includes a date field. Even if you kept five years of history, there are only 1826 or 1827 (depending on leap years) unique date values in that time period. With a row-oriented storage approach, you would need to store all of those billions of values on disk. Using a columnar storage technique you could potentially store each date only once and record the number of occurrences for that date. This technique, referred to as run-length encoding, can lead to dramatic compression ratios, as suggested by the example. Other techniques are available too but I won’t go into those at this point. What is important to note is that the benefits of compression are significant enough that row-oriented vendors have figured out how to engineer some these same techniques into their storage algorithms.

Let’s move on to the second major aspect of columnar databases, columnar execution. Columnar databases not only can reduce the amount of storage required, but also in many cases can reduce the amount of memory required to process the data. If the compact representation of the data can be retained as queries are processed, it follows that less memory is required to manipulate the data. Without going into all the details, some columnar database engines can select subsets of data and perform joins on the compact representation, resulting in even more efficiency and performance gains.

Columnar databases do have their downsides. They are typically less efficient when it is necessary to update or delete data, for several reasons. First and foremost, updating or deleting a single row of data requires finding several locations on disk where the individual columns are stored. Even single row retrievals can be slower, resulting in a noticeable performance difference. I know of one financial service firm that has spreadsheets with thousands of individual data references in separate cells of the spreadsheets they use. Even though each individual lookup only takes a small fraction of a second longer, the overall performance is much slower because the difference is magnified many times over. The second reason updates or deletes can be slower is the organization of the data. If a single value in the middle of a long list of values is deleted or updated, some portion of the page needs to be reorganized. Depending on the vendor’s approach this reorganization issue can become significant over time as more and more updates or deletes are processed.

Because of these issues with columnar databases, at least two hybrid implementations have emerged. One form of hybrid is a row-oriented database with some columnar capabilities added, such as those noted above. The other form is a columnar database with some row-oriented capabilities added on. Although a hybrid architecture minimizes the downsides of each approach, I suggest that you think about it this way: While a hybrid minimizes the downsides it does not eliminate them.

The bottom line is that your database engine will be primarily column- or row-oriented, and you should consider that in the selection and evaluation process. Over time I suspect the hybrid techniques may approach each other in terms of performance and capabilities, but for the time being I think you will still see some differences depending on the specific use case and workload you are trying to manage.

Columnar databases can also be implemented as MPP (massively parallel processing) systems, as hardware appliances or as in-memory systems. Calpont, Paraccel and Vertica are examples of MPP columnar databases. Kickfire, acquired by Teradata, is probably the best example of an appliance-based columnar system, while the SAP High-Performance Analytic Appliance (HANA) is probably the most ambitious in-memory columnar system.

I hope you can use this information as you investigate database vendors and evaluate their product offerings. Consider whether you need columnar storage alone or if you need columnar execution as well. Consider whether the cost of learning and managing a separate system is worth the benefits. Consider your current and future use cases and how they match a row-versus-column orientation. Regardless of what you think you understand about the different approaches make sure to conduct a proof of concept with your data and your workload. As you might imagine from this article, the differences between these approaches can be subtle and a proof of concept is likely to be the only way to evaluate which approach works best for you.

Regards,

Ventana Research