An essential part of providing the best possible BI solution is the choice of database.

In an ever evolving world of technological possibilities, choosing a solution that is both capable of solving the current problem and scales for the foreseeable future is of critical importance.

First off, based on the practical use of your database system, an important choice is the type of database system.

This article focuses on the choice between two database architectures: row-oriented vs column-oriented databases, more specifically (row-oriented) PostgreSQL and (column-oriented) HPE Vertica.

Row-based databases (like PostgreSQL) are the best known database architecture. As the name implies, data is stored and manipulated in rows. Row-based operations work really well in an application context, where the database load consists of relatively large numbers of CRUD-operations (create, read, update, delete).

In analytical contexts, where the workload consists of a relatively small number of queries over a small number of columns but large numbers of records, the row based approach is not ideal. Columnar databases have been developed to worked around the limitations of row-based databases for analytical purposes. They store data compressed and per column, much like an index in a row-based database.

Vertica is not just an analytical database, it is a  distributed analytical database. By running on a cluster of machines, Vertica is capable of handling huge volumes of data, providing snappy response times through standard SQL.

Although the true power of Vertica only shows when running on a cluster, handling huge volumes of data, the architectural benefits already show on a single machine and more modest amounts of data. We’ll compare both databases in a BI solution using a sufficiently large amount of flight data.

The core functionality for our BI solution test case will be the calculation and aggregation of flight data for reporting purposes. A possible example report contains the average departures and arrivals per airport per year. The key factor in this test case is the speed of calculation and aggregation of the data to report.

Calculating and aggregating data in database queries are tasks that span large amounts of data. The way these large amounts of data are handled differs between the types of database systems.

In a row oriented database table, every record contains 1 item of the information stored in the table. Every field in this record specifies this item of information.

for example

As discussed earlier, row-oriented database systems are designed to query for specific operations records and link these to specific records in other tables through joins. This optimizes create, read, update and deletes (CRUD operations). To optimize the search for specific records, an index can be created on the search identifier.

A row-oriented database is thus optimized for the performance of frequent, item-specific CRUD operations.

A column-oriented database system is, in essence, structured differently. Rather than store the information row by row, the information is stored per column in combination with the unique identifier for the specific item.

Column oriented database systems are optimized for queries that require only specific columns of a table. Because the different columns are stored separately on disk, a query only reads the columns necessary to perform the query. Columnar data is stored on disk in a compressed format, which further reduces the amount of I/O required, and thus further speeds up the query response time.

To perform the tests for this use case, we use a centOS (7.1) virtual machine in Oracle Virtualbox. This virtual machine has the following database systems installed:

• PostgreSQL 9.2.15
• Vertica Analytic Database v7.2.0-0

Both database systems have a database instance set up named dbflights. Both dbflights instances contain the following tables, modeled according to the datawarehouse star scheme:

• flight_fact
• airport_dim
• carrier_dim
• flight_dim
• plane_dim
• time_dim
• date_dim

The flight fact table contains 35874731 records.

Using ETL developed with Pentaho Data Integration (PDI), both dbflights instances are loaded with a sufficient amount of relevant flight data. All the flight data used is available online.

To compare the performance of the two database systems in the specific usecase, queries relevant for reporting purposes are sent to both instances a couple of times using the freely available SQL client DBeaver.

All query execution times are registered. For every query, the execution times are compared to draw a conclusion based on sufficient facts.

Executed queries vary from simply counting rows to slightly heavier grouping and aggregating. For every query tested, the query, the query result and the execution times are displayed. Execution times are registered in milliseconds (ms).

General row count of the flight_fact table

select count(*) from flight_fact;

Counting the amount of flights per airport without joins

For testing the effect of an aggregation on the response time of the query, the flight_fact table is grouped by airport_origin_id.

select airport_origin_id, count(*)

from flight_fact

group by airport_origin_id;

Counting the amount of flights per year

select d.year, count(*)

from flight_fact f join date_dim d on f.date_id = d.date_id

group by d.year;

Getting the top 20 airports with the most departure flights

select a.airport_fullname_name, count(*)

from flight_fact f

join airport_dim a on f.airport_origin_id = a.airport_id

group by a.airport_fullname_name

order by count(*) desc

limit 20

Counting the busiest days per year

select d.year, d.fullday, count(*)

from flight_fact f

join date_dim d on f.date_id = d.date_id

group by d.year, d.fullday

order by d.year, count(*) desc;

Calculating largest percentage of cancellations per carrier

select c.carrier_description_name, sum(f.cancelled_qty), count(*), (sum(f.cancelled_qty)*1.00/count(*))*100 as PCT

from flight_fact f

join carrier_dim c on f.carrier_id = c.carrier_id

group by c.carrier_description_name

order by PCT desc;

It would be very interesting to see in which airports these cancellations took place. If we join the existing query with a second dimension, airport_dim, we get the following query:

select a.airport_fullname_name, c.carrier_description_name, sum(f.cancelled_qty), count(*), (sum(f.cancelled_qty)*1.00/count(*))*100 as PCT

from flight_fact f

join carrier_dim c on f.carrier_id = c.carrier_id

join airport_dim a on f.airport_origin_id = a.airport_id

group by a.airport_fullname_name, c.carrier_description_name

order by PCT desc;

This query takes around 11000ms to fetch 1000 rows on the Vertica instance whereas the PostgreSQL instance doesn't succeed in completing the query after several minutes.

With differences varying from only 0.12% (36ms – 29973ms) up to about 27% (6609ms – 25943ms) of the calculation time, the execution time of the operations on the Vertica instance are significantly lower than the execution times on the PostgreSQL instance.

These statistics show that the Vertica instance computes the operations a lot quicker than the PostgreSQL instance. Therefore, this is an excellent example of why you shouldn't waste time tuning performance when the choice of system can resolve the performance isssue. And yet, the true power of a Vertica system resides in its clustering capabilities. If we increase the amount of data, divide it over a few nodes and then compare it to a PostgreSQL instance, the difference in response times will only increase. This is a very interesting subject for a follow-up blogpost!