Better Programming

Advice for programmers.

Member-only story

7 Database Concepts You Should Know About

Peter Christian Fraedrich
Better Programming
Published in
9 min readJul 1, 2021

--

Reddish-pink grid with black lines
Photo by Michael Dziedzic on Unsplash.

There’s a lot to know about databases. They’re complex mission-critical applications that sometimes require specialized subject matter experts to maintain them, but that doesn’t mean that they’re some kind of magic black box either. Databases are the backbone of our applications, and the more you learn about how they work, the better you will be at using them, writing applications against them, and troubleshooting problems when things inevitably go wrong.

So let's dive into seven things you should (probably) know about databases.

Note: Unless stated, I’m typically going to be talking about relational databases like PostgreSQL or MySQL and not NoSQL databases.

1. Databases Store Transactions — Not State (Kind Of)

Databases, or rather their internal architecture, aren’t very intuitive. You might think that a database is simply a data file or two and some code that manages connections and modifications to that data file. And you would be right to a certain extent. But really, at its core, a database is just a log file. This log file is a list of transactions that have been submitted to the database kept in the order in which they were submitted.

Everything else (the state of your tables, rows, schemas, etc.) is emergent from the accumulated changes recorded on this log. Each database engine stores this log a different way, but a section pseudo-log might look something like this:

...
2021-06-07 12:24:35.044513-4000 INSERT INTO 'People' "jeff" "wilson"
2021-06-07 12:25:33.232098-4000 INSERT INTO 'People' "mike" "lou"
2021-06-07 12:25:37.140013-4000 INSERT INTO 'People' "pat" "cat"
2021-06-07 12:26:11.030002-4000 INSERT INTO 'People' "mark" "wilson"
...

From this log, a database can rebuild its tables, databases, schemas, and everything else if the data files are somehow corrupted. But there’s one very important caveat: Once a transaction is committed or rolled back, it is removed from the log. The purpose of the log is to stage changes until a transaction is completed — not to act as a sort of backup mechanism. Somewhat small and…

--

--

Peter Christian Fraedrich
Peter Christian Fraedrich

Written by Peter Christian Fraedrich

Entrepreneur, software developer, writer, musician, amateur luthier, husband, dad. All opinions are my own.

Responses (10)