 OK, welcome to our next talk about PostgreSQL. So please give a warm applause to Michelle Bank. Hello, everybody. My name is Michelle Bank. One of the organizers, I work for Creative. And I have a typo in a slide title, because it's Keeping PostgreSQL 8.4 Live for Squeeze LTS. Sorry. 9.4 just got released in the end of last year. And then they'll be on for a while. But 8.4, well, let's first talk about PostgreSQL. It's one of the major relational database systems. And actually, it's pretty used. It's used quite a bit in Debian infrastructure. So to my knowledge, Dac uses it. And I want to build at least can use it. I think we have moved to it. I'm not quite sure. I think Alioth uses it. And Summit uses it as well. So the conference itself runs on PostgreSQL to some degree. It has the reputation for being rather rock solid compared to some other databases. It has also reputation for being less of a web scale upstart thing in the 90s. But it's becoming much more popular in the last couple of years. Lots of startups now use it. It seems to be getting very popular due to some rather advanced features coming in, like having native JSON support, which is loved by people, actually being able to use indexes on it. So you have really fast JSON querying and search and that kind of stuff. There's also foreign data wrappers now. So you can actually modify it and then use it with other data sources and stuff. And then people are doing cool stuff with it. So it seems to be getting a second coming of Postgres in the last couple of years. Replication has landed. And it's also due to, let's say, multi-core systems and some vendors licensing every core. It's getting also very popular as enterprise workload replacement. So there was a Gartner report this year saying that you can basically use it for any kind of enterprise workload these days. But well, the Postgres itself is run by the Postgres Global Development Group. It's not run by any vendor. It's totally independent. There is a core team of, I think, six or seven people who decide the policy. And from a release point of view, it's basically released every year. It used to be around September. And the current policy is that they freeze a beta, which is then feature freeze in around March, and then try to stabilize it until it's ready. So 9.4 was a bit late. It only got released shortly before Christmas. So we almost missed Jesse. It kind of got released after the Jesse freeze. But thanks to the release team, we managed to get it in via the beta and release candidate releases. And then as a first stable policy there, they do have, if you have five years of upstream support after release, so 9.4 will get releases for the next five years. And that means that you basically have five branches to patch every time you do a point release, which is kind of a bit of a worry. But they're doing it for five years. And they all do it at the same time. So we get roughly five to six point releases every year with cumulative bug fixes for every branch. And you can see the upstream support timeline on that, where I'll post it there if you're interested. And they detail very clearly when a release got made and when it will be end of life five years later. Also, for the point releases, they have a really rigorous policy. It's a long-term project. I mean, it's been there since the 80s. And it's been an open source project since 95s or 20 years. So they really are. They know what they've been doing. Still some of the people are still there. And they only fix real bugs and some things, which they think is low critical and doesn't have a lot of risk to change in order to ease migrations and that kind of things or actually make it easier to backpatch other things afterwards. So they have a good trade-off there. If it's a huge involved bug fix and it's not high risk, then they will just not backpatch it. Or they will backpatch a less severe change. And due to that, it has been quite listed by the Debian stable release managers for quite a while. So any Debian stable release in its own point releases will get actual upstream point releases by Postgres and not just some patches on top of whatever was released at the time of the Debian release, which had to be manually reviewed by the Debian release team. So if, say, 8.4 point, whatever, 19 was the release at the time of Squeeze. And 8.4 point 20 got released as a point release afterwards. Debian would just pull that in without actually having a lot of trouble arguing about whether or not it has the right kind of fixes. They just say, OK, yeah, we trusted upstream Postgres people to do the right thing and we'll do it. So as I said, Postgres 8.4 is the version in Squeeze. It got released in July 2009. And it's actually quite a good release. There used to be in 8.3 and before, you used to do some rather unclear tuning things about free space maps and stuff, which was quite opaque to most non-senior DBAs, I would say. But 8.4 is better. I mean, you still have to tune it to memory requirements and that kind of stuff. But that's a given, basically, at that time. It still has no built-in replication that came one release later with 9.0. But it's actually pretty reasonable performance. So Postgres was known to be rather slow in the 90s, or maybe 20005 or something. But from 8.0 on, 8.1, 8.2, 8.3, they made huge performance improvements for single query workloads. So back then, they still didn't look at, well, how would it scale on a 64-core system? Because in 2009, that was not what most people had. And also, the developers didn't really have a huge machine at hand. So it has a reasonable performance for small cores. So if you're running on eight cores or something, it should scale to eight cores if you run eight queries in parallel. But if you run it on a 64-core machine and it might only scale to 16 or 20 cores at that point. But it's OK for smaller cores. So it's actually a good release. It's quite stable. Doesn't have a lot of bugs. The 9.0 release got huge changes in the replication system. So they weren't that severe, but it wasn't clear. So it's pretty good. It was end of live in July 2014. So a bit over a year ago. And the actual last point release was 8.4.22 at the end of July. And Debian Squeeze ships 8.4, as I said, as its default Postgres release version. And it was end of live in May 2014. So about a year ago for official Debian security team support. But that was around the same time as Postgres QL8.4. And as you all know, there is and was back then the Debian Squeeze LTS effort, which I think is a great thing for people trying to run Debian in a stable production-based workload and don't need to or don't have to upgrade immediately. So the Squeeze LTS team has extended the support for Squeeze until February 2016. It's organized by Frisian. I'm hoping I'm pronouncing that right. And Raffael Herzog has mostly been driving this. There was a talk from him from Saturday. So I'm not sure whether it's already online. But the slides, OK, it's already online. Thanks. The slides are there. And you can look at that if you missed it. And so basically what we had about a year ago was that we started Squeeze LTS. But actually Postgres was no longer upstream supported. So they wouldn't do any more point releases. They wouldn't do any more bug fixes. So yeah, what to do about it. So my employer, Creative, we decided to step in and actually continue upstream's work of backpatching changes to the 8.4 release in order to generally do it, but mostly with a target of Squeeze LTS. So this is not just my lots of the Squeeze LTS packages are patches on top of the already existing Squeeze package or maybe a new upstream release gets uploaded if that's possible. But we've been actually doing the upstream work to do the LTS. So most of the work actually was doing the upstream work. And then the packaging, that's kind of like a standard job for my colleague, Christoph Burke, who's doing most of the Postgres uploads. The work we mostly did was actually backpatching, figuring out the patches and doing the upstream release. So we created a repository on GitHub at the Creative team. It's called Postgresql-LTS. And we backpatched all the changes there. We called the branch 8.4 LTS instead of the regular 8.4 or rail 8.4 release, as it's called. I think it's called rail 8.4 stable, sorry, from upstream. We had a discussion with the Postgres core team about naming, and we decided to de-brand it. So not calling it 8.4.23 once we make a point release, just so to make sure that nobody confuses it with whatever they are providing upstream. So we did that in that GitHub repo. So far, there's been more than 150 commits over the time. There's been quite a few bugs still. Nothing extremely major, but it's kind of still fixed. So there was still work to do, which we did, basically. And we did four releases since then, because there were four point releases this year. We have to say we were a bit lucky, because there was no point release last year, so we didn't have to scramble immediately to get things going. We had a bit of a head start, and I think the first point release was only in February. So for some reason, Postgres decided to not have a point release for half a year, maybe also because 9.4 was delayed and they were concentrating on that. The last commit I have to admit is from July 3rd. So, well, there hasn't been a point release announced yet, and we will get to it, but we are constantly updating it in batches, I would say. And personally, I was a bit busy with DebConf organization, so I didn't do anything much in the last month. Right, so the release process in general is the core team decides when a point release is due, either because there is a pretty bad bug, or there's been a lot of time, or there's pending security fixes. Then somebody assembles the release notes from the commit messages. So in general, the commit message are great, and in Postgres they take a lot of care to explain what they're doing. And also the release notes are really good, this is a summary of the commit messages usually, so people can figure out what actually changed. And the typical schedule is that the tree is tagged on Monday, and then the towel balls are released on Thursday. So that means that packages internally get a kind of a head start, and it's except for really, really secure. There was one case where there was a high impact security release where they actually even took down the Git repository so nobody could see it, but otherwise it's basically tagged Monday. If there's some security stuff, they just commit it immediately before they're tagging it, and then they make a release of all the active branches. So our policy was to just backpatch everything which is in a 9-0 branch, so all the commits which got there, we looked at it, and if they were applicable still to 8-4, so the code wasn't totally changed, it did not do anything with this replication stuff that they put in, we would backpatch it, and we would try to make a branch release of the LTS branch at the same time as the official back branch release, and we decided to call it 8-4-22 LTS-1-2-3, as I said, to avoid naming confusion with the upstream Postgres team. So these are the releases that we did. As you can see, 8-4-22 LTS-1, we tagged on the 12th of February, so about a week later than official release, and then everything else was basically at the same day. We tagged 8-4-22 LTS two days before because as I said, the tag actually is done a couple of days early upstream as well, so we were ready to release it. So right now, we seem to be doing quite okay with getting the back branch 8-4 releases out and uploading them to Davian Squeeze LTS that seems to work. So now we have the problem that Postgres 9.0, which we've been taken for as a base for back branching, has been announced for end of life in September, so there will be probably one more point release if we're lucky of it, but that's it. So we decided, okay, until February, 2016, or whenever Squeeze LTS is officially end of life, we will continue to back patch changes from the 9.1 branch instead. That's, it's, well, there's still more changes. It might be a bit more difficult, but we at least try to add more changes if it's possible. That's the current thing. We hope that that should work out fine, you'll see. So now the next thing is Weezy LTS. I believe it's pretty much a given and was announced that it will take place and Weezy ships 9.1. So upstream support is until September, 2016. And the Weezy regular security support end of life is again, February of 2016. So we will already have a gap between the beginning of the LTS. No, we actually not have, but okay, there will be a half a year of LTS. We can use upstream if they're still doing it. But then again, for the rest of the Weezy LTS cycle, there will be no upstream support. So we expect to do the same work again and to continue post-crest QL 9.1 until then. So far we've been doing this on a best effort community basis. That means that if nobody else in the database team has anything more urgent to do, like a client's database has just gone on fire, we might look at the LTS branch and then do some commits. But of course, if somebody is really interested in it, we can also do it on a support contract. Also, I want to announce or remind that there's a preparing for Weezy LTS session today at six in Room Amsterdam. If you're interested from a developer point of view, I really like shaping the policy there. So that's basically what I have, what I wanted to present. That's a contact, as I said, we're creative. Again, our GitHub repository if you want to check it out and we're also still looking for new people helping us doing our job. So I hope I was on time and thanks for your attention. Do you have any, if there are any questions, please raise your hand. Do you have usage stats? So did you look at how many users are actually left on LTS using Postgres? I actually tried to look at it. GitHub is pretty bad at showing you how many downloads there are. Didn't get to it before preparing the talk to figure it out how to get there. There seems to be some APIs, but I think it's not actually that many. That's why we haven't heard any, we didn't get any bug reports, I have to say. So nobody really complains, it's not working. That might be a good or might be a bad thing. And you could probably look at a popcorn count. If, well, there's also the problem that people running stuff in production might not enable popcorn. So it could be that it's pretty difficult to figure out. So I think one problem is that squeeze LTS was announced so late that lots of people already saw the horizon or the end of life coming and decided to already jumped off the boat and into Weezy. So, and also we may have announced it so late that people didn't really see, oh, there is still support for 8.4, I can run it. So maybe with Weezy it might get better. So if people know already in advance and can plan, okay, cool, I can run it until a couple of years more. We'll just keep going with 9.1. It might be actually have more uptake, but yeah, I don't have any concrete numbers, unfortunately. How do you deal with the official recommendation which I see quite often, for example, on Postgres performance that when people are using old versions, they are advised not to try to bother with fixing some problems on old version or tweaking configuration, but updating to what's newest and at the time of writing? Well, what I see usually is that people are very much advised to use the latest stable point release of whatever branch they're using. So if, but if somebody is, well, it depends. I mean, of course, if somebody has a huge query with tens of joins and it's running on 8.4, then yeah, the answer might be right. We optimized that in the last couple of years. We will not, I mean, it's clear that they will not fix structural changes or back patch, as I would say, performance increasements which are usually features and not bug fixes. There are a couple of obvious, I mean, if it's really a bug in the planner or in the optimizer and it's not invasive, they would back patch it. I mean, we as a company are not, as I said, we use the same policy. So in general, we also advise our clients who run Weezy if possible, but or anyway, I mean, just as a, to drop that in, my colleague, Christopher Burke, is also running at postgresql.org and there you get actually the, any kind of upstream version for any kind of Debian release. So you can get the 9.4 version for Weezy. I'm not sure he's doing it for Squeeze though, but in general, you can get new upstream versions over apt.postgresql.org, which is a great thing. So he's basically building every postgresql version on every Debian version and any Ubuntu LTS version. Is that, sorry, does that answer your question? Are these supported on the same basis as for LTS, so? Can I relate? You mean the ones at apt.postgresql.org? They are supported on a community basis as well, right? So, I mean, if one of our clients is running them and they have a support contract, we would of course support it on a support basis, but otherwise it's kind of like, yeah, it's a community project. It's run by postgresql.org. It's not run by creator chief or Debian, just that Christoph is doing most of the work. And if there's bug reports, the community will look at it, I would say. And of course, if you have commercial support, you can get somebody to look at it as soon as possible. Otherwise, I mean, but also I think there's not a lot of bug reports coming in over there. So it also seems to be working quite okay. Christoph's not here, I think, so he cannot comment, but talk to Christoph about it if you're interested. It's a really cool thing. He has also, I think he has a talk later this week about it, I'm not sure, because they're building thousands of packages with all the, because you have all the extensions and all the versions and it's a huge matrix and they're doing the Jenkins and it's a pretty cool project. There will be talk about this on Thursday, I mean. Right, I think he's giving a talk about it, right. So if you're interested, sorry, I didn't, I forgot to put it on, but check the schedule. It's Christoph Burke. Sorry? Friday, 1500, thanks. If there are no other questions. Ah, okay, okay. Yes, sorry, just the last one. I, maybe you said it in the talk, is these are just security fixes or maybe other smaller stuff or enhancements as well? No, that's actually, most of them are bug fixes. So from these 150 commits, I would say only like 10 actually fix the CVE. And due to the, as I said, due to the Postgres people being really good, the stable release managers always accepted also these bug fixes because they were very considerate and yeah, just improving the product, basically. Okay, not a question, but announcements. Michael Stonerbreaker, who created Postgres won this year ACM Touring Award. So he also gave the speech, which is, which should be already on ACM web page. Yeah, I watched it, yeah, it's pretty good. And he also gave a shout out to Postgres. He basically actually gave a Postgres tour of how it went on. So if you are into Postgres, watch this. Okay, it's good news. So thank you very much and enjoy your lunch.