The Two-Book Rule of Data Modeling: One Book for the Truth, One for the Crowd
A shopkeeper keeps a ledger. Every sale goes in once, in order, with the amount, the item, and the customer. It is the most honest book in the shop. If a price was wrong, she fixes it in one place. Nothing in the book contradicts anything else.
Then business grows, and every day a hundred people ask her the same question: “How much did we sell today?” Each time, she must run her finger down the whole ledger and add. At first it takes a minute. Later it takes an hour—customers queue. The ledger is not wrong. It is simply not built for being asked the same question a hundred times.
So she does what any sensible shopkeeper would do. She hangs a board by the door: “Today’s total: 84,300.” Now the hundred people read the board and leave in two seconds. Her day is saved.
But the board has a price. Every sale now needs two writes, one in the ledger and one on the board. If she is busy and forgets, the board lies, and every customer who trusts it is misled. And the board only answers one question. Ask for sales by item, and she is back to the ledger.
That is data modeling for reads versus writes. The ledger and the board are the same data in two shapes, and every database design is a decision about how many boards you can afford to keep up to date.
In a previous post here we saw one kind of board: the index, a cabinet of cards that points at the data. Today we go one level up, to whole copies of data shaped for the people reading them.
Book one: keep the truth in one place
Database designers call this normalizing. Each fact is stored exactly once. A customer’s address lives in one row in one table, and every order points to that customer. When she moves house, you change one row, and every order now shows the new address. Nothing can disagree with itself, because there is only one copy to disagree with. This is the right default for anything that gets written to. It makes writes simple and keeps your data honest.
The price is paid at read time. To show an order page, you might need the order, the customer, the items, the product names, and the shipping address, which means joining five tables. For one page, that is fine. For ten thousand people looking at dashboards every minute, it adds up. MongoDB’s documentation makes the same point from the other side: embedded data models are often considered denormalized because frequently accessed data is duplicated, and that duplication is the trade you accept for reading it in one go [S5].
Book two: a copy shaped for the readers
The first tool most relational databases give you is the materialized view. A normal view is just a saved query, run again every time. A materialized view stores the answer. PostgreSQL’s manual describes it as persisting the results in a table-like form, so that reading it is like reading from a table [S1].
The manual is refreshingly honest about the cost: while access to the data stored in a materialized view is often much faster than accessing the underlying tables directly, the data is not always current, yet sometimes current data is not needed [S1]. Its own example is a sales summary for a dashboard that graphs historical sales, refreshed nightly [S1]. The dashboard does not care about the last five minutes. It cares about being fast.
There is even a striking number in the manual. In a separate example that loads a word list from a file into a materialized view with indexes, a spell-check lookup that took about 188ms against the original source took about 0.12ms against the materialized view [S1]. That example is about a foreign data source, not an ordinary table, so do not expect those exact numbers from your own tables. The lesson is the size of the gap when you stop recomputing the same answer.
Here is the shopkeeper’s board, in all three relational databases. We will use the same dashboard question: sales per seller per day.
PostgreSQL keeps the copy until you refresh it:
CREATE MATERIALIZED VIEW daily_sales AS
SELECT seller_id, order_date,
SUM(total) AS sales_total, COUNT(*) AS order_count
FROM orders
WHERE order_date < CURRENT_DATE
GROUP BY seller_id, order_date;
CREATE UNIQUE INDEX daily_sales_key ON daily_sales (seller_id, order_date);
-run from a nightly job
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;
A few details from the manual are worth knowing. A refresh completely replaces the contents of the view [S2]. Without CONCURRENTLY, a refresh that touches many rows may block people reading the view, though it tends to use fewer resources and finish faster [S2]. With CONCURRENTLY, readers are not locked out, but it is only allowed when the view has at least one UNIQUE index made of plain column names that covers all rows, which is why the example creates one [S2]. And only one refresh at a time may run against a view [S2].
SQL Server calls its version an indexed view. The result is stored by putting a unique clustered index on the view, and the first index on a view must be that unique clustered one [S3]. The view must be created WITH SCHEMABINDING, and when it groups, it must include COUNT_BIG(*) [S3]:
Get Rufus Kehinde’s stories in your inbox
Join Medium for free to get updates from this writer.
CREATE VIEW dbo.daily_sales
WITH SCHEMABINDING
AS
SELECT seller_id, order_date,
SUM(total) AS sales_total,
COUNT_BIG(*) AS order_count
FROM dbo.orders
GROUP BY seller_id, order_date;
GO
CREATE UNIQUE CLUSTERED INDEX ix_daily_sales
ON dbo.daily_sales (seller_id, order_date);
Microsoft lists more rules for indexed views, so read the full list before you build one [S3]. There is a bonus: once it exists, the optimizer can use it to speed up matching queries even when the query does not mention the view [S3]. The bill is on the write side. Microsoft warns that when a table referenced by many indexed views, or by a few complex ones, takes an INSERT, UPDATE or DELETE, those views must be updated too, and DML performance can degrade significantly [S3]. This is the shopkeeper updating a very busy board on every sale.
Oracle gives you a choice of how the board is kept fresh. A materialized view stores precomputed results of expensive joins and aggregations [S4]. It can refresh ON DEMAND (when someone runs a refresh procedure, the default) or ON COMMIT, which refreshes automatically when a transaction that changed a base table commits [S4]. Oracle notes that ON COMMIT makes the commit take slightly longer, so it may not suit heavily concurrent workloads [S4]. A fast refresh, which applies only the changes, normally needs a materialized view log on the base table, and COUNT(*) must be present in the view for all types of fast refresh [S4]. The shape below follows Oracle’s own example [S4]:
CREATE MATERIALIZED VIEW LOG ON orders
WITH SEQUENCE, ROWID (seller_id, order_date, total)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW daily_sales
REFRESH FAST ON COMMIT AS
SELECT seller_id, order_date,
COUNT(*) AS order_count,
SUM(total) AS sales_total,
COUNT(total) AS total_count
FROM orders
GROUP BY seller_id, order_date;
Oracle also has a warning for the careless: if a view fails during an ON COMMIT refresh, it stops refreshing automatically until you invoke a refresh yourself after fixing the error [S4]. That is a board that quietly stops being updated, which is the nightmare version of the shopkeeper forgetting.
The simplest board of all: a counter
Not every board needs a fancy feature. Sometimes the board is one number. Keep an order_count column on the customer row, and change it in the same transaction that inserts the order. It is fast to read and cheap to keep, as long as every code path that creates or cancels an order remembers to update it. The risk is exactly the shopkeeper’s: one forgotten path and the number drifts. If you do this, put the update next to the insert, in one transaction, and add a nightly job that recounts and compares.
MongoDB: embed or reference
MongoDB does not have joins in the same everyday way, so the same decision shows up as a modeling choice. Embedding puts related data inside the parent document. According to MongoDB’s documentation, it gives better read performance, the ability to retrieve related data in a single operation, and the ability to update related data in a single atomic write [S5]. It suits one-to-many relationships where the many are viewed in the context of the one [S5].
The limits are firm. Documents in MongoDB must be smaller than 16 mebibytes [S5]. And the documentation names a classic mistake, the unbounded array: if you do not limit how many elements an array can hold, your documents may exceed the 16 MB limit, and an unbounded array can strain application resources and decrease index performance [S6]. A book that embeds every review it will ever receive is the textbook case [S6].
The documented fixes are to use subsetting and referencing to bound arrays [S6]. Referencing means each review lives in its own collection, and the book only holds IDs. It solves the size problem but adds latency, because you must query the reviews collection to read them [S6]. The subset pattern splits the difference: keep a few recent reviews inside the book for instant display, and the rest in their own collection [S6].
// books: only the 3 newest reviews live inside the document
{
title: “Example Book”,
recent_reviews: [
{ reviewer: “Ada”, rating: 5, text: “…” },
{ reviewer: “Tunde”, rating: 4, text: “…” },
{ reviewer: “Chidi”, rating: 4, text: “…” }
]
}
// reviews: one document per review, all of them
db.reviews.find({ book_id: bookId }).sort({ created_at: -1 }).limit(20)
Do not split too far
Once you taste fast reads, the next temptation is to split the whole system in two: one model for writing and a completely different model for reading. This pattern has a name, CQRS, short for Command Query Responsibility Segregation. Microsoft’s Azure Architecture Center describes it as separating read and write operations into different data models [S7].
The benefits are real. Reads and writes can scale independently, and the read side can use a schema optimized for queries, avoiding complex joins [S7]. But there is a cost the article states plainly: when the read and write databases are separated, the read data might not show the most recent changes immediately, which means stale data, and keeping the two in sync is hard [S7]. Because you usually cannot put a message broker and a database in one distributed transaction, the article recommends the Transactional Outbox pattern and idempotent consumers to mitigate this [S7]. That is the outbox from a previous post here, and the idempotency idea from a previous post here. Finally, Microsoft says CQRS might not be suitable when the domain or business rules are simple, or when simple CRUD-style access is enough [S7].
My own rule: try the cheapest board first. An index, then a counter, then a materialized view, then a separate read model. Each step is faster to read and more expensive to keep honest.
Three questions before you copy anything
How often is it read compared with written? A copy pays off when reads far outnumber writes. If the data changes on every request, the board will cost more than the ledger saves.
How stale can it be? Nightly, every minute, or instantly? The answer decides your refresh style. If a stale number could cause real harm, such as a balance, keep reading from the truth.
How big can the list grow? If the answer is “no limit”, it should not be embedded in one document or one row.
Then write down who keeps each copy fresh. A board without an owner is a board that lies.
The common thread
Databases are not slow because of bad luck. They are slow because the same question was asked a million times and answered from scratch each time. The fix is not magic. It is a second book, kept honest, shaped for the crowd. In a previous post here, we saw what happens when a shortcut like a cache hides a slow database until the day it breaks. A precomputed copy is the same trade done on purpose: know what it costs, know how stale it can be, and know who keeps it fresh.
A quick audit for your own system: list every copy of data you keep, whether it is a cache, a counter, a summary table, or a view. For each one, write down what refreshes it, how stale it can get, and what happens when the refresh quietly fails.
Sources
S1: PostgreSQL manual, Materialized Views. https://www.postgresql.org/docs/current/rules-materializedviews.html
S2: PostgreSQL manual, REFRESH MATERIALIZED VIEW. https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html
S3: Microsoft Learn, Create Indexed Views. https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver17
S4: Oracle Database Data Warehousing Guide, Basic Materialized Views. https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html
S5: MongoDB manual, Embedded Data in Your MongoDB Schema. https://www.mongodb.com/docs/manual/data-modeling/embedding/
S6: MongoDB manual, Avoid Unbounded Arrays. https://www.mongodb.com/docs/manual/data-modeling/design-antipatterns/unbounded-arrays/
S7: Microsoft Azure Architecture Center, CQRS Pattern. https://learn.microsoft.com/en-us/azure/architecture/patterns/cqrs
