Learning is not a spectator sport

I have a presentation that I’ve given from time to time over the years and it’s called the “bullet point myth”. The main thrust of the presentation is that if you look at a high enough level then all databases can be considered equal.

At the highest and most generic levels, it’s easy to claim that

• Every database has consistent read.
• Every database has some sort of backup and recovery.
• Every database has partitioning,

etc. etc. You can be confident that the sales department of every database vendor does this with great enthusiasm 😊. You focus the customer on the high level which then lets you claim that your database has every possible piece of functionality that every other database has as well.

But my presentation mounts a rebuttal of that high-level argument in saying that what really matters is not the fact that every database can make a claim to tick off every single functionality bullet point, but how well does a database match the business requirements.

For example, if one of your critical business requirements is stringent uptime then while every database will can probably tick a checkbox saying “Yes, of course we have high availability!” there is a more nuanced discussions about just exactly what constitutes high availability for a database and how much effort you have to go to in order to implement it to meet your particular business needs.

Which brings me to this example I saw on a TikTok video the other day. It was presented as an interview question: If I have a 300 million row table and I want to add a new column and that column must be fully back-populated, what is the best way to do it?

Now, every relational database can add a column – that’s pretty much part of what a relational database is. They all hence tick that magical bullet point of saying “Yes, I can add columns to tables“. However, this is a perfect demonstration of why not all databases are the same. I continued on watching the TikTok video and the first thing they presented was “This is the wrong way in order to do it. Don’t just add the columns, don’t just write a big update statement to backpopulate the data etc. because you will lock all the users out of your system while you’re doing it.”

The video continued on saying if you are answering this interview question the right way you would answer it would be to tackle the task in 3 stages.

• In stage 1 you add the column but leave it empty.
• In stage 2 you then fix the application such that all future writes will populate the column as expected,
• And finally stage 3 is a back population of the data, using incremental batching to ensure that you don’t create too many locking issues etc.

To be honest, even that approach worries me, especially at the stage 3 phase where taking every single row and making it larger may have some implications depending on which database you are running.

But that brings me back to the concept of the bullet point myth. The fact that every database can add a column does not reflect the vast differences between database platforms in the way they let you implement it. For example, let’s take a look at how I would tackle this problem if I’m running an Oracle database. I’ll build my 300 million row table

SQL> create table t 
  2  as select r from generate_series(1,300000000);
Table created.
SQL> select count(*) from t;
  COUNT(*)
----------
 300000000

and here is how I add a column that is fully backpopulated. I’ll turn on timing to see how long this task will take – after all, it is such a demanding task that it warrants an interview question!

SQL> alter table t add plan varchar2(10) default 'free' not null;
Table altered.
Elapsed: 00:00:00.01

Voila! One command, add an in an instant, no stage 1, no stage 2, no stage 3, no back population etc. And just to prove that my rows are in fact there, I’ll just query a random 10 rows from the table and you can see they have all been back-populated.

SQL> select plan from t where rownum <= 10;
PLAN
----------
free
free
free
free
free
free
free
free
free
free
10 rows selected.

The lesson from this post is the same as the lesson from my presentation. When you are evaluating a database, you need to go far deeper than just the bullet point claims at the high level. Because at the highest levels, every database looks the same.

But that simply sets you up for a rude awakening when you actually start to use that database in your production systems. It’s important that you go deeper and pick the database platform that best matches your business requirement needs.