Non-relational architectures

Data warehouses

Star schemas

Warehouses are denormalised and designed to be able to ask questions about large data sets quickly. For example there is the star schema which is organised conceptually by creating a huge denormalised table (called a fact table) of all the keys and the summary data you want to aggregate together which is then referenced via queries against dimension tables that are fast keys into the fact rows. For example this allows you to pull all of the sales data for a specific customer at once without having to join it to other entities, you can combine dimension tables together to ask more complex questions. You can get information like sales totals by summing data directly from the fact table, instead of doing several joins. The fact table does not bother with the individual order lines, but just have the totals. It’s called a star schema because it looks like a drawing of a star with the dimension tables around the fact table, a big star schema is sometimes called a centipede schema because it looks like a centipede. Who said computer people don’t have fun?

You couldn’t use one of these schemas for #OLTP because the complexity of keeping it updated would be extremely high and it would probably break under load. Warehouses are usually downstream of the main systems and have specialist software to populate them, or sometimes just carefully constructed queries. More modern concepts such as data lakes build on this.

Cubes

SQL isn’t so great at showing you when information isn’t there. Data analysts came up with the concept of a cube. To illustrate this let’s assume we have some time based data, a weather station, for example:

If we had the situation where there were some missing readings it would be really hard to write the plain SQL to do this. You would have to use a special function like generate_series in Postgres to create a table that simulated the time series and then do an outer join against to give you the missing ones, or write some code to create a dummy table that had the time series in, along with maintaining that table too.

This is fiddly but works for the one dimension of the time series with a bit of magic. Say you have several stations in different regions and you wanted to be able to get missing stations with missing data so you now need to outer join to two or three things, and then make sure you don’t get bitten by a fan trap.

What cubes are is denormalised data sliced up using ranges and functions with an entry where the dimensions intersect. So instead of writing a query you would put the dimension values into the function for the cube and see what value popped out.

This definitely wouldn’t work well for OLTP style systems.

Document databases

There was a hipster trend about 15 years ago for noSQL databases. In essence these were document based databases that didn’t use SQL at all. The most notorious of these is MongoDB but there is also CouchDB - at a high level all these systems are is document repositories, you have a key that will take you to an arbitrary JSON document that can contain anything you like.

In fact you would have the equivalent of tables, where documents of similar structure were gathered together. Querying this data involved creating functions that dug into the JSON attributes and would then pull records that matched the function’s expectations. Think about how you write JavaScript to scan through an array of objects and filter them, the process is identical.

You can change the structure underlying JSON by adding or removing attributes that you are saving and it will carry on. This takes you to a wonderful world where schema changes are really easy and you don’t need to think about migrations until it bites you. The kindest thing to say is this was a little over-hyped.

In essence everything becomes a full table scan. Just as you would in JavaScript in the browser you would create a map that merges the data you want by joining the appropriate document types based on some common data (a map), and then filter the result to get what you need (reduce the values you want). This process is known as map/reduce. It’s very common on extremely large data sets, for example query engines like Hadoop use map/reduce to take streams of matching data and merge them. This is how search engines work in a very rough approximation. It can also be quite lossy if the data changes underneath, which is fine for engines with summaries of millions of web pages, but not great for your sales processing.

Despite some cynicism I have about these systems they are incredibly useful when you have the appropriate use case for them. They were somewhat hampered when the query tools put on top of them pretended to emulate SQL because the underlying ideas are completely different and you could accidentally create map/reduce processing that used up all the resources on the machine the database was running on.

I have a soft spot for CouchDB because it sits there looking like a web server you can talk to using CURL, sending documents and getting IDs or documents back. If you update the document it creates a new version, but you can go back and look at the older versions if you want to. It’s incredibly useful for logging and keeping parameter sets in, where you can go back and see what changed when. I had a bad experience with MongoDB when it was new because we asked it to do something it wasn’t designed to do and it would run out of RAM and crash. I hope this has been fixed.

An aside about key-value stores

Key-value stores are systems like Redis where you can store an arbitrary value against a key of your devising. Superficially this looks like a noSQL store, and you could use it as one if all you wanted to do was store the data. Redis doesn’t have the map/reduce functions built in, and it wouldn’t work in this scenario so you’d have to write your own.