PostgreSQL Goes to Eleven!

Video thumbnail (Frame 0) Video thumbnail (Frame 1493) Video thumbnail (Frame 3006) Video thumbnail (Frame 8806) Video thumbnail (Frame 9816) Video thumbnail (Frame 11071) Video thumbnail (Frame 12039) Video thumbnail (Frame 17989) Video thumbnail (Frame 28015) Video thumbnail (Frame 29641) Video thumbnail (Frame 36622) Video thumbnail (Frame 37757) Video thumbnail (Frame 49406) Video thumbnail (Frame 61055) Video thumbnail (Frame 72704)
Video in TIB AV-Portal: PostgreSQL Goes to Eleven!

Formal Metadata

PostgreSQL Goes to Eleven!
Title of Series
CC Attribution 2.0 Belgium:
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.
Release Date

Content Metadata

Subject Area
The latest and greatest version of PostgreSQL is PostgreSQL 11. This talk will start off with some history of the community and a description of the development process including some metrics trends. It will then cover a high level overview of the features which will be part of PostgreSQL 11, and prognosticate a bit about the future of PostgreSQL. Community * History * Development Process PostgreSQL 11 * Patches committed 12 and Beyond * Actively worked items * Under active discussion
Enterprise architecture Term (mathematics) Multiplication sign Projective plane Whiteboard Event horizon
Laptop Process (computing) Patch (Unix) Projective plane Core dump Electronic mailing list Bit Selectivity (electronic) Discrete element method Freezing
Point (geometry) Sequel Open source Code Interior (topology) Multiplication sign Source code Relational database 1 (number) Database Student's t-test Neuroinformatik Formal language Query language Wiki Information retrieval Root Physical system Standard deviation Electric generator Relational database Projective plane Open source Interactive television Database Group action Flow separation Product (business) Query language Information retrieval Data conversion Prototype Physical system
Link (knot theory) Enterprise architecture Online help Graph (mathematics) Server (computing) Web page Source code Electronic mailing list Core dump Database Data storage device Bookmark (World Wide Web) Formal language Product (business) Product (business) Data model Green's function Bridging (networking) Data conversion Form (programming) Extension (kinesiology)
Trigonometry Game controller Multiplication sign Physical law Core dump Core dump Database Database transaction Mereology Data type Physical system Flux
Slide rule Group action Building Multiplication sign Patch (Unix) Translation (relic) Mereology Formal language Product (business) Latent heat Different (Kate Ryan album) Core dump Automation Diagram Software testing Data structure Error message Associative property Computing platform Form (programming) Email Projective plane Feedback Expert system Electronic mailing list Counting Core dump Database Ring (mathematics) Order (biology) Self-organization Whiteboard Table (information) Diagram
Point (geometry) Mobile app Enterprise architecture Software developer Multiplication sign Patch (Unix) Database Insertion loss Branch (computer science) Sphere Mereology Number Software bug Product (business) Revision control Mathematics Shared memory Software testing MiniDisc Information security Stability theory Physical system Thermodynamischer Prozess Linear regression Cycle (graph theory) Projective plane Planning Menu (computing) Bit Lattice (order) Numbering scheme Frame problem Replication (computing) Shooting method Personal digital assistant Revision control Bridging (networking) Ubiquitous computing Website Cycle (graph theory) Freezing Spacetime Extension (kinesiology)
Email Group action Thermodynamischer Prozess Closed set State of matter Multiplication sign Time zone Set (mathematics) Database transaction Software bug Mathematics Core dump Thermodynamischer Prozess Email Link (knot theory) Theory of relativity Feedback Electronic mailing list Bookmark (World Wide Web) Open set Thread (computing) Connected space Data management Order (biology) Software testing Convex hull Cycle (graph theory) Physical system Point (geometry) Trail Mobile app Process (computing) Variety (linguistics) Feedback Patch (Unix) Division (mathematics) Discrete element method Rule of inference Mach's principle Revision control Hacker (term) Internetworking Authorization Software cracking Address space Patch (Unix) Video tracking Projective plane State of matter Volume (thermodynamics) Line (geometry) Mathematics Inclusion map Single-precision floating-point format Freezing
Tuple Covering space Source code Price index Client (computing) Software bug Set (mathematics) Information security Exception handling View (database) Electronic mailing list Parameter (computer programming) Bit Control flow Replication (computing) Partition (number theory) Hash function Code refactoring Cycle (graph theory) Inheritance (object-oriented programming) Computer file Sequel Variety (linguistics) Patch (Unix) Mathematical analysis Number Differenz <Mathematik> Cache (computing) Subject indexing Energy level Newton's law of universal gravitation Authentication Information Server (computing) Run time (program lifecycle phase) Electronic program guide Code Counting Client (computing) Line (geometry) Limit (category theory) Resource allocation Query language Network topology Revision control Table (information) NP-hard Keyboard shortcut Just-in-Time-Compiler Code State of matter 1 (number) Port scanner Insertion loss Mereology Replication (computing) Subset Mathematics Hash function Query language Chromosomal crossover Vertex (graph theory) Extension (kinesiology) Partition (number theory) Area Data recovery Feedback Parallel port Mountain pass Statistics Type theory Data mining Computer configuration Repository (publishing) Compilation album Software testing Procedural programming Information security Metric system Physical system Digital filter Statistics Server (computing) Regulärer Ausdruck <Textverarbeitung> Table (information) Feedback Authentication Limit (category theory) Generic programming Login Read-only memory Uniqueness quantification Statement (computer science) Default (computer science) Context awareness Just-in-Time-Compiler Parallel computing Projective plane Memory management Planning Stack (abstract data type) Predicate (grammar) Similarity (geometry) Subject indexing Cache (computing) Number Commitment scheme Blog Key (cryptography) Computer worm
Polymorphism (materials science) Email Mehrplatzsystem Source code Price index Database Client (computing) Parallel port Variable (mathematics) Software bug Type theory Different (Kate Ryan album) Finitary relation Encryption Multiplication Physical system Dreizehn Building Web page Electronic mailing list Bit Database transaction Control flow Formal language Replication (computing) Partition (number theory) Message passing Order (biology) Data conversion Quicksort Point (geometry) Web page Slide rule Vacuum Sequel Computer file Disintegration Similarity (geometry) Library catalog Branch (computer science) Online help Data storage device Goodness of fit Term (mathematics) Intrusion detection system Subject indexing Authorization Autonomic computing Energy level Implementation Communications protocol Backup Metropolitan area network Form (programming) Data type Default (computer science) Key (cryptography) Code High availability Login Coma Berenices Line (geometry) Cartesian coordinate system Existence Compiler Graphical user interface Visualization (computer graphics) Personal digital assistant Query language Function (mathematics) String (computer science) Revision control Formal verification Musical ensemble Table (information) Integer Scheduling (computing) Window Domain name Standard deviation Installation art Run time (program lifecycle phase) Just-in-Time-Compiler Ferry Corsten Code Multiplication sign View (database) Visual system 1 (number) Set (mathematics) Insertion loss Parameter (computer programming) Replication (computing) Database transaction Bookmark (World Wide Web) Subset Synchronization Hash function Query language Logic Information Data conversion Extension (kinesiology) Pairwise comparison Partition (number theory) Scripting language Email Process (computing) Wrapper (data mining) Data recovery Concurrency (computer science) Computer file Data storage device Parallel port Statistics Element (mathematics) Connected space Type theory Data mining Array data structure Exterior algebra Computer configuration Software testing Right angle Encryption Procedural programming Data type Physical system Asynchronous Transfer Mode Asynchronous Transfer Mode Game controller Functional (mathematics) Server (computing) Vapor barrier Table (information) Link (knot theory) Line (geometry) Transport Layer Security Data recovery Login Discrete element method Revision control Network topology String (computer science) Integer Absolute value Tunis Mathematical optimization Window Operations research Noise (electronics) Multiplication Inheritance (object-oriented programming) Expression Memory management Planning Analytic set Database Subject indexing Logic Lie group Password Electronic visual display Communications protocol Vacuum Address space Extension (kinesiology)
okay in the spirit of being an event
that runs on time we'll we'll get started here with the next session this is Joe Conway he's been a committer on the the post post best project for almost 20 years he's going to talk about both the Postgres community and some upcoming features in the next releases so Joe take it away thank you can you guys hear me in the back all right that aren't good all right well you said my name is Joe Conway I'm the VP of engineering at crunchy data and a long term community member committer I'm on the infrastructure team for the project and I'm also on the board of directors for the the u.s. Postgres association so
what I'm going to do is I'm going to cover a little bit of history about the project and then quite a bit about how the community works and then toward the end I'm gonna cover out of real high-level stuff that hit in Postgres 11 kind of more like a bullet list of features that maybe will catch your attention at all you know zero in on a few of them but if you want to see real details about those things one of the other community members and core team member magnus hag ander is giving an in-depth talk about Postgres 11 certain select features in Postgres 11 tomorrow in the dev room so you should go check that out too and I will touch on some things that have been committed toward Postgres 12 and some other things that are being still worked on it might or might not make it into 12 now my laptop is freezing what's happened to the last guy that was
working all right so maybe yeah I guess I'll just have to use the switch on the
computer so the the Postgres project started at the university of california
berkeley this is just a couple of pictures from the campus but even before
that there was a project at Berkeley called ingress how many people here have heard of ingress actually quite a fair amount it's not very popular these days but basically in 74 there was a professor named stone breaker at University of Berkeley and he started a project which was really kind of the first modern relational database and they called it ingress and stood for inner interactive graphics retrieval system which interestingly doesn't sound much like a database but I think it was because his grant was actually based on something else and he decided to build a database with it or something like that and that went on to spawn some some well-known commercial databases ingress has you know its roots can be seen inside base and MS sequel something called non-stop sequel and some other database technologies that are out there and in 1980 that ingress actually became a commercial company and and then changed name several times and it's actually still around today as a company called act Ian's and they open sourced ingress back in 2006 so you can still use ingress you can get the source code for it you can get support from Actium and that's not gonna work okay so in 1986 the same professor Stonebraker started the Postgres project and the idea was it was post ingress so that's literally where the name comes from Postgres and it was supposed to be the neck generation it was an object-relational database at this point in time there wasn't an actual SQL standard so they invented in a language called quell that he would use the query of this database and that original source code actually spawned other commercial databases including a la strada Informix which I think at this point are pretty much dead olestra spawned and formics informix co-opted by IBM and i think it kind of died a quiet death a few years ago although I still hear about people using in a formics from time to time when they're converting to post growth in 1994 and 95 there are two students two graduate students I think at Berkeley named Andrew U and jolly Chen and they're the ones who basically took the SQL which was now as an emerging standard and strapped it on to what was post crush at the time and at the same time they also critically modified the license and made it more liberal because that set us up for 1996 which is when the Postgres project actually was formed the community started with commits into CVS from the original code that came from Berkeley in 1996 so you can see as an open source project Postgres has been around for over 20 years now and has a source code the project has been around for over 30 years now so it's it's been around a long time it's really pretty much as long as all of the other commercial databases that are out there also I make note that there is a wiki page which shows all the different
derived products from Postgres so one of the things because the license is so liberal it's basically described as either a BSD form or MIT form license which the way the community community generally sums it up is you can do anything you want with the source code just don't sue us so because of that it's been forked and made into many different things over the years and you can see based on this list there's quite a lot that has been spawned by Postgres as a project many of these commercial entities that have you know made good money from it so
just for fun I have a couple pictures here this is uh this is Michael Stonebraker the originator jolly channa Andrew you the two guys you who turned Postgres into a SQL database and this
was the original core team a few of these people are still around Tom Wayne and Bruce mom Jen are still members of the core team these other folks have come and gone for the most part worked on various parts of the system the deme did the major work on the MVCC which is the transaction control of Postgres uses thomas law clerk did a lot of the work on the early the early work on date time datatypes and and so on and our first conference basically as
the community was in 2006 for the ten year anniversary of the open sourcing of the project so this is a picture of all the people out in front of University in Toronto I think it was I'm in there somewhere there's a bunch of people that are walking around the halls here there's a Postgres table up kind of behind us over here and a bunch of the people that were in this picture 13 years ago are still active members of the community and and walking around here today so this diagram I got
actually last year at the Postgres dev room there was a talk by and I know I'm gonna butcher her name as Latia Averell and she put this slide out because she was talking about the Postgres community and it struck me as like a perfect slide for describing how the post-post community works and I had been thinking about the structure of the community for many years but I had never actually put it to a slide and when I saw this it was like I've got to use that so she gave me permission to use it and I've used it a few times now but basically in order to understand the community this kind of sent this ring form over here is important there's a core team that's five people and they provide the governance for the project but closely associated with them and it also included in much of the governance are the people who have commit access there's 28 total people with commit access including a few of the people who are on the core team so the entire project is basically managed by that core group of people but for every release there are a lot of people that are not part of that core group that submit patches review patches they do translations they do testing they run what we call build farm animals which is how testing gets done in an automated way across different platforms so all of these people are very important to the community as well these are the people that are doing reviews people are developing packet pack patches the people who are doing translations Postgres is translated and the actual error message and so on are translated into well quite a few languages so that's all due to that larger group and that group keeps growing release after release I think someone did a count on the release notes for a major release we try and name everyone who did something that was kind of directly touched something that got committed and maybe someone can correct me if I'm wrong but I believe the list for Postgres 11 was something just over 300 people so there's quite a few people get involved in the production of Postgres as a database now also you notice that we have over here user groups and associations and advocacy and you know as I mentioned I'm on the board of directors for PG us there's a PG Europe organization there's an organization in China I think in India that probably sure there's one in Russia as well there's there's a bunch of them basically spread out and mainly what they're there for is to advocate the use of Postgres and to teach people how to use Postgres and so those groups run most of the Postgres specific conferences where you can go learn in depth from experts and that same group Postgres europe is running a ran a PG day here in in Brussels yesterday at the Marriott and they're running the dev room tomorrow so tomorrow the dev room will have I don't know exactly how many probably six or so talks that it will be dedicated to post grouse and then further you've got the users everyone is using Postgres maybe participating the mailing list maybe not those folks are all part of the community too we've got a very active and robust community we get lots of feedback from the users that gets incorporated into fixing things that are broken or new features that come out and so that the project is very much driven by its its community
sometimes it works sometimes it doesn't so just this picture is kind of a to show you what the Postgres development cycle a typical development cycle looks like so this is probably I don't know if this is readable in the back of the room here but basically at some point here in the kind of August timeframe last year in shooting in 2017 we branched a stable branch for Postgres ten when that happens basically we open up development on whatever is going to be next so in this case it would have been post Chris 11 so all the new development starts as part of the development process we have a number of these commits fest C F here C F one two three and four commits fess I'm going to go into that in more detail but basically commit fest or where the other people who have submitted stuff patches where we focus as a community on reviewing those packages getting them cleaned up to the point where they can be applied and getting them committed so we focus on what everyone else has provided during those time frames as opposed to what the committers themselves maybe want to work on and more and more in recent years even the committers have have tried to put their patches into this commit fest process which I'll describe later there's also we do that one month at a time and we skip a month in-between so this is typically here for the Postgres 11 this was September November January and March this year we actually added a fifth one we branched a little early and for Postgres 12 we actually have five confess we started a bit early but this is pretty typical over the years you also notice I have this developer meeting at FOSDEM which is something we did this Thursday before we have the PG day and then at the end of this fourth commit fest that's what we call future for use for that release so the end of March or basically that usually spills over slightly into April but about the end of the first week at April we called last year we called feature freeze on Postgres 11 and then shortly after that we declared a beta 2 beta testing and here there's a very short release candidate cycle and then we released 11 here toward the end of October last year so our goal is normally to get out of release once a year and we are generally shooting for dusting yeah so we try and get a new major release out once a year still not testing yeah okay in in the the September timeframe and you know that we the project is very conservative so if if it's not ready to go we're not going to ship it and again I'll talk a little bit more about the the process of how we get that done but there have been years where the major release slipped into you know as it did last year into October or even kind of later and we've been making a consolidated effort recently to kind of pull it back to where it would happen closer to September so just a note on the versioning huh how many people here are familiar with how Postgres does the versioning and the change recently not too many so this this could be something that would be confusing up until version 9.6 Postgres used a three segment release version so this nine point six point eight nine point six in this case is actually the major version portion and the point eight indicates a bug-fix release so that's important to understand because one of the other things about the Postgres community is new features only go in the major releases and the bug-fix releases are truly only bug-fix releases and so therefore you really should as soon as you can when a bug-fix release comes out you should try and go apply it to your production systems because it fixes known bugs and it shouldn't create any regressions there you know over the 20 years I've been involved with the project I can remember a very small less than a handful of times where there was some kind of a regression that was caused by a minor release there are definitely a lot of things that get fixed in every minor release so highly recommend that you upgrade those minor releases you know the the major releases take more planning and more effort and so you can space them out a bit but not the minor releases all right how's that that's good so anyway starting with Postgres 10 we switched basically to a two segment so now 10 is a major release 11 as a major release 12 will be a major release and when you see 10.3 that's actually a bug-fix release probably the other thing that's worth noting here is that the minor releases we usually target those about once a quarter so if there is some kind of an emergency some kind of a data loss bug that's discovered or a security issue we do sometimes do interim minor releases but they tend to come out if they're coming out as scheduled they come out once a quarter and there is a there is a dates listed on the Postgres website for that okay so now I want to talk a little bit about the commit fest we have this
this app that was put together by one of
the core team member Magnus wrote this and maintains it and basically this is a place where if you've got a patch that you want to submit to the Postgres project you would go register it on the open commit fest so I don't know if you can see this so you can see here the 20 1903 commit fest which is the March commit fest is currently open that means if you had a patch you wanted to submit the Postgres you'd go register it in there let's see if my internet connection works and you can see when you go in there basically each patch gets a line item it has a status I'm going to talk about some of these things and this app is kind of designed to track the mailing list discussion that'll happen on the hackers mailing list with respect to that particular patch as well as keeping track of the last version of the patch and who's going to review it and who's planning to commit it so one entry in that commit
fest can be either one patch or it can be a related set of patches that build on top of each other and they implement some goal it might be a bug fix it might be some refactoring it might be documentation improvements or it might be new new features and they're gonna be one or more authors and one or more reviewers that are listed and you know as I just said it could be it can be really a whole variety of things that are included in those the goal of the commit fest is that patches don't get lost before we started doing the commit fest we were getting an increasing volume of patches being submitted by people outside of that you know core group of core team members and committers and things would start getting lost through the cracks and so this this is the goal of this is to make sure that everyone who does want to participate in the community gets a fair chance at actually having their patch reviewed the other thing is it makes it easier for us to find things that need our attention and and the goal here is also to encourage if if you're going to submit five patches that you want to get into Postgres 12 let's say the hope is that you'll go review five other people's patches because that's the only way this process is going to scale so that in a nutshell that's what we're trying to do with that the entry states as you can see here needs review basically that's a patch that has needs someone to look at it not necessarily a committer and again in fact the the idea here is that we can have people who are not committees doing the initial triage on these things helping them get improved to the point where they're ready for a committer and then once they're marked ready for a committer because that's a scarce resource a committer will go spend time on that if the reviewer says something needs to be fixed or changed they might set it back to waiting for author because now they're basically asking the author and make some change once it gets committed obviously it gets marked as committed we do have patches that get rejected for a variety of reasons sometime the author submits a patch and then kind of disappears and we have feedback and they don't address it sometimes we end up marking them as returned with feedback because no one else wants to pick up a patch and finish it and we can also have them get moved to the next commit fest the the basic process is that the day before the commit fest starts is the deadline for getting a patch in for that open commit fest and that's defined as anywhere on earth so at the end of February anywhere on earth you can still submit a patch and get it into the commit fest and when the commit fest starts all patches should either be in a needs review or ready for a committer state we assign someone from the community to be a commit fest manager that person is basically the one who's gonna bug everybody and try and keep things moving keep the status is updated and whatnot the reviewers will review provide feedback the authors will make adjustments and then when it's finally ready it'll get committed if we're waiting on an author for too long we will mark it as return with feedback and at the end of a commit fest in particular if the patch still needs review will automatically move it to the next commits fest but if it's waiting on author at the end of the commit fest it'll get automatically get mark returned with feedback and so the author now needs to take some action to address whatever the feedback was in order to get it into another commit fest if they care to do that the other thing is the last commit fest so that commit fest in March that I showed you that's the last one of a development cycle for a major version just before feature freeze we do have an extra rule that basically something that's large and invasive shouldn't show up for the first time in that commit fest we're also much more aggressive at taking large patches that look like they aren't going to make it in before the feature freeze and and marking them return with feedback or whatever in moving them to another commit fest for the next major release
okay so now I've got some metrics here for you though this is the nine six ten and eleven cycles you can see here nine six we also had five commit fest in 12 we're gonna have five commit fest but you see the level of activity here has been going up pretty steadily the the stark part of the bar is the committed so you can see we're getting more stuff committed move to the next commit fast stuff that just didn't get finished and that's also increasing in size the rejected is uh you know pretty consistent throughout it's small but it's there and return with feedback has actually increased a bit because we've had more stuff submitted some statistics
out of the repository this is going all the way back to Postgres 72 which was think back and around 2001 so the number of files changed from major release to major release is increasing but not by a huge amount insertions and deletions of lines of code is actually with the exception of a few spikes has been pretty consistent and the diff size and again Postgres 10 seem to have a abnormally large diff size for some reason maybe because all the partitioning code that went in but and that's been kind of slowly increasing from release to release okay so now I'm gonna cover some of the things that got committed in 11 I just wanted to start first with again some numbers it's kind of funny someone was show me last night I guess someone did a blog about the talks at FOSDEM and they were like it was a satirical blog and basically they were roasting each of the the talks and so what they said about mine was that I was gonna stand up here and read you the commit logs for an hour but the funny thing is I actually went through the commit logs for post Kristin and post crystal 11 putting this talk together I'm not obviously going to try and read them to you because they're like 15,000 lines each but but I thought it was it's good to look at kind of the variety and the the absolute numbers of how many things get done in each release so you can see here bug fixes 84 a bunch of client work does anyone here know what do you see PG is Steven you don't count I didn't even know you were back there you can see even down to code comments documentation changes lots of performance related stuff and I'll get to this in a little bit in a little bit more depth you can see a lot of things having to do with parallel query and partitioning and try to categorize these is kind of hard because there's a lot of crossover some of the things that are kind of partitioning related are also kind of performance related as well and vice-versa procedural languages refactoring 38 so these are these are just patches that did nothing but move code around to make it easier to understand and maintain Postgres code is by most accounts some of the cleaner or source code out there for open-source projects I don't know if everyone will agree with that and and certainly in the community we sometimes complain about our own source code but I think in general it's pretty good a couple of security fixes a bunch of things related to the to the server features new sequel commands sysadmin alright so here's a list now of things that did get committed in eleven and you know again if you want in-depth information about these go see Magnus's talk tomorrow I think do I see him back there to go see Magnus is talk in the dev room he'll go into more details about a subset of these I'm sure the more important ones but some of the ones that are interesting this this is the list of things having to do with partitioning that got into Postgres 11 so you can see it's quite a bit partitioning kind of became a full full fully supported feature I mean we've had it for a number of years but in 10 it became part of the grammar and but it was the first release with the partitioning and so there were a lot of things left to be done you can see a lot of that got done in Postgres eleven things like hash partitioning partition wise join the ability to create indexes that will be replicated on all the partitions lots of improvements for faster use of partition tables so in general if you have a need for partition tables with Postgres you definitely want to be on eleven and probably twelve when it comes out again parallelization parallelization first hit Postgres in nine six but it is steadily improved with each major release so some important things here to note you can do now hash joins in parallel be tree index builds in parallel queries with a pen plan nodes I mean that's kind of not easy to understand but things like Union queries that would apply to it also applies to things like partition tables limit Clause gets passed down of the worker so now if you use limit clauses and you have parallelized query it can be a lot faster because of that lots more in the area of performance push more of this push down of limits automatic pre worm how many people here are aware of PG pre-warm yeah again you guys don't count so that's an interesting extension to Postgres you can use it to basically you know when you first start up your server and your caches get warmed up by your actual queries well you can use this to basically save that warmed state and so if you need to restart your server you can start it back up and and get it back to a warm state much more quickly and that's been improved in Postgres 11 let's see what else is notable here index only bitmap scans covering indexes so covering indexes this is where you build an index on some Col but you can include other columns in there that you basically don't want to be part of the index but you want them to be in the index so that we can do a 10x only scan is that having to look at the heap so that's another significant performance boost for certain types of queries just-in-time compilation you don't want to forget that one so that was a that's a big one first released is Postgres 11 is the first release with it there's problem there's you know similar to what I was just saying with parallel query and with partitioning you know the first release of JIT is going you know it's great but it's limited in scope so I would expect to see over the next several major releases continuing refinements and improvements to the way that works logical replication now is truncations port constantly doing improvements to test coverage some authentication improvements there's a lot of stuff in this miscellaneous list you can now build if you're wanting to
build on Windows I don't personally recommend it but if you want to do it do it with Visual Studio 2017 now fewer Super User checks this is an important one Steven back there was involved in that basically a lot of things that we used to require the Super User to do are now Granta ball to normal users so that you can use the principle of least privilege you can basically just grant privileges to do specific things to specific users instead of having to give them super user to do that sequel procedures with transaction control so that this is actual stored procedures for years Postgres you know people talk about Postgres functions like they were stored procedures but they're really not the same thing stored procedures can run with their own transaction control so now you can basically build for teachers in Postgres that act more like maybe your scripts were doing before and it also puts us on a par with other database systems that have similar things see if there's anything else I wanted to note you can allow an external command for getting a passphrase for SSL key files so you can have your SSL key files and password protected basically but not have the server prompt you to type a password at startup and again some more windows support support for huge pages pg bench is the tool that comes with Postgres for doing kind of basic benchmarking there's been lots of improvements made to that over time it keeps it's like a Swiss Army knife you can do all kinds of different things now P sequel is the command-line client for Postgres the Postgres users the hardcore ones typically like the command line versus some GUI tool and consequently this tool gets a lot of love so it's it's a very good command-line tool makes very good use of red line for command line history and editing all kinds of other stuff so more improvements to that but the the big one to note here I guess I don't quite get why this was so important but a lot to a lot of people this was huge you can now just type quit or exit and an exit okay so so now I'm just going to touch a little bit on things that have either been committed since Postgres 11 branch so things that would get into 12 or are currently being worked on so in terms of things that have acted and this is a little bit old its we just are finishing up another one of these commit fests this was done at the end of the commit fest in November things were moving too fast I didn't really want to complicate the slides by trying to get it like right up to date so it's missing like the last months where the commits but in any case the git log from when we branched Postgres 11 to this point at the end of November was almost 15,000 lines so a lot of stuff kind of committed now a lot of that is bug fixes that are also getting back patched to the older stable branches so when I got went go through it I look for things that were just net new for post gross 12 so again a bunch of new performance related things that have been in included in that will be included in Postgres 12 you know some of these are pretty arcane but allow multi inserts during copy under partition table hand code string the integer conversion for performance I mean this is the kinds of things that happen in Postgres all the time I'm not even sure if I have it on the on the other slides but there's a guy right now a man Joe Gerth who's working on a whole new way to to print out floating point values because of the speed improvements that'll provide there's been a logging improvements at an application name to the connection authorized message improve auto vacuum how many people here familiar with auto vacuum how many people like auto vacuum you should actually like it of course there's some there's some improvements coming that maybe will make that potentially obsolete more improvements to peace equal again miscellaneous replication slots dropped in a single user mode can now so again this is the the procedural language hand are being able to create procedures and Postgres was new in Postgres 11 and again similar to those other features it will get better with each release so in 12 you'll be able to use polymorphic argument types with procedures which means you can create a procedure that basically will take different types of arguments integers floats or text and do the right thing at runtime as long as you design them to do the right thing one a note here if you do development in particular we're going to be started to require c99 for Postgres so we're now requiring a 20 year old compiler and in fact we don't even really in the source code we don't support all aspects of c99 but but that is required now for the compiler which means if you're going to compile on Windows you have to have MS for UC 2013 or up extensions can now install their own headers Oh another important one recovery cough has been integrated into PostgreSQL kampf so if you're used to setting up high availability or replicas of any kind with Postgres you're used to manipulating the file called recovery comm that now actually goes away there's a bunch of new functions some of these are related to the the effort to allow non super users to do more things principle of least privilege there's been settings added for control of SSL TLS protocol version there's been a few things removed I kind of doubt anyone's using these but there was a time travel extension that is gone we've these data types ABS time rel time interval and are all gone now they've been deprecated forever so hopefully you're not using them and it Postgres originally supported oh i d--'s on every table that's been deprecated for years and hasn't been the default for years and that's now and pulled out terms of ongoing discussions things that have not yet been committed probably the biggest one is this pluggable storage I was just talking to Andres who's been working on that and
sounds like there's a reasonable chance that pluggable storage itself will get committed for 12z he is one of the first new types of storage that would make use of this pluggable storage layer and talking to some people about that it sounds like Z heap will probably not make it into 12 but it is an ongoing discussion Z keep is the thing I was alluding to earlier which will I think if I understand it correctly eliminate the need for vacuum because of the way it works it works more like Oracle with undo logs which should give you shivers but there's been an ongoing discussion for a while about a feature called merge it's an often asked for a feature we've got similar functionality in in other features in Postgres already this one actually got committed for 11 and then got pulled out because there were problems with it and then I think it got committed again and pulled out again something like that just looking at these guys back here so I it sounds to me like merge is probably not going to make it into 12 but you never know what could happen in March there's other features having to do with the json standard there's been work I doubt this would get into twelve but there's been work going on now for a couple of years sort of at a low level to go to 64-bit transaction IDs which would have an impact on something called RAC transaction wraparound there were talks yesterday at the PG day in at the Marriott on database encryption I don't know whether that is likely to get into twelve but I think actually the the author of that yesterday said they hope maybe it would get into thirteen or fourteen so that's again ongoing work probably not going to make it into twelve logical replication conflict handling that would be one of the things that we need in order to make logical replication start to look more like a form of multi master synchronous logical replication alternatives to open SSL I know this is one that I'm interested in I know Stephen back there is interested in there's some discussions about and I think maybe that actually is a chance to get in to twelve global indexes Postgres with partition tables you can create an index that will automatically get created on the partitions but that's not an actual global index that spans the partitions and that's something else that people have asked for there are pluses and minuses to it they're definitely use cases where I think it makes sense autonomous transactions is another thing that gets asked for a lot I don't know where we are on that but I don't think that'll make it into twelve so again we'll be building more on logical replication we'll be doing more with foreign data work wrappers hopefully Ferndale wrappers are basically a way to call get data from an external data source Postgres foreign data wrapper which lets you connect to other Postgres servers is actually pre advanced in the way it works it does a lot of push down of things that allow the queries to be optimized in a sensible way but one of the things that it cannot do right now is it cannot actually go scan a bunch of foreign data tables asynchronously kind of in parallel so that's something that I hope will come in the not-too-distant future another favorite of mine is create variable inlining CTS is being worked on and discussed on the list right now that has implications for CT is a common table expression which is basically a with clause and the way that works right now is it's an optimization barrier it gets optimized all by itself that has implications in that that cannot then participate in parallel query so there are certain types of queries where you want that to actually kind of get rolled into the main body of the query so can get optimized and parallelized so that's that's going to be important for a subset of people and so that's that's all I have and I think I have probably about five minutes left secular questions [Applause] if anyone has a question question anyone parallel DML I use a lot of PostgreSQL also for business intelligence analytics and stuff is that parallel the ML is something that's yeah there have been discussions about parallel DML I don't know okay you guys in the back help me out on that now basically you have put all acquittee now I'm parallel DML which pound yeah yeah for inserts and updates and so on yeah well yeah it's definitely been discussed I don't think it's on the I don't think it's gonna be in 12 okay you know those okay okay hey thank you for the talk I havin a tangental question to everything you were talking about is there a good book or a resource - that describes all the new stuff that people can read and understand these things if I correct me if I'm mistaken but I think the last book for performance tuning was written for pause-press 9.0 or something like that yeah are there any plans to get us any updates thank you I know the author of that book and he's he's making he's made some noises about maybe doing an update to it as far as features go you really your best bet is to look at the detailed release notes that come out with a with a new major release and there's typically I think we do a pretty good job of providing links into the documentation so if you've kind of read through those release notes they're not that terribly long and you'll get a JIT you'll get a pretty good you know view of what the major features are and the the two or three that you're really interested in just drill in to what it says in the docs get on the the post Chris general mailing list people you know if you post a question of the mailing list you'll have an answer probably within 10 minutes so that's what I'd recommend you know that it's hard for books to keep up with this especially when we're making new releases once a year so yeah that would be the best advice out of any of our questions thank you [Applause] [Music] [Applause]