When I need data persistence at my software job, I have a menu of managed databases I can choose from. The two options I most frequently reach for are PostgreSQL - a relational database, in the "SQL" family - and MongoDB, a document-oriented database.
The first consideration for selection is the nature of my data and the queries I intend on running. If the task is to model relationships between entities, a relational database is a natural choice. On the other hand, if I'm persisting arbitrarily complicated but unrelated documents, the document store is likely the better choice.
Feature Parity
The major databases have a tendency to converge towards a similar feature set, even between databases with different paradigms. PostgreSQL, for example, has supported JSON columns for some time, enabling a column to store minimally structured data, similar to document databases.
Meanwhile, MongoDB has been adding transactional support, a feature more associated with traditional relational databases.
This convergence can tempt engineers to try to avoid making trade-offs. Wouldn't it be grand if we had the solid ACID semantics of PostgreSQL with the flexible data definitions of MongoDB?
Every so often, a well-intentioned proposal such as this crosses my desk:
create table document_store (
id bigint generated always as identity primary key,
document jsonb not null
);This table resembles a document collection in MongoDB. It has:
- A unique document identifier (
id) with an implied index, similar to the_idproperty in MongoDB. - An arbitrary payload of JSON, comparable (for most typical purposes) to the BSON stored by MongoDB.
PostgreSQL offers a deep toolbox of JSON
functions and operators. For example, you can update specific keys
within your JSON using jsonb_set, similar to the
$set update operator in MongoDB. But we're still using
PostgreSQL; all our favorite transaction mechanisms are still available
and work like they always have. Truly, the best of all worlds?
No Free Lunch
Whenever it seems like we can bypass a classic computing trade-off, it's important to take a deeper look.
Let's start with a brief overview of PostgreSQL's means of updating a row. Like many databases which serve multiple users concurrently, PostgreSQL employs multi-version concurrency control, or MVCC, to avoid mutations of data from interfering with concurrent reads on the same data. In short, when a row is updated, the row is in fact copied, and the database has appropriate bookkeeping to ensure a given query only interacts with the copy which existed when that query started (this is the isolation property, the "I" in ACID). Without such a mechanism, writers would have to wait for all readers to be done reading, or else a reader could have their data mutated by a writer in the middle of a read.
Creating a copy on each mutation neatly isolates reading and writing concerns. The price, of course, is the storage used by keeping an arbitrary number of copies of each row. To mitigate this, PostgreSQL employs a process called "vacuum", which will mark the copies no longer used by a query ("dead tuples") as eligible to have their storage space used for other purposes.
If we have rows which might be "large," over a couple kiB, PostgreSQL
has a storage area called "The Oversized-Attribute Storage Technique" or
"TOAST." The "big" columns (any column with the EXTENDED
storage strategy, the default for jsonb) will undergo
compression, and if still over ~2KiB, will get moved off to separate
TOAST-specific storage. Instead of the arbitrarily large blob, we have a
pointer to the TOAST item in the main heap.
So long as the content of those columns doesn't change, the new copies of each row made on modification will continue to point to the same TOAST item. This way, we can update all the "little" columns over and over without creating new copies of our "big" columns. But the moment we change a single byte of those "big" columns, we have a copy of the "big" column.
Let's revisit our little PostgreSQL table. If we have some non-trivial JSON stored in a given row, and then make any modification to that JSON, such as updating a single key within it, we have the following process:
- Retrieve the complete JSON content from disk (TOAST storage) and load it into memory.
- Apply the change to the JSON.
- Create a new TOAST item with that modification.
- Copy the original row, pointing the new copy to the new TOAST item.
In short, we're rewriting the entire JSON document upon each modification, regardless of the size of the update being applied.
Let's apply some numbers. I'll use base-10 measurements (1 kilobyte = 1,000 bytes) for mental math purposes. Suppose:
- Our typical JSON document is 100kB in size.
- Our application updates rows at 100 updates per second.
This means that we're writing new data at 100kB per write × 100 writes per second = 10MB written per second. Consider now the possibility that we're streaming our changes to a replica (after all, we are responsible stewards of our dataset); 10MB per second is a constant 80Mbps network transfer for each replica.
Of course, this is napkin math - it's very possible there's compression on transport or similar mitigation of this river of data. The math is further complicated by the particulars of how PostgreSQL manages its small slices of disk usage (called "pages") and how our data fits within the boundaries of these pages, but this paints the correct picture for our discussion.
Now let's consider how MongoDB, a document-storing specialist, handles this situation. Rather than copy the entire document on each update, MongoDB mutates the document in memory and appends a minimal record of change to a replication ledger called the oplog. Replicas of our database only need to receive this smaller stream of surgical updates rather than a complete re-transmission of the entire 100kB document on each write. This reduces the size of this data stream by a few orders of magnitude.
That MongoDB can do partial updates of arbitrarily complicated documents efficiently should not be a surprise: it was built from the ground-up to address this very use case. A document database which has inefficient operations on those documents wouldn't be very popular with users.
Match Techniques to the Tool
The purpose of this example isn't to imply that PostgreSQL has a deficiency. The developers of that database made specific engineering choices to achieve specific goals and made intentional trade-offs. In fact, the authors discuss our situation specifically in their documentation:
Consider limiting JSON documents to a manageable size in order to decrease lock contention among updating transactions. Ideally, JSON documents should each represent an atomic datum that business rules dictate cannot reasonably be further subdivided into smaller datums that could be modified independently.
The last sentence is the most important for our example. Storing JSON blobs is perfectly fine, though making small modifications to those blobs results in less-than-ideal I/O and storage consequences.
A more idiomatic relational table (or set of tables) here would avoid the problem. Instead of representing the entire document in a single JSON structure, we can define a series of tables representing each essential part of the document with appropriate relations between those parts. When a row in one of these sub-tables is updated, only that piece is copied as part of MVCC, and only that subset is replicated.
But perhaps your JSON table has only very small documents, so the cost of a copy is minimal. Perhaps you're running at very few (possibly zero) updates a second, rather than a hundred, so the copies aren't so numerous. Perhaps your network capacity is unbounded, so the traffic amplification from replication isn't a concern (if this is the case, please let me know who does your hosting). In short, whether or not this is a problem at all will depend on the specific situation and specific constraints, so evaluate the situation in that context.
There are many aspects to how these two databases manage their data that a keen engineer should explore while they choose. How big of JSON are we talking about? Could it be measured comfortably in kilobytes, megabytes, or gigabytes? Is storing these within the same database even desirable, or would a completely separate storage technology, optimized for storing large blobs, be more appropriate? What's the acceptable behavior on ungraceful database termination?
The polished syntax in modern tooling hides a common trap in software engineering: while the abstractions offer the user excellent ergonomics and flexibility, it's vital to understand the stack well beyond this facade to avoid trouble.