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 gathered 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.

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.

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

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 turing 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.

ACID Transactions

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.

Transactions

This is an operation that creates or

DDL

Object-Relational Mapping

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 use fully 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.

Normalisation

Database normalization is the process of organizing 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).

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

Before We Begin: Important 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).

Thinking about indexes

What are indexes for?

Let’s look at these topics

• Nulls
• Discriminator functions
• Indexes using functions
• Types of index

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 persented 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 because it complicates things.

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. The Orthogonal 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.

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>       |
+-------------+------------------+--------------+--------------+--------------+

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?

create_trap.sql

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)
);

populate_trap.sql

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
);

add-sales-targets.sql

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
    ),
    (
        (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
    ),
    (
        (SELECT customer_id FROM customers WHERE customer_name = 'Acme Industries'),
        DATE '2027-07-01',
        250.00
    );

naive_fan_query.sql

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;

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

Often nulls are not indexed - this often means 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.

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

There is a directory called ruby-examples in the repository, the Readme explains how to set things up so it will run. It’s also configured to give logging as it runs.

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]]

Binary search

This is one of the simplest ways of searching through large amounts of data. It assumes that the data has some kind of key that can be used to say whether or not you have found it and that the data is stored in order.

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 bit 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 a database system 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

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.