The main arguments in favor of the document data model are schema flexibility, better performance due to locality, and that for some applications it is closer to the object model used by the application. The relational model counters by providing better support for joins and many-to-one and many-to-many relationships. Let’s examine these arguments in more detail.
If the data in your application has a document-like structure (i.e., a tree of one-to-many relationships, where typically the entire tree is loaded at once), then it’s probably a good idea to use a document model. The relational technique of shredding—splitting a document-like structure into multiple tables (like positions, education, and contact_info in Figure 3-1)—can lead to cumbersome schemas and unnecessarily complicated application code.
The document model has limitations. For example, you cannot refer directly to a nested item within a document; instead, you need to say something like, “the second item in the list of positions for user 251.” If you need to reference nested items, a relational approach works better, since you can refer to any item directly by its ID.
Some applications allow the user to choose the order of items—for example, imagine a to-do list or issue tracker where the user can drag and drop tasks to reorder them. The document model supports such applications well, because the items (or their IDs) can simply be stored in a JSON array to determine their order. In relational databases there isn’t a standard way of representing such reorderable lists, and various tricks are used, such as sorting by an integer column (requiring renumbering when you insert into the middle), maintaining a linked list of IDs, or using fractional indexing.
Schema flexibility in the document model
Most document databases, and the JSON support in relational databases, do not enforce any schema on the data in documents. XML support in relational databases usually comes with optional schema validation. No schema means that arbitrary keys and values can be added to a document, and when reading, clients have no guarantees as to what fields the documents may contain.
Document databases are sometimes called schemaless, but that’s misleading as the code that reads the data usually assumes some kind of structure—that is, there is an implicit schema, but it is not enforced by the database. A more accurate term is schema-on-read (the structure of the data is implicit and interpreted only when the data is read), in contrast with schema-on-write (the traditional approach of relational databases, where the schema is explicit and the database ensures that all data conforms to it when the data is written).
Schema-on-read is similar to dynamic (runtime) type checking in programming languages, whereas schema-on-write is similar to static (compile-time) type checking. Just as the advocates of static and dynamic type checking have big debates about their relative merits, enforcement of schemas in databases is a contentious topic, and in general there’s no clear winner.
The difference between the approaches is particularly noticeable when an application wants to change the format of its data. For example, say you are currently storing each user’s full name in one field, and you instead want to store the first name and last name separately. In a document database, you would just start writing new documents with the new fields and have code in the application that handles the case when old documents are read. For example:
if (user && user.name && !user.first_name) {
// Documents written before Dec 8, 2023 don't have first_name
user.first_name = user.name.split(" ")[0];
}
The downside of this approach is that every part of your application that reads from the database now needs to deal with documents in old formats that may have been written a long time in the past. On the other hand, in a schema-on-write database, you would typically perform a migration along the lines of this:
ALTER TABLE users ADD COLUMN first_name text DEFAULT NULL;
UPDATE users SET first_name = split_part(name, ' ', 1); -- PostgreSQL
UPDATE users SET first_name = substring_index(name, ' ', 1); -- MySQL
In most relational databases, adding a column with a default value is fast and unproblematic, even on large tables. However, running the UPDATE statement is likely to be slow on a large table since every row needs to be rewritten, and other schema operations (such as changing the datatype of a column) also typically require the entire table to be copied.
Various tools exist to allow this type of schema change to be performed in the background without downtime, but performing such migrations on large databases remains operationally challenging. Complicated migrations can be avoided by adding the first_name column with a default value of NULL (which is fast) and filling it in at read time, as you would with a document database.
The schema-on-read approach is advantageous if the items in the collection don’t all have the same structure (i.e., the data is heterogeneous); for example:
-
There are many types of objects, and it is not practicable to put each type of object in its own table.
-
The structure of the data is determined by external systems over which you have no control and that may change at any time.
In situations like these, a schema may hurt more than it helps, and schemaless documents can be a much more natural data model. But when all records are expected to have the same structure, schemas are a useful mechanism for documenting and enforcing that structure. We will discuss schemas and schema evolution in more detail in the related discussion.
Data locality for reads and writes
A document is usually stored as a single continuous string, encoded as JSON, XML, or a binary variant thereof (such as MongoDB’s BSON). If your application often needs to access the entire document (e.g., to render it on a web page), this storage locality has a performance advantage. If data is split across multiple tables, as in Figure 3-1, multiple index lookups are required to retrieve it all, which may require more disk seeks and take more time.
The locality advantage applies only if you need large parts of the document at the same time. The database typically needs to load the entire document, which can be wasteful if you need to access only a small part of a large document. Furthermore, on updates to a document, the entire document usually needs to be rewritten. For these reasons, it is generally recommended that you keep documents fairly small and avoid frequent small updates.
However, storing related data together for locality is not limited to the document model. For example, Google’s Spanner database offers the same locality properties in a relational data model, by allowing the schema to declare that a table’s rows should be interleaved (nested) within a parent table. Oracle allows the same thing, using a feature called multi-table index cluster tables. The wide-column data model popularized by Google’s Bigtable and used, for example, in HBase and Accumulo has column families, which have a similar purpose of managing locality.
Query languages for documents
Another difference between a relational and a document database is the language or API that you use to query it. Most relational databases are queried using SQL, but document databases are more varied. Some allow only key-value access by primary key, while others also offer secondary indexes to query for values inside documents, and some provide rich query languages.
XML databases are often queried using XQuery and XPath, which are designed to allow complex queries, including joins across multiple documents, and format their results as XML. JSON Pointer and JSONPath provide an equivalent to XPath for JSON. MongoDB’s aggregation pipeline, whose $lookup operator for joins we saw in “Normalization, Denormalization, and Joins”, is an example of a query language for collections of JSON documents.
Let’s look at another example to get a feel for this language—this time an aggregation, which is especially needed for analytics. Imagine you are a marine biologist, and you add an observation record to your database every time you see animals in the ocean. Now you want to generate a report saying how many sharks you have sighted per month. In PostgreSQL, you might express that query like this:
SELECT date_trunc('month', observation_timestamp) AS observation_month, 1
sum(num_animals) AS total_animals
FROM observations
WHERE family = 'Sharks'
GROUP BY observation_month;
1
The date_trunc('month', observation_timestamp) function determines the calendar month containing timestamp and returns another timestamp representing the beginning of that month. In other words, the function rounds a timestamp down to the nearest month.
This query first filters the observations to show only species in the Sharks family, then groups the observations by the calendar month in which they occurred, and finally adds up the number of animals seen in all observations in that month. The same query can be expressed using MongoDB’s aggregation pipeline as follows:
db.observations.aggregate([
{ $match: { family: "Sharks" } },
{ $group: {
_id: {
year: { $year: "$observationTimestamp" },
month: { $month: "$observationTimestamp" }
},
totalAnimals: { $sum: "$numAnimals" }
} }
]);
The aggregation pipeline language is similar in expressiveness to a subset of SQL, but it uses a JSON-based syntax rather than SQL’s English sentence–style syntax. The difference is perhaps a matter of taste.
Convergence of document and relational databases
Document databases and relational databases started out as very different approaches to data management, but they have grown more similar over time. Relational databases added support for JSON types and query operators, and the ability to index properties inside documents. Some document databases (such as MongoDB, Couchbase, and RethinkDB) added support for joins, secondary indexes, and declarative query languages.
This convergence of the models is good news for application developers, because the relational model and the document model work best when you can combine both in the same database. Many document databases need relational-style references to other documents, and many relational databases have sections where schema flexibility is beneficial. Relational–document hybrids are a powerful combination.
Note
Codd’s original description of the relational model allowed something similar to JSON within a relational schema. He called it nonsimple domains. The idea was that a value in a row doesn’t have to be a primitive datatype like a number or a string; it can also be a nested relation (table), so you can have an arbitrarily nested tree structure as a value. This construction is comparable to the JSON and XML support that was added to SQL over 30 years later.