The previous lesson ended with a list of three failures: the GPS telemetry of every trip, the enriched station profile and the incidents with a variable structure. Three things VallBici's relational schema handles badly, not because it is badly designed, but because they are data that do not ask for what the relational model knows how to give.

This lesson takes those three exact pieces —not one more— and models them in MongoDB applying the query-driven design method from 03-03. It does not redesign the transactional core: subscriptions, trips, charges, docks and bicycles stay in PostgreSQL, and the first part of the lesson explains why that boundary is the most important decision in the whole chapter.

At the end, each design decision is compared with how it turned out in 08-01, what has been lost is listed precisely, and the alternative an experienced professional would raise in the meeting is put on the table: and why don't we do it all in PostgreSQL with jsonb and PostGIS? It is a legitimate question and it deserves an answer with arguments, not a shrug.

And it ends with an open problem we will not know how to solve yet: from today on there will be two databases holding information about the same bicycle, and nobody has said which one wins.

1. What leaves PostgreSQL and why not everything
2. The method: queries first, documents afterwards
3. The stations collection: the enriched profile
4. The trips_telemetry collection: the bucket pattern
5. The incidents collection: variable structure with validation
6. Write operations: real-time telemetry
7. Aggregation pipelines: the operations reports
8. Geospatial queries: what the relational side did worse
9. Indexes, explain() and expiry with TTL
10. What has been lost and how it is mitigated
11. The honest comparison: PostgreSQL with jsonb and PostGIS
12. Common Mistakes and Tips
13. Exercises
14. Conclusion

Let us start with what is not done, because that is where most people get it wrong.

The wrong decision is "we migrate VallBici to MongoDB".

If somebody proposes it in the meeting, these are the concrete —not generic— arguments against it in this system:

1. Billing must be ACID and multi-document. Closing a trip touches four things: the trip, the dock, the bicycle and the charge. In PostgreSQL it is one transaction and there is nothing more to say. MongoDB has had multi-document transactions since 4.0 (we saw it in 03-04), but they carry a noticeable performance cost and are the exception, not the normal working mode. Building a billing system on top of an engine's exception is a bad choice.
2. The constraints that hold VallBici up are declarative. EXCLUDE USING gist for overlapping subscriptions, the partial unique index for open trips, the composite foreign key of the hierarchy discriminant. MongoDB has none of the three. They would be application code, and we already saw in 08-01 what happens to rules that depend on code under concurrency.
3. The city council's queries are unpredictable. A document model is designed to answer a known set of queries fast. Next year the council will ask for a cross-tabulation nobody has imagined today, and there the normalized schema wins hands down: it is the virtue 03-03 highlighted in the relational model, answering questions nobody foresaw.
4. There is no scale problem in the core. 24,000 subscribers and 1.6 million trips a year fit comfortably in a single-server PostgreSQL. The sharding from 03-01 solves a problem VallBici does not have.

A dataset is a candidate to leave the relational world when it meets several of these conditions. None is enough on its own:

The first three columns meet five or more conditions; the fourth meets practically none. The boundary is not ideological: it is this table.

The dashed arrows are not foreign keys: they are agreements. They all point the same way and none of them is enforced by an engine. Who maintains them, with what delay and what happens when they fail is the entire content of lesson 08-03.

One important nuance about the incidents. In 08-01 there is workshop_orders, with its cost NUMERIC and its unique open-order index. That table stays: it is the workshop's accounting record and it feeds into the municipal budget. What leaves is the detailed report of the incident —the variable structure with photos, measurements and observations—, which will live in MongoDB referencing the order. That distinction between "the accounting fact" and "the heterogeneous detail of the fact" is one of the most useful splits there is, and it applies to a great many domains.

In 03-03 we inverted the mental order of module 2: in the document model you do not start from the domain entities, you start from the queries. So before writing any document, here is the list, with its frequency and its latency requirement.

Two observations before designing:

• Q1, Q4, Q7 and Q9 read a whole object by its identifier. They are the ones the document model serves with a single disk access, and they are 95% of the volume. That figure alone justifies the decision.
• Q5, Q6 and Q8 are analytical aggregations. There are few of them and they tolerate seconds. The documents do not have to be designed for them; you just have to make sure they can be answered.

The rule from 03-03: design for the frequent queries, tolerate the rare ones.

_id is PostgreSQL's station_id, not an ObjectId. It is the most consequential decision in the document. Advantages: joining the two worlds is trivial, no extra index over a station_id field is needed, and a repeated write with the same _id is naturally idempotent. Drawback: it forces the station to exist in PostgreSQL first. That is accepted, because PostgreSQL is the source of the truth for the inventory — and that sentence, which sounds innocent here, is the whole subject of 08-03.

district embedded as a subdocument with id and name: extended reference pattern. The district name is displayed on the profile; going to fetch it from another collection for two words makes no sense. Only what is displayed is copied, not the district's whole document. The duplication risk is the one 03-03 warned about: if the council renames a district you have to update 60 documents. With five districts that change name approximately never, that is an acceptable risk and the corresponding updateMany is one line.

photos embedded, not referenced. We apply the criteria from 03-03: there are few of them (2-6 per station), they are always read with the station, they are never queried separately and they do not grow without limit. It is a textbook "one to few". Embed.

accessibility and opening_hours as free-form subdocuments. Here is the reason this collection exists. In the relational world, pavement: "cobblestone" and passage_width_cm: 120 would be two more columns that only have a value at some stations. Here, the station with no ramp simply does not carry the field, and adding direct_bike_lane: true tomorrow requires no migration at all.

What is NOT embedded: the bicycles present. The temptation is enormous —"that way the app asks for one document and has everything"— and it is one of the four anti-patterns 03-03 flagged: an array that changes dozens of times an hour inside a document that is read 60,000 times a day. Every unlock would rewrite the whole document, invalidating the cache and causing contention. Real-time availability does not live here; in 08-03 you will see where it lives.

That last row is the honest downside: in the document world, "it has no ramp" and "we do not know whether it has a ramp" look dangerously alike. It is solved with schema validation, which we will see in the incidents collection.

This is the case where the choice of structure changes the system by two orders of magnitude, so let us first do the calculation that rules out the naive option.

An average trip lasts 14 minutes with a position every 5 seconds: 168 points. With 1.6 million trips a year:

One document per point multiplies the number of documents by 168, multiplies the index cost by more than 100 and turns the most frequent query over this data into the retrieval of 168 objects that have to be sorted. It is the anti-pattern 03-03 called the too-small document: the metadata weighs more than the data.

Short field names: t, p, v, b. It is not affectation. MongoDB stores the name of every field in every element of the array. With timestamp, position, speed and battery, the names would weigh about 30 bytes per point × 168 points × 1.6 M trips ≈ 8 GB a year just repeating words. With one-letter names, about 900 MB. It is one of the very few situations in which sacrificing field readability is justified, and the trade-off is documenting it well.

t is an offset in seconds, not a date. An ISODate takes 8 bytes and a small integer 1 or 2. The absolute date is reconstructed by adding it to start_ts. Same reasoning.

bbox and distance_m are computed fields (the computed-field pattern from 03-03). They are filled in when the trip is closed and avoid having to walk the 168 points every time somebody asks "how far did it go?". It is the same logic as available_bikes in 08-01: you pay with a redundancy that has to be maintained and you collect on every read.

bicycle embedded with four fields: extended reference again. Why copy the plate and the model if they are in PostgreSQL? Because Q8 —incidents by model— and Q5 —distance by bicycle— would otherwise be resolved with an impossible $lookup: the bicycles table is in another engine. Copying the model here is what makes it possible to group by model without leaving MongoDB. The price: if a bike changes model (it does not happen) or its plate is corrected (it happens rarely), there are historical documents with the old value. For historical data that is correct, not an error: the trip was made with the plate it had at the time. It is exactly the same argument as the frozen fare in 08-01.

The hard limit of a MongoDB document is 16 MB. A normal trip takes 14 KB, so there is room to spare. But VallBici has a rare case: the weekend tourist route, with five-hour trips. 5 h × 720 points/h = 3,600 points ≈ 300 KB. It still fits, but a pathological trip —a bike left in a truck for two days with the GPS transmitting— could get close to the limit.

The operating rule, applying the bucket pattern with a ceiling:

Maximum 900 points per document (75 minutes). When that is exceeded, a new document is opened with window: 1, window: 2…

Q4 then goes from being a findOne to a find with a sort, but only for the 0.3% of trips that overflow. It is the literal application of the outlier pattern from 03-03: you do not design the whole system for the rare case; you design for the normal case and flag the rare one.

The balance 03-03 argued for. Without validation, MongoDB accepts anything and six months later there are documents with type: "Brakes", type: "brake" and severity: "high". With excessive validation, you lose the only advantage that justified using MongoDB.

The key line is detail: { bsonType: "object" }. It is required to be an object —not a string or an array— and absolutely nothing is said about its contents. Adding type: "gps" tomorrow with a detail of three new fields requires one single thing: adding "gps" to the enum. No ALTER TABLE, no downtime window.

Proof that it works:

Compared with 08-01: there, severity BETWEEN 1 AND 5 would have been a twenty-character CHECK. Here it is twelve lines of JSON. Validation in the document world costs more to write and you have to want it explicitly, and that is why so many collections end up with none at all.

The bicycle emits a position every 5 seconds. Is every point written the moment it arrives?

It works, but it has two measurable problems. The first: every $push rewrites the document if it has grown beyond the reserved space, and a document that grows from 200 B to 14 KB in 168 steps is relocated several times. The second: it is 269 million network and WAL operations (here, journal operations) a year.

The chosen option is the micro-batch in the telemetry gateway: the application accumulates 30 seconds' worth of points in memory and writes six at once.

The four details that matter:

• $each turns six writes into one. It reduces the number of operations by a factor of 6 and the risk of the document being relocated.
• $slice: 900 is the bucket ceiling enforced by the engine itself: the array never goes beyond 900 elements, whatever the application does. It is the document-world equivalent of a CHECK.
• $max: { last_t: 440 } makes the write order-idempotent: if a batch arrives late and out of order over the network, last_t does not go backwards.
• upsert: true lets the first batch create the document. If the "trip started" event were lost, the telemetry is not lost with it.

What $push does not fix, and it has to be said: $each is not idempotent. If the gateway retries a batch because it did not get an acknowledgment, the six points are added twice. With position data that is tolerable —two identical consecutive points do not change any conclusion— and that is why it is accepted. If they were monetary amounts, it would not be, and that is exactly the reason the money stayed in PostgreSQL.

It is an update with an aggregation pipeline (available since MongoDB 4.2): it lets you compute the derived fields inside the engine, without pulling the 168 points into the application. It is the document-world equivalent of a generated column.

Stage by stage:

Almost the same query. The real difference: the SQL needs two JOINs to reach the model, and the pipeline needs none because the model is copied inside the document. That is the central trade-off of the document model, in one sentence: duplication in exchange for local access.

The $unwind is the dangerous stage and you have to understand why. It turns each trip document into 168 documents, one per point. Over 45,000 trips in a month that is 7.5 million intermediate documents. That is exactly the reason for the two precautions the pipeline carries: the $match goes before the $unwind (if it came afterwards, the 1.6 million trips of the year would be unwound) and the $project reduces each point to two rounded numbers before grouping.

It is also the answer to why Q6 tolerates 10 seconds and runs 30 times a day, not 30,000. A pipeline with a $unwind over millions of documents is not an application query: it is a report.

The double $group is the pattern to learn from here. The first groups by (model, type); the second groups again by model and uses $push to put the results of the first into a nested array. The outcome is a hierarchical structure that in SQL would require either two queries or one with window functions and a manual pivot with FILTER like the one you did in 07-04. Producing hierarchies is where the aggregation pipeline clearly beats SQL.

And notice what makes the query possible: bicycle.model is copied into every incident. Without that duplication, grouping by model would require going to PostgreSQL, and there is no way to do that from a pipeline.

Q2 —"stations less than 500 m away"— is the query that came out worst in 08-01. With latitude and longitude as NUMERIC, the only way out without extensions is a Haversine formula in the SELECT, which cannot use any index: it forces you to compute the distance to all 60 stations and filter afterwards.

In MongoDB it is one index line and one query.

Three things come free in that query: the results arrive sorted by distance without asking, the $maxDistance is in meters over the sphere (not in degrees), and the additional capacity.covered filter combines with the geospatial one without ceremony.

For Q3, "stations inside the district polygon", the operator is a different one:

$near sorts by proximity and needs an index; $geoWithin does not sort and can work without one, although it does much better with it. And the classic mistake, which deserves bold type because everybody makes it the first time: GeoJSON is [longitude, latitude], in that order. The other way round, Vallmar shows up in the Indian Ocean and the queries return zero results without raising any error.

The order of the fields in a composite index follows the same rule as in PostgreSQL (06-03): equality first, range or ordering at the end. { status: 1, type: 1, opened_at: -1 } serves Q7 completely, and also "all open incidents" —a prefix of the index—, but not "all brakes incidents", because type is not a prefix. It is identical to what happened with composite B-tree indexes.

Checking with explain():

How to read it. totalKeysExamined == totalDocsExamined == nReturned is the perfect result: the index located exactly the 23 rows needed, with no discards. And there is no SORT stage, because the index already delivers the requested order. The reading is the same one you did with EXPLAIN ANALYZE: if totalDocsExamined were 40,000 to return 23, you would have the equivalent of a high Rows Removed by Filter.

The city council keeps the telemetry for 180 days; after that only the aggregates are of interest. In PostgreSQL that would be a monthly DELETE over 45 million rows, with its corresponding VACUUM and its maintenance window, or else range partitioning.

In MongoDB it is an index:

An internal process walks the index every 60 seconds and deletes what has expired. Three warnings, because TTL surprises anyone who does not know them:

1. The deletion is not punctual. A document can survive up to a minute —or more, under load— after its expiry. It is not good enough for legal requirements of "exact" deletion.
2. The field must be a date. If start_ts were a string, the index works but deletes nothing, silently.
3. In a replica set only the primary deletes, and the replicas receive the deletion through replication. That is the correct behavior, but it means a lagging secondary holds documents that "no longer exist".

Before the TTL deletes anything, a monthly process consolidates what does have to be kept:

$merge is the document-world equivalent of a materialized view from 05-04: the detail expires, the summary remains.

A case study that only tells you the advantages is advertising. These are the four real losses, with their mitigation and with what the mitigation does not achieve.

In 08-01, trips.bicycle_id REFERENCES bicycles guaranteed that a trip of a non-existent bike cannot exist. Here, trips_telemetry.bicycle.id = 417 is a number. If somebody deletes bike 417 from PostgreSQL, MongoDB does not find out and keeps orphan telemetry forever.

Mitigation: (a) the $jsonSchema forces the field to exist and to have the right format, which avoids typing errors but not referential ones; (b) ON DELETE RESTRICT in PostgreSQL means bikes are never deleted, only marked status = 'withdrawn' — and that decision, taken in 08-01 for accounting reasons, turns out to be the one that also protects coherence between engines; (c) a nightly reconciliation that lists the distinct bicycle.id values in MongoDB and checks that they all exist in PostgreSQL.

That 947 is an orphan: telemetry of a bicycle that does not exist. The reconciliation does not prevent it; it detects it. That is a difference in nature, not in degree: we have gone from a guarantee to an alarm.

$lookup joins two collections in the same MongoDB database. It cannot join with PostgreSQL. Full stop. Any report that crosses telemetry with billed amounts needs:

And even if the collections were in the same MongoDB, $lookup is not an equivalent JOIN: it runs as a loop over the foreign collection, it does not exploit statistics and there is no planner to reorder anything. It is a tool for enriching already-filtered results, not for querying two large collections at once. The practical rule: if your pipeline starts with a $lookup over millions of documents, the model is wrong.

Nothing prevents bicycle.model from saying "Norvent Urban2" when PostgreSQL says something else. The mitigation is procedural, not engine-based:

• Idempotent writes: the _id is the trip_id, so rewriting the same document twice produces the same result. That is worth gold when there are retries.
• A single writer per collection: only the telemetry service writes to trips_telemetry. No three different services touching the same collection "because it is faster".
• Scheduled reconciliation with a discrepancy report, like the one above.
• schema_v in every document: when the shape of the document changes, the old ones are still readable and you know which ones to migrate.

There is no EXCLUDE USING gist, there is no partial unique index with an arbitrary predicate, there is no cross-column CHECK expressed with the naturalness of SQL. $jsonSchema validates shape and ranges; it does not validate relationships between documents or temporal conditions.

Mitigation: put in MongoDB only data where those constraints are not needed. Which, if you look closely, is precisely the criterion from section 1 read backwards. A coherent design is one that does not need the guarantees its engine does not give.

The uncomfortable question that had to be asked: everything in this lesson can be done in PostgreSQL. jsonb stores documents with GIN indexes; PostGIS does geospatial work better than MongoDB; range partitioning expires data better than TTL. Why not stay on a single engine?

Count the votes: PostgreSQL wins in more rows. And the honest conclusion is this:

If VallBici had 6 stations and 90 bicycles, the correct answer would be to stay in PostgreSQL with jsonb and PostGIS, no discussion.

What tips the balance in the real VallBici is two rows, not nine: 272 million GPS points a year and the council's forecast of doubling the fleet in three years. That write volume of data that needs neither transactions nor referential integrity is the case a document store exists for, and it is the only weighty reason.

The signals that would say "go back to PostgreSQL, this was not worth it":

1. The telemetry turns out to be queried crossed with the charges constantly.
2. The volume plateaus at a level a well-partitioned PostgreSQL can take.
3. The team cannot keep real competence in both engines.
4. Complex geospatial requirements appear (routing, isochrones) that PostGIS would do and 2dsphere would not.

None of those signals is shameful. Undoing an architecture decision when the data changes is professional competence, not failure.

Mistake 1: migrating everything. The complete case in this lesson is three collections. Nobody touched billing. A project that starts with "let's move the database to MongoDB" instead of "these three datasets fit better in documents" has already made the decision before analyzing it.

Mistake 2: one document per GPS point. The calculation in section 4 is the argument: 269 million documents against 1.6 million. When data arrives as a time series associated with something, the bucket is almost always the answer.

Mistake 3: a bucket with no ceiling. The 16 MB limit gives no warning until you hit it, and then the write fails in production. $slice in the $push and a window flag.

Mistake 4: [latitude, longitude]. GeoJSON is [lon, lat]. It raises no error: it simply returns zero results and makes you lose an afternoon.

Mistake 5: $unwind before $match. It multiplies the documents and filters afterwards. It can turn an 8-second pipeline into an 8-minute one.

Mistake 6: creating the collection without a validator "because the application already validates it". It is the same sentence that in 08-01 justified not adding a CHECK, and it ends the same way. In the document world it is worse, because the damage accumulates silently for months.

Tip 1: write the query table before the first document. The nine rows of section 2 determined the three collections, the duplicated fields and the six indexes.

Tip 2: short field names only inside large arrays. In points it is justified by the 8 GB a year. Putting n instead of name in stations is gratuitous illegibility: they are 60 documents.

Tip 3: a meaningful _id whenever you can. Using PostgreSQL's trip_id as the _id gives you idempotence for free, saves an index and makes the correspondence between engines obvious.

Tip 4: read explain() with the same eyes as EXPLAIN ANALYZE. totalDocsExamined against nReturned is MongoDB's Rows Removed by Filter, and an in-memory SORT stage is the warning that an index is missing.

Tip 5: write the reconciliation process today. When there are two engines, the question is not whether they will diverge, but when and how long it will take you to find out.

The app is going to allow rating a station from 1 to 5 with an optional comment and up to three tags (dirty, poorly_lit, stiff_docks…). About 200 ratings a day are expected. The station profile must show the average score and the three most recent ratings.

1. Decide whether the ratings are embedded in stations or go in a collection of their own, with the criteria from 03-03.
2. Write the resulting document and the necessary indexes.
3. Write the operation that records a new rating and keeps what is shown on the profile up to date.

Operations wants to detect suspicious trips: average speed above 35 km/h (the bike travelled in a vehicle), more than 20 minutes at speed 0 in the middle of the trip, or an electric bicycle losing more than 40% of its battery in less than 15 minutes.

Write an aggregation pipeline that returns the trips of the last day meeting any of the three conditions, with the condition they triggered and the bicycle's data.

An audit discovers that 41,000 documents in trips_telemetry have bicycle.plate in the old format (0417 instead of VB-0417), because for two weeks the gateway wrote the field wrongly.

1. Write the query that locates them.
2. Write the update that fixes them without pulling the documents into the application.
3. Explain why this problem could not have happened in the relational schema of 08-01, and what MongoDB mechanism could have prevented it.

Solution 1

1. Its own collection with an embedded subset. The criteria from 03-03 give a clear answer: 200 ratings a day × 60 stations over years is a "one to very many" with no ceiling, and an unbounded array inside a document read 60,000 times a day is the unbounded-array anti-pattern. But the profile needs the three most recent, and a second query on the app's hottest path is a real cost.

The answer is the subset pattern: a ratings collection with everything, and in stations a summary with the three most recent and the aggregates.

2. The design:

sum is stored in addition to avg so the average can be recomputed incrementally without walking 1,284 documents.

3. The write, in two operations:

Two pipeline stages because the second needs the values set by the first. $concatArrays + $slice: 3 keeps the three most recent at the front and discards the rest. They are not atomic with respect to each other: if the second fails, there is a rating recorded that does not appear in the summary. It is mitigated with a nightly recomputation of the summary, which is the known price of every computed field in the document world.

Solution 2

Three points worth highlighting: the initial $match goes first and uses the index over start_ts; the $filter counts stopped points without $unwind, which is the correct way to operate over arrays when you do not need to unwind them; and $switch labels the reason, respecting the same priority order as the $or.

Solution 3

1. Locating them:

2. Fixing them on the server:

The update pipeline lets you build the new value out of the old one inside the engine. Without it, you would have to read 41,000 documents, transform them in the application and rewrite them: a few minutes of network and memory for something that here takes two seconds. Notice that the updateMany filter is /^\d{4}$/ and not the $not from the query: only those with the known old format have to be fixed, not anything that fails to match.

3. Why it would not have happened in the relational world, and what would have prevented it here. In 08-01 the plate is not copied anywhere: it lives only in bicycles.plate, with its CHAR(7) UNIQUE, and trips reaches it by foreign key. A value that exists only once cannot be written wrongly in the copy, because there is no copy. The duplication that gives us performance here in Q5 and Q8 is exactly what made the error possible.

What would have prevented it in MongoDB: a $jsonSchema on trips_telemetry with pattern: "^VB-[0-9]{4}$" over bicycle.plate, just like the one we did put on incidents. The incidents collection was protected and the telemetry one was not, and the error appeared exactly where the validator was missing. That is no coincidence: it is the rule.

You have taken three concrete pieces of the VallBici system —telemetry, station profile and incidents— and solved them in MongoDB with the method from 03-03: the nine queries first, the documents afterwards. The result is three collections with defensible decisions: stations with extended references and free-form subdocuments, trips_telemetry with the bucket pattern, a 900-point ceiling and computed fields, and incidents with a $jsonSchema that validates what is common and leaves what is specific free.

And you have seen what each advantage costs. bicycle.model copied inside every document is what makes it possible to group by model without leaving the engine; it is also what produced the 41,000 badly written plates in exercise 3. The bucket pattern reduces 269 million documents to 1.6 million; it also forces you to watch a 16 MB limit that did not exist before. TTL expires the telemetry with an index; it also deletes when it feels like it, not when you say so. In document modeling there are no free decisions: there are decisions whose price you know and decisions whose price you will discover in production.

The comparison in section 11 is the part worth remembering a year from now. PostgreSQL with jsonb and PostGIS wins on more criteria than it loses, and for a small VallBici it would be the correct answer with no qualifications. What justifies the second engine is not the elegance of the document model: it is 272 million annual writes that need neither transactions nor referential integrity. When somebody proposes adding a database to a system, that is the question to ask — what number justifies this?— and if there is no number, there is no reason.

One open problem remains, and it is the hardest of the three. From now on, bicycle 417 has data in two places: its plate, its model and its status are in PostgreSQL, and they are also copied inside thousands of MongoDB documents. Station 12 has eleven columns in PostgreSQL and a thirty-field profile in MongoDB. Nobody has said which one wins. When the two disagree —and they will— which version is the true one? With what delay does a change propagate? What happens if the synchronization process goes down on a Tuesday night? And we still have to bring Redis into the equation, which will hold real-time availability, and Elasticsearch, which will serve the search for stations by name and address.

That is polyglot persistence, and it is lesson 08-03: not how each engine is designed —that is already done— but how you decide who is the source of the truth for each piece of data, how they are synchronized, what keeps working when one of them goes down and when it is worth dismantling this whole architecture and going back to a single database.