All right, hello, hello, okay great, I mean, it's like two minutes technical, I saw the time I can yeah, I just probably need hand mic for Q&A if any if any will happen Thank you so much. Now really really appreciate it Really appreciate it No, I don't no, I don't no, I don't I have my I have my finger I'm good. Thank you Yeah, yeah, okay I mean actually I can try this one. I guess Hold on. Let me see you because I tested mine. I did not test this one Now this one does not like me this one Yeah, we do not do this one in process All right, awesome All right So can everybody hear me? I assume yes, right? Thank you. So since there is no moderator today I am just starting because I always start on time. So if anybody been to my Chicago Pug PostgreSQL user group, they know I start in time even when the speaker does not show up I make a quick substitute because it's a way to keep people like, you know, organized because you know Other people need to be done and get on the train or whatever So anyway Good morning, everybody Thank you so much for coming for the first talk of the day and especially coming into this like room Which is a freezer So like I know that some sacrifice choosing between this room and the other room So I really appreciate you choosing my talk. So Today we will be talking about securing your postgres data I and I'm delighted to speak at scale For the first time in my life. So it's all new for me That's huge conference and there are people of whom I usually do not meet at the conferences. So thank you everybody Who is at my talk for the first time? So I'm happy Do not bother about last name. There is only one Haiti in postgres Google Haiti postgres. You will know Everything about me more than probably I want to know so there will be plenty of information So I'm Haiti on LinkedIn Haiti on github and Haiti everywhere So I've been doing lots of stuff with databases in general and postgres specifically And at the moment, I'm a database architect at the DRW holdings in Chicago They said they're going to change my title because nobody knows what architects does and rumors are that data Architects do not do anything which is not case in my case, but anyway at the moment. I am a database architect So more important title, which I hold longer than any of my job titles I am the local organizer of the Chicago postgres quell user group And so I'm leading this group since December 2016 and I did not check recently at some point it was second largest user group in the western hemisphere I think there are more a larger group now, but I am happy that I led this group through pandemic We actually continued to We switched on to zoom in April 2020. So I missed just one meetup and since then we are meeting both virtually and in person and during pandemic I Promise that I will always live stream my meetups when they will return to be in person and I still do. So if you are interested We are meeting once a month. So go to meet up and find Chicago postgres quell user group And I will always, you know have link to our zoom version of meetup. So even if you are not in Chicago, you can Join me virtually And another very important thing I'm doing in my life is I'm the operations part of operations committee of PJ day Chicago and That is by the way only the second PJ day in the United States I was shocked when I learned it because there are so many PJ days in Europe and So there are only two legit PJ days in the United States PJ company work and PJ day Chicago. It is happening now for the second time And so far it's a Monday conference So please support it because if I will meet the goal for this year, then hopefully next year I will be given two days conference. So I still need sponsors. I still need Participants registration is open. It's still like plenty of time If you can do even like small sponsorship, please sign up if you want to attend, please sign up I know the program is awesome. So check our website. It's like a great program Free tracks and I returned it back to Chicago loop. So at least you are not looking at I'm a gosh I don't want to look at the windows You will look at all the like best of Chicago from the windows if you are not looking at what people are presenting So please come and join me Some Why this talk So for those who know me, I'm all about performance. I'm like Performance like whatever the same performance queen, whatever I call this Russian lady who comes in like, you know, I'm like Tell tells people like how things should work. So anyway Normally I'm doing talks about performance and about my Projects I have several open source project which I love. So that is my first security talk So why I'm talking all of a sudden about security. I never did before. So before I will go with Official slide here is an official story at some point during my employment with the RW holdings I had to automate security settings, which all this talk will be about and When I started to convert databases from disaster to proper security I found so many issues with how postgres is doing security. I was like shocked.  It was like Facebook style discussion people. They came very angry people were writing a long responses to me So that was a disaster and then I like, okay I need to let the world know and I started to submit the stock and I got rejected rejected rejected So I talked to my friend that is Avro like I got rejected for the fifth time What's wrong like how I can let the world know what I think because otherwise nobody will listen to me and She said, you know what first of all, you do not call the talk What is wrong with postgres because everybody will get like upset and like, okay It is something which is wrong with postgres. So how you want me to Do this and she said, okay, you know what? Like use charge EPT. That's what I do with all my talk descriptions and you will get accepted I like no charge EPT is not me. Everybody will see it's not me. I try try so I tried So charge EPT Replies something like, oh my gosh, that's not me. No, I'm not doing it and I fought for a couple of days Okay, I will take charge EPT version. I will make it look like me and this one got accepted So that's a charge EPT plus heading. Okay. So finally, I have a chance to talk about what I wanted to talk for like, you know For a whole year. Okay. So now official version. Why I'm doing this talk We live in the time of Age of data breaches and I do not even need to Indumerate any recent ones because everybody knows Like everybody heard many of you have been like a victim of data breach. So we know, right? so and So securing that is high priority like anywhere here. We have the whole track about security by the way They didn't get they did not accept my talk postgres accepted my talk the track The security track on scale said it's not important You know why they said it's not important because they believe that only thing you need to do you need to secure your Network you need to secure it from intruders from outside from inside the moment you get into your super secured network You can do everything because now body protects data on the database And by the way postgres has everything so you look at any other database system postgres has the most like sophisticated most flexible system to provide the permissions in very Fine granularity exactly what you need postgres has it all Still you know what happens in real life not in theory not when the contributors tell us over the draw level security So you know what happened happens in real life, right?  It's interactive Who knows what happens in real life? No, that's what happens in real life like Like production applications, you know what my internal customers come to me We need to install this application. We just paid tons of money I'm like I cannot install it because it's like this, but yeah, that's they require they require super user They require public schema okay, so and You know why it happens. There is a reason why it happens There is a reason no matter how hard we try this happens and I will tell you why and Just to start what will be covered so we will go through Most important security challenges, which is like what I said yesterday I want this to be part of postgres not hate is fixing postgres and We will like I will explain the necessity of standardizing approach And I will show the security models which we adopted in dr. Double your holdings and how we use Automation and what is next what I'm trying to achieve except for making half of it part of postgres So The reason why it's still a public schema user postgres There are several challenges first challenge is You do not need to have any security to start working with postgres So postgres has very low entrance barrier right we know because nobody needs to learn postgres You get it you install it open source and then maybe you start reading the commutation But for the moment you installed it locally you are automatically connected as postgres. You are automatically like within public schema go okay, and If you decide to look at the commutation That's the table of contents of the commutation Anywhere anything about rules users permissions Tell me do you know where is it? I know actually where is it like nothing, right? When remember, okay, most of us have been with Oracle at some point of our career. Let's be honest. Let's admit it So remember when you start with Oracle you do this cot tiger thingie. You cannot be like nobody, right? You can be nobody or you can be everybody you can be super user in postgres So I will tell you way in documentation like the first word about privileges appear In the middle, right? So you are done through what is postgres how to use a quick start everything all of a sudden Oh, now we can talk about users and if you click on this Do you see where privileges are? Not in the beginning right here So you're kind of like you already stopped reading the commutation because you did quick start You know how to do everything Yeah, there's it and the most alarming thing So, you know how happy I was when Magnus just announced that the special status of public schema is removed in postgres 15 still still in 15 in 16 all Examples in documentation are from public schema like like look for it Like nobody ever mentioned you can create something other than a public schema goes by default. So Then we have hard time telling users that that is wrong, you know, I heard like, you know users like okay You just told us the schemas exist. We did not know before like seriously. I'm not even kidding So so that was challenge number one As a result what happens? Yeah So applications are developed using postgres user because it's easy when they go to production They don't have time because like it's deadline its deployment and you know, we will lose money if I won't deploy today You know, no matter how long you develop, but we kind of do anything. Okay, then the other thing like for some reason who ever Designs like country builds to postgres. They forget that in real life. There are no users typing There are applications using connection pool. So forget about roll rail security, whatever Everybody are connected using the same user and how often this user is postgres more than you want to know. So It's the same database user Challenge number two the wonders of inheritance So We did it. We did it long time ago Actually, you know yesterday was the conversation who started when I was still there at seven three I remember when we moved to it. So the Difference between groups and users was eliminated in seven three Because now we have every everything is a role. So now user is a role with login and we can Grant anything to anything anybody to anybody So if we create roll one then that will be roll no login roll. We create roll two With login so that is a user. That's okay We create user one is the same as a roll with password. So that same roll So now all of these grants will work postgres does not see anything wrong with this So you grant roll one to a user you grant one user to other user by the way forget you Secure this user with password you granted this user. So another user does not need to know this password Go and at the end wonders of inheritance you create another role and you Grant this role to roll one and when you grant you do not know who else owns this role one who else has it so everybody will grant Will receive this grant everybody who have roll one will have also roll three and when you will grant emissions to roll three everything will go up and You do not even like No, what will happen? Come so You know it creates total security Hazard like in two steps two steps is enough to create total security hazards in your database like the one horrible thing I had in real life not like recently but I Accidentally everybody say like public was granted a super user Like by the like by ways of multiple inheritance So another thing which for some reason okay people who work with postgres professionally know but for some reason everybody forget People think that user is created for database because you are connected to a database and you create user and You do not created for the database because users are created on instance level And I cannot tell you how many times people say we want this in separate database I like why for security reasons that there is no security any user you create have automatically connect Permission to the new database and yes, you can explicitly remove it But when you create another database it will get you need to know what to remove So you need to know from which users you want to remove access So you create a user by default they have access so they have connect And if you have public anybody from any database Anybody can create anything in public schema of any of other databases and Also Sometimes you we need super user because of whatever so if you made this mistake This super user can do anything in all databases on this instance. So hello, no no security here now We actually know how to the things the right way and until I started with the Rw holdings I thought I know how to do things the right way because when people know that We need to use schemas as units of security. We need to have an owner of the schema We need to have roles. We are not granting privileges to users directly. We create a role Which holds the privileges and so what I'm going to demonstrate now it is like Written in many many blog posts not in post this documentation because we are flexible but like Any blog post from anybody who like tries to do it the right way? You will find something like this using schema and granting roles to users So you will have something like this you create a schema owned by Honor roll you grant this role to a user you create read write role and you create read All the role and you operate like this. So that's like not new. That's not hate invented. It had been around for very long time Okay Now and what you do you grant select on all tables to orders read only your grants selecting sort of daily to tables One all tables to orders read write. So What won't work What won't work it's interactive Trank it. Yes. No, but not only what else truncate. Yes. Thank you. What else? What else will not work? Hey, I play any application developers here Thank you. Thank you. Yes. Yes. Thank you. Yes. So yes The full privilege challenge and by the way one thing everybody forgets here grant usage cause like I still need to understand why it has to be grant usage But when you grant read write whatever things do not work and then yeah, you need to define default privileges because the previous grant this one which we had here, okay? So here we only granted Permissions to tables which were already in the schema. So if we just created the schema, it's nothing So we need to do default privileges, okay? Default privileges out the default privilege in schema orders grant select on tables to read user grant everything else to read write and This should be Okay, because now what was saying any table which we create and views by the way views are also tables in this situation Read user will have select right user will have everything so And remember we created role we created user some all good, right? So user there is a user who can create tables We create a table By default permission of the plate It's interactive by default permission of the plate You created table they're not applied. Why? Okay Because yes No No, no, no all tables so good. No, it's not it's I created I created a table So remember I had the owner user on a roll. I did alter So I'm admin who can create I create table default privileges are not applied. You know why? Yeah, I mean What matters is that you need to grant default privileges not to whatever But for specific user because if you issue this alter default privileges You know to whom it will alter default privileges to the user who was doing this So if you have a database owner when you did this out of schema, it will grant to post grace to whoever not to intended so now Yes, that's what you need to do you need to alter default privileges for the schema owner because you want them to be kind of like encapsulated on this database on This actually leads to other Citation because if you are now admin user you need to explicitly switch to this role because otherwise you can create table But default privileges won't be applied as well. So they're like two things which can go wrong All right Wonders of ownership. That's the thing which blows mind for everybody who comes from Oracle so we own Schema right create schema orders on orders on So this is created by default. So you do not do this grant all on schema orders to orders on that it is Im implicitly granted when you create a schema. So this role orders owner can create all objects grant has all selected sort of update delete truncate whatever some Now and I face it all the time. But like people are my gosh, we actually need to reduce security if you do I Also schema orders owner to New orders owner who knows what will happen with permissions Anybody knows anybody tried it? Who knows what happens with permissions? Okay, the answer is nothing happens with permissions like literally nothing So this schema this schema will change the owner But the old owner will still have all set of privileges So in order to switch the owner what you need to do you need to rework Everything from the previous owner you need to read catalog go through all the objects You need to switch in honor and yes, there is Reassigned command reassigned all it does ownership it does nothing with privileges So you need to explicitly rework all the default privileges grant new default privilege So it's huge project. So like one of my asks is why we if we want to be flexible why at least we won't allow some flag hi Like switch ownerships like cascade or something like this because that is a huge project if you have lots of objects in the schema so and Lots of other things and I can talk like the whole hour about all other weird things But like just a couple of things, okay for example, okay, so you granted select on The table to a roll Then you grant insert update delete on the same table to another role you create user You grant this rule to to user one. So now user one can update Insert update delete so then office is actually we do not want this user to do delete What will happen if you rework delete who knows what happens? Yes, correct. It does not do anything. It does not do anything because we never granted delete We granted a role which has delete delete as a separate privilege was never granted But do you think postgres tells you you are doing something wrong? He does not Not all it will not work. There'll be no error. So you execute revoke it will set Yes, revoke revoked revoked and then this user can delete. So that's like one thing. I reported long time ago And that's like what I was referring to. Yeah, that's probably wrong But we have like 120 other things which we need to address So if you revoke permission, which you did not granted or nobody granted it would not tell that no This is wrong. It's revoked all good So and also if you grant the same permission multiple times the only time when it will report or you already have it is when you grant the existing role one more time okay Then in addition other challenges You cannot drop the user Which has any privileges? So it is a mistake, right? It will error and like you cannot drop this user You cannot drop user cascade because you know with all other things you can drop user cascade You cannot drop user cascade. So it will not you need to revoke all privileges explicitly and remember They can be in different database now And most interesting there is no easy way to tell Which permissions this user has like trust me because I Wrote a function which lists all the privileges for the user's function is like about hundred lines long and other people who try to do the same they kind of end up in about hundred lines of code just to list all privileges for one user It's all okay when you have several databases, okay, so I have 280 Approximately, we are not sure how many we have in organization, but something along that lines and We have a team of Okay, we have a team of very little number of people because also postgres is not our only and not even our most often used database in our organization and we have total debate team of 14 people like for all databases Again postgres is just one and not the biggest so What we're going to do First of all I work in trading and in trading people are super secretive They want them to be isolated as we already know databases Separate databases would not help separate instance switch project I had internal customer who requested New instance literally for each new pair of tables because they wanted to be separated So that also kind of expensive even for trading So what are other approaches? so that's kind of like what led me to Developing this security framework again Why I'm talking about this 280 instances. So there is no room for error Previously I could say hey, you know what you did not switch to correct rule I'm sorry. You'll need to start in your I cannot tell it to owners of 280 databases So I need to have some automation which does not allow anything like right and left So here is my security model. So It's kind of like not really open source. So I only will show the pieces of code So I will try to show ideas Rather than how it is done. So if you want like more clarification, I will try please like stop me but like Just telling you it's not exactly pieces of code is more like what we did So what are basic principles so first of all principle of least privileges there's like a Principle of any security settings. So user is given minimal level of access needed to perform their actions So if you do not need something you do not have this only exactly what you need not more second is durability which means it's like with right again I and I promised to take a person who will break it out to fancy restaurant if they will break so so far nobody broke it Because I like I challenged my DBA team to try to break it without being a super user. So it's pretty much unbreakable and flexibility because different customers have different needs in Like securing their data. So I was struggling with different package version. So the Version which I have now is flexible. So just using two flags there I can satisfy pretty much everybody's security and permission needs Now what are the key features how it works? So first of all again, that's my big ask a postgres postgres I I do not want to create event rigors for this. I actually want it to be kind of You know, you you should be able to set this up in postgres But now I have an event trigger which switches permissions when object is created So pretty much what it does it Forces permissions to be correct in the correct schema Then I have security level matrix and I will go through this matrix like in a second Security is on the database level. So not only is this level on each database You set security package and you can set your security level on each database and How it is all executed? I have a set of security definer functions Which do everything instead of user issue in command. So I'm not giving anybody the super user, but users can create schemas grant permissions create users all the through security definer schemas and So to define the functions and they can only execute things which we allow them to execute Yes, yes, yes, yes So enabling so there is a security package which set a function It is by default installed in each postgres Database and it is dormant until we explicitly initiated by a setting it up with security metrics So you cannot change settings after they're set once for the database otherwise. Yeah, it's like It'll be bad so there is like one function which postgres user executes Grants create schema users and it uses permission settings Which I will show on the next slide the permission settings are recorded for this particular database and If you redeploy this package because the biggest challenge I have I made changes in the existing function So I do not want to break it and start over. So if I already had security setup the installation script picks it up and Reimplements it so I do not need to do it. I can redeploy multiple times So that is the matrix and I will highlight all of them So we have a schema owner and account owner fingers. So how it how it will happen. So first was this Schema which I described previously. So database owner owns all the schemas and grants Read only or read write. It's a covers most of the situation Then one of my internal customers came said no We actually want to be isolated per schema because there are different teams who are creating objects And we want them know nothing about others and if you can do this for us without us being creating yet another Instance, it would be great. So there comes a model number two schema owner through Where each schema is owned by separate user schemas are still created by admin but This is scheme is owned by its own user and gives its own privileges to them like application user then There's another customer. Okay. Ah, you know what actually I want something different. I want to power users Which can each can create their own schemas, but not knowing anything about each other So then it was model with account owner and account owner has the schema ownership And then there was the third one more user And they said we are selling services So we want to create Accounts in this accounts can create their own schemas and again They can do everything within their own schemas, but know nothing about each other. So I like said, okay and That was kind of like finalization of this matrix So at the moment like again if somebody knows anything else, please tell me but so far this Matrix covers like everything what what we can do. So let's Okay, so let's look at what is done for each of them. So in first case false false it is each schema is created by Like database owner and this schema is created with two users read all in the read-write and They are the rules are granted. Oh, sorry. The privileges are granted to these roles So that's like classic everybody who does security on schema level does this So next month again database admin creates Like user segment creates schemas for each schema it creates the schema on a roll and same story creates Read all the roll and read write roll And again here the owner is the schema owner so in this situation We create account and account has like some privileges privileges to create schemas again via security define the functions and I also creates a read all in the read write users and this last one We each schema is created by account owner but for each schema they create separate schema owner and all the default permissions are like Cascading in this manner and in this case the account owner can do everything with their schemas but the Schema owners can do like can do everything within their schemas. Okay so What are the functions doing? So they honestly the biggest challenge for people to adopt it when they realize what the advantage is it gives them they love it but The problem is that they need to call functions and again, I would love it not to be functions I would love it to be like you can switch to some Flag or like option on create schema create table whatever and I'd be happy but anyway So create schema roles as first function. So whoever Has a permission to create it they can this function will create schema it will create a Application user it can create user and password at the same time doesn't have to it can create a schema owner if the security level is what schema owner and can create the password and It might Desegnate it to specific count again if account is applicable It is defaulted to the current user and you cannot really change it. I will tell in a second. Why? So again every the call is the same everything inside is driven by the existing security metrics So which roles are created which not? Drop schema roles. So remember you cannot drop Anything without dropping all the privileges and the best thing is if you drop schema, which you can Then you have all these remaining users and you cannot recreate the schema because you cannot recreate this users. So Instead of drop schema you call drop schema users and you pass schema name as a parameter So what it does it revokes all the associated roles from the users And it drops this roles and it drops the schema. So all of these things. Yes clean clean drop Then assignment functions so you can assign the Schema owner user you can pass schema name the user name and the password So here is what it does if it's a new user It creates a user with a password if it's an existing user it will Like if you do not pass the password you just assign the role if it is Existing user and you pass the password it will change the password. So that's how it works So same for the application user so same story, but for the application and obviously there is one function underneath of it But our users do not need to know this one function with the rules at all and For it only user again same story. It's underneath is the same function and Also, there are all these revoke functions So same you can grant role and you can revoke role and again everything is done kind of in a clean way So we do not drop users because remember we do not know what is else these users do because you know There are some creative people who actually reuse the user. So we're like doing it safely All right so And additional security define the functions again those are the functions which are like very very very needed So first of all select all privileges. So if anybody in the previous lives ever saw my diff Project I like create these differences between different Instances so this function is copied from the diff and it does list all Privileges kind of like disassembled to atomic level So no matter how the privileges are granted by roles or directly So this will list everything everything everything. So it's a good self-check and the other two Seen blocking processes and prejudices that activity. So why we need this because By default again, why not giving people super user privileges? otherwise, you can always see your own sequel and If you like run it, you will see if it's not you there will be no sequel and displayed because it's security and Admin wants to know who did this. So this function works as a security definer as Like postgres and it can display all the code of the queries. So that's Very important in our Citation because you know in the queries you can see all the data and you know what again? Work in and trading I see all the data which I severely want to unsee but I have to see so So no nobody except of their admin like is entitled to see things All right. So code details Again, it's no magic. So how I am forcing privileges again, that is the part of the event trigger So when you create any object, it's not one create It's whole bunch of crates because when you create table, it's create table Create constrain create index if you have some primary key Like or unique key and whole bunch of things So event trigger on create goes through all the objects which are created Changes ownership and permissions when it makes sense because there are lots of little things like you do not change ownership on index But index needs to be created after ownership is already changed. So everything is like done inside this trigger So no matter who you are no matter how many times you forgot to switch to correct role If you own this role if you have a right to create objects in the schema The permissions will be the ownership and permissions will be assigned correctly using the event trigger Now how I know that you have a right to do this. That's how I know that's like again part of the code which That it's standard recursion which identifies which role you have Plus if you were smart and explicitly switch to the right role, which you do not have but like, you know you should So this basically returns true if you are allowed to do things in this schema now In other thing is so that is like you are allowed but how you know you're allowed or we have the security metrics we have the account owners So I need to know who you are But now you are about to go to the security define a function in security define a function when you are inside You already postgres. So how I know who you were before you come So I actually have non security define a function on top which passes me a current user and then Okay, if I'm allowed to do security define How can I make sure that I am not nobody calls it directly and for these I am actually checking the stack So security define a function can be only called not stand alone But from non security define a function this way I know that I captured who you are correctly So and then I know are you allowed not just to execute the security define a function, but to Executed on this particular object. So you cannot drop somebody's L schema you cannot create objects in somebody's L schema You can cannot assign new user to somebody's L schema You can only do it as you and by the way postgres as well Like it's like disappointments for our debates, but yeah all right So This is like again very Briefly how it works and I know it was not enough code. The package is pretty gigantic so What is next but this so first of all Users are keeping asking me about reporting and although I have this list of privileges, but it's on my backlog to Create reporting which tells you okay this database has this accounts this schemas and those are the people who like Have this role. So nice Jason is due it will happen You need testing you know nobody wants so first of all people do not understand that you need unit this It's like the whole big thing. Nobody wants to do this not like I love doing unit this but with this complexity I actually had to do this. I am like 80% done with unit this No, it turns out each time that I did not cover some of the cases I have like about hundred tests for each of the Versions of this matrix and it's still not enough. I'm still finding cases. So hopefully it will be done soon and Conversion automation so if the big thing is that we have customers who are not on security framework and now we're making it as a company-wide standard and We want to automate the conversion from God knows what like literally like you cannot imagine what what can happen to proper security Framework so far is severely manual process because again People are so creative. They're like schemas own by login user. Then you need to do several things There are objects which are owned by different users within one schema So fun part is I try to kind of automated create functions So when you have partition table and partitions are like several thousand and we have it and honest to God It's legit in many cases then I'm like running off DDL locks in transaction So I actually before I do this I need to investigate how many skimmers have Partition tables and then I need to change ownership because yes all partitions ownership is separate So so far. It's like very very loosely automated process. So That's my backlog again most importantly I would love to have Lots of these things as part of postgres like to summarize. I do not think postgres should revoke things Which do not exist silently. I think there should be some hint I would love to have an option. I created the schema Please make sure all objects in the schema are owned by schema owner. I would love to There is a minimum, you know, I Talked to many people they already told me that Preventing from granting privileges directly to user is not working since we did what we did with roles and users So and I know that you can create something there's no login role and then you can just Add a password your login role great so like Yeah No, no salvation that but You know, it is the way about doing it now it helps and each time Any of our internal customers kind of like oh my gosh, what's happening? We like do not know like the law's permission here like permission granted, but they were like, okay switch to this this framework gives some sanity to the base on support because at least we know what to expect and again We know that nobody even other to be on support who does not know I Had so many calls from DBAs on support. Why have this? Because you are not allowed to do things directly So yeah So that's that's it Thank you for listening hopefully it was interesting in the Questions Hello, as far as Visibility into Issues you you have presented Do you or have you ever considered using like a threat modeling tool or other command-line tools to Identify permission issues or other issues with databases like Microsoft threat modeling tool or What did it work? No, okay, so permission issues happen So the thing is like everything what I demonstrated I came across when I was trying to convert people and was trying to switch permissions I found so many things. I like don't want to know how many I found so Then the question is like how you That's a big problem because there is no like command in the sequel which allows you to list all permissions You can there are some sequels like this recursion which allows you to list all roles But that's pretty much it you cannot really list all atomic permission. So I have this deep packages open source So again, it's one heading post this Haiti dig it up There's deep package the IFF and I have it there. So that's a Function which you can like deploy time on your database. It's not an extension And it will issue all permissions. So other things like maybe there is something else I'm listening all I found what is that inconsistent Slice will be okay if they won't be there within the next hour you can find me and like beat me Okay, I was making changes like until this morning because I forgot to just to display Do does either you or the Chicago group that you work with have any fabulous github repos where we can borrow some of your code Do you or the Chicago group have a github repo where we can borrow code samples Okay, so this one is not open source because I developed it for the company and I still need to figure out what I will Outsort like outsourced what and all my open source projects are in my repo I have five open source projects again Haiti dear I'm visible I will try to open source like at the moment is proprietary code, but I'm planning to open source as much as I can at the moment is not So for the security piece, did you reference any like mist or? CIS or any security related templates for to secure your your module What wait no No It's all like in big so pretty much, you know like first a security scheme is everybody using and I started to use it I use it my free previous jobs and startups and then all of a sudden I learned that people need a way more and I just started to Come up with models and then I generalize them So so far all I have within this like 280 databases fits in one of the four Do you have any strong feelings towards? Role level security Scarecrow things about security. Yeah, how do you feel about role-level security? RLS role-level security where restricts So, okay I can tell how I feel because people whom I want to hear this are not here as I will tell anyway This is one of these things when people who came up with this they Do not know how people use Postgres in the real world because it's like not needed it's like like not needed 100% because They are not users who are sitting in typing commands. They're applications and most applications like if we have like one User per application. It's already huge plus very often. It's like one user for all applications So forget about this. So I think it just like one of these things when I Try to be voice of reality here, you know, so I'm not a contributor I'm nobody so so then do you think it's a bad idea to have many different users accessing a same Same goal database where maybe no, no, no, it's great. It's not it's a great idea to separate by yours, but the World does not work this way again. I'm in the real world. I'm a realist. I know that There is where I'm no my no matter how much I hate or M or M is there to stay no much How I hate many things which are Out there they are here to state and my goal is to assist Application developers to do what they need without creating hazard in the database. That's all I'm trying to do So like a low level security is something which like I was always wondering What was the idea behind this because it's like absolutely not like applicable to real life Okay, no, I'm happy if somebody does awesome any other questions All right, thank you. Thank you. I think so. Thank you so much Test test test hello test There we go. Cool. Maybe Yeah, I feel like I'm at the dentist that is definitely not comfortable test test Hello Oh, it's it is on. Oh turn it off. I don't know like Turn up the volume. I don't I don't know if I could do it from here. There's probably a Or like again, I could just do the handheld mic Yeah, but I could it'll be easier to control Yeah test test cool Much better. All right. Good morning. How's everyone? Good cold. Yeah, so I apologize. I unfortunately I don't control the the temperature or the weather so There's not so much I can do. Yeah You control the okay, so Devrim can control the weather. So thank you Devrim Hopefully you'll get a little bit warmer, but this is a very hot topic. So that will also get it warmer So I'm Jonathan Katz. I work at AWS. I'm also on the core team of the Postgres project and For better or worse, I have been staring at this problem of vectors for well over a year now and I'm very you know, I'm very excited to talk about this topic today, which I'm terming vectors are the new JSON and You know maybe before we start like it sounds kind of weird like what do you mean vectors are the new JSON? Like that would you know that that sounds like you're just trying to you know Do something that's like click bait or you know get attention to it But you know allow me know please know entertain me for a bit. So here's a JSON document And you know this seems you know it's you know has some content in it you know, it's about Postgres you know it contains something about supported versions and We saw at one point that We might have like an application that has this JSON document and you're like okay cool Well, I love using Postgres. I'm gonna take that JSON document. I'm gonna turn it into a relational structure Side note. I think that pretty much all JSON can be turned into a relational structure and you know stored in a database Cool But then we just got the notion saying like hey, you know, that seems like a lot of work You know, I already have structured data might not necessarily have a you know a schema as we as we know it from a database standpoint But it has structure. Why can't I just take it and store it directly in a database? So people did that, you know, we saw a lot of these JSON databases or document databases come up in the in the late 2000s and You know, you know likewise Postgres ended up supporting it in part because you know as I could say right here, you know JSON's a data type It's a complex data type But it's just a data type and one thing that Postgres does very well is that it stores a data type and it stores it you know, you know, it basically has a representation in its storage that allows you to query it and Manipulate it and get all sorts of insights on it And that goes beyond just Postgres, you know to be fair that when you have a data type and you have a database or a data storage system You can store it. You can query it and you do all sorts of things with it Postgres has a long rich history with JSON. You're starting back in 2012 I mean as JSON really became popular in the late in the late 2000s with web apps We saw like a huge demand for folks who just wanted to store their JSON data and Postgres because it either lived next to their other Application data or it was their application data and it was just much simpler for what they were doing to be able to query it directly What was cool is you know Postgres was the first relational database to to be able to store JSON and lo and behold It became part of the SQL standard, which is pretty neat Now fast forward a bit right, you know, you know, this started back in 2000. Let's go to 2024 and This notion of this like kind of weird line has become very popular you know, this is a vector and You know to the best of my abilities, I drew a two-dimensional vector of you know, 0.5 0.5 coordinates or attributes bear with me and The vector has two key properties It has magnitude or size, you know, you know how big it is and It has an angle so basically you have some kind of size and you're pointing somewhere in space It's kind of cool because you can add more attributes or more dimensions to it and you could see that okay You know here I am in three dimensions or hopefully you can see the three dimensions, you know I my parents were grateful that I didn't go to art school and You know, you can see that I'm you know pointing, you know, I can see where I am in space Vectors are kind of old, you know, JSON is a newer data type Vectors there is you know, they have some history to it. They've about let's call it like 120-130ish of history and there's this book in 1901 that you know codified the early mathematics to vectors now Back then you saw vectors in more mathematical applications around physics You know, you kind of study vectors when you're learning about forces in physics or even in you know You know translate back to you know, introductory computer science classes where you learn that you can have a vector is being a specialized form of an array but Vectors have become very popular as of late, which again is interesting right because I said wait a second like you know We kind of learn about vectors indirectly in an introductory computer science class, but they're a big deal now in these storage systems and It really is because of generative AI which in some ways I take a step back even further I think the reason why it's become very popular is that it's much simpler to use AI and ML systems today True story back when I was in college I thought I was gonna go into machine learning like I was fascinated by the fact that you could like take these systems and You know learn on them and be able to create inferences or you know some kind of artificial intelligence is something that really fascinated me But at the time I was looking at all the research going to machine learning And I was like oh everything's ad hoc whenever you want to do something you have to build your own model It takes time to train it. It's very exhaustive, you know, I'm impatient. I'm gonna go into the startup world I'm gonna build web apps, you know yada yada yada And of course, you know my you know I kicked myself as I didn't have the foresight to see that this would be commoditized that Using these models would be as simple as making a rest call And that's really the transformation in many ways is that yes, these models are much smarter And they're able to get these inferences that are much deeper today But they're so accessible like we can use them very easily like you know We can just go right now and you know you probably going right now and have one of these models build out the talk That I'm giving right now and that that's really cool But the other key thing about what we're seeing with you know the big foundation models Is that they're trained on public information or information that is widely distributed, you know I like to think of like the entire internet as being that kind of database But there's a lot of information that's in private databases information that you know you've been collecting for your applications or You know or whatever you know whatever things that you're doing that the foundation models don't know But yet There should be a way to be able to take that information that you have in your database And be able to use these foundation models to be able to build that generative AI experience or that richer you know add a richer AI experience to your applications and The example I like to give is something called well the technique that's become very popular is retrieval augmented generation Which you know, I'll explain a second, but I'll start with an example So when I first built this out, I was sitting in my in-laws sunroom in Florida and my father-in-law loves to collect You know all sorts of like Florida chotchkies, you know, that's like alligator heads to cans You know all sorts kind of birds, so I'm like, okay Let's say I had a store that was selling Chotchkies and I took the example of like a blue elephant face because you know blue elephants I kind of like them and It's like okay. Well, let's say I go to a foundation model and now the blue I'm like how much is this cost in My store and the foundation model is gonna be like, I don't know. I don't have access to the information in your store I don't have access to that database and that's what retrieval augmented Retrieval augmented generation comes in which I'm just gonna say rag from now on because I'm a trip up over those words But the idea with rag is that you do have information in a private database that you can use to augment the model So in this case, I have a product catalog. I have pricing information I know how much a blue elephant face cost and when that question comes in I can basically take that information residing in my database and Put it into the you know the foundation model to be able to trigger the response to which I could say Yes, a blue elephant face does cost about $20 and That's a really cool part is that you don't need to have to train your own foundation model to get that experience You can take the information in your database and be able to augment that Then the question becomes that's great. How do I do that? And that's where the vector comes in We basically need a way to be able to take the information on our database and have in some ways like a lingua franca Or a universal language to be able to move the information from the database to the foundation model on back And you know, that's where this you know this that's where this occurs in the rag workflow so Rag occurs in two parts The first part is you need to be able to take your raw information, you know, it could be unstructured data And what have you and be able to create a numerical representation of it? And you know today that numerical representation is the vector So that's that first part down here, you know the example here is like I have a bunch of PDF documents Let's say this is like my store catalog You have to do something called chunking it up because the way these embedding models work is that they look over a subset of the information and then they're able to assign that numerical representation to it and You put put into the embedding model and then you store it in a vector database or a database or you know We'll get more into that So that's part one that basically builds up your data set to be able to be used for rag Part two is well, I got a customer.  So the next part is that you have to take that question and turn into a vector Because what you're going to do is you could do something called a vector similarity search in the database to find the things That most resemble your blue elephant vase and you'll see like, oh, okay. I have a blue elephant vase Here's the pricing information take that question give the you know provide that additional context on price Send to the foundation model and then get that response that we saw before So that's part two So the vector is important in both parts because that's basically linking that unstructured information whether it's the you know the information about the products itself or the question that's coming from the customer and It ties it all together to be able to deliver the answer to to the user. So that's pretty cool. So I'm We're all questions in the the end, please. Thank you so that's so that's rag in a nutshell and It's a fairly simple workflow but there are some challenges that you know are presented particularly with vectors and In the case of rag and generative AI the first part is how long it takes to generate embeddings Some of these things can take, you know, tens of milliseconds hundreds of milliseconds or depending on how busy the model is maybe longer And okay, maybe that's not so bad You know if you're doing this is part of you know asking a question But if you're generating it or you need to generate it in real time You don't want to go through your whole data set and have to generate these embeddings all at once You want to be able to do you'll be able to cash them somewhere The other thing is that they get to be quite large You know a lot of the models they return you know over a thousand dimensions here 1500 dimensions and the attribute the individual Dimensions of the vector are four byte floats So that becomes quite large very quickly like one of these vectors is about six kilobytes Again, you're probably like Jonathan like six kilobytes is not big But imagine you have a million of these that's already like 5.7 gigabytes that you have to store Now again, you might say like Jonathan that's not very big like I have like all these PDF documents that are much larger But if you think about it from a database standpoint like your average database row is nowhere near six kilobytes It's probably closer to 600 bytes, you know, it's not taking up that much space So you have a million of these you're already you're paying a lot more for space And you're like well look we got compression right we can like shrink things down, but not really for vectors Because these are a bunch of random 4-byte floats like there's no patterns that you can use to to shrink them down. There's different techniques There's quantization techniques There's principal component analysis where you can make the vector smaller But you lose information when you do that and that's part of the challenge that we're going to discuss a little bit later So All right, so we're already seeing that this is already pretty challenging, but what about querying them? The primary operation to compare two vectors is called a It's called a you're finding the distance between two vectors you might hear it as a similarity search and There's two things that go on here is that if you want to find the vectors that are most similar to your query vector in a data Set you have to query every single vector in that data set to find the ones that are most similar to you So again, you have a million vectors. You're doing similarity search You have to query all 1 million vectors, but wait it gets worse because when you do that There's no shortcuts. You have to query against every single dimension in that data set So if you have a million vectors each of them with 1,536 dimensions if a query vector You're doing a lot of calculations there so There's no shortcuts well There's one shortcut Something called approximate nearest neighbor search So the idea with approximate nearest neighbor search or again I must abbreviate to ann is that you can find similar vectors in your data set without searching through all of them Which sounds a lot like indexing from a traditional database standpoint And it is a kind of index because you are looking over a subset of the data set And again, if you don't have to like look at all 1 million vectors in your data set If you only have to look at a hundred let's say to get your answer. That's way better That's gonna be a much faster query and it is it's much faster than They call it like traditional nearest neighbor search or a traditional, you know Exact nearest neighbor searchers, which is the term we have today But because you're not looking over every vector in the data set You can't be sure that you're getting the most relevant results and that goes into this measurement called recall Which is your percentage of expected results? So take you know those you know ten orange blocks to the Sorry ten orange circles to the the side. Let's say those are my expected results that if I have a query vector I expect to see those ten vectors But I only return eight of those when I search the index my recall is 80% because I only got 80% of my expected results For people who've been working with databases a long time This is a very weird concept because you're kind of like wait a second like I put in a query I expect to get the exact results that I stored in back, but you're saying I'm not getting my expected results That personally tripped me up the first time. I worked with PG vector. I'm like this is wrong I'm getting the wrong results But this is how approximate nearest neighbor search is because you're you're looking over a subset of all the data in the set And you're making it best guess and some of these annexing algorithms are very good that you're going to get a high You're a high level of recall, you know 95% 99% But you might not get the best answers or all the answers that you'd like to see there So this is a very important concept to keep in mind as we go further into this journey So before diving into one of my favorite topics first like a very good thing to ask is when you're looking at storing vectors is Does vector storage even fit into your workflow? Like do you need to actually have a quote vector database to store the vectors or is it something where you could do it entirely in memory? You can use a library like feist to be able to calculate everything That's one thing that you do have to determine like you may not need to store vectors But if you do like part of that you know part of that math is like how much data am I storing? Is it millions tens of millions billions hundreds of thousands? You know and because based upon that there's gonna be all sorts of cost associated with it. I mean as we saw These very large vectors have a payload, you know six kilobytes for a 1500 dimensional vector is a lot If you have tens of billions of those you're probably gonna look for cheaper storage But if you have a hundred thousand your 250,000, you know, whatever it is, that's you know, that's still not that much and Then from there you're gonna decide, you know, what makes the most sense for me Like do I want something that's high performance? Am I most interested in a hundred percent of relevancy like I always need to do the exact nearest neighbor search And you know ultimately what is my budget and From there like once you understand which attributes are most important to you Then you can design you can make designs for your vector database, you know Traditional things like schema design, which is you know, my favorite thing when working with a database And you can determine like what's your indexing technique going to use do you need an indexing technique? You know, what's your target query time? Which brings us into postgres as a vector store So this is really cool right because you know postgres has a long history you know, I'm roughly the same age as postgres and Postgres has evolved through the years from being your traditional relational database to beings, you know geospatial database being a time series database being a being a document database and What's great is that now we can see postgres as a vector database and part is this because postgres from the get-go was designed to be Extensible you can add functionality to postgres without having to fork postgres and that's a really powerful concept And what's also really cool about that is that over the past several years postgres added the ability to build your own custom indexing techniques, you know without leveraging one of the existing indexing frameworks in it and This is very powerful particularly when we're looking at vector searches because one of the reasons why postgres has become popular for vector searches is that It can work with your existing client libraries again, you know, I'll make a confession in front of a bunch of database folks I like using RMS when I was an app developer. It was very easy and convenient But that's great right because now if I want these vector capabilities in my database I can continue using my favorite ORM and I don't need to change anything and that's powerful too and also because Likely in my application My area ML data is you know I want to keep down the same database as the rest of my data and being able to have that Co-location is powerful because I don't need to store my data all over the place and some suddenly have like a distributed data problem I can keep everything all in one place and that's very nice And then you get you know you get postgres with your vector database because postgres Is a lot of things for you that you might take for granted being an asset compliant database is very important You know, I you know, I always like to focus on the D of asset compliance You know the durability that I know that if my database is writing data to the disk It's getting stored to the disk, you know, I know you know That's a very powerful property as well as the properties around like visibility and you know how things ultimately get searched So just as a fun fact Postgres actually has been a vector database since the get-go The array data type was in the original was originally in the Berkeley version of postgres Arrays were created to help with looking of access control list within postgres So you didn't have to do a join out to be able to get those rules, you know They could stay they basically you could look at everything in one column now the rate the rate data type is awesome you know, it's very versatile it works with you know a bunch of the day types already within postgres and You know, there's almost like no limits with it as I say there, but It doesn't support distance operations however back in 2000 or 2001 of the cube data type came there and the cube data type was basically able to store Larger vectors of data actually you could index up to a hundred using the built-in Yeah, and with the built-in gist index you can't you could do it like exact nearest neighbor lookups very quickly Just a side note the gist index is what post gas which is that geospatial Capabilities for postgres I mentioned leverages to do very fast exact nearest neighbor searches now Cube is great, but it's limited to a hundred dimensions and again like when I say machine learning like 20 dimensions was a high amount of dimensions So like I see like you know, 1536. It's like what is that? That's a lot of dimensions But the fact is that we're limited because you know with the things built into postgres We're not able to handle these newer size vectors So this brings us to PG vector PG vector is an open-source extension that gives you all the capabilities you need to be able to do vector search in postgres for one It has a vector data type You know has a couple of indexing methods which we're going to go into it supports exact nearest neighbor search approximate nearest neighbor search a bunch of different distance operations and You know the the best part of it is that you get postgres You can take your vector data and co-locate it with all the other data associated with your embeddings and have it all in one place Now I'm rushing over this slide because we're going to dive deep into like all these different things You know during the remainder of the talk, but you know, this is pretty cool I'm like this is the power of the postgres, you know extension framework is that if there is a new a new kind of data pattern or Some capabilities that are needed you can build it in an extension And also you know as we learned at the AMA yesterday if there's something that you can't do in an extension And you need to expose something, you know in postgres directly to enable an extension That's a great, you know, that's a great, you know topic of conversation with people who are you know contributing hacking on postgres Now one thing I do want to address real quick is just you know understanding PG vector performance and You know, there's a lot of different benchmarks out there I could do a whole talk just on vector benchmarking But one thing is that you know, it is like relatively fast particularly with some of the newer indexing methodologies out there You know, this was like one test, you know I ran just to like look at throughput and in this case. I was actually trying to compare different processors But one of the powers of postgres is that it can scale concurrently and it can scale vertically pretty well And like this does apply to vector data that if I do throw more, you know You know more concurrence selects that you know at the data and you know I continue to vertically scale the hardware that it can actually go, you know pretty far So, you know, that's one thing to you know consider You know, we're gonna look at a little bit of some scaling techniques throughout the talk But it does perform pretty well particularly when you're looking over like a smaller space of your vector index now As we can see throughout the talk there was always going to be a tension between the speed of your queries and the recall in your queries and There's a lot of things that you factor into recall But just keep in mind, you know when you're trying to decide what is your target recall? That is going to impact ultimately the speed of your vector queries So I'm gonna take a quick drink of water. Unfortunately, I have the handheld mic So please bear with me a second and now I have modeled how I can drink from a bottle of water. So Distance distance is very important because remember the distance is the fundamental way that you're going to compare two vectors PG vector offers three different types. There's Euclidean or L2 distance, which is line of sight So think of that as like me looking at you looking at me You have cosine distance, which is angular distance, which is like and you have inner product which kind of captures elements of both All three are used. We've been seeing cosine distance has been more popular with some of the larger embedding models It's one of those things where you do have to try it out Now all these have like different mathematical properties to them I'm trying to make sure I can get to all my slides. So I didn't put a slide, you know comparing all of it but One key thing when you're designing a vector database or a vector indexing a search system is that you want to cheat wherever you can And when I say cheat you want to reduce the amount of compute wherever you can because they're costly operations And in particular a lot of these operations have division and division is like the worst of all the operations That's still to this day the most expensive, you know one of the most expensive compute operations and That's where there's this trick There's something called the unit vector Which is basically if you take all your attributes and you find the magnitude of it. It comes out to one You might also hear it as called as normalizing a vector So when peachy when peachy vector stores a vector when stores in the vector column that column is going to be stored as You know, it's gonna sort of the vector, you know as as you insert it but when you add it to the index it's going to normalize it or convert it to a unit vector and the reason you're going to do that is It's gonna let you cheat when you do some of the distance Calculations because you're gonna be able to eliminate some of those divisions and in some cases, maybe some of the multiplication operations Which makes it a little bit faster because remember like whenever we're doing these operations like any place we can shave off CPU the better I mean another note I'll just you know, I'll just say right here is like what you know One of the challenges with vectors too is that you kind of have to optimize like everything in your compute cycle CPU memory IO, you know I like to say it's just really a nasty data type because there's so little shortcuts to be able to speed things up that like Any shortcut that you can get like just take it and you know, this is you know This is a popular technique is storing the normalized version of the vector The other nice thing about normalizing is that you don't lose information But when you're looking, you know, if you were to visualize the vectors in space You could actually see them get like clumped a little bit too much together And that you know becomes a little bit more of an issue when you're dealing with an indexing speaking of PG vector provides two different an and indexes IV a flat and h and s w So IV a flat is the cluster-based indexing method we're gonna like look at both of these in depth, you know throughout the rest of the talk and What's nice about IV a flat is that? It's relatively simple to understand It you know, you know when you really when you think about it It's like hey, I'm gonna build clusters around a few lists so like define like where my centers are and then cluster my vectors around them What's nice is that these indexes tend to be very quick to build? But they tend to be you know You have to basically have all of your data preloaded up front in order to build them in order to create the correct vectors Okay, please hold your questions till the end of the talk. Thank you So with h and s w hs w is a graph-based method So what so a graph so you think about traditional database indexing you store things in trees Graphs are just a super set of trees here I like to call them like slightly more complex trees and the way h and s w works is that it organizes vectors into neighborhoods So the idea is that when I'm trying to place a vector in the index I'm trying to find the vectors around me that are most similar and I might say like find me the most 16 most similar vectors once it does that you create links to all those vectors around your neighborhood and You know what happens is when you do the search You know you trace these neighborhoods to be able to find like you know these like strong clusters and more likely than not You're gonna find the vectors that you're most similar to we're gonna see a demo of how these work What's nice about h and s w is that it's an iterative index where you don't need to have your data loaded all at once To use it you can continue to insert it But it does have a much higher build time than IVF flat So I'll give a I'll give the cliff notes like which search method do you know do you choose is a little bit complicated But there's one simple one that if you need a hundred percent recall You got to do an exact nearest neighbor search Don't choose an index because you can't necessarily guarantee you with any method you're gonna get a hundred percent recall If you need fast indexing you're gonna choose IVF flat and that's you know If you know that if you know that you're gonna need to rebuild your index constantly You know you're adding data in such a way where you know It makes more sense to just continue to rebuild and find the new centers use IVF flat The reason I put an asterisk is that the recent releases of PG vector have improved h and s w build performance significantly where? This is becoming slightly less true. It's still true But you know with some of the h and s w properties, you know, it makes more sense to use that and you know just being ahead You know from an active available standpoint h and s w is a lot simpler to manage it feels more like a quote traditional index and It has you know What's nice is that it gets this high performance recall ratio in the sense that You're able to very quickly find the vectors that you're most similar to and more likely than not they're the most relevant to your results Most of the vector day pieces out there offer h and s w as you know the the first index available There are reasons to use both you know This gets into a deeper discussion and I've seen people implement IVF flat in their production systems And they're very happy the recall and performance So in some ways, you know the answer is test to see which makes the most sense from you I'd probably recommend starting the h and s w because it's a little bit easier But again, you know you have choice You know I think you know that definitely helps particularly because There's a lot of other factors that go into recall beyond just what indexing strategy you use such as your embedding model So let's go into some strategies and best practices and I divide them up into five sections Storage the two indexing sections filtering which your test database folks is the wear a clause and one that actually worked on the airplane over here today Sorry two days ago, which is distributed queries, which is kind of exciting and scary so storage strategies in order to understand storing vectors and postgres we need to understand toast and You know, I can make the toast joke like no, it's not the thing that you put in your toaster, but You know in some ways it kind of is so toast is it's called the oversized attribute storage technique and The reason my toast is important is that by default postgres stores 8 kilobytes of data within a page the page is the atomic unit in Postgres that gets stored to disk and that's you know one of the ways that we're able to implement all the nice features around postgres But we have data. That's larger than that. You know text being like the first example and toaster is designed to be able to store Data that went beyond 8 kilobytes. I believe you can store up to 1 gigabyte of data in a single toast row I Think I think that's correct. If not, I apologize But by default if you have data over 2 kilobytes postgres toast it which is gonna happen quite frequently with these large vectors that you have a 510 dimensional 4 byte float vector it will be toasted and anything beyond that So let's understand like what happens when we toast it because again for like a lot of us Like we've never considered toast, you know other than the fact that just works up until this time, but it's gonna impact us So postgres has four different column storage types which impact how it gets toasted The first is plain which basically says store the data in line But keep in mind if you use that you're only allowed to store up to 8 kilobytes of data Which is roughly a 2000 dimension vector There's extended where data is stored and compressed in your toast table This was the default in PG vector before zero six zero, but keep in mind. There's that magic word compressed I thought we said we couldn't compress vector data and that's correct We're basically wasting time trying to compress it to figure out like oh no We can't compress it. In fact, it's actually gonna generate something. That's a little bit larger and there go We're not gonna compress it at all which led to the adoption of external as the default storage type in the newer versions PG vector which you'll store it in the toast table, but it won't attempt to compress it So we'll save some CPU cycles because again every bit of CPU we can save we want to save And finally there's main which just for completeness. It's here not super relevant to vectors Now why am I spending so much time on toast because it can't impact your performance? so here's a You know postgres has had query parallelism since version nine six and it's a wonderful feature and particularly if You have to do a lot of these vector computations in an exact nearest neighbor scan you're going to use it So I had a data set. I think it's a million. I can't read it's other a million or 10 million I can't I think it's a million, but it's like hey, you know You know do an exact nearest neighbor scan on a million 120 dimension vectors cool. I'm gonna give you six parallel workers So let's do the same thing on 15 dimension vectors and it says cool. I'm gonna give you four parallel workers That seems wrong right like wait a second these this is much smaller Why am I getting only four parallel workers here? And that's kind of like a gotcha because these vectors are toasted when postgres is doing the query planning It's basically seeing you know the toast table is actually a separate table So it does the query plan it looks at the main table It's like there's not that much data here because yeah Because like all the data is in like a different table and it's not considering that in the query plan so That's a gotcha and like we need to make some plans around that because One of the big differences between this and how toasters originally used is that the vectors in your hot path of your query Like it's being used you know for serious workload So there's some ways around this to make your life easier One is use plain storage if you know that your vectors not going beyond two thousand dimensions You can store it in line You know just make sure it makes sense with the rest of your queries because that is a lot of data The reason that we use toast is to speed up Queries that are in your main table so but if your vector if your vector query is you know The resin to etcher or the reason that you have that table it may make sense to keep it in plain storage Also right now PG vector only supports up to indexing two thousand dimensions So plain storage may make sense for that You know we'll talk a little bit about that that other point layer dirt or there's this parameter called Min parallel table scan size which can help induce more parallel workers So in that previous example, you know, I kept my vectors toasted and I set Min parallel table scan size to one and look 11 workers a lot more which is good because we can definitely benefit from a lot More workers in this case so The reason why you know, I spent so much time on toast is you know going back to my roots as an app developer This would not be something I would think about at all because again most things in Postgres it's like set and forget and just works but because this data is a little bit different and You know because of like the size of this data Like it helps to understand these techniques to make sure that we're optimizing our performance And there's probably a discussion of how do we make this simpler and it is a fair point And I think that's you know something I'm personally interested in helping out on So let's get into hnsw So hnsw stands for a hierarchical navigable small worlds say that ten times fast And as I mentioned the idea is that it's a it's a graph But a graph is just you know specialized tree Oh, sorry It's a super set of a tree and the idea is that when we search through things We're going to search through a vector space where we start with a very sparse The way that I could think of it. I thought about this the other days I think of it like a cake We have a cake of vectors But instead of vectors we are sprinkles and let's say I designed the cake where at the top of the cake I have a few sprinkles going down in and each layer I get more and more sprinkles till the bottom layer has like all the Sprinkles I'm basically tracing the sprinkles through the cake that like I find like the sprinkle I'm closest to here And I go down like oh, maybe it's here etc. etc. etc And it was actually kind of a very interesting algorithm It's been around for about seven years I think because it did it did basically help put folks or put like query vectors into the right space There's the key with hnsw is your build How you select your build parameters will ultimately impact how you're able to search over them There's two key build parameters here The first is M, which is the maximum number of bidirectional links between index vectors So, you know and think of M is like if I'm standing here. I'm being indexed I'm gonna build like links to like the 16 people closest to me in this room If construction is effectively your memory as you're going through and building the index that you're keeping a list of all The vectors you're closest to and ultimately you're going to choose your closest 16 Think of it as like your search radius Like this larger the search radius that you have the more likely you're going to pick the vectors that you're most similar to Now there's gonna be you know, there's trade-offs to this We're gonna see you know the different trade-offs in a few slides But instead of me trying to visualize myself as like a seven layer cake, you know Let me show you you know how in anime way of how a building hnsw index works So let's save a bunch of vectors and I have a you know vector. I'm gonna index that's an orange What happens during hnsw is you first start at the top layer You have something called an entry point and then you find the vector that you're closest to in this case It's this one what happens is then you go down to the next layer And there's a few more vectors and then you find you know You find ones that you're closest to and you might build some links along the way as well Because that's got ultimately help the search that happens and then you get to the bottom layer you might find a few more vectors that you're closest to as you build those links and Then there you go. You're indexed So again oversimplified example, but that's effectively how it works and again in order to do that It might take some effort because you're you know maintaining a list of like vectors that you see in you know in that EF parameter But when you do that work up front the payoff is in the query time So when you query it, there's one parameter you need to be aware of EF search And EF search is again similar. It's like how many of those nearest vectors are you maintaining in a list? The more of those vectors you maintain the more likely you're going to return a highly relevant search But there's gonna be a cost because you're maintaining a larger list that you have to look over That must be that value must be greater equal to limit by the way So if you have like limit 50 EF search 40, you're only gonna return 40 results So you're not getting everything that you need to see in the limit I think a newer version of PG vector put in like a check or a guard against that But I don't remember off the top of my head and search is very quick The idea of the search is you go you start at the top layer you find your entry vector And then once you go in you're gonna find the vector that you're closest to in that layer Go down to the next layer same thing, you know, I started there. I'm gonna find the vector. I'm closest to Go down to the final layer and then you know, I'm gonna traverse the graph and I find the vector I'm closest to cool very quick kind of feels like a bee tree search and in many ways it is You know a graph is just you know a larger tree So there's a few best practices and I'm gonna say these keep evolving and I think that's like one key point with vector searches Is that this is like a very rapidly evolving field? Even like when I first wrote this talk some of the guidance has changed you guys You can see about the the second and third bullet point the nice thing We did a lot of testing on the default values and like the default values usually work Though we're gonna see that you're based upon some a recent PG vector release We can probably crank up if construction a bit The other thing I just want to mention as well is that you know not all h and s w is equal Different vector database has have different implementations of h and s w some might use a library like feist to be able to Perform the algorithm and you're stored to disk PG vector actually implemented h and s w itself based upon the paper and actually chose a different version of the algorithm Then some of the other implementations This is actually in part to make sure we could optimize it for postgres storage Because what you'll see with like some of the some of the the query performance from h and s w like it does depend on How many times you have to go to disk how many times you have to go and retrieve pages from disk? The more that you have things clustered like on the same page the faster it's going to be because that's gonna be less work Overall as you're searching the data that doesn't mean like you know there aren't some other techniques We can do to help speed things up Particularly building indexes With PG vector zero five one you want to use this concurrent insert method where you'd start with it empty index and then Either do concurrent inserts or copies because that was gonna be way faster PG vectors zero six zero added the ability to do parallel build so you could preload your data and then do a power you know use parallel workers to build the index and You know based on upon some early testing that's looking to be a little bit faster than the concurrent insert method I will note that there was a commit earlier this week. That's targeted for PG vectors zero six two That's go work much better for very large data sets I was actually trying to build a a billion data set with like 128 workers and I was noticing like oh geez This is not going as fast as it could so Andrew Kane who's the you know the primary maintainer of PG vector Within within a few hours found it you know found a locking contention issue and solved it By the way, Andrew Kane is an incredible job with PG vector. I just you know want to note that for the record record But yeah, so I would say generally though the parallel build if you're able to leverage parallel builds. It should be a little bit faster Now this is also updated some guidance So you know as mentioned With you know, it's all for HSW. It's all about your build parameters and what you choose and before PG vectors zero six zero You know, I would say that you know, for instance on this data set It was like over a million fifty hundred dimensional vectors We start seeing diminishing returns on recall You know as we cranked up EF construction that one of the reasons why the default was good enough is like you're getting pretty good Recall that level. It's already point nine one like do really really need to spend a lot more time building the index to get point 93 I mean this is a difference of hours here but Re-running the same exact test Using 64 parallel build workers on a PG vector. Well, I think PG vectors zero six two one released Notice that we still kind of see the same degradation, right? Like, you know, we're not getting you know, you're going from like maybe like point nine one We call the point nine three But the time axis changed significantly like we're talking about minutes not hours and that's big right because like even if I'm still seeing the Same performance degradation, but I can measure it in minutes not hours That's an argument to say like hey like let me use EF construction to 56 instead of 64 Because I will get slightly better recall and the cost of getting that recall is not that bad So that's why this is I will say like this is a rapidly evolving area But we're definitely seeing you know performance gains and it is helping us to update guidance And just in general if you can build more Acre I might use the term accurate machine learning folks I like why use accurate here if you can build more accurate indexes though It makes more sense and again M is a parameter that helps with that as well that we can see you know This was the the just 960 dataset which tends to be Most most approximate nearest neighbor indexes don't do well with it But you know the parameter and which is the bi-directional links can help increase recall But notice like the time penalty you pay again like we're talking about hours, so You have to decide what's the most important to you when you're building this that are you looking for better Recall and you're willing to pay the upfront cost of building the index or do you want the industry to get built quickly and You'll deal with the fact that you might not necessarily get the most relevant queries though again relevancy may make sense for you So that's it in a nutshell for hnsw You know you have a few lovers building is the most important thing You can always increase hnsw if search increase recall, but there's gonna be intention it's going to decrease the performance of your queries and The more of your data you can fit into memory the more that you're able to The more that speeds up queries right like the less trips to disk generally the faster things are so at least I'll be able to keep Your index in memory Ideally if you can keep your index and table in memory that'll be the fastest But at a minimum you want to argue the index particularly because those could be that's gonna be the fast path So I'm gonna go through IV a flat real quick So IV a flat as mentioned it was a clustering algorithm It's basically decided Based upon the number You know they call it list other people call it centers You can call it buckets, but the idea is that you ever you already have loaded your data set and you're going to find Blist of vectors that you're gonna group everything around So for example, let's say I have you know a bunch of vectors in space some of the previous example And I say it you know I want list three. So I'm gonna find the centers and I'm assigned vectors to the lists Then when you search over it, you have this value called probes So if you set IV a flat probes to one that basically means that you're going to search one center And in this case, I want to find the three closest vectors to me So if I searched just one center, these are my three closest vectors But if you eyeball this you're going to say like wait a second like those aren't actually the three closest vectors Like these are the three closest vectors. That's correct And what you have to do is to get a more relevant search you have to increase the number of probes and That case when you increase the number of probes you actually do find your three closest vectors Now that's the trick with IV a flat is that you often have to increase your pro value to be able to get the results That you want, but it's gonna come at a cost and you know from what we've like observed it tends to be more linear Versus you know more of like a gradual like logarithmic linear, which you might see in a hnsw So there are some perform strategies, which is you know if you want to increase recall increase IV I thought probes But it does decrease performance I think that's been the general complaint with IV a flat that often you know based upon how things get Clustered that you do need to increase probes and impacts performance But it's kind of like you can actually have an IV a flat Index outperform an hnsw index because if you're able to get your list small enough and you actually get you know the The most relevant clusters you're going to outperform hnsw because it's good just be like very very quick queries But again, you know, I think the trout becomes like do you want to be spend your all your days?  So it depends where you want to spend your time You can also induce indexing queries by lowering random page cost again as an app developer Like I had rarely looked at costing parameters in Postgres until I start looking at vector indexes And finally, you know you can set shared buffer or sorry not finally but you can set shared buffers again to keep you know as much in memory as possible and If you're looking over a lot of lists, you might want to increase the work memory parameter on a per query basis So work memory is basically how much of your working set you're going to keep in memory before it spills to disk because If you spill to disk there's going to be a penalty to that because you're pulling things You know in and out of disk to be able to perform operations So let's say like you have 10,000 lists and you set probes to a thousand You might exceed Postgres default working memory and you're dealing with that swap going between memory and disk So best practices So a lot of these I took actually from the PG vector repo in terms of how you choose the value of lists There's an art and a science to it The thing to keep in mind with IVF5 is that as you add and remove vectors from your data set That could end up skewing your centers Which means that you have to rebuild it to get queries that have the highest amount of relevancy But you can use parallelism to accelerate build times and just real quick You know the way that it originally worked in PG vector was that when you were doing the list assignment You would do a sequential scan on the table basically read every single vector one by one So imagine you have a million ten million a hundred million. That's going to take a while With parallelism you can basically break that up and have a bunch of parallel workers do the list assignment Which in this experiment, you know, I saw a 2x improvement It was a a million seven hundred six eight dimensional vectors and sometimes I saw up to a 4x improvement So again speed is good if you can build your indexes more quickly generally up. That's a good thing filtering Filtering I think has been the has been the hot topic for the past let's say three to six months in general for for vector databases and You know, I kind of chuckle again as a you know with my database hat on because This is what this is what postgres does you you put a where clause on something you filter everything out But it gets a little bit weird with vector queries like everything with vector queries It gets a little bit weird So if I add a where clause the first thing might happen is postgres chooses not to use the index and it's like fine I'm gonna do sequential scan So suddenly when you thought you had a query that was returning in milliseconds You have it returning in seconds because it scans the entire table and then does the you know the field You know, well, maybe it filters everything out and then it you know, you know, you get the result of your query It might you it might then end up using a vector index But you might not get enough results because your index might be on your entire data set now in the filter And it's doing the search based upon what vectors are your clue your closest to well Those vectors might not have the filtered attribute on them you know if if your cat you know back in this example if your category ID is seven and I return a bunch of vectors where a category ID is six all those are gonna get filtered out and I'm not gonna get the results They want or the filtering may occur after using the index There's actually one more thing that I need to add in here Is that you might end up using a different index to do the filtering like a bee tree on category ID and they do the Exact nearest neighbor search after which actually might be totally fine because if you get say like a hundred results back Just do the exact nearest neighbor search That's gonna be you know super fast and you're gonna get you know the the relevant results that you want And You know I think that's you know I think that's what it is I guess I got ahead the first point is that a bee tree might be the way to go here You might not need an an index and frankly like it's if you can use a bee tree.  Do you want exact results or approximate results? And you know that's on you to decide for what you want with your application So there's two filtering strategies to consider today There's the partial index another great postgres invention we're basically index over a subset of your data and That will give you you know that will give you that effective pre-filter where I'm looking at all of all the rows that are Assigned to category ID equals seven and I can get the approximate nearest neighbor search And make sure I'm seeing all the relevant results that I want to see Or I can partition my data if you have a clear partitioning strategy But just be careful how you partition your data and do an approximate nearest neighbor search because if you have multiple values that match your filter within the same partition You'll run back into the same problem we mentioned before because you're searching over all the vectors in that index and If you filter out too many that have the partition attribute You might not actually get the rows that you want There is a patch actually there's several patches Proposive PG vector right now to do you know the equivalent of multi-column indexing which is another kind of pre-filtering Which might simplify this from an app developer standpoint Not committed yet. We'll talk about that in a second, but There's definitely more work on this you know I'd say this is still one of the areas that is being rapidly innovated on just on vector databases in general It's not not just PG vector So last but not least I'm actually call this looking ahead a little bit distributed PG vector queries So I'm of so I'm of the personal opinion that this is still scaling pretty well vertically on postgres But there may come a day where we need to be able to do distributed queries where effectively If you don't have enough memory on a single instance to be able to efficiently do your vector searches You might want to be able to push that down to other instances and then do a final aggregate on a single instance Now when you get to that point there's a few things to consider right like one like that memory issue I'm discussing but also your network overhead must be acceptable because you are going to pay some latency here Finally you have to be able to manage a multi-node system like that's not trivial You know you especially in production you need to worry about availability backups monitoring Software upgrades etc. And there's definitely a multitude of ways to do that, but as I like to say more databases more problems Now postgres does make it possible to do this out of the box using something called foreign data wrappers So here's an example where I do the setup for a foreign data wrapper to be able to query against multiple vector databases Sorry multiple instances that contain vectors in them There's a whole discussion on security here, which is probably a talk in itself In fact, I gave it to I gave a talk on that probably like 10 years ago. So skipping that here and then You have to set up what it's called a foreign table, which is a reference to your remote table Now what did not show you was that I actually set up these tables on the different instances and each of them had like 2.5 million vectors in them So there's an additional step here, but I kind of want to show the layout here because sorry I should have gone back here There's two key things to add when you're setting up the the foreign server Which is you're saying like hey like these foreign, you know These foreign postgres instances have the PG vector extension loaded and they're capable of asynchronous queries Because you don't necessarily want to be able to create each a base one by one You want query them all at once and be able to get the result back So I did that and you can see like hey, I'm actually able to do the foreign scan But actually I'd say this is hot off the presses because I'm still tracing it down I wasn't doing a synchronous foreign scan so you can see you know based upon the timing that it was cumulative when I did the final The final search The other thing I'm testing right now, which actually is looking very positive It's like this is impact recall and the answer so far seems to be no You're able to do this and still be able to maintain your recall targets, but Ask me later in the year possibly in May at a peachyconf.dev 2024 So the the thing I'm tracing down is like can we do the async foreign scan It might have to do with an append node versus a merge append node, but to be determined So looking ahead what's on the roadmap for PG vector Well, as I mentioned performance improvements for massively parallel hnsw builds that has been committed very much looking forward to that So this is the efficient pre filtering to be determined what method is used There's a patch for HQ ANN to be determined again if that ends up going in But again pre-filtering is the hot topic right now Allowing more data types per dimension, you know two byte floats one byte unsigned integers And what's cool is that there is proposals for how to do this right now in PG vector And this allows you to do something called scalar quantization with expression indexes where you can have your full Vector stored in your your table vector column and then Do a cast to like a you know two-byte float in your in your vector index And the powerful thing about that is that that lets us expand the amount of dimensions that We can index with PG vector That's pretty cool excited for that another technique that'll expand upon that as well as product quantization again being explored What we found PG vector was again things were you know kept scaling vertically fairly well That didn't make sense to add necessarily the quantization techniques You know based upon the performance numbers we saw and frankly the comparable performance numbers we saw and you'll being able to add parallel query The nice thing about hnsw is that we really don't need parallel query IV a flat could benefit from from parallel query hnsw To be determined particularly as you expand your EF search Parallel query could have some benefits, but we haven't really seen much there yet so in conclusion you know like JSON a vector is just a data type and There's all sorts of like different storage and search techniques to consider around it but fundamentally it's a data type and Traditional databases like Postgres are very good at doing these things with these data types as You add vectors into your applications should you so choose?  Like do you need super fast storage or do you want something that's a little bit slower? You know based upon like just the pure size and volume of your data set Do you want to get you know the largest compute instance to be able to like run super fast? massively parallel queries and index builds or are you going to keep something that's a little bit smaller and you might feel the fact that you Do it need to make fetches to disk and then you have to pick your indexing strategy. What makes the most sense? It tends to be you know right now HHSW is seemingly the default that people are choosing But you have to decide what makes the most sense for you and finally this is rapidly evolving that you have to plan for today and tomorrow You know PG vector is pretty mature of I've already seen plenty of production deployments I've also seen a lot of kicking the tires, which is completely fine because I think people are kicking the tires on generative AI in general But you know there you do you need to you do you need to plan for the fact that it's rapidly evolving? Like I think the the foundational performance features are in PG vector minus like one or two and then from there It really is off to the races. I think we're gonna see you know even further innovations from that so Thank you, and we have a few minutes for questions Any questions? Are there significant differences between postgres as opposed to like chroma or pinecone that are worth mentioning? So I'm not I'm not trying to do like a This one I'm not gonna try to do like an apples to apples or apples to oranges comparison between the different databases But you know I think I'd say with postgres you know postgres just has you know 40 year development almost 40 year development history Really you know first starting with like how do I store the data making sure that I can store it safely It's acid compliant and you know there's a whole tooling you know there's a lot of tools already built up around postgres You know the other thing is you just get everything else with postgres I mean if you want to like build like geospatial time series vector database like all in one like that Something you can do it just based upon all the the postgres extensions that are out there Hello Can you please give an example of how to convert attributes? Let's say in an e-commerce site of a product into the dimensions and also Do all of the vectors need to be the same size the dimensions the same order? What happens if some attributes are missing from certain? Records, so I didn't understand the first part of the question But for the second part when you run when you build an index All the vectors within the index have to be the same have the same dimensionality You can have you can store mixed vector types within it within a postgres database even within the same column But then when you build the index you have to make sure all those vectors are of the same dimension So the first one was you showed in the example how you have dimensions of 0.5 0.5 0.5 Yes, how do you get to those numbers from the actual record that you're trying to compare? So is it like if I want to like introspect those numbers directly or is it like how did I get? How did I make up this number? Yeah, so that was just an example Contrived like what's happening with the generative AI models that I have like some text like You know vectors are the new JSON and I take that put it the embedding model And it's going to generate the the attributes But that's not that's basically about how the embedding model is defined and how it's able to do that That's on that's on it Just final question. Yeah, and I can answer questions outside after. Yeah, I was just wondering With your rag diagram When someone query the database in the diagram it also insert it back into the database I was wondering when you insert new data Do they have to rebuild everything for the vector or? So no One of the so even with both IVA flat HNSW you don't need to rebuild everything when you insert it And in fact with Postgres it's it's an online insert So as soon as you insert the record into the table, it's automatically get inserted into the index now IVA flat and HNSW have different properties around the insert because HNSW is iterative it's kind of like building a traditional B tree index where you insert it and You're gonna put the vector in its highest probability space of where it occurs with IVA flat Because it's cluster based what happens is that you will insert it into a cluster But over time your centers are going to skew So the centers you have represented are not necessarily the true centers anymore and that case you might want to rebuild in order to get better results Thank you Thank you. Thank you everyone. So the next talk is about Postgres 17 in this room in 15 minutes Yeah, is it on yes But yeah, we're gonna stand there and hold it. You can stand there and hold it Okay, I think it's time to start Okay, thank you. I'm done Well, at least this microphone works Those who saw the previous talk as well. There are some challenges with the other one But you know, there should be some technology challenges So I'm expecting my slides to give up about halfway through no just because they can just because you know the possibility exists Well Welcome here to all of you. My name is Magnus Hagander I work for a company called Red Bill in Pro. We're an open-source company in Scandinavia based in Stockholm in Sweden Myself I'm part of the Postgres core team. I am one of the committers who Will see I don't think I have a feature of my own in Postgres 17 yet But you know, it's not too late and I currently serve as the president of the board of Postgres Europe But We're here to talk about Postgres 17, right? Who's already using Postgres 17? Okay, good. Who's using it in production? Very good. How's it working out for you? Perfect So yeah, I post her 17 is at this point still the upcoming version of Postgres, right? The currently just and greatest that is supposed to be used in production is postgres 16. Who's on postgres 16 in production? Okay, let's see 15 14 See so far the numbers are going down. I'm worried when this trend is going to change 13 12 11 Okay, now we're getting into troublesome area 10 Earlier than 10 Okay, well, I don't need to tell you that you should fix that You probably know that already and and if it was easy you would have done it But yeah, as of today the latest supported version of postgres is 12 And the newest supported version of postgres is 16 17 is not yet supported, but 17 is the upcoming version, right? And the way that we work in postgres when we build is that we actually started postgres 17 in June or last year by branching postgres 16 Because the way the development works within postgres is we do all the new feature development against the master branch and then when we feel it's you know nice and ready and Stable and the new one we create a branch in this case. We call it release 16 stable and that's how you magically make your software stable Just create a branch name with a stable in its name and you should be good to go But you know jokes aside what that means is the moment so in June 2023 we branch We created the branch called release 16 stable and what that really means is You're not allowed to put any new features onto that branch anymore It's now bug fixes and maintenance only and the master branch the main development branch gets opened up for building new features And then starting adding the features that will eventually bring us to postgres 17 Then in postgres we work with something that we're called commit fests It's really just our way of doing iterative developments You can call it whatever you want, but we chose the name commit fests because nobody else had used it and of course we wanted our own name But the idea is we spend one month building and contributing features to postgres Then we spend one month reviewing and committing them onto the main branch So the idea is the first commit fest for postgres 17 was in July that would then If there would be things built in June and if it worked all the way through then we had another one in September We had another one in November. We had one in January and we are currently in the middle of the fifth commit fest Which is for March which will be the last one So the way we do it, you know these two month cycles until March and then we just stop So at the end of this commit fest 5, which is currently in progress when that one finishes That's the feature freeze for version 17 It's a very long commit fest We're gonna get so many features in Yeah, I probably did Because I kind of copied that and added one to everything So no it is it is March 2024. Yes And so and I think we were typically setting about a week a week to ten days into April is when the official feature freeze for 17 is Building on that we'll stabilize that and eventually get to a beta version usually around the May ish We'll see how many beta versions there will be we'll see when we get to release candidate But the general target is to try to get a release out in the September to October time frame as Usual as you know if you've been around postgres for a while That's when we usually try to get the new major versions out But that also means that as a disclaimer where we are now today may be completely different from where we are three weeks from now We know that nothing will get added that nobody has submitted yet But there are hundreds of things in the queue that we don't know which of them are gonna make it in So this will be a preview and the same thing of course right up until release any of these features can be removed If it's determined that you know, it's not good enough. It runs into a corner case that cannot You know reasonably be fixed within the time frame of the release This has happened a few times around it happened in the last version that some things were removed After the beta version was out and then they got removed again and some of them are back now in 417 So the almost current status that we're at because I think this number is from yesterday and you know For some reason there's not a freeze around when I do these presentations So it's hard to keep up and I updated this sometime last night not this morning But at that point we were looking at in this case as you can see you know as we all know these are the best ways to measure developer productivity We were at 1600 commits with about a hundred and fifty thousand lines of new code and About half as much deleted So where what is this then what kind of features are we what are we looking for? For those of you who've seen my presentations before you see I usually divide them into a couple of sections just to have a little Areas we got DBA and admin got sequel and developer Back up in replication and then we'll finish off about talking a little bit about performance because everybody loves performance And there of course every postgres version has some interesting stuff, but let's start with breaking changes Right. What are we breaking for you? Hopefully not that much, but probably something actually I'm willing to say hopefully something Because if you're not affected by any of this then you are not doing the things that you should be doing But probably not the first ones which is for example We have a different way of building Windows builds now you how you can no longer use our manual really ugly hacked up scripts You have to use me son That's a good thing We dropped AIX support who used who uses postgres on AIX Who has ever used postgres on AIX? Who has ever used AIX? Okay, there we go. At least something right And we've also removed this is one of those, you know double negations. We've removed the ability to remove thread safety I don't think anyone turned off thread safety for many many years So these things should hopefully not mean that much to you a couple of other things that you may have been using the admin pack Country module is gone All the functionality in that module already exists in postgres core But if you had code or if you had modules that were using it you just have to change them to use the functionality That's already in postgres core Has anyone ever used db user namespace? It's a configuration parameter that you can turn on Okay, I'm glad basically the idea behind this one was you could Have different users in different database in the same instance The minor drawback was you could no longer have encrypted passwords at all everything was clear text So I'm glad you didn't use this And this is why it has been removed. Did anyone use the postgres snapshot to old feature? Did you have a good experience with it? Yeah, it had far too many quirks and it didn't really work very well, and it was getting basically unmaintained So it's gone Okay, these are things that I was hoping you weren't using has anyone ever read from PG stat BG writer. I Hope all of you have done that Maybe not directly but made with your monitoring tools So a few things are gone the columns for checkpoints time checkpoints requested right time sync time buffers checkpoint back and Buffers back in and back and f sync are all gone from this view. So all your monitoring software will break. Sorry What it's going to mean, of course is that you need to upgrade your monitoring software to a version that supports version 17 But most monitoring software will be reading from this view and it's vastly different in this version The other thing that I hope is not going to break for you, but it might is That the search path Variable in postgres is now by default secured in all functions called in maintenance operations.  Well, the basic idea is there are a few other covers, but the simple case is if you have an expression index, right? You have an index on the result of a function When that function by default now when that function is executed postgres will reset the search path variable to just contain PG catalog If you need that function to be able to call fun other functions That are not in PG catalog You have to explicitly change set the search path on the function otherwise it will stop working Right because it will not find the functions Previous versions of postgres would use whatever search path you were in at which point the maintenance operation could become very It's very easy to build yourself a security foot gun Where you could basically allow code execution as other users by placing things in different schemas and stuff So that the hard code thing is if you don't do anything Everything will become secure because it will restrict it to PG catalog and regular user can never put functions in PG catalog But it also means that if you are using expression index on your own functions that are calling out You will need to go and explicitly set The search path for them. Hopefully you were already doing that In reality, you probably weren't And now you will so if you are using again expression index on Custom functions that call out to other functions. They will likely break. You just need to investigate it Unless you're doing something really dumb Which you shouldn't be doing then all you need to do is to do the you know alter function set search path And it will work again, but you've just changed what the hard coding value is So these are basically the breaking changes I found so far. Let's talk about the new stuff instead Right, you didn't all just come here to you don't upgrade to 17 just to get things broken, right a Little bit. I mean some things are actually good Some of these breaking things are good, but on general no So let's start talking about the area of DBA and administration We'll start with a small one that I think people are using it are gonna like it a lot who's using Let's start by who's using psql? Surprisingly few actually do you know that in psql you can do this backslash watch Which is the same as as watching you need like run this query over and over again and show me the result The new thing here is you can just give it a parameter It says when it stop when it drops below So if you run your query and you do this, you know backslash watch MXl2 it'll run until the number of rows returned is less than two and then it'll stop So it's kind of neat if you're pulling some sort of a background job who goes Who's ever done that you know pulling you know PG stat progress vacuum put it on the backslash watch Vacuum finishes and then the interesting data starts scrolling off the screen and out of your Scroll back buffer before you notice. I've certainly done that So small things very useful Other things that I think are going to be quite useful, but but are fairly small is there is yet another time-out We've had many many time-outs in postgres you have statement time-outs and session time-outs and idle in transaction time-out And now we have a transaction time-out It is really what it says if you set transaction time-out to one minute No transaction is allowed to run for more than one minute regardless of what it's doing Right a statement time-out applies to the individual query, but this one you can say One minute for a transaction whether it's a million queries or one query. We don't care stop after one minute The typical user or a typical use case for this can be if you have you know You have a classic web app you got a web request coming in you're gonna time out that web request at some point anyway And then the queries are gonna keep running But setting a statement time-out for that is really hard because you might have multiple queries Right now you can just match up this time-out to set it to you know a second or two longer than your web server time-out And then it'll just clean up the old queries when there is not going to be anyone listening for the result anyway All right, excuse me if you can you define this on a per user basis? Yes, it's like the other time-out sessions. Yes Can you change your procession? Yes, you can change it pretty much everywhere in the same places as the statement time-out Who's using event triggers in Postgres? Okay, not too many so event triggers are you know the triggers that normal triggers trigger based on data event triggers based on well events But you can have them fire on things like create table You can have them fire on DDL and log it or prevent it and things and At the simplest you can now also they fired those you can create a trigger whenever re-index runs and make things happen and The the more debated one is you can also have a login event trigger Which means you can now get a trigger that runs when a user logins to the system or as we like to call it a footgun extraordinary You need to be very careful if you write one of these because if your event trigger crashes for example Nobody can log into your database including you so you can no longer fix your event trigger so Yeah, I mean Actually, you can because you can also know there is a global parameter We can just globally turn off all event triggers so that you can get in and fix your login event trigger But yeah, it is you you can do classic things like if the event trigger writes anything. No, believe it. I will be Sorry, nobody will ever be able to log into one of your standbys again Because the event trigger runs it tries to write you can't write anything on a standby so it crashes So you can do very good things with login event triggers and you can do very bad things It is a big footgun, but if it's used right it can be very powerful You can do you can get connection auditing at a level that the built-in one isn't enough you can add policies of You know who can do what we can log into what assigning roles at login There's a whole bunch of things you can do but be very very careful Because again something goes wrong and it goes wrong for real and you're stuck Hopefully everybody by now has learned the idea of wait events if you went to Jeremy's talk yesterday He did a good example of why you should be looking at your wait events They've been around in Postgres since version 10 we renamed all of them a couple of versions ago just to confuse you And now we renamed them then but only now did we actually create a new view called PG wait events that will actually tell you what they are So when we rename them the next time you can find them But now so basically you now have a view that's called PG wait events It's really is you know the event type the name and a small description so that you can find it Obviously for the version that you're in and one other important reason for this one is that wait events can now also Get custom wait events for extensions Previously there was a single wait event for extensions that just said it's waiting on an extension But as of 17 an extension can register their own wait events which will then show up in this PG wait events So that you can see what it's waiting for inside of the extension as well Another thing that I always like about Postgres We had someone mentioned was it in the AMA yesterday that you know the new the statistics features Always good new statistics features in Postgres. We try to bring them to every version We have new interesting parts to the statistics system in version 17 as well Well as I'll just bring this one right back. We also broke this as I just mentioned right We removed a whole bunch of columns from PG stat PG writer when you think about it None of these statistics are actually about the background writer and that's why they were removed instead We have a PG stat checkpoint where we put them back. So it's not that we took the numbers away. We just move them So you can't find them But the other more important part is as you can see in this example here You've got these numbers of time then requested and your times and you see restart points now get separate statistics Restart points in simplified being the checkpoints that run on a standby or the checkpoint that run during startup how many of those are happening and Splitting those apart into different values. So the old value is BG writer Background writer statistics are still in the BG writer view and checkpointer statistics have basically been moved over to PG stat checkpoint So again, if you have monitoring tools that are using these then that's the way to go some of the most of the The other things that were removed here these buffers Are moved into the PG stat IO view where they were added and Or in 16, but they were just not removed from the BG writer view at the time We have some additions to PG stat statements, which I'm hoping you're all using PG stat statements or have a really good reason not to One of the myths that you can get the local block IO statistics per query So the local cache in the back and like temp tables and stuff We didn't have the IO timing for it. Well, now you have those local block read time and write time And you get the entry time when a query was first recorded by PG stat statements in its current entry So the first time will go in in the stats since Now this can be a query as you know PG stat statement tracks a certain amount of statements or a certain number of statements and A query might leave the statistics and come back Right because it's no longer in the top five thousand or whatever you have and it will have the same query ID But it will then get a new Statistics since since at that point the statistics are lost There is also a min max stats since that's related to the fact that you can now choose to reset all the Statistics for a query except for the minimum and maximum time So you keep the minimum and maximum times separate from the other statistics and at that point these two numbers can go Two different values if you're not explicitly doing this reset yourself at different times They will always end up showing the same thing the query jumbling as it's called in Pg stat statements now properly normalizes call if you're using actual stored procedures in Previous versions if you called in this you know you can see I didn't have a stored procedure in my set test database So it's just dummy But basically now it can identify that you know the parameters are actually parameters and normalize them to dollar one dollar two Whereas in previous versions, they would it would see every call statement as a different statement when it had different parameters Which can be very annoying Because you can rapidly explode and be like half of your Pg stat statements or just different versions of of a call statement Pg stat vacuum progress Now shows index progresses So when you are vacuuming a table, it will not tell you wit