They and I'm going to try and explain to you why there are five types of databases and why you might use each.

One. So this came out of a discussion with colleagues. I had a colleague who categorized databases in ways I frankly still don't understand, and I said, but it's obvious there are five types, and he said, what?

So I think the types are relational, column, document, key-value and graph.

I'm going to give you an example of how you might use these, and at least one open-source example of each of these.

Note that the examples I chose are not the only ones. Go out and do your own research. So, we're going to start with relational, um, for reasons that may become obvious in a moment.

So, here is a table. It's called books. It has three columns.

They are called ID, title, and author. Each column has a data type. We've got an integer ID which we're using to distinguish the rows from each other.

And we have two string columns. One is the title of a book and the other is the name of the author.

Uh, I have not written any books. These books are made up. All books in this example are false.

Uh, don't go looking for books called this book and that book. Although I want books called that.

So relational databases come from um a paper by um Edgar Codd back in 1970 called a relational model of data for large shared data banks.

Large here is in the context of the time; we would regard these as relatively small, but this was people trying to do what were at the time large amounts of Data.

Uh, the trouble was that the existing ways of creating databases tended to require your program to know the structure of the database. So if you wanted to alter the data, um, you had to change your program.

Um, and also it was all a bit fiddly. He wanted to create a mathematical approach that was just simple enough and just abstract enough to represent just about anything.

And I think he succeeded beyond what would have been his wildest dreams because relational technology has taken over the world these days.

Um, and I have a note here to regard him as the granddaddy of relational databases and perhaps of databases altogether.

Uh, this paper is online in the notes; there’s a link to it.

Um, in his terminology, what he called a relation we now call a table. This actually took about 15 years to gain adoption. Uh, adoption on micro and mini computers, uh, fax machines and so on was quite quick, but IBM was very reluctant to abandon their older forms of database, and it wasn’t until the mid-1980s that IBM, who were very dominant in the big computing world at the time, adopted relational databases, and that became the dominant technology.

Now, our original table we’re all used to thinking of in tables, but if you start to increase what’s in your table data, it gets very complicated and you often end up with a lot of duplication.

So what we’re doing here is we’ve got a books table and an authors table.

Um, and we are using the author ID, which is in red. We can see that the two books in the Books tables have the same author.

We can look at that ID across in the authors' table. This has advantages when there for instance we have two John Smiths.

Well, we can specify which one we mean. If we were just using John Smith as the name in one table, it would all get very confusing.

This idea that you can link things in different tables by using shared IDs is fundamental to how relational databases work as well.

And if you think about it, I think of these as a bit like Python objects with references to other Python objects.

An important concept here is SQL, which is a domain-specific language for talking to relational databases.

It originated in the 70s. So again, very early on from when the original paper came. The original name was SQL, which was a pun probably on a related language called Quell from Ingres, which it was competing with.

It couldn’t stick with that name because there was a commercial product with the same name and they would get upset.

So it became SQL, but that’s why you’ll hear people talking about SQL.

That pronunciation has been around for a very long time. It was standardized in the 1980s, but the latest version was released in 2023.

So this is a language that’s still under active development.

Because it’s standardized, you’ll find that just about every relational database vendor has SQL bindings.

They’re not always quite the same as each other, but there’s a lot of commonality.

So to create those tables, these are the SQL statements we might use. Um, so we're creating a table called authors. We're defining an ID column, which is an integer.

We don't want the values to be null. We want them to have values. And we're saying this is the primary key.

This is the way we identify our records. And then it has a name, which is a text field, not null.

The details may differ between databases. And then we've got our books. Again, we've got an ID, which is an integer, the primary key.

It's not null. We've got a title, which mustn't be null.

And then we've got an author ID. This is an integer. And here we're saying that this references the ID in the authors table.

This means that when we add an author ID, the database can check that we are using an ID that already exists in the authors table.

So this keeps our data tidy. It helps stop us from making mistakes.

And to find the books written by this notional author, this Tibs, um I can select books.

title from books and I need to join together the author's ID in the authors table with the ID in the books table that match, and say I want those to match when I'm looking this up, but I want the author's name to be Tibs, and that will give me this book and that book back.

I should point out I've been struggling with writing SQL joins Since the 1980s, I always have to go and look up how to do this.

So don't be worried if you find SQL difficult.

SQL, however, is a really good language to learn because it’s a different kind of language than the procedural languages we’re all used to, like Python, where you tell the computer, do this, do that, do that.

In SQL, what you’re doing is finding out about this, give me this result.

Um, and it’s up to the SQL engine then to figure out how to do it for you.

So, relational databases, they have tables, they have rows, they have columns.

We’re all quite used to that. We’ve been doing that with paper and pencil for generations. And of course we probably used spreadsheets as well.

So these tend to be a very natural way to think. Schema design, saying how the data is constructed, and what the data is like is upfront.

When you create your table, you have to say what you want your columns to be.

So my first example is PostgreSQL, which is um, the elephant is a very nice logo for PostgreSQL because it’s kind of this big, powerful um thing.

It’s had 35 years of active development, which takes us back to 1990. Um, I wrote that down so I’d remember.

Um, it’s always a good place to start. It’s one of my favorite databases.

Um, I have favorite databases. Okay. Um, it’s pronounced PostgreSQL, which I find, but you’re allowed to call it PostgreSQL because of history, and it derives from going back to the 80s.

There's a sequence of databases that lead up to PostgreSQL.

It's got rich data types, not just integers, text, and so on. You can store functions in the database, which means that you can have the database run code, which means that your client doesn't have to do it.

It's very extensible. You can write plugins and so on. But also, it's very easy to fork it, do things with it, and then, if you're nice, push the your extensions back into PostgreSQL.

A lot of companies have done that over the decades. It's got excellent documentation. So, it's very broad.

It talks about everything, but it also goes very deep, which makes me very happy because I often find that documentation talks about what I want and then stops too early.

And I recommend it as a good place to start if you need a relational database.

My second example though is SQLite. Now, the author says he pronounces this SQLite, but he also says he doesn't care how anyone says it.

So, I tend to say SQLite because that's easier. This is a library. And I love this second quote here: Small, fast, reliable.

Choose any three. So, it's a library. It's built into the Python standard library.

Import sqlite3 and you've got it. You can run it at the command line and when you Start it; it creates a temporary database.

It's everywhere. It's on your phones. So, it can legitimately claim to be the the database that has the most penetration in the world.

It's everywhere. It's single-user, which you'd expect because it's a library.

Um, it's slightly odd in some ways, and actually the schema is optional, and how the types work is a bit odd, and the documentation recommends using it as a file format rather than JSON, YAML, or TOML for local storage.

In fact, they claim that it's sometimes faster than actually creating a text file.

Um, it can be faster than fopen to access. Um, and it can be quite portable.

There are extensions to create zip files that you can send around. So when should we use a relational database?

Well, it's almost always a good place to start. The technology has been around a long time and is very mature.

So if your data fits, which it probably does, um, and whatever you need to do, there's probably a relational database that can do it, and it's probably going to be fast enough.

So, column databases. So this looks like a relational database except I split the columns apart. I apologize for the name of that first column for reasons that become evident later.

Um, the timestamp was too long, so this is just DT. So we've got a timestamp, we've got an ID, we've got a book name, we've got a price, we've got how many books we sold, and we've got a customer ID who bought that book.

Um, someone named 923 bought this book twice at the same time. Maybe they were giving it away.

What we've done here is just split the columns up. Very simple idea. So, column databases are in many ways an optimization of relational databases.

They're saying sometimes you don't want to access data as rows. You want to access data as columns.

Maybe I've got 100 rows, but I typically use two or three of the columns. So, if I have to go away and retrieve all the rows and then extract just the columns I want, that's both wasteful and slow.

But if I know I'm going to do that, if I've got the columns stored in the database, I can just get the columns I need.

So, storing data as columns can already give you a win there. But because we know the data type of the columns, we can compress that data, um, giving more efficient data storage.

So, we take up less space, which also means data is faster to retrieve.

So, this tends to be good for data that doesn't change a lot because, um, working with data that's compressed is a pain.

So here's a view of that same table, but I've shown where we've compressed repeated values.

Um, so the yellow items are things where that data is repeated in the next rows.

Um, because it's a column, you can imagine our storage. You need to get a A little bit clever about indexing helps us actually figure out where we want to be in the compressed data to retrieve things.

This is stuff we've been doing in computer science for decades. So it's faster at adding new rows. Um, and then the compression will happen in the background over time.

If there's compression, it's faster at adding new columns, which is interesting.

In a relational database, adding a new column means adding it to all the rows. It's a slow operation.

It's fast at querying a few columns out of many. It's going to be slow at changing or deleting rows because you've got to unpick all your columns and then put them back together again.

So it suits log data, sensor data, um, pressure, time, humidity, or tons of information you might be collecting, time series data in general, and actually any sort of data where you're going to store it for historical purposes.

You're not going to change it, but you want to use it for analytical purposes.

So you'll see column databases used as data warehouses. And my example here is ClickHouse. Now, this is quite a common model.

ClickHouse has an open-source version which is the GitHub repository linked there and um a commercial venture that will give you enterprise editions that do more stuff, and the commercial version claims it's the fastest and most resource-efficient real-time data warehouse and open-source database.

ClickHouse is blindingly fast for queries, nicely because it's essentially still a relational idea. It's still SQL, so Everything you've learned about using SQL still works.

It's got extra functions and utilities to make it easy to do columnar stuff, but it's very familiar.

Explaining the 5 types of database and how to choose between them — Tibs

The official YouTube Channel of the EuroPython conferences. Current edition: EuroPython 2023.

EuroPython is the official European conference for the Python programming language.

Copyright © 2004-2023, EuroPython Speakers, EuroPython Society, Sweden and the Local Organisers of the EuroPython conferences. "EuroPython" is a trademark of the EuroPython Society.


Explaining the 5 types of database and how to choose between them — Tibs

Scripts