Beyond Good and ORMs

Talks

Kerri Miller

#database-performance

#ruby-on-rails

#web-development

#object-relational-mapper-orm

Beyond Good and ORMs

by Kerri Miller

The video presented by Kerri Miller at the Ancient City Ruby 2015 event explores the use of Object-Relational Mappers (ORMs), particularly ActiveRecord, and their impact on database usage and application development. Miller discusses the powerful features of relational databases that are often overlooked due to the abstraction provided by ORMs.

Key Points Discussed:

- Introduction to ORMs: Miller acknowledges the benefits of ORMs in developing applications quickly but cautions against losing sight of the capabilities of underlying databases.

- Views and Materialized Views: She explains how views can optimize database queries by acting as predefined queries that return results efficiently, reducing memory usage and processing time. Materialized views store data persistently for fast access, which can be very useful in reporting.

- Stored Procedures: The discussion includes how stored procedures can encapsulate data transformation logic within the database, improving performance by limiting the back-and-forth communication between the application and the database.

- Data Types: Miller explores advanced data types in databases, particularly focusing on PostgreSQL. She highlights how data types such as arrays, hashes (HStore), JSON, and enums can facilitate complex data handling without compromising relational integrity.

- Triggers: The concept of triggers is introduced as a means of automatically executing certain SQL commands based on database actions, which can reduce ActiveRecord callbacks in Ruby.

- Functional Fixedness: She illustrates the idea of functional fixedness and how developers can benefit from reassessing the tools and data types at their disposal, likening it to the ability of a tool to have more uses than initially perceived.

Significant Examples:

- Miller uses an analogy of a barn to describe the limitations of relying solely on ActiveRecord as a hammer.

- She describes how using a view to gather restaurant data can minimize query execution times and reduce memory overhead compared to traditional ActiveRecord queries.

- She discusses how PostgreSQL's array data type enhances the ability to manage and query complex data like product colors efficiently.

Conclusions and Takeaways:

- Miller emphasizes the importance of understanding the underlying database features to build robust applications, advocating for a more in-depth knowledge of SQL and database functionalities.

- Developers are encouraged to rethink their approach to database interactions, moving beyond the basic functionalities of ORMs to leverage the full power of the databases they are using.

- Ultimately, the video serves as a reminder to developers to balance the convenience of abstraction with a thorough understanding of database capabilities for long-term application health.

00:00:01.360 Two households, both alike in dignity, in fair Verona where we lay our scene.

00:00:08.800 From ancient grudge break to new mutiny, where civil blood makes civil hands unclean.

00:00:13.840 From forth the fatal loins of these two foes, a pair of star-crossed lovers take their life, whose misadventured, piteous overthrows do with their death bury their parents' strife.

00:00:27.039 The fearful passage of their death-marked love and the continuance of their parents' rage is now the two hours' traffic of our stage.

00:00:39.520 If you with patient ears shall attend, what here shall miss, our toil shall strive to mend. Now, it's been about ten years since I got to give that monologue.

00:00:51.039 I used to be a Shakespearean actor a bit, and that's my favorite opening monologue to any Shakespearean play. "Romeo and Juliet"—everyone agree that's "Romeo and Juliet"?

00:01:05.519 Messed it up a little tiny bit. Well, that's the character of the Chorus, who is traditionally in the theatrical world an omnipotent, omniscient narrator who is perfect.

00:01:19.760 He does not lie to us, or she, except that in "Romeo and Juliet," the narrator is lying to you.

00:01:30.880 What is "Romeo and Juliet" about? Two star-crossed lovers? Anyone else? Mostly hormones, right? Especially if you read it in high school.

00:01:47.840 Yeah, well, it's about all that, but it's also about peer pressure, the role of women in society, and the corrupting influence of politics on art.

00:01:59.119 It's about religion's relationship to a perfect divinity and the way that people actually live. It's about what happens when our society, and especially parents, fail children.

00:02:11.200 Those are some very subtle things that weren't lost on Shakespeare's audience, what he was writing for at the time. His audience was really interesting—it ranged from people who were barely on the bottom of middle-class society all the way up to royalty.

00:02:23.760 One of the structural tricks that Shakespeare uses to make his plays accessible to his audience is this idea of the shifting viewpoint that cuts across class and societal role.

00:02:40.640 This allows us to see the same situation presented again and again through different lenses, different observers, different characters in the world of the play.

00:02:51.760 We're told the tale of "Romeo and Juliet" primarily through the teens. They are the observers; they are the actors—those who are actually acting and causing events to happen.

00:03:03.519 But we also see the play through the eyes of Juliet's nurse, who, in her role as surrogate mother, is encouraging this 13-year-old girl to have a love affair that's kind of crazy.

00:03:09.519 Friar Lawrence, the monk who helps them and supplies the poison, is primarily also a surrogate parent figure, but the only reason he's helping the two of them hook up is he's hoping to clean up the mean streets of Verona and restore the influence of the church.

00:03:23.120 Benvolio and Mercutio are two very favored characters by most teenage boys who get forced to play it. They are sexually experienced role models who are also street thugs. They taunt and tease Romeo and basically bully him into having a sexual relationship with Juliet, but ultimately, they just see his little love affair as a trifle against the politics of what's going on between these two families.

00:03:47.200 So, is "Romeo and Juliet" just about star-crossed lovers and hormones? Is it a sad tale of how love, raging against all odds, really doesn't conquer all? Is it a cautionary tale about how the fire of youth dims in all of us, and as adults, we need to nourish that flame when we see it in others and ourselves?

00:04:00.560 Shakespeare uses this technique of having other characters from different class roles, different genders, and different societal stations examining the events. We often see commoners commenting on what's going on in the play, saying, "Man, can you believe these crazy royals and what they're doing?"

00:04:29.199 That allows him to connect and show the same situation and examine it from different positions within society, rather than merely presenting the opinion of the upper crust.

00:04:46.560 It allows these characters to communicate across these gulfs of class and life, and although the characters themselves might be unable to change the lens, it allows us to continually change our lens and have a greater understanding. That's one of the geniuses of Shakespeare that his contemporaries just didn't have.

00:05:06.080 They wrote better poetry in some examples, but he was a humanist, and he believed in the divinity of the human over the divinity of any given solid truth. Now, if you can't tell, before I was a developer, I was a performing arts major.

00:05:30.479 I once appeared in three Shakespearean plays simultaneously as a messenger in all three. That's fun when you mess up which play you're in. You tell Macbeth all about what's going on in Rome.

00:05:56.560 Usually, you cover by saying, "Oh, but my lord, you care not for that." I covered by saying, "Oh, I work for LivingSocial right now where I am a heavy metal software developer. It's right there in my title."

00:06:06.840 Tender love's not here, people, you got me. I'm also on the unfortunately named culture committee, which I'm hoping to change the name of—especially after the wonderful talk yesterday about the destructive nature of names in the culture.

00:06:23.120 The culture committee reminds me of some sort of Soviet-era five-year plan. That's literally me—I'm literally that.

00:06:32.440 Before I came to Ruby, though, I've been at Rubios for about six years, but before I came to Rails, I was a Perl developer and then I was a poker player for a year, and then I got into PHP.

00:06:50.400 So you can see that downward moral slide. While PHP has really outgrown its double-clawed hammer reputation that it earned at the time, and six years ago when I made the switch, it was still kind of a hodgepodge; it was still kind of a messy, messy language.

00:07:05.839 There were a lot of great patterns and libraries to use, so let's take a pretty simple case where the sales guy comes to me and says, "I need a report. I need all the users: I need the name, email, and birthday of all the users named William."

00:07:27.520 Anyone ever write PHP? Everyone close your eyes. Now, who wrote PHP? A few more hands—that's great.

00:07:37.199 So, okay, I set up a query here, and then I'm actually going to make the query kind of and this looks pretty. Oh wait, no, I've got to do error checking. Right. Okay, this looks a lot like Rails.

00:07:59.199 And then, oh yeah, like I better let MySQL know that I'm done talking to it. I do all that manually, so imagine how excited I was when I could just write that and it just magically worked.

00:08:13.039 Active Record is this amazing thing, and every time I have to read through its documentation—and that happens a lot more than you might want to admit—I discover that Active Record is capable of something new, like there's some new corner edge case that it does.

00:08:27.680 These things that I forget because all I really concentrate on is CRUD operations: select some things, inject some things, maybe delete something, and I forget about all the other things that I used to use databases for when I had to handwrite and hand-tune every SQL query on my own.

00:08:43.200 Active Record, as great as it is, though, kind of leads us to a problem because what if every carpenter in the world only had a hammer? What would we end up building?

00:09:05.839 I think I have to say that LivingSocial is in fact hiring. We have a lot of barns. They're not all this bad, though. Usually, what we build ends up not being quite this bad.

00:09:17.360 This isn't bad, right? This is a pretty good barn. I mean, what does the barn have to do? It has to keep you dry, keep you out of the wind, and I guess the roof is mostly whole here; it's not rusted out.

00:09:36.080 And you know that one wall's all thin, and if the wind comes that's probably the hurricane side, right? You all know about hurricanes, I don't know. Think about hurricanes—they're a drink.

00:09:54.560 If, you know, if a big wind does come, we'll just slap up some boards on that side, call it good, and you know if we need a door, we'll add a door and it'll be great, right?

00:10:07.680 Active Record is an awesome hammer, and it's got some cool Swiss Army tools on it, but because it's so great, we, as an industry—at least the Ruby community part of the industry—have kind of forgotten some of the cool things that databases can do for us that they're designed to do by default.

00:10:26.880 Those are things that we could be using to build more solid applications. So without any further ado, let's get started—this is my talk.

00:10:41.839 I can't believe you don't have something that's funny. I love this book I've given to people, and this is my favorite panel from it.

00:11:02.720 It's great; it's like a Sailor Moon episode about SQL. So one of my favorite things about relational databases—one of the features that we tend to forget about—are views.

00:11:20.640 That's the traditional definition of what a view is—if you go to Wikipedia, as of this morning, that is the definition of a view. I don't know what the frick that means. I'm sorry, can I curse? Is that okay? I just got your responsible.

00:11:38.560 Strong language! But basically, a view is a table that exists in memory rather than on disk; it is a table that isn't the result of a bunch of inserts and deletes, but as the result of a query.

00:11:54.560 It's a specific query that you program ahead of time. It's a lot like going to a restaurant and ordering a number three instead of saying, 'I'm going to have this, and this, and this, and this,' right? It's a shortcut that's stored ahead of time.

00:12:15.200 You could think of it as a named query, like you have named scopes and named routes that are shortcuts—this is a named query.

00:12:35.680 So, if we had this pretty basic data model, right? And I want to print all the restaurants, their regions, and their annual sales, we do something like this in Active Record.

00:12:50.800 What's the problem with this? Exactly! This is an N plus one situation where I make the query to get all of the restaurants, and then every single time I loop over this, I've got to query out to find the region for that restaurant, creating N plus one queries in my database.

00:13:11.520 I can solve that; that's actually pretty easy, right? We do this all day long. Now Rails will load up all the regions as well as all the restaurants.

00:13:22.560 But what if I also have a managers table and I want to join that in as well? Let's say I want to list all the managers of the store—they've got assistant managers and head managers—I want to list that.

00:13:39.920 You know, I keep growing this include, and pretty soon that's like five different tables I'm joining on. That's a mess.

00:13:53.440 And what happens if I've got say 50 restaurants, 10 regions, and 100 managers? What happens then? You get this in your log.

00:14:08.720 These are actually five different queries that are getting executed against your database. Every single one of those potentially locks or eats up resources or something else blocks, because we've got other applications that are trying to execute queries.

00:14:23.600 Sometimes you see it like this, and this is the one that your DBA calls you up in a panic at 2 AM saying you're breaking everything—production's down!

00:14:34.080 So you can have five potentially good queries or one completely unreadable one, but you gotta run, and they're kind of slow. And what happened to your object graph during all this?

00:14:56.880 If you recall, I said we have 50 restaurants, 10 regions, and 100 managers. Well, now I've just added 160 objects, plus all the objects for all of their names and titles and phone numbers.

00:15:15.840 Who knows? Maybe managers are like automatically joining up and including their addresses, their home phone numbers, because someone decided way back in this time that was important.

00:15:36.000 I've loaded up every single address for them—both their billing and shipping—because we have billing and shipping now for our.

00:15:49.840 You see how this spirals out of control really quickly? This is a lot of memory and CPU we're chewing through, just as if it was free.

00:16:04.080 So, views, on the other hand, execute their predefined query, in this case loading up just what we need—the restaurant's name, the region, the assistant managers—gathers all that up and it saves it as a table and returns that to us.

00:16:29.280 Instead of getting back 160 objects that we're storing up in memory that we have to parse over and go through, we get back simply 50 objects in memory.

00:16:44.320 Active Record actually supports this out of the box because a view is a table—it's a virtual table. But it's just like a table; you can create them in migrations, and you can add indexes to them.

00:16:57.920 By storing this in the database, we isolate it to where it can be optimized by the database engine itself, instead of worrying about Ruby and like how do I get the performance of Ruby to get better here. Maybe I can do it in pieces—maybe in parts.

00:17:19.440 We do this to get cleaner code in Ruby because that's what we're going to be interacting with.

00:17:40.480 We get a slimmer memory footprint, and we don't sacrifice the—oh goodness!—that we've come to expect, and Demeter is happy. Since we're making a new object to be backed by this view, again, a virtual table, we've got an object—restaurant region manager report.

00:17:57.440 We can add methods to perform transformations, records, and callbacks and all that sort of business that we would normally do with an Active Record object.

00:18:25.360 So we get back to concentrating on solving the problem that we have at hand rather than worrying about the technique.

00:18:50.720 I know what you're saying, "But Kerri, that's a query and now I've tied my application to my database! How often do you change databases? How often do you just wake up on a Tuesday and be like, 'Ah, this Postgres, hear a lot of good things about that MySQL'—I mean that never happens, right?

00:19:08.799 We just don't change databases as much as we think that we do, as much as we claim that we want that sort of flexibility—we just don't do it. Right? So a view is a named query.

00:19:23.360 And I know what someone else is thinking: 'Well, that's just a query, and I have to run that very expensive query, so there's not a real big advantage.' Well, that's where materialized views come in.

00:19:39.120 Which is a very fancy word for saying that the content of the view, rather than being updated whenever you call it, like rewriting that query and returning it, is basically cached by the database whenever one of the underlying tables gets updated.

00:19:55.760 Say we add a new restaurant or we update sales or hire a new manager; every time that changes, we update the contents in memory of that view, so it's always hot, piping fresh.

00:20:13.680 More or less. It's very fast to access because it's just simply 50 rows of text instead of joining and scanning and all that business.

00:20:25.520 Views can really, really speed up things. I use them a lot with reports when I'm building business intelligence applications.

00:20:43.760 That nightly email you have to the nightly cron job that writes the email to tell the salespeople how much commission they didn't earn that day? Materialized views are your friends.

00:20:59.120 I like this rabbit a lot. How many people saw a rabbit? Did everybody else see a duck? Oh man, maybe if I flip it around.

00:21:10.640 This is the rabbit-duck; this was an image that appeared in an 1800s comedy magazine—in Germany—and Wittgenstein stole it and wrote extensively about it in his book, Philosophical Investigations.

00:21:27.760 He discussed this idea of figures that can be seen and understood in two different ways and how weird that is.

00:21:43.680 You could see something in a very straightforward way—we see that if we see this as a rabbit, for example, it's a rabbit. And then something weird happens in our brain, and we say, 'Oh, it's a duck.'

00:22:01.440 This happens with every optical illusion, right? You see the old woman; they see the young woman. You see the horse, and then you see the frog—all the famous ones, right?

00:22:18.560 But has the object actually changed itself? That's a really strange thing where now we've got a dichotomy between the outside world and the inside world.

00:22:30.560 The world that exists in our head has just changed, and suddenly the meaning of this—Is it a duck, or is it a rabbit?—It's both! It's now much richer to our imaginations and has more utility.

00:22:45.920 Once we can see that, this also represents the idea of functional fixedness, which we talk about in cognitive psychology. The common example of that being...

00:23:14.720 You all know about the Titanic? I showed a picture of Leo DiCaprio. I mean, you know the Titanic movie, right? Okay, you see.

00:23:33.440 Everybody jumps off the ship, right? Because the ship was sinking. Why didn't they get on the iceberg? The iceberg was floating; it's right there—why didn't they get on the freaking iceberg?

00:23:49.920 Functional fixedness: You don't think about the iceberg as something that's floating that can save you. It's got to be in a ship; I'm on the water.

00:24:06.000 When you see a tool—whether it's a database or a gem or a framework or something—and then you can see it in a different light, you can see a different use for it.

00:24:26.560 When you realize that your hammer is also really good for holding open doors, now it's a richer tool. When you see that the screwdriver also makes a pretty damn good pry bar.

00:24:44.640 And if you're in a pinch, I've never done this, you could jam it across two things in your car to jump start it if the starter is not working correctly; you could use it as a fuse.

00:25:02.080 You could jam it into a fuse box. As a journeyman member of IATSE—the stage hands union—don't do that! We never do that.

00:25:17.680 Hampton, are you still here by any chance? I saw this, and I was like, 'This is so cool!' This is great; I just stood out. I'm staring at it, and I'm like, 'Why did they hang it on the side?' Does anyone know why they hung it sideways?

00:25:35.840 I flipped it so it's right side up. Why? Anyone see that? It's not on its side; it's right side up. It's just the foot—it's the reflection upon still young. But it's our functional fixedness around this idea that it's a human figure and it needs to be upright because it's not dead, and he's not sleeping.

00:25:50.720 So clearly, this is hung sideways. Sorry, Hampton; I'm sorry I burst your bubble on this one. That's functional fixedness.

00:26:12.159 Let me move on to stored procedures. Most SQL that you encounter is very declarative in nature, right? It's like, 'Insert this,' or 'Grab select that,' or 'Delete that other thing—create a table.'

00:26:35.120 For the most part, we're just using our persistence layers for data injection and retrieval, but it can do so much more.

00:26:50.560 We usually do data transformation in our application. Stored procedures can do data transformation for us in the database engine in efficient ways.

00:27:08.399 It can be conditionally reactive to other data in the database that we're not actually retrieving. Does that make any sense to anybody?

00:27:23.360 So you combine these with a materialized view, and suddenly you've got this really super high-performance view level of complex data for simply the low cost of tightly bundling your application to the database.

00:27:36.080 As I already said, it's great—you could write your entire application, in fact, inside of your database that way. It’s kind of a trade-off.

00:27:51.680 A lot of people have done it. We used to do a lot in the Oracle world. We used to do a lot of Oracle Amazon; we did all sorts of weird complex stored procedures in the database, and it was kind of magic.

00:28:06.720 So, you have to really figure out where you want to put your logic—the benefit is you're often going to improve performance. I've seen performance improvements when doing something in Postgres versus doing it in Ruby on the order of magnitude faster.

00:28:28.800 Now granted, Ruby's pretty fast, but when you're talking like 100 milliseconds down to 10, that's pretty great—that’s 90 seconds my JavaScript front-end devs can wait, so that's great.

00:28:47.920 So really again, the cost—the cost of these stored procedures, right? Is now we have to write in two different languages, and we're splitting our application code and testing across two different programming paradigms.

00:29:05.008 Postgres—I talk a lot about Postgres mostly because I'm a Postgres advocate. Postgres has its own internal language for this called PL/SQL. That's kind of annoying.

00:29:23.200 So they also support TCL, Perl, and Python natively; you can just send it random strings of that, and it'll work. There's third-party support for PHP, Java, Scheme, Ruby, and JavaScript.

00:29:37.799 I do know what you're thinking: 'Yes, you can, in fact, insert your entire Node.js application into a stored procedure and have it execute in Postgres, and it will return a result!' Who are you people?

00:29:51.440 'Cans are not shoulds.' Okay, so how do you actually do that?

00:30:06.320 Right? How do you actually run some of that stuff? Here's an actual example using JavaScript where we create a function and say we're going to use the PL.

00:30:28.320 V8 has actually the V8 JavaScript engine in there, and just run this, and data comes back as JSON.

00:30:41.440 Oh come on, it's Trigger! It's true, you know Roy Rogers' Trigger and the dog Bullet and this happy guy; that might have been his name, I don’t know.

00:31:03.520 Anyway, triggers are something else we talk a lot about in the database world. A trigger is a SQL command that’s automatically executed by the server when certain database actions happen—usually inserts, updates, and deletes.

00:31:19.520 There are Active Record callbacks in our database; you can do this before and after any insert, update, or delete. That’s pretty cool!

00:31:39.920 This is a stored procedure to update the log table when someone makes a purchase. We say, 'Hey, this is our stored procedure! It says, whatever I'm naming this thing, whenever I call a log purchase, I want you to do this other little bit of SQL which is insert into logs a bunch of BS.'

00:31:56.560 Here’s the actual trigger itself—we say after insert on purchases, for each row, execute this procedure which just updated that log.

00:32:14.080 That is super powerful! Because I hate debugging Active Record callbacks after updates, and then it does a whole bunch of like, well send something to the log and write this other thing and note down with the user you’re saving.

00:32:31.440 You do all these other extra queries that you don't need to be doing; you don't need to be doing it in Ruby—all you have to do is do it in the database layer where it’s faster.

00:32:46.080 You're not doing back and forth network communication; you’re not hogging up resources that your other applications could be using!

00:33:05.520 Something else I’m really super excited about are data types because I’m a nerd. Out of the box, this is a list of all of the Active Record data types that are listed in the current edge Rails documentation.

00:33:17.440 This is actually not a complete list, but this is pretty much what you’ve seen. Most applications that we work with, especially when we're doing web-based applications, deal with basically chunks of text, right? We classify them into these different buckets.

00:33:35.680 But I've got a super awesome one I love called arrays. Those are solar arrays, but it’s just a tough room.

00:34:05.520 So let’s pretend that we’re building another application because they loved our restaurant reporting; it was so fast.

00:34:27.680 We want to build an e-commerce platform system; we want to sell shirts. We want to let all these different shirt retailers up and down George Street here—we want to let them just arbitrarily access our API.

00:34:41.440 We can reasonably expect merchants to have standardized categories or classes of data about their products like name, price, current inventory, and the item’s description. Right? That’s pretty normal.

00:35:10.720 This fits really nicely into a lot of the Active Record data types that we normally see, but what if someone wants to upload a list of all the colors of shirts that they offer?

00:35:30.080 Now, I’ve seen a lot of people do this with a relational table, but maybe like blue gets spelled differently, and then we're in a mess—a whole heap of trouble, and we've got raspberry, magenta, different colors—red, you know, this is kind of a problem.

00:35:49.480 A lot of people just accept a common delimited list. They say, 'Here you go—here's all this business.' And if you want to actually pick out all the colors of this shirt, you’ve got to do some dance like this.

00:36:09.200 Of course, you can put it behind an initializer or you can have some sort of method that splits it apart on the model, but that’s still ugly and ungainly.

00:36:28.400 What if we want to search for everything that has blue in it? Well, now we're doing full-text searches across database columns.

00:36:43.280 I see some nodding out there—people who have scars on the inside about this! Well, Postgres' awesome array data type solves this case for us.

00:36:57.200 Here I'm just grabbing a product where I've added those colors as an array data type. You see here it's an array. Postgres knows about arrays.

00:37:15.600 You can save something as an array, and then Rails—and excuse me, Ruby and Rails, Active Record, that whole mishmash—says, 'Oh, that's an array!' I'm going to make that an array—that's really super handy.

00:37:31.280 If I want to search on it, that’s a little ugly; I don’t really like that, but that’s SQL for you. I could just simply pass in an array, and I can search. You see here, it found one product that has the color right in there.

00:37:47.600 And if I want to find everything that has all the products that are offered in red and blue, I can do that as well.

00:38:06.440 Anyone see the cool thing that's going on here? Though probably not, because this is a whole wall of text! Oh, hey, there you are.

00:38:25.680 It's not order dependent because Postgres is indexing that array as an actual array—as a data structure itself—not simply as a text string.

00:38:40.960 So it doesn't matter the order of the objects I look for in that array. I can look for blue, red, red, blue, red, green, green, red; it doesn't matter—it'll still return that one thing.

00:38:54.720 I think that's pretty cool! It is really ugly to work with—especially that ampersand greater than. So you could set it up as scopes, which is pretty easy.

00:39:08.640 No one thinks this is cool? They don’t think this is cool? Who thinks this isn’t cool? If you don't think it's cool, just your monkey.

00:39:22.080 This is all you have to do to add it—that's all you have to do! You just say, 'Oh, it's an array,' and its default is an empty array—that's it.

00:39:39.600 Something else I did on this—I realized afterward—I’m making an index; I’m saying I’m using the GIN method. There are two methods in Postgres—anyway, GIN and gist.

00:39:54.720 We always say, 'Oh, don’t index—that's really tough for ins. You know, every time you insert that's really expensive.' GIN is best for static data because it does better, faster lookups.

00:40:06.640 But gist—gist is better for when you have data that's changing a lot; when you’re going to be doing a lot of inserts, you can use this different technique—you still get, you know, 90% of the benefit of having an index but less of the downsides.

00:40:19.440 So that's an array. What if we wanted to show the inventory for each color? It kind of sounds like a hash, doesn't it?

00:40:35.680 Don't know. I searched for hash in HStore, and this is the first image result I got, so I figure I just—I'm behind the times. Anyway, Postgres has hashes.

00:40:50.720 Active Record also supports it. We have to actually do like a create extension here to like enable it onto our Postgres.

00:41:04.960 Look at that! I can just add it, and I'm defining it as an HStore column, because Active Record knows about it. Active Record doesn’t tell you about it, but it’s there.

00:41:18.080 Then I can really do this wonderful thing where I say: color inventory, stabby arrow key blue has the value of zero.

00:41:35.600 Or key blue is greater than zero, so I can search across all of the keys that exist in that hash. That’s really, really cool!

00:41:51.600 And I know what you’re thinking: We’re coming up on JSON territory now, aren’t we? Problem is that HStore can’t nest objects, and it stores everything as text.

00:42:08.320 We actually end up doing some processing on the back end; we’re doing full-text searching. If we actually want to do JSON, we can.

00:42:22.080 I'm really sad Hampton's not here—I included this for Hampton, because this is something else that happened in 2008. This album came out. This is like some dude's mixtape, I don't know. I think he’s really intense.

00:42:40.960 Okay, boom! JSON as a data type—you can have a column that just is like JSON, that’s going to be well-formed. If it’s not well-formed, the database blows up.

00:42:55.440 But you can store JSON in a relational table. If you have Postgres, I'm very proud of my doing that, people!

00:43:09.680 With a single arrow for searching like that, you can actually get back a JSON—Ruby JSON object if you use the double-stabby arrow there—and you can actually get back a text string.

00:43:22.560 I don't know why you'd want that; apparently, I don't actually need this slide. You know, sometimes I actually have to lead people to this, but it seems like a good number.

00:43:34.800 But if you didn't understand why that's good, here's why! Postgres is an ACID database; it has certain guarantees about the integrity of its data.

00:43:44.960 When it says something’s written, it’s written. When I say I'm going to delete something, I delete it. It follows the ACID protocol of databases.

00:43:57.680 If you need to do document storage but you also have relational data, you don't have to compromise one way or the other—you can combine the two.

00:44:06.960 These are good things; this is peanut butter and chocolate territory, people! And there's more.

00:44:19.920 Range, money, composites—this is cool! You get back a Ruby range of money; you can do money calculations in your database, even for non-decimal money systems.

00:44:36.720 Like if you're dealing with shillings or something, I don't really know. Enums are really super cool—they’re the drop-down select box of a column.

00:44:51.920 You say, 'Hey column, I'm only ever going to put open, closed, and pending into you!' And if I try to put something else into you, it blows up!

00:45:05.520 That's super cool for status fields—it’s really cool for employee roles, anything like that where you want to have human-readable data but you want to limit what could possibly go into that column.

00:45:24.160 IP addresses—oh my God! I worked for a hosting company one time; we used MySQL, which does not have an IP data type.

00:45:41.360 So we stored all of our IP addresses for all of our different servers as—you guessed it—text strings! How do you sort things? How do you search for what's in this particular subnet?

00:45:57.680 Well, you can do it with Postgres; you can't with MySQL, unfortunately.

00:46:05.920 Postgres, out of the box, can create UUIDs for you; it also does geospatial records and calculations.

00:46:21.840 I found it super important. I spent a long time looking for a sports company that did stuff with GPS to find out like you were awesome over here and someone else was awesome over here—how far apart are two points on that map?

00:46:39.760 Well, it turns out if you're using latitude and longitude to record where on the planet Earth something is, it's not as simple as drawing a straight line.

00:46:58.480 It actually requires trigonometry to tell how far apart two latitude-longitude points are because of the curvature of the Earth.

00:47:10.640 Postgres handles that for you; you can just say, 'Give me everything within 50 miles,' and it figures out the weird oblong egg that that would result in.

00:47:28.560 So those are some of the cool things that we forget because we're using text, strings, and integers all the time.

00:47:47.920 Anyone know who this is? Hazard a guess—he died before this city was founded. Yes, it's Richard III, in fact. He was buried yesterday.

00:48:06.480 Anyone know that? Yeah, apparently, the story is that like—everyone's always said, 'Oh yeah, like we buried him after this big battle where he was killed.'

00:48:21.600 Then we dug up his body and, you know, to add insult to injury, threw his bones in a river. They found his body buried under a parking lot two or three years ago and they conclusively proved, yes, this is in fact the evil hunchback.

00:48:38.080 And they just reburied him yesterday with honors in a church in Leicester. So Richard III—Richard Third is the villain in the star of Shakespeare's play of the same name.

00:48:55.840 Anyone familiar with it? You might know it from a horse—a horse, a kingdom for my horse! You might know it from this opening line: 'Now is the winter of our discontent made glorious summer by the sun of York.'

00:49:13.920 This is really wonderful. This is a wonderful little couplet here—'Now is the winter of our discontent': he’s using the royal hour; he’s talking about his family.

00:49:30.160 'Made glorious summer by the sun of York.' The interplay of winter and sun and summer—it's seasonal.

00:49:46.080 What he's really talking about is his cousin Clarence, who is a son (S-O-N) of York, and in fact, depending on which edition of Shakespeare you go back to, which folio—you know, all through the 1700s—we’re reprinting this stuff.

00:50:04.080 The spelling of S-U-N versus S-O-N will change depending on who the editor was. This is a pun that Shakespeare's making about this evil man, and he is evil—make no mistake about it.

00:50:20.720 He goes on in this monologue to talk about how now that the war is over, everyone's hanging out with their ladies and how we're dancing for the women now and putting on our fancy airs.

00:50:39.680 He’s a hunchback and ugly and has a hare lip and is just generally evil. He’s kind of the Loki of this world.

00:50:57.680 Since I cannot prove a lover to entertain these well-fair-spoken days, I'm determined to prove a villain and hate the idle pleasure of these days.

00:51:17.680 Plots I have laid, inductions dangerous by drunken prophecies, libels, and dreams to set my brother Clarence against the king and deadly hate the one against the other.

00:51:35.280 And if King Edward, as true and just as I am subtle, false, vindictive, and treacherous, this day should Clarence closely be muted up about a prophecy which says, 'A G of Edward's heirs and the murderer shall be!'

00:51:50.640 He's telling the audience flat out, 'I'm an evil, and I'm tricking everybody to fight against each other because they're good—and that's what they do. They fight injustice and plot and treachery.'

00:52:06.760 So I'm gonna give him something to fight about, and then I'm gonna come in behind and clean up.

00:52:25.760 Now we could see Richard simply as the villain he's written to be, and he's one of the most compelling villains in all of Shakespeare. Usually Shakespeare's villains are kind of caricatures of people.

00:52:49.920 But we really see the inner life of this guy in Shakespeare, and if you can understand him as that—as just a snarling villain—you’d be well rewarded; it’s a really compelling piece of English literature.

00:53:11.200 But if you understand the deeper context of what Shakespeare's writing, he's writing Richard III as a piece of history for the winning side of this battle.

00:53:30.080 The current royalty won the War of the Roses and beat Richard's family. Shakespeare is trying to curry favor with the current ruling class by showing their eminent enemy who died only like 100 years before as a total jerk.

00:53:43.760 He is fitting in and trying to get some political points with the upper crust. Shakespeare knows what he's doing. Shakespeare actually was arrested for hoarding grain during a famine.

00:54:00.480 He was a cold-blooded marketeer! You see beyond what's in front of you—that's an amazing skill to cultivate and it's critical to being an engineer.

00:54:18.720 Does anyone come across the magic shop here in town? Magic! They made the store disappear—amazing! I wish I’d taken a photo of it.

00:54:32.800 On the front door, there's a sign that says, 'Open 11 to 6 PM! Come inside and see the magic!' And if you go and you peer in, it’s empty; the shop's empty—there's no magic!

00:54:52.160 There is no magic in that store, and there's no magic in what we do, and that is a common thing I'm saying over and over again to everybody I meet.

00:55:07.920 We think there's magic, and part of the reason that we think there's magic is this.

00:55:24.000 Let me start with some math. Does everyone agree that this is a pony? I know I promised no ponies. You can't—

00:55:39.000 Now we have two ponies, correct? One pony plus one pony equals two ponies! One plus one equals two.

00:55:52.760 Can we agree to that? Sure! Here’s your homework: I don’t know why you’re laughing. This is just a simple extension of what we’ve been talking about.

00:56:10.160 This is how we talk about software. This is how we teach it. If you pick up a book on software and you have only a cursory introduction to the topic, this is what it looks like.

00:56:28.880 It feels like magic! And so we tend to reach for these abstractions: 'My SQL is hard—oh, Active Record, sweet! I don't play SQL anymore.' But we lose some subtlety.

00:56:49.440 We lose some meaning—we lose some of that magic of what's lying beneath the surface. This was my favorite book when I was a kid.

00:57:09.920 I’ve replaced it like twice now as an adult. This is from Richard Scarry's 'What Do People Do All Day?'

00:57:28.960 Here are this whole series of children’s books which were basically these exploded diagrams of cool things he did—one about the house, bakery, about making a road, what a cruise ship is like inside.

00:57:49.920 I love—the first of all, I just love these little characters! These are so cool! But you can see that he's actually showing inside of the walls—like you could follow where the sewage goes and where the hot air is coming up and the cold air comes down.

00:58:13.520 You can't see it, but there’s a furnace down here, and it strips away the magic of seeing a room as merely four walls, as a cube, as a building being merely a collection of cubes.

00:58:36.640 A city merely being a collection of buildings that are collections of cubes. It says, 'Look at all the stuff that's underneath there.'

00:58:52.160 This looks insane! This is a plot diagram or theme diagram for Romeo and Juliet; this is crazy, but that is nothing compared to Rails.

00:59:06.320 I can’t even fit this chart—I had to spin it sideways, much like that painting! This is a mind map that a friend of mine in Seattle did of everything you need to know to be considered a competent Rails developer.

00:59:26.320 Who here is 10 for 10 on these? I mean, you can't even read these, right? I won’t bother reading them.

00:59:41.440 But applications, whether they're Tinder for sharks or Groupon for the Amish, the technical problems are almost always the same. Right?

01:00:01.440 How do I make this faster? How do I retrieve some data? How do I display it? How do I submit an order for all of these problems?

01:00:20.320 Each specialty that we rely on to make us competent developers is going to offer us a different solution, and the shape of that solution that we eventually reach for reflects what we know and what we've been exposed to.

01:00:36.400 H

Ancient City Ruby 2015