Undersecretary Spaulding is the, well it's the Undersecretary for the National Protection and Programs Directorate at the Department of Homeland Security. Her very impressive and detailed bio can be found in your program. I think it's page 25. But I will just say she brings to us, it comes to us with over 20 years of experience on the Hill, in the administration, in the private sector. And this is something not everybody can say. Working for both Democratic and Republican offices and administrations. So that's an increasing rarity. So we're glad to have you here today. So thank you for joining us. And not surprisingly, since this is a conference about the future of war, the topic of cyber security and cyber warfare has come up a little bit. So just to start us off, you know, we've been talking about the future of warfare among nations, perhaps thinking a little bit about the difficult questions about how you even know when you're at war, who you're at war with. Tell us a little about your perspective on cyber security and the threats that we face and why you're here. Yeah, thank you, Alan. Well, it's a real honor to be here. I'm very envious, I was saying, of all of you who have been able to participate yesterday and today. It's one of the things I miss most about being in private practice is the opportunity to come and hear such what sounds like it's been very rich discussions and conversations. And I'm really pleased to be here to do this, but wish I could be here for more of the conversation. You know, why am I here is a good question. The Admiral Stockdale question. Yeah, exactly. I thought, you know, what is the Department of Homeland Security doing at a conference on the future of war, right? Well, the first answer is Peter Bergen asked me to be here and I would do anything Peter asked me to do. So that probably is enough. I try to do the same. Yeah, so that's, but also as you, you know, alluded to in your opening remarks, this is an area where traditional lines are ever blurring and where traditional stovepipes really will be problematic for us. If we continue to approach these challenges with our traditional mindset and thinking about things in our traditional stovepipes in ways of breaking this down, whether it is between domestic and foreign or between war and peace in the cyber arena between destructive and non-destructive incidents between state actors and non-state actors and individuals, you know, there are just, you know, a whole slew of areas in which these traditional distinctions that have served us well. I mean, I came up, I spent nearly 30 years in the traditional national security community where the private sector, for example, you interacted with the private sector really under only two circumstances. They were either a contractor from whom you were buying a good or service or they were a potential victim about whom you had specific incredible information that you were about to share with them. This notion of the private sector as being a full partner in the security solution in addition to being a significant part of the targeted space is really something new, relatively speaking. And it's one we're still in the traditional national security community, I'm sorry to say, working to get our heads fully around.  But we have the overarching mission of strengthening the security and resilience of the nation's critical infrastructure. And all of you know what that means. It goes well beyond what a lot of people think of as infrastructure roads and bridges and transportation to include the electric grid and water, financial services, chemical facilities, nuclear facilities, agriculture, public health. There are 16 sectors that we've divided critical infrastructure into. But there, too, we have to be careful we don't get into stovepipes. Because while we focus on these sort of traditionally identified areas of critical infrastructure, we really have to pay attention to all of the private sector for a variety of reasons, not the least of which is that those things we really care about exist in a broader ecosystem. And if we are not paying attention to the cybersecurity of small and medium-sized businesses that perhaps don't appear to be critical infrastructure today, we ignore them at our peril as things like the target breach have taught us, right? So we take responsibility. We have the lead for the dot-gov securing the civilian government systems in collaboration with the departments and agencies who have primary responsibility for securing their networks and systems. But we provide an overarching assistance in terms of tools and the deployment of sensors across that civilian government space and providing guidance and direction.  We got three key bills through, one of which codified our authority to provide that kind of guidance to departments and agencies in the dot-gov. The other codified our center that is the heart and soul of our cyber security efforts, the National Cyber Security Communications Integration Center, or the NKIC. And that is where we bring together those departments and agencies, but also our private sector folks and state and local folks. All have a seat on the floor of this operations center. And that's the other area where we have a leading role, and that is in helping to protect and enable and empower the dot-com world to have effective cybersecurity. So those are the areas in which we focus. But as I say, a line between, you know, are we at war or are we at peace as a thin one? So I'm happy to be here today. So that's terrific and a great introduction just to let's dig into it a little bit. So you mentioned Target, you mentioned the dot-com world. You know, what's the right way to think about what the home front looks like? I mean, and I think also, you know, from the consumer or, you know, individual perspective, there's just this steady drumbeat of bad news, right? Target, Home Depot, Neiman Marcus, Sony, you know, celebrities getting their cell phones hacked. It just feels like we have not our ability to protect ourselves, has not kept up, has not kept pace with the technology that we're building. What's your view of what the home front looks like in that context? Well, we are, I mean, it's absolutely the case. We are under attack every single day. You know, we have millions of intrusions and attempted intrusions every week. And all across, again, our critical infrastructure sectors, but all across the private sector and in the government. And so, and we are, we are not where we need to be in our cybersecurity and cyber hygiene and it's interesting because when we talk about this, there are two things that we often say. One is, you've heard this, those of you who've been in part of this conversation now for quite some time. The only way to really protect yourself against a determined adversary in cyberspace is to destroy your computer, right? That, that, that, that a determined adversary in cyberspace will find a way over time, eventually, to get into your system. And what worries me is that while that is true, I think it creates a sense of fatalism that is not helpful. Because at the same time, it is also true that some very basic cyber hygiene steps could stop, prevent, between 80 and 90 percent of the intrusion activity that we see today. So there is a lot that can be done. That's a bold claim. Can you give us just a, not to digress, but like. Well, there are studies and I don't have them at my fingertips, but that, but if you go online, you'll find them. There was one in Australia where they, they, they took four basic cyber hygiene measures and were able to, to demonstrate that with those, if you implemented those appropriately, that you could stop. I think it was 94 percent, I can swear by it, but it was, it was certainly above 80 percent of the intrusion activity. So there's a lot we can do if we had in the cyber security framework is all about achieving that basic level of cyber hygiene. It's not a one size fits all framework. And so it allows sophisticated industries to go farther, but it, it calls on industries to assess where they need to be in cyber security and provide some very basic things that can be done. It does not have to be expensive, but it does require commitment, personnel, technology and procedures. And you can stop a great deal of the activity. And then we can really and need to focus our attention on that remaining 10, 20 percent. That we, that is what keeps us awake at night. Now, you've talked a little about critical infrastructure and department's role in that. Do you, do you, do you look at this and think, you know, when we think about critical, when we think about infrastructure or what you're trying to protect, let's not use the charged term in critical infrastructure. But what the department is out there trying to protect, even in a warfare context, even when we're talking about the home front, it's maybe more than just a few of these, even the 16 industries. It's something broader when you're talking about protecting consumers. Is that part of what we need to be thinking about? As I said, we absolutely have to think broadly when we look at our outreach. And so a big part of what we do is we try to, the information that we garner, largely through these sensors that we have in place in the dot gov, in the civilian dot gov world. But also information that we get voluntarily from our private sector partners and from our state and local folks. We take that information and we try to then turn around and get it out as broadly as we can. And we push that out well beyond traditional concepts of critical infrastructure. And that's really, I think, what has to, a big part of what has to happen here. As our Deputy Undersecretary for Cyber Phyllis Schneck, Dr. Phyllis Schneck says, we have the ability to connect these dots in a way that really our adversaries don't. It's one advantage that we do have. How does DHS play with some of the other agencies or departments that are gonna show up in this context? So each of the sectors has sector-specific agencies that have the lead for those. DHS happens to be the lead for 10 of the 16 sectors. But for example, the lead is the Department of Energy. So I meet on a quarterly basis with the Deputy Secretary of Energy and between 20 and 30 CEOs of electricity companies in the electricity sub-sector coordinating council. And they come to the table, a very important group, because getting to Chief Executive Officers is one of the important things that we do. They make decisions about risk management across their enterprise. They understand completely that physical and cybersecurity are inexorably intertwined, that we cannot build a stovepipe around cybersecurity and around physical security because they are related across the entire risk spectrum from threats, vulnerabilities, consequences, and often overlooked mitigation. So I often say that sometimes you have to consider that perhaps the best return on investment to mitigate a serious, significant, damaging cyber incident, to mitigate those consequences, will be to put in a hand crank, right? So it's not always just a technical solution and you need to look at it holistically. And these CEOs do that. And they make resource allocation decisions across their enterprise. So we've had a terrific partnership just to give one example. And with Treasury and the financial services sector is another. Digging into how this actually unfolds in the context of an actual incident, maybe Sony's a good example. How does DHS get involved in a situation like this, the attack on Sony Pictures? Yeah. Well, and your question reminds me that the other key players, of course, that we work very closely with on a daily basis are our partners in law enforcement. And that includes within the Department of Homeland Security the Secret Service, very active, particularly in cyber crimes involving financial crimes. And ICE, our Homeland Security investigators who are part of our Immigration and Customs Enforcement who are also very much engaged in law enforcement activities with regard to crimes facilitated by cyberspace, but particularly the FBI. And then on the intelligence side, particularly the NSA and increasingly the broader intelligence community, and I'm happy to talk a bit about the CTIK, the Cyber Threat Intelligence Integration Center, and maybe you've had some folks here talking about that already. But the intelligence community plays a key role in helping us gain situational awareness and understand the environment. And law enforcement also plays a key role in helping determine attribution and with deterrence. And so often what will happen in an instance like the Sony incident is that one of us will get the call and then we'll call the others, right? And so we are sometimes brought in by the Bureau to assist with understanding what's happened here and developing mitigation efforts and assisting in rebuilding in a more secure way.  Who the actor was. Who the actor was, right? Which is one of the reasons that traditional sort of national security way of thinking about things, well, who responds is gonna depend on the nature of the perpetrator, is not particularly helpful here. Were you folks involved in the Sony investigation follow-on? So one of the things that we do, what helps us to do our mission, which is all about again assisting our stakeholders and providing information and mitigation assistance is that we build these relationships of trust. And so that means we don't go out and talk about the companies that we work with. Because part of the reason that they come to us for assistance is that they know that we can work with them in a way that we don't have to make public. That was the most excellent way I've heard of not answering a question. No, I mean that in a, or it's a great answer, but we'll just have to speculate. I do wanna turn to the audience for questions in just a moment, but I can't resist asking. You know, the department has been in the papers of late and it's not just for cybersecurity. Although I should say, I think the White House did talk about cybersecurity in the context of one of the big concerns about the funding standoff that we face. What are the ramifications if there's no deal this week? Alan, thank you for that question. Because the secretary, as you know, has been out really pounding the pavement and pounding the tables about the importance of getting an appropriation for the department. There are implications, let me start first and foremost with the implications just for our workforce. I mean, one of the things that I take very seriously as the leader of NPPD and a leader in the department of Homeland Security is the obligation to our workforce. And this is incredibly hard on our workforce. It has been said that there will be little impact because most of the workforce will still come to work because they will be exempt from the prohibition on working during a funding hiatus. They will come to work without a paycheck. Last time we were in a funding hiatus for 16 days and people missed a pay period. And for much of our workforce, that is not insignificant. So first of all, and not to mention the impact on morale from people who hear this kind of discussion and debate about the importance of their mission and the organization. Then there is the impact, potential impact on the mission. And I talked about the sensors that we deploy in the DACAV world. Well, we are in our third iteration of our Einstein program, which started out as something that just helped us to see what was coming in and out of a reduced number of connections to the internet in the civilian government context. Einstein II, which allows us to better understand and see, know what it is, recognize bad stuff as it's coming through. And now E3A, Einstein IIIA, which will allow us to block those bad things from coming into the civilian DACAV. That effort to deploy that comes to a grinding halt. Similarly, our continuous diagnostics and mitigation effort, which looks at the inside of the civilian DACAV, it tells us about the health and helps for CIOs and CISOs prioritize the things they need to address there. That deployment effort comes to a halt. The analytics that support our NKIC, our operation center, that help, again, to prioritize allocation of resources, that help us to understand what we're seeing, that help us to be agile and nimble against an agile and nimble adversary. Those things are going to be decimated. So there are definitely impacts and they grow, obviously, the longer that it continues. So real impact is what you'd say. Yes. Okay, questions from the audience? Why don't we start up here? Got a microphone? Good afternoon, thank you for this opportunity. Dianne Divis with Inside Unmanned Systems and Inside GNSS Magazines. GPS was mentioned by the last speaker and it is an essential element across more than half of the critical infrastructure sectors. Excuse me. Can you please speak to the work being done by DHS in terms of the research you're doing on backing up the GPS signals in particular timing and the E-Laran system which has been proposed as one possible backup? Yes, thank you. You're absolutely right. We have been very much involved in looking at GPS and our reliance on it. And as your question noted, one of the most significant aspects, particularly for those of us who worry about critical infrastructure, is the timing. The number of systems and networks that depend upon GPS for the timing that allows them to function properly is one of the things we are most concerned about. And so we are, again, as we do with all of these things, working very closely with the private sector. So we have, for each of the sectors, we have a sector coordinating council that is made up of the private sector folks in that sector. We brought them together to explain why we are so concerned about this, what we think the implications are, and to ask them to go out and work with their colleagues to determine, to understand better their dependencies, to understand precisely which items, which kinds of manufacturers, et cetera, what does the landscape look like out there in terms of our dependency on GPS for timing, and then to work with us to share best practices and mitigation. Our science and technology division is working with others in the government to look at ways in which we can mitigate this. And you're absolutely right, we're looking at, and we have paused, the dismantlement of Eloran so that we can take a good look at all of the options that are out there. Other questions right up here? Hi, Joe Marks from Politico. You said you were interested in talking about the Cyber Threat Intelligence Integration Center, so I'm hoping you can do that in the context of, there's been some confusion about exactly what this center will do that's not already being done at NKK and elsewhere. So if you can tell us what's new and also how it will help the private sector if there aren't private sector people in the room there. Yep, and thank you for the question because I know there's been a lot of questions and to be perfectly candid, that always makes my staff nervous when I start to think of that. They seem okay right now. The initial reaction is understandable. Why do we need yet another center? But this really I think will be very helpful. This is, as its name implies, the Cyber Threat Intelligence Integration Center. The idea here is to bring together under the authorities of the DNI all across all 16 intelligence agencies and entities, pull together the information that they have that informs our situational awareness about the Cyber Threat. And for us, I think that will be hugely valuable. Right now we don't have a single place where all of this comes together. And I'm looking forward to the opportunity to sit down through this organization with not just the cyber experts in the intelligence community, but with the regional experts in the intelligence community, with those folks who can tell us not just what we're seeing in cyberspace from a given actor, but give us a broader sense of what are the gaps, what are the gaps for this actor? What else might they be looking for? If we see activity around a particular target, what might that mean? We might have some assumptions, but maybe there are experts out in the intelligence community who can better help us understand. Part of what I worry about in our cyber efforts is that we sometimes are looking under the lamppost. If you know that old analogy of why you're looking for your keys under the lamppost, because that's where the light is, but you really lost them over there in the dark. We need to be looking places we're maybe not thinking about. So I really think there's tremendous value in bringing that together. The other huge benefit that I see in the CTIC and its role is that that's one place we can go to get information declassified. And that's a big part of what we do. Again, as I've said, our whole raison d'etre, the whole reason, our whole mission is all about getting information out as broadly as we can. And so we are constantly pushing to have information reduced to a level where we can get it out more broadly. And CTIC is specifically tasked to do that. So I think that will be hugely valuable. Other questions, maybe we go towards the back. We've got a mic back there.  Hi, Jordan Bruner from Arizona State University. So a recent executive order was signed by President Obama encouraging cooperation between the public and the private sector through information sharing and analysis organizations. And it talks about them setting certain standards for those nonprofit organizations to follow. Can you talk a little bit about that and what the Department of Homeland Security might want those standards to look like? Yeah, what the executive order calls for is for the department to help set up a third party organization. And I think we'll probably wind up doing that through a kind of grant process. To work with the existing ISEOs and others in the private sector, much the way the cybersecurity framework came together by talking to the private sector about what are the best practices out there. This will be very similar. They will, this entity will, working with the private sector, pull together best practices. And the idea here is A, to actually open the aperture to recognize that there are all kinds of private sector groups coming together to share information with each other. We don't view that as competition. We welcome that. The most important thing is that information is shared. We obviously will encourage and would welcome that ISEOs share appropriately. Cyber threat indicators, for example, with us doesn't need to be associated with any particular company or entity. Just give us the technical indicator information that could be of benefit to all those who are defending networks. That's what we're about, network defense. And so part of the idea here is to recognize not just ISEOs for each of the 16 sectors, but information sharing and analysis organizations that might crop up in other contexts. But also to give companies who are looking to say, Jo, I join this ISAC or that ISAC or this organization, a comfort level that there are certain standards with regard to protecting their information. If they think there ought to probably be standards with regard to the protection of personally identifiable information and privacy safeguards and all the rest of that. And so these are the kinds of things that we hope to see in the standards that will come forward. And our hope is that it will help those who are looking to join and perhaps to create these organizations and broaden the amount of sharing that's taking place. Do you detect any discomfort in this sharing or working with you?  I know one of the stories coming out of there was that there seems to be a little bit of a trust deficit in some ways between a lot of the companies who are angry at some of the activities coming out of the government. How do you address that? Well, first of all, are you seeing it and how do we address it? So it's definitely there. There's no question about it. We are actually in a relatively good position at DHS with regard to having established over many years, largely starting first in the physical arena in our Office of Infrastructure Protection. But now through our Cybersecurity and Communications Office, these trusted relationships in the private sector and we have an excellent record. The department had the first statutory privacy officer. We take privacy issues very seriously. I have in NPPD my own privacy team and they are with us at every step of the way and we find that tremendously beneficial. We have a privacy advisory group so we have a very strong record on privacy issues and on protecting sensitive business information, on working in a trusted basis with the private sector. That having been said, there's still concerns. There are concerns about potential liability and sharing and that's why the administration has proposed this legislation that would make the NKIC, our National Cybersecurity Communications Integration Center, a single portal for the private sector to share cyber threat indicator information and give liability protection for the sharing of that information. We wanna bring that information together in a trusted place, a civilian place where that information can come together. We want it to all come into one place so that we can connect the dots and come into a place whose mission it is to then take that information and get it out in near real time to all of our stakeholders, to the FBI, to our private sector, to state and local, to all of these folks with appropriate privacy protections and in an appropriate manner. And so that's, again, part of what we're able to do because we have built that relationship of trust. The other area that I have not talked enough about as I've gone out to Silicon Valley and talked to other private sector folks is our ability to protect information from companies worry that if I give it to DHS then the regulators will use it and come after me. And we have something called PCII, Protected Critical Infrastructure Information Regime which is a statutorily based regime which says companies can give us vulnerability information, they can give us information about an incident that they've suffered and all of how they think the perpetrator got in, et cetera. And we are not allowed to share that information broadly around the government. It cannot be used as the basis for regulatory action. We cannot hand it over in civil or criminal litigation. It is not subject to FOIA. And so that addresses also a lot of the concerns the private sector has about sharing information. So we're well positioned, I think, to continue to build those relationships and get that information. Other questions from the audience? Maybe right up here. And then we'll go to the back. I see you waving back. Hi, Nancy Wong from Medell News Service. I have a quick question about, you talked about bringing experts into DHS and bringing into the government for cyber security experts. But there has been concerns that there aren't enough human resources who are expert in this area. And a lot of the experts in the government are very well educated about the new updates in the cyber security community. So what is your thought on that? Do you see a lack of new generation, new experts coming into the government? And what do you think government should do about that? Yeah, absolutely. One of the things we spend a lot of time and effort on is building that cyber pipeline. So the third piece of legislation amazingly that we got through last Congress was authority for us to create greater incentives for folks to come in as cyber professionals. But I also think that we need to just think about this in a different way. I spent many years at CIA and I remember having the same thought there, which is this notion that we've had in the national security community particularly, where we're gonna get people right out of school and we're gonna keep them through their entire career and they're gonna retire as a government employee just simply is not viable anymore. And it certainly isn't viable, I don't think largely in the cyber arena where we can't compete with the private sector certainly on salaries. We compete on mission. So what I've said to our private sector folks who say to me, what are you doing to build the pipeline? I say to them, what are you doing to build the pipeline? We're in this together. And here's the deal that I would make for you. You help, you contribute also resources and effort and energy to build, to encourage young people to pursue this and to help colleges and universities and community colleges all train these folks the way we need them trained. We'll take them when they first graduate. We'll give them that first training, that on the job training.  You'll lure them away into the private sector. And then I think at some point later at the end of their career after they made a lot of money, they'll miss the mission and they'll come back. And something like that I think is sort of the mindset we have to move to if we're gonna compete in this. Question in the back. Hi, Andrea Eshalal with Reuters. I wanted to ask you, you said, the country's not where it needs to be. And we've spoken about these infrastructure sectors. Do you have a kind of a particular concern about any particular sectors that you'd like to share? And on this issue of protecting the privacy of reporting from prosecution or criminal or civil litigation, what do you think is gonna be required to get the bulk of private infrastructure and private companies to really step up their game? I mean, we continually hear about simple mistakes that are made, patches, just really simple things that haven't been done. On the second question, again, I do think that outreach to CEOs is a big part of it. And we really need to continue to convey that sense of urgency that this is, and this will go to the first part of your question, that this is no longer something that is theoretical. This is the here and now. And they need to take this seriously. And they need to devote the resources and the time and energy and convey to their entire enterprise how serious this is. But that's a big part of, I think, what we need to do to get folks to step up their game. There are also market forces that have not fully been exploited yet. So for example, I've been working with, and Dan Sutherland, my general counsel, been working with the American Bar Association to get them to reinforce for lawyers who do have our assisting clients in transactional activity, mergers and acquisitions, for example, that if they are not including cyber security in their due diligence, they are not doing their job. We talked to venture capitalists and investors in Silicon Valley and up in New York and all across the country. If you're investing in a company and you're not checking out to make sure that they have appropriate cyber security in place, you are throwing your money down a rattle because that intellectual property that you're paying all those millions of dollars for is going out the back door. So they've got to be asking those kinds of questions. If you're a client and you're looking for it to hire a law firm to do your intellectual property work and you're not asking that law firm about its cyber security practices, then again, I think you're making a mistake. So these are the kinds of market forces auditors need to be including cyber security in their activities as well. So we need to tap into, there are still more market forces that I think we can leverage. On your first question, and now I'm trying to remember. Is there a particular sector? A particular sector, right.  Yes, exactly. No, and it's not a question and this is part of what we need to deal with is that there's no stigma attached to being attacked. This is their, again, this is one of the adages of cyber security professionals. There's only two kinds of companies in the world. Those who know they've been hacked and those who haven't figured it out yet. I mean, this is an equal opportunity threat. Having said that, we do see a lot of activity targeting some of those key critical infrastructure sectors that we worry about. We all saw, there was a lot of press in the last couple of years, the attacks against, efforts against the financial services sector. That's clearly one that we work very closely with. I've talked about the work we do with the electricity sector. They know that there are a lot of folks who are coming after them. But really it cuts across virtually all of our critical infrastructure sectors. Maybe one more from the audience over here. Hugh Rinestaff. You talked about recruiting people. But what have you done to ensure there'll be no more Snowden's? In other words, how are you doing the internal checks? So we have very recently, within the last few months, I asked our team to do refresher training on counterintelligence. And we need to do that on a regular basis. I think one of the hardest things that we have to focus on are both the insider threat and obviously the increasingly sophisticated social engineering around spearfishing. And so on both those fronts, we have stepped up our training for our own folks around those issues. We also have working with our CISO and our CIO, audits in place, and there are things that have been put in place post Snowden so that at a minimum, we're in a better position to detect activity. But it's something we take very seriously. I'm afraid my colleagues at OTI would not forgive me if I didn't also say that one way to prevent the next Snowden is to make sure that our practices are not so out of step perhaps with what people's expectations are about them. At least many people within the broader public. So we're almost out of time here. Any final thoughts or words of wisdom for this community as it ponders the future of war? Well, first of all, again, I wanna thank you because your presence here indicates a real interest in what I think is an increasingly important subject. And I said at the outset that we are very mindful that these traditional boundaries and lines that we have taken for granted over the years are blurring. And again, the line between are we in conflict or are we at peace is one that quickly becomes a real challenge and not always a particularly helpful line for us in the context of cyberspace. It's an area where we need creative thinking and where all of you bring unique perspectives to bear that can help us as we go forward in our mission to try to safeguard this country in cyberspace in collaboration with our partners across all of government. We talk about this as a whole of government approach, a whole of DHS approach within my director to whole of NPBD approach, but really it's a whole of nation approach uniquely and all of you, every one of you in this room has a role to play in that. So I thank you for the work that you do, many of you in this room on a daily basis. And for those of you who don't really think of yourselves as part of this effort, I hope that the discussions yesterday and some of the discussions today have helped to clarify the role that each and every one of us can play. This is a shared responsibility. So thank you. Well, please join me in thanking Under Secretary Spalvin for being here today. Thank you.


Dude, I always thought it was the Baron Stain Bears. Turns out it's the Baron Stone Bears. Total Mandela effect. Dude, I think you mean the Manolo effect. Dude, total Enrico Dandolo effect. That's when you think it's the Mandela effect, but really it's the Manolo effect. Right, but I thought Enrico Dandolo was the president of South Africa. Dude, you're thinking of Nelson Manolo. Dude, total Moldova effect.


Should I put you guys all on my Insta story?  OK, so to get started, I need two people that aren't really tired to give me a hand with something. It's really basic, non-computing, but I do need two volunteers. OK, I got one here. One more volunteer just to come up and help me out. Come on up. Appreciate it. OK, so we're going to do a little competition. I have two books here. I just need each of you to pick a book. OK, stand on each side of me, please. Now, can you look up every instance of the word war in each of these books? And the first one to get it, just raise your hand. We have time. Go ahead. Just every instance of the word war. Please don't use your cell phone. I've done this and someone use their cell phone. I was like, come on. You have the right idea there, I think. French ministry work. Oh, you got it. OK, so you found it really quick. What are you doing over here? How many have you found? None. That's not that bad. Let's just, what page are you on? OK, thanks. I think you get the point here. Thank you very much for helping me out with that. Yeah, so the talk is dropping into bee trees. That guy's name is Jaws. I'm not kidding. Hopefully this drop-in isn't as vertical as that is. Hopefully it's like a nice 18-degree descent. My name is David McDonald. I'm a developer at Weed Maps. Although this is the title of the talk and we will dive into bee trees here, we're going to talk more in general about indexes. So bear with me a little bit as I set the stage. Yeah, as developers, we spend a lot of our time learning. That comes with the territory. By my estimate, I spend 25% of my time learning and 12.5% of my time learning about the new JavaScript whatever and a lot of time watching YouTube videos, specifically cat videos, because they're hilarious. And I feel like I want more time. I feel like a lot of people I talk to in the industry just want more of that time, but it's not coming, especially if you work for somebody else, as most of us do. We don't have an endless amount of time to learn about the things that we would like to learn about. And we have to prioritize. We end up not getting as much time as we might like to learn about the tools we even use every day. So that's kind of where this came from. There's been tons of instances in my career where I'd run into that just in time learning. Something's broken. I've got two hours to fix it. Sprint deadline is at 3 PM or something. And now I'm diving in. Well, that just isn't sufficient sometimes. And I think when it comes to database issues, specifically indexes, that's always the case. It's kind of a vast topic. 50 years of computer science going into just one thing, like a B-tree, for example. As Rails developers, we've likely spent more time looking up, like Googling how to use options for a select and a slim template. Or just like, how do I get sprockets to cooperate with my Angular 1.x thing? So yeah. And our databases just work. They're time tested and reliable. So for the most part, it's kind of plug and play. For the average developer, our databases are just casualties of this time crunch. And one of the sides I hope you take away from this today, which is a little bit in contrast with what David spoke about this morning, but not too much, it's that it's worth diving deeper into certain topics that can yield a lot of fruit, right? And I feel like indexes is definitely one of those topics. There are a lot of spaces, though, in our stack like this, middleware, for example, maybe, that they require expertise and they require attention and they're very marketable things, right? They're things that will help you in any team you go into for the rest of your career in perpetuity. Now, I frankly spend most of my career treating databases the way I would treat my smartphone. Like, I know how to use the thing, but I don't really know exactly what it's doing, right? That's mostly how I look at a database. And I think your average person walks around with their cell phone just using it. They don't really think about radio waves. They don't really think about what, they don't really know the difference between Swift and Kotlin, they don't care about that stuff. So I've gotten by, like most of us do, with gems and heuristics and online searches, but I'm a professional, I use these things every day. I should be able to go a little bit deeper when I need to. So that inspired me to do just that. Databases are a vast topic, so focusing on indexes, I feel like, gives us the most bang for our buck, okay? Understanding indexes is key to demystifying the database performance and functionality, and will have a huge impact on your ability to plan, contribute and troubleshoot as you progress in your career. If I'm talking too quickly, someone signal me because I drink a lot of coffee. So this is a bold statement. What are indexes anyways, right? We use them all the time. Do we ever really stop to kind of think of what they are? Yeah, they're those things that speed up selects, right? They speed up SQL queries, especially ones that are starting to run slowly. They can help you look up things faster, and they actually help you look up things much faster depending on the size and structure of your stored data. And all this may be true, but what are they? We know what they do, right? But we don't really know what they are sometimes. How do they speed up selects? What is this data structure? How does it work? We kind of know what they do, but we really don't know what they are. At least, I didn't know what they are. Standard definition you'll find is that a index is a data structure stored on disk that organizes a reference to your data. It is both a copy of the data you wish to index combined with a reference to the actual data location. So pictures are helpful, right? For completeness, this would be like a non-clustered form here. You have this separate place stored on disk where you're actually making copies of your data that you're trying to index and you have pointers pointing to them. So it really is the best image I could come up with there. These data structures provide the basis for random lookups and efficient access to ordered records. So ordered meaning put in tables in our case, right? Let's just dwell on that image for a second and just really let that sink in how it's this separate thing, how it makes this copy. You can start to deduce a lot just from knowing that, right? But let's go a little bit deeper. If you take our book example again, imagine you need to add a paragraph, okay, and it's gonna add the word war in there. Well, what are you doing when you have an index that's already in this book? You can't just add the word war and expect your index to be untouched, right? What you're doing is you're actually inserting two wars in there. You gotta go to the index, update that, point to where that is in your book. And our databases work the exact same way. At this point, you likely have more questions than you came in here with. So let's just add a couple more. When and how do I use this index? Why is an index on an integer faster than on a string? Why do inserts bring a performance penalty? How can you drop an index and not hurt the data? Another question I have for you. How can you open a file, say on Linux? You're messing around with it.  On top of all these questions we all know, just as working developers that as our data grows and as the amount of queries increase, the number of rows being scanned for grows. And as these requirements shift, you will need to go deeper and understand the issues that will arise. So some examples should help us out with these questions. You can, let's keep these questions in the back of our mind as we kinda go forward. I wanna do a brief disclaimer. I'm gonna be talking about these examples in the context of Postgres. There's two reasons for that. The number one is that that's what I work with every day. Number two, that's pretty much it. I work with it every day, so. If you use another flavor of relational database, don't fret, because these principles that we'll talk about generally apply. So okay, let's take some, keep those questions in the back of your head. We're gonna jump into Postgres with some examples and then we'll kinda revisit them in a bit. So I just did a simple sampling of data. I threw up here on a gist, if you wanted to follow along or check it out later. But just in Ruby, created 50,000 rows in a CSV file using the Faker gem. If you're not familiar with that, it's great. Created my table, we have an ID. We'll make that our primary key. Name, email, city, all strings. And then we just copy that in, specifying our delimiter and where we put that file. So now we have 50,000 records, okay? Why 50,000? I found that that's where I started to see a performance difference between certain things. So we're gonna talk about today. It's a tiny amount of data though. That's important to keep in mind. At Weedmaps, we have several tables with millions and millions of rows, right? And that's not even a lot of data. I think that kinda tells you a lot though. We're gonna see the performance differences on small tables, like at one with 50,000 rows. And hopefully you can kind of see the value in diving deeper into this topic. Okay, so that yields a table like this, right? That's just what you see in PSQL. Notice we have an index here, to primary key, B tree on the ID. We all know that looking up rows by that primary key will be fast and painless, but there's a catch. So remember, indexes are stored on disk, and that means this table has stored a copy and a pointer to each of the fields in addition to the actual data in the table. So moreover, for every insert and update, like we talked about with the book, that we make to those columns, it forces the database to make two writes. One write to the actual table for the desired field, the intersection of the column and your row there. And again, to make a copy of the data included with reference and some metadata to the index. So there is a small cost. Obviously, this trade-off is well worth the cost on columns that are used frequently for lookups, and most databases do more reads and writes, but just keep in mind that there is a cost. What does all of this mean for us? Well, it doesn't seem like such an obvious win to just be throwing indexes on everything willy-nilly, and that's exactly right. This is why it's the best practice to only add indexes as you need them. You need them obviously on primary keys, foreign keys, and integer columns that are used for joins, or otherwise just queried often, but you need them on string fields. Well, sometimes you do, right? And it depends on a case-by-case basis, but how do you just go about making these decisions? You can't just Google it. You really can't. Everyone's use case is different. So we kind of need to understand what these data structures are and how they act. Let's take a look a little bit here at what we got in that table. This is just a screen grab for my, so, sorry. We do a little explain here on a full table scan. So if you're not familiar with Postgres Explain, this is like a quick little crash course with that. We see this seek scan. That just means a full table scan, meaning it's gonna look at every single row, right? And then we get these weird metrics here. Here we have a cost. Now this is just an arbitrary metric, like most metrics are, that's kind of meaningless. You need some kind of reference point. So your reference point is what does it look like compared to other cost queries, right? Other explains. This, there's two numbers you gotta look at. This first one here is your startup time, your ramp up time, you call it. So you notice we got a zero for this full table scan, all 50,000 records. We got this 1022, whatever that means. We have 50,000 rows and we have 52 bytes wide, meaning the bytes as a width of what you're getting back. Okay, so not too crazy, right? And if you were to run this locally, it just happened like that. Let's do another one. We do an explain on our primary key, okay? So ID 666, because you know, well we got a whole different set of output here. This time we did an index scan using our primary key on users and look at our cost compared to our full table scan. The first thing you'll notice is a huge decrease, right? Eight compared to 1022, huge win already. Now we're only getting one row, but it's worth noting. We got this ramp up time here, which is completely different. You gotta wonder, okay, why is there a ramp up time when we're looking at one single row? Got one row, same width on that guy, and here's our index condition here. So this explain is very helpful and determining these kinds of things going forward, this is the way to do it. When performance matters and you need to make that decision and it comes down to you, I don't know how else to go about it without having this broader understanding and actually looking at what you're doing and running some basic benchmarks. Let's do a select on a string column, okay? So we set our email with string, right? Yeah, bar car. So we do an explain on users where email is artlemke at low.co, art's a real person, he's a friend of mine. We have a sequential scan, so same thing we did up here earlier, but wait, we're only looking at one row. We were looking at 50,000 rows. Why are we doing a seek scan on this one row? Look at our cost. So there's no ramp up time again, but we actually took more time to look up one row than it took us to look up 50,000 rows. The rest of this is the same. Look at this filter here we use. Okay, so no index. That's obvious. So what do we do here in this case? We need to use the email to look, maybe we're doing some authentication system. We need to use this email for lookups all the time. It is crucial that you know how to examine your DB and what you're doing, okay? But so these questions remain. Why are we starting to see these things slow down? Someone might be thinking, well it's an index, it's a string index, and those are slower, okay? Well you'll add this index here. We do this to explain. Notice the cost compared to our int column. Where's that? Right here. So 0.29 to eight, three one. 0.41, a little bit more ramp up time, a little bit more look up time. Same deal, well it's just one column. So yeah, well it's obvious, right? We're doing a string index, so that's slower. We just know that, that's a heuristic we live by. String comparisons are more expensive, even though we are dealing with the same number of records, right? This is where knowing what the actual data structure is will help you out. So indexes come in many flavors. Postgres has more than this, but these are the main ones we talk about. And you know what, in 98% in cases, your indexes are just B trees. We do have B trees, hashes, gist, spgist, gin and brain indexes in Postgres. And a lot of these, I think hash doesn't exist in a lot of other relational databases, but a lot of these do exist elsewhere as well. And our indexes, both of those were B trees. So a B tree kind of looks like this if you were to conceptualize it. It's just a simple heap-like data structure. Note that the root note has two children, right? B trees speed up your searches because they sort the values known as keys inside of each node. Depending on what it is, what is actually stored in these nodes, it will sort them differently. For example, if you've got characters compared to integers, characters would be sorted alphabetically, integers in numerical order. Although they can house potentially thousands of keys per node, they try to evenly distribute the keys among the nodes. The number of levels, that way the number of levels that you need to traverse is actually quite small, okay? So this is a gross simplification of your typical B tree, but it's more or less this.  Each pointer leads to a child node that contains more or less the same number of keys and each other as each child does. Storing and looking at data on the B tree follows really simple rules. So let's kind of take an example. Let's take a character B tree. Notice how all the data's laid out here. And let's say we wanna look up the V, okay? With your human eyes, that's like instant. Like yeah, there's the V down there, okay? But obviously computers are pretty dumb, they just do what you tell them to do and they don't know how to find this V. So let's say we issue a query, we arrive here. We have this V and we arrive at M. So you look at the root node, okay? And the rule to follow throughout is to obey alphabetical order here. So V comes after M. So we follow the right pointer down to the next node, okay? If we had something like B, we would have gone to the left, it's that simple. As you're starting to see, this is all just basic control flow and this is stuff that we do every day, right? Next you'll arrive at an intermediary node. Again, like you can just kind of see where you need to go, but how should the algorithm proceed? We just arrive here and we've got these three values and we want a V and we got a PTX. So this is a really perfect spot for binary search, right? We actually just land in the middle, we compare to whatever's there to our left. If we are greater than, we check the next value over and so forth. And when we arrive at the spot that we should be, we slide down that pointer to the next node, okay? So we could have just done a sequential scan, right? But again, I mentioned, I mean there's only three there. Cardinality of three, that's super quick to do a sequential scan. But these nodes could potentially house thousands of keys. So we just do a binary search. As luck would have it, these are sorted alphabetically every time. So you're not gonna run into any issues. If you had to sort it yourself, it wouldn't be a good candidate for that. So back to our intermediary node. We start right in the middle and we compare V is after T. So we don't need to worry about that. What comes to the left there? So there's only one value to the right remaining and that's X. V is before X. We take the pointer down in between and we land in the middle. We look to our left and compare and voila. There's our V. Okay, so now I know I just walked you through a B tree. Just for completeness, Postgres uses B plus trees. Up here on the upper right, you have an abstraction of what that looks like. But it's more or less the same idea, right? With B plus trees, the root nodes and inner nodes contain keys and pointers to lower level leaf nodes and those leaf nodes contain pointers to the heap, okay? And the heap is where your tables are stored. Okay, so in defining what this index is, we mentioned making a copy of our data and whenever we execute the create index command, Postgres saved all of our existing IDs or emails into a B tree and it became the keys of our tree. So it would be just like this one here, right? Additionally, whenever we perform an insert, the database will add to the structure. So when you add to the structure, what happens? Well, we can't do our search, our binary search if things are out of order, right? So we have this balancing act that we have to do. Every time we add something, every time we have to rebalance, right? Sometimes it's as simple as like, oh, well, this is a 10, it comes after this eight and nine, I can just slip it on the back here and we're good. There is some rebalancing that has to happen. In fact, B tree stands for balance tree, right? As mentioned before, you won't get a B tree that has wildly different differing numbers of nodes on the same level and all of this has a cost and it has to be managed something we have to remember for larger data sets. So if you go back to our example of doing this with strings, you can instantly see why this is a little bit more expensive. Not wildly expensive, you know, but it is more expensive. If our user's table had 10 million rows in it, we'd have a pretty massive B tree. Every right would potentially cause some rebalancing. We've mentioned a lot of the cost of B trees, but do keep in mind that these things are super efficient. The algorithm that Postgres uses, Lehmann and Yalla have been around, I think since the 60s, just works. B tree indexes are really great for equality and range queries and they can operate against all data types and they're also great for retrieving null values. So there's a lot of value there. Okay, so now that we have a better understanding of what these indexes are, specifically B trees, let's rethink some of these questions from earlier. Why is an index on an integer faster than a string? Well, comparing two ints is no problem, right? The string's a whole different mess. I think you can see pretty quickly. Just also, we're talking about the size of the bytes. Now that we know what this thing looks like and how it operates, you don't even need to wonder about these questions anymore. This is kind of the beauty of diving in here. Why is, why do inserts bring a performance penalty? Well, you have to do two writes every time, right? So if I've worked at a company where we did probably 80% writes and 20% reads and this became a nightmare really quick and this is the just in time moment for me where I was like, I need to learn what's going on here. How can you drop an index and not hurt the data? Why can you do that? We have this image here. What happened if we just destroy this guy? Well, we still have the tables, right? Everything's still living on the heap. So we haven't actually done anything, but if this is a new concept to you, it's kind of like, oh yeah, of course. I'll leave a couple of other questions for you to think about. Maybe we can talk about it after the talk, but how does this work with multicolumn indexes? Consider indexing a string field, okay? Every look up on the index now has to compare strings on every node inside the B-tree until it finds the desired result, which is an expensive computation compared with ints. And only then can we follow the pointer to the actual row it needs in the table, okay? So my hope in just doing this whole 200 foot view down to the nuts and bolts a little bit is that you can kind of take away some quick bullet points. You should be able to answer the previous questions. You know, that we just walked through them. Hopefully you can walk away with that. I hope that you're just more aware of what you are and what you're not indexing. That is a huge win if you can walk away with that. If you can double think like, well, let's just throw an index on that string field. Well, you should probably benchmark it. You should probably think about your use case. You should probably think about if there's a way to get around using that string field. Or, you know, if it's not a big deal, maybe you don't do lookups that often. Maybe it's an admin page that's kind of obscure. Well, maybe that's not a big deal. Or maybe you need to build a whole read API, an elastic search or something. I don't know. You got to figure that out. Know how to analyze your queries. So this is how you figure it out. Just use explain. Rails has great benchmarking, Ruby has great benchmarking tools as well. You can just do that in prior or whatever. You get the same results. Understand the basics of how a B3 index works for reason rights. So hopefully that has helped you arrive there. If you walk away with any of these things today, I'll be happy. Hopefully we all have a better understanding of one of the pillars of database performance and functionality. And thanks for attending. I've left some resources for you. One of the main inspirations for this talk was this Pat Shaughnessy blog here on Btrees from 2014, excellent read. Just some general documentation. Heroku's documentation on indexes is excellent. Obviously Postgres as well. I left a little mock data just for you if you want to play with that so you can mess around. Run into plenty of migration woes in a company like Weedmaps that has 80 engineers. So we've all kind of rallied around this gem called Strong Migrations and thrown some checks on our PR so that you don't run indexes on things, locking up the tables so our millions of users can no longer access them. There's some really great just like bullet points and the read me in there just to live by. And the gem is super helpful. And you can find me at David PM IO, just a little GitHub page where I leave a link to my Instagram, I think. I don't know. Thank you.