I'm Magnus Hagender. I'm here today to give a talk about what Postgres 9.4 looked like three days ago. Things have happened in those three days and I haven't updated everything. Let's take a look at them approximately. As I said, I'm Magnus Hagender. I'm a Postgres

core team member and one of the committers who has not done as much work as he should have on Postgres 9.4. Other people have pulled more of the weight this time around, but that's to be expected sometimes. I also work in Postgres Europe, which is why you'll find me a lot over at our table in the AW building if you haven't been there yet.

Come look at our merchandise and talk to us, ask questions, such things over there. When I'm not working on community Postgres, I work for a company called Red Pill Inpro in Stockholm, Sweden where we do sort of generic open-source consulting where well, kind of obviously my focus is Postgres.

So let me start by actually going back to the first line and say Postgres 9.4. How many in here are already running Postgres 9.4? That is surprisingly many. 9.3. That's nice. 9.2. 9.1.

9.0. 8.4. You guys have work to do. Postgres 8.4 is end of life in June this year. You need to upgrade by then. 8.3. Okay, you're already screwed.

7.4. Okay, that's some point there. But in particular, those of you who are on Postgres 8.4, work on it. Like, start working on it now, because come June you will no longer get

any bug fixes or any support on that product. And there are a lot of people who are still using it. So definitely look at that. But today we're going to talk about Postgres 9.4, which is somewhat newer. In fact, it's not there yet. As most of you probably know, Postgres 9.4 has not been released. It's the upcoming version. You mean planet.postgres.org?

If you've actually done that, you wouldn't have to come here. Planet.postgres.org is the Postgres blog aggregator, and there are a couple of people there who have done a lot of blogging about the new features. Mostly from a very technical perspective, but they basically take the commit message, read it and then translate it into something that other technical people can understand.

In particular, we've got Dipesh and Michael Paquier have written they've each got their own series of, I think they both call them Waking for 9.4 which is every time a new major feature goes in, gets there. So I've stolen a bunch of things from them, because that makes life easier. And of course, thanks to the guys who actually wrote all these features.

There's quite a few things coming in. But of course, where we actually are with Postgres 9.4 today, some of you may be familiar with the terms and the cycles that we use for Postgres development. We typically aim for a release once every year. We branched off Postgres 9.3 on June 14th last year

and that's when the master branch became what's now turning into 9.4 and we opened it up for development of new features. And in Postgres we work with a cycle based on what we call commitfests, which is basically, we take I guess the original idea is we take a month to do development and then we take a month to

actually manage to stick to that schedule. We're never finished in one month and they overlap and cycle, but that's sort of the basic cycle that we're working on. So we've been running four commitfests per release, typically. We've got one in June, one in September

one in November, and one that starts in January. And I think we've started all of the commitfests on time and not finished any one of them on time. This time, but that's fairly common. And in particular, the idea with the January commitfest, which is where we are right now, even though we're now in February, as you all learned when you got your mailman reminders this morning, is

the January one is expected to take more than a month, because if your patch does not get reviewed and committed in the January commitfest, you are not going into 9.4. So the status right now is we are in this final commitfest. We started it on January 14th

so we've been going it for a bit over two weeks. And we're actually making good progress when we look at the number of patches. I think almost a third of them have been handled. Of course, we've handled the third of them that's easy and fast. Many of the big ones are still pending. But the idea with this one is it continues until the end. So if you have some spare cycles

in particular, if there's stuff that you're interested in there is a web application at commitfest.postgres.org where you can get a list of the pending patches and if you're interested, download it, apply it, test it. Just applying it and making sure it builds still helps. Just post

the report to the main in this thing. I applied this patch, I tried to run the query and it segfaulted. People want to know that. That helps everybody with the development so that we can actually get 9.4 out. I always tend to do these statistics views because you can get interesting stuff out of git. We're currently

at approximately 2,000 files changed in 9.4. 81,000 insertions and 55,000 deletions. This is actually significantly lower than 9.3 which was a little bit lower than 9.2. But as we all know line count is the best possible way to measure developer productivity. Line count is important.

There are some pretty major things and also we're less than halfway into the final commitfest. There are some fairly large patches waiting. Well it shows you there's a lot of work already gone into 9.4. And of course there will be more of it. I always try, I do this talk for every version and then I try to figure out let's categorize all the things that I'm talking about into groups and then I

fail. Because it's pretty hard to actually try to find groups for them and then fit things in. One year I only managed to find two groups. I think it was performance and other. I've sort of done it so that we can go through a laundry list of some of the things that are in. Obviously we can't cover everything in just 45 minutes.

But some of the major features, some of the minor features that may make major impact as well. I've grouped them into sort of developer and SQL features and of course that's developers building against Postgres. We don't cover too much of the backend things. We've got infrastructure which is for developers who are building Postgres or who are building extensions into Postgres. So sort of

the more core level. DBA administration thing, it's all a bit blurry. And we're back to doing some replication and recovery stuff. 9.3 was a very unusual version in that we didn't actually have any major replication features in Postgres 9.3. We've fixed that now. We've added some more stuff.

But let's start at the beginning with developer and SQL features. Actually let's start by, so how many of you would consider yourself a developer using Postgres? And more of a DBA administrator in Postgres. Why are the rest of you here? What's left?

If you're not developing and you're not administering it... Marketing? So sort of developer this is like the SQL level new things. We have some interesting new sort of smaller things. There's been a bunch of work around aggregates that will help a lot of people. We allow variadic

aggregates. We don't ship with any. We've had the same discussion about other functions where we've said, we're not going to ship with them because it's really easy to get confused. But if you want to confuse yourself, go ahead. So what variadic means for those of you, basically aggregates with a variable number of arguments. Right now you have things like string

ag which takes two arguments. The field and whatever separator you want. But you can now do a variable number of arguments. There's an interesting improvement of the explain plan that's actually very useful to have, which is you get a group key in your explain plans now, which you didn't do before. So you had to look at your explain plan and go like, oh that looks like it came from, which of my group buys

did that come from? Now it will actually tell you that. Very simple things that can help you read your plans a lot more. Something that's going to help you type a lot less is that we now have support for filters in aggregates. I'm sure you've all built solutions that is better built this way. You've built solutions with lots of select something

in a case when something than zero or one and then summarize that. You can just say something like this, so count star filter where b is greater than five will then count only the rows where b is greater than five. And of course we can combine multiple of these aggregates within a single group by.

It's one of those features that seems really simple. I'm not sure why we didn't have it before, but I'm sure there's a good reason somewhere deep in the code. Very useful in particular about simplifying the kind of queries you're writing. Because it's quite often this that you actually want to do. You want to count number of approved versus unapproved entries.

Things like that. And filter will make that a lot easier. Now we also have a very big change when it comes to aggregates. So how many people in here actually know what an ordered set aggregate is? So two of you and you write books on this stuff and you review the documentation for this patch I think.

It's sort of the same. I remember when I did this presentation about lateral last year. I didn't know what lateral was. I knew what it was. I didn't know it was called lateral. And it's sort of the same thing with this. I actually knew what it was. I knew we wanted it, but I didn't know what it was called. It is in the SQL standard, yes.

So it's been there for a while, which is why I knew we wanted it. I didn't know what it was called. So this is basically a new class of aggregates that deals with things like offsets inside of your group. The key syntax is within group. And we also allow for something called hypothetical aggregates.

Some of these names are just fantastic. Hypothetical aggregates. It lets you aggregate on rows as what they would have looked like if they had been there. That sounds much better. ... ...

... So we select A and we say, mode something within group order by B. We always have to have an order by. This gives us the most common value in the group. So it gives us A and for each A the most common B. It can be quite useful.

Actually, doing that today is much harder. You have to use a window aggregate and then nest it in a CTE and things like that. The other thing you get is you get access to percentiles. So in this case you select A and select percentile comp and percentile disc

is the continuous and the discrete percentile. So it looks which row is 30% into this group. So from the beginning you move 30% into the group. Which row is there? Again, this is something you couldn't do before at all. You had to get the whole set back and then try to reduce it. The hypothetical ones

are possibly the most important ones. Select rank 4 in this case says if there was a row that was exactly this far in in percent rank, 4% in, what would it look like? Even if the row isn't there. So basically you take a range

of values and you say well I want the exact center of it. You get that even if there's no row there. Well it doesn't get you the row, it gets you the value that you would have had in that column for that row. And of course they're all shown as aggregates with a groupby. You don't have to do a groupby. You can do it over the whole table. That's the same as a groupby of nothing.

Or you can have multiple levels of groupby. And of course you can combine them. Most of these can also be used together with a window function. I'm sure that's awesome I haven't found a good example yet. But it opens up a new way of accessing things without having to read back the whole

groups of data either into your application or maybe into a stored procedure and process it. Instead you can just let the database take care of it. And that's usually a good thing. Move the problem to somebody else's problem. I mean that's the whole idea of SQL. You tell them what you want and somebody else figures out how to do it. And hopefully they do it fast.

And then we live in this imperfect world where we have to start tuning things. But in theory we shouldn't have to do that. We have significantly improved the handling of updatable views. We support partially updatable views now, which suddenly makes them much more useful. So this is the automatic updatable views.

You've always been able to do your own updatable views in Postgres by creating a bunch of rules. But in 9.3 you would get the automatic rules if your views were very simple like select from a single table. But when you added things like a join it rapidly becomes non-updatable because the system doesn't know which row

in the other table that corresponds. What you'll get in 9.4 is you'll still be able to update the rows or the columns that are from sort of the master table, even if we don't know where the other ones are. Because they're not affected by this. So we can update some columns even in the case where

some of them cannot be predicted and therefore not be automatically updatable. So again, less manual rule writing. And if you need to update the ones that you can't define which they are, well you have to start by defining which they are. There's no way the system can figure that out. We've also added something called with check option.

Again, automatically updatable views that will have the view verify the value. So in 9.3 if you have, say, a view that's select star from my table where group equals four you can actually insert into that view with a value of groups that's not four.

And then you can't get the row back through the view. Which may or may not be what you expected. If you enable with check option it won't let you do that. So if you say with check option local it will verify on that specific view saying well, you know, if you inserted group equals five I'm not going to see that row so you don't get to insert it. And we have

with check option cascade which will look at other views if you have nested views and do the same thing. You will no longer be able to insert a row that you can't see. Now sometimes you actually want to be able to insert rows that you can't see depending on how your system works and just don't specify with check option. You still have the old behavior.

Gives you the choice of both of them. In sort of the aggregate style area we've added multi-argument unnest. You're working with a lot of arrays. You unnest two arrays into a single table.

Even though they're completely independent this query will return your table of three rows. One which is AD, one which is BE and one which is CF. And obviously these doesn't have to be actual arrays. It can be sub-selects or things like that that converts through an array. And you can also combine this

with a keyword that's with ordinality. That means that for each row that's coming back from this unnest just add the row number. Seems like that would be easy but it's pretty hard to actually do that today. You have to create a temporary sequence and get the value out. And it's also quite useful

not maybe if you're just selecting this and reading it back into your application because you can count rows. I'm sure you know how to do that in your application code. But if you're joining that with something else then the ordering goes away. This way you know the original ordering. You get a generated column that you can then maybe order your final results from.

Something like that. It gets you up for that. How many of you like writing stored procedures in PLPG SQL? A couple of you. How many of you have had trouble debugging them because you don't know where in the call stack you are? Now you can have a stack trace. GetDiagnostics can now return a stack trace

using pgContext. And that will return a stack trace within PLPG SQL. It helps when you do, like, some of us like to accidentally write infinite recursion and stuff like that. You can find out where it happened. Just a simple stack trace. There are some other improvements

to PLPG SQL as well but personally I find this to be the big one that we've had so far. It's going to help. So let's go into a few of the infrastructure things that you're probably never going to use. As in, you are not going to use them. Somebody else is going to use them and give you

a nice tool. How many of you actually build other people in Europe have built a backend site extension to Postgres in C some time? I figured like five or six people in the group. Well this is for you. The rest of you can take a quick nap. We now have something we call dynamic background workers.

9.3 got background workers which is just utility processes started by Postgres but they all have well you know like we have already things like auto-vacuum. We have things like the statistics collector. These are system background workers basically. They could only be started with Postgres and if you want to change anything you have to restart

the whole system. Now you can start them dynamically. So you can start them from SQL. You can have one background worker start other background workers. This is how auto-vacuum works. So it gives you the ability to build more logic that runs basically on the server. It gets access to a bunch of these things.

Alongside with this and also very useful for these background worker things is that we now have dynamic shared memory. The main Postgres shared memory segment where we keep all of our buffer cache on those things is still not dynamic. You still have to restart the server if you want to change your cache. Sorry, not yet. We might get there. But you can now all cache shared memory on request

in these background workers. So we are actually able to communicate with each other in a much better way. This is all about building an infrastructure that we so far have nobody that's using other than example code. But once it's out there it provides an ability to write things as an extension

where you previously had to patch the Postgres source code. So hopefully people will find cool things to build. We also have a lightweight message queue that's built on top of this so you can have a message queue between your background workers for example or between a regular backend talking to your background worker. Something that will affect you

more but it's really deep down just a code change. How many in here know what snapshot now is? So snapshot now is sort of a Postgres artifact whereby when we scan and use our system tables we haven't been using MVCC the multi-version concurrency control. We've taken a bunch of locks

and we've had some really strange visibility rules for some of them. This has all now been changed and is now all using MVCC the same way as the rest of the system is. And snapshot now doesn't exist anymore so if any one of you who put your hand up has actually built an extension that uses snapshot now your extension will break as in it will no longer compile. That's intentional

because snapshot now doesn't exist. There are other ways to do similar things but we wanted them to break so that you know that they've actually changed. It allows for simpler more robust code. It allows for more code that's not specialized. There are a lot of code that's had very specialized access paths for these system relations

just to deal with snapshot now. And in the future that's an ongoing progress. It allows us to decrease the locking required for a number of DDL operations. Like less locking for an altered table. Today we still, I mean we don't lock the table itself depending on what you're doing but we still lock the system tables. This will allow us to make less locks on the system

tables and allow greater concurrency in things like DDL operations as well. Going more into the DBA and administration. So Postgres 9.3 added materialized views. How many of you are using them by now?

Not many. How many of you are not using them because you can't refresh them without locks? Now you can. You can now do a refresh materialized view concurrently. And then the view name. I always put them in the wrong order myself. I personally say that the materialized view that we had

in Postgres 9.3 were not very useful. Particularly because you couldn't refresh them without an exclusive lock. So you couldn't even read them while they were being refreshed. Refresh materialized view concurrently lets you keep doing stuff. It lets you read them while they're being refreshed. You can only do this if you have a unique index

on the view. Because that's how we merge in changes that happen while it's being refreshed. Hopefully we may at some point far in the future remove that restriction but in fairness unique indexes we usually have those somewhere anyway. And once you do that you can now refresh your materialized view

without having any locking. Without it blocking any of your other operations. You can set tablespace options when you created and didn't have this before. You can then do an alter tablespace to change it.

Now you can specify it when you create it. A possibly more useful one if you're actually in an environment where you're using a lot of tablespaces is that you can now move objects

in tablespaces not just one by one. If you had a tablespace with 100 tables and 1000 indexes you had to move them one by one if you wanted them to a different tablespace before. Now you can just say alter tablespace move all over here or move the indexes over here. Obviously you can still move the individual tables if you want to

but this will let you move all of them as one big batch. Tablespaces are cluster wide but obviously you'll need to be in the database that contains your table if you're going to move it. Otherwise we can't find out that the table exists.

Move all indexes. Because we can't see the ones that are in the other ones. We don't know that they exist if they're in a different database. We'll notice when we try to drop the tablespace and it says no there's stuff here.

You need to move it. Yes? It is single threaded. It'll be a one by one move. You can probably do it multi-threaded on your own by basically doing an alter table move but this one we'll just do sequentially one by one. Because I think about it when you have partitioned tables, no?

Yeah, it will move them one by one. We've got a small simple tool called pgprewarm if you need to prewarm your cache. There have been other tools that can do similar things

like how many of you have used pgfincore or however you're supposed to pronounce that? A couple of you. This sort of does that. You get a new function when you install this called pgprewarm where you can say for this table load it into the operating system cache or set a read ahead for the operating system cache or load it into the Postgres buffer cache.

And it'll just read the data into memory and then not do anything with it. It's fairly simple but it's something that might be useful if you're planning to do say a replication switchover. You want to make sure you don't switch over to a cold cache or if you're just bringing up a new node in a cluster you want to make sure things are in cache before you put your workload on it. Because yes, the workload is going to put

the things in the cache pretty quickly but while it's doing that it's going to be slow. So it's a fairly simple one but it can help you out in a lot of cases and unlike pgfincore it's platform independent so it works on all platforms except for the read ahead hinting which only works

if your platform has POSIXF advice which most platforms except Windows has. I think Mac also doesn't have it actually. But all the others do. We've added the ability to change configuration via SQL. You don't have to edit your config

in postgres.com anymore. You can now say alter system set and that will change it for you. What it does is it puts it in a file basically it's a separate config file so we will now have two config files for Postgres there's a postgres.conf and there's a postgres.order.conf

this tool will manage one of them. So basically it just writes them there, it overrides whatever is in that file by default overrides what you have in postgres.conf so if you run an alter system set and you change it in the postgres.conf file the alter system set will win. It does not change

it immediately so you still have to reload. Typically in this case like what you hear with select pgreload.conf which will reload whatever is in there right now. You need to have superuser to do this, yes. A regular user cannot do alter system set.

That would be bad. The regular user for workmen for example the regular user can still just do set workmen for itself but it can't change the system default. System default is superuser only. Now our contexts still apply so parameters that are set for example to require a reload

still require a reload. Parameters that require a restart still require a restart but you can't actually do the restart from SQL. So if you change for example say your shared buffer setting, you can change that with alter system set but then you still have to do a restart of the service from the operating system somehow.

And there are some tricky things. The big advantage of this one is if you for example put in an invalid value in many fields you'll know before when you just edit the config file you put in an invalid value in workmen and then you do reload and you have to go check the log files and see what happened here the alter system set will immediately give you the error.

But in particular when it comes to parameters in restart context it might not know. So you can for example set it to an invalid size but it's a valid number but it just doesn't work or it's dependent on something else that we can't see at the time then you do a restart and it still doesn't come back up. So particularly when doing alter

system set on postmaster context variables you still have to be very careful. Just as carefully as we were ... ... your shared buffers to 8 terabytes on your 8 gigabyte machine, strange things will happen. You'll also uncover bugs in the code because I used that as a test

for this. In completely unrelated parts of the code don't like shared buffers of 8 terabytes. Yes. It's in the data directory as well. So right next to the other one. Well right next to the other one unless you're on a Debian platform in which case it will still be in the data directory whereas your main one will be in the etc directory. Because you know everything else

would be way too confusing if we had everything in one place. No. It can't be the same everywhere. That's boring. Couple of new important or less important depending on your environment configuration parameters. We have a separate parameter now

auto vacuum workman. In previous versions auto vacuum has used maintenance workman. Problem being that auto vacuum can be used in multiple parallel sessions which makes this a somewhat tricky thing to configure in large installations. So you had to tune it down and then it wasn't high enough for other operations. Things like that.

It's the same thing. The functionality is the same as maintenance workman used to do for auto vacuum in previous versions. In fact if you set it to minus one which happens to be the default then the system still uses maintenance workman. But it gives you the choice to control the auto vacuum memory separately from your other maintenance workman.

We've got a new parameter called session preload libraries to load plugins. We've previously loaded plugins either with shared preload libraries which load when you restart the whole cluster or with local preload libraries which loads every time you connect

but the limit of local preload libraries is they can only be loaded from a specific plugins subdirectory because any user can load them so they can only load trusted code. This is sort of a crossing between these two. It allows you as a super user in the config file to specify a library to be loaded for every connection at connection start time.

And it allows you to load it from anywhere in the Postgres library directories. So it just sort of cross over between these two parameters. We've got a parameter called while log hints. What it does is it logs hint bit changes to the while. Did that help anyone?

That's pretty much the commit message I think. It's useful for two things. It's useful for what we call the rewind tool which are tools that will now probably be safe to use. If you want to back a replica a couple of transactions backwards in order to be able to connect it to a new master

It has still some limitations but it's at least doable. What it also gives you, it gives you a hint about what's your x log overhead going to be if you enable checksums. As of 9.3 we can do checksums, block level checksums in Postgres but you need to do it at any TV time and depending on your workload it can come with a large overhead or a small overhead. This is a good

way to actually measure the transaction log part because this parameter you can change and just restart Postgres. And it adds the same amount of data to the transaction log that the checksums would do. You will also have some more CPU overhead if you enable the actual checksums but that's usually not your problem. Your scalability problems or performance problems

tend to be related to IO. And this gives you a hint of what... How many of you use pgstat statements today? The rest of you, look at it. You should be using it. It's great. And 9.4 will make it even

better. 9.4 will expose what we call the query ID which is the actual hash value of the query. In pgstat... ...look exactly the same in pgstat statements. But they're actually different. They can be different because they have different search paths. They can be different because an object

was dropped and recreated. But you can't actually tell that from the information in pgstat statements. Now the pgstat statements could tell that because they knew they were different. It just didn't expose that information to you as the user. Now it does. So there's an internal hash value in a field called queryID which will uniquely identify this query. Now it's important

that this is not stable across versions. It's based on the parse tree as we call it. So it's based on internal state of Postgres which is not the same in 9.3 and 9.4 for a lot of these things. So you can't rely on this to be a long term stable hash number. It will also be different for the same query on a different platform.

...things like that. There's some schema modifications that will make it unstable as well. But in general it gives you a much better view and the reasoning as into why these queries

look separate. And when you're tracking queries over time using a tool somewhere gives you visibility into what it's actually doing. Let's take a look at some of the replication and recovery features. Again we're back to actually doing something in this area after having a bit of a low

in 9.3 where we focused on other things. We get a new recovery target. ...those things So it's becoming confusing to configure. I think we need to fix the interface for that soon. But right now you can set recovery target xid you can set recovery target time, recovery target name and now you can also set recovery target equals immediate

which is the only value you can actually set to the recovery target. The point of recovery target is immediate is to stop restoring your backup. You take a base backup, you go through the log file. You've had all these parameters to tell you how far to go. This parameter will tell you to go as short as possible.

Just get the system up as soon as you can. The typical use case for this is actually being able to use your nightly backup as a snapshot backup. It will recover to just midnight or whenever you took it

plus exactly as far as it needs to go to be consistent. If you just restore your base backup and nothing else your database is not consistent. We don't like that. We prefer to be consistent. So this will take you as short as possible meaning you get the system up and running as soon as possible. Maybe if you're building a reporting slave off your backups or something like that, it will get your system back as quickly as possible.

We've started logging our transactions to the WoW regular intervals even if your system is not doing noticeable amounts of work. It requires you to know about transaction snapshots from the master.

Previously we would only ship this information to the check point. The problem is if you had very long-running transactions only for example on your master, your slave

could get stuck in an inconsistent state and not actually start up. It would start up but we wouldn't let anybody connect to it. What we're doing now is basically we're just logging a list of running transactions every 15 seconds. Because if you actually are doing anything on your system, logging that every 15 seconds is probably not even going to be measurable.

And if you're not doing anything at all, we're not going to log up. If your master is completely idle, we're not going to suddenly make it non-idle. More directly useful feature, we've got time-delayed standbys so you can configure your replication slave

to intentionally be behind the master. Normally we'll try to be as close to the master as possible. In asynchronous in good cases you can talk about milliseconds after. But sometimes you might want much more. Say you might want an hour or half an hour or something like this. Why would you want that? Well it's useful when you do

something really stupid on the master, like drop an important table. Normally what you'd have to do is you'd have to go to your backup

the specific point just before you drop the table. Without having to go back and restore completely from backup. If you have a large database and you're restoring completely from backup, even though restoring with point-in-time recovery based is fairly fast, if we're talking about a couple of terabytes it still takes time. This will be much faster, so it's a way to

prevent the long downtime for doing stupid things. You probably want to use this in combination with an actual up-to-date replica for the case when the server crashes and you don't want to prevent it from doing anything. It's also worth

noticing that it keeps replaying all your normal transaction logs. It's just if you have long transactions you're not going to get a very spiky load. It's going to get reasonably even at whatever delay you set for the time delay.

Those are the main things that I was going to go through. There's always more of course. There's a huge number of small things. ... ... ...

Some of them are actually not in progress anymore. But I was at a conference yesterday and on a plane two days ago. It's updated as of me leaving the Stockholm airport. It was up to date then, but we're in the middle of a commit fest, so things happen. ... I don't think any such thing has been rejected between now and then.

Just as a note, we've got people working on triggers for foreign tables and inheritance for foreign tables. This is probably mostly useful because we use inheritance for partitioning. So it would allow you to combine partitioning and foreign tables. Which could probably let you do a lot of interesting things. We've got some fairly large

performance things that are still in the queue. We're looking at some of these reduced lockings for alter table to do that. Reduced while volume for updates, just decreasing the amount of information in the while for certain types of updates, using intelligent compression and things like that. Partial sorting, when we don't need to sort everything.

We've got something called gen index fast scan, because our gen indexes can apparently be faster than they are. I haven't entirely read up on how it does that. I think several of these are probably in fairly good shape for getting applied, because people have been working on them for quite a while.

In the backup space, we're looking at things like backup throttling, being able to make your backup go slower. To have less impact on your system while it's running. Being able to relocate tablespaces in pgBaseBackup. Right now, if you've used pgBaseBackup with multiple tablespaces, it's a really clunky interface.

You might not want your tablespace to be in exactly the same position on every node, to let you fix that. I know the last one has been applied, pgStatArchiver gives you a view into your log archiving, tells you how far along the system is, and what it does. One of those that we might get, don't get your hopes up too high, it's been

debated heavily for a long time, is what we call insert into onDuplicateKeyLogForUpdate, or as some people would like to call it, upsert. I like the longer name, because the SQL standard loves it. I don't think it's a SQL standard, the long name, I think that's just Peter, but the SQL standard has merge.

This is not merge. It's not all of it. But this is intended to cover the most common use case. Which is exactly, you should be able to insert into a table. You can do this almost today, because you can say insert into the table, but the problem is there's a race condition you insert into the table, and they say oh, there's a duplicate, then it's updated

then somebody has already deleted it. And that's definitely a race condition, that's basically what this thing is trying to solve. And you can combine this with a writable CTE to get upsert. So we will not have the statement upsert, because that's a weird statement. But we will combine it with writable CTEs and the nice keywords of onDuplicateKeyLogForUpdate.

We might get a shorter version of that. And of course the feature that everybody's waiting for, but not everybody, but a lot of people, we're hoping to see HStore version 2. How many in here are using HStore today? How many in here know of HStore but just aren't using it? The rest of you need to go read up on HStore. HStore

today is just a key value store in a single column but it's fully dynamically indexed. You don't need to create an index for every key, you can just search for anything you want. HStore 2 allows us to nest them so we can have hierarchical storage we can have a key that points to a value that is another key value store.

And it casts directly to JSON and we're looking at adding a new data type called JSONB which is binary JSON. But the thing is it maps directly on top of HStore so it could give you dynamic JSON indexing where you don't actually have to declare. Today if you want to index a JSON field you have to declare which keys

you want to index on and create one index for each of them. With JSONB and nested HStore you don't have to do that, you just say here's my JSON, just index it and give me fast access. We don't know how much of it we're going to get, we don't actually know if we're going to get any of it, but if we're lucky we get all of it. That's going to expand some really, really

good on the capabilities of storing schema-less or dynamic schema, whatever you want to call it, that sort of data, document storage, key value storage. To the point, this is the feature, some of you have probably seen it some of you have been to a Postgres event, the one in Dublin, where the guys went in the evening when everybody else went for beer

someone had shown them where MongoDB indexing was faster than Postgres so they went back and fixed that. And the fact is that this one in their benchmarks obviously this is one benchmark, but it was significantly faster than MongoDB for JSON indexing. We'll see exactly where we go The big difference between JSON and JSONB really from an interface perspective is going to be

if you store a formatted JSON in JSON today in Postgres it comes back in exactly the same format, because we actually just parse it but we store your text. JSONB will be de-parsed into pieces and then put back together when you read it back, so it will not have the same indentation or whatever that you were using, but syntactically

it will still be the same JSON. Tiny favourites. I always try to find one of those. It's like this microscopic patch of like four lines that makes some people so happy Dynamic library loading is now debug one Anyone who's used local preload libraries knows that every time someone

connects to your database it logs every library it loads. Anyone who's ever installed Postgres on Windows for example knows that the PL debugger does just this Well that stuff is now debug logging as it should be, so that's a lot less log spam for you. It's probably going to help you if you have the problem If you don't, you're just not going to be there.

Just because we're Postgres, we have a patch that now allows us to support year numbers greater than five digits in non-ISO formats Because if you were using ISO formats, we've already supported that of course, but if you were using these weird backwards American date formats or something like that, we actually only supported four year dates

Well now you can have more than four year dates Yay! Ridiculous limits, but there was no reason for the limit to be there, so now there is no limit anymore Are you positive or also negative? Both positive and negative years. It needs to be within the range of a timestamp

which is going back to, I don't remember, it's five thousand years before Christ, I think, and eight hundred thousand years in the future, something like that. So I don't think it might actually apply to negative because timestamp can't store that large numbers

Those were the features I wanted to go over. Obviously there is many more. We are currently in the middle of CF4 When that is finished, we'll put out the first beta version. If you want to spend some time building it, please go ahead and download the Git head and try it out. Let us know now what the bugs are, because there are bugs. The earlier you

let us know, the quicker we can get them fixed. If you don't want to go through that when the beta version is out, there will be packages out. There will be packages for all your major platforms whether you're on Linux or Windows, all of those will be out. Please download it run it with your workloads. Let us know if things don't work

Let us know if things are slower. Well, let us know if they're better too and test out the new features to make sure we didn't miss a corner case somewhere. And again, if you can get this testing done before the release, well then, once we get the release, you can just deploy it to production and be happy Thank you all for showing up

Any questions? Then we'll just go for fresh air

We used to have the same problem with another timing parameter. The next streaming standby delay used to have the same problem because it was defining milliseconds. But now that standby streaming delay is defining seconds, so that's long enough I don't think there's much of a use case from me and you don't have any words

No, but on the other hand, I'm not sure there's much of a use case for having this delay defining milliseconds I want the delay to be 114 milliseconds It's been no more than that. I have to know too about that I have to look at it and fix the back When you change the SC message to something

You have to restart The SC messages I know you wouldn't say that if you didn't change the SC message It's been way too long and I'll talk to you about it I'm going to talk about it, like H4 and

And C4, and all the shots I don't know what's going to happen It should be more But in my experience, it isn't If it's for getting there

It's not possible anymore H4 is actually pretty awesome even without this

So it's lots of to do for me in standardization of SQL Next There's always stuff to do, but I've given up So it's lots of to do for me in standardization of SQL I've given up on standardization for us We managed to get limited to it

And also we managed to get What was it? Period That's why it's been a period? But Peter and me managed to get a period Well we don't have a period anymore

It matches, yeah So, let's go Postgres performs for humans, not for me then H4 is compatible with the values Yes Do you need to upgrade it? No, so what it's going to be, you're going to need, the way it looks now I mean it's not committed yet, but The way it looks now is you're going to have to re-index your GIST indexes

The GIN indexes will actually be backwards compatible And the table store will be backwards compatible Ok, ok, ok I think that was one Ok

So

Ok Ok

I'm about to ask you if it's a kind of TDR problem or not Oh yeah, yeah

Ok

Do you know if the room hosts this? Do you know if the room hosts this?

Ok

Do you know if the room hosts this? Do you know if the room hosts this?

Is it? Is it?

I've got fifteen more seconds

Alrighty, hi everyone I'm Craig Kersteins I work at Heroku, hopefully