Introduction

Database - A guide for the perplexed

I was asked to introduce the topic of how Relational Databases work by the team I’m working with at the moment. As the material grew I decided it would be useful to gather it together and put it out on the public internet where everyone who might need a quick introduction to some of the most important concepts can easily find them. I’m Francis Fish and I’ve been writing code and all of the activities around it for since 1985. For my sins I’ve worked for companies like KPMG and Oracle. I switched to Ruby on Rails many years ago and don’t regret it, even though it’s probably cost me a few bob moving away from sheltering under the loving arms of big corporations and working for smaller companies.

This text is meant to give folks who work with databases every day a little grounding in what happens underneath and a brief introduction to the academic theory that underpins this important technology. If you work through the lens of something like Rails’ Active Record it may help you understand why you sometimes get results you don’t expect

I am a Ruby/Rails dev so the examples that aren’t SQL will be in Ruby.

Download the Ruby examples

This is a big topic. I’m gonna try and lay this out in a simple way

1. The theory that lies behind relational databases
2. How that theory relates to SQL.
3. How to think about how queries are built
4. How data is stored
5. Common pitfalls with nulls and other things
6. Non-relational approaches

This is quite a large undertaking, but I’m hope to do my best to make it easier for people to follow. by breaking it down into small enough pieces to be digestible.

This theory and its utility doesn’t seem to be taught any more, or at least not well. Most of us talk to databases through ORMs and it’s worth understanding what happens out of sight.

If you would like to print out a copy of this press the print icon at the top right.

Some key concepts

OLTP

This stands for On Line Transaction Processing, it’s a very old term for an application that serves a lot of customers. A typical commerce or support app would be an OLTP app.

Relational database

The theorist Edgar F Codd created relational algebra to model how data can be stored once and retrieved efficiently all the way back in 1970. SQL is a partial implementation of his ideas and is what we’ve ended up with now. Read the Wikipedia article if you want to know more about it. If your database is modelled using Codd’s relational algebra it is a relational database. It’s made of relations between tables, that’s it.

RDBMS

This is software that supports the creation and management of relational databases, Relational Database Management System. So things like parsing SQL and turning it into result sets, plus the automation of updating and deleting data are done transparently without you having to do more than send it the SQL to do what you ask.

Transactions

Take an operation that updates information in a database. Taking the information and saving it, making sure it is saved consistently, is a transaction.

Before the web-based systems we use today using client/server software architectures meant the forms you wrote would keep a connection to the database open. Until the end user had hit a button that committed the all of the changes made they would be operating inside a transaction that could also be rolled back. This was one of the fundamental things about these systems that now seems completely alien. Making many changes across different tables that could then be undone if necessary was part of how the architecture worked. This was also true if you were running a command line SQL interpreter, you would have to explicitly commit depending on which RDBMS you were using.

The web is stateless in essence. This is why we need to have things like cookies so we can identify who is logged in and what they are allowed to do. Keeping a persistent connection is far too expensive and difficult at web scale. It also meant that whatever you sent using a form was sent in one transaction, if there is a requirement to save many pieces of data then you may find that you ask the user to use a wizard style interface that allows them to build up all of the data between steps and then finally save it all in one transaction at the end of the processing.

ACID Transactions

This is what transactions need to support to work correctly.

Wikipedia.

• Atomicity: Each transaction is treated as a single unit that either succeeds or fails.
• Consistency: The result of any series of transactions goes from one consistent state to another, with each following any validation rules.
• Isolation: Each transaction takes place in its own space and transactions do not interfere with each other. They will wait until the previous one is complete.
• Durability: Once data is committed it will remain committed, even in the event of system failure.

Some database systems have what’s called eventual consistency where there is a window in which the data could be inconsistent for operational reasons.

DDL vs DML

You rarely see these terms now, but the first stands for Data Description Language, which is the SQL for creating and changing how data is stored, as in the structure of tables and indexes, plus things you can’t undo like truncation. By definition you can’t roll this back once done. DDL was historically left up to the database vendors, this means the creation of databases and tables often has slightly different syntax between vendors. For example, Oracle has table spaces and Postgres does not.

Data Manipulation Language is the part where you create, update and destroy the data itself. The DML side of SQL is subject to an international standards body, and tends to be consistent across vendors, or at least it is if you stick to the standard.

Object-Relational Mapping

This is covered in detail in Fun with Object-Relational Mapping

Full table scan

This is simply having to read every row in a table in order to match some filter criteria. For small tables it may not matter, but larger tables that lack an index or partial index may well cause performance problems. This term is I think an Oracle one, when you look at explained plans in Postgres it’s called sequential scan. In essence it means looking at all the data in a table instead of going in through an index and cutting down the amount of information you need to process to find what you’re looking for.

Logical vs Physical models

Logical models show what the relationships would look like if we had an RDBMS that was sophisticated enough to model many to many relationships. Physical models show how the model is actually implemented. These days we tend to just do the physical model.

Normalisation

For the relational model to work correctly the database structure must be normalised. What follows is a brief introduction to normalisation.

Database normalisation is the process of organising data in a relational database to reduce redundancy and improve data integrity. It involves breaking a large, flat table into smaller, related tables and defining relationships between them.

The progression from 1st Normal Form (1NF) to 5th Normal Form (5NF) represents a step-by-step refinement. To reach any given normal form, the database must first satisfy all the requirements of the preceding normal forms (e.g., to be in 3NF, a table must already be in 2NF and 1NF).

Most of the time 3NF is enough for a database that will work well. The other forms become important if you start having problems with cross validating relationships between columns, because they make it harder to create invalid data, but updating and managing the data is harder.

Here is a breakdown of the normal forms, from 1st to 5th.

Before We Begin: Important Concepts about Keys

• Superkey: Any column (or set of columns) that uniquely identifies a row.
• Candidate Key: A minimal superkey (no unnecessary columns). A table can have multiple candidate keys.
• Primary Key: The candidate key selected by the database designer to uniquely identify rows.
• Prime Attribute: A column that is part of any candidate key.
• Non-prime Attribute: A column that is not part of any candidate key.

1. First Normal Form (1NF)

The Rule: A table is in 1NF if every column contains only atomic (indivisible) values, and there are no repeating groups of columns.

• Problem: If a table stores multiple values in a single cell (like a comma-separated list of phone numbers) or has repeating columns (like Phone1, Phone2), it is hard to query, update, or index the data.
• Solution: Split multi-valued attributes into separate rows, or move repeating groups into a related table.

Non-1NF Example:

1NF Solution:

2. Second Normal Form (2NF)

The Rule: A table is in 2NF if it is in 1NF and no non-prime attribute is dependent on a proper subset of any candidate key. This is known as eliminating partial dependencies.

This rule only applies when the primary/candidate key is composite (consists of more than one column).

• Problem: If a table has a composite key, and a column depends on only part of that key, data redundancy occurs.
• Solution: Move the partially dependent columns and the part of the key they depend on into a new table.

1NF (but not 2NF) Example:

• Composite Primary Key: (StudentID, Course)
• Instructor depends on the entire key (who teaches Math to Student 101).
• StudentEmail depends only on StudentID (a subset of the composite key). This is a partial dependency.

2NF Solution:

Split into two tables so StudentEmail is no longer partially dependent.

Table A (Student Details): Primary Key is StudentID

Table B (Student Courses): Composite Primary Key is (StudentID, Course)

3. Third Normal Form (3NF)

The Rule: A table is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on the primary key.

Essentially, this means non-prime attributes must depend only on the primary key, and not on other non-prime attributes (i.e., “no transitivities”). A common way to remember this is: Every attribute must depend on the key, the whole key, and nothing but the key (so help me Codd).

• Problem: If column A determines column B, and column B determines column C, then column A transitively determines column C. If you change column B, you must update column C in multiple places.
• Solution: Break the transitive relationship out into a separate table.

2NF (but not 3NF) Example:

• Primary Key: StudentID
• Department depends on StudentID.
• DeptHead depends on Department, which is a non-prime attribute. This is a transitive dependency: StudentID (\rightarrow) Department (\rightarrow) DeptHead.

3NF Solution:

Split the table to isolate the department hierarchy.

Table A (Student Majors): Primary Key is StudentID

Table B (Departments): Primary Key is Department

(Note: There is also an intermediate normal form called Boyce-Codd Normal Form (BCNF), which is a slightly stronger version of 3NF. BCNF addresses anomalies that can occur when a table has multiple overlapping candidate keys.)

4. Fourth Normal Form (4NF)

The Rule: A table is in 4NF if it is in BCNF (or 3NF) and has no multi-valued dependencies (MVDs).

A multi-valued dependency occurs when the presence of one or more rows in a table implies the presence of certain other rows. This typically happens when a single entity has two or more independent, multi-valued relationships.

• Problem: Suppose a teacher can teach multiple subjects AND has multiple hobbies. If we try to store both independent lists in one table, we are forced to represent every combination of subject and hobby to keep the data consistent, leading to massive redundancy.
• Solution: Separate the independent multi-valued facts into their own tables.

3NF (but not 4NF) Example:

Because hobbies and subjects are completely independent of each other, we have to duplicate rows to represent all combinations.

4NF Solution:

Split into two separate tables to isolate the independent many-to-many relationships.

Table A (Teacher Subjects):

Table B (Teacher Hobbies):

5. Fifth Normal Form (5NF)

The Rule: A table is in 5NF (also known as Project-Join Normal Form) if it is in 4NF and cannot be decomposed into any number of smaller tables without introducing redundancy or losing information.

A table is in 5NF if every join dependency in the table is implied by the candidate keys.

• Problem: Sometimes, a three-way relationship exists where facts are ternary (involving three attributes), but there are physical/logical rules stating that if pair (A,B) and pair (B,C) and pair (A,C) exist, then the triple (A,B,C) must exist. If we do not design for this, we get logical update anomalies where we might insert an invalid combination, or fail to insert a required combination.
• Solution: Decompose the table into three separate tables representing the pairwise relationships. Rejoining them will reconstruct the original table exactly (hence “Project-Join”).

4NF (but not 5NF) Example:

Let’s trace Agents, Companies they represent, and Products they sell. Rule: If Agent Green represents Company X, and Company X makes Laptops, and Agent Green sells Laptops, then Agent Green MUST sell Laptops for Company X.

If we keep this in one table, we have redundant combinations:

5NF Solution:

To handle this strictly, we decompose the ternary relationship into three binary (2-column) tables:

Table 1 (Agent-Company):

Table 2 (Company-Product):

Table 3 (Agent-Product):

If we perform a join on all three of these tables, we will reconstruct the exact correct relationships of the original table without any risk of partial, mismatched, or impossible combinations being inserted key-by-key in a single unified table.

Summary Checklist

• 1NF: Atomic values only. No repeating columns.
• 2NF: 1NF + No partial dependencies (every non-key column depends on the whole primary key).
• 3NF: 2NF + No transitive dependencies (non-key columns do not depend on other non-key columns).
• 4NF: 3NF + No independent multi-valued dependencies.
• 5NF: 4NF + No join dependencies (reconstructing data from split tables doesn’t create spurious rows).

Surrogate keys

In most modern systems we usually add an id column to a table and use that as the primary key, even if there is a key we could use as per the relational model. This is a pragmatic thing that allows us to edit the actual key (which could be something like an email address for example) without having to update it everywhere it might be referenced. It does mean you should put unique indexes on the true keys to stop your database degenerating into a mess.

Codd’s algebra: select, project and join

We have normalised our data, now we want to retrieve it:

The algebra has three operations:

1. Select: Work out what data you need and what filters need to be applied
2. Project: Express that data as a set or collection of sets
3. Join: Join the sets together to create a result set

In the algebra the joining adds the columns when joining, say customers and customer orders. When you look at examples of the algebra in practice they often look like a dialect of SQL. The theory uses sets to do all the operations, and this underlies how SQL works, hence cartesian joins etc. For the full beauty go here.

Relations

We’ve talked about normalisation, which breaks our lumpy data into manageable tables with relationships between them. What are the different kinds of relations?

What kinds of join are there

There are:

• Inner join
• Outer left
• Outer right
• Union (all)

Note that other set operations like minus and intersect are also supported, which can sometimes help a lot when a query is difficult to frame as a join but you know how to get the data sets you’re looking for differences in.

Union has by definition unique rows and checking for and ensuring uniqueness can take a lot of time on large data sets. Specifying union all removes this constraint.

Implementing the relational model

We’ve gone quite deep into how you might store data and store only once. The theory was extremely useful for making sure that you hadn’t made any mistakes and the design of how you were going to put things into some kind of structured format was gonna be consistent and well thought out, having a basis in mathematics and set theory. The question arose after this of how you would take this theory and implement it so that you had a a real world way of using the data.

This is where we come to the invention of SQL, which was originally quite primitive and has only ever implemented some of the theory and logic behind it. SQL is a cut down and pragmatic implementation that a lot of academics get upset by, but it’s always been good enough to get the job done. We also have to address the elephant in the room which is computing in the 1960s and 70s was quite primitive, you had things like basic types of strings and numbers of various sorts but the most sophisticated items from the model where you would have a domain that defined something that might use a string, but in a particular way, or perhaps a number. Validation had to be done in your code the database itself couldn’t implement them. Policing the relationships was one thing, but granular validation on data itself was not available in very early systems and still isn’t in systems that don’t let you define types.

Eventually, SQL became a standard, at least in terms of the query language. Most vendors had syntax of their own that would create tables and indexes and so on, but that actually didn’t matter very much because most of the time you were writing queries. If you look at what came before SQL, there were systems like Codasyl where your database was defined as a fixed set of relationships with pointers to different places on disc. This meant that if you wanted to interrogate the database, you would have to get somebody to write you a COBOL program. The beauty of the relational model and it’s flawed but useful child, SQL, was that as long as you’d set your data up correctly so that it had no holes in it you could ask arbitrary questions.

It’s no accident that a lot of the early research into creating RDBMS was funded by organisations like the CIA. They wanted to be able to take quantities of relatively arbitrary data they held about individuals and then look for connections so they could build up a picture of who might not want their country robbed at gunpoint so they could do the CIA thing to them. Indeed, when I worked for Oracle in the early 2000s one of our customers had a whole system of not trading with unethical companies and it was touch and go whether we would be able to do business with them. To my lasting embarrassment I was politically quite naive and thought this an odd thing. Now, of course, trying to make sure you do business that’s ethical is a given in most circumstances that don’t involve the British government. You’d have the same problem with IBM or Microsoft to be honest, it’s the way our system works. Big companies are big because they aren’t ethical.

Another way of storing and retrieving data was simply to use plain files that had indexes on them. This is known as ISAM, which is an acronym for index sequential access method. These files had the data in them, and key fields in those files would have indexes defined which allowed you to quickly get to the places in the file where the data you wanted was stored. This concept is fundamental to what goes on inside an RDBMS. When we start turning theoretical things such as relations into SQL tables and constructing our database the RDBMS is in essence sitting on top of ISAM systems that will be used to physically implement each relation.

Running a query would be translated into pulling data from the tables and then constructing temporary tables that you can then start merging and filtering together, assembling it into your projected and joined result. So the RDBMS turns your SQL into operations on physical structures on disc that look like ISAM files underneath and then assembles that result for you. This is a very key idea: it’s how the abstractions are turned into something where you can usefully get data back. It’s also how you can break queries down to work out why you have performance problems. Once you’ve understood that the table is a bunch of data gathered together and that data has indexes on it to help you quickly find things like a particular customer, or order, or whatever arbitrary data you want, then tuning queries becomes obvious. You can start asking yourself questions like do I need to put an index in a particular place or does the order the tables are in this in the select from part of my query matter? To be fair, a decent optimiser will order the tables correctly anyway as long as the meta data needed to build the query is reasonably up-to-date. That kind of thing that used to matter in the early days. We’ll look at this in a lot more detail later in a section on how queries are constructed, this is a very high-level view.

A word about keys and modern ORM systems

It seems like a lifetime ago, but if you look at the model in its exact form, you notice that we don’t have a column called ID everywhere that has an integer or some arbitrary generated UUID string in it. We started doing this thing with IDs as a pragmatic response to the real world hitting purity of the theory.

In the real world you might have a dozen customers called Singh, they are different people, but it’s possible that you would be unable to create a compound key for your customer based on their name that uniquely identifies them. Including their home address would be an implementation nightmare every time the address changed. You can see how hard this might make life difficult. In this circumstance you’d end up with one row for all of those customers which wouldn’t work so the IDs you see everywhere are in fact something called surrogate keys but everyone seems to have forgotten this.

The other thing surrogate keys allowed you to do was take the actual keys identified in your analysis which might be strings or even combinations of several columns and be able to edit them to whatever you liked without having to cascade that change across the whole system where the references to the key are set. Sticking in IDs everywhere was a pragmatic response to this problem too.

This means that when identifying complicated compound keys in your analysis you should make sure there are at least unique indexes on those compound keys or you’re gonna end up with duplicate records. This is something everybody seems to have forgotten. It’s one of those avoidable messes that is now missed because everyone’s in such a hurry.

ORM is object relational mapping, it’s how we go from our object oriented systems like Ruby to creating SQL that brings us data back. In active record every table has a primary key which is a surrogate key, this is true even of tables that are joining tables that just consist of keys themselves. This means that you can sometimes have joining tables that have duplicates in which can cause all kinds of fun when you’re writing queries. It can do things like double the count of things because of the way the operators combine sets. We’ll get to this later, but putting unique index is on your compound keys in joining tables is also a very good idea, and will speed up the joining process too so there’s no need to stint on indexes in this case. It’s something you should check when physically implementing a database.

Holy databases, Batman!

Some useful ideas:

• Cartesian product. If you were to create a query with no joins it will give you back every row of every table put together in one big projection of all available columns. This is from set theory and if you want to bend your brain look at the Wikipedia entry.
• Fan Trap. If you have a table that has two (or more) 1:many relations and you were to try to get a sum from the many tables you will find that there might be an underlying cartesian product that creates a much bigger combined set before the values are summed and they will be wrong. For example you have customers, orders, order_lines and sales targets. If you were to write a naive query that joins and sums the tables together you will include the sales target data for every row you have in order lines, this will give you the wrong totals.
• Chasm Trap. Where the fan trap is too broad, a chasm trap is too narrow. For example, you want all customers that have a target set, but some of those customers won’t have orders. If you just join the tables together you will not see the ones with targets but no orders. This is what outer joins give you, all the filtered from one table and any data that matches the join conditions but with blank columns for the data that isn’t there.

Fun with Object-Relational Mapping

The way object oriented code models data and the relational model we’ve been discussing here are different. With active record we have what is a good compromise. We can use database rows just like they are Ruby objects, as long as we remember to tell them to save back to the database when we’ve finished.

What is Active Record?

Martin Fowler is a well known thinker in the enterprise software space and works for Thoughtworks. Many years ago he wrote the classic book Patterns of Enterprise Application Architecture that looks at how you build enterprise level systems. The book contains many useful patterns for this and Active Record is presented for wrapping and using databases. A good many of Fowler’s patterns ended up in Rails because its originators used his work as an inspiration. He has many other books about things like how to model financial systems and all sorts that are well worth getting hold of if you want to think about enterprise systems more broadly.

Active Record allows you to declaratively model the the relationships between the entities in a system while easily being able to query the attributes of any relation itself without having to write loads of SQL yourself.

Plain old Ruby Objects (PORO)

Let’s think about Customer/Order/OrderLine and just do it in Ruby:

class Customer  
  attr_accessor :name, :orders  
  
  def initialize(name, orders = [])  
    self.name = name  
    self.orders = orders  
  end  
end  
  
class Order  
  attr_accessor :date, :order_lines  
  
  def initialize(date, order_lines = [])  
    self.date = date  
    self.order_lines = order_lines  
  end  
end  
  
class OrderLine  
  attr_accessor :product_name, :quantity, :unit_price  
  
  def initialize( product_name, quantity, unit_price)  
    self.product_name = product_name  
    self.quantity = quantity  
    self.unit_price = unit_price  
  end  
end

Let’s play with this a little:

irb(main):035> require 'date'
irb(main):036> customer = Customer.new("cust")
irb(main):037> order  = Order.new(Date.today)
irb(main):038> customer.orders << order
irb(main):039> order_line = OrderLine.new("Prod 1", 1, 200)
irb(main):040> order.order_lines << order_line
=> [#<OrderLine:0x0000000122f7fe40 @product_name="Prod 1", @quantity=1, @unit_price=200>]
irb(main):041> customer
=> 
#<Customer:0x0000000122fd0020
 @name="cust",
 @orders=[#<Order:0x0000000122f7ff08 @date=#<Date: 2026-08-04 ((2461257j,0s,0n),+0s,2299161j)>, @order_lines=[#<OrderLine:0x0000000122f7fe40 @product_name="Prod 1", @quantity=1, @unit_price=200>]>]>
irb(main):042> 

Ignoring the complication of creating and referencing a separate product class.

Looking at the code above and ignoring the obvious need we might have to save and reload the data if using it in anger you can see the differences quite easily:

• The objects don’t have a database ID, so rendering routes like /customers/1 would mean we need to add something like an ID to make building an app possible
• There are no queries as such, just elements in arrays or the actual object itself
• Queries would have to be hand written using the methods active record reuses, for example methods like select and find from the Hash and Array classes.
• We don’t have any of the active record helpers like belongs_to, or any validation, and have to rely on what we can write ourselves by hand.

ORMs like Active Record allow us to use the enormous power of databases to create applications that can persist and find data while still having our nice, easy to use objects, that work in the web space. We don’t have to write SQL to do the simple stuff. The Ruby Active Record framework is designed to do the 95% simple things easily, and then let you just write some SQL for the stuff that’s hard to express using object notation.

There’s also some niceties like queries using attributes that are nil are automatically set up to use the is null operator when they get to the database.

Some folks argue that AR does too much, people drop business logic in the models where it does not belong and they become bloated and break the single responsibility principle. As usual there are no hard and fast rules. If something starts to hurt your process then think about it, otherwise leave it alone. Using patterns like services to put business processes in is a first step when this becomes too hard. If you want to get really funky the Hexagonal Architecture splits everything at the expense of being harder to set up initially, if you really want to layer things for some version of properly.

Binary search

This is one of the simplest ways of searching through large amounts of data. It assumes that the data is stored in order of the key you’re searching for.

In essence:

• Chop the data in half.
• If the key you’re looking for is greater than the one at the halfway point
  • Chop the data for the smaller valued keys in half and check again
• Otherwise
  • Chop the data for the larger valued keys
• If one of the keys matches you found what you’re looking for
• Keep doing this until you’ve found the key you’re looking for or neither half of the remaining data matches the keys you have.

The actual algorithm has some checks around end conditions and but this is the essence. Divide and conquer until you find what you’re looking for or it isn’t there.

There are a number of data structures like B-trees that let you take an arbitrary list of keys and create a binary searchable list that has pointers to the data you want with a minimum of effort.

Once you’ve scanned some data and created a B-tree with key values and where to find the data you have created an index that will allow you to quickly get to keyed data. There are many data structures that can be used for things like searching strings, or attributes of images. The principle is the same: work your way down a data structure looking for the key (or set of keys for the more sophisticated structures) until you either get a link to where the data is or the search fails.

Indexes

This is a simple idea:

• Create a data structure and store it somewhere that lets us go straight to where the information we want is, instead of having to plough through every record until we find the ones we want. The data structure emulates a binary search ordered list.
• This structure can also point to more than one row (say it’s an index on a foreign key) so you can quickly get to multiple rows of data.

There’s also a useful idea that’s used when looking for data, if you have some selection criteria but don’t need to retrieve the data, for example does a record exist that matches some joining criterion, you can perform an index probe that scans the index and says whether or not something exists with a particular key without having to go and fetch the data.

Indexes are created by reading every record in a file or table and placing a link to that record against the key in the data structure.

Partial indexes

Indexes can be on more than one column and filters can use the index to match multiple columns.

Unique indexes

This does what it says on the tin. Unique indexes will cause an error if you try and insert the same data twice in the indexed table. Adding unique indexes to an existing table can be challenging if there is duplicate data in the columns you now believe should be unique. Depending on which RDBMS you are using you can sometimes get it to create the index anyway, but not allow any new rows to be created that break the rule. Postgres does not allow this if my reading of the documentation is correct.

Query Discussion

Introduction

When an SQL query is turned into a result set I imagine the process is something like this:

• Break the query into other result sets or sets of result sets to break some more
• Generate the result sets and merge them together

Simple query

More complex query

This query is more complex but still quite a simple one. You can see how using indexes would speed this up, an index on the customer ID in the customers take you straight to the row data you need to construct the customer projection, similarly an index on customer id on sales targets would give you the rows you need to create the targets projection.

If you think about this some more you can also see how the order the intermediate projections are created matters. In the complex example above we could place the filter on sales targets instead of customers. Then we would be retrieving the single customer row for every sales target row, which is a lot more processing. Driving the query from the smaller table into the larger one is always a good idea. When you start to get large amounts of data you can see how putting the right indexes in the right place starts to become important too. One of the banes of well performing SQL is the full table scan on large tables, or a partial index match that actually makes more work than using the index would save.

Storage Discussion

A simple model of how data is stored

Let’s consider a customer record:

The underlying data becomes bit pattern that can be translated into an integer, say 64 bits or 8 bytes, and a string in Postgres that can be in theory of any length. How would we store this?

For simplicity’s sake we could say that, for now, a string could be up to an arbitrary 200 bytes, ignoring the complexities of UTF character encoding for the sake of this discussion.

This would give us a naive way of storing a customer record that would always take 8 bytes (for the integer) and 200 bytes for the string. We could then create a file that is sliced up into 208 byte chunks. This is similar to how data was stored in the old COBOL days, except that language didn’t use binary representations of numbers, but instead the concept of pictures that described how the number would be represented. Arithmetic was done in line and then written back to the format. These were simpler times and you could look at the files as raw data and work out what was in them.

If we are willing to live with this we could store the information for a customer. We might also add a marker record that says where the end of the active customer data is, so when we retrieve it we don’t search through a heap of empty records.

Updating our data

In this scheme updating data is relatively trivial:

1. Scan the customer file record by record until we find the ID we want
2. Replace what’s in the file at this point with our new data, as long as we have fixed length records this is trivially easy.

Deleting our data

This is harder, there are few things we could do:

1. Come up with an empty record marker (maybe ID being 0 )
2. Scan through the file until you find the ID of the row you want to delete, mark it as deleted.

Inserting data over time

Over time we may have a file that has several empty records, and not a lot of space left towards the end of it. So we can

• Logically split the file by adding a new one
• Shuffle all the data to the beginning of the file so we have more space
• Write insert code so that it goes looking for deleted records and replaces them with new ones
• This becomes much harder as soon as we start having records that can be any length, or new columns have been added to the table and we need to store them and maybe set up default values.

Searching for a particular record

A simple search that uses the ID.

• Naively we could read the file one record at a time until we find the record with the ID or hit the end of the file.
• If we didn’t shuffle records and knew how many were in the file we could do a Binary search this would allow us to take the integer value of the ID and then use it to work out where the record should be and then go see if it’s there. This means the records must have been inserted in the slot their ID maps to.
• We could use an index to let us find data wherever it appears.

What does an RDBMS give you?

After this discussion of a very simple way of storing data for a single table it becomes obvious that an RDBMS is a very sophisticated piece of software.

• Create tables
• Change data in tables
• Create indexes
• Update indexes when table data is updated
• Store arbitrary data in tables and manage variable length strings and other data that can vary
• Add and drop columns
• Clear up and manage unused space
• Allow many concurrent connections
• Backup and restore the data as needed

Full fan trap example

Setting up

To run this you need to access a Postgres database.

I opened up the psql command and created a new databse, then switched to it:

psql
> create database banana;
> \c banana

After this you can just call psql banana. You don’t have to call your database banana, by the way.

If you save the scripts here into files rather than pasting them the command \c filename.sql will let you run them.

Create the tables

DROP TABLE IF EXISTS sales_targets;
DROP TABLE IF EXISTS order_lines;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    customer_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name text NOT NULL
);

CREATE TABLE orders (
    order_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(customer_id),
    order_date date NOT NULL
);

CREATE TABLE order_lines (
    order_line_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id integer NOT NULL REFERENCES orders(order_id),
    product_name text NOT NULL,
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(10,2) NOT NULL CHECK (unit_price >= 0)
);

CREATE TABLE sales_targets (
    sales_target_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(customer_id),
    target_period date NOT NULL,
    target_amount numeric(12,2) NOT NULL CHECK (target_amount >= 0),
    UNIQUE (customer_id, target_period)
);

Add some data

INSERT INTO customers (customer_name)
VALUES ('Acme Industries');

INSERT INTO orders (customer_id, order_date)
VALUES (
    (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
    DATE '2026-07-01'
);

INSERT INTO order_lines (order_id, product_name, quantity, unit_price)
VALUES
    (
        (SELECT order_id FROM orders WHERE customer_id = (
            SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'
        ) AND order_date = DATE '2026-07-01'),
        'Widget A',
        2,
        19.99
    ),
    (
        (SELECT order_id FROM orders WHERE customer_id = (
            SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'
        ) AND order_date = DATE '2026-07-01'),
        'Widget B',
        1,
        49.50
    ),
    (
        (SELECT order_id FROM orders WHERE customer_id = (
            SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'
        ) AND order_date = DATE '2026-07-01'),
        'Service Plan',
        1,
        9.99
    );

INSERT INTO sales_targets (customer_id, target_period, target_amount)
VALUES (
    (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
    DATE '2026-07-01',
    250.00
);

Run the query

So if we were to naively ask for a sum of the targets and a sum of the orders we’d expect the targets to be 250. But they aren’t:

SELECT
    c.customer_name,
    SUM(ol.quantity * ol.unit_price) AS order_total,
    SUM(st.target_amount) AS sales_target_total
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.customer_id
JOIN order_lines AS ol
    ON ol.order_id = o.order_id
JOIN sales_targets AS st
    ON st.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;

  customer_name  | order_total | sales_target_total 
-----------------+-------------+--------------------
 Acme Industries |       99.47 |             750.00

So, can you work out how to fix this?

Chasm trap example

Setting up

Please first run the SQL in the fan trap example

Add another customer with a target

INSERT INTO customers (customer_name)
VALUES ('Acorn Industries');

Run the query

This time we want a list of all customers and their targets, whether or not they have one set up.

SELECT
    c.customer_name,
    SUM(st.target_amount) AS sales_target_total
FROM customers AS c
JOIN sales_targets AS st
    ON st.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;

  customer_name  | sales_target_total 
-----------------+--------------------
 Acme Industries |             250.00

How do we get Acorn to show?

Exploring the fan trap

Here’s a few things to try when looking at one of these problems.

The cartesian product

Remove the sum() calls and remove the group by from the SQL. What does this tell us?

SELECT
    c.customer_name,
    ol.quantity * ol.unit_price AS order_line_total,
    st.target_amount AS sales_target_line
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.customer_id
JOIN order_lines AS ol
    ON ol.order_id = o.order_id
JOIN sales_targets AS st
    ON st.customer_id = c.customer_id;

This gives us

+-----------------+------------------+-------------------+
| customer_name   | order_line_total | sales_target_line |
|-----------------+------------------+-------------------|
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 9.99             | 250.00            |
+-----------------+------------------+-------------------+

This means that the single value of the sales target is included 3 times, once for each order line. We have a cartesian join that will affect the sum of the values. If we had many targets, say for each month of the year, then the order line totals would be mutiplied by the number of targets. Note that for ease of understanding the July 2027 data wasn’t included because that would mean 2 month 7s and we’d have to complicate the queries beyond wha’ts needed for mere illustration of a point.

INSERT INTO sales_targets (customer_id, target_period, target_amount)
VALUES
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2026-08-01',
        250.00
    ),
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2026-09-01',
        250.00
    ),
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2026-10-01',
        250.00
    ),
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2026-11-01',
        250.00
    ),
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2026-12-01',
        250.00
    ),
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2027-01-01',
        250.00
    ),
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2027-02-01',
        250.00
    ),
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2027-03-01',
        250.00
    ),
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2027-04-01',
        250.00
    ),
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2027-05-01',
        250.00
    ),
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2027-06-01',
        250.00
    );

Now, if we re-run the query that does the totals

SELECT
     c.customer_name,
     SUM(ol.quantity * ol.unit_price) AS order_total,
     SUM(st.target_amount) AS sales_target_total
 FROM customers AS c
 JOIN orders AS o
     ON o.customer_id = c.customer_id
 JOIN order_lines AS ol
     ON ol.order_id = o.order_id
 JOIN sales_targets AS st
     ON st.customer_id = c.customer_id
 GROUP BY c.customer_id, c.customer_name;
 
+-----------------+-------------+--------------------+
| customer_name   | order_total | sales_target_total |
|-----------------+-------------+--------------------|
| Acme Industries | 1293.11     | 9750.00            |
+-----------------+-------------+--------------------+

The underlying data makes it even more obvious:

SELECT
     c.customer_name,
     ol.quantity * ol.unit_price AS order_line_total,
     st.target_amount AS sales_target_line
 FROM customers AS c
 JOIN orders AS o
     ON o.customer_id = c.customer_id
 JOIN order_lines AS ol
     ON ol.order_id = o.order_id
 JOIN sales_targets AS st
     ON st.customer_id = c.customer_id;
     
+-----------------+------------------+-------------------+
| customer_name   | order_line_total | sales_target_line |
|-----------------+------------------+-------------------|
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 39.98            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 49.50            | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
| Acme Industries | 9.99             | 250.00            |
+-----------------+------------------+-------------------+

How do we fix the fan trap?

We have 2 sets, one of which is the order lines, the other of which is the sales targets. So we need to split them into two different operations.

We can do this first for the sales targets. Let’s create ourselves a query that gets the sales targets by month:

SELECT
    st.customer_id,
    extract(month from st.target_period) as target_month,
    SUM(st.target_amount) AS target_total
FROM sales_targets AS st
GROUP BY customer_id, target_month;

+-------------+--------------+--------------+
| customer_id | target_month | target_total |
|-------------+--------------+--------------|
| 1           | 12           | 250.00       |
| 1           | 5            | 250.00       |
| 1           | 1            | 250.00       |
| 1           | 4            | 250.00       |
| 1           | 11           | 250.00       |
| 1           | 3            | 250.00       |
| 1           | 9            | 250.00       |
| 1           | 7            | 500.00       |
| 1           | 2            | 250.00       |
| 1           | 10           | 250.00       |
| 1           | 6            | 250.00       |
| 1           | 8            | 250.00       |
+-------------+--------------+--------------+

Now let’s get the orders and their month by month too:

SELECT
    o.customer_id,
	o.order_id,
    EXTRACT(month from o.order_date) as order_month,
    SUM(ol.quantity * ol.unit_price) AS order_totals
FROM orders AS o
JOIN order_lines AS ol
    ON ol.order_id = o.order_id
GROUP BY
    o.customer_id,
 	o.order_id,
    order_month;
    
+-------------+----------+-------------+------------------+
| customer_id | order_id | order_month | order_line_total |
|-------------+----------+-------------+------------------|
| 1           | 1        | 7           | 99.47            |
+-------------+----------+-------------+------------------+

We can turn these into inline tables and join them to the customer:

SELECT
    c.customer_id,
	c.customer_name,
    o_totals.order_month,
    o_totals.order_totals
FROM customers AS c
JOIN
(
	SELECT
	    o.customer_id,
		o.order_id,
	    EXTRACT(month from o.order_date) as order_month,
	    SUM(ol.quantity * ol.unit_price) AS order_totals
	FROM orders AS o
	JOIN order_lines AS ol
	    ON ol.order_id = o.order_id
	GROUP BY
	    o.customer_id,
	 	o.order_id,
	    order_month
) as o_totals
    ON o_totals.customer_id = c.customer_id;


+-------------+-----------------+-------------+--------------+
| customer_id | customer_name   | order_month | order_totals |
|-------------+-----------------+-------------+--------------|
| 1           | Acme Industries | 7           | 99.47        |
+-------------+-----------------+-------------+--------------+

We’ve removed the grouping and summing into the inline table. Now let’s add in the inline view for the sales targets as well:

SELECT
    c.customer_id,
	c.customer_name,
    o_totals.order_month,
    o_totals.order_totals,
    target_totals.target_total
FROM customers AS c
JOIN
(
	SELECT
	    o.customer_id,
	    EXTRACT(month from o.order_date) as order_month,
	    SUM(ol.quantity * ol.unit_price) AS order_totals
	FROM orders AS o
	JOIN order_lines AS ol
	    ON ol.order_id = o.order_id
	GROUP BY
	    o.customer_id,
	 	o.order_id,
	    order_month
) as o_totals
ON o_totals.customer_id = c.customer_id
JOIN (
	SELECT
	    st.customer_id,
	    extract(month from st.target_period) as target_month,
	    SUM(st.target_amount) AS target_total
	FROM sales_targets AS st
	GROUP BY customer_id, target_month
) as target_totals
ON target_totals.customer_id = c.customer_id;

+-------------+-----------------+-------------+--------------+--------------+
| customer_id | customer_name   | order_month | order_totals | target_total |
|-------------+-----------------+-------------+--------------+--------------|
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
| 1           | Acme Industries | 7           | 99.47        | 500.00       |
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
+-------------+-----------------+-------------+--------------+--------------+

This gives the same answer 12 times, once for each target month, we also need to join our inline views on target month:

SELECT
    c.customer_id,
	c.customer_name,
    o_totals.order_month,
    o_totals.order_totals,
    target_totals.target_total
FROM customers AS c
JOIN
(
	SELECT
	    o.customer_id,
		o.order_id,
	    EXTRACT(month from o.order_date) as order_month,
	    SUM(ol.quantity * ol.unit_price) AS order_totals
	FROM orders AS o
	JOIN order_lines AS ol
	    ON ol.order_id = o.order_id
	GROUP BY
	    o.customer_id,
	 	o.order_id,
	    order_month
) as o_totals
ON o_totals.customer_id = c.customer_id
JOIN (
	SELECT
	    st.customer_id,
	    extract(month from st.target_period) as target_month,
	    SUM(st.target_amount) AS target_total
	FROM sales_targets AS st
	GROUP BY customer_id, target_month
) as target_totals
ON target_totals.customer_id = c.customer_id
WHERE target_totals.target_month = o_totals.order_month;

+-------------+-----------------+-------------+--------------+--------------+
| customer_id | customer_name   | order_month | order_totals | target_total |
|-------------+-----------------+-------------+--------------+--------------|
| 1           | Acme Industries | 7           | 99.47        | 500.00       |
+-------------+-----------------+-------------+--------------+--------------+

We’ve now moved from a fan trap to a chasm trap. We have the correct values but now can’t see the targets for the rest of the year. Breaking this down we have 3 sets:

1. Customer
2. Sales targets
3. Order totals

We can fix this with an outer join. It will pull everything from the joined table into the results. They come in two flavours, left and right. Left includes all of the data in the table driving the query, in our case customers, however we want all of the target data, so we want the right join. Drawing a Venn diagram often helps you resolve this:

SELECT
    c.customer_id,
    c.customer_name,
    o_totals.order_month,
    o_totals.order_totals,
    target_totals.target_total
FROM customers AS c
JOIN
(
	SELECT
	    o.customer_id,
	    EXTRACT(month from o.order_date) as order_month,
	    SUM(ol.quantity * ol.unit_price) AS order_totals
	FROM orders AS o
	JOIN order_lines AS ol
	    ON ol.order_id = o.order_id
	GROUP BY
	    o.customer_id,
	 	o.order_id,
	    order_month
) as o_totals
ON o_totals.customer_id = c.customer_id
LEFT JOIN (
	SELECT
	    st.customer_id,
	    extract(month from st.target_period) as target_month,
	    SUM(st.target_amount) AS target_total
	FROM sales_targets AS st
	GROUP BY customer_id, target_month
) as target_totals
ON target_totals.customer_id = c.customer_id
AND target_totals.target_month = o_totals.order_month;

+-------------+-----------------+-------------+--------------+--------------+
| customer_id | customer_name   | order_month | order_totals | target_total |
|-------------+-----------------+-------------+--------------+--------------|
| 1           | Acme Industries | 7           | 99.47        | 250.00       |
| <null>      | <null>          | <null>      | <null>       | 250.00       |
| <null>      | <null>          | <null>      | <null>       | 250.00       |
| <null>      | <null>          | <null>      | <null>       | 250.00       |
| <null>      | <null>          | <null>      | <null>       | 250.00       |
| <null>      | <null>          | <null>      | <null>       | 250.00       |
| <null>      | <null>          | <null>      | <null>       | 250.00       |
| <null>      | <null>          | <null>      | <null>       | 250.00       |
| <null>      | <null>          | <null>      | <null>       | 250.00       |
| <null>      | <null>          | <null>      | <null>       | 250.00       |
| <null>      | <null>          | <null>      | <null>       | 250.00       |
| <null>      | <null>          | <null>      | <null>       | 250.00       |
+-------------+-----------------+-------------+--------------+--------------+

Notice also that we had to change the where at the end of the query to an and so that it was included in the outer join. Leaving it as a where would have made the outer join not work. It needed to outer join to both of the other sets.

NOTE - this query isn’t quite right cos we should be seeing cust number and name in the outer join. Might need to create another subquery? See if you can make it work for you.

SELECT
    c_target_totals.customer_id,
	c_target_totals.customer_name,
	o_totals.order_totals,
    c_target_totals.target_month,
    c_target_totals.target_total
FROM ( 
SELECT
    c.customer_id,
	c.customer_name,
    target_totals.target_month,
    target_totals.target_total
FROM customers AS c
LEFT JOIN (
	SELECT
	    st.customer_id,
	    extract(month from st.target_period) as target_month,
	    SUM(st.target_amount) AS target_total
	FROM sales_targets AS st
	GROUP BY customer_id, target_month
) as target_totals
ON target_totals.customer_id = c.customer_id
) as c_target_totals
LEFT JOIN
(
	SELECT
	    o.customer_id,
	    EXTRACT(month from o.order_date) as order_month,
	    SUM(ol.quantity * ol.unit_price) AS order_totals
	FROM orders AS o
	JOIN order_lines AS ol
	    ON ol.order_id = o.order_id
	GROUP BY
	    o.customer_id,
	    order_month
) as o_totals
ON o_totals.customer_id = c_target_totals.customer_id
and o_totals.order_month = c_target_totals.target_month
order by target_month, customer_id
;

+-------------+------------------+--------------+--------------+--------------+
| customer_id | customer_name    | order_totals | target_month | target_total |
|-------------+------------------+--------------+--------------+--------------|
| 1           | Acme Industries  | <null>       | 1            | 250.00       |
| 1           | Acme Industries  | <null>       | 2            | 250.00       |
| 1           | Acme Industries  | <null>       | 3            | 250.00       |
| 1           | Acme Industries  | <null>       | 4            | 250.00       |
| 1           | Acme Industries  | <null>       | 5            | 250.00       |
| 1           | Acme Industries  | <null>       | 6            | 250.00       |
| 1           | Acme Industries  | 99.47        | 7            | 250.00       |
| 1           | Acme Industries  | <null>       | 8            | 250.00       |
| 1           | Acme Industries  | <null>       | 9            | 250.00       |
| 1           | Acme Industries  | <null>       | 10           | 250.00       |
| 1           | Acme Industries  | <null>       | 11           | 250.00       |
| 1           | Acme Industries  | <null>       | 12           | 250.00       |
| 2           | Acorn Industries | <null>       | <null>       | <null>       |
+-------------+------------------+--------------+--------------+--------------+

The problem with nulls

Boolean logic

Everyone who has worked with SQL has been bitten by nulls. We have our Boolean logic, where statements resolve to either true or false. A null in SQL means we don’t know. The problem is there are many different types of not knowing:

• We may have a place holder for a date or value that will come in the future
• We may have optional data
• We may have added things that matter operationally now but don’t know what to put in old records
• I’m sure you can think of some more.

All of these things are modelled with null. Null is a state, not a value. Any operation done with a null returns null, which equates to false in Boolean operations and null in column selections.

It also means, weirdly, that nulls have a type, unlike nil in Object-Oriented programming.

This means our nice clean boolean logic stops being true or false, and becomes true / false / not yet known / optional / things changed / something else we haven’t thought of.

This is why SQL does not support equality between nulls, unknown does not equate to unknown, any operation with unknown is itself unknown, and will be treated as false.

> select null = null ;
+----------+
| ?column? |
|----------|
| <null>   |
+----------+
> select null != null ;
+----------+
| ?column? |
|----------|
| <null>   |
+----------+

This is why SQL has is (not) null as an operator, you have to explicitly say you’re working with a null and what you want to do with it, and then add that to your boolean statements.

You move away from the pleasant ease of using simple true or false and instead have many results from any number of possible reasons. Suddenly there may be any number of meanings to the use of the null. C J Date, author of the classic Introduction to Database Systems, hates nulls because they break Boolean logic so badly.

An example of how nulls can cause problems

Let’s give ourselves a table of People with first_name, last_name and allow last name to be null:

CREATE TABLE people (
    person_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name text NOT NULL,
    last_name text
);

Now let’s whack some data into it:

INSERT INTO people (first_name, last_name)
VALUES 
(
  'First', 'First'
),
(
  'Second', 'Second'
),
(
  'Unknown', NULL
)
;

Now let’s look at some joining queries:

select p1.first_name p1f, p2.first_name p2f
from people p1, people p2
where p1.last_name != p2.last_name
and p1.last_name != 'Second'
;
+-------+--------+
| p1f   | p2f    |
|-------+--------|
| First | Second |
+-------+--------+
SELECT 1

So where is the third (contrived) record?

select p1.first_name p1f, p2.first_name p2f
 from people p1, people p2
 where coalesce(p1.last_name,'ZZZ') != p2.last_name
 and coalesce(p1.last_name,'ZZZ') != 'Second'
 ;
+---------+--------+
| p1f     | p2f    |
|---------+--------|
| Unknown | First  |
| First   | Second |
| Unknown | Second |
|---------+--------|

This is horrible, and very difficult to remember to do for columns that you don’t know are null. Many database designers completely avoid using nulls because it’s so fraught with not getting back what you expect.

Also see:

select * from people where last_name in ('First', null);
+-----------+------------+-----------+
| person_id | first_name | last_name |
|-----------+------------+-----------|
| 1         | First      | First     |
+-----------+------------+-----------+

and

select first_name || ' ' || last_name from people;
+---------------+
| ?column?      |
|---------------|
| First First   |
| Second Second |
| <null>        |
+---------------+

Any operation with null is null.

Sorting nulls

select last_name from people order by last_name;
+-----------+
| last_name |
|-----------|
| First     |
| Second    |
| <null>    |
+-----------+

Ways of avoiding using nulls

One of the strategies proposed is to use 6th Normal Form, which turns attributes that can be nulls into tables in their own right with IDs that point to values. If you want to read up on this then look here (PDF) - in essence nullable columns become references to tables with only keys and values in them. You create a row in that table that the unknown value maps to. It feels very convoluted, and is the database equivalent of the null object pattern.

Some systems use special values or markers to indicate an unknown state, for example a fixed date far in the future for dates that are unknown, or using -1 when an optional ID field is required. This has parallels with some programming practices, it is very common to return -1 when you’ve been looking for the position of something in a zero-indexed array.

Some databases, for example Oracle, store empty strings as nulls and you can’t check to see if a string’s length is greater than zero, because the test will return null.

Indexes

Postgres has a number of options about how to treat nulls in indexes, this can mean that as soon as you do anything with nulls you can end up with full table scans. Sometimes you might find that a query that looks ok to you, creating a small result set for driving another query through an index, suddenly misbehaves because the optimiser gets confused by a null operation. These days, you can get around it by creating indexes that use functions, and those functions can use coalesce to force the row to be indexed.

What are N plus 1 queries

This happens a lot in Rails and other ORMs. Conceptually we have a master object, something like an Order (or Project) and it has other data hanging from it. Instead of pulling the data in one hit from the database we will get our list of orders, and then start pulling all the detail records for it one row at a time. You’re doing one query to get the list of orders, and then for each row another to get the details, hence N + 1, it should perhaps be 1 + N to be pedantic about it - as in one query gets you N queries.

This doesn’t sound too bad, the detail queries are probably using indexes and relatively quick to run, but you might have hundreds of them.

Think about how the query result set turned into something you can access as an object:

In this example also think about how an order might also have many products that need rendering, then you have (N + 1 ) + 1 queries to the database.

Marshalling is taking the data and turning it into objects, it’s also sometimes called serialisation, turning the data into a stream that can be transmitted from the database to the Rails stack over an API using a socket.

It’s easy to create an N + 1 in Rails, because using Active Record you just pull the main query, and the subqueries happen when you access the child data. The code looks fine and renders ok. If you fix them the rendering code looks the same, but the data comes from the database in one hit. You may also need to use outer joins to get things. If data is missing in a more complex query you might find yourself not seeing rows that you should.

Fixing them

In Rails there’s a gem called Bullet that will warn you if there seems to be an N + 1 happening in a view render. You can also ask a friendly AI to identify some, but sometimes they get confused and call something an N + 1 when it is not. Even without Bullet you can usually see issues like this in the development log. You’ll see the query for customers, and then a pile of queries once for each order.

Let the database do as much work as possible and get all of the data you need.

1. Use includes and joins to fetch the child data along with the order data
2. Marshall all of the data you need to render in one hit
3. No more back and forth to the database

It sounds trivial, and it kind of is, but you need to look really carefully at the nesting of queries to work out when it’s needed.

There is a zip file of ruby-examples here, the Readme explains how to set things up so it will run. It’s also configured to give logging as it runs.

Let’s explore the customers and orders tables some more:

First, let’s add some customers and orders

3.times do |i|
  customer = Customer.create!(customer_name: "Customer #{i + 1}")
  2.times do |j|
    Order.create!(customer: customer, order_date: Date.new(2026, 8, j + 1))
  end
end

Now trigger the N + 1

customers = Customer.all
customers.each do |customer|
  puts "#{customer.customer_name}: #{customer.orders.count}"
end

This gives us a log like this:

D, [2026-08-03T13:43:35.575054 #52735] DEBUG -- :   Customer Load (0.6ms)  SELECT "customers".* FROM "customers"
D, [2026-08-03T13:43:35.576622 #52735] DEBUG -- :   Order Count (0.8ms)  SELECT COUNT(*) FROM "orders" WHERE "orders"."customer_id" = $1  [["customer_id", 1]]
Acme Industries: 1
D, [2026-08-03T13:43:35.577016 #52735] DEBUG -- :   Order Count (0.2ms)  SELECT COUNT(*) FROM "orders" WHERE "orders"."customer_id" = $1  [["customer_id", 2]]
Customer 1: 2
D, [2026-08-03T13:43:35.577349 #52735] DEBUG -- :   Order Count (0.2ms)  SELECT COUNT(*) FROM "orders" WHERE "orders"."customer_id" = $1  [["customer_id", 3]]
Customer 2: 2
D, [2026-08-03T13:43:35.577631 #52735] DEBUG -- :   Order Count (0.2ms)  SELECT COUNT(*) FROM "orders" WHERE "orders"."customer_id" = $1  [["customer_id", 4]]
Customer 3: 2

As you can see, the customers are loaded first, and then each order one at a time.

Now, let’s stop the N + 1 by including the orders, note that we’re using size instead of count because we’ve retrieved the array and don’t want to trigger a count in Active Record:

customers = Customer.includes(:orders).to_a
customers.each do |customer|
  puts "#{customer.customer_name}: #{customer.orders.size}"
end

When interleaved with the log output it looks like this:

irb(main):001> customers = Customer.includes(:orders)
D, [2026-08-03T13:51:28.614437 #77991] DEBUG -- :   Customer Load (0.4ms)  SELECT "customers".* FROM "customers" /* loading for pp */ LIMIT $1  [["LIMIT", 11]]
D, [2026-08-03T13:51:28.632245 #77991] DEBUG -- :   Order Load (0.5ms)  SELECT "orders".* FROM "orders" WHERE "orders"."customer_id" IN ($1, $2, $3, $4)  [["customer_id", 1], ["customer_id", 2], ["customer_id", 3], ["customer_id", 4]]
=> [#<Customer:0x0000000125d6d550 customer_id: 1, customer_name: "Acme Industries">, #<Customer:0x0000000125c48490 customer_id: 2, customer_name: "Customer 1">, #<Customer:0x0000000125c48350 customer_id: 3, customer_name: "Customer 2">, #<Customer:0x0000000125c48210 customer_id: 4, customer_name: "Customer 3">]
irb(main):002* customers.each do |customer|
irb(main):003*   puts "#{customer.customer_name}: #{customer.orders.size}"
irb(main):004> end
D, [2026-08-03T13:51:31.532422 #77991] DEBUG -- :   Customer Load (0.6ms)  SELECT "customers".* FROM "customers"
D, [2026-08-03T13:51:31.533314 #77991] DEBUG -- :   Order Load (0.3ms)  SELECT "orders".* FROM "orders" WHERE "orders"."customer_id" IN ($1, $2, $3, $4)  [["customer_id", 1], ["customer_id", 2], ["customer_id", 3], ["customer_id", 4]]
Acme Industries: 1
Customer 1: 2
Customer 2: 2
Customer 3: 2

Now you can see that ActiveRecord pulls all of the customers and all the related orders before processing. This means that the database (and the Postgres API) isn’t being hammered by lots of small requests.

Note that you can also stop N + 1 + 1 (whatever that’s called) with constructs like

Customer.includes(:orders, { orders: :order_lines } )
D, [2026-08-03T14:50:20.585853 #77991] DEBUG -- :   Customer Load (0.5ms)  SELECT "customers".* FROM "customers" /* loading for pp */ LIMIT $1  [["LIMIT", 11]]
D, [2026-08-03T14:50:20.588160 #77991] DEBUG -- :   Order Load (0.4ms)  SELECT "orders".* FROM "orders" WHERE "orders"."customer_id" IN ($1, $2, $3, $4)  [["customer_id", 1], ["customer_id", 2], ["customer_id", 3], ["customer_id", 4]]
D, [2026-08-03T14:50:20.595203 #77991] DEBUG -- :   OrderLine Load (0.9ms)  SELECT "order_lines".* FROM "order_lines" WHERE "order_lines"."order_id" IN ($1, $2, $3, $4, $5, $6, $7)  [["order_id", 1], ["order_id", 2], ["order_id", 3], ["order_id", 4], ["order_id", 5], ["order_id", 6], ["order_id", 7]]

How to use explain plan

This is one of the most powerful tools you have for understanding why a query is slow or not returning the results you think it should. It’s quite simple to use:

explain select * from customers where customer_name = 'Bob';
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on customers  (cost=0.00..25.88 rows=6 width=36)
   Filter: (customer_name = 'Bob'::text)
(2 rows)

It will do a sequential scan on the table and create a result set with all the rows that match the filter. Easy enough.

banana=# create index try_me on customers(customer_name);
CREATE INDEX
banana=# explain select * from customers where customer_name = 'Bob';
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on customers  (cost=0.00..1.05 rows=1 width=36)
   Filter: (customer_name = 'Bob'::text)
(2 rows)

Note that Postgres knows the table is so small an index scan is unnecessary. Let’s add a few more rows.

banana=# INSERT INTO customers (customer_name)
SELECT
  CASE
    WHEN n <= 20 THEN 'Bob'
    ELSE 'Customer ' || n
  END
FROM generate_series(1, 1000) AS n;
banana=# vacuum;
banana=# explain select * from customers where customer_name = 'Bob';                                                                                                                          QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on customers  (cost=4.43..11.68 rows=20 width=16)
   Recheck Cond: (customer_name = 'Bob'::text)
   ->  Bitmap Index Scan on try_me  (cost=0.00..4.43 rows=20 width=0)
         Index Cond: (customer_name = 'Bob'::text)
(4 rows)

Note we had do use the vacuum Postgres command to make it re-analyse the database so it knows there are enough rows in the customers table to justify using the index. I told this script to create some rows with the customer name of Bob but in fact it doesn’t matter for explain because the query is not run.

You can see that it will now use the index to home in on the result set it needs. For more details of explain read the Postgres documentation.

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.

Cacheing

Note: This doesn’t really sit directly in a database book but it’s too small a topic to be turned into a book in its own right.

What is a cache?

The Oxford English Dictionary (OED) defines a cache like this:

Noun: a collection of items of the same type stored in a hidden or inaccessible place:
an arms cache
a cache of gold coins

More specifically for computing:

an auxilliary memory from which high-speed retrieval is possible

It goes on to explain why you might want to do this, saying store away for future use.

Why cache things?

The last part of the dictionary definition gives us a clue here. If something is difficult or time-consuming to create then why go through the creation process again? Instead just keep a copy of the thing. In other cases, if we need something over and over again, why not just create it once and then reuse it?

The problem with cacheing

Doing things once and keeping hold of the result works really well, and can speed things up no end. What happens when the underlying data changes, though? You have stale data and may make bad decisions or just not be able to see what you expect to see.

There needs to be a process that lets you invalidate the cache. This means it will be reconstructed when you next ask for the information.

Most cacheing technology also tends to use a store that forgets things after a given amount of time. This results in the re-creation of cached objects every so often anyway. It’s a way of making sure things stay current even if there is no direct invalidation. Sometimes for performance reasons tolerating a degree of staleness is a way of optimising response times. As usual there’s a trade off.

The Russian doll problem

You may be familiar with the Matryoshka doll which is a set of dolls that each contain smaller and smaller dolls beloved of tourists that go to Russia. There is a similar problem with cacheing. You may have something like an HTML view that is comprised of smaller view fragments. Those fragments can themselves be comprised of smaller fragments, and so on. Imagine you have cached all of these fragments in your data store.

Let’s think this through:

1. The data behind one of the fragments changes.
2. A very expensive way of solving this is to machine gun all of the cached objects and reconstruct the whole view from scratch - we don’t want to do this if 90% of what we will send back is the same.
3. Rails has a cacheing system uses a key usually based on the last updated time of the object being rendered.
4. As the view is reconstructed the time stamped keys are checked against what has been stored.
5. If it has changed that identifies that cached object needs re-rendering.
6. As the view is rendered it only re-creates and re-stores (as in stores again) the pieces that have changed. So the 90% of the fragments that are unchanged are not re-rendered.

Storing and invalidating cached objects is one of the hardest problems in computer science because identifying changes without having to re-load all the data is hard. Russian doll cacheing is a pragmatic approach to this and it works well. It has been in Rails since Rails 5.

Other view cacheing strategies

In the days when the web wasn’t as fast as it is now many sites wanted to show their customers something straight away inside the magic 100 ms window. Showing the sexy page with all of the dynamic menus and stuff took a long time to arrive at the browser. So instead they would show a page full of placeholders that populated itself using Javascript after it arrived. This eventually mutated into the single page app that ironically these days locks up and takes 10 minutes to download all its Javascript libraries and images. Doing a simple version of this can still work really well. Rails’ turbo library is an attempt to do this transparently and is turned on by default in new apps.

I have a memory of a building society that had a very clever Flash animation showing how using their services meant you didn’t have to worry about security. It had an animation of some kind of private eye character who had heavy footsteps. You would have to wait several minutes for the animation to load while the heavy footsteps kept coming out of the speakers of your laptop. After it loaded you could close it and interact with the site proper. This is obviously the opposite of this strategy but it was in the days when not very many folks were on the internet so probably forgivable, at least they were trying something to make them stand out, albeit perhaps not how they intended.

APIs

Another win can be to turn the arguments you’re sent for a query API into some kind of key or hash and store the results for a while in a cache store like Redis. If you get a hit for your hash in Redis return that instead. Again, the store needs to be set to forget things after a while, but it can usefully speed up response times if used carefully.

Where do we store our cached stuff?

There are two main places to do this:

1. Files. Store the rendered view fragments in a temporary directory using the cache keys a file names. This works well if you have one server, unless you’re willing to have the temporary directory on every node in your front end and build things on demand per server. This could work.
2. Key value stores. Instead use a key-value system like Redis or perhaps CouchDB. This allows you to store any data you like against a key you specify. The service provided means you can share it across multiple servers. There will of course be overhead using such a service, so it has to be quicker than regenerating the things you are cacheing. Redis can also do things like set a lifetime for a given item in the store, using files means you would have to do this yourself or use a system that does it for you.

Let’s look at some code

Here we’re just talking about cacheing view rendering. We’re going to look at the use of the view cache helper. If you search for the work cache in the Rails API there it appears in a lot of places.

At its most basic code looks like this:

<customer>
  <h1><%= customer.name %></h1>	
    <ul>
      <% customer.orders.each do |order| %>
        <% cache(order) do %>
          <%= render order %>
        <% end %>
     <% end %>
  </ul>
</customer>

So what are we doing here? The cache helper creates a key based on the order’s class, key, and last updated date. The first time it is called it creates an entry in the cache store by calling the block, for subsequent calls it checks the cache key and returns what’s stored if they match, otherwise it calls the block again and creates a new entry for the new key. This is also why things need to eventually disappear from the cache, there’s no point in keeping data for invalidated keys around forever, and tracking when something was last accessed is much harder work that just letting them expire over time.

This code may have a hidden problem though.

Say within the order rendering we have order lines being rendered:

<order>
  <h1>Order on: <%= order.date %></h1>
  <% order.order_lines.each do |line| %>
	<%= render line %>
  <% end %>
</order>

We haven’t cached each line (although we could have). So what is the problem?

If the order line changes it won’t invalidate the cached order view. We also need to change the order line model:

class OrderLine
  belongs_to :order, touch: true
end

If an order line changes it will touch the updated at of its parent order and invalidate the view cache when its next rendered.

You can also just call touch on any ActiveRecord object and it will do this, so in situations where the automatic handling of touch with this belongs_to aren’t enough you can still make it happen directly.

This also means that if we were to cache the order lines only the one that changed would be regenerated, reusing the cached order lines where needed.