What's new in PostgreSQL 11
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.
Hagander, Magnus
PostgreSQL 11 is out! Do you know what your favorite feature is yet? This talk will outline some of the bigger new features in this new version of PostgreSQL, giving you a chance to pick your favorite feature - or to change your mind if you already had one! PostgreSQL 11 is out! Do you know what your favorite feature is yet? This talk will outline some of the bigger new features in this new version of PostgreSQL, giving you a chance to pick your favorite feature - or to change your mind if you already had one!
 So, welcome back again. Our next speaker is Mekka Sagander. He's a core team member, Postgres Committer, Postgres Major Contributor, everything about, you know, most of the things about Postgres. And now he's going to talk about Postgres 11 features. Thank you. Thank you, Debra. Now we're on, yes, technology. I'm going to say good morning even though it's, you know, noon because we consider it morning. I just want to make one more note for the record. And for Debra, before I get started, which is no. And then we're good to go. So we're, I'm here today to talk about Postgres 11. Who's already using Postgres 11? It's good. Who's already using Postgres 12? You guys are boring. So, as Debra said, my name is Magnus Hagander. I work for a company called Red Bull in Pro. We're an open source services and consultancy business in the Scandinavian area. I'm out of our Stockholm office where I work, well, primarily with databases and unsurprisingly, that means Postgres in our case. That's kind of why I'm here. Within the Postgres project, I'm one of the core team members. I'm one of the Committers on the database back end. And I do a lot of work for Postgres Europe, which is the nonprofit that coordinates a lot of these activities across Europe. For one thing, our dev room here today and the PG day that we had on Friday, more downtown. But let's talk about Postgres 11. And let's talk about the new things in Postgres 11. Some of you have seen these things before. I do this sort of the new version of Postgres talk every now and then. The format's the same. Luckily, the content is different. There are actually new things in Postgres 11. And as with all Postgres releases, we go for approximately one release per year. Targeted at release in September. What happens then is the Postgres 11 actually started in August of 2017 when we branched off Postgres version 10. At that point, we opened up the development of our master branch for what would become Postgres 11. Now in Postgres, everybody has their own names for everything. We work with something that we call commitFests, which is basically our way of doing iterative development, where we do the idea, as I should say, because we all know how well plans work. But the idea is to do one month worth of building features and then one month worth of reviewing and then committing these features. That's the commitFest. And then we just do rinse, repeat that for four times, which if you do the math, that's eight months, not 12. But then it takes a while to stabilize it and get it out to be an actual release. So we started in August of 2017 and we finished in almost September 2018. Some people says it was October, but we can pretend that it was almost September. We're only less than a month late. It's not all that bad. So the release has been out since October of 2018, which is why I guess I'm actually surprised that so many people put their hand up for that you're already running Postgres 11. That's great news. Well, I hope it's great news. I hope it actually works for you. But that's actually unusually fast. About this far into release, I would normally see fewer people who are actually on this version and more people on the previous version. Anyway, let's get into the features that we want to talk about here. I've tried to separate them up a little bit into four sections. We've got DBA and administration, SQL and developer, backup and replication, and then just sort of performance because everybody loves performance. And there's always new interesting performance things coming out. So let's start in the area of DBA and administration. First, what's the actual difference between DBA and developer today? Will DBAs do development and developers do whatever and DBAing? I just decided to split saying, well, if it's used through SQL, then it's developer. And if it's config file and command line, then it's at DBA. So let's start with a small feature or potentially big, depending on whether you need it. Those of you who worked with Postgres for a while have probably run into the fact that our transaction log or our while is made up of 16 megabyte files and they're always 16 megabytes. And if you happen to generate a large amount of transactions when you do your log archiving for backups, it sends the files off one by one. And if you generate many of these files every second, generate a few hundred megabytes in a second, that's a lot of overhead in just transmitting the files. You've actually been able to, I think from the day that transaction log was added to Postgres, change the size of these files, you just had to recompile from source and get an incompatible data directory format, which you probably didn't want. 11 will let you configure this while segment size by giving a parameter when you initialize the database. You still won't be able to change it once you've initialized your cluster, but at initial point you can set it. I would expect the normal use case for this is if you have a very high transaction rate system that generates lots of transaction log, you want this to be a higher number. You can also set it to be a lower number if you are really resource constrained and things like 32 megabytes of disk space is way too much. But most cases today when we're dealing with database servers like 30 megabytes, it's really not that much. But there could be a use case for it. The next thing I want to mention is something that can potentially break your things, potentially. Who's using the plugin PGSTAT statements today? Okay, the rest of you should really look into the plugin PGSTAT statements. It's awesome. It gives you a lot of insight into what your system is actually doing in a very useful format. Now, PGSTAT statement tracks your most frequently used SQL queries in the system across time. And it hashes them and gives you something called a query ID which uniquely identifies this query. Prior to version 11, this query ID was 32 bits. In 11, it's 64 bits. Normally, you don't really have to care, but particularly if you have a system that, for example, snapshots the data from this into a monitoring to generate, you know, cross-time graphs or something, you need to update that system as well to support the 64-bit version. It really is that simple, but there are systems out there that break. It also means if you're using one of the more common tools around this, there are, you know, web visualization tools for PGSTAT statements, you just need to upgrade them. As far as I know, they've all released updated versions that support it, but you need to actually install that version. Otherwise, it doesn't help. So we've added the ability to collect statistics on expression indexes. If you've been tweaking Postgres query tuning, you know about the command alter table set statistics because you get a better query plan. You tell Postgres, well, look at a bigger part of the data to build more detailed statistics to generate better query plans. And previously, you could only do that at the column level. So you could say, this column, we need to collect more data about this column to get better query plans, making planning more expensive trade-off for better query plans. The new thing in 11 is that you're now able to do this for expression indexes. An expression index is you create an index on, well, something that's an expression, so it's not actually a column. In this example here, created an index on x and then, you know, x, y, and z plus t. The last thing there is not a column. It's an expression. It could be a function call to do all sorts of different things. And previously, there was no way to tell Postgres to look more at that. The syntax for doing this is alter index and then alter column and you actually give it the column ordinal in the index, so the third column in the index. Because columns and indexes don't have names. They just don't have names. When you do it on a table, you'd say, alter index, alter column, my column, but since it doesn't have a name, you have to give it the number. Other than that, the statistics works the same. The default value is normally 100. You increase it, collect more data, analyze takes longer, query planning takes longer, hopefully you get a better query plan. The other big thing that we've done to indexes is we now have something called include indexes. People used to other databases will also know the term covering index, for example. The idea here is that we can add columns to our index making them less efficient. And of course everybody wants less efficient indexes, right? The idea is we do this in order to be able to use an index only scan. If you look at something like the example, I have a unique index on my table. If I have a unique index, like my primary key, then obviously that index can be used to look up things in this column that's unique. If I want to be able to look up one more column by using an index only scan, I can add this column to the index, but this becomes a problem because now the index is no longer unique on what I thought was my primary key. It is now unique across both of the columns. And the difference with include indexes is when I say something like this, I say create unique index using Btree on ID, means it's going to be a regular unique index on ID, and then I say include second field. It's going to add second field to the index for every row, but not to the key. So it's still unique only across ID, but any query referencing the ID and second field columns and no other columns can now be satisfied by an index only scan and you never have to look at the actual table. But for as a regular index, this index just became less efficient because there's more data in it, less data fits in cache. So it's a useful trade-off, but it's something to consider that the actual index does become less efficient, but the idea is trigger the index only scans. Anyone using the PG pre-worm extension? A couple of people, not too many. It comes in postgres contrib, PG pre-worm has been around for a while. The idea behind PG pre-worm is for example if you need to restart your server, you can run PG pre-worm, it'll snapshot the information about what's in your cache, because normally when you restart, your cache goes away. So you can tell pre-worm snapshot, it restart and then load it back. And the thing that we can do now in postgres 11 by setting this to automatic is we can have it just snapshot every five minutes and if it crashes or you know you get a replication failover or something like this, it will automatically load the cache as it looked up to five minutes ago, which is normally good enough. Now what it does actually, story, it doesn't dump your entire cache, so if you have 50 gigs of cache, it doesn't write 50 gigs of data, it just writes a list of which disk blocks were in the cache, not the contents of them, just the list of them. So in particular in cases like recovering from replication failover and things like that, this can be a good way of saving this terrible performance blip you usually get when no data is in the cache anymore. We've added a bunch of new default roles, this is part of the steps towards getting rid of superuser, well not really getting rid of superuser, but getting rid of the need to use superuser. So we have three new roles, PG read server files, write server files and execute server programs, they kind of tell you what they do, right, if you grant PG read server files to a user they can read any file on the server as long as the Postgres OS user has permissions on it. And if you grant PG execute server program, well they can execute programs on your database server, which may not be the greatest idea, but it depends on who you're granting it to and it's still better than superuser, because superuser can do all these things and more. So it's making these things more granular and making it possible to make it more secure installation. I think one of the most requested features in Postgres over many, many years, Ultra table add column who's ever accidentally done that in production and added the column with a not null and default and then looked at the downtime counter as the entire table was rewritten. Well you can now actually add a column, not null, with a default and it's more or less instant, does no longer need to rewrite the table. Now it does have to be a non-volatile function, you can't set things like current timestamp, because that would change over time, that would be very strange, but you can give it an actual default value and what happens is that Postgres remembers this and then as the table gets rewritten, either by you updating something else in the row, creates a new copy of the row, then it will materialize the default value into that row. So eventually it will be like slow updating across the table and eventually it will be just like it was before, but it does all of this without holding an exclusive lock and rewriting half your database and causing downtime and evil things. So that's, I'm sure everybody has made that mistake at some point, if you haven't, well you will and luckily it will make much less effect now as long as you set it up right. So let's take a look at some of the SQL side of things. Who's using Postgres full text search? Been enhanced further, in particular it doesn't actually enhance the search itself but we've previously had two TS query which takes a Postgres specific syntax, plain two TS query which takes a plain text, phrase two TS query which takes a phrase and turns these into the internal query syntax. Now we have web search two TS query which takes what you expect and turns it into a search thing. Basically it takes, you know, the syntax is that we are used to using in search engines, so you can type, you know, foo and bar, you can use parentheses to group things, you can do not, you can do all these things in like free text mode and it will parse those and turn it into a reasonable search query and then run that. So I would say almost in any case if you're taking user input and doing a search based on it which is not exactly an uncommon way of using full text search, you probably want to be using the web search two TS query and it's a drop in replacement, of course the syntax changes a bit for the user but functionality it's just passing the search term, run the search. It's kind of what you want in almost every case. The previous ones are of course there if you need the more control and if you need to be more specific about what you're doing. Postgres domains have been enhanced to basically do things that they couldn't do before, there were like little corner cases. You can now create an array of a domain and you can create a domain over a composite type so you can do a domain over an array over a row over an array over a domain, well okay don't do that but basically it's been one of those oh there is a little one box that wasn't checked of things you could do with domains and now you can do those things which is always good. On the SQL side the really biggest thing I think is full support for SQL 2011 window frame clauses, all of them. Our resident SQL standard expert has been telling me that we're actually at this point the only database that does that. Oh at least we were about half a year when I last saw him give that presentation. I don't know enough but we do add a lot of them. In particular the new added feature is the range between. Previously we could only do rows between on the window clause. So it now handles values and not just row counts and the fact that we have exclusion clauses so we can exclude current rows which can exclude ties and things like that. Of course everybody knows exactly what that means right? Okay let's try an example. If you look at the first one this is the classic the way we used to do it or which we of course still do. We do a select I in this case is a silly table with one three five seven nine etc. reference. I and then we say sum I over or to buy rows between two proceeding and two following. That's what we've been able to do before it. For each row we declare a window that's the two rows prior to this row, this row and the two rows following it and then we do a sum over those and we get a you know running average or running sum across our data. So that's our column that gives us you know nine sixteen twenty five etc. The different thing that we can do now is we can also say sum I over or to buy I range between two proceeding and two following. So instead of looking at two rows it will look at two values. So for any row if we look at for example the row five well the twenty five here is one plus three plus five plus seven plus nine. The fifteen comes from well it starts with the current row and goes backwards. It's like oh this one is two or less okay include. This one is not so stop. And then the other way that so in this case it's three five seven. Now if my data is this uniform it's actually every second value I could just say rows one before and one after if that would be smarter. But this lets you look at arbitrarily arbitrary data through that. And the exclusion thing that we can add is we can just say well also when you do this just exclude the current row. Then you get the two before and the two after but not the current. And you can say exclude ties which means you skip rows if they have the same value as the previous one. Things like that. So you can do that one of the more interesting things around this I think this is a more interesting example through it is you can use any data type in Postgres that's sortable basically that has the B3 operators as required. So in this case we're saying you know select T and then we're counting over order by T range between 15 minutes preceding and 15 minutes following. So presumably T is a time stamp. So we're saying take create a window of 15 minutes before us and after us and just run it across our entire data set and return the count. We can return any aggregates we can return sums or averages or sort of anything like that. And we get data back like this for every row you can say well at this time stamp at you know 0,013 going 15 minutes back and forth there were 12 rows in our data. And it's with use cases like this that this feature starts to really pay off and you can do really interesting analytics that previously required you to either write a stored procedure or copy all your data up into the application and just loop over it. And obviously this is going to be faster than either one of those ideas because none of those is sort of ever going to be. So basically you have the count and the over. You always have to have an order by otherwise it would be like yes please take me random data and give me a sum. That doesn't really help. You create the range 15 minutes preceding, 15 minutes following, creates the window and then it just runs the window across the data. The other big thing at the SQL level is stored procedures. And some of you have been around for a while and was like hey didn't people used to say that the reason we should use Postgres instead of MySQL is that Postgres had stored procedures and MySQL didn't. Like back in the days when they didn't. It was like yeah people kind of said that but Postgres never actually had stored procedures. Postgres had stored functions. Very similar, slightly different. You know the important thing is it uses the SQL standard syntax so you can now say call instead of select. It's really important right? It's the main thing. Now the really interesting difference between stored procedures and stored functions is that stored functions runs inside of your transactions. Stored procedures can control your transaction. You can't do rollback or commit inside a stored function. You can do rollback and commit inside a stored procedure. The syntax and how you create it is very similar to how you create a function except instead of saying create function you say create procedure. And you don't have a return value. The same value. You don't return a result set in the same way. So if you look at this we say well we say begin. We say insert value is one. Commit that. Insert value two. Rollback that. If you tried this in a stored function you would just get a syntax error saying sorry you can't do this inside of a stored function. Now what you'd expect if you write this. If you read that you go well if I call this stored procedure the table should end up with just the one. And the two would then be rolled back and gone. If you do it well that's actually exactly what happens. So it does work the way you would expect it. It gives you the ability to do full transaction control. In stored functions you could use save points. But you can't control the outside transaction that sort of runs everything. So that's also like a big thing. In particular I've noticed I do a lot of work with people who migrate from you know the big expensive proprietary databases into Postgres. And there are a lot of patterns there and a lot of in particular things like loading jobs and ETL jobs kind of things that really use this heavily. You've usually been able to accomplish the same thing in Postgres previously but this makes it a lot easier both from the migration and the actual implementation side of things. So let's take a look at back up and replication. You all do back ups right? No you don't. I mean come on really. There have been a couple of there's been no super major things but a number of incremental improvements. Postgres adds something called the ability to advance replication slots. Primarily this is not something that you will use directly. It is something that your replication management tools will be using. You know tools like Petroni or Rep Manager or all of these things around Postgres. The idea is we have these replication slots that keep track of where different nodes are in the replication stream. But if you have say you have a primary in a standby and then you do logical replication to a different machine and then you get a failover between your primary and the standby it will actually lose track of the logical replication step by default because the replication slots are gone. And what you could do previously is well then I create the replication slot on the standby as well and the logical replica will work just fine. Except now your standby will instead run out of disk space because you have a replication slot that blocks disk reuse. And there's previously no way to move this replication slot without actually replicating the data. So there were you know workarounds where let's create a replica that pipes all this data to DevNol which is not really productive. The idea is very simple you can now at regular intervals just move all your replication slots across a cluster of many machines to be in sync. They never have to be exactly in sync but they you know close enough in sync within a couple of megabytes so that you don't run out of disk space. So this makes the kind of like mixed clusters of mixing physical and logical replication a lot more manageable. Speaking of logical replication who's using logical replication in Postgres 10 or 11 today? That is surprisingly few. That was one of our headline features. Come on guys you should be using it since 10. It took a lot of work to build that. One of the things that we couldn't do in Postgres version 10 is logical replication did not replicate truncate. It would replicate your insert updates and deletes but if you actually did truncate on the primary the table would go empty on the primary but it would keep the data on the standby because the truncate wasn't replicated which is usually not what you wanted. In 11 it does. You can turn this on and off on an individual publication. You can say for this table I want truncates to replicate. For this I don't want them to replicate. Logical replication has a lot of like non-standard use cases where that actually makes perfect sense but since in most cases it doesn't make sense it will be on by default. So if you just create a publication or if you upgrade your Postgres version 10 your logical replication will just start replicating truncate. It will just start working and if you don't want that you have to explicitly go turn it off with ultra publication. Another more on the physical replication side and in particular on the backup side. Base backups taken through the Postgres internal protocol no longer include unlogged tables which makes a lot of sense. The unlogged tables are tables that don't go into the transaction log so by definition they're not crash safe but they're useful for things like large data loading job. You might load the data into an unlogged table and then do some processing and merge it into a regular table for example. An unlogged table works the way that when Postgres crashes or restarts it just wipes the contents of the unlogged table. It comes up as an empty table because it's not crash safe. We can't trust the data so we delete the data. The table is there but it's empty. And what we did for backups was we included this table in the backups then we restored this table on your restore and then as you started Postgres we deleted it again. Particularly given that these are often used for large loading tables that's kind of a waste of space in your backups if the only thing we guarantee is we're going to delete them as soon as we start up. So we no longer do that. And similar thing for temp tables which are even more apparent. Temp tables when you do create temporary table. Well if you disconnect the table gets deleted. That's how it's supposed to work. So by definition if Postgres restarts it gets deleted. If you restore from a backup it gets deleted but it used to be included in the actual backup and get deleted on the restore instead. So yeah. I've seen cases where this saves a lot of disk space in the backups that were just completely useless. The other thing that backups do now is that they will validate page level checksums as the backup runs. If you've enabled page level checksums on Postgres unfortunately this is something that is not enabled by default and you can only enable it currently on start sorry on initialization of your database clusters so when you run in a DB you have to decide to use them. You can't turn them on after the fact. You probably should turn them on in almost every single case because you want to know if your data is corrupt. Now the way that this worked previously is whenever Postgres reads a disk page you run a select query or something and reads a page from disk it validates the checksum. But if you have tables that are very infrequently read or portions of tables that are very infrequently read you can get bit rot in those files and you would never know because Postgres didn't validate the checksum. Now when we run backups we read all your data. That's kind of you have to do that. And the expensive part of validating the checksum is reading the data. Actually calculating a checksum across an 8k block is very very cheap today. So what we're doing now is just whenever we read the data for backups as soon as we read the block we validate the checksum of it. And this way because backups will include your infrequently used portions of the database. So it will then notice that your data is corrupt. Now I can't fix it for you unfortunately but it can tell you hey you have corrupt data in this table. The idea being well hopefully you still have your previous backup. And you can go restore from your previous backup and use your log archive to basically roll past the corruption and just remove the corruption. But if you only get to know it you know six months later when somebody queried the infrequently queried table you may not have a six month old backup and you may not want to restore a six month old backup. So it's all about getting the information sooner so that you can properly handle it while it doesn't cost you so much to handle it. But again checksums are not enabled by default but I do strongly encourage you to consider enabling them. We are looking at in the upcoming version of Postgres they're most likely it's not been committed yet but most likely there will be toolage available to enable and disable checksums. Unfortunately enabling checksums on a large database is very expensive because it basically has to rewrite the whole database. But disabling checksums will be very cheap. And there are actually tools you can get they're just not part of Postgres today that will safely turn off checksums on a running cluster. You just have to stop Postgres run a tool that runs in less than a second and start it again to turn it off. Which I find a good reason for well just turn them on by default because it's easy to turn them off. It is not easy to turn them back on. They do create a bit of overhead in my experience nowhere near enough overhead to be a problem versus the problem of realizing too late that you have this corruption. And if you trust your hard drives or your SAN or your cloud provider not to generate this corruption then good luck with that. Far too many people do and it can be very expensive. Okay let's talk about a few things around performance right. Everybody wants things to run faster. Almost everybody wants things to run faster. Postgres 9.6 added parallelism so the ability to run parallel query use multiple CPUs for the same query right. Up until 9.6 you could only you had the matching of one connection one CPU which becomes a problem with modern machines with hundreds of CPU cores and you're like yes you can use one. Sometimes we could use two one for a background job and one for your query. As of 9.6 we could use multiple ones but in practice it didn't really help that much because the limit it limited the number of queries like the number of types of joins the number of types of scans for example in 9.6 you couldn't parallelize a query if it involved an index scan. Postgres 10 made it really really useful because it removed most of these restrictions. I can actually say I think I'm still at a point that none of our consulting customers have actually had any use for parallelism in 9.6 and probably 80% of them have had use for parallelism in 10. So it's really that's when it became useful. Now 11 adds a few more things that makes it even better. There's a bunch of general enhancement that just sort of you know silently makes it better and you don't have to care. Now we've added something called a parallel append plan nodes. Doesn't really help you know that it just runs faster. And we have something called parallel aware hash joins whereas previously if you were running a hash join it supported parallelism but it did basically if you parallelized it across four processes in a simplified version you can say it ran four parallel hash joins they built their own hash tables they were completely independent just on different parts of the table. With the parallel aware hash joins they all share a hash table so you can get a much bigger hash table much more efficiency by still using it across multiple processes. And these are all things that you don't have to do anything about this is just like literally run 11 and your queries will be faster. That's the best kind of enhancements right. We now have the ability to do parallel create index because well create index is a DDL command that usually takes a long time if you have a lot of data and it's very often CPU bound unlike many other DDL commands which are blocked by the amount of IO you can do create index it's simple form well it loads your whole table it sorts it and then it writes it out as an index right. Obviously if it's a large table it's not quite that simple but the basic principle is that and sorting takes time sorting is expensive. So we can parallelize that we have a new parameter called max parallel maintenance workers say that fast four times. The default value is two which means that your create index will use two CPUs to run. There are definite cases particularly if you're like loading from a dump or you know big load jobs where you don't really care about the effect of the rest of the system where you will want to increase this quite a lot like eight you scale out across eight CPUs for example. Problem is of course if you run your create index across eight CPUs you probably kill everybody else on the system right. So you might not want to change that globally for everything but it's something that is worth considering. Now unfortunately parallel create index only works for B3 indexes. The most expensive index I think we normally create today in postgres is for example post GIS geographical indexes. They can't be parallelized well yet but they can't be parallelized in 11. Hopefully we'll get that sorted but it's not there yet. So parallel query was the big headline feature of postgres 9.6 right and it made really useful in 10. The big headline feature in postgres 10 was partitioning and in my view sort of just like parallel query was not really that useful in 9.6 partitioning is not really that useful in 10 but it builds a lot of important infrastructure. And 11 for partitioning is like 10 for parallelism it starts delivering like now is when you get all the benefits. There were a lot of things declared of partitioning with a syntax and basic functionality was in 10 so if you were using the previous sort of manual partitioning and you're on 10 it's definitely worth migrating to this one but it fundamentally works exactly the same as before just with prettier syntax but it has all the same drawbacks almost. It fixed like one thing. Now 11 fixes a bunch of things. Number one it adds support for default partitions. In postgres 10 if you added partitions and you try to insert a row that didn't match anything you would just get an error. In 11 you can add a partition like this you know partition of the default and every row that doesn't match anything else will go here. One of the absolute biggest things is if you did in previous versions if you didn't update on a row so that the partitioning key changed so that it would end up in a different partition it would just fail. You can do that. Once you put a row into a partition it would have to stay in that partition. In 11 you can just update it it will get moved to the other partition it will just work. What you had to do previously was basically manually delete it and then reinsert it and then it would work but that comes with other like it's a lot more work it's tricky and the whole point of partitioning is that it's supposed to be transparent. It's supposed to look like one table to your application while actually being a hundred tables underneath and having to not do update is not really transparent. It's still not perfectly transparent but it's much closer. Now you can still get some concurrency issues because in practice what it does is it's deleting then an insert but they're not as bad as they were before but they're crude in theory you could do the delete and then somebody else does the same thing at the same time and things like that. Another limitation and advantage is that you had no way to actually once you partitioned your table you couldn't create indexes across the whole thing. You would have to create indexes individually in each partition. 11 adds what we call local partitioned indexes which is you can create an index on your master table which will then automatically get created across all of your partitions and most importantly if you create a new partition it will also get the index. So it keeps going. Now you can still add individual partition indexes as well because maybe you know if you have time-based data your old partitions can afford to have a few more indexes because they're mostly read only so you can still have them but you can create these indexes that will spread out to all of them including future ones. And you can actually now create a cross-partition unique constraint as long as the old partition keys are part of the constraint because then postgres can know that this is actually unique across all of the partitions by the fact that it is also unique on the individual ones. Now this is the foundation for being able to have a foreign key pointing to a partition table. Unfortunately we can't do that yet. We're hoping to have that. It's a patch that's being reviewed for postgres version 12 and just a couple of days ago I think we heard the report saying it's most likely going to be in there but it's not there yet so no promises. Other things around the partitioning insert on conflict use that the the absurd method insert on conflict do update or insert on conflict do nothing it didn't work on partitioning now it does. Okay that's good. That's also one of those it's not transparent if some things don't work. Another really big thing is we have a new and better way of doing partition pruning. Partition pruning is when the query planner and executor figures out that oh I only need to look at one of your hundred partitions thus things run faster. That's one of the main reasons we do partitioning right. The way that this used to work is that postgres did this in the planner. So when you submit your query it would look at the query it would look at the parameters to the query and based on that data it would figure out which partitions to scan. And there were a lot of cases where that could fail for example if you had a query you know where x equals or where x in and put a sub query there then the planner has no way of knowing what the sub query is going to return so it will scan all partitions and that defeats a lot of point. The way the 11 does it is it does the old pruning first so it looks at the query and deletes it removes all the partitions it can but then it runs a second run of pruning once it's in the executor and it can run the sub query it can get oh I got these numbers back and then it run a second pass and deletes a new bunch of partitions. So there's a lot of types of queries that would previously just give you a scan across every partition that will now just scan the partitions that you need. Which again is one of the main reasons that we use partitions right it's to be able to scan them and to be able to delete them. We've added hash partitioning previously we had list partition and range partition now you can say hash partition which is the idea you pick a column you say I want this in four partitions have the system distributed evenly between these partitions as good as evenly becomes based on the hash value but if you have a good hash function it will be pretty good. So in this case previously you would say partition by hash of i instead of partition by list or range and then you still have to create the partitions so here I say we'll create this partition for values with modules four remainder zero then you get a modules four remainder one two and three those are four partitions and you can sub partition it as long as you keep the ratios workable and get into more of those. And this is for just sort of if you just want to distribute your data maybe across multiple storage devices or something like that you can put your partitions across them. We've added partition wise joins which is basically if you have two partition tables that are partitioned on the same key and you join on that key then you can actually execute that query as joining partitions individually to each other instead of just doing one partition across all the others and one partition across all the others. It has to be a join on the complete partition key but if you do that the planner can figure out and see that okay this is the special case where you can run these as many simple operations instead of one more complicated operation. This is turned off by default so there's a configuration like enable partition wise joins equals false by default and the reason for that is that it makes planning of your queries on partition tables more expensive like there's a cost involved in figuring this out. But if you have a workload where you know that you're actually doing this you're joining tables that are partitioned on the same keys then you should definitely consider turning this on for those particular sessions by you know just issuing the set enable partition wise join equals on or true or one or you know whichever one you prefer. So that's one of the few of these that you actually have to turn on. Most of the other things are just there. We've added something called partition wise aggregates. So again if you do a group by and you group by on your partition key or you know part of the partition key sorry the partition key is part of your group by then you can actually run the group on the individual partition and bring up the summary values and then run it again on those thereby making the scan much more scalable. For example I mean the sum of a bunch of rows is the same thing as the sum of the sums. Then it's based on that as long as you have the partition key. There is some other sort of general performance. The particularly big thing you'll see here is Postgres now has jit compilation of expressions and jit compilations of what we call tuple deforming and forming which of course everybody knows exactly what it means right. It uses LLVM. So the availability of this actually depends on your package Postgres. What's normal is you may need to install an extra package. For example if you're using the yum distribution on Red Hat or CentOS there's a separate package that you install on top of your Postgres that just enables this. And what it does is for example it does expression in lining which is if you run like big data warehouse inquiries the way the Postgres works is you run the expressions normally in software and it's not super optimized for the CPU because it can't be because we have all sorts of interesting data types. And what it'll do is when the cost goes above a certain cost it will trigger and have LLVM by compile this particular expression you know take this column plus this column divided by this column and turn that into machine code and then run your query with that which you know if you're processing a few hundred million rows or something that can be massively much faster. You've seen benchmark of you know ridiculous numbers like 90 percent. That's obviously a constructed benchmark. There are also cases where it will be slower because it takes longer to do the compilation than it takes to run the query itself. The system tries to only enable it when it's beneficial it will not always succeed. There's a tuning parameter that you can tune how high should the cost be before we turn on JIT. But particularly if you are running like data warehousing or something like that something in the process is large amounts of data this go look at installing those packages it will definitely make things faster. If what you're doing is simple OLTP you're never looking at more than two rows at a time it's probably not going to make a fundamental difference. It's for the cases where you run a lot of expressions fast. So that's a lot of features right. Then we probably covered I don't know five percent but hopefully we covered all the big ones. There's always going to be a lot more things right. There's a lot of smaller fixes there are a lot of performance improvements that we can't go through. If there are so usually say if there's any of the Postgres developers in the room and I didn't mention your feature I'm sorry. I'm sure it's important because all the features are important they're important to somebody they're important to somewhere. So please help. We do still need your help. Postgres 11 has been released but we still need your help out here to download and test it and run it. And some of you are already that's awesome. Now you need to go test the new features that you maybe didn't know existed. Download your packages run your application and let us know. We're still very much interested in feedback on things like in particular you know oh in this workload you know JIT made things lower or you know the parallelism doesn't actually work and things like that. We're we're we believe we're in pretty good shape on the fact that we still deliver the correct responses but there can definitely be you know performance regressions under certain workloads where we may need to tune default values of parameters and things like that. And if we get that feedback in early it makes it easier. You don't have a version of Postgres 12 to test on yet but feedback that you give us on Postgres 11 now is going to make it into Postgres 12 which you're going to see in about six months most likely development ongoing. We hope to have it ready in let's call it September. And we'll see what happens. We hope to have it ready this year. We all know you know time plans and things. So that's all I wanted to mention. I want to thank you very much for showing up and staying awake on the second day of FALSTEM. That's pretty impressive. If you have any further questions I think we have like three minutes or so before we have to get out and after that feel free to just grab me outside for any further questions. But anyone? Yes. Is it on? Yeah. I believe you said for the partitioning that there is now a feature where it will run a sub query first and then re-plan based on the results. So that is not entirely correct. It will not re-plan things based on the result. It will re-run the partition pruning. So the only part that runs is the whole oh I know that I don't need to scan these partitions over here because based on the things that came up I know all my data is over here. It doesn't actually change the query plan. Oh that's a shame. I see a lot of cases where that might help actually. That would definitely be an interesting addition sort of generic re-planning when you start running a query and realize oh this is not good but that's not something we have today. No. Sorry. Anyone else? Any questions? We have one in here. How should we take care of indexing when we use window range operations? Do we preferably have an index on the field that is used for the ordering as I guess but do we have other things to take care about? You mean for the window query? Yeah. What you presented earlier. It's really the same. What it's running underneath is you know it's select with an order by. So you need the same kind of indexing considerations as you would if you just did that select with an order by. Okay. You may also have an outside where clause and then combine it with the order by for an index like that but at the bottom it's the same kind of indexes that you would have. So existing indexes would be used efficiently for this kind of operation. We don't have anything on top to take care of. Exactly. Existing indexes will absolutely be used. Now of course if you run it across your entire table it's probably not going to use the index but it didn't do that before either. Yeah. Because that becomes too expensive but it's the same considerations as you had for sorting that kind of data before. Okay. Thank you. Okay. David, run. Okay. Thank you for trans action and procedures. Now we can build the actual process without any third part system and I would like to ask about small thing calculated field to have calculated field and table in tables like in the mass square two have a indexes niece in this field and that's that so we think like. I think that the standard goes into the generated column where you define that this column is going to be x plus y. We don't have that. What you can do in post-codes, you can create an index on the expression. And that will be used if you're querying it. And then you can create a view on top so that you read it through the view. But there's no way to materialize it into the table unless you want to write the trigger. Thank you. Thank you. I think it's time to kick everybody out and change the next look. Thank you very much.
 So, welcome back again. Our next speaker is Mekka Sagander. He's a core team member, Postgres  Committer, Postgres Major Contributor, everything about, you know, most of the things about  Postgres. And now he's going to talk about Postgres 11 features. Thank you.  Thank you, Debra. Now we're on, yes, technology. I'm going to say good morning even though it's,  you know, noon because we consider it morning. I just want to make one more note for the record.  And for Debra, before I get started, which is no. And then we're good to go. So we're,  I'm here today to talk about Postgres 11. Who's already using Postgres 11? It's good.  Who's already using Postgres 12? You guys are boring. So, as Debra said, my name is Magnus  Hagander. I work for a company called Red Bull in Pro. We're an open source services and  consultancy business in the Scandinavian area. I'm out of our Stockholm office where  I work, well, primarily with databases and unsurprisingly, that means Postgres in our  case. That's kind of why I'm here. Within the Postgres project, I'm one of the core  team members. I'm one of the Committers on the database back end. And I do a lot of work  for Postgres Europe, which is the nonprofit that coordinates a lot of these activities  across Europe. For one thing, our dev room here today and the PG day that we had on Friday,  more downtown. But let's talk about Postgres 11. And let's talk about the new things in  Postgres 11. Some of you have seen these things before. I do this sort of the new version  of Postgres talk every now and then. The format's the same. Luckily, the content is different.  There are actually new things in Postgres 11. And as with all Postgres releases, we go for  approximately one release per year. Targeted at release in September. What happens then  is the Postgres 11 actually started in August of 2017 when we branched off Postgres version  10. At that point, we opened up the development of our master branch for what would become  Postgres 11. Now in Postgres, everybody has their own names for everything. We work with  something that we call commitFests, which is basically our way of doing iterative development,  where we do the idea, as I should say, because we all know how well plans work. But the idea  is to do one month worth of building features and then one month worth of reviewing and  then committing these features. That's the commitFest. And then we just do rinse, repeat  that for four times, which if you do the math, that's eight months, not 12. But then it takes  a while to stabilize it and get it out to be an actual release. So we started in August  of 2017 and we finished in almost September 2018. Some people says it was October, but  we can pretend that it was almost September. We're only less than a month late. It's not  all that bad. So the release has been out since October of 2018, which is why I guess  I'm actually surprised that so many people put their hand up for that you're already  running Postgres 11. That's great news. Well, I hope it's great news. I hope it actually  works for you. But that's actually unusually fast. About this far into release, I would  normally see fewer people who are actually on this version and more people on the previous  version. Anyway, let's get into the features that we want to talk about here. I've tried  to separate them up a little bit into four sections. We've got DBA and administration,  SQL and developer, backup and replication, and then just sort of performance because  everybody loves performance. And there's always new interesting performance things coming  out. So let's start in the area of DBA and administration. First, what's the actual difference  between DBA and developer today? Will DBAs do development and developers do whatever  and DBAing? I just decided to split saying, well, if it's used through SQL, then it's  developer. And if it's config file and command line, then it's at DBA. So let's start with  a small feature or potentially big, depending on whether you need it. Those of you who worked  with Postgres for a while have probably run into the fact that our transaction log or  our while is made up of 16 megabyte files and they're always 16 megabytes. And if you  happen to generate a large amount of transactions when you do your log archiving for backups,  it sends the files off one by one. And if you generate many of these files every second,  generate a few hundred megabytes in a second, that's a lot of overhead in just transmitting  the files. You've actually been able to, I think from the day that transaction log was  added to Postgres, change the size of these files, you just had to recompile from source  and get an incompatible data directory format, which you probably didn't want. 11 will let  you configure this while segment size by giving a parameter when you initialize the database.  You still won't be able to change it once you've initialized your cluster, but at initial  point you can set it. I would expect the normal use case for this is if you have a very high  transaction rate system that generates lots of transaction log, you want this to be a  higher number. You can also set it to be a lower number if you are really resource constrained  and things like 32 megabytes of disk space is way too much. But most cases today when  we're dealing with database servers like 30 megabytes, it's really not that much. But  there could be a use case for it. The next thing I want to mention is something that  can potentially break your things, potentially. Who's using the plugin PGSTAT statements today?  Okay, the rest of you should really look into the plugin PGSTAT statements. It's awesome.  It gives you a lot of insight into what your system is actually doing in a very useful  format. Now, PGSTAT statement tracks your most frequently used SQL queries in the system  across time. And it hashes them and gives you something called a query ID which uniquely  identifies this query. Prior to version 11, this query ID was 32 bits. In 11, it's 64  bits. Normally, you don't really have to care, but particularly if you have a system that,  for example, snapshots the data from this into a monitoring to generate, you know, cross-time  graphs or something, you need to update that system as well to support the 64-bit version.  It really is that simple, but there are systems out there that break. It also means if you're  using one of the more common tools around this, there are, you know, web visualization  tools for PGSTAT statements, you just need to upgrade them. As far as I know, they've  all released updated versions that support it, but you need to actually install that  version. Otherwise, it doesn't help.  So we've added the ability to collect statistics on expression indexes. If you've been tweaking  Postgres query tuning, you know about the command alter table set statistics because  you get a better query plan. You tell Postgres, well, look at a bigger part of the data to  build more detailed statistics to generate better query plans. And previously, you could  only do that at the column level. So you could say, this column, we need to collect more  data about this column to get better query plans, making planning more expensive trade-off  for better query plans. The new thing in 11 is that you're now able to do this for expression  indexes. An expression index is you create an index on, well, something that's an expression,  so it's not actually a column. In this example here, created an index on x and then, you  know, x, y, and z plus t. The last thing there is not a column. It's an expression. It could  be a function call to do all sorts of different things. And previously, there was no way to  tell Postgres to look more at that. The syntax for doing this is alter index and then alter  column and you actually give it the column ordinal in the index, so the third column  in the index. Because columns and indexes don't have names. They just don't have names.  When you do it on a table, you'd say, alter index, alter column, my column, but since  it doesn't have a name, you have to give it the number. Other than that, the statistics  works the same. The default value is normally 100. You increase it, collect more data, analyze  takes longer, query planning takes longer, hopefully you get a better query plan.  The other big thing that we've done to indexes is we now have something called include indexes.  People used to other databases will also know the term covering index, for example. The  idea here is that we can add columns to our index making them less efficient. And of course  everybody wants less efficient indexes, right? The idea is we do this in order to be able  to use an index only scan. If you look at something like the example, I have a unique  index on my table. If I have a unique index, like my primary key, then obviously that index  can be used to look up things in this column that's unique. If I want to be able to look  up one more column by using an index only scan, I can add this column to the index,  but this becomes a problem because now the index is no longer unique on what I thought  was my primary key. It is now unique across both of the columns.  And the difference with include indexes is when I say something like this, I say create  unique index using Btree on ID, means it's going to be a regular unique index on ID,  and then I say include second field. It's going to add second field to the index for  every row, but not to the key. So it's still unique only across ID, but any query referencing  the ID and second field columns and no other columns can now be satisfied by an index only  scan and you never have to look at the actual table. But for as a regular index, this index  just became less efficient because there's more data in it, less data fits in cache.  So it's a useful trade-off, but it's something to consider that the actual index does become  less efficient, but the idea is trigger the index only scans.  Anyone using the PG pre-worm extension? A couple of people, not too many. It comes in  postgres contrib, PG pre-worm has been around for a while. The idea behind PG pre-worm is  for example if you need to restart your server, you can run PG pre-worm, it'll snapshot the  information about what's in your cache, because normally when you restart, your cache goes  away. So you can tell pre-worm snapshot, it restart and then load it back. And the thing  that we can do now in postgres 11 by setting this to automatic is we can have it just snapshot  every five minutes and if it crashes or you know you get a replication failover or something  like this, it will automatically load the cache as it looked up to five minutes ago,  which is normally good enough. Now what it does actually, story, it doesn't dump your  entire cache, so if you have 50 gigs of cache, it doesn't write 50 gigs of data, it just  writes a list of which disk blocks were in the cache, not the contents of them, just  the list of them. So in particular in cases like recovering from replication failover  and things like that, this can be a good way of saving this terrible performance blip you  usually get when no data is in the cache anymore. We've added a bunch of new default roles,  this is part of the steps towards getting rid of superuser, well not really getting  rid of superuser, but getting rid of the need to use superuser. So we have three new roles,  PG read server files, write server files and execute server programs, they kind of tell  you what they do, right, if you grant PG read server files to a user they can read any file  on the server as long as the Postgres OS user has permissions on it. And if you grant PG  execute server program, well they can execute programs on your database server, which may  not be the greatest idea, but it depends on who you're granting it to and it's still better  than superuser, because superuser can do all these things and more. So it's making these  things more granular and making it possible to make it more secure installation. I think  one of the most requested features in Postgres over many, many years, Ultra table add column  who's ever accidentally done that in production and added the column with a not null and default  and then looked at the downtime counter as the entire table was rewritten. Well you can  now actually add a column, not null, with a default and it's more or less instant, does  no longer need to rewrite the table. Now it does have to be a non-volatile function,  you can't set things like current timestamp, because that would change over time, that  would be very strange, but you can give it an actual default value and what happens is  that Postgres remembers this and then as the table gets rewritten, either by you updating  something else in the row, creates a new copy of the row, then it will materialize the default  value into that row. So eventually it will be like slow updating across the table and  eventually it will be just like it was before, but it does all of this without holding an  exclusive lock and rewriting half your database and causing downtime and evil things. So that's,  I'm sure everybody has made that mistake at some point, if you haven't, well you will  and luckily it will make much less effect now as long as you set it up right.  So let's take a look at some of the SQL side of things. Who's using Postgres full text  search? Been enhanced further, in particular it doesn't actually enhance the search itself  but we've previously had two TS query which takes a Postgres specific syntax, plain two  TS query which takes a plain text, phrase two TS query which takes a phrase and turns  these into the internal query syntax. Now we have web search two TS query which takes  what you expect and turns it into a search thing. Basically it takes, you know, the syntax  is that we are used to using in search engines, so you can type, you know, foo and bar, you  can use parentheses to group things, you can do not, you can do all these things in like  free text mode and it will parse those and turn it into a reasonable search query and  then run that. So I would say almost in any case if you're taking user input and doing  a search based on it which is not exactly an uncommon way of using full text search,  you probably want to be using the web search two TS query and it's a drop in replacement,  of course the syntax changes a bit for the user but functionality it's just passing the  search term, run the search. It's kind of what you want in almost every case. The previous  ones are of course there if you need the more control and if you need to be more specific  about what you're doing. Postgres domains have been enhanced to basically do things  that they couldn't do before, there were like little corner cases. You can now create an  array of a domain and you can create a domain over a composite type so you can do a domain  over an array over a row over an array over a domain, well okay don't do that but basically  it's been one of those oh there is a little one box that wasn't checked of things you  could do with domains and now you can do those things which is always good. On the SQL side  the really biggest thing I think is full support for SQL 2011 window frame clauses, all of  them. Our resident SQL standard expert has been telling me that we're actually at this  point the only database that does that. Oh at least we were about half a year when I  last saw him give that presentation. I don't know enough but we do add a lot of them. In  particular the new added feature is the range between. Previously we could only do rows  between on the window clause. So it now handles values and not just row counts and the fact  that we have exclusion clauses so we can exclude current rows which can exclude ties and things  like that. Of course everybody knows exactly what that means right? Okay let's try an example.  If you look at the first one this is the classic the way we used to do it or which we of course  still do. We do a select I in this case is a silly table with one three five seven nine  etc. reference. I and then we say sum I over or to buy rows between two proceeding and  two following. That's what we've been able to do before it. For each row we declare a  window that's the two rows prior to this row, this row and the two rows following it and  then we do a sum over those and we get a you know running average or running sum across  our data. So that's our column that gives us you know nine sixteen twenty five etc.  The different thing that we can do now is we can also say sum I over or to buy I range  between two proceeding and two following. So instead of looking at two rows it will  look at two values. So for any row if we look at for example the row five well the twenty  five here is one plus three plus five plus seven plus nine. The fifteen comes from well  it starts with the current row and goes backwards. It's like oh this one is two or less okay  include. This one is not so stop. And then the other way that so in this case it's three  five seven. Now if my data is this uniform it's actually every second value I could just  say rows one before and one after if that would be smarter. But this lets you look at  arbitrarily arbitrary data through that. And the exclusion thing that we can add is we  can just say well also when you do this just exclude the current row. Then you get the  two before and the two after but not the current. And you can say exclude ties which means you  skip rows if they have the same value as the previous one. Things like that. So you can  do that one of the more interesting things around this I think this is a more interesting  example through it is you can use any data type in Postgres that's sortable basically  that has the B3 operators as required. So in this case we're saying you know select  T and then we're counting over order by T range between 15 minutes preceding and 15  minutes following. So presumably T is a time stamp. So we're saying take create a window  of 15 minutes before us and after us and just run it across our entire data set and return  the count. We can return any aggregates we can return sums or averages or sort of anything  like that. And we get data back like this for every row you can say well at this time  stamp at you know 0,013 going 15 minutes back and forth there were 12 rows in our data.  And it's with use cases like this that this feature starts to really pay off and you can  do really interesting analytics that previously required you to either write a stored procedure  or copy all your data up into the application and just loop over it. And obviously this  is going to be faster than either one of those ideas because none of those is sort of ever  going to be. So basically you have the count and the over. You always have to have an order  by otherwise it would be like yes please take me random data and give me a sum. That doesn't  really help. You create the range 15 minutes preceding, 15 minutes following, creates the  window and then it just runs the window across the data. The other big thing at the SQL level  is stored procedures. And some of you have been around for a while and was like hey didn't  people used to say that the reason we should use Postgres instead of MySQL is that Postgres  had stored procedures and MySQL didn't. Like back in the days when they didn't. It was  like yeah people kind of said that but Postgres never actually had stored procedures. Postgres  had stored functions. Very similar, slightly different. You know the important thing is  it uses the SQL standard syntax so you can now say call instead of select. It's really  important right? It's the main thing. Now the really interesting difference between  stored procedures and stored functions is that stored functions runs inside of your  transactions. Stored procedures can control your transaction. You can't do rollback or  commit inside a stored function. You can do rollback and commit inside a stored procedure.  The syntax and how you create it is very similar to how you create a function except instead  of saying create function you say create procedure. And you don't have a return value. The same  value. You don't return a result set in the same way. So if you look at this we say well  we say begin. We say insert value is one. Commit that. Insert value two. Rollback that.  If you tried this in a stored function you would just get a syntax error saying sorry  you can't do this inside of a stored function. Now what you'd expect if you write this. If  you read that you go well if I call this stored procedure the table should end up with just  the one. And the two would then be rolled back and gone. If you do it well that's actually  exactly what happens. So it does work the way you would expect it. It gives you the ability  to do full transaction control. In stored functions you could use save points. But you  can't control the outside transaction that sort of runs everything. So that's also like  a big thing. In particular I've noticed I do a lot of work with people who migrate from  you know the big expensive proprietary databases into Postgres. And there are a lot of patterns  there and a lot of in particular things like loading jobs and ETL jobs kind of things that  really use this heavily. You've usually been able to accomplish the same thing in Postgres  previously but this makes it a lot easier both from the migration and the actual implementation  side of things. So let's take a look at back up and replication. You all do back ups right?  No you don't. I mean come on really. There have been a couple of there's been no super  major things but a number of incremental improvements. Postgres adds something called the ability  to advance replication slots. Primarily this is not something that you will use directly.  It is something that your replication management tools will be using. You know tools like Petroni  or Rep Manager or all of these things around Postgres. The idea is we have these replication  slots that keep track of where different nodes are in the replication stream. But if you  have say you have a primary in a standby and then you do logical replication to a different  machine and then you get a failover between your primary and the standby it will actually  lose track of the logical replication step by default because the replication slots are  gone. And what you could do previously is well then I create the replication slot on  the standby as well and the logical replica will work just fine. Except now your standby  will instead run out of disk space because you have a replication slot that blocks disk  reuse. And there's previously no way to move this replication slot without actually replicating  the data. So there were you know workarounds where let's create a replica that pipes all  this data to DevNol which is not really productive. The idea is very simple you can now at regular  intervals just move all your replication slots across a cluster of many machines to be in  sync. They never have to be exactly in sync but they you know close enough in sync within  a couple of megabytes so that you don't run out of disk space. So this makes the kind  of like mixed clusters of mixing physical and logical replication a lot more manageable.  Speaking of logical replication who's using logical replication in Postgres 10 or 11 today?  That is surprisingly few. That was one of our headline features. Come on guys you should  be using it since 10. It took a lot of work to build that. One of the things that we couldn't  do in Postgres version 10 is logical replication did not replicate truncate. It would replicate  your insert updates and deletes but if you actually did truncate on the primary the table  would go empty on the primary but it would keep the data on the standby because the truncate  wasn't replicated which is usually not what you wanted. In 11 it does. You can turn this  on and off on an individual publication. You can say for this table I want truncates to  replicate. For this I don't want them to replicate. Logical replication has a lot of like non-standard  use cases where that actually makes perfect sense but since in most cases it doesn't make  sense it will be on by default. So if you just create a publication or if you upgrade your  Postgres version 10 your logical replication will just start replicating truncate. It will  just start working and if you don't want that you have to explicitly go turn it off with  ultra publication. Another more on the physical replication side and in particular on the  backup side. Base backups taken through the Postgres internal protocol no longer include  unlogged tables which makes a lot of sense. The unlogged tables are tables that don't  go into the transaction log so by definition they're not crash safe but they're useful  for things like large data loading job. You might load the data into an unlogged table  and then do some processing and merge it into a regular table for example. An unlogged  table works the way that when Postgres crashes or restarts it just wipes the contents of  the unlogged table. It comes up as an empty table because it's not crash safe. We can't  trust the data so we delete the data. The table is there but it's empty. And what we  did for backups was we included this table in the backups then we restored this table  on your restore and then as you started Postgres we deleted it again. Particularly given that  these are often used for large loading tables that's kind of a waste of space in your backups  if the only thing we guarantee is we're going to delete them as soon as we start up. So  we no longer do that. And similar thing for temp tables which are even more apparent.  Temp tables when you do create temporary table. Well if you disconnect the table gets deleted.  That's how it's supposed to work. So by definition if Postgres restarts it gets deleted. If you  restore from a backup it gets deleted but it used to be included in the actual backup  and get deleted on the restore instead. So yeah. I've seen cases where this saves a lot  of disk space in the backups that were just completely useless. The other thing that backups  do now is that they will validate page level checksums as the backup runs. If you've enabled  page level checksums on Postgres unfortunately this is something that is not enabled by default  and you can only enable it currently on start sorry on initialization of your database clusters  so when you run in a DB you have to decide to use them. You can't turn them on after  the fact. You probably should turn them on in almost every single case because you want  to know if your data is corrupt. Now the way that this worked previously is whenever Postgres  reads a disk page you run a select query or something and reads a page from disk it validates  the checksum. But if you have tables that are very infrequently read or portions of  tables that are very infrequently read you can get bit rot in those files and you would  never know because Postgres didn't validate the checksum. Now when we run backups we read  all your data. That's kind of you have to do that. And the expensive part of validating  the checksum is reading the data. Actually calculating a checksum across an 8k block  is very very cheap today. So what we're doing now is just whenever we read the data for  backups as soon as we read the block we validate the checksum of it. And this way because backups  will include your infrequently used portions of the database. So it will then notice that  your data is corrupt. Now I can't fix it for you unfortunately but it can tell you hey  you have corrupt data in this table. The idea being well hopefully you still have your previous  backup. And you can go restore from your previous backup and use your log archive to basically  roll past the corruption and just remove the corruption. But if you only get to know it  you know six months later when somebody queried the infrequently queried table you may not  have a six month old backup and you may not want to restore a six month old backup. So  it's all about getting the information sooner so that you can properly handle it while it  doesn't cost you so much to handle it. But again checksums are not enabled by default  but I do strongly encourage you to consider enabling them. We are looking at in the upcoming  version of Postgres they're most likely it's not been committed yet but most likely there  will be toolage available to enable and disable checksums. Unfortunately enabling checksums  on a large database is very expensive because it basically has to rewrite the whole database.  But disabling checksums will be very cheap. And there are actually tools you can get they're  just not part of Postgres today that will safely turn off checksums on a running cluster.  You just have to stop Postgres run a tool that runs in less than a second and start it again  to turn it off. Which I find a good reason for well just turn them on by default because  it's easy to turn them off. It is not easy to turn them back on. They do create a bit  of overhead in my experience nowhere near enough overhead to be a problem versus the  problem of realizing too late that you have this corruption. And if you trust your hard  drives or your SAN or your cloud provider not to generate this corruption then good  luck with that. Far too many people do and it can be very expensive. Okay let's talk  about a few things around performance right. Everybody wants things to run faster. Almost  everybody wants things to run faster. Postgres 9.6 added parallelism so the ability to run  parallel query use multiple CPUs for the same query right. Up until 9.6 you could only you  had the matching of one connection one CPU which becomes a problem with modern machines  with hundreds of CPU cores and you're like yes you can use one. Sometimes we could use  two one for a background job and one for your query. As of 9.6 we could use multiple ones  but in practice it didn't really help that much because the limit it limited the number  of queries like the number of types of joins the number of types of scans for example in  9.6 you couldn't parallelize a query if it involved an index scan. Postgres 10 made it  really really useful because it removed most of these restrictions. I can actually say  I think I'm still at a point that none of our consulting customers have actually had  any use for parallelism in 9.6 and probably 80% of them have had use for parallelism in  10. So it's really that's when it became useful. Now 11 adds a few more things that  makes it even better. There's a bunch of general enhancement that just sort of you know silently  makes it better and you don't have to care. Now we've added something called a parallel  append plan nodes. Doesn't really help you know that it just runs faster. And we have  something called parallel aware hash joins whereas previously if you were running a hash  join it supported parallelism but it did basically if you parallelized it across four processes  in a simplified version you can say it ran four parallel hash joins they built their own  hash tables they were completely independent just on different parts of the table. With  the parallel aware hash joins they all share a hash table so you can get a much bigger  hash table much more efficiency by still using it across multiple processes. And these are  all things that you don't have to do anything about this is just like literally run 11 and  your queries will be faster. That's the best kind of enhancements right. We now have the  ability to do parallel create index because well create index is a DDL command that usually  takes a long time if you have a lot of data and it's very often CPU bound unlike many  other DDL commands which are blocked by the amount of IO you can do create index it's  simple form well it loads your whole table it sorts it and then it writes it out as an  index right. Obviously if it's a large table it's not quite that simple but the basic principle  is that and sorting takes time sorting is expensive. So we can parallelize that we have  a new parameter called max parallel maintenance workers say that fast four times. The default  value is two which means that your create index will use two CPUs to run. There are  definite cases particularly if you're like loading from a dump or you know big load jobs  where you don't really care about the effect of the rest of the system where you will want  to increase this quite a lot like eight you scale out across eight CPUs for example. Problem  is of course if you run your create index across eight CPUs you probably kill everybody  else on the system right. So you might not want to change that globally for everything  but it's something that is worth considering. Now unfortunately parallel create index only  works for B3 indexes. The most expensive index I think we normally create today in  postgres is for example post GIS geographical indexes. They can't be parallelized well yet  but they can't be parallelized in 11. Hopefully we'll get that sorted but it's not there yet.  So parallel query was the big headline feature of postgres 9.6 right and it made really useful  in 10. The big headline feature in postgres 10 was partitioning and in my view sort of  just like parallel query was not really that useful in 9.6 partitioning is not really that  useful in 10 but it builds a lot of important infrastructure. And 11 for partitioning is  like 10 for parallelism it starts delivering like now is when you get all the benefits.  There were a lot of things declared of partitioning with a syntax and basic functionality was  in 10 so if you were using the previous sort of manual partitioning and you're on 10 it's  definitely worth migrating to this one but it fundamentally works exactly the same as  before just with prettier syntax but it has all the same drawbacks almost. It fixed like  one thing. Now 11 fixes a bunch of things. Number one it adds support for default partitions.  In postgres 10 if you added partitions and you try to insert a row that didn't match  anything you would just get an error. In 11 you can add a partition like this you know  partition of the default and every row that doesn't match anything else will go here.  One of the absolute biggest things is if you did in previous versions if you didn't update  on a row so that the partitioning key changed so that it would end up in a different partition  it would just fail. You can do that. Once you put a row into a partition it would have  to stay in that partition. In 11 you can just update it it will get moved to the other  partition it will just work. What you had to do previously was basically manually delete  it and then reinsert it and then it would work but that comes with other like it's a  lot more work it's tricky and the whole point of partitioning is that it's supposed to be  transparent. It's supposed to look like one table to your application while actually being  a hundred tables underneath and having to not do update is not really transparent. It's  still not perfectly transparent but it's much closer. Now you can still get some concurrency  issues because in practice what it does is it's deleting then an insert but they're not  as bad as they were before but they're crude in theory you could do the delete and then  somebody else does the same thing at the same time and things like that. Another limitation  and advantage is that you had no way to actually once you partitioned your table you couldn't  create indexes across the whole thing. You would have to create indexes individually  in each partition. 11 adds what we call local partitioned indexes which is you can create  an index on your master table which will then automatically get created across all of your  partitions and most importantly if you create a new partition it will also get the index.  So it keeps going. Now you can still add individual partition indexes as well because maybe you  know if you have time-based data your old partitions can afford to have a few more indexes  because they're mostly read only so you can still have them but you can create these indexes  that will spread out to all of them including future ones. And you can actually now create  a cross-partition unique constraint as long as the old partition keys are part of the  constraint because then postgres can know that this is actually unique across all of  the partitions by the fact that it is also unique on the individual ones. Now this is  the foundation for being able to have a foreign key pointing to a partition table. Unfortunately  we can't do that yet. We're hoping to have that. It's a patch that's being reviewed  for postgres version 12 and just a couple of days ago I think we heard the report saying  it's most likely going to be in there but it's not there yet so no promises. Other things  around the partitioning insert on conflict use that the the absurd method insert on conflict  do update or insert on conflict do nothing it didn't work on partitioning now it does.  Okay that's good. That's also one of those it's not transparent if some things don't  work. Another really big thing is we have a new and better way of doing partition pruning.  Partition pruning is when the query planner and executor figures out that oh I only need  to look at one of your hundred partitions thus things run faster. That's one of the  main reasons we do partitioning right. The way that this used to work is that postgres  did this in the planner. So when you submit your query it would look at the query it would  look at the parameters to the query and based on that data it would figure out which partitions  to scan. And there were a lot of cases where that could fail for example if you had a query  you know where x equals or where x in and put a sub query there then the planner has  no way of knowing what the sub query is going to return so it will scan all partitions and  that defeats a lot of point. The way the 11 does it is it does the old pruning first so  it looks at the query and deletes it removes all the partitions it can but then it runs  a second run of pruning once it's in the executor and it can run the sub query it can get oh  I got these numbers back and then it run a second pass and deletes a new bunch of partitions.  So there's a lot of types of queries that would previously just give you a scan across  every partition that will now just scan the partitions that you need. Which again is one  of the main reasons that we use partitions right it's to be able to scan them and to  be able to delete them. We've added hash partitioning previously we had list partition and range  partition now you can say hash partition which is the idea you pick a column you say I want  this in four partitions have the system distributed evenly between these partitions as good as  evenly becomes based on the hash value but if you have a good hash function it will be  pretty good. So in this case previously you would say partition by hash of i instead of  partition by list or range and then you still have to create the partitions so here I say  we'll create this partition for values with modules four remainder zero then you get a  modules four remainder one two and three those are four partitions and you can sub partition  it as long as you keep the ratios workable and get into more of those. And this is for  just sort of if you just want to distribute your data maybe across multiple storage devices  or something like that you can put your partitions across them. We've added partition wise joins  which is basically if you have two partition tables that are partitioned on the same key  and you join on that key then you can actually execute that query as joining partitions individually  to each other instead of just doing one partition across all the others and one partition across  all the others. It has to be a join on the complete partition key but if you do that  the planner can figure out and see that okay this is the special case where you can run  these as many simple operations instead of one more complicated operation. This is turned  off by default so there's a configuration like enable partition wise joins equals false  by default and the reason for that is that it makes planning of your queries on partition  tables more expensive like there's a cost involved in figuring this out. But if you  have a workload where you know that you're actually doing this you're joining tables  that are partitioned on the same keys then you should definitely consider turning this  on for those particular sessions by you know just issuing the set enable partition wise  join equals on or true or one or you know whichever one you prefer. So that's one of  the few of these that you actually have to turn on. Most of the other things are just  there. We've added something called partition wise aggregates. So again if you do a group  by and you group by on your partition key or you know part of the partition key sorry  the partition key is part of your group by then you can actually run the group on the  individual partition and bring up the summary values and then run it again on those thereby  making the scan much more scalable. For example I mean the sum of a bunch of rows is the same  thing as the sum of the sums. Then it's based on that as long as you have the partition  key. There is some other sort of general performance. The particularly big thing you'll see here  is Postgres now has jit compilation of expressions and jit compilations of what we call tuple  deforming and forming which of course everybody knows exactly what it means right. It uses  LLVM. So the availability of this actually depends on your package Postgres. What's  normal is you may need to install an extra package. For example if you're using the  yum distribution on Red Hat or CentOS there's a separate package that you install on top  of your Postgres that just enables this. And what it does is for example it does expression  in lining which is if you run like big data warehouse inquiries the way the Postgres works  is you run the expressions normally in software and it's not super optimized for the CPU because  it can't be because we have all sorts of interesting data types. And what it'll do is when the  cost goes above a certain cost it will trigger and have LLVM by compile this particular expression  you know take this column plus this column divided by this column and turn that into  machine code and then run your query with that which you know if you're processing a  few hundred million rows or something that can be massively much faster. You've seen  benchmark of you know ridiculous numbers like 90 percent. That's obviously a constructed  benchmark. There are also cases where it will be slower because it takes longer to do the  compilation than it takes to run the query itself. The system tries to only enable it  when it's beneficial it will not always succeed. There's a tuning parameter that you can tune  how high should the cost be before we turn on JIT. But particularly if you are running  like data warehousing or something like that something in the process is large amounts  of data this go look at installing those packages it will definitely make things faster. If what  you're doing is simple OLTP you're never looking at more than two rows at a time it's probably  not going to make a fundamental difference. It's for the cases where you run a lot of  expressions fast. So that's a lot of features right. Then we probably covered I don't know  five percent but hopefully we covered all the big ones. There's always going to be a  lot more things right. There's a lot of smaller fixes there are a lot of performance improvements  that we can't go through. If there are so usually say if there's any of the Postgres  developers in the room and I didn't mention your feature I'm sorry. I'm sure it's important  because all the features are important they're important to somebody they're important to  somewhere. So please help. We do still need your help. Postgres 11 has been released but  we still need your help out here to download and test it and run it. And some of you are  already that's awesome. Now you need to go test the new features that you maybe didn't  know existed. Download your packages run your application and let us know. We're still very  much interested in feedback on things like in particular you know oh in this workload  you know JIT made things lower or you know the parallelism doesn't actually work and  things like that. We're we're we believe we're in pretty good shape on the fact that we still  deliver the correct responses but there can definitely be you know performance regressions  under certain workloads where we may need to tune default values of parameters and things  like that. And if we get that feedback in early it makes it easier. You don't have a  version of Postgres 12 to test on yet but feedback that you give us on Postgres 11 now  is going to make it into Postgres 12 which you're going to see in about six months most  likely development ongoing. We hope to have it ready in let's call it September. And we'll  see what happens. We hope to have it ready this year. We all know you know time plans  and things. So that's all I wanted to mention. I want to thank you very much for showing  up and staying awake on the second day of FALSTEM. That's pretty impressive. If you  have any further questions I think we have like three minutes or so before we have to  get out and after that feel free to just grab me outside for any further questions. But anyone?  Yes. Is it on? Yeah. I believe you said for the partitioning that there is now a feature  where it will run a sub query first and then re-plan based on the results. So that is not  entirely correct. It will not re-plan things based on the result. It will re-run the partition  pruning. So the only part that runs is the whole oh I know that I don't need to scan  these partitions over here because based on the things that came up I know all my data  is over here. It doesn't actually change the query plan. Oh that's a shame. I see a lot  of cases where that might help actually. That would definitely be an interesting addition  sort of generic re-planning when you start running a query and realize oh this is not  good but that's not something we have today. No. Sorry. Anyone else? Any questions? We have  one in here. How should we take care of indexing when we use window range operations? Do we  preferably have an index on the field that is used for the ordering as I guess but do  we have other things to take care about? You mean for the window query? Yeah. What you  presented earlier. It's really the same. What it's running underneath is you know it's  select with an order by. So you need the same kind of indexing considerations as you would  if you just did that select with an order by. Okay. You may also have an outside where  clause and then combine it with the order by for an index like that but at the bottom  it's the same kind of indexes that you would have. So existing indexes would be used efficiently  for this kind of operation. We don't have anything on top to take care of. Exactly.  Existing indexes will absolutely be used. Now of course if you run it across your entire  table it's probably not going to use the index but it didn't do that before either.  Yeah. Because that becomes too expensive but it's the same considerations as you had for  sorting that kind of data before. Okay. Thank you. Okay. David, run. Okay. Thank you for  trans action and procedures. Now we can build the actual process without any third part  system and I would like to ask about small thing calculated field to have calculated  field and table in tables like in the mass square two have a indexes niece in this field  and that's that so we think like. I think that the standard goes into the generated column  where you define that this column is going to be x plus y.  We don't have that.  What you can do in post-codes, you can create an index on the expression.  And that will be used if you're querying it.  And then you can create a view on top so that you read it through the view.  But there's no way to materialize it into the table unless you want to write the trigger.  Thank you.  Thank you.  I think it's time to kick everybody out and change the next look.  Thank you very much.
What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_0_47.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_48_131.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_132_179.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_180_1775.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_1776_2795.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_2796_2915.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_2916_2963.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_2964_3263.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_3264_5795.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_5796_7067.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_7068_9383.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_9384_10955.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_10956_11387.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_11388_12779.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_12780_12827.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_12828_14051.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_14052_17015.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_17016_18563.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_18564_19619.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_19620_20003.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_20004_20387.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_20388_21887.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_21888_22115.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_22116_24047.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_24048_24923.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_24924_25943.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_25944_26039.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_26040_28127.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_28128_28679.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_28680_28727.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_28728_28787.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_28788_28847.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_28848_29615.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_29616_30983.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_30984_32291.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_32292_33299.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_33300_34451.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_34452_34859.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_34860_37607.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_37608_39047.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_39048_40775.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_40776_41615.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_41616_46607.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_46608_46847.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_46848_49859.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_49860_52343.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_52344_53363.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_53364_53699.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_53700_53987.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_53988_55655.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_55656_56819.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_56820_57959.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_57960_58019.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_58020_58391.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_58392_60383.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_60384_61895.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_61896_63803.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_63804_64835.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_64836_64895.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_64896_64943.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_64944_68063.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_68064_68111.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_68112_68219.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_68220_68315.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_68316_68843.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_68844_68891.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_68892_68963.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_68964_70655.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_70656_71699.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_71700_71747.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_71748_73415.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_73416_73751.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_73752_76091.png What_s_new_in_PostgreSQL_11_lgzXuFQ0Pbk_76092_76206.png