PGCon 2019 - Lightning Talks
CC Attribution 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Hagander, Magnus
Wilcox, Matthew
Katz, Jonathan S.
Fetter, David
et al.
A regular feature, PGCon will have a Lightning talks session, with presentations on diverse topics. The format remains essentially the same: in a one hour period, audiences are entertained and informed by a rapid fire series of short talks on interesting new or on-going work by individuals or groups. Slides aer permitted, but not obligatory; pictures are highly recommended. Topic areas include new open source software projects, works in progress for future releases of existing projects, student projects, etc. Lightning talks topics this year may make good conference papers next year! The number of slots is limited, and experience suggests there will be more takers than slots. Sign up well in advance to be assured a spot. Please subscribe to the PGCon announce mailing list and wait for the call to go out for submissions. NOTE: there is no "2" below because that lightning talk did not include slides.
 Hi. So I work for Oracle. Last year I was here working for Microsoft. You know, actually that isn't the sentiment that I had gleaned about Microsoft today, actually. I mean, everyone loves Amazon because Amazon's sponsoring the party tonight. And everyone's pretty much OK with Google having access to all of our data. But for some reason people don't seem to like Oracle all that much here. You're catching on. All right. So Oracle's a big company. We've got 137,000 employees, $180 billion of market capitalization. I can't even visualize $180 billion. That's just ridiculous. Oh, good for you. But Oracle's not just a big company. It's a cloud company. We don't just do a database. We don't have 137,000 people working on the database. And our competitors, the people who I thought you were all happy about, they have their own databases too. Microsoft, of course, has MS SQL. Amazon have nine different databases on their cloud, RDS or Aurora, Redshift, more. Some of them are based on Postgres, but Amazon's not exactly a big contributor back to Postgres yet. I went to look at Stephen Frost's GitHub repository with all the Amazon patches and there's none in there. So, you know, I don't know whether that's accurate, but Google's got at least five and including some NoSQL options like BigQuery. But competition is good, right? I mean, competition drives innovation. You know, if my SQL weren't around, you guys would be nowhere without them driving you to be better and actually keep hold of people's data. But the important thing is that competitors can collaborate. Just because the Oracle database competes with Postgres does not mean that we're enemies. We're competitors, right? And the important thing about competition is, you know, you kind of need a level playing field. And for us, really, that's SQL, right? We all implement a slightly different variation, slightly different dialects of SQL, but at least everyone who implements SQL is a competitor. We can compete for people. You can migrate from one database to another with minor adjustments to people's code. All right, you're laughing, but it's a lot easier than going to a NoSQL model, right? You know, the Bruins and the Blues are literally trading punches on the ice, but they're all members of the NHL Players Union. And yesterday, an Oracle employee loaned an HP HDMI adapter to an Amazon employee who plugged into his Apple laptop to give a presentation. Now, that's a standard, right? That's goodness, right? We all benefit from having those kinds of collaborations. And so I don't actually work on the database myself, obviously. I'm a Linux kernel guy. And it turns out there's about 150 people in Oracle's Linux kernel team. And we work on all kinds of things which benefit Linux broadly. We have our own internal projects, of course, but, you know, we do QA on Linux kernel, which just helps everybody. I mean, I suspect most of you work on Linux based systems. I know there's a couple of free BSE people in here, but by and large, most people are using Linux today. We have security people. We do RDMA stuff, which is very important to some databases, less so to others. Personally, my thing at the moment is memory management. I swore I would never do memory management, but here I am, breaking my own promises. So what I'm working on right now is huge pages. If you're not, you know, into the low-level details of kernels and micro-architectures, you probably don't know a whole lot about them. But Postgres has had support for huge pages using something like huge TLDFS or Shumget or something for a couple of years now. But what I'm working on is transparent fileback pages. And we're going to see a 2% to 6% performance improvement. And you guys are going to get that for free. You're not doing anything for that. We're doing it. We're doing it not for you. I mean, there's nobody at Oracle who wakes up and says, I'm going to make Postgres 2% to 6% faster today. But we do wake up in the morning and say, hmm, how can I improve Oracle database performance? And then you guys get the benefit of it for yourselves. So thank you for coming. Please don't kill me. And if you are going to pour your drink over me, the official Canadian way is to have me open my mouth, lay down on my back, and pour it very, very slowly, a few millilitres at a time. Thank you. Thank you very much. Sorry. Hi. So I'll start right in the middle. I want to talk about PG Bouncer Rapper, SQLizing PG Bouncer. Who has used PG Bouncer before? Okay. Who loves that console? Okay. There's always a few. Not everybody does, and I wanted to give some alternatives. But for those that haven't always used PG Bouncer, one of its awesome properties is that it can pause a Postgres database. It sort of just gives you a way to do things in the background without necessarily taking an outage and violating your SLA. That is pretty nice. This is not. Anybody seen something like this? It's a show users on a busy PG Bouncer. And it's a little hard to deal with, and it's a little hard to scroll through, and there's just not a whole lot of there, there, or I should say there's way too much there, there, and there's not a lot of usable information. So it's just too much. So this is where I go to the live demo. Here we go. So thanks. So thank you. So let's have a quick look at some of the things that we could do from PG Bouncer Rapper that you really can't do from PG Bouncer itself. So here I'm doing a little SQL on these table-like things, and up comes the actual thing that you wanted, even though in the original table-like thing this information wasn't there, so you can do joins, all that kind of fun stuff. I don't think I need to convince anybody here that SQL is fun to play with. Let's see what else we can do. Oh, just this is just a bunch of views laid atop of the show commands, and if you were wondering about the rest of the verbs, here they are. There's a disable and an enable and a kill and a pause and all these things, and you can call them from SQL. And that's the end of the live demo. Let's see if I use the right mouse. All righty. Does anybody want to hear about the history of PG Bouncer? Too bad. It comes from Skype. We made Skype tools of which PGQ, Lone Deesta, Wall Manager, and of course, PG Bouncer are our parts. I'd like to thank them for being able to do this. I'd also like to thank my employer, OneLogin. I'd also like to thank Joe Conway for DB Link. And I'd like to thank you for your patience. Thanks so much. Thank you. This one? Oh, wait. I got to. Thank you. Got it. This one? This is for the room. Wait. Our next speaker is Jonathan Katz. Going to talk about Scramming. Thank you. Hi, everyone. My name is Jonathan Katz, as Magnus said, and today we're going to tell MD5 to Scram. In New York, we tell MD5 to do something else, but then it's not as funny in this context. So first, before we start, let's have a brief history of Postgres password management, because today we're going to be talking about password-based authentication, which is something we do every day. So before Postgres 10, there were two methods. One was called password, which stored the password as plaintext in the database. And this is totally fine if you basically trust a lot of different people who are interacting with your system. And of course, we all do. And of course, you never repeat your passwords ever. And there actually was a legitimate reason to use this method, which was that your driver, your library, did not support the MD5 method, but this is no longer relevant because we do not support this method anymore. But we do still support MD5. And this was a very popular way to do password-based authentication in older Postgres, which basically you took a password, then you took a salt, which happened to be your username. You threw it in, I call it Postgres MD5, which is the MD5 hashing algorithm with MD5 prepended to it. And that was it. And that's how you stored it. And this is all in well. And then when you said, like, hey, I want to connect to Postgres, Postgres would say, all right, well, I got to challenge you. So here's a random four-byte salt, which basically, what you would do is you take that MD5 password and pre-compute it, attach the salt, put it through the Postgres MD5 again, and then the Postgres server would do the same exact thing and say, all right, cool. But the problem is that that kind of salt had almost like no randomness because if you use the same username and password for everything, you basically have the same salt for everything, sorry, the same MD5 hash for everything. But it's not like you can get that very easily because there's no way that information could be given to you. And you probably couldn't just take that authentication method and use it anywhere. So it's totally secure. Good. So MD5 needs to scram. Or as we say in New York, something else. But scram seems, it seems kind of rude, but it's not. It's actually the salted challenge response authentication method. And it's a standard. And it's a very well-defined standard. And what's also cool is it's actually pretty easy to implement, as evidence because I've implemented it independently. So the thing that's so cool about it is that it defines a way that your client and server can authenticate with each other without ever sharing the password. Really all you need to do is you need to store something like this. Or this is what Postgres stores, which is the digest you want to use, the hashing algorithm, the number of iterations you put your password through the hashing algorithm using an HMAC-based signing, the initial salt that you want to use because we always need a salt. And then you have something called the stored key and a server key, which you'll touch on. But the client can be blissfully unaware of this, except for when you do any initial generation. So how do we scram? Let's see if I can do this in one minute and 58 seconds. Client says, I want to connect. Postgres is like, all right, but you got to scram. So I'm going to send you the hashing digest for you to figure that out. The client is like, well, the client can either say, well, I don't actually support this method in Goodbye, which is now far less of a problem than it used to be. Or the client can say, great, well, let me send you some channel binding information, which we'll touch on later. The username, which Postgres completely ignores because it already knows your username. And then a nonce, a single, one-time, randomly generated piece of garbage. The server is like, OK, so you really want to scram? It's great. So I'm going to actually append to that nonce so that way we know that we're communicating with each other. I'm going to send you back the salt and the number of iterations you need to put your plaintext password into. Well, we've supplied the plaintext password on the client side, but we don't ever want to send that over. So basically, we do some hashing magic to generate what we call a client proof. And because you can all read this very quickly, you can see that we can generate this client proof. And you have to trust me when I say that we're never sending the password over. We basically just send back the nonce and how we get the client proof. The server then says, all right, well, let me validate if you can actually, you know, if the proof you sent to me is valid. And the idea is that both of them effectively derive the stored key. And they both derive the same stored key. Then they're likely valid. But of course, there's a few other things that we check. But the server now knows at this point the client is valid. But the client doesn't not necessarily know like the server is valid and the server has the correct stored key. So the server has to send back a signature for the client to validate to say like, hey, everything is OK. So there's a method to upgrade to scram basically set password encryption to scram shot 256. You leave empty five based off enabled in pghba.conf until everyone's rehashed their passwords. There's channel binding. It helps prevent man in the middle attacks. And we did that in five minutes. Hello. Can you all hear me? All right. So this short presentation is going to be about high-go software incorporated in Canada. Just wanted to take this opportunity to announce that we are creating a small team for post SQL community development. And that team is going to be based in multiple geographical locations. So the primary function of that team is going to be working doing community development. So they'll be working on new features and other community activities. Okay. So who am I? My name is Aasan Hadi. I've recently taken on a job of VP of development with high-go software. Prior to that, I was working for enterprise DB for about 15 years as a senior director of product development. Okay. So can I get a show of hands of people who have heard about high-go software before this presentation? Okay. So we've got a few there. So high-go software incorporated in Canada is a subsidiary of high-go software that's based out of China. That's our parent company. And high-go software is the biggest post-crest shop in China. They've got about 300 employees. And they are the biggest, you know, that's kind of leading the post-crest skill development and promotion of post-crest in that region. So that's high-go China. There are some other random pictures of high-go China. Got one more slide here. So this is all high-go China. All right. So high-go China, you know, they're also a core part of post-crest skill association in China, which is made under CUPU, which is China Open Source Software Promotion Union. So you can see a list of all the other members that are part of that association. I can only read a few from there, so I guess you'd probably do the same. So this is another flyer of China Post-Crest Skill Association that you can read. Okay. There is the high-go, the high-go software China has done some work, you know, some open source work. So they've got a couple of tools out there. One of them is the X-Log Miner. It's a tool for wall parsing. Then there is a PG-LiTool, which is a light weight backup tool. These tools are still kind of work in progress, so there's more work to be done before we get them to production quality, but they're all available on GitHub, and they've been done by the high-go China team. So this is a high-go software-in-cooperated China-Canada office. So we've got a few employees in our Canada office. This is another picture of our office in Canada. Then this is a high-go software-in-cooperated office in Pakistan. We don't actually own the whole building. We just have a small office in that building, but one day we hope that we can have a building like that. So I think to some people's surprise, high-go, Postgres is also very popular in Pakistan. We've got at least four companies in Pakistan, Postgres companies in Pakistan that have their development office, and Postgres is also very popular in the government sector. The national identity card is basically, they're using Postgres, and every citizen of Pakistan is supposed to have a national identity card. So Postgres is actually getting very popular in Pakistan, and they also have development that's happening there as well. So in terms of what we plan to do as part of this team that we are putting together for Postgres community development, obviously you want to start with things like patch reviews, blogging, maintenance, bug fixing, obviously translations appearing and talking in conferences. That's kind of our short-term plan. In the long or medium term, we're looking at doing some development in areas like backup performance, scalability, horizontal scalability, vertical scalability. We also want to do some work in the security area, and there are also some projects that we plan to undertake under the R&D banner. So that's kind of our medium long-term plans. Obviously the areas that I've talked about, we are not restricted to work in just those areas, so there are suggestions from the community members. We are welcome to those suggestions, and you can contact us. Okay, so this is the last slide. We are, Higo is also hosting a Postgres conference in China in Beijing in July, and we're expecting around 200 to 300 people, and we would just encourage everybody to attend that conference. It's going to be a great conference, and my friend Grant here, who's organizing it, can give you all the details, and if you don't find that enticing enough, there's going to be a trip to one of the mountains in Beijing as well. Thank you very much. Okay, is that work? So, yes, I'm Chapman Flack, and for a few years, I've been working with the R&D, and for a few years now, I have been maintaining the PL Java extension. There's a story behind that. Let me touch on it this way. If you picture sort of the design of a typical PL extension, you've got Postgres here on one side of it. There are some constraints. It has certain APIs it exports, and you don't get to change SQL. You've got your programming language over here that you're going to use. You don't get to change its spec. You do get to design all of this stuff in the middle, how you're going to embed this thing, what you're going to do, what PL types you're going to use, for what SQL types, how you convert from one to the other. The easiest answer is probably, well, I'll use the simplest PL types around. Maybe I'll use strings. I will call the Postgres data type in-out routines and, you know, done 40 lines. PL Java is a funny case because even this stuff in the middle is pinned down by formal specs. So, SQL part 13 tells you what these details have to look like. It tells you how you're going to do the data type mapping, which has to be the same way it's done in JDBC, which is more than 40 lines. And that's, it makes it convenient for using PL Java. You've got these nice Java types. They have useful behaviors. You get them without much work. For hacking on PL Java, it turns it into this game where you're trying to satisfy four different specs at the same time. And that was the same sort of thing that got me my unofficial title in a lab in grad school of the glue meister. And that might be why when I started sending, you know, early patches to Thomas Hallgren for PL Java, he was ready to spend his next 10 years doing something else. And he said, oh, look, here's the right kind of weirdo. And so here I am, and I've done a few releases since then focused on not breaking things while getting enough familiarity to jump into more ambitious changes. And some of those are forced by JDBC as it continues to evolve. PL Java originally kind of has assumed that in the C code, it knows what data type transformations it's going to need to make. It can have those all teed up before the Java code needs them. Meanwhile, JDBC keeps adding support for new Java types, some of them as alternate mappings of the same SQL types. So it no longer makes sense to assume you know what to tee up in advance. That took some hammering and bending in PL Java 152. And we'll get some more work in a major release down the road. I promised I would say something about Grawl VM, which is this Oracle Labs ETH Zurich quest that they've had for several years. And it just 11 days ago had its first non-pre-release release. They don't do it for us, but competitors can collaborate. And it's a polyglot VM. It runs Java, JavaScript, R, Python, Ruby, LLVM, bit code. You can compile stuff from C, other JVM languages. And it does all this with very little cost for inter-language calls and actually inter-language data accesses. And beneath all of that, it is still a JVM. If you have PL Java installed in your database, you can change the setting of libJVM location to where you've downloaded Grawl VM. The next session that starts up with PL Java will be running on Grawl VM. You can create Java functions the same way you're used to. And now these functions can use the polyglot API and do stuff in other languages. What you can't do at this point is create language, create function in those other languages and use those directly from SQL. For that, you're going to want me to finish the changes on the data type mapping that I alluded to earlier. And if anyone has questions in 18 seconds. Thank you. Next speaker is Oskun. Can I present somebody else's talk? Yes. Oskun, I'm with Microsoft Slash Citus Data. And today I'm going to talk about PG Auto Failover, a Postgres solution for automated failover and high availability. And it was actually Lucas who was going to present this talk, but his flight got cancelled. He got stuck in New York. And he called me up and said, hey, Oskun, can you present in my place? And I told him, hey, sure, what do you want me to do? He said, you just open source this extension and then PGCon is a critical audience. So I'd like for you to talk about it and underpromise and overdeliver later. So keep it modest. And I said, sure, Lucas. And I decided to go ballistic. So PG Auto Failover is the best HA solution there is ever, not just for Postgres, for any database really. And if you don't like the framing, Lucas is flying here. He'll be here in about an hour or so. Please share your feedback with him. If you can't find him, he's the tall Austrian guy. I have his cell number two. And on a more serious note, PG Auto Failover is an auto failover solution for Postgres. And why another auto failover solution, high availability solution? It's the combination of three things that make PG Auto Failover unique. It's simple, first and foremost, robust and open source. And when I say simple, like no external dependencies, you can download this thing without any anything else set it up and run in eight steps. It's robust, fairly well tested. And there's a finite state machine that manages state transitions. And we recently open sourced it under the Postgres license. It's very new. And then the primary reason I'm presenting here is to gather your feedback into the extension and solution. Here's a simple architecture diagram. Basically you have the primary that does the streaming replication to the secondary. And then we have a monitoring node in there. And the monitoring node is the authoritative node that manages the state and then state transitions between the primary and the secondary. So in this picture, if your primary fails, the monitor does health checks regularly and at some point decides to promote the secondary. If your secondary fails, the monitor node again intervenes and then does the right thing. And if the monitor node fails, then as long as you have a healthy primary and secondary, the cluster keeps being operational. So the architecture is very simple in that sense too. As long as you have two out of three of the instances running, your cluster will always be operational. And then here's the other side of it. Basically, PgAuto failover sports, Postgres versions 10 and upwards. So you have your client application connecting over the Pq 10 plus afterwards. And in the connection string, you specify, hey, here are the machines that I want to connect to. And if the primary fails and then the secondary gets promoted, basically the application at the Pcq layer is going to detect that and then automatically failover to the secondary. In summary, in short, PgAuto failover is a very simple way to set up auto failover on HA for Postgres. The first version is just out and then we actually do take HA very seriously. And we'd love your feedback. We have the GitHub repo here in case you'd like to take a look at it. And this actually, as I just stated, is the first project that we open sourced as part of the Microsoft family. And you can read more about the announcement in here. Thank you. I think that's it. I needed this to be reusable. So, bad code is like swearing around children. Robin Williams had a special back in the 80s called Live at the Met, where he was talking about being a new father and driving around Los Angeles and someone cut him off in traffic. And he said a bad word. And in the back seat was his three-year-old son, who heard that bad word, and then spent the rest of the day repeating it, using it in sentences, conjugating it. And that, what I want to impress upon you today is that bad code is like swearing around children. Some of the code you have written is bad. You should feel bad about this. Shame is important. It could be like this, or this, nonoptimal piece of code, or this. People new to programming, newer than you, will see what you do because they know you. You're smart. You're here today. You must be pretty smart. It doesn't mean you're wise. Those newer programmers will look up to you and assume that what you have done must have been a good thing because you're smart. So they can't copy your code if they can't find your code. So you don't want newer programmers stumbling across a script like this because when they see that it empties a table, they might try to quit out of it. And you commented out the transaction, and now you just lost a production table. You definitely don't want them seeing your college error code. Don't show them that. I always wanted to write my own in-chef riser. This is the closest I got. You wrote the code. You put effort into it. It works. It's still good. Why let it go to waste? We should leave that wisdom around. Consider these two little bits of code. The first one you wrote, and the second one you wrote later, possibly with the same function name, but it's more optimal. If one of these children sees these, at least the first one, they're going to use it because you're smart. You knew what you were doing when you wrote that. Dead code, code you're not using. It's wasting your time in compiling, in refactoring it, in your unit tests, and it just is being late around where somebody who doesn't know what to do with it could find it. I just finished a Python two to three upgrade at one of my clients, and a whole lot of the code we went through doesn't get run. I wonder how many hours I spent fixing code that no one actually uses. There are ways to make this better. Don't comment out code. Delete it. Delete code is debugged code. It's still in GitHub. You've got it. You can get it back. Just don't leave it around. Delete that function. Make a comment about why it was a bad thing, but don't leave it in the source file. When you're doing examples for other people, add in today's date and the versions of all the tools you're using. Time continues on after you write the post, and what you wrote in that post may not be relevant in the future. You can't predict whether it will be or not, so put in a marker like this that helps people understand where you were when you made this advice. When you are deleting a code or when you can't get rid of things or when you can't make the update that you want to make because there isn't time, at least record your regrets in an issue tracker of some sort so that somebody can get around to it. In summary, bad code is like swearing around children. If you don't pay attention to what you're letting them see, they'll just say, fuck it. Is this how bad it would end? You know what? There we go. Dan didn't think running a conference was enough work, so you're also going to do a talk? Yes. A very short talk. So how many of you type things into a computer or a laptop? How many of you do that? Oh, come on. Be brave. How many of you do that on more than one laptop or machine? More than three. OK. How many times have you written something somewhere and not been able to find it because you can't remember where you wrote it? OK. That's what this solves. Sync thing is like, where's the clicker? This clicker? This clicker. OK. There we go. So sync thing hits all the buttons. I've got it running at home. It basically syncs my documents directory, but you can make it sync whatever directories you want. And it's not like our sync. It's peer to peer. So what my solution involves is having three systems running sync thing. My laptop, my laptop, my other laptop, and a server. That's actually four. The reason I have the server doing that is because not all the laptops have to be on at the same time in order for this to work, just the server. So whatever laptop's on, syncs to the server. And then whenever another laptop comes along, it syncs to the server as well. So when you install it and get it running, it pops up a little web page on the computer running and have a look at it. And in my case, what was the focus button? I had a red button here. So that's one of my laptops. That's my server. That's the laptop it's running on. And with this is about 20. How many? I can't remember. There's something like 212 directories, 2,600 files, and 60 gig. The initial sync takes a while, but after that is very low CPU. It's doing like 0.23% at the moment. And now I don't have to worry about where I wrote something. It's always in my Documents folder, and it doesn't matter where it is. The advantage to syncing it to this server at home, the server here, is that this is running on a ZFS snapshot. It gets snapshot. It gets backed up. It gets replicated to other locations. So I think it's very difficult for me to lose my documents directory accidentally. And that one. Any questions? In the back? There's one over here. You can get the other one. You can get repos of the new CPUs and stuff like that. No, this is not about repos. This is just about plain, flat text files, for example. You could have your source files in here, and it would sync the source files somewhere else. But it won't. It'll also sync the Git directories, but I don't think that's what you want. Yeah. Any other questions? Here. What's the source? Just set. I'm trying to answer that. Yeah, there's syncthing.net. I thought I had another slide. I think it's on my other laptop. I think I added that and didn't upload it. So syncthing.net, you can get it there. They have various packages and stuff like that that works. We have another minute and a half. I do. I do. But I'm going to finish if there's no more questions. OK, thank you. Thank you. No. So that's your pointer, and that's your advance. Left and right. So our next speaker is Renee. She's going to talk about the D&I for the day. I have no idea what that means, but I think she's going to tell me. Diversity and inclusion. Now I know what it is. Now we all know. So an acquaintance of mine, Sumina Hari Hariswara, was talking with me at Bang Bang Con. And she said, yeah, you know, all of the marginalized background people that I met feel like they have to give the D&I talk. And I was like, yeah, I'm looking forward to not being the person to do it. But here I am volunteering again. I'm Renee Phillips. I'm based in New York. We're in Ottawa. Did I get that right? Good. So we all work with people no matter if it's a million years ago today ordering things from Amazon, getting delivery on food, sending mail, working alone in a cubicle. There are people around us in our lives, even if we're unemployed, we're still working cooperatively with people in some way. So this is something that applies to everybody. So we should all be awakened, concerned about this. So thank you for staying. I am very annoyed by these two books and many other things that often come up in the D&I world. And what these two books in particular do is they say, hey, ladies. And it's generally focused on ladies and a very particular kind of lady, very middle class, very white, very able-bodied. But it says, hey, ladies, here's how you fix your D&I problem. And I'm here to tell you that this is not my problem. This is our problem. So we all can do things to help. And I'm going to give you three things to think about and do. Bathrooms, complaints, and hiring. I called this talk, Complaints About Bathrooms and Auditions. So we want everybody to come to our spaces. We want people to feel welcome. We want people to participate. We want the ideas of people who don't look or speak or sound or walk or talk like us. And that includes not just gender. This includes race, first language, abilities, whether that's physical or neurotypical or other biological types of things. We want people to be able to get into our spaces, stay there, be comfortable, and get more people into our spaces. So Complaints About Bathrooms. I will cry at work. I would prefer not to have to cry in the bathroom. I really don't want you to tell me not to cry at work. I want all of us to be able to cry at work. If your dog dies, you should be allowed to cry about that. If you stub your toe and you're in physical pain, you should be allowed to cry about that. So my crying at work hopefully will open the door for all of you to cry at work. If I do have to cry in the bathroom, I want the bathroom to be a reasonable and safe place to be for everybody. So things that you can all do at work is if you notice or hear that your workplace is having menstrual supplies in the women's room, maybe mention to your workplace that they might include having those supplies in the men's room, because people have all kinds of gender, gender representations, gender identities, gender feelings, might be menstruating in the men's room, need those supplies. So that's one thing that you can do. Just suggest to your boss, you did this thing in one place, please be equitable and do it in another place, because one of the most dangerous things for a trans man to do who, and I hate to use the word passing, but someone who has passing privilege to out themselves and say, hey, I need a tampon, that could be really dangerous. So if you just put them there and then don't ask any questions, yay. This is especially great if you're holding interviews or social events at your office. People can see, hey, this is an equitable place to work. If they don't want to work in a place that is an equitable place to work, great, you just scared off a jerk. If they are people who need or want accommodations or will appreciate those accommodations, great, you just encourage them to be there. More bathroom stuff. This is a Marcel Duchamp piece of art. It's a urinal. There are a lot of reasons getting into the complaint thing that people don't make complaints. Here's a list of them. Here's a list of responses we often get when we make complaints or bring up concerns. The list is very long. There are a lot of things that give cover for terrible people to continue doing terrible things. When someone comes to you at work, whether you're in a position of power or not, listen and don't invalidate their concerns. Maybe help the person make a plan. Maybe say that sucks. If you overhear something terrible happening, make a code of conduct complaint. Even if that terrible conduct wasn't addressed at you, you can still report it. Finally, auditions. We know that blind auditions work. So if you're in a workplace that's hiring, please encourage your workplace to anonymize resumes. There are great ways to do this. We know from musicians that truly anonymous audition practices have helped putting carpets down. Talk to me if you have questions. Thank you. I hope if anyone else was planning to give a lightning talk, I missed it, so you're not doing it. Julian is going to talk about Hola. Hi. So I want to talk about Poirot 4, which is obviously the fourth version of Poirot. So probably you don't know about this tool. Quick introduction. What it does, it's Poirot is an acronym for PostgreSQL Analyzer. So what it does is help you to give some insight on your workload, on your databases, on more or less real-time fashion. So the idea is you can take a snapshot on a various data sources. So the first one is obviously PG-stat statements, when you can get a lot of really useful contours, like number execution and everything. But there are support for a lot of other extensions. One of my favorite ones is a PG-weight sampling, which is available for PG-10 and above. So what this extension does is sample weight events at high frequency, and it's super cool. So you can use that to have insight of weight events. You can also get information about CPU usage and physical disk usage with another extension, which is called PG-stat K-cache. You can also sample predicates, so the wear clothes and the join clothes, to have more information, like what index are missing and everything. Then you store everything in a Postgres database. Obviously, you retrieve it. And then the UI gives you a lot of nice, I hope, dashboards to see everything. And there is also, since the last version, a wizard, which kind of do a global analysis of everything happened on one database and try to come up with a global index suggestion, which tried to get the smallest amount of indexes to create, to optimize your workload by trying to get as much multi-glot index to avoid too many indexes and stuff like that. So the interesting thing is, for this version, is there is now a remote mode, which means you don't have to store everything on your production database and make everything even slower. So that's a nice thing to add. It's also work on a standby. So there is a graph, which doesn't mean a lot, but you can see a lot of fancy things. There is documentation, if you're interested, so you can see everything and on the project. Some screenshots, because it's much more fun. So you see, you have on the UI multiple instance, you can collect on one database. So you can choose whatever instance you want to drill down to see what's happening. So you have a system matrix with a PgStatK cache. So you can see the context switches and the patch faults and stuff like that. So you have that as a per query, per database, and per server level. So it's quite nice. I don't think there is another way to have that for now on POS-RES. You can also have the CPU usage, once again, per query, per instance, and per database. So it's a nice way to see if you have a system CPU usage or user CPU usage. Or it's also nice to see if you're missing something in your C code or function or whatever. Still, with the same extension, you can compute a real heat ratio, which means you can see what was in the shad buffer, in the operating system cache, and just read on disk. So it can help you to tune like, oh, you're missing some shad buffer or index because you're reading too much from disk or whatever. Another extension I did not mention in PgTrack settings, which can track whatever change happened on your server. And then you can put some dots and see what setting changed at one point. So you can see if you put your work time from 1 gigabyte to 1 megabyte, and everything suddenly is super slow, you can see what changed and how it changed on your graph. So probably can help you to drill down whatever change was effective or not. The white event I was talking about, so you can see a graph of everything happening and a chart with the number of logs. So in this case, you can see transaction ID is the highest one. Probably it was a PgBench doing a lot of small inserts, so you're just waiting on that. It's a global index suggestion. So it's a big picture, which probably can't see a lot, but you can see here a lot of queries and just these four index trying to optimize all those queries and saying one index can optimize six queries. So it can be nice. Reference, you have all the links, and there is an online demo available. And thank you very much. [? APPLAUSE?] Well, thank you very much to all of our Lightning Talk speakers. That's it for today at this venue. Some of you will probably want to join us at the social events, which somebody in the audience will now tell me when it begins. Six o'clock. Those of you who don't do that, we'll see you all again for the second day tomorrow. Thank you very much. [? APPLAUSE?]
 Hi. So I work for Oracle. Last year I was here working for Microsoft. You know, actually  that isn't the sentiment that I had gleaned about Microsoft today, actually. I mean, everyone  loves Amazon because Amazon's sponsoring the party tonight. And everyone's pretty much  OK with Google having access to all of our data. But for some reason people don't seem  to like Oracle all that much here. You're catching on. All right. So Oracle's a big company.  We've got 137,000 employees, $180 billion of market capitalization. I can't even visualize  $180 billion. That's just ridiculous. Oh, good for you. But Oracle's not just a big company.  It's a cloud company. We don't just do a database. We don't have 137,000 people working on the  database. And our competitors, the people who I thought you were all happy about, they  have their own databases too. Microsoft, of course, has MS SQL. Amazon have nine different  databases on their cloud, RDS or Aurora, Redshift, more. Some of them are based on Postgres,  but Amazon's not exactly a big contributor back to Postgres yet. I went to look at Stephen  Frost's GitHub repository with all the Amazon patches and there's none in there. So, you  know, I don't know whether that's accurate, but Google's got at least five and including  some NoSQL options like BigQuery. But competition is good, right? I mean, competition drives  innovation. You know, if my SQL weren't around, you guys would be nowhere without them driving  you to be better and actually keep hold of people's data. But the important thing is  that competitors can collaborate. Just because the Oracle database competes with Postgres  does not mean that we're enemies. We're competitors, right? And the important thing about competition  is, you know, you kind of need a level playing field. And for us, really, that's SQL, right?  We all implement a slightly different variation, slightly different dialects of SQL, but at  least everyone who implements SQL is a competitor. We can compete for people. You can migrate  from one database to another with minor adjustments to people's code. All right, you're laughing,  but it's a lot easier than going to a NoSQL model, right? You know, the Bruins and the  Blues are literally trading punches on the ice, but they're all members of the NHL Players  Union. And yesterday, an Oracle employee loaned an HP HDMI adapter to an Amazon employee who  plugged into his Apple laptop to give a presentation. Now, that's a standard, right? That's goodness,  right? We all benefit from having those kinds of collaborations. And so I don't actually  work on the database myself, obviously. I'm a Linux kernel guy. And it turns out there's  about 150 people in Oracle's Linux kernel team. And we work on all kinds of things which  benefit Linux broadly. We have our own internal projects, of course, but, you know, we do  QA on Linux kernel, which just helps everybody. I mean, I suspect most of you work on Linux  based systems. I know there's a couple of free BSE people in here, but by and large,  most people are using Linux today. We have security people. We do RDMA stuff, which is  very important to some databases, less so to others. Personally, my thing at the moment  is memory management. I swore I would never do memory management, but here I am, breaking  my own promises. So what I'm working on right now is huge pages. If you're not, you know,  into the low-level details of kernels and micro-architectures, you probably don't know  a whole lot about them. But Postgres has had support for huge pages using something like  huge TLDFS or Shumget or something for a couple of years now. But what I'm working on is transparent  fileback pages. And we're going to see a 2% to 6% performance improvement. And you guys  are going to get that for free. You're not doing anything for that. We're doing it. We're  doing it not for you. I mean, there's nobody at Oracle who wakes up and says, I'm going  to make Postgres 2% to 6% faster today. But we do wake up in the morning and say, hmm,  how can I improve Oracle database performance? And then you guys get the benefit of it for  yourselves. So thank you for coming. Please don't kill me. And if you are going to pour  your drink over me, the official Canadian way is to have me open my mouth, lay down on  my back, and pour it very, very slowly, a few millilitres at a time. Thank you.  Thank you very much.  Sorry.  Hi.  So I'll start right in the middle.  I want to talk about PG Bouncer Rapper, SQLizing PG Bouncer.  Who has used PG Bouncer before?  Okay.  Who loves that console?  Okay.  There's always a few.  Not everybody does, and I wanted to give some alternatives.  But for those that haven't always used PG Bouncer, one of its awesome properties is that it can  pause a Postgres database.  It sort of just gives you a way to do things in the background without necessarily taking  an outage and violating your SLA.  That is pretty nice.  This is not.  Anybody seen something like this?  It's a show users on a busy PG Bouncer.  And it's a little hard to deal with, and it's a little hard to scroll through, and there's  just not a whole lot of there, there, or I should say there's way too much there, there,  and there's not a lot of usable information.  So it's just too much.  So this is where I go to the live demo.  Here we go.  So thanks.  So thank you.  So let's have a quick look at some of the things that we could do from PG Bouncer Rapper that  you really can't do from PG Bouncer itself.  So here I'm doing a little SQL on these table-like things, and up comes the actual thing that  you wanted, even though in the original table-like thing this information wasn't there, so you  can do joins, all that kind of fun stuff.  I don't think I need to convince anybody here that SQL is fun to play with.  Let's see what else we can do.  Oh, just this is just a bunch of views laid atop of the show commands, and if you were  wondering about the rest of the verbs, here they are.  There's a disable and an enable and a kill and a pause and all these things, and you  can call them from SQL.  And that's the end of the live demo.  Let's see if I use the right mouse.  All righty.  Does anybody want to hear about the history of PG Bouncer?  Too bad.  It comes from Skype.  We made Skype tools of which PGQ, Lone Deesta, Wall Manager, and of course, PG Bouncer are  our parts.  I'd like to thank them for being able to do this.  I'd also like to thank my employer, OneLogin.  I'd also like to thank Joe Conway for DB Link.  And I'd like to thank you for your patience.  Thanks so much.  Thank you.  This one?  Oh, wait.  I got to.  Thank you.  Got it.  This one?  This is for the room.  Wait.  Our next speaker is Jonathan Katz.  Going to talk about Scramming.  Thank you.  Hi, everyone.  My name is Jonathan Katz, as Magnus said, and today we're going to tell MD5 to Scram.  In New York, we tell MD5 to do something else, but then it's not as funny in this context.  So first, before we start, let's have a brief history of Postgres password management, because  today we're going to be talking about password-based authentication, which is something we do every  day.  So before Postgres 10, there were two methods.  One was called password, which stored the password as plaintext in the database.  And this is totally fine if you basically trust a lot of different people who are interacting  with your system.  And of course, we all do.  And of course, you never repeat your passwords ever.  And there actually was a legitimate reason to use this method, which was that your driver,  your library, did not support the MD5 method, but this is no longer relevant because we  do not support this method anymore.  But we do still support MD5.  And this was a very popular way to do password-based authentication in older Postgres, which basically  you took a password, then you took a salt, which happened to be your username.  You threw it in, I call it Postgres MD5, which is the MD5 hashing algorithm with MD5 prepended  to it.  And that was it.  And that's how you stored it.  And this is all in well.  And then when you said, like, hey, I want to connect to Postgres, Postgres would say,  all right, well, I got to challenge you.  So here's a random four-byte salt, which basically, what you would do is you take that MD5 password  and pre-compute it, attach the salt, put it through the Postgres MD5 again, and then the  Postgres server would do the same exact thing and say, all right, cool.  But the problem is that that kind of salt had almost like no randomness because if you  use the same username and password for everything, you basically have the same salt for everything,  sorry, the same MD5 hash for everything.  But it's not like you can get that very easily because there's no way that information could  be given to you.  And you probably couldn't just take that authentication method and use it anywhere.  So it's totally secure.  Good.  So MD5 needs to scram.  Or as we say in New York, something else.  But scram seems, it seems kind of rude, but it's not.  It's actually the salted challenge response authentication method.  And it's a standard.  And it's a very well-defined standard.  And what's also cool is it's actually pretty easy to implement, as evidence because I've  implemented it independently.  So the thing that's so cool about it is that it defines a way that your client and server  can authenticate with each other without ever sharing the password.  Really all you need to do is you need to store something like this.  Or this is what Postgres stores, which is the digest you want to use, the hashing algorithm,  the number of iterations you put your password through the hashing algorithm using an HMAC-based  signing, the initial salt that you want to use because we always need a salt.  And then you have something called the stored key and a server key, which you'll touch on.  But the client can be blissfully unaware of this, except for when you do any initial generation.  So how do we scram?  Let's see if I can do this in one minute and 58 seconds.  Client says, I want to connect.  Postgres is like, all right, but you got to scram.  So I'm going to send you the hashing digest for you to figure that out.  The client is like, well, the client can either say, well, I don't actually support this method  in Goodbye, which is now far less of a problem than it used to be.  Or the client can say, great, well, let me send you some channel binding information,  which we'll touch on later.  The username, which Postgres completely ignores because it already knows your username.  And then a nonce, a single, one-time, randomly generated piece of garbage.  The server is like, OK, so you really want to scram?  It's great.  So I'm going to actually append to that nonce so that way we know that we're communicating  with each other.  I'm going to send you back the salt and the number of iterations you need to put your  plaintext password into.  Well, we've supplied the plaintext password on the client side, but we don't ever want  to send that over.  So basically, we do some hashing magic to generate what we call a client proof.  And because you can all read this very quickly, you can see that we can generate this client  proof.  And you have to trust me when I say that we're never sending the password over.  We basically just send back the nonce and how we get the client proof.  The server then says, all right, well, let me validate if you can actually, you know,  if the proof you sent to me is valid.  And the idea is that both of them effectively derive the stored key.  And they both derive the same stored key.  Then they're likely valid.  But of course, there's a few other things that we check.  But the server now knows at this point the client is valid.  But the client doesn't not necessarily know like the server is valid and the server has  the correct stored key.  So the server has to send back a signature for the client to validate to say like, hey,  everything is OK.  So there's a method to upgrade to scram basically set password encryption to scram shot 256.  You leave empty five based off enabled in pghba.conf until everyone's rehashed their  passwords.  There's channel binding.  It helps prevent man in the middle attacks.  And we did that in five minutes.  Hello.  Can you all hear me?  All right.  So this short presentation is going to be about high-go software incorporated in Canada.  Just wanted to take this opportunity to announce that we are creating a small team for post  SQL community development.  And that team is going to be based in multiple geographical locations.  So the primary function of that team is going to be working doing community development.  So they'll be working on new features and other community activities.  Okay.  So who am I?  My name is Aasan Hadi.  I've recently taken on a job of VP of development with high-go software.  Prior to that, I was working for enterprise DB for about 15 years as a senior director  of product development.  Okay.  So can I get a show of hands of people who have heard about high-go software before this  presentation?  Okay.  So we've got a few there.  So high-go software incorporated in Canada is a subsidiary of high-go software that's  based out of China.  That's our parent company.  And high-go software is the biggest post-crest shop in China.  They've got about 300 employees.  And they are the biggest, you know, that's kind of leading the post-crest skill development  and promotion of post-crest in that region.  So that's high-go China.  There are some other random pictures of high-go China.  Got one more slide here.  So this is all high-go China.  All right.  So high-go China, you know, they're also a core part of post-crest skill association  in China, which is made under CUPU, which is China Open Source Software Promotion Union.  So you can see a list of all the other members that are part of that association.  I can only read a few from there, so I guess you'd probably do the same.  So this is another flyer of China Post-Crest Skill Association that you can read.  Okay.  There is the high-go, the high-go software China has done some work, you know, some open  source work.  So they've got a couple of tools out there.  One of them is the X-Log Miner.  It's a tool for wall parsing.  Then there is a PG-LiTool, which is a light weight backup tool.  These tools are still kind of work in progress, so there's more work to be done before we  get them to production quality, but they're all available on GitHub, and they've been  done by the high-go China team.  So this is a high-go software-in-cooperated China-Canada office.  So we've got a few employees in our Canada office.  This is another picture of our office in Canada.  Then this is a high-go software-in-cooperated office in Pakistan.  We don't actually own the whole building.  We just have a small office in that building, but one day we hope that we can have a building  like that.  So I think to some people's surprise, high-go, Postgres is also very popular in Pakistan.  We've got at least four companies in Pakistan, Postgres companies in Pakistan that have their  development office, and Postgres is also very popular in the government sector.  The national identity card is basically, they're using Postgres, and every citizen of Pakistan  is supposed to have a national identity card.  So Postgres is actually getting very popular in Pakistan, and they also have development  that's happening there as well.  So in terms of what we plan to do as part of this team that we are putting together for  Postgres community development, obviously you want to start with things like patch reviews,  blogging, maintenance, bug fixing, obviously translations appearing and talking in conferences.  That's kind of our short-term plan.  In the long or medium term, we're looking at doing some development in areas like backup  performance, scalability, horizontal scalability, vertical scalability.  We also want to do some work in the security area, and there are also some projects that  we plan to undertake under the R&D banner.  So that's kind of our medium long-term plans.  Obviously the areas that I've talked about, we are not restricted to work in just those  areas, so there are suggestions from the community members.  We are welcome to those suggestions, and you can contact us.  Okay, so this is the last slide.  We are, Higo is also hosting a Postgres conference in China in Beijing in July, and we're expecting  around 200 to 300 people, and we would just encourage everybody to attend that conference.  It's going to be a great conference, and my friend Grant here, who's organizing it, can  give you all the details, and if you don't find that enticing enough, there's going to  be a trip to one of the mountains in Beijing as well.  Thank you very much.  Okay, is that work?  So, yes, I'm Chapman Flack, and for a few years, I've been working with the R&D, and  for a few years now, I have been maintaining the PL Java extension.  There's a story behind that.  Let me touch on it this way.  If you picture sort of the design of a typical PL extension, you've got Postgres here on  one side of it.  There are some constraints.  It has certain APIs it exports, and you don't get to change SQL.  You've got your programming language over here that you're going to use.  You don't get to change its spec.  You do get to design all of this stuff in the middle, how you're going to embed this  thing, what you're going to do, what PL types you're going to use, for what SQL types, how  you convert from one to the other.  The easiest answer is probably, well, I'll use the simplest PL types around.  Maybe I'll use strings.  I will call the Postgres data type in-out routines and, you know, done 40 lines.  PL Java is a funny case because even this stuff in the middle is pinned down by formal  specs.  So, SQL part 13 tells you what these details have to look like.  It tells you how you're going to do the data type mapping, which has to be the same way  it's done in JDBC, which is more than 40 lines.  And that's, it makes it convenient for using PL Java.  You've got these nice Java types.  They have useful behaviors.  You get them without much work.  For hacking on PL Java, it turns it into this game where you're trying to satisfy four different  specs at the same time.  And that was the same sort of thing that got me my unofficial title in a lab in grad school  of the glue meister.  And that might be why when I started sending, you know, early patches to Thomas Hallgren  for PL Java, he was ready to spend his next 10 years doing something else.  And he said, oh, look, here's the right kind of weirdo.  And so here I am, and I've done a few releases since then focused on not breaking things  while getting enough familiarity to jump into more ambitious changes.  And some of those are forced by JDBC as it continues to evolve.  PL Java originally kind of has assumed that in the C code, it knows what data type transformations  it's going to need to make.  It can have those all teed up before the Java code needs them.  Meanwhile, JDBC keeps adding support for new Java types, some of them as alternate mappings  of the same SQL types.  So it no longer makes sense to assume you know what to tee up in advance.  That took some hammering and bending in PL Java 152.  And we'll get some more work in a major release down the road.  I promised I would say something about Grawl VM, which is this Oracle Labs ETH Zurich quest  that they've had for several years.  And it just 11 days ago had its first non-pre-release release.  They don't do it for us, but competitors can collaborate.  And it's a polyglot VM.  It runs Java, JavaScript, R, Python, Ruby, LLVM, bit code.  You can compile stuff from C, other JVM languages.  And it does all this with very little cost for inter-language calls and actually inter-language  data accesses.  And beneath all of that, it is still a JVM.  If you have PL Java installed in your database, you can change the setting of libJVM location  to where you've downloaded Grawl VM.  The next session that starts up with PL Java will be running on Grawl VM.  You can create Java functions the same way you're used to.  And now these functions can use the polyglot API and do stuff in other languages.  What you can't do at this point is create language, create function in those other languages  and use those directly from SQL.  For that, you're going to want me to finish the changes on the data type mapping that  I alluded to earlier.  And if anyone has questions in 18 seconds.  Thank you.  Next speaker is Oskun.  Can I present somebody else's talk?  Yes.  Oskun, I'm with Microsoft Slash Citus Data.  And today I'm going to talk about PG Auto Failover, a Postgres solution for automated  failover and high availability.  And it was actually Lucas who was going to present this talk, but his flight got cancelled.  He got stuck in New York.  And he called me up and said, hey, Oskun, can you present in my place?  And I told him, hey, sure, what do you want me to do?  He said, you just open source this extension and then PGCon is a critical audience.  So I'd like for you to talk about it and underpromise and overdeliver later.  So keep it modest.  And I said, sure, Lucas.  And I decided to go ballistic.  So PG Auto Failover is the best HA solution there is ever, not just for Postgres, for  any database really.  And if you don't like the framing, Lucas is flying here.  He'll be here in about an hour or so.  Please share your feedback with him.  If you can't find him, he's the tall Austrian guy.  I have his cell number two.  And on a more serious note, PG Auto Failover is an auto failover solution for Postgres.  And why another auto failover solution, high availability solution?  It's the combination of three things that make PG Auto Failover unique.  It's simple, first and foremost, robust and open source.  And when I say simple, like no external dependencies, you can download this thing without any  anything else set it up and run in eight steps.  It's robust, fairly well tested.  And there's a finite state machine that manages state transitions.  And we recently open sourced it under the Postgres license.  It's very new.  And then the primary reason I'm presenting here is to gather your feedback into the extension  and solution.  Here's a simple architecture diagram.  Basically you have the primary that does the streaming replication to the secondary.  And then we have a monitoring node in there.  And the monitoring node is the authoritative node that manages the state and then state  transitions between the primary and the secondary.  So in this picture, if your primary fails, the monitor does health checks regularly and  at some point decides to promote the secondary.  If your secondary fails, the monitor node again intervenes and then does the right thing.  And if the monitor node fails, then as long as you have a healthy primary and secondary,  the cluster keeps being operational.  So the architecture is very simple in that sense too.  As long as you have two out of three of the instances running, your cluster will always  be operational.  And then here's the other side of it.  Basically, PgAuto failover sports, Postgres versions 10 and upwards.  So you have your client application connecting over the Pq 10 plus afterwards.  And in the connection string, you specify, hey, here are the machines that I want to  connect to.  And if the primary fails and then the secondary gets promoted, basically the application at  the Pcq layer is going to detect that and then automatically failover to the secondary.  In summary, in short, PgAuto failover is a very simple way to set up auto failover on  HA for Postgres.  The first version is just out and then we actually do take HA very seriously.  And we'd love your feedback.  We have the GitHub repo here in case you'd like to take a look at it.  And this actually, as I just stated, is the first project that we open sourced as part  of the Microsoft family.  And you can read more about the announcement in here.  Thank you.  I think that's it.  I needed this to be reusable.  So, bad code is like swearing around children.  Robin Williams had a special back in the 80s called Live at the Met, where he was talking  about being a new father and driving around Los Angeles and someone cut him off in traffic.  And he said a bad word.  And in the back seat was his three-year-old son, who heard that bad word, and then spent  the rest of the day repeating it, using it in sentences, conjugating it.  And that, what I want to impress upon you today is that bad code is like swearing around  children.  Some of the code you have written is bad.  You should feel bad about this.  Shame is important.  It could be like this, or this, nonoptimal piece of code, or this.  People new to programming, newer than you, will see what you do because they know you.  You're smart.  You're here today.  You must be pretty smart.  It doesn't mean you're wise.  Those newer programmers will look up to you and assume that what you have done must have  been a good thing because you're smart.  So they can't copy your code if they can't find your code.  So you don't want newer programmers stumbling across a script like this because when they  see that it empties a table, they might try to quit out of it.  And you commented out the transaction, and now you just lost a production table.  You definitely don't want them seeing your college error code.  Don't show them that.  I always wanted to write my own in-chef riser.  This is the closest I got.  You wrote the code.  You put effort into it.  It works.  It's still good.  Why let it go to waste?  We should leave that wisdom around.  Consider these two little bits of code.  The first one you wrote, and the second one you wrote later, possibly with the same function  name, but it's more optimal.  If one of these children sees these, at least the first one, they're going to use it because  you're smart.  You knew what you were doing when you wrote that.  Dead code, code you're not using.  It's wasting your time in compiling, in refactoring it, in your unit tests, and it just is being  late around where somebody who doesn't know what to do with it could find it.  I just finished a Python two to three upgrade at one of my clients, and a whole lot of the  code we went through doesn't get run.  I wonder how many hours I spent fixing code that no one actually uses.  There are ways to make this better.  Don't comment out code.  Delete it.  Delete code is debugged code.  It's still in GitHub.  You've got it.  You can get it back.  Just don't leave it around.  Delete that function.  Make a comment about why it was a bad thing, but don't leave it in the source file.  When you're doing examples for other people, add in today's date and the versions of all  the tools you're using.  Time continues on after you write the post, and what you wrote in that post may not be  relevant in the future.  You can't predict whether it will be or not, so put in a marker like this that helps people  understand where you were when you made this advice.  When you are deleting a code or when you can't get rid of things or when you can't make  the update that you want to make because there isn't time, at least record your regrets in  an issue tracker of some sort so that somebody can get around to it.  In summary, bad code is like swearing around children.  If you don't pay attention to what you're letting them see, they'll just say, fuck it.  Is this how bad it would end?  You know what?  There we go.  Dan didn't think running a conference was enough work,  so you're also going to do a talk?  Yes.  A very short talk.  So how many of you type things into a computer or a laptop?  How many of you do that?  Oh, come on.  Be brave.  How many of you do that on more than one laptop or machine?  More than three.  OK.  How many times have you written something somewhere  and not been able to find it because you can't remember  where you wrote it?  OK.  That's what this solves.  Sync thing is like, where's the clicker?  This clicker?  This clicker.  OK.  There we go.  So sync thing hits all the buttons.  I've got it running at home.  It basically syncs my documents directory,  but you can make it sync whatever directories you want.  And it's not like our sync.  It's peer to peer.  So what my solution involves is having three systems  running sync thing.  My laptop, my laptop, my other laptop, and a server.  That's actually four.  The reason I have the server doing that  is because not all the laptops have  to be on at the same time in order for this to work,  just the server.  So whatever laptop's on, syncs to the server.  And then whenever another laptop comes along,  it syncs to the server as well.  So when you install it and get it running,  it pops up a little web page on the computer running  and have a look at it.  And in my case, what was the focus button?  I had a red button here.  So that's one of my laptops.  That's my server.  That's the laptop it's running on.  And with this is about 20.  How many?  I can't remember.  There's something like 212 directories, 2,600 files,  and 60 gig.  The initial sync takes a while, but after that  is very low CPU.  It's doing like 0.23% at the moment.  And now I don't have to worry about where I wrote something.  It's always in my Documents folder,  and it doesn't matter where it is.  The advantage to syncing it to this server at home,  the server here, is that this is running on a ZFS snapshot.  It gets snapshot.  It gets backed up.  It gets replicated to other locations.  So I think it's very difficult for me  to lose my documents directory accidentally.  And that one.  Any questions?  In the back?  There's one over here.  You can get the other one.  You can get repos of the new CPUs and stuff like that.  No, this is not about repos.  This is just about plain, flat text files, for example.  You could have your source files in here,  and it would sync the source files somewhere else.  But it won't.  It'll also sync the Git directories,  but I don't think that's what you want.  Yeah.  Any other questions?  Here.  What's the source?  Just set.  I'm trying to answer that.  Yeah, there's syncthing.net.  I thought I had another slide.  I think it's on my other laptop.  I think I added that and didn't upload it.  So syncthing.net, you can get it there.  They have various packages and stuff like that that works.  We have another minute and a half.  I do.  I do.  But I'm going to finish if there's no more questions.  OK, thank you.  Thank you.  No.  So that's your pointer, and that's your advance.  Left and right.  So our next speaker is Renee.  She's going to talk about the D&I for the day.  I have no idea what that means, but I think she's going to tell me.  Diversity and inclusion.  Now I know what it is.  Now we all know.  So an acquaintance of mine, Sumina Hari Hariswara,  was talking with me at Bang Bang Con.  And she said, yeah, you know, all of the marginalized background  people that I met feel like they have to give the D&I talk.  And I was like, yeah, I'm looking forward to not being  the person to do it.  But here I am volunteering again.  I'm Renee Phillips.  I'm based in New York.  We're in Ottawa.  Did I get that right?  Good.  So we all work with people no matter if it's a million years  ago today ordering things from Amazon,  getting delivery on food, sending mail,  working alone in a cubicle.  There are people around us in our lives,  even if we're unemployed, we're still working cooperatively  with people in some way.  So this is something that applies to everybody.  So we should all be awakened, concerned about this.  So thank you for staying.  I am very annoyed by these two books  and many other things that often come up in the D&I world.  And what these two books in particular do  is they say, hey, ladies.  And it's generally focused on ladies  and a very particular kind of lady, very middle class,  very white, very able-bodied.  But it says, hey, ladies, here's how you fix your D&I problem.  And I'm here to tell you that this is not my problem.  This is our problem.  So we all can do things to help.  And I'm going to give you three things to think about and do.  Bathrooms, complaints, and hiring.  I called this talk, Complaints About Bathrooms and Auditions.  So we want everybody to come to our spaces.  We want people to feel welcome.  We want people to participate.  We want the ideas of people who don't look or speak or sound  or walk or talk like us.  And that includes not just gender.  This includes race, first language, abilities,  whether that's physical or neurotypical or other  biological types of things.  We want people to be able to get into our spaces, stay there,  be comfortable, and get more people into our spaces.  So Complaints About Bathrooms.  I will cry at work.  I would prefer not to have to cry in the bathroom.  I really don't want you to tell me not to cry at work.  I want all of us to be able to cry at work.  If your dog dies, you should be allowed to cry about that.  If you stub your toe and you're in physical pain,  you should be allowed to cry about that.  So my crying at work hopefully will open the door for all  of you to cry at work.  If I do have to cry in the bathroom,  I want the bathroom to be a reasonable and safe place  to be for everybody.  So things that you can all do at work  is if you notice or hear that your workplace is having  menstrual supplies in the women's room,  maybe mention to your workplace that they might include  having those supplies in the men's room,  because people have all kinds of gender,  gender representations, gender identities, gender feelings,  might be menstruating in the men's room, need those supplies.  So that's one thing that you can do.  Just suggest to your boss, you did this thing in one place,  please be equitable and do it in another place,  because one of the most dangerous things for a trans man  to do who, and I hate to use the word passing,  but someone who has passing privilege to out themselves  and say, hey, I need a tampon, that could be really dangerous.  So if you just put them there and then don't ask any questions,  yay.  This is especially great if you're holding interviews  or social events at your office.  People can see, hey, this is an equitable place to work.  If they don't want to work in a place that  is an equitable place to work, great,  you just scared off a jerk.  If they are people who need or want accommodations  or will appreciate those accommodations, great,  you just encourage them to be there.  More bathroom stuff.  This is a Marcel Duchamp piece of art.  It's a urinal.  There are a lot of reasons getting into the complaint thing  that people don't make complaints.  Here's a list of them.  Here's a list of responses we often  get when we make complaints or bring up concerns.  The list is very long.  There are a lot of things that give cover for terrible people  to continue doing terrible things.  When someone comes to you at work,  whether you're in a position of power or not,  listen and don't invalidate their concerns.  Maybe help the person make a plan.  Maybe say that sucks.  If you overhear something terrible happening,  make a code of conduct complaint.  Even if that terrible conduct wasn't addressed at you,  you can still report it.  Finally, auditions.  We know that blind auditions work.  So if you're in a workplace that's hiring,  please encourage your workplace to anonymize resumes.  There are great ways to do this.  We know from musicians that truly anonymous audition  practices have helped putting carpets down.  Talk to me if you have questions.  Thank you.  I hope if anyone else was planning to give a lightning talk,  I missed it, so you're not doing it.  Julian is going to talk about Hola.  Hi.  So I want to talk about Poirot 4, which is obviously  the fourth version of Poirot.  So probably you don't know about this tool.  Quick introduction.  What it does, it's Poirot is an acronym for PostgreSQL  Analyzer.  So what it does is help you to give some insight  on your workload, on your databases,  on more or less real-time fashion.  So the idea is you can take a snapshot on a various data  sources.  So the first one is obviously PG-stat statements,  when you can get a lot of really useful contours,  like number execution and everything.  But there are support for a lot of other extensions.  One of my favorite ones is a PG-weight sampling, which  is available for PG-10 and above.  So what this extension does is sample weight events  at high frequency, and it's super cool.  So you can use that to have insight of weight events.  You can also get information about CPU usage and physical disk  usage with another extension, which is called PG-stat K-cache.  You can also sample predicates, so the wear clothes  and the join clothes, to have more information,  like what index are missing and everything.  Then you store everything in a Postgres database.  Obviously, you retrieve it.  And then the UI gives you a lot of nice, I hope,  dashboards to see everything.  And there is also, since the last version,  a wizard, which kind of do a global analysis of everything  happened on one database and try to come up  with a global index suggestion, which  tried to get the smallest amount of indexes  to create, to optimize your workload  by trying to get as much multi-glot index  to avoid too many indexes and stuff like that.  So the interesting thing is, for this version,  is there is now a remote mode, which  means you don't have to store everything on your production  database and make everything even slower.  So that's a nice thing to add.  It's also work on a standby.  So there is a graph, which doesn't mean a lot,  but you can see a lot of fancy things.  There is documentation, if you're interested,  so you can see everything and on the project.  Some screenshots, because it's much more fun.  So you see, you have on the UI multiple instance,  you can collect on one database.  So you can choose whatever instance  you want to drill down to see what's happening.  So you have a system matrix with a PgStatK cache.  So you can see the context switches and the patch  faults and stuff like that.  So you have that as a per query, per database,  and per server level.  So it's quite nice.  I don't think there is another way  to have that for now on POS-RES.  You can also have the CPU usage, once again, per query,  per instance, and per database.  So it's a nice way to see if you have a system CPU usage  or user CPU usage.  Or it's also nice to see if you're  missing something in your C code or function or whatever.  Still, with the same extension, you  can compute a real heat ratio, which  means you can see what was in the shad buffer,  in the operating system cache, and just read on disk.  So it can help you to tune like, oh,  you're missing some shad buffer or index  because you're reading too much from disk or whatever.  Another extension I did not mention in PgTrack settings,  which can track whatever change happened on your server.  And then you can put some dots and see  what setting changed at one point.  So you can see if you put your work time from 1  gigabyte to 1 megabyte, and everything suddenly is super  slow, you can see what changed and how it changed on your graph.  So probably can help you to drill down  whatever change was effective or not.  The white event I was talking about,  so you can see a graph of everything happening  and a chart with the number of logs.  So in this case, you can see transaction ID is the highest  one.  Probably it was a PgBench doing a lot of small inserts,  so you're just waiting on that.  It's a global index suggestion.  So it's a big picture, which probably can't see a lot,  but you can see here a lot of queries  and just these four index trying to optimize all those queries  and saying one index can optimize six queries.  So it can be nice.  Reference, you have all the links,  and there is an online demo available.  And thank you very much.  [? APPLAUSE?]  Well, thank you very much to all of our Lightning Talk speakers.  That's it for today at this venue.  Some of you will probably want to join us  at the social events, which somebody in the audience  will now tell me when it begins.  Six o'clock.  Those of you who don't do that, we'll  see you all again for the second day tomorrow.  Thank you very much.  [? APPLAUSE?]
Lightning_Talks_-_Magnus_Hagander_0_491.png Lightning_Talks_-_Magnus_Hagander_492_551.png Lightning_Talks_-_Magnus_Hagander_552_611.png Lightning_Talks_-_Magnus_Hagander_612_2723.png Lightning_Talks_-_Magnus_Hagander_2724_3851.png Lightning_Talks_-_Magnus_Hagander_3852_4655.png Lightning_Talks_-_Magnus_Hagander_4656_4775.png Lightning_Talks_-_Magnus_Hagander_4776_7235.png Lightning_Talks_-_Magnus_Hagander_7236_7619.png Lightning_Talks_-_Magnus_Hagander_7620_14663.png Lightning_Talks_-_Magnus_Hagander_14664_16211.png Lightning_Talks_-_Magnus_Hagander_16212_16319.png Lightning_Talks_-_Magnus_Hagander_16320_16631.png Lightning_Talks_-_Magnus_Hagander_16632_16691.png Lightning_Talks_-_Magnus_Hagander_16692_16751.png Lightning_Talks_-_Magnus_Hagander_16752_17999.png Lightning_Talks_-_Magnus_Hagander_18000_18431.png Lightning_Talks_-_Magnus_Hagander_18432_18551.png Lightning_Talks_-_Magnus_Hagander_18552_19919.png Lightning_Talks_-_Magnus_Hagander_19920_21827.png Lightning_Talks_-_Magnus_Hagander_21828_25883.png Lightning_Talks_-_Magnus_Hagander_25884_26027.png Lightning_Talks_-_Magnus_Hagander_26028_27263.png Lightning_Talks_-_Magnus_Hagander_27264_27455.png Lightning_Talks_-_Magnus_Hagander_27456_27683.png Lightning_Talks_-_Magnus_Hagander_27684_31271.png Lightning_Talks_-_Magnus_Hagander_31272_31991.png Lightning_Talks_-_Magnus_Hagander_31992_36299.png Lightning_Talks_-_Magnus_Hagander_36300_39167.png Lightning_Talks_-_Magnus_Hagander_39168_39899.png Lightning_Talks_-_Magnus_Hagander_39900_40643.png Lightning_Talks_-_Magnus_Hagander_40644_41303.png Lightning_Talks_-_Magnus_Hagander_41304_45911.png Lightning_Talks_-_Magnus_Hagander_45912_47699.png Lightning_Talks_-_Magnus_Hagander_47700_48383.png Lightning_Talks_-_Magnus_Hagander_48384_48491.png Lightning_Talks_-_Magnus_Hagander_48492_48611.png Lightning_Talks_-_Magnus_Hagander_48612_48779.png Lightning_Talks_-_Magnus_Hagander_48780_49127.png Lightning_Talks_-_Magnus_Hagander_49128_49427.png Lightning_Talks_-_Magnus_Hagander_49428_49847.png Lightning_Talks_-_Magnus_Hagander_49848_49991.png Lightning_Talks_-_Magnus_Hagander_49992_50039.png Lightning_Talks_-_Magnus_Hagander_50040_50291.png Lightning_Talks_-_Magnus_Hagander_50292_50675.png Lightning_Talks_-_Magnus_Hagander_50676_51215.png Lightning_Talks_-_Magnus_Hagander_51216_51443.png Lightning_Talks_-_Magnus_Hagander_51444_52427.png Lightning_Talks_-_Magnus_Hagander_52428_53507.png Lightning_Talks_-_Magnus_Hagander_53508_55067.png Lightning_Talks_-_Magnus_Hagander_55068_55355.png Lightning_Talks_-_Magnus_Hagander_55356_56159.png Lightning_Talks_-_Magnus_Hagander_56160_57731.png Lightning_Talks_-_Magnus_Hagander_57732_59819.png Lightning_Talks_-_Magnus_Hagander_59820_63203.png Lightning_Talks_-_Magnus_Hagander_63204_63251.png Lightning_Talks_-_Magnus_Hagander_63252_63443.png Lightning_Talks_-_Magnus_Hagander_63444_64223.png Lightning_Talks_-_Magnus_Hagander_64224_66719.png Lightning_Talks_-_Magnus_Hagander_66720_66899.png Lightning_Talks_-_Magnus_Hagander_66900_67151.png Lightning_Talks_-_Magnus_Hagander_67152_67343.png Lightning_Talks_-_Magnus_Hagander_67344_68219.png Lightning_Talks_-_Magnus_Hagander_68220_68279.png Lightning_Talks_-_Magnus_Hagander_68280_70835.png Lightning_Talks_-_Magnus_Hagander_70836_72695.png Lightning_Talks_-_Magnus_Hagander_72696_73655.png Lightning_Talks_-_Magnus_Hagander_73656_77942.png