MongoNYC 2012: Schema Design by Example

MongoNYC 2012: Schema Design by Example, Kyle Banker, 10gen. MongoDB has been designed for versatility, but the techniques you might use to build, say, an analytics engine or a hierarchical data store might not be obvious. In this talk, we'll learn about MongoDB in practice by looking at hypothetical application designs (based on real-world designs, of course). Topics to be covered include schema design, indexing, transactions (gasp!), trees, what's fast, and what's not. Sprinkled with tips, tricks, shoots, ladders, and trap doors, you're guaranteed to learn something new in this interdisciplinary talk.

Transcript

1. Schema Design by Example Using MongoDB
2. Kyle Banker kyle@10gen.com
3. @hwaet
5. Three App Designs
6. What we'll cover: Schema Design Principles Hierarchies Ticketing Feed reader
7. Schema Design Principles
8. Documents Documents can be flat and relational, like rows. Or they can be richly structured...
9. Strategy Start with a normalized model. Use a rich, 'embedded' document for simplicity and / or performance.
10. 1. Hierarchies
11. Two Strategies Store a map of the hierarchy in a single document. Store a list of ancestors (what we'll do).
16. A sample node: { _id: ObjectId("6a5b1476238d3b4dd5000048"), parent_id: ObjectId("9a9fb1476238d3b4dd5000001"), name: "Hard bop", description: "Pioneered by ...", slug: "hard-bop", ancestors: [ {name: "Bop", _id: ObjectId("8b87fb1476238d3b4dd5000003"), slug: "bop" }, {name: "Ragtime", _id: ObjectId("9a9fb1476238d3b4dd5000001"), slug: "ragtime"} ] }
17. Fetch the 'hard-bop' node with parent links db.categories.find( {slug: 'hard-bop'} );
18. Display all descendents of 'Bop'... db.categories.find( {'ancestors.slug': 'bop' } )
19. You'll want this index: db.categories.ensureIndex( {'ancestors.slug': 1 } )
20. Now, how do we update the structure?
21. First: a helper method.
22. Build an ancestor list, and update the category. function build_ancestors( _id, parent_id ) { ancestor_list = [] while( parent = db.cat.findOne( {_id: parent_id}, { name: 1, slug: 1, _id: 1 } ) { ancestor_list.unshift( parent ); parent_id = parent.parent_id } db.cat.update({_id: _id}, {"$set" {ancestors: ancestor_list}} ); }
23. Now, let's add a new category: Swing.
24. Now, let's add a new category: Swing.
25. Create a document for 'swing'... category = { parent_id: ragtime_id, slug: "swing", name: "Swing", description: "It don't mean a thing if it ain't got that..." };
26. Insert and then build ancestors. swing_id = db.cat.insert(category); build_ancestors(swing_id, ragtime_id);
27. Next, let's place 'Bop' under 'Swing'.
30. First, update the parent id... db.cat.update({_id: bop_id}, {$set: {parent_id: swing_id}});
31. Then, update the ancestor list for all descendents of 'Bop'. query = {'ancestors.id': bop_id}; db.cat.find(query).forEach(function(category) { build_ancestors(category['_id'], category['parent_id']); }
32. Finally, rename 'Bop'...
33. ...to 'Bebop'.
34. Use the $ (positional) operator.
35. Update the main 'Bop' document... doc = db.cat.findOne({_id: bop_id}); doc['name'] = 'Bebop'; doc = db.cat.update({_id: bop_id}, doc);
36. ...then mult-update in all ancestors with ($) db.cat.update({'ancestors.id': bop_id}, {'$set': {'ancestors.$': doc}}, false, true)
37. Summary Use arrays and multi-key indexes. Update embedded objects with the positional operator.
38. 2. Ticketing
39. But don't you need transactions?
40. Transactions without Transactions
41. Bank accounts Transferring money is not a transaction. Distributed systems, long-running transactions, and contentious environments may require a different transactional strategy (Jim Gray. "The Transaction Concept: Virtues and Limitations").
44. Two collections
45. One document for the seating map. // Orchestra section map. { _id: ObjectId("8b87fb1476238d3b4dd5000003"), event_id: 133, section: 'ORCH', seats: { 'A1': 1 'A2': 1 'A3': 0 'A4': 0 'A5': 0 // 'A5' - 'J20' } }
47. 2. One document for each seat. { _id: { event_id: 133, seat: 'A1' }, order_id: null, updated: new Date(), state: 'AVAILABLE' }
48. The protocol: User requests seats A3, A4, and A5. We read-modify-write the state of each document. Then we update the seating map.
49. // Transition state, and roll back on failure. [ 'A3', 'A4', 'A5' ].forEach(function(n) { q = { _id: { event_id: 133, seat: n } }; u = { $set: { order_id: 333, state: 'CART', updated: new Date() } }; if( ! db.seats.findAndModify({ query: q, update: u }) rollback(); }
50. // If successful, modify seat map. db.seat_maps.update({ event_id: 133 }, {$set: { 'seats.A2': 1, 'seats.A3': 1, 'seats.A4': 1 });
51. Intermediate state (Compensation mechanism)
52. Cleanup protocol: Periodic daemon. Runs every minute. Revert if 'CART' or 'PENDING_AUTH' and older than 10 minutes. Set to 'AVAILABLE'.
53. Checkout // Transition state, and roll back on failure. [ 'A3', 'A4', 'A5' ].forEach(function(n) { q = { _id: { event_id: 133, seat: n } }; u = { $set: { order_id: 333, state: 'PENDING_AUTH', updated: new Date() } }; if( ! db.seats.findAndModify({ query: q, update: u }) rollback(); } db.orders.update()
54. Authorize // Authorize credit card. // If failure, rollback() tickets to 'CART' status. // If successful, transition tickets to 'PURCHASED'. // Add tickets to order object. db.orders.update( _id: 333, { $pushAll: { line_items: [ 'A3', 'A4', 'A5' ] });
55. Summary Use atomic writes and read-modify write semantics. Define a transactional procedure using compensation mechanisms. If you're using MongoDB, and you need basic transactional semantics, you have some tools.
56. 3. Feed reader
57. Four collections Users Feeds Entries Buckets
58. User document (stores subscribed feeds) { _id: ObjectId("4e316f236ce9cca7ef17d59d"), username: 'kbanker', feeds: [ { _id: ObjectId("4e316f236ce9cca7ef17d54c"), name: "GigaOM" }, { _id: ObjectId("4e316f236ce9cca7ef17d54d"), name: "Salon.com" } { _id: ObjectId("4e316f236ce9cca7ef17d54e"), name: "Google News" } ], latest: Date(2011, 7, 25) }
59. Unique index on 'username' db.users.ensureIndex( { username: 1 }, { unique: true } )
60. Feeds { _id: ObjectId("4e316b8a6ce9cca7ef17d54b"), url: 'http://news.google.com/feed.xml/', name: 'Google News', subscriber_count: 2, latest: Date(2011, 7, 25) }
61. Unique index on URL db.feeds.ensureIndex( { url: 1 }, { unique: true } )
62. Adding a feed with an upsert db.feeds.update( { url: 'http://news.google.com/feed.xml/', name: 'Google News'}, { $inc: {subscriber_count: 1} }, true )
63. Subscribing a user to the feed db.users.update( {_id: ObjectId("4e316f236ce9cca7ef17d59d") }, { $addToSet: { feeds: { _id: ObjectId("4e316b8a6ce9cca7ef17d54b"), name: 'Google News' } } )
64. Unsubscribing from a feed db.users.update( { _id: ObjectId("4e316f236ce9cca7ef17d59d") }, { $pull: { feeds: { _id: ObjectId("4e316b8a6ce9cca7ef17d54b"), name: 'Google News' } } )
65. Removing a feed subscription db.feeds.update( {url: 'http://news.google.com/feed.xml/'}, { $inc: {subscriber_count: -1} } )
66. Feed entries (populate in background) { _id: ObjectId("4e316b8a6ce9cca7ef17d54b"), feed_id: ObjectId("4e316b8a6ce9cca7ef17d54b"), title: 'Lorem Ipsum...', body: 'Vestibulum eget dui metus...', reads: 5, date: Date(2011, 7, 27) }
67. What we need now Populate personal feeds (buckets) Avoid lots of expensive queries Record what's been read
68. We'll use the bucketing pattern
69. By default, a less-then-optimal query. db.entries.find( feed_id: {$in: user_feed_ids} ).sort({date: -1}).limit(25)
70. But with bucketing, we can be smarter. entries = db.entries.find( { date: {$gt: user_latest }, feed_id: { $in: user_feed_ids } ).sort({date: -1})
71. A compound index on 'date', 'feed_id'. db.entries.ensureIndex( { date: 1, feed_id: 1} )
72. bucket = { _id: ObjectId("4e3185c26ce9cca7ef17d552"), user_id: ObjectId("4e316f236ce9cca7ef17d59d"), date: Date( 2011, 7, 27 ) n: 100, entries: [ { _id: ObjectId("4e316b8a6ce9cca7ef17d5ac"), feed_id: ObjectId("4e316b8a6ce9cca7ef17d54b"), title: 'Lorem Ipsum...', body: 'Vestibulum eget dui metus...', date: Date(2011, 7, 27), read: false }, { _id: ObjectId("4e316b8a6ce9cca7ef17d5c8"), feed_id: ObjectId("4e316b8a6ce9cca7ef17d54b"), title: 'Panda bear waves hello', body: 'A panda bear at the local zoo...', date: Date(2011, 7, 27), read: false } ] }
73. The bucket is a strategic denormalization 1 query + 1 seek (if not in memory) = document-level locality
74. Insert the bucket and set the latest entry db.buckets.insert( bucket ) db.users.update( { _id: ObjectId("4e316f236ce9cca7ef17d59d") } { $set: { date: latest_entry_date } } )
75. Viewing a personal feed db.buckets.find( { user_id: ObjectId("4e316f236ce9cca7ef17d59d") } ).sort({date: -1}).limit(1)
76. Retrieving the next set of results... db.buckets.find( { user_id: ObjectId("4e316f236ce9cca7ef17d59d"), date: { $lt: previous_reader_date } } ).sort({date: -1}).limit(1)
77. Include this index, of course. db.buckets.ensureIndex( { user_id: 1, date: -1 } )
78. Mark a personal feed item as 'read'. db.buckets.update( { user_id: ObjectId("4e316f236ce9cca7ef17d59d"), 'entries.id': ObjectId("4e316b8a6ce9cca7ef17d5c8")}, { $set: { 'entries.$.read' : true } )
79. Increment the read count on the entry... db.entries.update( { _id: ObjectId("4e316b8a6ce9cca7ef17d5c8") }, { $inc: { read: 1 } } )
80. A note on sharding. The buckets collection is eminently shardable Shard key: { user_id: 1, date: 1 }
81. Summary Use rich document structures for caching. Take advantage of locality of reference. Think about sharded schema design.
82. Thank you