We've got some coupons up here at the front for it if you're interested. It's annually in September in the Baltimore, D.C. area. As most people here, we're also hiring. We're hiring pretty much everybody. About me, I'm the author of several Postgres tools,

notably pgPartMan and pgExtractor, which I've had a lot of people come up and talk to me about. I'm not a core contributor. I've done no actual work on any Postgres core code. I don't have a lot of the deep knowledge that a lot of people that have done the features talks before have,

but those people are all here, so you can feel free to go talk to them, or there are other talks about a lot of the topics I'll be covering today specifically. The biggest question is, when is it coming? Just yesterday, they had a PGCon developer meeting.

It's still a little bit up in the air. I think they're trying to target for mid to late October, and hopefully have something released this month. I couldn't find a clear answer on what that would be released this month, alpha beta. Alpha soon. Alpha soon, okay, there you go. Hopefully that happens.

Let's go right into the features. The biggest feature that most people talk about is upsert. The technical explanation of what it is there, optimistic variant of regular insertion. Basically, if you try to do an insert, it doesn't work.

Then it will do an update instead. It's similar to merge. If you talk to Peter Gagan, it's a lot better if you get his opinion on it. Let's see what it actually does. For anybody that's following along, if you're not able to see the command line from what I'm doing, if you see a little down arrow down here for the slides, that has most of the code that I'll be doing.

It will be similar to that throughout the rest of the presentation if I'm doing a demo. Just hit down if you can't see the code on the screen. The first thing to show you... This is the version that I'm running right now.

If I run into any bugs for Postgres, I challenge the developers here to go get a commit pushed out, and I'll pull it down and see if it works by the time the talk is done. Hopefully, everything is working okay.

You can see I'm actually running 9.5 right now. First little setup here. Just have a plain table. It has two rows, A and B with a primary key.

The first thing you usually try to do... Try to insert a duplicate value. You usually get your typical error there. Duplicate key error. What upsert will do for you is...

The first thing you can do is if you run into a duplicate, you just want it to do nothing. You say on conflict, do nothing. The basic format for the new upsert option revolves around this on conflict clause right here,

and what you'd like to do with what happens when a conflict occurs. Some first testing out with it. The first thing you'd normally want to do, say you just want to update the new value, but you actually have to tell it what you want it to do,

which columns you want it to handle the errors on. It's telling you you have to either give it which columns you want it to handle, or which constraint you want it to handle. We'll try to do that. Still not quite happy. One of the things that is now introduced with the insert command

is you can now alias the target table. The reason being, and I'll show you a little bit more later, there's a special tuple available for trying to get the old value, and that has a name that could have some conflict with it. To actually get the update done,

there you go. On conflict, for the sum key column, which is the primary key which we were having the error on before, what we want it to actually do is to update the sum value column and just add one to it. As you can see, I aliased the upsert table to U there and used the U value right there, and now I put the 1 there.

The other thing we can do, this is what the reason for the alias is, is because there's this special variable called excluded. If you actually want to get the original value that you were trying,

you can see we were trying to update column B or we were trying to insert a new column with value B with a value 10, but there's already a key B there, but we wanted to get the original value 10, so we used this special excluded tuple

and got the original value and put that in there. That's the reason why the alias was added, because you may have a table called excluded, so now you can alias your table and work like that. I think that's all my upserts.

Reading some of the blog posts about it, you can give the constraint name that you want to be able to capture, but it's actually better to give the column name because the constraint name could change, but typically the column name won't, so it's usually better to give the column name there.

The next new feature is row-level security. This is in addition to all the schema table and column privileges that are already there. Now you can get down to row-level security, yes?

I don't think so. I would be surprised if it did. No, as soon as it'll either succeed or it'll fail. Maybe it'll be either one or the other.

It's the same way you used to handle it before with a function and an exception block. It'll either fail or it'll succeed either way.

I'm not sure. Okay, there you go. Yes, it was. He was asking whether if something causes a trigger to happen, which trigger would fire, the on or insert trigger or the on update trigger? It depends what happened. Okay, insert trigger.

Thank you. The next new feature. By default, super users and the owner of the object will bypass the row-level security unless there's a new postgresql.conf group value.

It's actually either a session value or it's a variable on the individual tables themselves. You can set the row security on or off. If it's on, that's the normal way users and object owners will bypass it. If it's off, obviously then no row-level security at all will be enforced. If you use the force, then that forces super users and object owners

to have the same row-level policies applied to them. By default, pgdump will turn row security off. If you'd like to have that enforced for the resulting dump, there's a new option for pgdump. If you have multiple policies on a table, it just does an or on them to figure that out in the end. For creating new users and modifying role privileges,

just like there's the login, no login super user, no super user, now there's a bypass rls and no bypass rls option for setting that. As you'll see in a bit, you can see the row-level security options just by doing a slash d on the table. Steven Frost tomorrow has a specific talk all about this.

If you really want to get into the details, you can go to his talk. I can show you a brief preview of it here, though. I just set up a basic table here, and then I made three roles,

Peter, Joanna, Bill. Right now, they have all the privileges on this new table that we want them to have and on the sequences. We added just some basic client info into the table there. With no row-level security, I changed my login.

I'm now logged in as Peter, so I select from the clients table. I see everything is there. If you go ahead and add a policy on there, I'll actually see what the actual syntax is on here.

I created a new policy for the clients table. You can say the next is for what actions you want it to happen on. This for this is all, but you can do for select, insert, update, or delete, and then for which roles you want it to apply to. If you just want it to be everything, it uses the public special name that I'm using here.

The using clause is kind of like a where condition. Whenever a query is run against the table that has the row-level policy on it, it's kind of like throwing that where condition on there. Then I'll show you later the with check is actually something you want it to check on an insert or an update or delete on the table. You want it to run that check to see if it passes that check as well.

You can have both the using and the check on there. As you can see, I'm logged in as Peter now. Now when I do the same query I did before, the condition is when the account manager equals whatever the current user is.

The current user is Peter, so the account manager, all he can see is Peter. I can't make this any bigger, unfortunately. Do you need the URL to look at on your own laptop? Not for this, unfortunately, it doesn't.

Do you have a laptop there? You can go to the slides, and I have all the commands and stuff up on the slides if you want to look at it. You can see I log in as Joanna, and now all I can see is Joanna.

Now if you actually try to do an insert, I'm logged in as Peter now. If I try to log in, or if I try to insert a row with an account manager with another name, it gives a policy error back. But if I try to do the insert with Peter, then it works.

So now there's two rows for Peter there. Now we're actually going to make a new policy on here. We want to actually allow the other clients to be able to insert things

for Bill, who's the supervisor. We had the same policy we had before, but now we add a check in there to see if they're adding the account name Bill in there as well. You can see if you do a slash D on the clients, this is what the policy looks like if you look at the schema of the table itself.

So now if we try to... So I log back in as Peter again, and now you can see now that I'm actually able to insert an account name with Bill, but if you actually look at the clients, he can't see.

He can't see what he actually inserted, but he was still able to insert it. So if I actually change back to me, you can see the second row he added in there. So like I said, if you have any more questions about role level security, I highly recommend going to Stephen Frost's talk.

I believe he's the one that wrote a lot of the stuff for it too as well, so he can answer a lot of your questions. Next big feature is the checkpoint segments option is now gone. It's been replaced by these two features, min wall size and max wall size.

I thought I had a pretty good understanding that this was a good thing, and it worked well. Then I talked to Robert Treat, and now I don't know anymore. From what I understood, basically what it does is, before if you had something that was running a whole lot of walls,

the number of checkpoint segments could grow larger. This max wall size actually puts a max on the number of XLOGs that will be there, and then based on the minimum size, it tries to do a moving average to get the checkpoint done. Like I said, it will only do up to max size. If you set these two values the same, you're essentially making it what it used to be like,

but now you have a maximum size on the wall. If you couldn't hear him, he said that the max wall size default is now one gigabyte.

Wallkeep segments, that just goes through when the master is going through to clean up, the wall files it knows it no longer needs.

That's what wallkeep segments is for, is to always keep that many around. That's so the slaves will have something to replay off if they need. They're kind of not really related. One is specifically for you always want to have at least this many around. Max wall size, it will never grow larger than this.

I guess if you have the max wall size set to something pretty big, but then your wallkeep segments was set to something even bigger, I guess which one would override the other? Robert, do you know which one would override the other? They're going to add?

Okay. Okay. They add together? Okay. Next new feature. PG Rewind is a new binary tool that Postgres comes with.

Before, if you were doing a failover, you'd stop your old master, touch a recovery trigger, and then you go rebuild your old master and pray that your new master doesn't go down while you're rebuilding your old master. For large databases, you could use Rsync, but for really large ones, even that was really inefficient because Rsync has no idea of the internals of what all the Postgres files actually are.

PG Rewind actually knows what the internals of all the data files and stuff are, so it's much better able than Rsync to get things back in sync the way they should be.

Do the same steps you did before, and then hopefully things are working okay. The only thing for this is if you have an existing cluster, if you want to turn this on, you have to turn the wall hints option on, which requires a restart. Otherwise, the only other way to do this is to turn the data checksumming on,

which you have to actually do at cluster initialization, so you'd have to rebuild your entire cluster if you want to have it that way. If you're starting off new and you want this feature, I'd highly recommend turning the checksumming on. Otherwise, you can just set the wall hints on.

Other new feature. Foreign table inheritance. If any of you are familiar with the foreign data wrapper, which allows you to connect to other databases, and then the inheritance feature, which allows one table to inherit the properties of other tables, now you can combine those two things. This is essentially a very simplified version of sharding.

You have all of the other tables on different databases all coming into one. I'll show you a quick demo of what this looks like. I logged in here. I made a foreign database here real quick, and then I made these two tables on the foreign database,

and now I'm connected back to my local database here. When you're setting up a foreign data wrapper, I'm just using the Postgres foreign data wrapper here, so I made the Postgres foreign data wrapper extension.

I made the actual foreign data wrapper connection here. I'm just connecting back to localhost to the new database I made called foreign database. Then you actually have to make a user mapping so it knows what user to log in to that remote foreign data wrapper table has.

I made this log entries table here, and now I go actually create a foreign table with the normal create foreign table command, but now you can use the inherit option with the create foreign table command. You can see it did the normal inheritance stuff, merging the column entries and stuff.

Now if you look at the description of the log entries table, you can see it has two child tables now. If you look at the description of one of those child tables, you can see it's defined as a foreign data wrapper. That's all working. The other fun thing you can do along the same thing

is you can now import foreign schemas. It's not actually importing the foreign schema and making it local. You give it the foreign database connection that you want, and it makes all of the objects in that other remote table,

foreign data wrapper tables, locally. I can show you what I mean by that here. You can see all I did was import foreign schema. I just used that same foreign data wrapper I made before,

and it made these two new tables, the FDW schema. If you actually look at these two here, it made those two tables that I had made on the foreign table before.

If you have hundreds of tables on some remote system, you could easily just import them all with one command now. I'm not quite sure. I would think views would be included on that because I think they're viewed as tables as far as the foreign data wrapper is concerned.

If you have any more questions about foreign data wrappers and doing things like this, there's actually a talk by Ashutosh tomorrow called Transacting with Foreign Servers. It's tomorrow at 2 p.m. in this room.

Yes? I can't hear you. I'm sorry. No, it'll work for any foreign data wrapper that you have the connection defined for.

Oh, okay. Right. Okay. All right. Another new option. This gets pretty deep into SQL FU,

so it's hard to talk too much about it here, but essentially what you used to have to require doing union alls before to get multiple group by clauses with the same result set you can now do in a single query. I'll show you an example here.

I made this table here with a whole bunch of how many inserts, updates, and deletes in transactions per day. It's just an example table here. Before, if we wanted to get the sum of all of these inserts and stuff

per app application, we first do it by group by app number, and then if we wanted to get it by day, we'd do a separate one, and then you'd do a union all on these, and you'd have them both together in a single result. Now you can do it in one query.

You actually get the result set back like that. It's tremendously more efficient than doing the union all, so if you need to do things like this, it's a lot easier now. You just have to get used to the syntax of it. There are these other things with rollup and cube. I know one of the examples I saw with cube is say you're gripping by

and you give it a column one, column two, column three. You want and then a sum on something. If say you wanted a sum on every possible combination of all of those columns in the group by, you could use something like cube, and it would go through and do every possible combination

of all of those things you're trying to do a group by, and you'd have all those different sums in a single query. Depeze, who's followed his blog a lot, this is actually a live link if you're following on my slides. If you go to his blog post, he does a really, really long, detailed example of how to use stuff like cube and rollup, and this is where I got the other example I did from.

I recommend going to his blog on this. Another one seems pretty simple, but it's actually a pretty interesting update. You can actually now do sub-selects and updates for multiple columns. That basically sums up what it does right there.

Don't want to have to get in too much more detail on it. I don't know if people are familiar with the generateSeries function. It's one of those things that's very, very handy if you know about it, and it can now handle numeric values.

If anybody's not familiar with what generateSeries does, it's like before, if you just want to do 1 through 10, you just do generateSeries 1, 10, and you get all your numbers back. It actually can do time as well. Most people don't know that. They're just used to using it for numbers.

You can actually do it in time and give it any arbitrary increment you want there. I just did a 65-day increment for over a year. With the new one, you can now use decimal numbers. I just went from 10.1 to 11.6 by .2 and got all those results back. It's a very, very handy little function.

If you're trying to make test data and you just want to fill a table with dummy data, you can do one to a million, and you have a million row table and one command. I found this pretty handy. I always had to go do weird things to get this to actually work before.

Now, if you use arrayag, you can now actually pass arrays to arrayag. I would have thought that would have worked before, but apparently it wasn't working before. There's an array function. You just want to take a bunch of values and make an array out of them. You can now pass arrays into that, and you'll get a multidimensional array back out.

It's pretty handy. This is a really handy SQL syntax if you have to deal with a lot of contention in your tables. It's easy to show by demo here.

Actually, I have to do a little trick here. Let's have a plain table here with 100 rows in it. It's just got some demo data. There's the first 10 rows there.

Say you do something. What you do is select for update. I just did a select from jobs, and I got just one row, but I did four updates. Now I'm in a transaction. I did a begin. That row is now locked if something else tries to come along. Look at that.

I tried to get the same row before. I also tried to do a for update and did the no wait clause. Otherwise, if I didn't put the no wait on, if I would have run that command, it just would have sat there and waited forever otherwise.

With no wait, it just skipped right over it. Now if you do skip locked, this new option here at the end, you can see it actually... You got ID 47, priority 98 over here, and it got a different row back here. It just got the next one, 56, 97.

Now if I start up another session and do it again, now I just got the next one, 99, 97. Now if I go back to commit,

now I get back to the other row I got before because that transaction is open now. It makes it really handy for more cleanly handling when you have a lot of contention going on and you need to use for update.

There's been some more work on reducing lock levels for a lot of operations. The biggest one actually on here to me is this last one for adding foreign keys.

Typically when you'd add a foreign key on a table, you'd actually have that table locked completely, no writes or reads. That's now been changed to share row exclusive lock, which actually means it allows reads but blocks writes. So now you can add foreign keys and still be able to query the table. Writes are blocked, but you can still query the table for the existing values.

These other ones for create trigger, enable trigger, they typically work pretty quick already, but now it's even less intrusive. Another new type of index called a block range index. They're also called min-max indexes.

Typically in a vtree index, for every value on the table, there's one entry in the index. For this new one, every data block in Postgres, there's a heat page. It stores the upper and lower bounds of that heat page, and that's what the index entry is.

You can configure how many heat pages you want to contribute to that upper and lower bound, and that'll adjust the size of the written index. If you look at these, the size is significantly smaller than a regular index, and they're also created much faster, and if you have to update the index, it's much faster. This comes with some performance penalties, though,

for most indexes. If you have statically ordered data that doesn't change very often, this can help a lot and make your indexes a lot smaller, but if your data is updating and changing a lot, you probably actually see a performance decrease. I found a really, really good example on this blog called Python Sweetness.

He actually goes through and explains how normal vtree indexes work and the entries work, and he goes through a detailed explanation of how the BRIN indexes actually work as far as using the boundaries on the heat pages and how you can adjust them. I recommend going there if this is something you're curious about.

For those that don't know what an index-only scan is, if you're doing a query like a select count or something like that and the actual values actually exist on the index and they don't actually have to go to the table to get them, I think it was back in 9.2, they added that feature for vtree indexes.

It'll actually not have to go to the source table. You can just get the data right from the index, and that's tremendously faster. They're starting to add that to just indexes now. For things like postGIS, which usually have tremendously large amounts of data in them, this can make queries against postGIS a lot faster. It's still limited.

It's only on box and point data types right now, but hopefully they'll get more in there. Speaking of indexes, now there's a simple command if you just want to re-index an entire schema. Like a typical re-index, this will block everything that's in there and the ones indexing, but if you just want to do that really fast,

there's a convenient command for it right now. Peter Gagan talked to me about this. I would have completely looked over this. I didn't even realize it was as significant a feature as it was. There's been some significant improvement in sorting speeds,

and this affects creating indexes, but also if you ever look at your explain plan, you see a lot of sorting and stuff in there. If you have text and numeric data, which is usually the majority of people's data now, you can see a tremendous speed up in query speeds. If you ever talk to Peter,

you know what I mean by exhausted explanation. This is an extremely complex feature. The notion behind it is something called irradiated keys. He has a very long explanation of what that is and how it works. I suggest you go there. This is one of those features that most people looking through

the patch notes and stuff would completely overlook because it sounds really complicated and they don't know, but this is actually a pretty significant improvement for query performance overall. If anybody uses the unlock feature, you can now turn that off and on dynamically on tables.

Just be aware it does a complete rewrite on the table, so if you have a really large table, this will lock it for that whole time and rewrite the whole thing, but now you can dynamically turn that off and on. If you have a table you want to turn into this, it makes it really easy.

The vacuum db binary command, this isn't the vacuum command in the database. This is the binary command outside the database. Now it has a jobs option, and it will do multiple connections to the database to try to speed up your vacuums and analyzes.

Very handily, it starts with the largest table and goes down from there. Just be careful in using up all your connections and overwhelming your I.O. Just be careful with it, but it's a very handy feature. This one is a pretty handy feature.

I've never actually used this option before, so I learned about this while I was making these slides. There's a new option to pgxlogdump. Xlogdump kind of gives you a summary of what's in the wall files. Actually, I have to go out to a command line here because I'll try to make it bigger too.

I can make this bigger. Can everybody read that? Can they make it bigger?

What's that? Just give it the pgxlogdump command and the stats command there, and you give it the name of an xlog file. You can actually give it a starting one and an ending one, and it will give you the summary of the data for the whole thing.

The resolution went down here. You can see these are all the different services in Postgres. They're the resource managers. You can see the options there. The one I found that was handy though

is if you give it this stats equal record option. It will actually give you what is in there as far as inserts, updates, deletes, vacuums,

all of that kind of information. If you're wondering what's causing your xlogs folder to blow up, like what's running, and you can't get into the database to see, you can go run this command on the wall files in the xlogs folder and get an idea of what it actually is, causing all your problems.

Another handy feature, most people use a tool that compresses their wall files for them already. Don't stop using that, but if you don't have the option to do that,

you can have Postgres try to pay a little bit of CPU cost for saving IO and disk space and have them compress the wall files for you. The 9.4 introduced the alter system command,

where you can actually change the postgresql.conf options from within the database itself. If you go ahead and do that, if you look in the data directory or wherever the postgresql.conf directory, there's a secondary one that keeps track of all these. If you use this command to change the option

and you want to change it back and you weren't sure where it was before, or you just want to change everything back to the way it was, you can use all. If you change the work mem setting and you want to change it back to what you originally had it before in the original postgresql.conf, or just the default value if you didn't set it before, you have this command available to you now.

For anybody that runs slaves and does failover and point-in-time recovery, this is a new option in the recovery.conf file. You can give it one of three options, either pause, promote, or shut down. If you're bringing up a slave or failing over

or trying to do point-in-time recovery and you give it some options, you want to tell it specifically what you want it to do when it gets to the end of recovery. Promote will obviously make it your actual new master standalone database. Shut down will turn it off. Pause is handy if you're trying to see if the database is actually where you want it to be for recovery.

You could set this option in recovery.conf, start your backup, restoring your backup, and have it pause when it gets to the end or whatever your recovery target is, and then go in there and check and see what it is. If it's not what you want, you can go in and adjust your recovery target options, and then you can use the replay resume

and continue again, and you can have it pause again and just keep doing that. You will have to restart in between, though, because... Oh, really? ...the target is not... Oh, okay. All right. Oh, that's right, yeah. You have to reread the...

This is another new option. This is really handy for people that run a lot of clusters on the same system. It's a new postgresql.conf option called cluster name. So I'll show you exactly what I mean by this.

So right now, if I do... You can see all the Postgres processes that are running here right now. And if you had multiple clusters running, the only thing that would really identify what this is would be kind of this first one up here,

but you don't really know if that's which one of these other processes is associated with that one. So you can do... Do a little restart here.

Yeah, my little handy commands. So now you can see I set the cluster name to pg95live, so now it has... You can easily identify which of the background workers are associated with which cluster.

Yes? I'm not sure about that. I don't think so. I'm not sure about that, though.

Could multiple clusters have the same port? Oh, no, they wouldn't be able to have the same port. No, no, no. This is another nice little feature in there.

If you've ever done... Like, you have backups stored, and you try to feed a... I'll show you what happens here.

I'm just going to make a dump file here of my...

Using the 94 binary to make the pg dump so I can actually use the pg restore... 94 pg restore binary or psql binary.

So if you've ever done a pg restore and then tried to use psql to restore that... You get crap like that, and you have no idea what was wrong. So if you actually do it with the 95 binary...

Actually, let me get to the top there. You get that nice little handy feedback. I can't see that here.

So it just tells you this is... Use pg restore, not psql. That's a lot of the features that I know are... that are confirmed and committed in there right now.

One neat feature I saw that Robert Hoss did a blog post about that is hopefully going to make it in, but... Oh, nope, okay. So not possibly right now, but parallel sequential scans. He actually has a talk dedicated... Him and Amit have a talk dedicated to this tomorrow at 1 p.m.

If you want to go hear more about it. This sounds like it's hinting at hopefully getting towards parallelized queries at some point, but... I think we're going to make it a 96. 96? Okay. 95. All right.

So there's... Still have some... That's the end of my talk here. I just want to say... Oh, actually, first, how can you help getting this stuff out? Every year we have... There's always commit fests going on. Right now there's the 2015-06 commit fest going on right now.

There's a website you can go to. The commit fest schedule for 96 has been released, so... Hopefully get feature freeze for 96 around April next year. And of course, if you... How can you help is go... There's the git repo. You can go download, install it, compile it, test it out, just like I did here.

And also, thanks to these people, Planet Postgres is basically a Postgres news feed. If you want to keep up on new features in people's blog posts and stuff that are out there, it's a great resource. You can subscribe to it and get emails and stuff about it. Thanks to Magnus for doing this talk so I could steal it.

And Hubert de Pez and Michael Pacquier for their series that they do on their blogs for features. It helps a lot. And if you're curious how I made this presentation, it's a software called RevealJS. I've been finding it pretty handy. I still have some time left.

If there's anybody here that has another feature that they... Like any developers here with any other features that you want to share with people that are coming out that I didn't cover, I guess not. Okay. Thank you.

Oh, there was a question.