[Script Info]
Title: 
[Events]
Format: Layer, Start, End, Style, Name, MarginL, MarginR, MarginV, Effect, Text
Dialogue: 0,0:00:17.24,0:00:18.69,Default,,0000,0000,0000,,STARR HORNE: So thank you guys for coming.
Dialogue: 0,0:00:18.70,0:00:21.09,Default,,0000,0000,0000,,This is gonna be an awesome talk.
Dialogue: 0,0:00:21.09,0:00:22.75,Default,,0000,0000,0000,,Quite possibly the best talk you'll ever hear
Dialogue: 0,0:00:22.75,0:00:26.44,Default,,0000,0000,0000,,in your life. What? I actually have
Dialogue: 0,0:00:26.44,0:00:27.92,Default,,0000,0000,0000,,a built-in escape hatch here,
Dialogue: 0,0:00:27.92,0:00:29.63,Default,,0000,0000,0000,,so if things start going wrong,
Dialogue: 0,0:00:29.64,0:00:33.49,Default,,0000,0000,0000,,I might just bail. Yeah.
Dialogue: 0,0:00:33.49,0:00:40.35,Default,,0000,0000,0000,,So, anyway. A couple weeks ago, I was, well,
Dialogue: 0,0:00:40.36,0:00:44.09,Default,,0000,0000,0000,,I was sort of having a panic attack about
Dialogue: 0,0:00:44.12,0:00:48.19,Default,,0000,0000,0000,,this presentation, because I'm a programmer,\Nand my natural
Dialogue: 0,0:00:48.19,0:00:51.16,Default,,0000,0000,0000,,habitat is in some sort of dark place looking
Dialogue: 0,0:00:51.16,0:00:53.65,Default,,0000,0000,0000,,a screen. It's not, it's not talking to a
Dialogue: 0,0:00:53.65,0:00:56.81,Default,,0000,0000,0000,,bunch of people with, like, lights. What's\Nup with
Dialogue: 0,0:00:56.82,0:01:00.01,Default,,0000,0000,0000,,this?
Dialogue: 0,0:01:00.02,0:01:01.45,Default,,0000,0000,0000,,So I, I was in the middle of this
Dialogue: 0,0:01:01.45,0:01:03.13,Default,,0000,0000,0000,,panic attack and I went out on a walk,
Dialogue: 0,0:01:03.14,0:01:04.91,Default,,0000,0000,0000,,cause that always clears my mind. I was listening
Dialogue: 0,0:01:04.91,0:01:08.29,Default,,0000,0000,0000,,to a podcast. My favorite podcast. It's called\NHardcore
Dialogue: 0,0:01:08.30,0:01:13.29,Default,,0000,0000,0000,,History. I highly recommend it. Yeah. Oh.\NSomebody likes
Dialogue: 0,0:01:13.29,0:01:16.64,Default,,0000,0000,0000,,it. Yeah!
Dialogue: 0,0:01:16.64,0:01:19.58,Default,,0000,0000,0000,,So this, this particular episode about Hardcore,\Nin, of
Dialogue: 0,0:01:19.58,0:01:24.39,Default,,0000,0000,0000,,Hardcore History was about the Punic Wars.\NAnd in
Dialogue: 0,0:01:24.40,0:01:26.60,Default,,0000,0000,0000,,case you haven't heard about the Punic Wars,\Ndon't
Dialogue: 0,0:01:26.60,0:01:28.22,Default,,0000,0000,0000,,know the story, I'll just give you, like,\Nthe
Dialogue: 0,0:01:28.22,0:01:31.54,Default,,0000,0000,0000,,brief rundown. Basically, a long, long time\Nago, there
Dialogue: 0,0:01:31.54,0:01:34.66,Default,,0000,0000,0000,,was this place called Rome. And they, they\Ndecided
Dialogue: 0,0:01:34.66,0:01:38.00,Default,,0000,0000,0000,,that they should take over the world. And,\Nfor
Dialogue: 0,0:01:38.00,0:01:40.82,Default,,0000,0000,0000,,the most part, they did. But there was this
Dialogue: 0,0:01:40.82,0:01:42.74,Default,,0000,0000,0000,,one country that was a thorn in their side
Dialogue: 0,0:01:42.74,0:01:45.24,Default,,0000,0000,0000,,for a long time called Carthidge. And they\Nfought
Dialogue: 0,0:01:45.24,0:01:46.71,Default,,0000,0000,0000,,all these wars back and forth. It was kind
Dialogue: 0,0:01:46.71,0:01:48.89,Default,,0000,0000,0000,,of a stale mate.
Dialogue: 0,0:01:48.89,0:01:51.07,Default,,0000,0000,0000,,And then one day, when, I don't know, maybe
Dialogue: 0,0:01:51.08,0:01:53.81,Default,,0000,0000,0000,,he was making a sacrifice to the fire god
Dialogue: 0,0:01:53.81,0:01:57.42,Default,,0000,0000,0000,,or whatever, this guy named Hannibal had this\Ngreat
Dialogue: 0,0:01:57.42,0:01:59.70,Default,,0000,0000,0000,,idea. He was like, I'm gonna, I'm gonna lead
Dialogue: 0,0:01:59.70,0:02:02.80,Default,,0000,0000,0000,,this sneak-attack on Rome, and I'm gonna do\Nso
Dialogue: 0,0:02:02.80,0:02:07.10,Default,,0000,0000,0000,,by marching my army through the Alps. Which\Nis,
Dialogue: 0,0:02:07.12,0:02:10.98,Default,,0000,0000,0000,,is pretty cool. I mean, that, that's pretty\Nbad
Dialogue: 0,0:02:10.98,0:02:12.77,Default,,0000,0000,0000,,ass. But I, I think the most awesome part
Dialogue: 0,0:02:12.77,0:02:15.75,Default,,0000,0000,0000,,of the story, for me, at least, is that
Dialogue: 0,0:02:15.76,0:02:19.18,Default,,0000,0000,0000,,the dude had war elephants. So, I, I don't
Dialogue: 0,0:02:19.18,0:02:20.56,Default,,0000,0000,0000,,know if you can see it in the slide,
Dialogue: 0,0:02:20.56,0:02:23.77,Default,,0000,0000,0000,,but there's actually a war elephant. It's,\Nit's kind
Dialogue: 0,0:02:23.77,0:02:26.83,Default,,0000,0000,0000,,of, I don't know, it's kind of under the
Dialogue: 0,0:02:26.84,0:02:31.22,Default,,0000,0000,0000,,G. And a lot of people have doubted this
Dialogue: 0,0:02:31.22,0:02:34.83,Default,,0000,0000,0000,,story, you know, over the years. And so, in
Dialogue: 0,0:02:34.83,0:02:39.40,Default,,0000,0000,0000,,1959, there was a British engineer who, on\Na
Dialogue: 0,0:02:39.40,0:02:44.35,Default,,0000,0000,0000,,bar bet, borrowed a circus elephant named\NJumbo and
Dialogue: 0,0:02:44.35,0:02:47.18,Default,,0000,0000,0000,,marched it across the Alps, too.
Dialogue: 0,0:02:47.18,0:02:49.82,Default,,0000,0000,0000,,So, I don't know what my point is here,
Dialogue: 0,0:02:49.82,0:02:54.69,Default,,0000,0000,0000,,really. I guess don't underestimate elephants,
Dialogue: 0,0:02:54.69,0:02:56.17,Default,,0000,0000,0000,,because they don't
Dialogue: 0,0:02:56.20,0:03:00.14,Default,,0000,0000,0000,,like it and they have long memories.
Dialogue: 0,0:03:00.22,0:03:07.22,Default,,0000,0000,0000,,So, OK. This talk is really about biggish\Ndata.
Dialogue: 0,0:03:07.58,0:03:10.35,Default,,0000,0000,0000,,So what, what the hell is biggish data. It's
Dialogue: 0,0:03:10.35,0:03:12.70,Default,,0000,0000,0000,,not, what is? Whoa, I've got one of these
Dialogue: 0,0:03:12.70,0:03:18.43,Default,,0000,0000,0000,,things. It's not big data. It's not about,\Nyou
Dialogue: 0,0:03:18.44,0:03:20.76,Default,,0000,0000,0000,,know, this talk isn't gonna be about Hadoop\Nclusters
Dialogue: 0,0:03:20.76,0:03:25.35,Default,,0000,0000,0000,,and server farms and all that stuff. That's,\Nthat's
Dialogue: 0,0:03:25.35,0:03:27.26,Default,,0000,0000,0000,,way over my head.
Dialogue: 0,0:03:27.26,0:03:31.46,Default,,0000,0000,0000,,It's not about fancy architectures. I'm not\Ngonna show
Dialogue: 0,0:03:31.46,0:03:35.76,Default,,0000,0000,0000,,you how to make multi-right PostGres clusters\Nthat do
Dialogue: 0,0:03:35.76,0:03:38.81,Default,,0000,0000,0000,,automatic sharding and stuff. I mean, that's\Nall like
Dialogue: 0,0:03:38.81,0:03:40.80,Default,,0000,0000,0000,,wicked cool, but it's not what we're gonna\Ntalk
Dialogue: 0,0:03:40.80,0:03:41.22,Default,,0000,0000,0000,,about.
Dialogue: 0,0:03:41.22,0:03:43.58,Default,,0000,0000,0000,,Instead, I'm gonna talk about something that\NI think
Dialogue: 0,0:03:43.60,0:03:47.35,Default,,0000,0000,0000,,is, is more practical, and it's kind of more
Dialogue: 0,0:03:47.35,0:03:49.28,Default,,0000,0000,0000,,interesting to me, and that is, how do you
Dialogue: 0,0:03:49.28,0:03:52.06,Default,,0000,0000,0000,,keep your app working as your production dataset\Ngrows?
Dialogue: 0,0:03:52.06,0:03:59.06,Default,,0000,0000,0000,,And grows into biggish data territory?
Dialogue: 0,0:03:59.16,0:04:01.19,Default,,0000,0000,0000,,And this is very easy. This is very easy
Dialogue: 0,0:04:01.20,0:04:03.61,Default,,0000,0000,0000,,to happen. Even if you don't have a firehose
Dialogue: 0,0:04:03.62,0:04:06.05,Default,,0000,0000,0000,,of data coming in. If you run a, a
Dialogue: 0,0:04:06.05,0:04:09.50,Default,,0000,0000,0000,,popular e-commerce site or just a site with\Na
Dialogue: 0,0:04:09.50,0:04:11.67,Default,,0000,0000,0000,,lot of users, over the course of years you
Dialogue: 0,0:04:11.67,0:04:15.43,Default,,0000,0000,0000,,can accumulate a ton of data. And as this
Dialogue: 0,0:04:15.44,0:04:18.79,Default,,0000,0000,0000,,data accumulates, you find that your site\Nperformance goes
Dialogue: 0,0:04:18.79,0:04:21.35,Default,,0000,0000,0000,,downhill sort of gradually in ways that you\Ndon't
Dialogue: 0,0:04:21.36,0:04:24.19,Default,,0000,0000,0000,,understand.
Dialogue: 0,0:04:24.19,0:04:28.89,Default,,0000,0000,0000,,So. What is biggish data? This talk is based
Dialogue: 0,0:04:28.90,0:04:31.55,Default,,0000,0000,0000,,on my experience at Honeybadger. In case you\Nhaven't
Dialogue: 0,0:04:31.55,0:04:35.66,Default,,0000,0000,0000,,heard it, of us, we are an exception performance
Dialogue: 0,0:04:35.66,0:04:38.28,Default,,0000,0000,0000,,and uptime monitoring company.
Dialogue: 0,0:04:38.28,0:04:39.67,Default,,0000,0000,0000,,That means that we essentially
Dialogue: 0,0:04:39.67,0:04:42.07,Default,,0000,0000,0000,,have a firehose of new data coming in every
Dialogue: 0,0:04:42.10,0:04:45.65,Default,,0000,0000,0000,,day. Right now we have about a terabyte of
Dialogue: 0,0:04:45.65,0:04:49.70,Default,,0000,0000,0000,,errors in our database. The world has a lot
Dialogue: 0,0:04:49.70,0:04:52.46,Default,,0000,0000,0000,,of errors. You guys need to start, like, doing
Dialogue: 0,0:04:52.46,0:04:54.48,Default,,0000,0000,0000,,a better job.
Dialogue: 0,0:04:54.48,0:04:57.95,Default,,0000,0000,0000,,And, and we get about, we get two gigabytes
Dialogue: 0,0:04:57.96,0:05:01.24,Default,,0000,0000,0000,,of new errors per day, roughly. And all this
Dialogue: 0,0:05:01.24,0:05:06.81,Default,,0000,0000,0000,,goes into a plain vanilla PostGres database,\Nand it's
Dialogue: 0,0:05:06.81,0:05:08.81,Default,,0000,0000,0000,,served, that backs a pretty plain
Dialogue: 0,0:05:08.82,0:05:11.55,Default,,0000,0000,0000,,vanilla Rails application.
Dialogue: 0,0:05:11.56,0:05:15.41,Default,,0000,0000,0000,,Well, the good news is that PostGres can handle
Dialogue: 0,0:05:15.41,0:05:19.59,Default,,0000,0000,0000,,this pretty easily. PostGres has got it covered.
Dialogue: 0,0:05:19.59,0:05:24.39,Default,,0000,0000,0000,,The bad news is that, unless you've engineered\Nyour
Dialogue: 0,0:05:24.39,0:05:27.87,Default,,0000,0000,0000,,app to deal with this level of data, you're
Dialogue: 0,0:05:27.88,0:05:31.27,Default,,0000,0000,0000,,kind of screwed. And the reason is that a
Dialogue: 0,0:05:31.27,0:05:33.53,Default,,0000,0000,0000,,hundred megabyte database behaves
Dialogue: 0,0:05:33.56,0:05:36.42,Default,,0000,0000,0000,,fundamentally different than a one
Dialogue: 0,0:05:36.42,0:05:41.88,Default,,0000,0000,0000,,terabyte database. And a lot of the conventions,\Na
Dialogue: 0,0:05:41.88,0:05:43.33,Default,,0000,0000,0000,,lot of the normal things we do in Rails
Dialogue: 0,0:05:43.33,0:05:45.91,Default,,0000,0000,0000,,apps just stop working when you have this\Nmuch
Dialogue: 0,0:05:45.91,0:05:49.94,Default,,0000,0000,0000,,data.
Dialogue: 0,0:05:49.94,0:05:55.54,Default,,0000,0000,0000,,Yup. This is actually a live feed. This queries
Dialogue: 0,0:05:55.54,0:06:02.56,Default,,0000,0000,0000,,been running since this morning.
Dialogue: 0,0:06:03.70,0:06:06.74,Default,,0000,0000,0000,,Something I learned, pretty much every pagination\Nsystem breaks
Dialogue: 0,0:06:06.74,0:06:13.15,Default,,0000,0000,0000,,on page, like, 2000. Even if you, you're like,
Dialogue: 0,0:06:13.16,0:06:15.69,Default,,0000,0000,0000,,dear God, make this stop, I just want to
Dialogue: 0,0:06:15.69,0:06:19.05,Default,,0000,0000,0000,,delete half my data. You're, you're just still\Nscrewed,
Dialogue: 0,0:06:19.06,0:06:22.38,Default,,0000,0000,0000,,because deleting data takes a lot of time.
Dialogue: 0,0:06:22.40,0:06:25.84,Default,,0000,0000,0000,,Now, I'm gonna explain all of this. I'm gonna
Dialogue: 0,0:06:25.84,0:06:27.78,Default,,0000,0000,0000,,explain why this happens, how you can work\Naround
Dialogue: 0,0:06:27.78,0:06:30.79,Default,,0000,0000,0000,,it, and how you can optimize the queries in
Dialogue: 0,0:06:30.79,0:06:34.70,Default,,0000,0000,0000,,your own database and optimize your stack.\NBut, to
Dialogue: 0,0:06:34.70,0:06:36.76,Default,,0000,0000,0000,,really understand this, we've got to take\Na little
Dialogue: 0,0:06:36.76,0:06:43.76,Default,,0000,0000,0000,,trip back in time, to the summer of 1978.
Dialogue: 0,0:06:44.74,0:06:49.26,Default,,0000,0000,0000,,The summer of disco, free love, and the VT-100
Dialogue: 0,0:06:49.26,0:06:53.87,Default,,0000,0000,0000,,terminal. The VT-100 was the first sort of\Ncomputer-shaped
Dialogue: 0,0:06:53.87,0:06:56.04,Default,,0000,0000,0000,,thing that you could buy from an actual company
Dialogue: 0,0:06:56.04,0:06:58.55,Default,,0000,0000,0000,,and sit on your desk to prove that you
Dialogue: 0,0:06:58.56,0:07:02.11,Default,,0000,0000,0000,,were the alpha nerd of the office. It was
Dialogue: 0,0:07:02.11,0:07:05.15,Default,,0000,0000,0000,,also the time that Oracle 1 point 0 was
Dialogue: 0,0:07:05.15,0:07:08.73,Default,,0000,0000,0000,,being developed, which was one of the first\Ndatabases
Dialogue: 0,0:07:08.74,0:07:12.25,Default,,0000,0000,0000,,that used the SQL language. It's pretty cool.\NIt
Dialogue: 0,0:07:12.28,0:07:15.03,Default,,0000,0000,0000,,was written in PDP-11 assembly language.
Dialogue: 0,0:07:15.03,0:07:18.85,Default,,0000,0000,0000,,And, and in case you're like too young to
Dialogue: 0,0:07:18.85,0:07:23.22,Default,,0000,0000,0000,,have ever seen a PDP-11, this is what a
Dialogue: 0,0:07:23.22,0:07:29.64,Default,,0000,0000,0000,,PDP-11 looks like. Yeah. Give it, show some\Nlove
Dialogue: 0,0:07:29.66,0:07:32.57,Default,,0000,0000,0000,,for the PDP-11.
Dialogue: 0,0:07:32.57,0:07:39.47,Default,,0000,0000,0000,,Now, this is pretty awesome. Nowadays, in\Nour, like,
Dialogue: 0,0:07:39.48,0:07:43.03,Default,,0000,0000,0000,,modern day times, right this second, marketers\Ndown in
Dialogue: 0,0:07:43.03,0:07:46.08,Default,,0000,0000,0000,,the, the presentat- what, what do they call\Nthat?
Dialogue: 0,0:07:46.08,0:07:50.74,Default,,0000,0000,0000,,The exhibit hall. Are, right now, throwing\Naround all
Dialogue: 0,0:07:50.74,0:07:54.12,Default,,0000,0000,0000,,sorts of words about cloud computing and platform\Nas
Dialogue: 0,0:07:54.12,0:07:58.19,Default,,0000,0000,0000,,a service and, and I don't know, some, some
Dialogue: 0,0:07:58.20,0:08:00.97,Default,,0000,0000,0000,,stuff I haven't even heard of, probably. And\Nback
Dialogue: 0,0:08:00.98,0:08:02.77,Default,,0000,0000,0000,,then it was the same, but the, the buzz
Dialogue: 0,0:08:02.77,0:08:05.44,Default,,0000,0000,0000,,words were different. The buzz words on everybody's,\Nthe
Dialogue: 0,0:08:05.44,0:08:09.81,Default,,0000,0000,0000,,buzz word on everybody's lips, at that time,\Nwas
Dialogue: 0,0:08:09.82,0:08:11.66,Default,,0000,0000,0000,,real time computing.
Dialogue: 0,0:08:11.66,0:08:17.79,Default,,0000,0000,0000,,And I, I'm using air quotes around real time,
Dialogue: 0,0:08:17.79,0:08:21.66,Default,,0000,0000,0000,,because that meant that you could have your,\Nyour
Dialogue: 0,0:08:21.66,0:08:25.98,Default,,0000,0000,0000,,report generated in five minutes instead of\Nfive days.
Dialogue: 0,0:08:25.98,0:08:29.15,Default,,0000,0000,0000,,Which I'm sure was pretty awesome. Like, I\Nwould
Dialogue: 0,0:08:29.15,0:08:32.67,Default,,0000,0000,0000,,have been really happy about that. But, you\Nknow,
Dialogue: 0,0:08:32.68,0:08:34.04,Default,,0000,0000,0000,,looking at this, it doesn't really seem like\Nthe
Dialogue: 0,0:08:34.06,0:08:35.42,Default,,0000,0000,0000,,sort of thing I would want to build a
Dialogue: 0,0:08:35.44,0:08:38.32,Default,,0000,0000,0000,,web app on top of, right?
Dialogue: 0,0:08:38.32,0:08:43.60,Default,,0000,0000,0000,,Like, I. Yeah. I, I wouldn't stick Honeybadger\Non
Dialogue: 0,0:08:43.60,0:08:47.52,Default,,0000,0000,0000,,that. But, a funny thing happened in the past
Dialogue: 0,0:08:47.52,0:08:53.07,Default,,0000,0000,0000,,thirty, thirty-six years. A funny thing happened\Nis that
Dialogue: 0,0:08:53.07,0:08:57.20,Default,,0000,0000,0000,,Moore's Law made this into a web stack. Now,
Dialogue: 0,0:08:57.20,0:08:59.22,Default,,0000,0000,0000,,I know that everybody here knows sort of,\Nvaguely,
Dialogue: 0,0:08:59.22,0:09:00.86,Default,,0000,0000,0000,,what Moore's Law is. But I thought it would
Dialogue: 0,0:09:00.86,0:09:06.04,Default,,0000,0000,0000,,be good to go over the technical, precise\Ndefinition
Dialogue: 0,0:09:06.04,0:09:11.50,Default,,0000,0000,0000,,of Moore's Law.
Dialogue: 0,0:09:11.50,0:09:18.12,Default,,0000,0000,0000,,And that is that, whoa. That is that computers
Dialogue: 0,0:09:18.12,0:09:20.11,Default,,0000,0000,0000,,get more awesome as time goes forward. Did\Nthat
Dialogue: 0,0:09:20.12,0:09:23.48,Default,,0000,0000,0000,,come back normally? Yeah. As time goes forward.\NAnd
Dialogue: 0,0:09:23.48,0:09:27.80,Default,,0000,0000,0000,,awesomeness is generally defined as, well,\Ntechnically, being able
Dialogue: 0,0:09:27.80,0:09:30.73,Default,,0000,0000,0000,,to do more stuff faster. Being able to process
Dialogue: 0,0:09:30.73,0:09:37.72,Default,,0000,0000,0000,,more data faster. So, based on this, I would
Dialogue: 0,0:09:38.10,0:09:41.30,Default,,0000,0000,0000,,like to postulate, for the first time in public
Dialogue: 0,0:09:41.30,0:09:43.19,Default,,0000,0000,0000,,- you guys are very lucky to be able
Dialogue: 0,0:09:43.19,0:09:45.29,Default,,0000,0000,0000,,to hear this - the first time in public,
Dialogue: 0,0:09:45.29,0:09:49.35,Default,,0000,0000,0000,,Starr's Corelary to Moore's Law, which is,\Nas database
Dialogue: 0,0:09:49.35,0:09:56.34,Default,,0000,0000,0000,,growth outpaces Moore's Law, you literally\Ntravel back in
Dialogue: 0,0:09:57.10,0:10:00.39,Default,,0000,0000,0000,,time.
Dialogue: 0,0:10:00.39,0:10:01.94,Default,,0000,0000,0000,,That was the Ted moment of this, of this
Dialogue: 0,0:10:01.94,0:10:04.94,Default,,0000,0000,0000,,talk. It's like, I can hear you guys, your
Dialogue: 0,0:10:04.94,0:10:10.38,Default,,0000,0000,0000,,minds being blown. It sounds like bubble wrap\Npopping.
Dialogue: 0,0:10:10.38,0:10:16.57,Default,,0000,0000,0000,,So, based on my, my extensive research of\Ntime-travel
Dialogue: 0,0:10:16.57,0:10:21.36,Default,,0000,0000,0000,,movies, the, the first rule for surviving\Nyour new
Dialogue: 0,0:10:21.36,0:10:23.30,Default,,0000,0000,0000,,time period is that you need to understand\Nthe
Dialogue: 0,0:10:23.30,0:10:27.29,Default,,0000,0000,0000,,methods and the motivations of the people\Nin that
Dialogue: 0,0:10:27.29,0:10:29.24,Default,,0000,0000,0000,,time period. We need to start thinking like\Nour
Dialogue: 0,0:10:29.24,0:10:34.49,Default,,0000,0000,0000,,ancestors. And our ancestors were very interested\Nin this
Dialogue: 0,0:10:34.50,0:10:37.52,Default,,0000,0000,0000,,thing called hard ware.
Dialogue: 0,0:10:37.52,0:10:42.06,Default,,0000,0000,0000,,Well, I, I don't mean to be flip, but
Dialogue: 0,0:10:42.06,0:10:46.12,Default,,0000,0000,0000,,I just want to bring this up first, because
Dialogue: 0,0:10:46.12,0:10:49.39,Default,,0000,0000,0000,,if you happen to be having database scaling\Nissues,
Dialogue: 0,0:10:49.39,0:10:52.01,Default,,0000,0000,0000,,like, if your app right now is getting slower
Dialogue: 0,0:10:52.02,0:10:54.10,Default,,0000,0000,0000,,because the amount of data in your database\Nis
Dialogue: 0,0:10:54.10,0:10:57.95,Default,,0000,0000,0000,,getting bigger, and your app happens to live\Nanywhere
Dialogue: 0,0:10:57.98,0:11:04.36,Default,,0000,0000,0000,,named EC-2, Linode, I don't know, whatever,\Nwhatever other
Dialogue: 0,0:11:04.36,0:11:08.12,Default,,0000,0000,0000,,companies offer that, you can probably solve\Nyour scaling
Dialogue: 0,0:11:08.12,0:11:10.56,Default,,0000,0000,0000,,issue just, like, right now just by going\Nand
Dialogue: 0,0:11:10.56,0:11:13.02,Default,,0000,0000,0000,,buying a real damn computer.
Dialogue: 0,0:11:13.02,0:11:19.73,Default,,0000,0000,0000,,Yeah. You'll be shocked and amazed. Because\Nthe two
Dialogue: 0,0:11:19.73,0:11:23.97,Default,,0000,0000,0000,,things that databases need to perform well,\Nto work
Dialogue: 0,0:11:23.98,0:11:26.33,Default,,0000,0000,0000,,at high performance with lots of data, is\Nthey
Dialogue: 0,0:11:26.33,0:11:29.00,Default,,0000,0000,0000,,need lots of ram, and they need really fast
Dialogue: 0,0:11:29.00,0:11:32.10,Default,,0000,0000,0000,,disc io. And virtual servers give you neither\Nof
Dialogue: 0,0:11:32.10,0:11:35.04,Default,,0000,0000,0000,,those things. So, just go buy a real damn
Dialogue: 0,0:11:35.04,0:11:39.03,Default,,0000,0000,0000,,computer. Yeah. And, and while you're at it,\Nthrow
Dialogue: 0,0:11:39.03,0:11:42.51,Default,,0000,0000,0000,,in a bunch of discs.
Dialogue: 0,0:11:42.51,0:11:45.36,Default,,0000,0000,0000,,I'm not talking about a radar ray. You probably
Dialogue: 0,0:11:45.36,0:11:48.28,Default,,0000,0000,0000,,want each of your, you probably want each\Nof
Dialogue: 0,0:11:48.28,0:11:53.24,Default,,0000,0000,0000,,your operating system, your PostGres data,\Nyour PostGres log
Dialogue: 0,0:11:53.24,0:11:55.23,Default,,0000,0000,0000,,file, you probably want all that stuff to\Nbe
Dialogue: 0,0:11:55.24,0:11:58.26,Default,,0000,0000,0000,,on separate hard drives. And that's just gonna\Nmake
Dialogue: 0,0:11:58.28,0:12:02.59,Default,,0000,0000,0000,,the operating system able to more efficiently\Nsort of
Dialogue: 0,0:12:02.59,0:12:04.24,Default,,0000,0000,0000,,schedule disc io.
Dialogue: 0,0:12:04.24,0:12:09.94,Default,,0000,0000,0000,,So, if, if that solves your problems, you\Nknow,
Dialogue: 0,0:12:09.94,0:12:13.91,Default,,0000,0000,0000,,great. You're welcome. If you guys want to\Nleave
Dialogue: 0,0:12:13.91,0:12:16.83,Default,,0000,0000,0000,,the room now, that's fine. I won't be offended.
Dialogue: 0,0:12:16.83,0:12:19.46,Default,,0000,0000,0000,,You can go call your local Colo facility.\NWork
Dialogue: 0,0:12:19.46,0:12:22.78,Default,,0000,0000,0000,,something out. If, if that doesn't solve your\Nproblem,
Dialogue: 0,0:12:22.78,0:12:24.100,Default,,0000,0000,0000,,or even if it does, you probably want to
Dialogue: 0,0:12:25.00,0:12:28.88,Default,,0000,0000,0000,,look at your queries next.
Dialogue: 0,0:12:28.88,0:12:33.74,Default,,0000,0000,0000,,Now, most people, most of us develop - oops.
Dialogue: 0,0:12:33.74,0:12:38.84,Default,,0000,0000,0000,,Hey. Most of us develop against smallish datasets.\NSo,
Dialogue: 0,0:12:38.86,0:12:40.77,Default,,0000,0000,0000,,when you develop against a small dataset,\Nyou don't
Dialogue: 0,0:12:40.77,0:12:44.97,Default,,0000,0000,0000,,notice inefficient queries. It's just life.\NAnd books have
Dialogue: 0,0:12:44.98,0:12:48.25,Default,,0000,0000,0000,,been written about query optimization. It's,\Nit's a very
Dialogue: 0,0:12:48.26,0:12:52.56,Default,,0000,0000,0000,,huge topic, and I can't ev, explain everything\Nin
Dialogue: 0,0:12:52.56,0:12:54.20,Default,,0000,0000,0000,,thirty minutes. So I'm just going to explain\None
Dialogue: 0,0:12:54.20,0:12:57.36,Default,,0000,0000,0000,,thing, and that's called a, a explain.
Dialogue: 0,0:12:57.36,0:13:02.55,Default,,0000,0000,0000,,Fortunately, PostGres gives us an awesome\Ntool called Explain,
Dialogue: 0,0:13:02.56,0:13:06.09,Default,,0000,0000,0000,,which basically you pass it a query and it
Dialogue: 0,0:13:06.14,0:13:13.14,Default,,0000,0000,0000,,gives us a query plan. Oh it, wait a
Dialogue: 0,0:13:16.08,0:13:19.33,Default,,0000,0000,0000,,second. That's a, that's a chapter from the\NIliad.
Dialogue: 0,0:13:19.33,0:13:23.84,Default,,0000,0000,0000,,Sorry. It gives us a query plan, which still
Dialogue: 0,0:13:23.84,0:13:26.23,Default,,0000,0000,0000,,kind of looks like a chapter from the Iliad.
Dialogue: 0,0:13:26.24,0:13:29.11,Default,,0000,0000,0000,,But, fortunately, we only have to, to look\Nat
Dialogue: 0,0:13:29.11,0:13:31.08,Default,,0000,0000,0000,,one metric here. The only thing we need to
Dialogue: 0,0:13:31.10,0:13:37.16,Default,,0000,0000,0000,,worry about is rows. For this talk.
Dialogue: 0,0:13:37.16,0:13:38.56,Default,,0000,0000,0000,,And here we have an example of a very
Dialogue: 0,0:13:38.56,0:13:42.30,Default,,0000,0000,0000,,efficient query, right. It's gonna use an\Nindex, and
Dialogue: 0,0:13:42.30,0:13:44.88,Default,,0000,0000,0000,,it's gonna look at one row. That's pretty\Nsweet.
Dialogue: 0,0:13:44.88,0:13:46.97,Default,,0000,0000,0000,,Very fast. This is the type of query that
Dialogue: 0,0:13:46.97,0:13:51.24,Default,,0000,0000,0000,,biggish data works with.
Dialogue: 0,0:13:51.24,0:13:52.56,Default,,0000,0000,0000,,But there's one thing you need to know about
Dialogue: 0,0:13:52.56,0:13:59.34,Default,,0000,0000,0000,,rows. And that, obviously, the more rows you're\Ndealing
Dialogue: 0,0:13:59.36,0:14:01.68,Default,,0000,0000,0000,,with, the more data your computer has to crunch
Dialogue: 0,0:14:01.68,0:14:04.17,Default,,0000,0000,0000,,to give you the answers you want. And so
Dialogue: 0,0:14:04.17,0:14:06.60,Default,,0000,0000,0000,,the whole, the whole name of the game in
Dialogue: 0,0:14:06.60,0:14:08.92,Default,,0000,0000,0000,,query optimization is to limit the number\Nof rows
Dialogue: 0,0:14:08.92,0:14:10.26,Default,,0000,0000,0000,,that you have to touch.
Dialogue: 0,0:14:10.26,0:14:15.72,Default,,0000,0000,0000,,Let's go back to that inefficient count query.\NSo
Dialogue: 0,0:14:15.72,0:14:17.86,Default,,0000,0000,0000,,if you run Explain on that, it turns out
Dialogue: 0,0:14:17.86,0:14:21.66,Default,,0000,0000,0000,,that when you ask PostGres to count all of
Dialogue: 0,0:14:21.66,0:14:25.17,Default,,0000,0000,0000,,the tab, all of the rows in a table,
Dialogue: 0,0:14:25.17,0:14:27.05,Default,,0000,0000,0000,,it actually literally counts all the rows\Nin the
Dialogue: 0,0:14:27.06,0:14:32.44,Default,,0000,0000,0000,,table, one by one.
Dialogue: 0,0:14:32.44,0:14:35.01,Default,,0000,0000,0000,,And so you wind up with a, one of
Dialogue: 0,0:14:35.01,0:14:40.58,Default,,0000,0000,0000,,these things. It's not pretty. And it's often\Nhard
Dialogue: 0,0:14:40.58,0:14:42.80,Default,,0000,0000,0000,,to know when stuff like this is gonna crop
Dialogue: 0,0:14:42.80,0:14:46.96,Default,,0000,0000,0000,,up. For example, I mentioned that most pagination\Nsystems
Dialogue: 0,0:14:46.96,0:14:51.57,Default,,0000,0000,0000,,break at page 1000 or 2000. And that's because,
Dialogue: 0,0:14:51.57,0:14:58.28,Default,,0000,0000,0000,,well, the offset and limit operators work\Na lot
Dialogue: 0,0:14:58.30,0:15:00.27,Default,,0000,0000,0000,,like count, in that, if you do an offset
Dialogue: 0,0:15:00.27,0:15:05.01,Default,,0000,0000,0000,,of 500,000, PostGres is going to count up\N500,000
Dialogue: 0,0:15:05.01,0:15:06.58,Default,,0000,0000,0000,,rows, and then if you have a limit of
Dialogue: 0,0:15:06.60,0:15:08.74,Default,,0000,0000,0000,,100, it's gonna count up another 100. And\Nso
Dialogue: 0,0:15:08.74,0:15:11.65,Default,,0000,0000,0000,,what you see is that pages one, two, and
Dialogue: 0,0:15:11.65,0:15:16.84,Default,,0000,0000,0000,,three load extremely quickly. And so you,\Nas a
Dialogue: 0,0:15:16.84,0:15:18.99,Default,,0000,0000,0000,,developer, are actually, when I say you I\Nmean
Dialogue: 0,0:15:19.00,0:15:22.21,Default,,0000,0000,0000,,me, cause I actually, I actually fell into\Nthis
Dialogue: 0,0:15:22.21,0:15:24.77,Default,,0000,0000,0000,,trap. You test it with, like, one or two
Dialogue: 0,0:15:24.77,0:15:27.100,Default,,0000,0000,0000,,pages and it works fine, so you move on
Dialogue: 0,0:15:28.02,0:15:29.89,Default,,0000,0000,0000,,and then later on your customers are complaining\Nbecause
Dialogue: 0,0:15:29.89,0:15:34.41,Default,,0000,0000,0000,,they can't access page 500. It's timing out.
Dialogue: 0,0:15:34.42,0:15:38.73,Default,,0000,0000,0000,,The solution to this is to use a range
Dialogue: 0,0:15:38.73,0:15:41.76,Default,,0000,0000,0000,,query. Range queries are very fast, and I'm\Ngonna
Dialogue: 0,0:15:41.76,0:15:44.65,Default,,0000,0000,0000,,give you a really stupid example here. Here's\Na
Dialogue: 0,0:15:44.66,0:15:51.56,Default,,0000,0000,0000,,link with a much more detailed analysis of\Nall
Dialogue: 0,0:15:51.56,0:15:55.10,Default,,0000,0000,0000,,this. But essentially, if you do a range query,
Dialogue: 0,0:15:55.10,0:15:56.59,Default,,0000,0000,0000,,you're gonna be able to use an index. It's
Dialogue: 0,0:15:56.59,0:15:58.70,Default,,0000,0000,0000,,gonna touch 100 rows and it's gonna be really
Dialogue: 0,0:15:58.70,0:15:59.55,Default,,0000,0000,0000,,fast.
Dialogue: 0,0:15:59.55,0:16:02.62,Default,,0000,0000,0000,,Now, I know what you guys are thinking. That's
Dialogue: 0,0:16:02.62,0:16:06.25,Default,,0000,0000,0000,,not the same as an offset limit query. And
Dialogue: 0,0:16:06.28,0:16:07.63,Default,,0000,0000,0000,,that's the reason I gave you the link in
Dialogue: 0,0:16:07.63,0:16:10.44,Default,,0000,0000,0000,,the previous slide, so.
Dialogue: 0,0:16:10.44,0:16:14.89,Default,,0000,0000,0000,,Sorting. Sorting is tricky. Sorting is the\Ndevil. Sometimes
Dialogue: 0,0:16:14.96,0:16:17.14,Default,,0000,0000,0000,,it's super fast. Sometimes if you happen to\Nbe
Dialogue: 0,0:16:17.14,0:16:20.73,Default,,0000,0000,0000,,asking for data sorted in exactly the same\Nway
Dialogue: 0,0:16:20.74,0:16:23.80,Default,,0000,0000,0000,,that an index is sorted, well, it'll be super
Dialogue: 0,0:16:23.80,0:16:26.58,Default,,0000,0000,0000,,fast. Other times, even if you have an index
Dialogue: 0,0:16:26.58,0:16:29.02,Default,,0000,0000,0000,,on a column, if it's not set up right
Dialogue: 0,0:16:29.02,0:16:30.91,Default,,0000,0000,0000,,or if you're asking for the data in a
Dialogue: 0,0:16:30.91,0:16:34.02,Default,,0000,0000,0000,,slightly unusual way, you'll wind up sorting\Nthe whole
Dialogue: 0,0:16:34.02,0:16:35.20,Default,,0000,0000,0000,,damn dataset.
Dialogue: 0,0:16:35.20,0:16:40.62,Default,,0000,0000,0000,,It's no good. So here's your rundown for query
Dialogue: 0,0:16:40.62,0:16:44.37,Default,,0000,0000,0000,,optimization. You want to develop against\Na real dataset,
Dialogue: 0,0:16:44.37,0:16:46.09,Default,,0000,0000,0000,,because otherwise you won't know when things\Nare going
Dialogue: 0,0:16:46.09,0:16:50.90,Default,,0000,0000,0000,,wrong until they go wrong in production. Use,\Nuse
Dialogue: 0,0:16:50.92,0:16:53.62,Default,,0000,0000,0000,,Explain. Use it a lot. And the whole name
Dialogue: 0,0:16:53.62,0:16:54.95,Default,,0000,0000,0000,,of the game here is to limit the number
Dialogue: 0,0:16:54.96,0:16:57.42,Default,,0000,0000,0000,,of rows that you touch. Because the more rows
Dialogue: 0,0:16:57.42,0:16:59.74,Default,,0000,0000,0000,,you touch, the slower your queries are. You\Nknow,
Dialogue: 0,0:16:59.74,0:17:01.58,Default,,0000,0000,0000,,in general.
Dialogue: 0,0:17:01.58,0:17:06.34,Default,,0000,0000,0000,,So, I, I don't know about you, but that
Dialogue: 0,0:17:06.34,0:17:08.89,Default,,0000,0000,0000,,just like, that just was a lot of cognitive
Dialogue: 0,0:17:08.89,0:17:12.10,Default,,0000,0000,0000,,load happening, right up here. So I want to
Dialogue: 0,0:17:12.10,0:17:19.10,Default,,0000,0000,0000,,give you guys a cookie.
Dialogue: 0,0:17:24.44,0:17:27.41,Default,,0000,0000,0000,,For the second half of this talk, we're gonna
Dialogue: 0,0:17:27.42,0:17:30.95,Default,,0000,0000,0000,,cover a lot of issues that relate to kind
Dialogue: 0,0:17:30.96,0:17:35.53,Default,,0000,0000,0000,,of the infrastructure around biggish data,\Naround big datasets
Dialogue: 0,0:17:35.53,0:17:41.07,Default,,0000,0000,0000,,and firehoses of data coming in. And here's\Na,
Dialogue: 0,0:17:41.08,0:17:44.39,Default,,0000,0000,0000,,a legend of, of how we're gonna approach this.
Dialogue: 0,0:17:44.39,0:17:48.10,Default,,0000,0000,0000,,We're gonna have a cute picture that, hopefully,\Nsort
Dialogue: 0,0:17:48.12,0:17:50.95,Default,,0000,0000,0000,,of symbolizes something about the, the topic.\NWe're gonna
Dialogue: 0,0:17:50.96,0:17:52.72,Default,,0000,0000,0000,,have the name of the topic, and then we're
Dialogue: 0,0:17:52.72,0:17:55.17,Default,,0000,0000,0000,,gonna have a link with more info about the
Dialogue: 0,0:17:55.17,0:17:57.68,Default,,0000,0000,0000,,topic if, if you want to, you know, find
Dialogue: 0,0:17:57.68,0:17:58.25,Default,,0000,0000,0000,,this later.
Dialogue: 0,0:17:58.25,0:17:59.55,Default,,0000,0000,0000,,And you don't have to write all these links
Dialogue: 0,0:17:59.56,0:18:02.77,Default,,0000,0000,0000,,down right now. At the end of the presentation,
Dialogue: 0,0:18:02.77,0:18:05.89,Default,,0000,0000,0000,,at the very last slide, I'll have a url
Dialogue: 0,0:18:05.89,0:18:07.92,Default,,0000,0000,0000,,where you can get a list of all of
Dialogue: 0,0:18:07.92,0:18:11.58,Default,,0000,0000,0000,,them. And, yeah, and I promise this isn't\Nmalware.
Dialogue: 0,0:18:11.58,0:18:16.44,Default,,0000,0000,0000,,So, you just gotta take my word on that.
Dialogue: 0,0:18:16.44,0:18:19.35,Default,,0000,0000,0000,,Our ancestors were really interested in disc\Noperating systems.
Dialogue: 0,0:18:19.38,0:18:21.31,Default,,0000,0000,0000,,It seems like they were building a new disc
Dialogue: 0,0:18:21.31,0:18:25.11,Default,,0000,0000,0000,,operating system like every two seconds, but.\NWhat, what's
Dialogue: 0,0:18:25.11,0:18:26.62,Default,,0000,0000,0000,,up with that? I, I don't know. Now we
Dialogue: 0,0:18:26.64,0:18:28.86,Default,,0000,0000,0000,,have, like, three.
Dialogue: 0,0:18:28.86,0:18:30.08,Default,,0000,0000,0000,,The first thing that you should do if you're
Dialogue: 0,0:18:30.08,0:18:33.86,Default,,0000,0000,0000,,using Linux is to increase your read-ahead\Ncache. A
Dialogue: 0,0:18:33.86,0:18:37.43,Default,,0000,0000,0000,,read-ahead cache is something that, well,\NI found not,
Dialogue: 0,0:18:37.43,0:18:39.61,Default,,0000,0000,0000,,not a ton of people know about, but it's
Dialogue: 0,0:18:39.62,0:18:44.16,Default,,0000,0000,0000,,a super easy way to get up to a
Dialogue: 0,0:18:44.16,0:18:49.67,Default,,0000,0000,0000,,doubling of your read-throughput. And essentially\Nwhat this means
Dialogue: 0,0:18:49.67,0:18:54.79,Default,,0000,0000,0000,,is that Linux examines the request that you\Nmake
Dialogue: 0,0:18:54.79,0:18:57.23,Default,,0000,0000,0000,,to the, to the disc. And if it sees
Dialogue: 0,0:18:57.24,0:18:59.66,Default,,0000,0000,0000,,that you're asking for lots of blocks that\Ncome
Dialogue: 0,0:18:59.66,0:19:02.07,Default,,0000,0000,0000,,right after another in a row, it's gonna preload
Dialogue: 0,0:19:02.08,0:19:04.90,Default,,0000,0000,0000,,the next set of blocks into RAM.
Dialogue: 0,0:19:04.90,0:19:09.65,Default,,0000,0000,0000,,The normal, the, the default for this is,\Nlike,
Dialogue: 0,0:19:09.65,0:19:14.70,Default,,0000,0000,0000,,256K RAM that it uses to prefix these blocks.
Dialogue: 0,0:19:14.70,0:19:17.11,Default,,0000,0000,0000,,If you update this to use, like, two megs,
Dialogue: 0,0:19:17.11,0:19:19.72,Default,,0000,0000,0000,,four megs, you'll get a really big increase\Nin
Dialogue: 0,0:19:19.72,0:19:25.05,Default,,0000,0000,0000,,read performance. Use a modern file system.\NThat means
Dialogue: 0,0:19:25.06,0:19:29.84,Default,,0000,0000,0000,,EXT-3 is not an option. If you want to
Dialogue: 0,0:19:29.84,0:19:33.05,Default,,0000,0000,0000,,know why, check out the link.
Dialogue: 0,0:19:33.06,0:19:37.74,Default,,0000,0000,0000,,And, and if you happen to be using EXT-4
Dialogue: 0,0:19:37.74,0:19:42.92,Default,,0000,0000,0000,,or XFS. I can never say that unless I
Dialogue: 0,0:19:42.92,0:19:45.57,Default,,0000,0000,0000,,say it super slow. You might want to consider,
Dialogue: 0,0:19:45.57,0:19:47.97,Default,,0000,0000,0000,,you might want to look into journaling settings.\NIf
Dialogue: 0,0:19:47.97,0:19:50.05,Default,,0000,0000,0000,,you have your database on a completely separate\Nhard
Dialogue: 0,0:19:50.06,0:19:55.38,Default,,0000,0000,0000,,drive, and it's running EXT-4 and you have\Nfull
Dialogue: 0,0:19:55.38,0:20:00.04,Default,,0000,0000,0000,,data journaling enabled, since PostGres does\Nits own journaling,
Dialogue: 0,0:20:00.04,0:20:01.83,Default,,0000,0000,0000,,you're gonna have an inefficiency there because\Nyou have
Dialogue: 0,0:20:01.84,0:20:03.12,Default,,0000,0000,0000,,two things journaling.
Dialogue: 0,0:20:03.12,0:20:08.95,Default,,0000,0000,0000,,Finally, or, wait. Not finally. Anyway. You\Ngotta tell
Dialogue: 0,0:20:08.95,0:20:14.99,Default,,0000,0000,0000,,PG, tell, tell Paul Gram about, about all\Nthe
Dialogue: 0,0:20:15.00,0:20:17.58,Default,,0000,0000,0000,,RAM that you bought in that, that fancy new
Dialogue: 0,0:20:17.58,0:20:20.29,Default,,0000,0000,0000,,box. A, a really easy way to set this
Dialogue: 0,0:20:20.29,0:20:24.00,Default,,0000,0000,0000,,up is to use a PG-tune script. It examines
Dialogue: 0,0:20:24.00,0:20:28.82,Default,,0000,0000,0000,,your computer and writes a, a PostGres configuration\Nfile
Dialogue: 0,0:20:28.82,0:20:33.46,Default,,0000,0000,0000,,that has some pretty, pretty reasonable values\Nin it.
Dialogue: 0,0:20:33.46,0:20:36.15,Default,,0000,0000,0000,,And you can tweak them from there. This stuff
Dialogue: 0,0:20:36.16,0:20:38.07,Default,,0000,0000,0000,,is all really easy to find.
Dialogue: 0,0:20:38.07,0:20:41.47,Default,,0000,0000,0000,,And then, finally, the bane of, of DB administrators
Dialogue: 0,0:20:41.47,0:20:47.22,Default,,0000,0000,0000,,is the vacuum command. PostGres needs to vacuum,\Nbecause
Dialogue: 0,0:20:47.24,0:20:51.06,Default,,0000,0000,0000,,it's messy. It. When you, when you run queries,
Dialogue: 0,0:20:51.06,0:20:53.36,Default,,0000,0000,0000,,when you delete things, when you update things,\Nit
Dialogue: 0,0:20:53.36,0:20:55.25,Default,,0000,0000,0000,,leaves sort of a lot of stuff undone. And
Dialogue: 0,0:20:55.25,0:20:59.97,Default,,0000,0000,0000,,it does that, it does that in the, to,
Dialogue: 0,0:20:59.97,0:21:03.67,Default,,0000,0000,0000,,it. In the name of speed, right. It only
Dialogue: 0,0:21:03.68,0:21:05.59,Default,,0000,0000,0000,,does what is necessary at the time of query
Dialogue: 0,0:21:05.59,0:21:08.82,Default,,0000,0000,0000,,to get you an answer to your query.
Dialogue: 0,0:21:08.82,0:21:10.72,Default,,0000,0000,0000,,And then it uses vacuum to go and sort
Dialogue: 0,0:21:10.72,0:21:14.51,Default,,0000,0000,0000,,of clean up after itself. The problem is that
Dialogue: 0,0:21:14.51,0:21:18.67,Default,,0000,0000,0000,,vacuum can be really resource intensive. And\Nso if
Dialogue: 0,0:21:18.68,0:21:21.61,Default,,0000,0000,0000,,your, if your server is under a lot of
Dialogue: 0,0:21:21.62,0:21:24.85,Default,,0000,0000,0000,,load, and you see that, OK, vacuum is also
Dialogue: 0,0:21:24.86,0:21:26.74,Default,,0000,0000,0000,,causing a lot of load, you may be tempted
Dialogue: 0,0:21:26.74,0:21:30.98,Default,,0000,0000,0000,,to turn off vacuum or to, or to make
Dialogue: 0,0:21:30.98,0:21:34.56,Default,,0000,0000,0000,,autovacuum happen maybe once a night or something.\NAnd
Dialogue: 0,0:21:34.56,0:21:38.62,Default,,0000,0000,0000,,that's generally a bad idea. We actually got\Nbitten
Dialogue: 0,0:21:38.62,0:21:41.39,Default,,0000,0000,0000,,by this ourselves, so that's why I bring it
Dialogue: 0,0:21:41.39,0:21:43.51,Default,,0000,0000,0000,,up. Usually the answer to vacuum problems\Nis to
Dialogue: 0,0:21:43.51,0:21:46.44,Default,,0000,0000,0000,,vacuum more often, not less often.
Dialogue: 0,0:21:46.44,0:21:51.74,Default,,0000,0000,0000,,All right. So. Velocity. I really wanted to\Nlike
Dialogue: 0,0:21:51.74,0:21:52.92,Default,,0000,0000,0000,,- I didn't have time - I really wanted
Dialogue: 0,0:21:52.92,0:21:56.52,Default,,0000,0000,0000,,to have a little, a little like, Tron guy
Dialogue: 0,0:21:56.52,0:22:00.60,Default,,0000,0000,0000,,on the motorcycle going down the little grid\Nthere.
Dialogue: 0,0:22:00.60,0:22:02.26,Default,,0000,0000,0000,,But. Eh.
Dialogue: 0,0:22:02.28,0:22:05.40,Default,,0000,0000,0000,,Now we're gonna talk about a, a couple things
Dialogue: 0,0:22:05.40,0:22:06.94,Default,,0000,0000,0000,,that are important if you have a ton of
Dialogue: 0,0:22:06.94,0:22:11.23,Default,,0000,0000,0000,,data coming in, or a ton of read-requests\Ncoming
Dialogue: 0,0:22:11.23,0:22:13.97,Default,,0000,0000,0000,,in, a ton of queries coming in. The first
Dialogue: 0,0:22:13.97,0:22:16.55,Default,,0000,0000,0000,,is too many database connections. Each database\Nconnection in
Dialogue: 0,0:22:16.56,0:22:21.16,Default,,0000,0000,0000,,PostGres is its own process, and each process\Nhas
Dialogue: 0,0:22:21.16,0:22:23.58,Default,,0000,0000,0000,,its own RAM overhead. So there's a limited\Nnumber
Dialogue: 0,0:22:23.58,0:22:25.68,Default,,0000,0000,0000,,of connections that you want to have to your,
Dialogue: 0,0:22:25.68,0:22:27.11,Default,,0000,0000,0000,,your database server.
Dialogue: 0,0:22:27.11,0:22:31.56,Default,,0000,0000,0000,,If you have, I don't know, a thousand workers
Dialogue: 0,0:22:31.56,0:22:35.16,Default,,0000,0000,0000,,and, and web app processes and all this, you
Dialogue: 0,0:22:35.16,0:22:37.95,Default,,0000,0000,0000,,don't want to open a thousand database connections\Nand
Dialogue: 0,0:22:37.96,0:22:40.55,Default,,0000,0000,0000,,you probably already know about this. The\Nsolution is
Dialogue: 0,0:22:40.55,0:22:42.27,Default,,0000,0000,0000,,to pool connections.
Dialogue: 0,0:22:42.28,0:22:44.63,Default,,0000,0000,0000,,There's ways to do this in Ruby. There's also,
Dialogue: 0,0:22:44.63,0:22:47.50,Default,,0000,0000,0000,,if you're interested in a more ops-y approach,\Nyou
Dialogue: 0,0:22:47.52,0:22:50.11,Default,,0000,0000,0000,,can check out something like PG-bouncer, which\Nis a
Dialogue: 0,0:22:50.11,0:22:54.26,Default,,0000,0000,0000,,proxy that sits in between your Ruby app and
Dialogue: 0,0:22:54.26,0:22:58.09,Default,,0000,0000,0000,,your database and functions as a, as a connection
Dialogue: 0,0:22:58.09,0:23:01.02,Default,,0000,0000,0000,,pool.
Dialogue: 0,0:23:01.02,0:23:02.85,Default,,0000,0000,0000,,You can also run into problems with too many
Dialogue: 0,0:23:02.85,0:23:05.70,Default,,0000,0000,0000,,locks. And this is, this is the sort of
Dialogue: 0,0:23:05.70,0:23:08.19,Default,,0000,0000,0000,,problem that you don't really ever run into\Nif
Dialogue: 0,0:23:08.20,0:23:10.65,Default,,0000,0000,0000,,you don't have a firehose of database, of\Ndata
Dialogue: 0,0:23:10.65,0:23:15.79,Default,,0000,0000,0000,,coming in.
Dialogue: 0,0:23:15.80,0:23:17.83,Default,,0000,0000,0000,,I don't expect you to know, like, everything\Nabout
Dialogue: 0,0:23:17.84,0:23:19.83,Default,,0000,0000,0000,,locks, because it's a, it's a pretty complex\Ntopic.
Dialogue: 0,0:23:19.84,0:23:23.88,Default,,0000,0000,0000,,But you should know that, within a transaction,\Nif
Dialogue: 0,0:23:23.88,0:23:28.19,Default,,0000,0000,0000,,you go to update a row, that transaction is
Dialogue: 0,0:23:28.19,0:23:31.04,Default,,0000,0000,0000,,gonna put a lock on the row until it's
Dialogue: 0,0:23:31.04,0:23:32.89,Default,,0000,0000,0000,,done. It's gonna say that nothing else can\Nwrite
Dialogue: 0,0:23:32.89,0:23:37.05,Default,,0000,0000,0000,,to that row until it's done. And, you know,
Dialogue: 0,0:23:37.06,0:23:39.19,Default,,0000,0000,0000,,that makes sense.
Dialogue: 0,0:23:39.20,0:23:40.91,Default,,0000,0000,0000,,But where this can bite you is if you
Dialogue: 0,0:23:40.91,0:23:44.95,Default,,0000,0000,0000,,have, say, imagine you have two Rails models.\NYou
Dialogue: 0,0:23:44.96,0:23:47.77,Default,,0000,0000,0000,,have a parent model and a child model. And
Dialogue: 0,0:23:47.77,0:23:50.41,Default,,0000,0000,0000,,the parent model has a counter-cache that\Ngets incremented
Dialogue: 0,0:23:50.42,0:23:53.79,Default,,0000,0000,0000,,every time you add a child. Normally, this\Nis
Dialogue: 0,0:23:53.79,0:23:55.74,Default,,0000,0000,0000,,no big deal. People do this sort of thing
Dialogue: 0,0:23:55.74,0:24:00.39,Default,,0000,0000,0000,,all the time. But if something crazy happens\Nand
Dialogue: 0,0:24:00.39,0:24:05.30,Default,,0000,0000,0000,,someone, you know, bombards your API and suddenly\Nyou
Dialogue: 0,0:24:05.30,0:24:09.70,Default,,0000,0000,0000,,have, like, fifteen thousand children created,\Nyou're, you're gonna
Dialogue: 0,0:24:09.70,0:24:11.04,Default,,0000,0000,0000,,have some locking issues.
Dialogue: 0,0:24:11.04,0:24:13.41,Default,,0000,0000,0000,,Cause what's gonna happen is your first query\Nis
Dialogue: 0,0:24:13.41,0:24:17.32,Default,,0000,0000,0000,,going to execute fine. The child's gonna get\Ncreated.
Dialogue: 0,0:24:17.34,0:24:19.92,Default,,0000,0000,0000,,It's gonna increment the counter. Everything's\Ngonna be fine.
Dialogue: 0,0:24:19.92,0:24:22.41,Default,,0000,0000,0000,,And while it was doing that, it put a
Dialogue: 0,0:24:22.41,0:24:26.55,Default,,0000,0000,0000,,lock on, on that row. And so, once the
Dialogue: 0,0:24:26.56,0:24:29.26,Default,,0000,0000,0000,,first query's done, the lock is, is removed,\Nand
Dialogue: 0,0:24:29.26,0:24:33.92,Default,,0000,0000,0000,,the second query happens. And if you had infinite
Dialogue: 0,0:24:33.92,0:24:35.96,Default,,0000,0000,0000,,time, like, this would all be fine. It would
Dialogue: 0,0:24:35.96,0:24:39.40,Default,,0000,0000,0000,,all eventually work itself out. But what happens\Nis
Dialogue: 0,0:24:39.42,0:24:42.17,Default,,0000,0000,0000,,that by the time you finish query 100, query
Dialogue: 0,0:24:42.17,0:24:47.70,Default,,0000,0000,0000,,15, 15,000 has timed out, which causes all\Nsorts
Dialogue: 0,0:24:47.70,0:24:50.88,Default,,0000,0000,0000,,of fun in your, you know, in your Unicorns
Dialogue: 0,0:24:50.88,0:24:54.91,Default,,0000,0000,0000,,and your Sidekiqs and all that. It's a, yeah,
Dialogue: 0,0:24:54.92,0:24:56.52,Default,,0000,0000,0000,,it's a huge pain in the neck.
Dialogue: 0,0:24:56.52,0:25:01.12,Default,,0000,0000,0000,,And the way you avoid this is to, it,
Dialogue: 0,0:25:01.12,0:25:04.13,Default,,0000,0000,0000,,it's just an architectural thing. You just\Nhave to
Dialogue: 0,0:25:04.13,0:25:07.09,Default,,0000,0000,0000,,avoid any situation where you could be updating\Nthe
Dialogue: 0,0:25:07.10,0:25:11.67,Default,,0000,0000,0000,,same record in the database like a gillion\Ntimes
Dialogue: 0,0:25:11.67,0:25:16.14,Default,,0000,0000,0000,,per second.
Dialogue: 0,0:25:16.14,0:25:21.39,Default,,0000,0000,0000,,Intensive database queries. Like, sometimes,\Nsometimes we have our
Dialogue: 0,0:25:21.39,0:25:25.84,Default,,0000,0000,0000,,production database, and we need that to be\Nvery,
Dialogue: 0,0:25:25.86,0:25:29.67,Default,,0000,0000,0000,,very performant for our users. But we also\Nneed
Dialogue: 0,0:25:29.68,0:25:31.91,Default,,0000,0000,0000,,to maybe do some preprocessing on data as\Nit
Dialogue: 0,0:25:31.91,0:25:34.32,Default,,0000,0000,0000,,comes in. And a really simple way to do
Dialogue: 0,0:25:34.32,0:25:38.90,Default,,0000,0000,0000,,this is to use PostGres's streaming replication\Nfacilities to
Dialogue: 0,0:25:38.90,0:25:42.96,Default,,0000,0000,0000,,create a read-only replicant. And then you\Njust do
Dialogue: 0,0:25:42.96,0:25:46.16,Default,,0000,0000,0000,,your intensive queries against that and it\Ndoesn't affect,
Dialogue: 0,0:25:46.16,0:25:49.92,Default,,0000,0000,0000,,affect your users. It's super simple. Sorry.\NI, I,
Dialogue: 0,0:25:49.92,0:25:53.30,Default,,0000,0000,0000,,I kind of feel dumb even talking about it
Dialogue: 0,0:25:53.30,0:25:56.40,Default,,0000,0000,0000,,here. But, there you go.
Dialogue: 0,0:25:56.40,0:25:59.21,Default,,0000,0000,0000,,Partitioning is awesome. Partitioning is like\Nthe best thing
Dialogue: 0,0:25:59.21,0:26:05.23,Default,,0000,0000,0000,,ever, because it allows you to dramatically\Nspeed up
Dialogue: 0,0:26:05.24,0:26:10.66,Default,,0000,0000,0000,,data calling and data archiving. What you\Ncan set
Dialogue: 0,0:26:10.70,0:26:13.73,Default,,0000,0000,0000,,up in, in PostGres is a partitioning scheme\Nin
Dialogue: 0,0:26:13.73,0:26:19.02,Default,,0000,0000,0000,,which data for different days go into different\Nphysical
Dialogue: 0,0:26:19.02,0:26:20.80,Default,,0000,0000,0000,,database tables.
Dialogue: 0,0:26:20.80,0:26:24.05,Default,,0000,0000,0000,,And, you know, that's all right. But the really
Dialogue: 0,0:26:24.05,0:26:27.84,Default,,0000,0000,0000,,cool thing is that you access that data by
Dialogue: 0,0:26:27.84,0:26:34.23,Default,,0000,0000,0000,,querying a parent sort of virtual table that\Nthen
Dialogue: 0,0:26:34.24,0:26:36.88,Default,,0000,0000,0000,,propagates that query across all of its children\Nand,
Dialogue: 0,0:26:36.88,0:26:39.10,Default,,0000,0000,0000,,you know, magically does its results and,\Nand spits
Dialogue: 0,0:26:39.10,0:26:39.61,Default,,0000,0000,0000,,them back to you.
Dialogue: 0,0:26:39.61,0:26:41.10,Default,,0000,0000,0000,,So you don't have to update your Ruby code
Dialogue: 0,0:26:41.10,0:26:44.96,Default,,0000,0000,0000,,at all, which is sweet. And this way, if
Dialogue: 0,0:26:44.96,0:26:48.96,Default,,0000,0000,0000,,you want to, say, delete data that's 180 days
Dialogue: 0,0:26:48.96,0:26:51.69,Default,,0000,0000,0000,,old, you just drop the table that's associated\Nwith
Dialogue: 0,0:26:51.70,0:26:55.48,Default,,0000,0000,0000,,180 days ago. If you were to do a
Dialogue: 0,0:26:55.48,0:27:00.06,Default,,0000,0000,0000,,delete where, you know, created_at is less\Nthan 180
Dialogue: 0,0:27:00.06,0:27:03.31,Default,,0000,0000,0000,,days ago, you're probably gonna be waiting\Nfor weeks.
Dialogue: 0,0:27:03.31,0:27:07.78,Default,,0000,0000,0000,,And last, but not least, backups are a real
Dialogue: 0,0:27:07.78,0:27:10.09,Default,,0000,0000,0000,,pain in the neck when you have a huge
Dialogue: 0,0:27:10.10,0:27:12.21,Default,,0000,0000,0000,,dataset. Like, that crime job that dumps your\Nentire
Dialogue: 0,0:27:12.21,0:27:16.97,Default,,0000,0000,0000,,database and uploads it to S3 doesn't work\Nso
Dialogue: 0,0:27:16.97,0:27:19.90,Default,,0000,0000,0000,,well when your database is, like, two terabytes\Nbig.
Dialogue: 0,0:27:19.90,0:27:24.95,Default,,0000,0000,0000,,And, yeah, that's a big S3 bill, too.
Dialogue: 0,0:27:24.95,0:27:27.75,Default,,0000,0000,0000,,Fortunately, we can take advantage of the\Nsame sort
Dialogue: 0,0:27:27.76,0:27:31.77,Default,,0000,0000,0000,,of things that PostGres does to allow it to
Dialogue: 0,0:27:31.77,0:27:36.93,Default,,0000,0000,0000,,do streaming replication to do, sort of, on\Nthe
Dialogue: 0,0:27:36.94,0:27:41.32,Default,,0000,0000,0000,,fly, incremental backups. And there's a tool\Ncalled Wall-E,
Dialogue: 0,0:27:41.34,0:27:45.15,Default,,0000,0000,0000,,which makes this super easy. And it, it's\Nreally
Dialogue: 0,0:27:45.15,0:27:48.53,Default,,0000,0000,0000,,cool, because it allows, it, it makes it very
Dialogue: 0,0:27:48.53,0:27:52.47,Default,,0000,0000,0000,,easy to upload incremental backups to, say,\NS3, and
Dialogue: 0,0:27:52.48,0:27:54.08,Default,,0000,0000,0000,,then when you want to restore your database,\Nit
Dialogue: 0,0:27:54.08,0:27:58.09,Default,,0000,0000,0000,,makes it really easy to, to restore a specific
Dialogue: 0,0:27:58.09,0:28:00.60,Default,,0000,0000,0000,,point in time.
Dialogue: 0,0:28:00.60,0:28:03.66,Default,,0000,0000,0000,,And so that, Wall-E is really awesome. I love
Dialogue: 0,0:28:03.68,0:28:04.25,Default,,0000,0000,0000,,it.
Dialogue: 0,0:28:04.25,0:28:07.30,Default,,0000,0000,0000,,Now, I, I, I stayed in my hotel room
Dialogue: 0,0:28:07.30,0:28:10.53,Default,,0000,0000,0000,,last night. I didn't get to go to the
Dialogue: 0,0:28:10.54,0:28:12.95,Default,,0000,0000,0000,,Speakeasy thing, which kind of bummed me out.\NBut
Dialogue: 0,0:28:12.95,0:28:14.54,Default,,0000,0000,0000,,I, I had to work on these slides for
Dialogue: 0,0:28:14.54,0:28:20.37,Default,,0000,0000,0000,,you people. And, and, and when I got done
Dialogue: 0,0:28:20.38,0:28:22.26,Default,,0000,0000,0000,,with this, when I sort of reached this point,
Dialogue: 0,0:28:22.26,0:28:25.68,Default,,0000,0000,0000,,I looked back on all of these, these slides
Dialogue: 0,0:28:25.68,0:28:28.10,Default,,0000,0000,0000,,- there's like fifty-four of them - and I
Dialogue: 0,0:28:28.10,0:28:30.52,Default,,0000,0000,0000,,was like, holy crap. This is a lot of
Dialogue: 0,0:28:30.52,0:28:32.00,Default,,0000,0000,0000,,information.
Dialogue: 0,0:28:32.00,0:28:38.84,Default,,0000,0000,0000,,Yeah. This is a lot of information. But the
Dialogue: 0,0:28:38.84,0:28:41.06,Default,,0000,0000,0000,,thing I, I want to stress is that, at
Dialogue: 0,0:28:41.06,0:28:45.38,Default,,0000,0000,0000,,least in, in my experience, these issues tend\Nto
Dialogue: 0,0:28:45.38,0:28:47.73,Default,,0000,0000,0000,,come at you one at a time. This isn't
Dialogue: 0,0:28:47.73,0:28:48.97,Default,,0000,0000,0000,,the sort of thing where you have to know
Dialogue: 0,0:28:48.97,0:28:53.85,Default,,0000,0000,0000,,all of this stuff in advance in order to
Dialogue: 0,0:28:53.85,0:28:57.74,Default,,0000,0000,0000,,deal with biggish data in PostGres. Things\Ncome at
Dialogue: 0,0:28:57.74,0:28:58.98,Default,,0000,0000,0000,,you one at a time, and you can deal
Dialogue: 0,0:28:58.98,0:29:00.65,Default,,0000,0000,0000,,with them one at a time.
Dialogue: 0,0:29:00.68,0:29:02.95,Default,,0000,0000,0000,,And I have faith in you. I think you
Dialogue: 0,0:29:02.95,0:29:08.42,Default,,0000,0000,0000,,can do it, because you're awesome, because\Nnobody told
Dialogue: 0,0:29:08.42,0:29:11.66,Default,,0000,0000,0000,,us that we could turn, like, transistors into\NLOLCats,
Dialogue: 0,0:29:11.66,0:29:14.95,Default,,0000,0000,0000,,but we did it. Like, that's the type of
Dialogue: 0,0:29:14.96,0:29:18.04,Default,,0000,0000,0000,,people we are, and that's, that's why I'm\Nproud
Dialogue: 0,0:29:18.04,0:29:21.71,Default,,0000,0000,0000,,to be at RailsConf. If you're interested in,\Nlike,
Dialogue: 0,0:29:21.71,0:29:23.13,Default,,0000,0000,0000,,talking to me about this, if you think I'm
Dialogue: 0,0:29:23.13,0:29:26.32,Default,,0000,0000,0000,,full of shit about anything, which I probably\Nam
Dialogue: 0,0:29:26.32,0:29:31.32,Default,,0000,0000,0000,,on at least one point, just, just say so.
Dialogue: 0,0:29:31.32,0:29:32.73,Default,,0000,0000,0000,,Just feel free to come up to me after
Dialogue: 0,0:29:32.74,0:29:36.21,Default,,0000,0000,0000,,the conference, or after my talk here. I have
Dialogue: 0,0:29:36.22,0:29:40.84,Default,,0000,0000,0000,,delicious hot and spicy Mexican candy, as\Nan incentive.
Dialogue: 0,0:29:40.84,0:29:43.36,Default,,0000,0000,0000,,So there you go.
Dialogue: 0,0:29:43.36,0:29:45.90,Default,,0000,0000,0000,,If you want to learn more about the stuff
Dialogue: 0,0:29:45.90,0:29:47.97,Default,,0000,0000,0000,,that I. Are you taking? Do you want to
Dialogue: 0,0:29:47.97,0:29:51.52,Default,,0000,0000,0000,,take a picture of the Mexican candy? OK. OK.
Dialogue: 0,0:29:51.52,0:29:55.75,Default,,0000,0000,0000,,I can send it to you.
Dialogue: 0,0:29:55.76,0:29:58.89,Default,,0000,0000,0000,,If. If you want to learn more, all of
Dialogue: 0,0:29:58.89,0:30:00.88,Default,,0000,0000,0000,,the links referenced in this talk are at this
Dialogue: 0,0:30:00.88,0:30:03.63,Default,,0000,0000,0000,,url, and if you are interested in having more
Dialogue: 0,0:30:03.64,0:30:08.96,Default,,0000,0000,0000,,visibility into your production errors, check\Nout Honeybadger, because,
Dialogue: 0,0:30:08.96,0:30:13.30,Default,,0000,0000,0000,,yeah. We love Rails devs. And that's it. That's.
Dialogue: 0,0:30:13.30,0:30:15.17,Default,,0000,0000,0000,,It says end of show here, so I guess
Dialogue: 0,0:30:15.17,0:30:16.21,Default,,0000,0000,0000,,it must be the end of show.