What is a Wide-Column Database?

Wide-column databases are one of the most important types of NoSQL data storage solutions. Countless businesses use them every day, especially in analytics and big data workflows.

But, if you go searching for a clear, concise definition, there’s a good chance you’re going to be left disappointed.

Unless you like technical marketing jargon.

Today, we’re filling that gap.

Specifically, we’re diving deep into everything you need to know about wide-column databases - including what they are, how they work, what they’re used for, and how they fit into the bigger picture of the data management landscape.

We’ll also compare these to SQL solutions, columnar databases, NoSQL databases, and other key alternatives.

Then, we’ll take a more practical turn and think about the specific benefits, challenges, limitations, and strategies involved in working with data in wide-columns.

Let’s start with the basics.

What is a wide-column database?

A wide-column store is a type of column-orientated database that stores data in tables, where the names and formats of individual attributes can vary from row to row.

This is sometimes also described as a two-dimensional key/value store. We can think of it as one key/value pair where the first key identifies the row - and its corresponding value contains potentially many different columns and their values.

The idea here is that the attributes that are stored against any given row can be flexible and dynamic. In other words - we can still have data tables, but we don’t necessarily need to store the same data in the same way for each individual entity.

Say for example we were dealing with data about your customers and clients. Chances are that what you need to represent a normal B2C customer will be quite different from the data you’d store about a major enterprise contract.

Wide-column store databases enable you to achieve this. As we’ll see later, this also brings along some major performance advantages, especially with regard to analytical use cases. This gets a bit technical though, so we’ll return to it a little later.

Examples include DynamoDB, Apache Cassandra, and Bigtable.

For now, let’s zoom out a bit.

What are column-orientated databases?

We said earlier that a wide-column store is a type of column-orientated database. But, we didn’t delve too deeply into what that actually means.

This is a type of NoSQL database that stores data in columns.

In terms of actual storage, we can distinguish between row-orientated and column-orientated databases. Basically, this is whether the stored data is stored in rows or columns.

So, in a row-orientated database, each entry is stored as a separate object, containing all of the relevant attributes. This might look like:

1rowId1, firstName1, lastName1, phone1;
2
3rowId2, firstName2, lastName2, phone2;
4
5rowId3, firstName3, lastName3, phone3;
6
7etc

This is an efficient way of storing data that’s going to be used for performing operations on a comparatively small number of rows - but if we want to perform operations on single attributes from a large number of rows, it‘s not optimal.

We’d have to handle the entirety of every row to access the phone numbers unless we had some form of indexing in place.

Column-orientated databases work differently. As you might have guessed, the data is stored in columns rather than rows.

So, stored objects contain all of the values for a given column, like so:

1rowId1, rowId2, rowId3;
2
3firstName1, firstName2, firstName3;
4
5lastName2, lastName2, lastName3;
6
7phone1, phone2, phone3;
8
9etc

Since all of the attributes for a given entity - like a customer - are stored across multiple objects whole-row operations are relatively inefficient.

By contrast, column-orientated databases typically offer better performance when we need to handle a subset of attributes from a large number of entities.

The distinction between a typical column-orientated database and a wide-column database relates to something called column families.

So…

What is a column family?

A column family is a database object that contains columns of data. Each column family stored is made up of a key/value pair, where the key is mapped to a value that is a set of columns.

So, each column family might store one or multiple attributes - if we want to represent large amounts of data.

An individual column within a column family will normally contain a name, value, and timestamp.

Another way of thinking about this is that each row has a unique identifier - sometimes referred to as a row key - and then the full details of each attribute stored against it as dedicated columns.

So, a single column family might look like this:

This is how we achieve the flexible, dynamic rows that we talked about earlier, as it enables us to store different attributes for individual data entities.

Wide-column databases vs RDBMSs

So, to solidify our understanding of wide-column databases, let’s think about how they differ from some of the other types of solutions that you’re probably familiar with.

The big alternative here is traditional relational database management systems (RDBMSs).

Most relational databases, including SQL variants, are row-orientated. Each row must store the same attributes as columns - even if some of these are null for individual entries.

The ultimate goal behind these is to ensure both consistency across your data set, and performance when querying multiple columns in a single row - known as online transactional processing (OLTP) use cases.

But, they’re less efficient for situations where we need to retrieve large numbers of values for a handful of columns across different rows - making them less effective for big data, IoT, or analytical use cases.

As we saw already, wide-column databases are effectively the mirror image of this, offering superior performance for online analytical processing (OLAP) use cases since we can query entire data sets for a particular attribute, without needing to know about the whole row.

So, it’s not quite the case that one solution is inherently better than the other.

Let’s return to the idea of consistency for a second. This is one of the big selling points of RDBMSs. We can consistently enforce rules on attributes across all of our data with relative ease - at the expense of flexibility.

Wide-column databases strike a slightly different balance. As we know, part of the appeal is that we can store different attributes against individual rows.

This naturally undermines the degree of consistency we can achieve across the data set as a whole, compared to something like MySQL or Postgres.

Wide-columns and other NoSQL solutions

Wide-column databases - and column-orientated databases more generally fall under the umbrella of NoSQL tools. This is a pretty broad categorization though - essentially drawing in any solution that isn’t a traditional SQL-based RDBMS.

So, it’s worth surveying the landscape a little bit in order to better understand where wide-column stores fit into the picture.

The most important clusters of NoSQL solutions include:

• Key/value stores - A simple database where all data is stored in key/value pairs.
• Document databases - A database where entities are represented by documents, usually in a JSON or XML format.
• Column-orientated databases - Where - as we’ve seen - data is stored in its constituent columns.
• Graph databases - Where database objects are stored as related nodes.

Each of these comes with its own particular benefits and challenges. For example, key/value stores offer a high degree of simplicity and scalability, but make it more difficult to represent complex phenomena and objects.

Document stores make it easy to store and retrieve entire objects as they are most often used by client applications, but can make it a little more complicated to perform bulk actions on these objects.

Graph databases make it easy to represent complex relationships between entities - as well as facilitating real-time data access - but they can also be quite complex to understand and work with.

But today, we’re specifically interested in wide-column databases and how they’re used.

That leads us to…

Wide-column databases use cases

So, in what kinds of situations would be turn to wide-column storage? We’ve already hinted at a few of these, but it’s important to spell some of the key use cases here out explicitly.

Here are three of the most common.

Data warehousing

Wide-column stores are pretty synonymous with data warehousing. This is the practice of creating a central repository of organizational data that can be used for analysis and informing decision-making.

This is an ideal use case for wide-column databases, as we’ll often need high-volume aggregation for data mining and garnering actionable insights from very complex, large data sets.

Big data and analytics

As we’ve said already, wide-column stores are ideally suited to analytical uses - especially in more complex or challenging applications - such as real-time analysis, high-volume or concurrent querying, and large-scale aggregation.

Similarly, big data applications require efficient storage, retrieval, and manipulation of massive, complicated sets of information - making wide-column storage one of the most viable options.

High-write throughput

Finally, we have situations that require a high volume of write actions, like gaming or ecommerce. In other words, situations where we need a large volume of users to be able to interact with our data with minimal latency.

Since wide-column stores are optimized for handling high-write throughput, they’re often used in these kinds of applications.

Challenges and limitations

However, wide-column databases aren’t suitable for every use case - and even in their ideal use cases, there are challenges that we’ll need to account for. So, it’s important to have a realistic picture of what we’re dealing with.

Some of the most common pain points of working with wide-column databases include:

• Limited querying capabilities - querying can be somewhat limited within wide-column databases, especially compared to other NoSQL platforms.
• Data modeling - wide-column databases aren’t typically optimized for creating repeatable complex data structures or relationships.
• Advanced or niche features - Many platforms are limited in their support for advanced use cases, like geospatial data processing.
• Achieving consistency - If ACID compliance is a priority, wide-column stores are usually an effective solution.
• Migrations and portability - Migrating to and from wide-column stores can be particularly challenging, labor-intensive, and time-consuming.

Of course, the point here isn’t to put you off working with wide-column stores altogether.

Rather, when choosing any data storage solution, the important thing is to have a clear grasp of the relative pros, cons, and appropriate use cases.

Working with data in wide-column stores

Finally, let’s wrap up by considering how we can work with data that’s stored in wide-column databases.

Typically, we’ll either need to install the relevant database engine on whatever environment we’re working in - or use the vendor’s API in order to access an existing database server remotely.

For example, Amazon’s DynamoDB offers both an SDK and an API for retrieving and manipulating data in client applications.

There’s also the issue of providing users with appropriate interfaces for working with wide-column data. Budibase offers a dedicated data connector for DynamoDB, alongside REST API integrations - and full support for custom data sources through our dedicated CLI.

To learn more, check out our ultimate guide to data management software solutions .