PGCon 2016 - 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
Jacobson, Moshe
Pan, Constantin S.
Kelvich, Stas
Munro, Thomas
Nagayasu, Satoshi
Korotkov, Alexander
Rielau, Serge
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!
 Hey, everybody. My name is Moshe Jacobson from NeedWorks, and I'm excited to announce that my extension, Sci-In Audit with a really cool logo, is ready for production now. It's been refined over the last few years, and it's an extension for in-database DML logging. So any kind of inserts, updates, or deletes that you do in your database, it'll be logged into your database with searchability through a view. So with Sci-In Audit, you can log all DML column by column, so you can choose which columns you want to log in your database. You can search your logs using a simple view. You just do a query, and it pulls out all your logs from as far back as you want. You can configure what to log using a config table. So it's just a matter of update your config table, set logging as on or off for whatever columns you want to turn on or off. And the cool part about it is if you have an application where your users are logging into your application and doing stuff, and that stuff gets done in the database, your Sci-In Audit will actually allow you to link the operation to the application user, so you can tell exactly who did every operation that happens in your database, and it's great for accountability. You can also put a label on each operation so that you can, when you're going back through your logs, you can tell where this operation happened from, what exactly was going on. It's really, really useful for forensics. You can also back up and restore your logs with the scripts. It dumps out to basically compress CSV files, very, very space efficient. You can, another cron job will rotate and drop your logs after they get a certain age. You can keep your logs on a separate table space if you want a large, slow volume to hold your logs, whereas a fast volume will do the rest of your database. And you can undo your transactions, actually, which is a really cool feature of it. It's not really undoing, of course. It's just playing everything back in reverse, but it's been a lifesaver for us at my company. Many times we've done a SQL command that did something we didn't want, and then all of a sudden it's there in the logs. You can just undo it. And it's made to be super easy. You just install the extension. You run a couple commands, and you're already logging everything in your database. So it's all written in SQL and PLPGSQL. It's all trigger-based. So it's not using any of the logical replication of infrastructure or anything like that. It's compatible with Postgres 9.3 and newer, and it's been production tested for a while now. The purpose of this lightning talk is to gauge interest in this as I'm trying to get this onto Amazon RDS. It's, I think, ready for prime time. And I was hoping to get a show of hands of people who might be interested in using this extension on their own application. Anybody interested in something like this? What about with, is there any modifications? If you have any ideas of modifications that you'd like to see, raise your hand. Okay. So we got a couple people interested in this. But yeah, if you're interested in trying it out, I love your feedback. We literally don't know how anybody else does without this at my company. We always say I literally don't know how other people do it. So very, very useful tool for us. I hope it can be a useful tool for somebody else as well. So if you want to download it, there's the link right there. And thank you to NeedWorks for sponsoring the development. I'm going to print this. Okay, let's talk about elephants and raft. My name is Konstantin Pan. I'm from PostgreSQL Professional. And during our implementation of multi-master and other cluster experiments, we needed to have a replicated key value storage for distributed deadlock detection and node failure detection. And we also already had implemented raft consensus protocol in C for our distributed transaction demo. So the idea is to use raft for replicated key value storage. This was implemented as an extension. It's available over this link. To use it, you just add shared-prolet libraries and configure the node number and other peers on each node and call create extension raftable. You can use set, get, and list functions in SQL and in C. And when it's running, the processes look like this. On each node, you have multiple backends and one raftable worker, which implements raft protocol over UDP. And it keeps the state machine in shared memory. So when a backend needs to perform a get operation, it just looks into the shared memory. And when it needs to put something new, it sends a query over TCP to the raftable leader and then waits until it gets replicated at this node. We need to do some more testing and implement node management in raft. That's all. Welcome, all. That's gone really fuzzy. Oh, that's better. Hello. My name's Dave. I've been working on PG admin for nearly 20 years now, believe it or not. Thank you. So who's used PG admin 3? Quick show of hands, very fast. I've got time to mess around. Who's used PG admin 2? A few people. What about the original PG admin, well, PG admin nothing. All right, for the last six months or so, we've been working on PG admin 4. Woohoo! PG admin 4 is a complete rewrite of PG admin 3. Well, it's just a rewrite in general. And if Greg's computer will actually change, what am I supposed to press? Base? Arrow? Yeah, I pressed that. Nothing happened. Really? Really. Technology. Take down! Take down! Oh, no. Is it a continuous mode? I don't know. Can we stop the clock? No, no, you get the clock. I'm feeling hard done by here. All right, so PG admin 4, if we can ever show you any of it, has been designed as a web application. Well, then it cut off the bottom was the problem. Well, no, no, no, it's close enough. It is. It still won't show the next page. There you go. Display that page. So we built it as a web application, backends written in Python. You missed all my good slides. That's no problem. I even put a cat slide in there because somebody told me you needed a cat in a presentation like this. Oh, we're working on it. It says loading. Really, really slow. I'm going home. All right, so it's been designed as a web application written in Python on the back end, mostly JavaScript and jQuery on the front end. Pretty big, the photos. We've designed it so that you can run it on a web server in the cloud managing all your servers. Or we've got a runtime desktop application that will allow it to run basically standalone, much as you'd run with PG admin 3 right now. We have all the things that you used to see before. Oh, we're actually, did you ignore the picture of the cat? That's the problem. So you're not supposed to have a cat. Those are the ones. All right, so we've got a few dashboards in there. I mean, you can tell that looks very different from PG admin 3. We actually stuck with the tree view, which is one of the few things that does look kind of similar. We looked at various different ways of navigating around the database structure. And frankly, the tree view is about the only way that really seems to work and not feel contrived and just, so we added some dashboards to give you something interesting to look at, some little graphs and things to show you what your database server's doing. Obviously, you can look at object properties. It's all pretty fine compared to how it used to be. We're still working on final touches here. So there's going to be tweaks and improvements over the next few weeks. You can edit objects. Now, all of this is responsive. So if you go on a smaller screen, it will rewrap and do all the things you'd expect when you try and run it on your iPhone. If anyone does try and run it on your iPhone, then don't come running to me if you have problems. It's just ridiculous trying to design a database like that. We have a query tool. PG admin 3 has a separate query tool and data editor. You know, you'd right click on a table and get up a separate data editor. We've merged those into one in this tool, so you can run a query in it. You can edit the data down below. We're going to make it smarter and smarter. Right now, the first version, it depends how you launch it as to whether it will let you edit the data. But we're putting smarts in there, so they'll understand and try and figure out whether the query you've hand typed is something that we can sensibly try and update. And we're doing for time. Tell me I'll get an extra minute, please. You already got the extra minute. Query plan visualization. There's more improvements coming on that. Procedural language debugger, maintenance tools we're doing back up and so on. Outfit release is coming soon. PG admin hack is at postgresql.org if you want to take a look. Or you can check out the code from the git.postgresql.org right now and have a play with it. But look out for it. Be with you probably around the time of 9.6. Finally, who likes Europe? Hey. So as you may know, Magnus and I and various other people that are here at the moment, organized this little tiny conference every year. We're doing our eighth one in Tallinn, Estonia, November 1st to 4th. It's really warm at that time of year. You'll love it. Take a look at the website. We'd love to have you come join us. Thank you. Thank you. Thank you. Let's see if the next set of slides work better. Okay. Okay. You must have taken a really high-res photo. Did they tweet it now? I'll make sure it gets to the program. Oh, no. The direction of the copy is the internet. Yeah, thanks. So, hello everyone. I want to talk here about several patches and extensions that we did in Postgres Pro last year. And that dealing with distributing transactions and helping to create distributed database out of the Postgres. So basically about two things. One thing we call distributed transaction manager. And second one, how we can achieve multi-master replication with the first thing. So, distributed transaction manager. It's a communication protocol, along with some algorithms that allow us to achieve distributed, can see distributed isolation and distributed transaction between different nodes. So, we can set up several nodes and run transactions that spans two nodes, for example. So, talking about Postgres, we created small patch that exposes several internal routines about visibility and log access and implemented an extension that allows us to create distributed transactions. And we done several integrations with Postgres FDW and PGShard. And also it can be used in application-based sharding. So, you can actually run start transaction on one node, on different node, call routines on that node, and that will promote transaction as a global one. So, let's see some example based on FDW. So, suppose we have simple bank test. So, there is a table with user ID and amount of money. And we have two different installations. Shard 1 and Shard 2 with local tables that create, that have user accounts. And we have third nodes that create like FDW connection to both of that nodes. And runs following transaction on that table. That's on the third node. Stable T is empty on that node, but it can look to the two shards. And we start transferring money. And actually it can be done, users can live on different nodes. And, parallelly, execute some of all accounts. Since there is a transaction, we expect that some will be constant all the time. But actually no any transactional information pushed down through a Postgres FDW by default. So, it's extended bench and it's one of the tools that we are using in our testing. It actually implements following transaction concurrently. And total is that results that summarizing transaction get. So, it's fluctuating. It's actually, it's repeatable read. So, it sees intermediate states inside the transaction. And we can use our extension, Pgtsdtm times stand, distribute transaction manager, create extension. It will run the ground worker that can talk with such ground worker on different node. And after that, there will be proper transaction isolation. And out of that, we can build a multi-master. So, it's built on top of Pg logical. It wraps update and selects as a global transactions. We do a concurrent application of transactions that replicated. So, if you have several big transactions, they will be applied concurrently. And also that Constantine talked about, we use raft-based storage for a log detection and dealing with failures. So, our implementation right now, it's about a half of speed of standalone Postgres for writes. And the same speed for reads. It can recover from node failures. So, node can, for example, go and reboot. And after it will be available again, it will replicate changes. And cluster will be available for writes when majority is online. Also, it can deal with network partition. And we are working on that right now. And can work as an extension if that XTM API will go into core or we will merge some, or we'll just merge one implementation of DTM into core. So, thank you. I think that was the best timing so far with nine seconds to go. Okay. So, the cat is now up on Twitter. Okay. Hi everybody. My name is Thomas Monroe. I work for Enterprise DB. I'm like everyone else who's spoken, I'm going to be talking about some software that doesn't exist. And specifically about a SQL standard feature that I think is really interesting and that we don't have in fact no one has. First of all, I'm going to travel back in time with my time machine to sometime in the mid-90s when I was out of university. And my first job, I came across a client that had a non-relational data store. And they had lots of objects except more than like millions and millions of things. And these things were all connected to each other with these pointers and there were these back pointers. So, you could navigate from an asset to a site and from a site back to all the assets. So, that's whatever you know, you could navigate around this graph. But the forward pointers and back pointers were kind of linked to each other. That sounds like it should work just fine, right? I mean, if you're going to update things, you've got to just lock the right objects and move these pointers around. And everything should be perfectly consistent at all times. So, let's get some concurrency happening and hang on a second. What's happened here? Now, we've got some pointers to some things that have gone and some forward pointers and back pointers that are mixed up. Yeah, something's gone wrong here. So, this is real. This is an experience I had. And so, a group of us were asked to come in and figure out how we were going to find out what's gone wrong with this data. And so, the idea was to build a report that would figure out which two-way pointers were not in sync. And after an initial phase of doing these reports, just write some software that would figure out which pointer was missing. And it must be pretty obvious how to fix it, right? So, I fondly think back to that time, it was the eventual constraints team, because that's kind of the thing that was missing from that system and the thing that in the universe that we live in, we have obviously traveling back in time now to the 1970s, all this great relational data bank stuff, which Ted Codd and a bunch of others gave us, so that we don't have problems like that, right? We have these things called constraints. There's unique constraints, there's foreign key constraints, that's specifically the thing that would deal with that problem. And then there's these check constraints that can, you know, impose some kind of constraint on the rows in your tables. But wait, there's more. The SQL standard says that you can do stuff like this. You can say there should be no more than 15 students in any given class, and there's a check constraint attached to a table that says, you know, this count star shouldn't give more than 15. Now, we can't actually do that. But remind me. We can't do that because that contains a sub-query and that's what Postgres will tell you. But in fact, no existing RDBMS does that. Now, there are a couple of RDBMS that will accept that, but they don't actually work. They just run the check when you insert something, but they don't check all the reference data, and that's of course the hard bit. Second, the other thing which is really difficult about it is dealing with concurrency. And you might think that serializable could help you with that, but generally speaking, people are afraid of serializable. And in most databases, it doesn't even work properly, right? Okay, so here's the crazy idea. We could think really hard and make an analyzer that can efficiently figure out which constraints need to be checked. Now, that's difficult and I haven't got time to explain why, but it could be done. And note that the same kind of analysis is going to be needed for materialized views. We get to doing incremental materialized view maintenance. Then if you required serializable isolation for any DML which is touching something that's referenced by one of these checks, you could have a scenario like this. You come along and try and access this enrollment table and insert another student, and it just tells you, go away, you're not running a serializable. I can't actually run the checks required for this update. So you run the same thing as serializable, and now it can reject it. You're trying to put too many students in there, and the check said, you know, you can only have 15 that's going to run that count. That's something we can't do today, but if we did that, it would be the first database to have that. And, you know, it's something that's been in the SQL standard since 1992, and it's a kind of consistency that people actually ask for quite often, I find. People who are new to using databases expect it to better do that sort of thing. Yeah, and actually, most people can express this kind of stuff quite easily. You know, it's just a sequel, right? So we might actually be the only RDBMS that could actually implement this and get it working, because we have our amazing SSI system. That's me. I'm done. Thank you. Okay. Thank you. My name is Satoshi Nagasu. I came from Japan. As some of you may remember, I gave a pitch at the last PGCon, which was the first plan of PGD Asia. Do you remember? Then one year passed. So it's time to share some updates about the conference with you. In the last march, this is my last pitch. In the last march, we made the first Pan-Asian process conference in Singapore. The PGD Asia 2016 was held on March 17th and 19th, as a joint effort with FOSS Asia 2016. Actually, PGD Asia has 90 speakers, 90 regular sessions and writing talks, as usual, and also 100 attendees. At that conference, Bruce gave the keynote, Dilip gave Postgres 9.6 performance talk, Savada-san introduced the vacuum freezing map, and also Umar talked about Big Data and Postgres. And also, E-Dia came to Singapore, and other Japanese new guys introduced migration technology from Morocco. And of course, many other great talks there. I would like to show some pictures about the conference. This is Happy Anniversary, 20 years anniversary study at Singapore. And reception, Bruce keynote, and talks, attendees, talks, meetups, and many people. This is group shot from day one. As I mentioned before, the PGD Asia was a joint effort with FOSS Asia. So this is a picture from day two, the PGD Asia day two, and also FOSS Asia day two. So there are many talks out there, and many people enjoyed both the Postgres conference and FOSS Asia at the same time. So, okay, this is a picture from post Asia day two. So, okay, it's time to think of the next event. The next plan has not fixed yet, but PGD Asia 2017 will be held in the same format, in the same season and same region. So please do not miss the next one if you are interested in coming to Asia. So if you are interested in coming to Singapore, please let me know and let's keep in touch. Okay, it's my time is over, and next is Mikhail Stam. How long do I have? One minute. So how long do I have? One minute? Two minutes? No, I'm joking. Like, I don't have much to say. Just you already know where you are going at the beginning of September. You will be in Estonia. I would recommend the beginning of November. Sorry, sorry. After that, you will be in California. And here is a new conference that will happen at the end of November, which is in Asia. PGD Asia 2016, the first one of its name. It's going to be here. Actually, it's going to be close to here. This is going to be in Tokyo, Akirabara. And Akirabara is known for that also. So there will be a lot of people there, and it is aimed at becoming the biggest conference in Asia for post-West QL. So people would like to do some developer meeting. There would be some business community track. And there's going also to be a call for paper that was announced should be in July. There is a dedicated team to that. And there is also a website which is up. So welcome to Japan. That's all. Thank you. Hi, my name is Konstantin Konizhnik. I'm working for the professional. But now I want to present the result of yet another Russian team from the Institute of System Programming of Russian Academy of Science. They have a huge experience in dynamic code generation and want to apply this experience to improve speed of progress. They are using LLVM grid, which is the de facto standard now for dynamic code generation. It is used in a large number of products. If you are developing your own programming language or speeding up existing ones, then LLVM is for you. So try to marry LLVM with Postgres and see results. This is Postgres query execution plan. And at the right you see result of LLVM grid code produced corresponding to this query execution plan. For example, in this query you can see that 50% of time is spent in the exact quality which evaluates expression and using LLVM allows to reduce it to 6% and almost speed up this query about two times. Certainly it is not first attempt to use dynamic code generation for Postgres. The same thing was done in VTSRDB, but it is commercial product and not open source. And these guys are going to publish the result of their work in open source. There is one major difference in approach used in VTSRDB and Institute of System Programming. Postgres contains about 2,000 operators. The first most preferred approach is to just manually rewrite them using LLVM. But it requires a large huge amount of work and academic people prefer to solve more generic tasks instead of doing such stupid work. So then develop converter which allows to automatically convert this method to LLVM code. The first step of this conversion is standard C-Lang compiler which produce LLVM grid code. Then converter generates C++ code which generates LLVM code. And finally, I've got complete tree which evaluates query. This is once again result of generation LLVM bit code for Postgres query execution plan. Here is the first query of the PCH. You can see that aggregation takes about 75% and the quite complex expressions are used in aggregations. And using GIT allows to significantly speed up execution of this query. They get right now three times improvement at this query but it's just first attempt and then expect much more speed up. But using GIT is not the only optimizations I've implemented. Another useful thing is replacing pull model with push style. It allows, according to their results, it allows to reach about 25% of speed up of query execution. Then have manually converted most of Postgres SQL nodes. Not all nodes are currently converted. For example, bitmap.hipscan not currently converted. Most of speed up is achieved at first query but I expect that, for example, Q6 also will be significantly improved once they have implemented bitmap.hipscan. So conclusions. I have done a lot of work in quite small amount of time because this work was started just in this year. No, we all learn what the lightning talk is, right? I'm sorry, you're out of time. Thank you. Hi, my name is Michael Richardson. I live in Ottawa and I'm a Postgres user. So I'll ask you to start by saying thank you to all of you because I don't think I sometimes I don't even know what version of Postgres I'm using. But two days a week I spend working for a small ISP in Montreal that is in the business to business ISP space. And they mostly do VoIP. We pull in about 100,000 CDR records per day into a system that the clients can then argue about which one was a long distance call and which one wasn't. And things like that. And like most small ISPs with people that aren't really have a lot of experience, they take some while to learn that they might need to have a ticket system or a customer relationship management system. But they always know from the very beginning they need a billing system. That's a critical thing. And so that's the legacy code that we wrote and we've been maintaining for several years and they suddenly decided, it would be nice if we could like, you know, have the same list of customers everywhere. And so they actually after installing some things decided they would like sweet CRM and OTRS which is written in pearl. And so that was the goal, take a list of clients from sweet CRM which, you know, should get billed and things like this. Be able to open tickets against those customers with OTRS which is written sweet CRM is written in PHP as a recall. We learned later on that it only speaks my SQL. And ticket sync system OTRS written in pearl doesn't use catalyst. Some stuff that's been around for a long time. And then we have our system that keeps track of all the assets for the client. All the telephones which telephones belong to which customers, which customers have which buildings, which buildings are hooked up by which piece of fiber. And who's going to lose service when a particular piece of fiber gets cut or maintained or something like that. And so that was it. So we, this diagram you see I'll show you again and we needed to put it all together. I talked all about that. Fundamentally the key piece of information that we wanted to start with was list of customers. Turns out that that's not a well-defined list because different people and different building systems have different lists of customers and we don't actually to this date have a definitive list. At this point, but we're hoping soon maybe and the duplicates and other stuff. So sweet. So we do TRS and it likes its own schema and its own list of customers and we have sweet CRM. And so the simple was, well, let's throw some views at the problem. And, you know, that worked really well. And it was really great. And then we moved on to sweet CRM and I'm like, wait a minute, why is the configuration option to make it do my post address? And then I read the documentation again and realized it wasn't. So foreign data wrappers to the rescue with 9.5 and isn't that great? And it worked great. It really worked great. So we tried to install, insert a new customer. So it works great. You know, sales guy can insert a new customer in this, in the CRM and blah, blah, blah. And you can pass it on to the operations guy who opens a ticket against this customer and the data all updates and everything's great. And then I want to do some work where I insert those missing customers from this other system using a Rails framework and it blows up. So it turns out that foreign data wrappers can't really handle at least the MySQL one, not surprisingly, can't really handle returning. And Rails quite intelligently uses Postgres and says, hey, insert this data and now return the ID of the new inserted data and MySQL can't do that. So it fails. The other thing that we discovered was that Rails along with things like Django and Catalyst and DBI in Perl would like to introspect the database and find out the list of tables and the fields in them. Foreign data wrappers aren't in the PG catalog because they're not really there. So it didn't get played in the right place. So the right thing, everything's solved with a layer of indirection. So we created a view which was, as you can see on the right, which was select star from this other thing and lo and behold, we had a real table or sort of one. And that's the end. That's the diagram. The green stuff is the stuff that's outside. And then we had all these views and stuff that go back and forth and everything worked relatively well. So thank you. Hello. Hello. My name is Alexander Kratkov. I'd like to present a short announcement of PG Passman, which is extension for PostgreSQL partitioning. And the idea of this extension is to address some of problems of constraint exclusion mechanism. We call this extension PG Passman because this extension became possible because PostgreSQL have a pass list hook. And then we be able to generate custom pass list. And thanks to this, we was able to do it faster and better. So in particular, the restrictions we want to work around is at first very slow query planning with large number of partitions because constraints exclusion just iterate over each of partitions during planning of each query. And with large number of partitions, it's very slow. If your query is simple, it could be much slower than actually query execution. Another limitation of exclusion constraint mechanism is that query conditions are pushed as these into partitions without any simplification. This is not an error, but something which is nice to optimize. Also constraint exclusion supports only some of partitioning types. This could be improved. For instance, hash partition could be supported. And another thing is runtime partition selection. I will show you this on example. What PG Passman do better. He does fast partition selection, better filter condition processing, runtime partition selection and also supports hash partitioning. This is kind of stress test with 365 partitions. So it's year partition by date. And results are TPS, so higher is better. And single table is blue, PG Passman is yellow and partman is red. So planning of queries is much, much faster with PG Passman. It's almost as fast as single table. There is a stress test for partitioning where all accesses were randomized. And it's not surprising we not see the win of partitioning over single table here, but we see that there is no such huge regression. And another thing is simplification of filter conditions. You can see that in constraint exclusion doesn't simplify anything. Every condition is pushed down to particular partition even if it's always true or always false. PG Passman automatically simplifies such conditions. And as a result, it could better plans could be selected. For instance, sequential scan could be selected instead of index scan. Index scan could be selected instead of composition of bitmap scans, which is much faster. Another thing is that runtime partition selection. For instance, it's very useful for nested loop joins. There is a join without runtime partition selection. Has join was selected because nested loop became too slow with this number of partitions. And if we use runtime append, it becomes many times faster, more than 20 times. There is a custom node, thanks to custom nodes mechanism in POSGRAS. This runtime update node selects which table to scan depending on parameters and scan only that table. So it's available on GitHub. It's currently better, not for production use, but every testing welcome. I will publish news on my blog and that's all. We have a new record for best timing. That was with one second to spare. Let's see if someone can talk that. We do have fractions in the display. Hi, I'm Greg Stark. Usually at these conferences there's a presentation usually by a swede standing over there called, look at the elephant's trunk with the new features coming out. I thought after 20 years it would be worth having one call a quick look at the elephant's tail and see where we've come from. I prepared a presentation for a different conference called sorting through the ages where I benchmarked POSGRAS, compiling old versions of POSGRAS. Going back as far as 7.3, in fact almost 7.2. These benchmarks are running a handful of queries. They're all queries that benchmark the sorting code and the talk focused on that. Right now I'm not going to talk so much about the sorting code and the performance, but about how I did this because this turned out to be a much longer journey than I expected. One thing to make clear here is I compiled not just the major releases, but there's about 180 data points there for each of these lines. I did get checkouts of the development versions about every four to six weeks and sometimes a bit denser around the changes. It turns out even on the recent releases that was not always so simple because there were obviously get checkouts could include bugs or development compilation problems. But the further back you went there were the more and more problems I had. This is what the benchmarks actually look like. It's basically a CSV file that my script. I ran PSQL, parsed the results in Perl and generated the CSV. It's a few different queries. The timing from PSQL backslash timing, which itself is a bit of a trick because that was a feature that was added at a certain point. I wanted to use modern tools, not install old Debian packages and run that because I wanted to see how the performance had changed over time of our code. Not how the effectiveness of GCC had changed. So I chose Dash Capital 2, but I had to add some options from modern compilations like Dash Frap V that weren't in the old ones. I set some of the parameters to be the same and I used Ukrainian because I wanted to test the sorting code. I ended up with a script that looked something like this. It had a huge list of revision IDs and checked out each one, built it, did a get check out, built, get clean, did a configure. It was a bit of a trick here, no, on the next one, I guess. So I export C-Flag, set all those options. Some of those are to disable optimizations in GCC that don't work on our older source code. There was various gotchas I got. I actually had to have different branches in the script to handle older versions where the configure script wasn't a configure script, but actually didn't recognize Linux 64 that was something that it hadn't ever heard of. And then I ended up with a collection of patches. Here's, I think this is, no, this is from the script. So this is all the, so here it's applying some patches. It just tries to apply them all and sees what, ignores patch failures. And then here's a collection of random things I had to fix to get all these old versions to compile. Bison was a headache. It seems Bison changed its API, its interface quite a few times over the time. ECPG, we never kept in sync properly, and Bison started being much more picky about duplicate tokens and things. So it actually wrote a script to resync anyways. So I'm running out of time. That's why I'm skipping some things. So said, Bison, Perl, Perl, it turns out Perl 5.10, according to our old configure scripts, is too old because we need at least 5.3, read line changes interface, libc changed some behavior with signal handling. Before 7.0, I stopped compiling older versions when it no longer supported 64-bit machines. I actually got 60.5 to compile, but it didn't run. And I learned some interesting things. There were some interesting decisions, I should have saved more time for this, where I discovered things that failed, that didn't fail at the time because we fixed it before it actually ran into, before the GCC actually started complaining or causing problems. So there were some good decisions made that avoided problems in our past. Yeah. Oh, man. No, you don't win the award. No, not quite. I think this is really a whole talk. I think it clearly was a whole talk. No, it's about 10 minutes. Thank you. Well, my name is Serge Lille. I work for Salesforce. I'm pretty much of a post-president UB, only with like one year of track record, and before that, that was a developer in DB2 as you W. So title is Backend Size Matters. Some stats about the system I'm working on. We are having about some in excess of 30,000 PLPG SQL functions, over 1,000 tables, over 2,000 PLPG SQL triggers, and then there's types, indices, views, you name it. And the system should be running with around 200 connections, and it's under full load. And all this is a 3D architecture where the client sessions do not have an affinity to any specific back end sessions. So basically, any transactions can go to any back end. Okay, so we're just spraying it around. So when you have a system like this, really, some interesting things happen. And the result of these interesting things are a lot of fud. So the first thing we heard was the database is leaking memory like a sieve. And because it's leaking memory like a sieve, the application server has to recycle connections every 100 transactions. But that really shouldn't be a problem because recycling connections don't really cost anything, right? So that's what I kind of like heard when I joined. I said, well, I don't really think that's true. So let's go to the facts. The facts are that with the frequent recycling of the back end that was enforced from the application, we found that about a third of the time we spent was just compiling queries and compiling PLPG, passing PLPG SQL functions and so on and so forth. So pretty nasty overhead, not something we wanted to do. However, if we would switch off back end recycling, then we would end up with 140 megabyte catalog cache, 550 megabytes PLPG SQL and embedded SQL cache. So basically the SQL within the PLPG SQL. And that is, you're assuming you basically reach a hot set of about 2,000 of these functions. So not all 30,000 in there. And then 50 megabytes of others. So you multiply that with 200 back ends and you end up with about 150 gigabytes space requirements just for your back ends. I'd rather have that in my buffer pool. So that clearly is not something that we think we will eventually use. So we have to work on that. So I did some measurements. This is actually already a measurement we had a little bit of trying to do some controls on this. So you see at the bottom, actually, these are things like transactions and executor allocated tasks. So it's all reasonably kind of like goes up in town and doesn't really get more than 20 megabytes each. You see the purple line that goes all the way to the top in the end is the, that's the catalog cache between the rail cache and the regular catalog cache reaches about 160. And then you see other things like the orange and the whatever rusty red together. This is a PLPG SQL in the other one. So it's up. So next thing we did was, okay, let's just tackle this. So good news. If you take a look at this thing, it kind of like comes like this and it goes algorithmic, right? So it's, I mean, it goes towards some isotopic max value. So it's not actually leaking. It's just bloating. Lots of bloating. So the first thing I did when I joined was I limited the PLPG SQL function cache. So there is an existing cache. It just doesn't have an LRU with it. It just keeps growing. So I put a max on that thing with an LRU and eviction, fixed some other issues that were inherent. And then I was able to reduce the cache by half and still get very good hit ratios. So I put it like 2,000 functions that allow in and go somewhere. So I limited the catalog cache to about 120 megabytes. What was originally going to get down to about 25, but it's about with 10,000 entries and it cut it down by three. And then we did some changes to the way how custom and generic plans work. We actually compare not just custom plan cost. We also checking out the shape of the plan using a plan ID. And then if the plan ID doesn't change between custom plans, we switch over to generic plans. And then I added a plan source cache for dynamic SQL that's not prepared by comparing statement titles. So with all that, I was able to do a memory estimator and I cannot control with gux the size of this thing. And then basically predict our good goals. So with this default value here with 200 connections, I would go to about 90 gigabytes, which is still too much. So food for thought in the last 20 seconds. So due to the spraying, all of our backends pretty much all the same stuff because there's no affinity. So any improvement I can do on the back end will only give me incremental savings. But if I were to have shared caches, I could drop the footprint by 100x. So and that's it and I have two seconds. Thank you.
 Hey, everybody.  My name is Moshe Jacobson from NeedWorks, and I'm excited to announce that my extension,  Sci-In Audit with a really cool logo, is ready for production now.  It's been refined over the last few years, and it's an extension for in-database DML  logging.  So any kind of inserts, updates, or deletes that you do in your database, it'll be logged  into your database with searchability through a view.  So with Sci-In Audit, you can log all DML column by column, so you can choose which  columns you want to log in your database.  You can search your logs using a simple view.  You just do a query, and it pulls out all your logs from as far back as you want.  You can configure what to log using a config table.  So it's just a matter of update your config table, set logging as on or off for whatever  columns you want to turn on or off.  And the cool part about it is if you have an application where your users are logging  into your application and doing stuff, and that stuff gets done in the database, your  Sci-In Audit will actually allow you to link the operation to the application user, so  you can tell exactly who did every operation that happens in your database, and it's great  for accountability.  You can also put a label on each operation so that you can, when you're going back through  your logs, you can tell where this operation happened from, what exactly was going on.  It's really, really useful for forensics.  You can also back up and restore your logs with the scripts.  It dumps out to basically compress CSV files, very, very space efficient.  You can, another cron job will rotate and drop your logs after they get a certain age.  You can keep your logs on a separate table space if you want a large, slow volume to  hold your logs, whereas a fast volume will do the rest of your database.  And you can undo your transactions, actually, which is a really cool feature of it.  It's not really undoing, of course.  It's just playing everything back in reverse, but it's been a lifesaver for us at my company.  Many times we've done a SQL command that did something we didn't want, and then all of  a sudden it's there in the logs.  You can just undo it.  And it's made to be super easy.  You just install the extension.  You run a couple commands, and you're already logging everything in your database.  So it's all written in SQL and PLPGSQL.  It's all trigger-based.  So it's not using any of the logical replication of infrastructure or anything like that.  It's compatible with Postgres 9.3 and newer, and it's been production tested for a while  now.  The purpose of this lightning talk is to gauge interest in this as I'm trying to get this  onto Amazon RDS.  It's, I think, ready for prime time.  And I was hoping to get a show of hands of people who might be interested in using this  extension on their own application.  Anybody interested in something like this?  What about with, is there any modifications?  If you have any ideas of modifications that you'd like to see, raise your hand.  Okay.  So we got a couple people interested in this.  But yeah, if you're interested in trying it out, I love your feedback.  We literally don't know how anybody else does without this at my company.  We always say I literally don't know how other people do it.  So very, very useful tool for us.  I hope it can be a useful tool for somebody else as well.  So if you want to download it, there's the link right there.  And thank you to NeedWorks for sponsoring the development.  I'm going to print this.  Okay, let's talk about elephants and raft.  My name is Konstantin Pan.  I'm from PostgreSQL Professional.  And during our implementation of multi-master and other cluster experiments, we needed to  have a replicated key value storage for distributed deadlock detection and node failure detection.  And we also already had implemented raft consensus protocol in C for our distributed transaction  demo.  So the idea is to use raft for replicated key value storage.  This was implemented as an extension.  It's available over this link.  To use it, you just add shared-prolet libraries and configure the node number and other peers  on each node and call create extension raftable.  You can use set, get, and list functions in SQL and in C.  And when it's running, the processes look like this.  On each node, you have multiple backends and one raftable worker, which implements raft  protocol over UDP.  And it keeps the state machine in shared memory.  So when a backend needs to perform a get operation, it just looks into the shared memory.  And when it needs to put something new, it sends a query over TCP to the raftable leader  and then waits until it gets replicated at this node.  We need to do some more testing and implement node management in raft.  That's all.  Welcome, all.  That's gone really fuzzy.  Oh, that's better.  Hello.  My name's Dave.  I've been working on PG admin for nearly 20 years now, believe it or not.  Thank you.  So who's used PG admin 3?  Quick show of hands, very fast.  I've got time to mess around.  Who's used PG admin 2?  A few people.  What about the original PG admin, well, PG admin nothing.  All right, for the last six months or so, we've been working on PG admin 4.  Woohoo!  PG admin 4 is a complete rewrite of PG admin 3.  Well, it's just a rewrite in general.  And if Greg's computer will actually change, what am I supposed to press?  Base? Arrow?  Yeah, I pressed that. Nothing happened.  Really?  Really.  Technology.  Take down! Take down!  Oh, no.  Is it a continuous mode?  I don't know.  Can we stop the clock?  No, no, you get the clock.  I'm feeling hard done by here.  All right, so PG admin 4, if we can ever show you any of it, has been designed as a web application.  Well, then it cut off the bottom was the problem.  Well, no, no, no, it's close enough.  It is.  It still won't show the next page.  There you go. Display that page.  So we built it as a web application, backends written in Python.  You missed all my good slides.  That's no problem.  I even put a cat slide in there because somebody told me you needed a cat in a presentation like this.  Oh, we're working on it.  It says loading.  Really, really slow.  I'm going home.  All right, so it's been designed as a web application written in Python on the back end, mostly JavaScript  and jQuery on the front end.  Pretty big, the photos.  We've designed it so that you can run it on a web server in the cloud managing all your servers.  Or we've got a runtime desktop application that will allow it to run basically standalone,  much as you'd run with PG admin 3 right now.  We have all the things that you used to see before.  Oh, we're actually, did you ignore the picture of the cat?  That's the problem.  So you're not supposed to have a cat.  Those are the ones.  All right, so we've got a few dashboards in there.  I mean, you can tell that looks very different from PG admin 3.  We actually stuck with the tree view, which is one of the few things that does look kind of similar.  We looked at various different ways of navigating around the database structure.  And frankly, the tree view is about the only way that really seems to work and not feel contrived and just,  so we added some dashboards to give you something interesting to look at, some little graphs and things to show you what your database server's doing.  Obviously, you can look at object properties.  It's all pretty fine compared to how it used to be.  We're still working on final touches here.  So there's going to be tweaks and improvements over the next few weeks.  You can edit objects.  Now, all of this is responsive.  So if you go on a smaller screen, it will rewrap and do all the things you'd expect when you try and run it on your iPhone.  If anyone does try and run it on your iPhone, then don't come running to me if you have problems.  It's just ridiculous trying to design a database like that.  We have a query tool.  PG admin 3 has a separate query tool and data editor.  You know, you'd right click on a table and get up a separate data editor.  We've merged those into one in this tool, so you can run a query in it.  You can edit the data down below.  We're going to make it smarter and smarter.  Right now, the first version, it depends how you launch it as to whether it will let you edit the data.  But we're putting smarts in there, so they'll understand and try and figure out whether the query you've hand typed is something that we can sensibly try and update.  And we're doing for time.  Tell me I'll get an extra minute, please.  You already got the extra minute.  Query plan visualization.  There's more improvements coming on that.  Procedural language debugger, maintenance tools we're doing back up and so on.  Outfit release is coming soon.  PG admin hack is at postgresql.org if you want to take a look.  Or you can check out the code from the git.postgresql.org right now and have a play with it.  But look out for it.  Be with you probably around the time of 9.6.  Finally, who likes Europe?  Hey.  So as you may know, Magnus and I and various other people that are here at the moment,  organized this little tiny conference every year.  We're doing our eighth one in Tallinn, Estonia, November 1st to 4th.  It's really warm at that time of year.  You'll love it.  Take a look at the website.  We'd love to have you come join us.  Thank you.  Thank you.  Thank you.  Let's see if the next set of slides work better.  Okay.  Okay.  You must have taken a really high-res photo.  Did they tweet it now?  I'll make sure it gets to the program.  Oh, no.  The direction of the copy is the internet.  Yeah, thanks.  So, hello everyone.  I want to talk here about several patches and extensions that we did in Postgres Pro last year.  And that dealing with distributing transactions and helping to create distributed database out of the Postgres.  So basically about two things.  One thing we call distributed transaction manager.  And second one, how we can achieve multi-master replication with the first thing.  So, distributed transaction manager.  It's a communication protocol,  along with some algorithms that allow us to achieve distributed,  can see distributed isolation and distributed transaction between different nodes.  So, we can set up several nodes and run transactions that spans two nodes, for example.  So, talking about Postgres, we created small patch that exposes several internal routines about visibility and log access  and implemented an extension that allows us to create distributed transactions.  And we done several integrations with Postgres FDW and PGShard.  And also it can be used in application-based sharding.  So, you can actually run start transaction on one node, on different node,  call routines on that node, and that will promote transaction as a global one.  So, let's see some example based on FDW.  So, suppose we have simple bank test.  So, there is a table with user ID and amount of money.  And we have two different installations.  Shard 1 and Shard 2 with local tables that create, that have user accounts.  And we have third nodes that create like FDW connection to both of that nodes.  And runs following transaction on that table.  That's on the third node.  Stable T is empty on that node, but it can look to the two shards.  And we start transferring money.  And actually it can be done, users can live on different nodes.  And, parallelly, execute some of all accounts.  Since there is a transaction, we expect that some will be constant all the time.  But actually no any transactional information pushed down through a Postgres FDW by default.  So, it's extended bench and it's one of the tools that we are using in our testing.  It actually implements following transaction concurrently.  And total is that results that summarizing transaction get.  So, it's fluctuating.  It's actually, it's repeatable read.  So, it sees intermediate states inside the transaction.  And we can use our extension, Pgtsdtm times stand, distribute transaction manager, create extension.  It will run the ground worker that can talk with such ground worker on different node.  And after that, there will be proper transaction isolation.  And out of that, we can build a multi-master.  So, it's built on top of Pg logical.  It wraps update and selects as a global transactions.  We do a concurrent application of transactions that replicated.  So, if you have several big transactions, they will be applied concurrently.  And also that Constantine talked about, we use raft-based storage for a log detection and dealing with failures.  So, our implementation right now, it's about a half of speed of standalone Postgres for writes.  And the same speed for reads.  It can recover from node failures.  So, node can, for example, go and reboot.  And after it will be available again, it will replicate changes.  And cluster will be available for writes when majority is online.  Also, it can deal with network partition.  And we are working on that right now.  And can work as an extension if that XTM API will go into core or we will merge some,  or we'll just merge one implementation of DTM into core.  So, thank you.  I think that was the best timing so far with nine seconds to go.  Okay.  So, the cat is now up on Twitter.  Okay.  Hi everybody.  My name is Thomas Monroe.  I work for Enterprise DB.  I'm like everyone else who's spoken, I'm going to be talking about some software that doesn't exist.  And specifically about a SQL standard feature that I think is really interesting  and that we don't have in fact no one has.  First of all, I'm going to travel back in time with my time machine to sometime in the mid-90s  when I was out of university.  And my first job, I came across a client that had a non-relational data store.  And they had lots of objects except more than like millions and millions of things.  And these things were all connected to each other with these pointers and there were these back pointers.  So, you could navigate from an asset to a site and from a site back to all the assets.  So, that's whatever you know, you could navigate around this graph.  But the forward pointers and back pointers were kind of linked to each other.  That sounds like it should work just fine, right?  I mean, if you're going to update things, you've got to just lock the right objects  and move these pointers around.  And everything should be perfectly consistent at all times.  So, let's get some concurrency happening and hang on a second.  What's happened here?  Now, we've got some pointers to some things that have gone  and some forward pointers and back pointers that are mixed up.  Yeah, something's gone wrong here.  So, this is real.  This is an experience I had.  And so, a group of us were asked to come in and figure out how we were going to find out what's gone wrong with this data.  And so, the idea was to build a report that would figure out which two-way pointers were not in sync.  And after an initial phase of doing these reports, just write some software that would figure out which pointer was missing.  And it must be pretty obvious how to fix it, right?  So, I fondly think back to that time, it was the eventual constraints team,  because that's kind of the thing that was missing from that system and the thing that in the universe that we live in,  we have obviously traveling back in time now to the 1970s, all this great relational data bank stuff,  which Ted Codd and a bunch of others gave us, so that we don't have problems like that, right?  We have these things called constraints.  There's unique constraints, there's foreign key constraints,  that's specifically the thing that would deal with that problem.  And then there's these check constraints that can, you know, impose some kind of constraint on the rows in your tables.  But wait, there's more.  The SQL standard says that you can do stuff like this.  You can say there should be no more than 15 students in any given class,  and there's a check constraint attached to a table that says, you know, this count star shouldn't give more than 15.  Now, we can't actually do that.  But remind me.  We can't do that because that contains a sub-query and that's what Postgres will tell you.  But in fact, no existing RDBMS does that.  Now, there are a couple of RDBMS that will accept that, but they don't actually work.  They just run the check when you insert something, but they don't check all the reference data,  and that's of course the hard bit.  Second, the other thing which is really difficult about it is dealing with concurrency.  And you might think that serializable could help you with that,  but generally speaking, people are afraid of serializable.  And in most databases, it doesn't even work properly, right?  Okay, so here's the crazy idea.  We could think really hard and make an analyzer that can efficiently figure out which constraints need to be checked.  Now, that's difficult and I haven't got time to explain why, but it could be done.  And note that the same kind of analysis is going to be needed for materialized views.  We get to doing incremental materialized view maintenance.  Then if you required serializable isolation for any DML which is touching something that's referenced by one of these checks,  you could have a scenario like this.  You come along and try and access this enrollment table and insert another student,  and it just tells you, go away, you're not running a serializable.  I can't actually run the checks required for this update.  So you run the same thing as serializable, and now it can reject it.  You're trying to put too many students in there, and the check said, you know, you can only have 15 that's going to run that count.  That's something we can't do today, but if we did that, it would be the first database to have that.  And, you know, it's something that's been in the SQL standard since 1992,  and it's a kind of consistency that people actually ask for quite often, I find.  People who are new to using databases expect it to better do that sort of thing.  Yeah, and actually, most people can express this kind of stuff quite easily.  You know, it's just a sequel, right?  So we might actually be the only RDBMS that could actually implement this and get it working,  because we have our amazing SSI system.  That's me. I'm done.  Thank you.  Okay.  Thank you. My name is Satoshi Nagasu. I came from Japan.  As some of you may remember, I gave a pitch at the last PGCon, which was the first plan of PGD Asia.  Do you remember? Then one year passed.  So it's time to share some updates about the conference with you.  In the last march, this is my last pitch.  In the last march, we made the first Pan-Asian process conference in Singapore.  The PGD Asia 2016 was held on March 17th and 19th, as a joint effort with FOSS Asia 2016.  Actually, PGD Asia has 90 speakers, 90 regular sessions and writing talks, as usual,  and also 100 attendees.  At that conference, Bruce gave the keynote, Dilip gave Postgres 9.6 performance talk,  Savada-san introduced the vacuum freezing map, and also Umar talked about Big Data and Postgres.  And also, E-Dia came to Singapore, and other Japanese new guys introduced migration technology from Morocco.  And of course, many other great talks there.  I would like to show some pictures about the conference.  This is Happy Anniversary, 20 years anniversary study at Singapore.  And reception, Bruce keynote, and talks, attendees, talks, meetups, and many people.  This is group shot from day one.  As I mentioned before, the PGD Asia was a joint effort with FOSS Asia.  So this is a picture from day two, the PGD Asia day two, and also FOSS Asia day two.  So there are many talks out there, and many people enjoyed both the Postgres conference and FOSS Asia at the same time.  So, okay, this is a picture from post Asia day two.  So, okay, it's time to think of the next event.  The next plan has not fixed yet, but PGD Asia 2017 will be held in the same format, in the same season and same region.  So please do not miss the next one if you are interested in coming to Asia.  So if you are interested in coming to Singapore, please let me know and let's keep in touch.  Okay, it's my time is over, and next is Mikhail Stam.  How long do I have?  One minute.  So how long do I have? One minute?  Two minutes?  No, I'm joking.  Like, I don't have much to say.  Just you already know where you are going at the beginning of September.  You will be in Estonia.  I would recommend the beginning of November.  Sorry, sorry.  After that, you will be in California.  And here is a new conference that will happen at the end of November, which is in Asia.  PGD Asia 2016, the first one of its name.  It's going to be here.  Actually, it's going to be close to here.  This is going to be in Tokyo, Akirabara.  And Akirabara is known for that also.  So there will be a lot of people there, and it is aimed at becoming the biggest conference in Asia for post-West QL.  So people would like to do some developer meeting.  There would be some business community track.  And there's going also to be a call for paper that was announced should be in July.  There is a dedicated team to that.  And there is also a website which is up.  So welcome to Japan.  That's all.  Thank you.  Hi, my name is Konstantin Konizhnik.  I'm working for the professional.  But now I want to present the result of yet another Russian team from the Institute of System Programming of Russian Academy of Science.  They have a huge experience in dynamic code generation and want to apply this experience to improve speed of progress.  They are using LLVM grid, which is the de facto standard now for dynamic code generation.  It is used in a large number of products.  If you are developing your own programming language or speeding up existing ones, then LLVM is for you.  So try to marry LLVM with Postgres and see results.  This is Postgres query execution plan.  And at the right you see result of LLVM grid code produced corresponding to this query execution plan.  For example, in this query you can see that 50% of time is spent in the exact quality which evaluates expression and using LLVM allows to reduce it to 6% and almost speed up this query about two times.  Certainly it is not first attempt to use dynamic code generation for Postgres.  The same thing was done in VTSRDB, but it is commercial product and not open source.  And these guys are going to publish the result of their work in open source.  There is one major difference in approach used in VTSRDB and Institute of System Programming.  Postgres contains about 2,000 operators.  The first most preferred approach is to just manually rewrite them using LLVM.  But it requires a large huge amount of work and academic people prefer to solve more generic tasks instead of doing such stupid work.  So then develop converter which allows to automatically convert this method to LLVM code.  The first step of this conversion is standard C-Lang compiler which produce LLVM grid code.  Then converter generates C++ code which generates LLVM code.  And finally, I've got complete tree which evaluates query.  This is once again result of generation LLVM bit code for Postgres query execution plan.  Here is the first query of the PCH.  You can see that aggregation takes about 75% and the quite complex expressions are used in aggregations.  And using GIT allows to significantly speed up execution of this query.  They get right now three times improvement at this query but it's just first attempt and then expect much more speed up.  But using GIT is not the only optimizations I've implemented.  Another useful thing is replacing pull model with push style.  It allows, according to their results, it allows to reach about 25% of speed up of query execution.  Then have manually converted most of Postgres SQL nodes.  Not all nodes are currently converted.  For example, bitmap.hipscan not currently converted.  Most of speed up is achieved at first query but I expect that, for example, Q6 also will be significantly improved once they have implemented bitmap.hipscan.  So conclusions.  I have done a lot of work in quite small amount of time because this work was started just in this year.  No, we all learn what the lightning talk is, right?  I'm sorry, you're out of time.  Thank you.  Hi, my name is Michael Richardson.  I live in Ottawa and I'm a Postgres user.  So I'll ask you to start by saying thank you to all of you because I don't think I sometimes I don't even know what version of Postgres I'm using.  But two days a week I spend working for a small ISP in Montreal that is in the business to business ISP space.  And they mostly do VoIP.  We pull in about 100,000 CDR records per day into a system that the clients can then argue about which one was a long distance call and which one wasn't.  And things like that.  And like most small ISPs with people that aren't really have a lot of experience, they take some while to learn that they might need to have a ticket system or a customer relationship management system.  But they always know from the very beginning they need a billing system.  That's a critical thing.  And so that's the legacy code that we wrote and we've been maintaining for several years and they suddenly decided,  it would be nice if we could like, you know, have the same list of customers everywhere. And so they actually after installing some things decided they would like sweet CRM and OTRS which is written in pearl.  And so that was the goal, take a list of clients from sweet CRM which, you know, should get billed and things like this.  Be able to open tickets against those customers with OTRS which is written sweet CRM is written in PHP as a recall.  We learned later on that it only speaks my SQL.  And ticket sync system OTRS written in pearl doesn't use catalyst.  Some stuff that's been around for a long time.  And then we have our system that keeps track of all the assets for the client.  All the telephones which telephones belong to which customers, which customers have which buildings, which buildings are hooked up by which piece of fiber.  And who's going to lose service when a particular piece of fiber gets cut or maintained or something like that.  And so that was it.  So we, this diagram you see I'll show you again and we needed to put it all together.  I talked all about that.  Fundamentally the key piece of information that we wanted to start with was list of customers.  Turns out that that's not a well-defined list because different people and different building systems have different lists of customers and we don't actually to this date have a definitive list.  At this point, but we're hoping soon maybe and the duplicates and other stuff.  So sweet.  So we do TRS and it likes its own schema and its own list of customers and we have sweet CRM.  And so the simple was, well, let's throw some views at the problem.  And, you know, that worked really well.  And it was really great.  And then we moved on to sweet CRM and I'm like, wait a minute, why is the configuration option to make it do my post address?  And then I read the documentation again and realized it wasn't.  So foreign data wrappers to the rescue with 9.5 and isn't that great?  And it worked great.  It really worked great.  So we tried to install, insert a new customer.  So it works great.  You know, sales guy can insert a new customer in this, in the CRM and blah, blah, blah.  And you can pass it on to the operations guy who opens a ticket against this customer and the data all updates and everything's great.  And then I want to do some work where I insert those missing customers from this other system using a Rails framework and it blows up.  So it turns out that foreign data wrappers can't really handle at least the MySQL one, not surprisingly, can't really handle returning.  And Rails quite intelligently uses Postgres and says, hey, insert this data and now return the ID of the new inserted data and MySQL can't do that.  So it fails.  The other thing that we discovered was that Rails along with things like Django and Catalyst and DBI in Perl would like to introspect the database and find out the list of tables and the fields in them.  Foreign data wrappers aren't in the PG catalog because they're not really there.  So it didn't get played in the right place.  So the right thing, everything's solved with a layer of indirection.  So we created a view which was, as you can see on the right, which was select star from this other thing and lo and behold, we had a real table or sort of one.  And that's the end.  That's the diagram.  The green stuff is the stuff that's outside.  And then we had all these views and stuff that go back and forth and everything worked relatively well.  So thank you.  Hello.  Hello. My name is Alexander Kratkov. I'd like to present a short announcement of PG Passman, which is extension for PostgreSQL partitioning.  And the idea of this extension is to address some of problems of constraint exclusion mechanism.  We call this extension PG Passman because this extension became possible because PostgreSQL have a pass list hook.  And then we be able to generate custom pass list.  And thanks to this, we was able to do it faster and better.  So in particular, the restrictions we want to work around is at first very slow query planning with large number of partitions because constraints exclusion just iterate over each of partitions during planning of each query.  And with large number of partitions, it's very slow.  If your query is simple, it could be much slower than actually query execution.  Another limitation of exclusion constraint mechanism is that query conditions are pushed as these into partitions without any simplification.  This is not an error, but something which is nice to optimize.  Also constraint exclusion supports only some of partitioning types.  This could be improved. For instance, hash partition could be supported.  And another thing is runtime partition selection.  I will show you this on example.  What PG Passman do better.  He does fast partition selection, better filter condition processing, runtime partition selection and also supports hash partitioning.  This is kind of stress test with 365 partitions.  So it's year partition by date.  And results are TPS, so higher is better.  And single table is blue, PG Passman is yellow and partman is red.  So planning of queries is much, much faster with PG Passman.  It's almost as fast as single table.  There is a stress test for partitioning where all accesses were randomized.  And it's not surprising we not see the win of partitioning over single table here,  but we see that there is no such huge regression.  And another thing is simplification of filter conditions.  You can see that in constraint exclusion doesn't simplify anything.  Every condition is pushed down to particular partition even if it's always true or always false.  PG Passman automatically simplifies such conditions.  And as a result, it could better plans could be selected.  For instance, sequential scan could be selected instead of index scan.  Index scan could be selected instead of composition of bitmap scans, which is much faster.  Another thing is that runtime partition selection.  For instance, it's very useful for nested loop joins.  There is a join without runtime partition selection.  Has join was selected because nested loop became too slow with this number of partitions.  And if we use runtime append, it becomes many times faster, more than 20 times.  There is a custom node, thanks to custom nodes mechanism in POSGRAS.  This runtime update node selects which table to scan depending on parameters and scan only that table.  So it's available on GitHub.  It's currently better, not for production use, but every testing welcome.  I will publish news on my blog and that's all.  We have a new record for best timing. That was with one second to spare.  Let's see if someone can talk that.  We do have fractions in the display.  Hi, I'm Greg Stark.  Usually at these conferences there's a presentation usually by a swede standing over there called,  look at the elephant's trunk with the new features coming out.  I thought after 20 years it would be worth having one call a quick look at the elephant's tail and see where we've come from.  I prepared a presentation for a different conference called sorting through the ages where I benchmarked POSGRAS,  compiling old versions of POSGRAS.  Going back as far as 7.3, in fact almost 7.2.  These benchmarks are running a handful of queries.  They're all queries that benchmark the sorting code and the talk focused on that.  Right now I'm not going to talk so much about the sorting code and the performance,  but about how I did this because this turned out to be a much longer journey than I expected.  One thing to make clear here is I compiled not just the major releases, but there's about 180 data points there for each of these lines.  I did get checkouts of the development versions about every four to six weeks and sometimes a bit denser around the changes.  It turns out even on the recent releases that was not always so simple because there were obviously get checkouts could include bugs or development compilation problems.  But the further back you went there were the more and more problems I had.  This is what the benchmarks actually look like.  It's basically a CSV file that my script.  I ran PSQL, parsed the results in Perl and generated the CSV.  It's a few different queries.  The timing from PSQL backslash timing, which itself is a bit of a trick because that was a feature that was added at a certain point.  I wanted to use modern tools, not install old Debian packages and run that because I wanted to see how the performance had changed over time of our code.  Not how the effectiveness of GCC had changed.  So I chose Dash Capital 2, but I had to add some options from modern compilations like Dash Frap V that weren't in the old ones.  I set some of the parameters to be the same and I used Ukrainian because I wanted to test the sorting code.  I ended up with a script that looked something like this. It had a huge list of revision IDs and checked out each one, built it, did a get check out, built, get clean, did a configure.  It was a bit of a trick here, no, on the next one, I guess.  So I export C-Flag, set all those options.  Some of those are to disable optimizations in GCC that don't work on our older source code.  There was various gotchas I got.  I actually had to have different branches in the script to handle older versions where the configure script wasn't a configure script,  but actually didn't recognize Linux 64 that was something that it hadn't ever heard of.  And then I ended up with a collection of patches.  Here's, I think this is, no, this is from the script.  So this is all the, so here it's applying some patches.  It just tries to apply them all and sees what, ignores patch failures.  And then here's a collection of random things I had to fix to get all these old versions to compile.  Bison was a headache.  It seems Bison changed its API, its interface quite a few times over the time.  ECPG, we never kept in sync properly, and Bison started being much more picky about duplicate tokens and things.  So it actually wrote a script to resync anyways.  So I'm running out of time.  That's why I'm skipping some things.  So said, Bison, Perl, Perl, it turns out Perl 5.10, according to our old configure scripts,  is too old because we need at least 5.3, read line changes interface,  libc changed some behavior with signal handling.  Before 7.0, I stopped compiling older versions when it no longer supported 64-bit machines.  I actually got 60.5 to compile, but it didn't run.  And I learned some interesting things.  There were some interesting decisions, I should have saved more time for this,  where I discovered things that failed, that didn't fail at the time because we fixed it before it actually ran into,  before the GCC actually started complaining or causing problems.  So there were some good decisions made that avoided problems in our past.  Yeah.  Oh, man.  No, you don't win the award.  No, not quite.  I think this is really a whole talk.  I think it clearly was a whole talk.  No, it's about 10 minutes.  Thank you.  Well, my name is Serge Lille. I work for Salesforce. I'm pretty much of a post-president UB,  only with like one year of track record, and before that, that was a developer in DB2 as you W.  So title is Backend Size Matters.  Some stats about the system I'm working on.  We are having about some in excess of 30,000 PLPG SQL functions, over 1,000 tables,  over 2,000 PLPG SQL triggers, and then there's types, indices, views, you name it.  And the system should be running with around 200 connections, and it's under full load.  And all this is a 3D architecture where the client sessions do not have an affinity to any specific back end sessions.  So basically, any transactions can go to any back end.  Okay, so we're just spraying it around.  So when you have a system like this, really, some interesting things happen.  And the result of these interesting things are a lot of fud.  So the first thing we heard was the database is leaking memory like a sieve.  And because it's leaking memory like a sieve, the application server has to recycle connections every 100 transactions.  But that really shouldn't be a problem because recycling connections don't really cost anything, right?  So that's what I kind of like heard when I joined.  I said, well, I don't really think that's true.  So let's go to the facts.  The facts are that with the frequent recycling of the back end that was enforced from the application,  we found that about a third of the time we spent was just compiling queries and compiling PLPG,  passing PLPG SQL functions and so on and so forth.  So pretty nasty overhead, not something we wanted to do.  However, if we would switch off back end recycling, then we would end up with 140 megabyte catalog cache,  550 megabytes PLPG SQL and embedded SQL cache.  So basically the SQL within the PLPG SQL.  And that is, you're assuming you basically reach a hot set of about 2,000 of these functions.  So not all 30,000 in there.  And then 50 megabytes of others.  So you multiply that with 200 back ends and you end up with about 150 gigabytes space requirements just for your back ends.  I'd rather have that in my buffer pool.  So that clearly is not something that we think we will eventually use.  So we have to work on that.  So I did some measurements.  This is actually already a measurement we had a little bit of trying to do some controls on this.  So you see at the bottom, actually, these are things like transactions and executor allocated tasks.  So it's all reasonably kind of like goes up in town and doesn't really get more than 20 megabytes each.  You see the purple line that goes all the way to the top in the end is the, that's the catalog cache between the rail cache  and the regular catalog cache reaches about 160.  And then you see other things like the orange and the whatever rusty red together.  This is a PLPG SQL in the other one.  So it's up.  So next thing we did was, okay, let's just tackle this.  So good news.  If you take a look at this thing, it kind of like comes like this and it goes algorithmic, right?  So it's, I mean, it goes towards some isotopic max value.  So it's not actually leaking.  It's just bloating.  Lots of bloating.  So the first thing I did when I joined was I limited the PLPG SQL function cache.  So there is an existing cache.  It just doesn't have an LRU with it.  It just keeps growing.  So I put a max on that thing with an LRU and eviction, fixed some other issues that were inherent.  And then I was able to reduce the cache by half and still get very good hit ratios.  So I put it like 2,000 functions that allow in and go somewhere.  So I limited the catalog cache to about 120 megabytes.  What was originally going to get down to about 25, but it's about with 10,000 entries and it cut it down by three.  And then we did some changes to the way how custom and generic plans work.  We actually compare not just custom plan cost.  We also checking out the shape of the plan using a plan ID.  And then if the plan ID doesn't change between custom plans, we switch over to generic plans.  And then I added a plan source cache for dynamic SQL that's not prepared by comparing statement titles.  So with all that, I was able to do a memory estimator and I cannot control with gux the size of this thing.  And then basically predict our good goals.  So with this default value here with 200 connections, I would go to about 90 gigabytes, which is still too much.  So food for thought in the last 20 seconds.  So due to the spraying, all of our backends pretty much all the same stuff because there's no affinity.  So any improvement I can do on the back end will only give me incremental savings.  But if I were to have shared caches, I could drop the footprint by 100x.  So and that's it and I have two seconds.  Thank you.
Lightning_Talks_p5Esr6DrKpw_0_6191.png Lightning_Talks_p5Esr6DrKpw_6192_6275.png Lightning_Talks_p5Esr6DrKpw_6276_6347.png Lightning_Talks_p5Esr6DrKpw_6348_6503.png Lightning_Talks_p5Esr6DrKpw_6504_6599.png Lightning_Talks_p5Esr6DrKpw_6600_7991.png Lightning_Talks_p5Esr6DrKpw_7992_10787.png Lightning_Talks_p5Esr6DrKpw_10788_13163.png Lightning_Talks_p5Esr6DrKpw_13164_13547.png Lightning_Talks_p5Esr6DrKpw_13548_14027.png Lightning_Talks_p5Esr6DrKpw_14028_14471.png Lightning_Talks_p5Esr6DrKpw_14472_14543.png Lightning_Talks_p5Esr6DrKpw_14544_14591.png Lightning_Talks_p5Esr6DrKpw_14592_14831.png Lightning_Talks_p5Esr6DrKpw_14832_15011.png Lightning_Talks_p5Esr6DrKpw_15012_15095.png Lightning_Talks_p5Esr6DrKpw_15096_15179.png Lightning_Talks_p5Esr6DrKpw_15180_15275.png Lightning_Talks_p5Esr6DrKpw_15276_15839.png Lightning_Talks_p5Esr6DrKpw_15840_16007.png Lightning_Talks_p5Esr6DrKpw_16008_16739.png Lightning_Talks_p5Esr6DrKpw_16740_18767.png Lightning_Talks_p5Esr6DrKpw_18768_19259.png Lightning_Talks_p5Esr6DrKpw_19260_21479.png Lightning_Talks_p5Esr6DrKpw_21480_21683.png Lightning_Talks_p5Esr6DrKpw_21684_21755.png Lightning_Talks_p5Esr6DrKpw_21756_30635.png Lightning_Talks_p5Esr6DrKpw_30636_30695.png Lightning_Talks_p5Esr6DrKpw_30696_31799.png Lightning_Talks_p5Esr6DrKpw_31800_31847.png Lightning_Talks_p5Esr6DrKpw_31848_37019.png Lightning_Talks_p5Esr6DrKpw_37020_37919.png Lightning_Talks_p5Esr6DrKpw_37920_40151.png Lightning_Talks_p5Esr6DrKpw_40152_40271.png Lightning_Talks_p5Esr6DrKpw_40272_41099.png Lightning_Talks_p5Esr6DrKpw_41100_42143.png Lightning_Talks_p5Esr6DrKpw_42144_42611.png Lightning_Talks_p5Esr6DrKpw_42612_43499.png Lightning_Talks_p5Esr6DrKpw_43500_43787.png Lightning_Talks_p5Esr6DrKpw_43788_43931.png Lightning_Talks_p5Esr6DrKpw_43932_44759.png Lightning_Talks_p5Esr6DrKpw_44760_45731.png Lightning_Talks_p5Esr6DrKpw_45732_46451.png Lightning_Talks_p5Esr6DrKpw_46452_47939.png Lightning_Talks_p5Esr6DrKpw_47940_48179.png Lightning_Talks_p5Esr6DrKpw_48180_49835.png Lightning_Talks_p5Esr6DrKpw_49836_51479.png Lightning_Talks_p5Esr6DrKpw_51480_51983.png Lightning_Talks_p5Esr6DrKpw_51984_53051.png Lightning_Talks_p5Esr6DrKpw_53052_53507.png Lightning_Talks_p5Esr6DrKpw_53508_56219.png Lightning_Talks_p5Esr6DrKpw_56220_57959.png Lightning_Talks_p5Esr6DrKpw_57960_58463.png Lightning_Talks_p5Esr6DrKpw_58464_58955.png Lightning_Talks_p5Esr6DrKpw_58956_60899.png Lightning_Talks_p5Esr6DrKpw_60900_62027.png Lightning_Talks_p5Esr6DrKpw_62028_64859.png Lightning_Talks_p5Esr6DrKpw_64860_67463.png Lightning_Talks_p5Esr6DrKpw_67464_72431.png Lightning_Talks_p5Esr6DrKpw_72432_73127.png Lightning_Talks_p5Esr6DrKpw_73128_74555.png Lightning_Talks_p5Esr6DrKpw_74556_74939.png Lightning_Talks_p5Esr6DrKpw_74940_75707.png Lightning_Talks_p5Esr6DrKpw_75708_75971.png Lightning_Talks_p5Esr6DrKpw_75972_76175.png Lightning_Talks_p5Esr6DrKpw_76176_76715.png Lightning_Talks_p5Esr6DrKpw_76716_77747.png Lightning_Talks_p5Esr6DrKpw_77748_77807.png Lightning_Talks_p5Esr6DrKpw_77808_77951.png Lightning_Talks_p5Esr6DrKpw_77952_79247.png Lightning_Talks_p5Esr6DrKpw_79248_80519.png Lightning_Talks_p5Esr6DrKpw_80520_82547.png Lightning_Talks_p5Esr6DrKpw_82548_84203.png Lightning_Talks_p5Esr6DrKpw_84204_84575.png Lightning_Talks_p5Esr6DrKpw_84576_85343.png Lightning_Talks_p5Esr6DrKpw_85344_85475.png Lightning_Talks_p5Esr6DrKpw_85476_86315.png Lightning_Talks_p5Esr6DrKpw_86316_86525.png