Ballroom G Friday Mar. 06 - SCaLE 23x

In this talk, Magnus Hagador discusses the latest features and improvements in PostgreSQL 18, the current stable version of the database system. He breaks down the new functionalities into categories for database administrators and developers, detailing enhancements in performance, security, backup, and replication. Key features include the default activation of page level checksums for data integrity, improvements in the vacuum process to optimize storage management, and support for UUID version 7, which aims to improve index performance. The speaker also touches on authentication updates, such as the deprecation of MD5 and the introduction of OAuth bearer token support, which enhances security for login processes. Overall, Hagador emphasizes the readiness of PostgreSQL 18 for production use, encouraging attendees to upgrade from older versions and take advantage of the latest advancements.

Let's see. Does this work? Wow. Technology works. Even though the tech people left. You'd think they saw something didn't work and they were like, "We're out." But it worked. Wow. Good morning and welcome to I guess the second day of scale. Uh and uh the what are you doing over there Dev? It keeps going on or up and down. Is it good volume for everyone or do

you Okay, good. Yeah, there is. I think you need to. >> Let's try. Is it better? >> That's better, I think. And we still have this working. See, that's what happens when you have technical people in the room. Usually, there'll be like five of them around there, everyone trying a different thing. But this time it worked out. So yeah, welcome to this uh second day of scale

and the second day of the uh Postgress tracks and my presentation today about what's new in Postgress 18. Um who was here last year and saw me give my presentation last year. Okay, there's a couple. So that was what's going to be in Postgress 18 and this is sort of the the quiz version of what actually ended up. So um you are probably going to recognize uh

quite a lot of what I said last year because again at the time of March uh we mostly knew what was going to be in Postgress 18. Uh there is something I think that was in there in March that didn't make it. Uh but mostly it's going to be a little bit of repeat for you guys. So uh sorry about that site but they did want me

to do the the 18 again which is the currently released version not the whatever is coming in half a year from now. Um, so a couple of quick words about me. My name is Magnus Hagador. I work for a company called Redpill Linro. We're an open source services and consultancy and and things like that business in the Nordics and I'm out of Stockholm in Sweden. Uh, where

I lead up our database effort which is focused around Postgress. Uh, within the Postgress project, I'm a member of the Postgress core team. uh I'm one of the postgress committers and I am serving on the board of Postgress Europe which is uh sort of the European organization that does events like this in Europe and then we have the corresponding US organization called you know unsurprisingly United States

Postgress uh that are handling the postcrist tracks here at scale for example and do similar uh kind of events over here in the United States. So Postgress 18, now that it's actually been out for a while, who is running Postgress 18 already and is just here to find out what you're actually running. There we go. And who's I learned this to who's running Postgress who's not running

Postgress at all, stuff like that. Oh, okay. That's a couple of them. Well, this will hopefully give you some input on why you should be. Uh, so what about Postcrest 17? Okay, 16, 15. 14 It's starting to get dangerous. 13 seeing like people are still keeping their hands up. They're a little bit embarrassed, but they're keeping their hands up. What about 11? What about 10 or earlier?

Here we go. Let's get a couple of people. um hopefully not by your own choice but either by it's embedded in something or it's the only thing supported by you know version whatever as you know Postgress does keep a uh fiveyear lifetime on major releases uh so yeah if you're on 10 or earlier you are entirely and have been in unsupported mode for quite a few years

by now um and I know that uh you know going with the clients that I work with that it's not uncommon to have to be end up in this position where you have a third party vendor that has a system that only supports a version of Postgress that isn't actually supported by Postgress and then you kind of have to choose your poison and which one you go

with and of course the the deal with Postgress it it new bugs don't suddenly appear just because it goes unsupported it's just if you run into something you're in trouble because nobody will fix fix it for you or they will fix it for you and then charge you lots of money for that fix uh if you're on the unsupported versions or you can fix it yourself and

then you just have to hire someone who has the capacity to do that. Uh but let's talk about Postcrest 18. Oh, hope that wasn't me. So Postcrest 18 again this is um as we've seen the development schedule. Postcres releases a major version every year. Uh Postgress 18 started work in July of 2024 and it reached the release in September of 2025. So about half a year ago

uh is when Postgress 18 was released. Right now uh the ongoing work in the Postgress developer community is primarily on Postgress 19 which will follow a similar schedule. Uh we are uh right in this March commitfest just started for uh Postgress 19 and we're hoping to have a Postgress 19 out in September or October. We'll see about that but that's the goal. uh and then you know

next time we'll we'll just update all the years and give you the new features and that but today if you are deploying Postgress today the latest version that is you know production quality or production ready uh is uh postcest 18 again it's about half a year old now so it should be fine to deploy it's like there shouldn't am I going to say there are no bugs

left of course there are bugs left there were bugs in old pieces of software including postgress tan earlier uh but uh so today If you if you don't uh if you're building a new system, build it on post 18. There's no reason to use anything uh older than 18 at this point. Uh so let's talk about what we actually added uh in Postgress 18. Uh I usually

again you've seen this before some of you you might have seen my uh talks about other versions earlier versions of Postgress. Uh I try to divide these new features into the groupings of DBA and admin and SQL and developer. uh the only real difference like sometimes I ask like what's the difference then between a DBA and a developer like where do you draw the line and for

your sake I hope your organization doesn't draw a hard line there and you know these should be in many ways the same people they would all working together but I just draw the line look sort of SQL and developer things that are SQL and what I put under DBA is things that aren't SQL that are more you know outside of of the SQL interface then of course

we got backup and replication uh it's always got its own little separate section. And finally, we're going to talk a bit about the things that have been done for performance because, you know, everyone loves better performance. Almost everyone loves better performance unless you're, you know, a cloud salesperson and badly performing software is great because you sell more instances. But before we get into that, let's go into

breaking changes. There are a couple of things that will break your things. Hopefully, these first ones will not really be a big thing. Uh if you are building Postgress yourself, well first of all you probably shouldn't be doing that. You should be using the existing packages. It'll make your life easier. But if you're on weird or uncommon platforms, there might not be packages. Uh for example, Postcross

18 removes this support for HPPA. So the PA risk platform, you can't build Postcross on that anymore. Does anyone have an HPPA machine? Has anyone ever seen an HPPA machine? Oh, that's at least three people in the room who has seen one. Yeah, this is not, you know, super modern. Um, we got a lovely double negative. Postcress has removed the support for not having support for hard

for spin logs. It's like, yeah, this this should affect no one. Uh, is we've support for atomic operations in your CPU. It's like, does anyone have like a I don't know 486 somewhere or something like this is old stuff. This really shouldn't affect anyone uh running postgress seriously anywhere. These should these are just you know basically code cleanups. Uh what could unfortunately it should also uh affect

no one but this one will affect some people. Uh there is no support now for OpenSSL older than 1.1.1 that this is old but anyone who's been looking at these systems particularly where people end up building things themselves know that all things on these systems are usually old. Uh so I've seen some people who who are building who had you know their builds started failing when Postgress

removed support for OpenSSL older than 111. The solution is to upgrade OpenSSL like this is very old and insecure OpenSSL. If you're on that you really need to upgrade and and you might have other services that are using OpenSSL and like get them upgraded. So there shouldn't be really any breaking changes I think in postcourse 18 for sort of generic breaking changes that will affect applications that

are running on any sort of you know reasonable and common platform. If you're on a Red Hat platform, if you're on auntu, Davian, Windows, whatever like these are not going to be a problem for you. U so instead let's talk about the new things. Nobody go nobody upgrades just to break things, right? You do usually upgrade to actually get some new stuff. So let's talk about some

of the new things. Um I've been arguing for this one for about 10 years I think. Uh so I'm happy to say that Postgress page level check sums are now turned on by default. Uh they're turned on when you run your initb. So when you initialize a new Postgress cluster, if you give it no special parameters, you will have page level check sums turned on means that

if you get any level of disc corruption anywhere, Postcross will at least notice. It won't fix it, but it'll at least tell you. Whereas without check sums, if you have disk error somewhere, Postgress will just assume that that can't happen and that you trust lower layer systems. Now if you're running Postgress on top of say a check summing file system, then you don't need this and then

you can turn it off. You can pass d-node data checks sums when you're running in DB. But pretty much anything else you're running on your default really should your default should have been for years to turn it on because it is better to know when you have a problem. This was like this used to be really a big thing back when we had you know spinning rust

to store our databases on. We had corruption all the time and then we kind of moved to SSD and the corruption reduced a lot and then we moved into these massive virtualized sand environments or cloud drives and now the corruption is back. Uh in the early years of of like Amazon EBS like that was an excellent way to test this code because you got corruption. It was

only a matter of time. EBS is much better now. It doesn't happen on a regular basis. It can still happen. And I think to in my experience, what I've seen and working with my clients, the most common case of having storage corruption today is enterprisegrade SAN, which is kind of feels like backwards. That should be good. But that's where I see the corruption because they there's so

much advanced software in them. There's, you know, multi-sight replications. There are data dduplication. All of these things are running so low in the stack here that when there is a problem there, it shows up as a disk level corruption. And with check sums turned on in postcards, you will it will give you an error saying, "Hey, your disc is corrupt." It'll give you this early and hopefully

you will still be able to just restore a backup and still have your data, right? Please have backups. Um, so I think it should always be on as a default. I think that's great. If this causes a performance problem for you because it certainly might then it's very easy and cheap to turn it off. There is a utility in Postgress called PG checks sums. You just say

PG checks sums turn it off and you have to stop Postgress run this tool start postgress but it like you do this in a second like the tool itself runs in I don't know 10 milliseconds and you have a restart of Postgress. Turning on check sums is really expensive because it has to rewrite your whole database which is why having it on by default makes a lot

of sense because it's very cheap to turn it off but having it off by default u then it's painful to turn it on. Now the thing to remember is if you are upgrading Postgress using PG upgrade check sums are not on by default right they will be they have to match whatever you had before. So this is only for freshly initialized systems which means if you're upgrading

with pgd dump pg restore they will be on but if you're upgrading uh with pg upgrade they won't and then you have to go through this painful painful process. Uh speaking of upgrades, uh the upgrades have solved another uh long-standing pain point I'd say, which is the statistics, right? Uh has anyone never used PG upgrade to upgrade a postcross? Okay, that's very few at least everyone else

has used it. If you ever used PG upgrade like PG upgrade is a very fast way to upgrade Postcross. Uh but you've upgraded Postcrest, you start it up and your application performs like right? Because you have to run this analyze command. you have to run the vacuum db analyze in stages command and that can take 10 times longer than the upgrade itself and we need this for

the optimizer statistics. Now postcrist 18 will be able to transfer these so that you upgrade and you can start running the system immediately after the upgrade. So it's really just the pg upgrade uh part that will take time. Now the actual this is actually implemented in pgd dump. is not part of of uh PG upgrade. But if you are using PG dump and restore for your upgrades,

then performance is clearly not something you care about. Like your database is small enough that it doesn't matter. Otherwise, that's just so slow anyway. But this will make uh PG upgrade much much faster to use uh and to be able to to sort of get things back. you might you probably in some cases depending on what your data looks like might still want to rerun an analyze

after you've done it but you can open your application before uh you have to run that and run it in the background. Uh we have some changes around the authentication system. Uh embarrassing question but I promise I won't call you out too much on it. Who's still using MD5 authentication in their database? Uh so I know the rest of you are you're just not telling me, right?

Uh MD5 is now finally officially deprecated. What this means that every time someone logs into your system and you're using MD5, you will get a warning message in your log. That can very quickly flood your log if you're not especially if you're not using connection point right there. You can there's a parameter you can say MD5 password warnings off to turn that log warning off. The better

choice is to stop using MD5 and switch to if you're using passwords switch to scramshaw 256 which is better in every possible way. Uh and scramshaw 256 has been in Postgress since version 10. So every the thing the reason it didn't just switch over initially was you have to upgrade all your clients to at least Postgress version 10. But that's a long time ago and it's supported

in in all the other, you know, in Java drivers and Go drivers and like it's it's everywhere now. So you just need to basically configure it and turn it on. Do that instead of turning off the warning. Like it's a security warning. Don't turn them off. Postgress also now supports authentication you using OOTH bearer tokens. Now Postgress itself um doesn't it it supports this and there is

no token processing. So it doesn't know what's in a token. So you can't actually use it. Useful, right? Uh you you need a serverside provider that has to be written in C and Postcus does not provide a default one yet. But there are available if you just uh look it up. There is for example a provider available for JSON web tok to tokens that will so you

can use JWTs directly like all the way through your stack into the Postgress login. uh I don't actually know if it's been updated of anyone on yet but one uh common use case for this is likely going to be these uh you know cloud managed postgress that you can integrate with whatever login systems they have all the way through uh so it's kind of an infrastructure around

that but using this uh plugin that of course for the moment I can't remember the name of it but if you search for postgress ooth uh and JWT you will find it uh uh and uh will let you do ooth a authentication all the way into postcress Uh the scram authentication talked about use scram instead of MD5. Uh scram authentication now allows scram pass through uh for

the postcress foreign data wrapper and dblink. So if you're having a postcress server and you have a a foreign table on a different postcress server. We can now pass the scram authentication all the way through. So you don't have to like recreate uh user mapping that has a clear text password stored somewhere for the connection between the postgress servers. You have to turn on the parameter use

scram pass through on the server so that it knows to pass this on. This is obviously a choice of the server. this is this is a configuration parameter in Postgress that just turns turns the whole thing on and then you create a user map uh without the password and it knows how to pass the the what's it called it's scram identifier scram authentic scram something instead of

a password gets passed through now the requirement is this you have to have the same salt and iteration count on the two postgress servers but if I I don't think we've changed them too much there but there could be a version of postgress where you have to adapt these between them but there isn't now so for now it will just uh but again for postgress for data

wrapper and dblink not necessarily for other databases if you're using uh TLS uh postcris now supports uh the TLS version 1.3 cipher suites uh and it supports multiple ECDH curves previously we had a single curve and it was hardcoded uh now you can configure it. So if you have uh environmental requirements that says we need to use specific curves for example uh you can just configure that.

Uh one of the reasons of course you couldn't do that on this old version of OpenSSL that's no longer supported but you can on the ones we have now if you're using PG crypto depending on what you're using it for you might want to reconsider using it like people use it for weird things but it's good for some things. The big thing you can do in PG

crypto now is you can turn off all the crypto that's in PG crypto which which sounds uh like a weird thing to do but PG crypto can basically a bunch of the the common algorithms are implemented in PG crypto or it can use the one that's in OpenSSL. So with this you can turn off the one that's in PG crypto completely and only use the one that's

in OpenSSL. uh which again can be really useful for example if you need to be FIPS compliant because then you can turn it off in PG crypto turn it on in OpenSSL and configure your OpenSSL to be in FIPS mode and then no non-approved algorithms will become available and again today everyone has OpenSSL pretty much it they didn't when this thing was written I think if if

OpenSSL was as widely spread from the beginning as it is now we might not even have the option of having a built-in in PG crypto. It might all be delegated to OpenSSL. Okay, let's talk a little bit about everyone's favorite Postgress feature, right? Let's talk about vacuum and auto vacuum. Uh there are some interesting things done here. Uh for the tuning side, uh you can now configure

an autovacuum maximum threshold. So we need you know you need a couple more parameters every time. So the idea uh as you might know so the way that autovacuum triggers a vacuum is uh uh it has a uh threshold today if you look prior to 18 it has a threshold and a scale factor right and it takes the number uh so if the the scale factor 02

which is the default that's 20% it says when 20% of the rows of the table have been updated or deleted run vacuum and it has a threshold that says if this number by default I think it's 50 so it says if this number is less than 50 then wait until it gets to 50. But prior to version 18, there is no ceiling for this number. So if

you have a table with say 10 billion rows, uh vacuum will run once by the default config once 20% of 10 billion has been updated. So it waits for two billion rows to be updated before it runs. And then when it runs, it has to do cleanup for two billion rows, which is not perfect. So basically this autovacuum max threshold sets a threshold at the top that

says by default the value is 100 million rows. So it says no matter how big your table even if you say 20%, once we hit 100 million it'll trigger an autovacuum. Um so it's really just to for really large tables to make sure that we don't get almost unbounded in how long we wait. the autovacuum max workers parameter can now be changed without restarting That's really useful.

Now to do that we've created a new parameter called autovacuum worker slots that you cannot change without uh restarting postgress and the autovacuum max worker has to be between zero and this autovacuum worker slots. So you go like well what did you really improve other than giving me one more parameter. The point here is you might want you can add a couple of extra autovacuum worker slots

and then autovacuum max workers becomes a tunable that you can change depending on how your system goes much much cheaper and then maybe you can say oh we've we've gone up now we need more then you can schedule an actual change of of autovacuum worker slots for the future when you have a service window or something but it gives you the ability to to use uh autovacuum

max worker more as a tunable because it's much cheaper and easier to change it. Uh it would have been great if we didn't also have to set this worker slots to to a value like that. That'd be perfect. But this is still, I think, a pretty good improvement. Uh when you're running manual vacuums, you can now say uh vacuum only. If you have a partition table, you

can say vacuum only the uh head the root table and it will not vacuum all the partitions. Now for vacuum that doesn't make all too much sense because there is no data in the root table. Uh but for analyze it also works. Uh and in analyze if you run an analyze on a partition table it will run the analyze on the root which will analyze the whole

thing and then we'll run it independently on each partition too. And if you say analyze only it will just do it for the root. Now you will then get really bad query plans if you are directly querying individual partitions. But if you never do that, if you always query them through the root table, then you don't need to update that statistics and you're fine to just update

it at the Uh, explain analyze has had some changes to the defaults. It'll now enable buffers by default. So the equivalent of before when you said explain with buffers, that's not one by default. uh we will now show uh statist scan statistics for parallel bitmap scans and we will show how much a materialized node actually used in memory and disk. So this is just sort of more

information available. Uh if you are using something that parses the text output of analyze, it'll break. Uh if you wrote it yourself, you have to fix it. What it generally means is if you're using like one of these graphical tools that will part parse this output, you just need to upgrade them to a version that supports version 18. Uh but it does change the default output. Uh

copy recently uh learned how to skip rows that failed data type conversions for example but for every one of them it would log. So if you loaded a large file with lots of errors you got lots and lots of log. Uh the new thing in 18 is you can say this uh you so on error ignore is I don't remember was it 16 I think or 17

17. Uh and now you also get log verbosity silent which means it will just be ignoring completely ignoring and not logging uh data rows that don't uh fulfill the the data types. For example, if you're trying to load a string into an integer or something like that uh to keep your logs quiet. Uh talking about the other kind of statistics which is the statistics of what's going

on in your system. We also have some interesting additions. Um there are now now new fields added to both PG stat database and PG stat statements for parallel workers to launch and parallel workers launched. The first one being how many parallel workers were postcress planning to launch and the second one is how many did it end up actually launching because there might not be slots available and

and things like that. And you can then track this again at a high-end level at the PG stat database and then look at your individual statements in PGstat statements to figure out which ones basically would have wanted more parallel workers but it failed to actually launch them. Uh in your PG stat user tables we're now tracking the total amount of time spent on vacuum autovacuum analyze and

auto analyze. Previously, we still had the number of times that have been run and when it was last run. But this is the sort of cumulative number of milliseconds spent on each individual table. So you can look and see where you know my my auto vacuum is working all the time. Which tables is it actually spending time on? Uh and the other interesting thing there is both

PG stat progress vacuum and analyze have gained uh values to tell you how much time it spent doing nothing. So you have these u the uh what do we call it uh the vacuum delay I forgot the keyword anyway we where we intentionally slow down vacuum cost based vacuum delay that's what we call it costbased vacuum delay that tells uh vacuum when it runs when it's used

a certain amount of I guess we call them credits then it just sleeps to let other parts of the system work and this will tell you on a running uh vacuum how much time has it actually spent sleeping versus how much time has it spent working and if it spent a a lot of time sleeping and a little time working then you know you have the ability

to increase like if you increase how much it's allowed to do it will do more and faster but it might have bigger effects on the rest of your system uh the IO for the while has been moved into pgio uh to get it much more granular and you can see on a per backend type was it regular query backends that generated this type of well was it

a a vacuum autovacuum back end like where where did it come from and the counters then have of course been removed from PG stat wall and they're now in PG stat IO again this means if you're having a monitoring software that uh reads these to generate graphs or whatever you're probably going to need to upgrade that to a version that supports Postgress 18 and knows uh where

this data is uh we've also received statistics about while buffer's full parameter it's added to PG stat statements you can see it in a vacuum analyze if you run with verbose and you will see it in explain if you add wall which will tell you uh for in stat statements for example for each individual query how many times has these queries run and the wall buffers that's

the default of 16 megabytes like how many times did we generate so much writing that we hit the end of the buffer and had to flush it to disk before we could continue uh you can still see it and you've been able to see that globally in pgtw already before this but now you can see which individual statements did this u so you can look at like

is that reasonable or like that should never happen why is that happening uh so there's some good uh new statistics to look at what's going on inside of your system uh at the SQL level uh there are some interesting things added there as well I think postgress has added support for UU ID version 7 which I still think is weird like I don't why we call this

versions because it's the same UU IDs as ever but UUID version 7 is a different way to create a UYU ID. It's the same UU ID. So like if your application consumes UU IDs and like there's no actual change in the UU ID. It's only a change in how we generate them. The default way that we generate them today uh is typically fully random, right? Uh and

the problem with those are if you use these as keys for example like your index usage gets a massive overhead and huge right amplification because these are pretty big and every time you insert a new one it ends up at a completely random spot in your index uh which will make the index fragmented. It'll it'll like every time you do an insert it's going to have to

write a whole page to the index probably instead of just filling it up. Now, what UUID version 7 does is splits the UUID's 128 bits into pieces and says at the beginning of it, we're going to have a piece that's made out of the time and then we're going to have the rest of it is going to be um random. So, the standard says the time has

to be to a granularity of a millisecond. Postgress has more than that. It it uses the first 12 bits as time and then it uses the rest as random. which means that the UUID version 7 is much much better for your indexes. So if you're using it as key somewhere and it will be I mean technically you went from 128 bit to what's that 116 bits of

randomness. So they're a little bit less secure if you're using them as external keys but you still have 116 bits of security in them. That's that's probably good enough and you get much much better performance. Now, as you can see in the examples, like it's a function called UUID V7, right? You can set that as your default value. But this only has an effect if you're generating

the UU ids in Postgress. If you're generating the UU ids in your application and then storing them in Postgress, well then whatever you're using to generate them in your application is what needs to be upgraded to UUID v7 because again the actual UU ID stored in queries are exactly the same as before. It only changes how we generate them. uh we've gained the virtual tables I guess

old and new for returning uh for updates and merge. So before you know you could say when you do an insert returning you can do an update returning but you can only get uh one of the values. Now you can say you know update my table set a equals 2 returning the old and the new value. And the same if you're doing a merge, you can get

the old and the new value that you had. Uh, and it also works normally when you do an insert, right? That there would be no old value, but it does also work for insert on conflict. And it means you can now do insert on conflict and your application can actually know whether it ended up being an insert or an update, which it it has turned out a

lot of the applications that I've seen and worked on actually needed to know that. And since you couldn't previously know that, they couldn't use insert on conflict. They had to do an update and then an insert or whichever. Now you can do an insert on conflict. So you can see in this example I'm inserting the values one and say on conflict and column A update it to

set it to plus one. And then I return old A new old B new.B. And the first one we run you see the old columns are both returned as null. That means that this was an insert because there was no old column. And since you have to have the A here, which is the one that we're looking for the conflict on, since the A uh has to

be a key, it must be not null. Otherwise, on conflict doesn't work. And therefore, you know that if null comes back, it was an an insert. Whereas then when we run it again, it comes back with values for both old and new and then you know that it was an update. So to me this opens a bunch of new ways of actually being able to use on

conflict instead of running multiple queries which is of course excellent for for performance both the fact that you don't have to run an extra query with all the latency but insert on conflict is very well optimized uh for the typical case of inserting new rows that sometimes cause a conflict and you have to update it. Um we've added support for virtual generated columns. I say this is

just like a virtual uh like a stored virtual column uh except it's not stored and I see that's wrong. It's not supposed to say like stored virtual column. It's supposed to say like stored generated columns. Uh so stored generated columns. It recalculated on every read and that means you can basically turn your table into being sort of half a table and half a view at the same

time. Uh there are good use cases for it like but it lets you do a partial view. So just as an example how they look different, right? create a table uh generated always as a plus b that becomes a virtual column. Every time you query this table, it will recalculate a plus b and return it. Or if you say generated always as a plus b stored, then

the actual result of the calculation is stored and it doesn't have to be recalculated. Now obviously having both of them in the same table is don't do that. That's silly. Uh but being able to do them virtual like adding a calculated column uh I've seen it used as well for cases where you want to like remove a column but you don't want to have to update all

of your applications but you can calculate it from some new column. Add a virtual column right it has zero cost in storage and then you've added that and then you can sort of step by step migrate away from using it in your application and then you can get rid of the virtual column when it's all done. uh we added support for temporal keys in postcrest and this

is things you could basically do this before but this is a standard way of doing it this is standard syntax so you can create your primary key and say in this case I got a column and then then I have valid uh without overlaps that's the uh temporal keys you see the valid here refers to this column that in my case is a date range uh it

is supported for any range types the SQL standard says it has to be times but Postgress has these generic range types and it it works fine with anyone and all of them. Uh and you can then also have a foreign key that references a different column including this uh reference and that we couldn't do before in Postgress. You'd have to do that with a trigger before. Now

you can do it declaratively and say you know I have these foreign keys. So basically you're saying in this the the typical example with the validity is you have a bunch of time stamp and says this version of the row is only valid between these times and then kind of foreign key pointing it and it will actually validate that it hits the the sort of right version

of that row. Uh so again we could do most of this before but having it declarative in here makes it a lot easier and you can remove a whole bunch of custom code if you were doing this kind of thing. It saves a lot of work. A good side note that is you probably want to install the contrib module B3 gist uh which is not installed by

default but if you don't have it like most of this just will say hey there is no op class available for this. So uh just assume that if you get weird errors when you try to do this install bet and you're fine. Um, one of those simple things that we added, there is now an array reverse. It's one of those when I when I saw this go

in, I was like, "What? Haven't we been able to do that before?" It turns out, no, it just takes an array and it reverses it. If you need that, it's one of those like, "Yes, thank you." And if it's someone who didn't, we was like, "Why didn't we have that? It seems simple." Um, so yeah, some complicated, some simple features uh that are added on the SQL

level. Uh let's move and talk a little bit about about backup and replication. I hope everyone has backups. Who does not have backups? Do you dare tell us that we don't have backups? I know you. Whoever how often do you verify your Who has verified their backups more than once? Okay. Okay. It's not so bad. So we have a new tool in Postgress uh since a couple

of versions ago called PG verify backup that verifies the backups taken by PGbased backup and it didn't work if you use PGbased backup in tar mode. It only worked if you use PGbased backup to a directory of files. Uh it now supports the tar formats which I think is what frankly most people use if they use pgbased backup. Uh again this is for uh specifically for PGbased

backup. If you're using one of the additional uh backup management softwares that are out there like PG back rest for example, they they do independent validations. They've been doing so for a while. This is just an an enhancement of the uh the in core version of it. Uh on the side of replication, Postgress can now uh replicate generated columns uh which we couldn't do before. Uh it

can only replicate the stored generated columns, right? If you have these virtual generated columns that we just added support for where they can't be replicated, but you can just create the same kind of virtual column on the receiving side and it will just be calculated on the read anyway. So there's not really much point in replicating it. It's cheaper to do that. Uh and what you basically

just uh say is when you say your uh when you create your um publication you you add the this part here uh publish generated columns equals stored you have stored is the only parameter value it can take but potentially it could take other things in the future that will then make it possible. uh and then you can just say and if you if you do create publication

test and just explicitly list I should say the column names and one of them is a generated column that'll also work this work if you just say replicate the whole table the generated one gets included in what is replicated whereas as you can see this is from the example table I had before where A and B d was the generated column C was the virtual one you

can't include that then you will just get an error so you have to explicitly specify the columns u if you do stats a slightly recursively named uh view called PG stat subscription stats. This runs on the receiving side of logical replication. So where the subscription exists and lets you know on a per subscription based basis uh conflict statistics. So if you replicate logically from one node to

another, it basically turns into inserts and updates on the receiving side, right? And those inserts could, for example, generate a primary key conflict. If your data is out of sync, or if you had a key created on the receiving side that didn't exist on the primary side, you might have duplicate data. And then this one will just count up and say, how many times did I try

to do an insert and it didn't work because of conflicts? How many times did I do an update and it didn't work before the conflicts? How many times did I get an origin conflict where I received the same data from two different places and it's not the same? uh update missing when when it replicated an update and the row that was supposed to be updated didn't exist.

Again, these are things if you're just replicating using logical replication a single table that is identical on both sides. These things should never happen like ever. Uh but in more complex scenarios, they might. For example, you might replicate data, but you might also insert data on the receiving side manually directly on that node. Logical replication lets you do that. uh and if you do something wrong then

then it's going to start failing. Uh and that it's good to know where it failed or or what things. So normally these should all return zero. Uh but that's similar to other kind of conflict views we have like you shouldn't be having replication conflicts. You shouldn't be having deadlocks these like if they happen you need to track down where they came from and what you can do

with them. And finally section on performance. Postcrest 18 will be faster, right? Okay, we're done. No. Uh there are a lot of different things uh that are happening in in Postgress. A lot of them are not directly exposed to you. Uh even more of them might not be exposed to you if you are using a managed service like you know RDS or Azure. Uh but a lot

of them aren't even exposed to you if you run your own Postgress. But it can still be interesting to know about some of them and some of them uh makes a difference to how you do things. mixture. So vacuum for example in post again everyone's favorite and we all think it's too fast or no vacuum now uses the streaming IO operations in Postgress which just means it

can cue a lot more IO it can work uh it's less latency sensitive it just works faster u it also does a much more eager vacuum of all visible pages everyone knows what that means right it's very clear uh the idea here is to um make the aggressive vacuum. If you've ever run into the what we call the aggressive vacuum in Postgress or the anti- wraparound vacuum,

you know, it's painful, right? It's when your regular vacuum isn't running fast enough or isn't running often enough. Eventually, a Postgress failsafe hits and it needs to run this so that you don't uh get basically a system shutdown because you ran out of transaction IDs. And when that hits, it's really expensive and it hurts. This is a way to make it cheaper and basically to aggressively um

whenever Postgres is has got a page that it's got where every row on this disk page is visible to everyone that's when we can run one of these operations normally but traditionally postcress still doesn't do that when you make make a change to a page and it's visible to everyone it just waits until vacuum runs and this just more aggressively and more eagerly will trigger vacuuming of

these all visible pages because Then when the uh anti- wraparound vacuum comes along, it doesn't have to do anything. It still has to look at the page and just see that it's that it's done, but it doesn't have to write it again, which means that once this expensive vacuum runs, it's cheaper. It still kind of hurts uh but it hurts a little bit less. There are a

bunch more uh performance improvements of vacuum 18. So in general, yeah, vacuum is less painful uh and faster running in 18. And it's nothing that you really need to do about it. Like you don't have to run special commands or tune anything. It just uh does it better. Parallel create index uh now also supports gen indexes. Uh previously you did B tree, right? But then you needed

when you really needed parallel create index such as your full text indexes or your JSON indexes. It didn't work. Uh so it now also works for Jin. It already worked for B3 and brin but it does not unfortunately work for gist Uh so gist is I think the big one left. Uh because gist we often use for our geographical indexes. Gist is what we use for things

like our range types that there's like temporal as you say the range the range types we use for temporal those cannot be parallelized but uh jin again with JSON indexes and full text indexes are often very expensive to create. Uh so parallelizing those uh will definitely help a lot in those scenarios. Uh our B3 indexes uh has learned about B3 index skip scan. Uh this is I

would say one of like has been for many years a very often requested feature that I've seen with my clients particularly when they've ported large systems from Oracle for example that has had this for a long time and the idea is as I'm sure you all have run into a a typical Postgress multiolumn index right can only be used if your query includes wear clauses on the

first columns right so if you have an index on ABC D you have to have a wear clause on A or on A and B or A B and C or A B C D. But if you have a wear clause on just B, the index can't be used, right? And with uh index skip scan, the index can now be used. So an index on A, B

can now be used for searches on B. It will not be as efficient as an index that starts with B, right? The B comma A will still be more efficient. Uh but the idea is you might only need one index instead of two. in particular um it's a win if you have few distinct values in the early columns right so if you're if you have an index

on a comma b if you have few distinct values in a it'll be able to use that index for scans in b and what it will basically do I mean obviously this is much simplified but say you have 10 different values of a in your index and you query for bost will just scan the index 10 times once for each value in A, it will look for

the values in B. That obviously doesn't scan the entire index. And there's but like conceptually that's what it does. Which is why, of course, if you have a million distinct values in A, it's not going to use this index skip scan thing because it would have to scan the index a million times. And that's probably not going to be faster than just scanning the whole table. So

it's it's not a a generic thing like, oh, I now just have to create an index on ABCD and I can use it for everything. Uh but in the use cases where it can where you have a first key that is uh that has relatively few distinct values, it can be a big win and you can maybe get rid of an index which makes a lot of

other things cheaper. And then of course what's what's a few what's a few distinct values? Is it you know 10, 100, a thousand? And the answer is yes. It will depend on your application. It will depend on all these things like all all things Postgress right it's cost based. it tries to figure out when it's right and when it's wrong. Um, but yeah, it it'll be very

dependent on what it is. I can say it's unlikely that it'll choose it if you have a million values in it. I don't think anyone will call a million a few unless you have a really really really really big table and then you probably partition into multiple indexes. Anyway, uh, PG upgrade uh is now much more parallelized than it was before. Previously the PG dump step you

know PG upgrade runs PG dump to recreate the table structure uh was parallelized and the copy link was parallelized but for example PG dump uh verifies a lot of things uh sorry PG upgrade verifies a lot of things that your your clusters are compatible that you know all your uh extensions exists and all that things and that stuff is now also parallelized so it will run faster

on systems where you have lots of objects. There is now also a swap mode if you didn't think PG upgrade was scary enough already, right? You know you have PG upgrade, you can run it in copy mode where it copies all the data over and then you can run it in link mode, right? Where it creates hard links with the data and as soon as you start

the system on the new version, you can't use the old version anymore. Now swap mode is is even harsher because it breaks it immediately even before you start it. uh and what it does so link mode will create you it will run initb and create a new like system catalog structure and then it will hard link over the tables into the new structure swap mode will create

the new structure within and then it'll just move that and overwrite the existing one with that and that way if that fails halfway through you get nothing right uh but it's faster right because it doesn't have to do the link like the system cataloges are always going to be fewer and smaller than your real data. So, it's faster, but there is zero chance of roll back here.

There's no way to roll it back. Now, in a lot of cases, even in link mode, like you're not rolling that back. Your roll back strategy would often involve a replica that if this upgrade fails, we're going to switch over to the replica and keep running and and you know, try again next week. And as long as you have a way like that to deal with it,

then yeah, it's faster. But be careful because again you there is no going back when that fails. If it has overwritten half of your system catalogs, it you really have Uh but again it's fast and as we say you know PG upgrade is already capable of of ruining your day pretty good but it is also really good at at you know making your upgrades take 5 minutes

instead of 48 hours or more. I think my longest projected, you know, dump reload upgrade ever was going to take the system offline for almost two weeks. Then like you you can't really do that. And then and then PG upgrade still ran it in like 15 minutes and with this it would run even faster. Uh there are a couple of things that will just run faster with

your general queries. Uh Postgress will now detect redundant group by based on unique constraints. Previously it only did it based on primary keys. That feels almost feels like an like why why wasn't that included given how close primary keys and unique constraints are implemented in Postgress. But this just means that yeah if you're doing group by on unique constraint on unique columns it'll just be faster. It

won't work differently. It'll just be faster. It knows that things are unique. So it doesn't have to check it right. Uh generate serious now generates proper row estimates for numeric and time stamp. It did it for integers. So if you did, you know, generate series from 1 to 100, Postcrist actually knew that was going to generate 100 rows. But if you did it for numeric and said,

I want one to 100 with 0.5 as the interval, postcrist did not know that that was going to generate 200 rows. But now it does. Or if say generate series, give me every day in the year. Postcus actually knows now that that's 365 or 366 depending which will then feed into the query planner and into the optimizer and make make queries or wrongly run more efficiently. Uh

because I mean generate serious a lot of people think generate series look it's only for populating data in your test tables kind of things but you can do a lot of interesting things with generate series as part of your regular queries like you join towards the generate series and find gaps and like there's a lot of useful stuff going on U some other things that are just

sort of interesting to know uh that there is an optimized tupil store for recursive CTE. What it really means is some of your recursive CTEs can run a lot faster. I've I've seen numbers of up to 25% faster with no changes. Uh it's just optimizing how the sort of next recursion accesses the data from the previous recursion in a more optimized way. Um there's less memory usage

if you're doing partition partition wise joining. Uh one of those thing I I keep being amazed what what people find. So uh JSON escaping can now be done using SIMD instructions. So this is the like the uh vector CPU instruction basically like you can do JSON escaping with that and the answer is yes you can. Uh so JSON escaping will now be faster. Uh Postgress supports a

right semi-join just didn't do that before. We did write full joins but not semi joins run faster. Uh numeric multiplication and division has been optimized. Again this is specifically for the data type numeric, right? Not if you're using integer, not if you're using float, but numeric just runs multiplication and division faster. Sometimes it's amazing the kind of like small things that people find. I don't know when

did we add numeric like Postgress 6 7.0 O or something and now someone goes along and hey we can multiply it faster. It's it's so it's pretty good. Another big one uh is that postcrist now supports self join elimination. That's basically if you were joining a table to itself on the primary key. You really don't have to join it. You can just read the columns from the

first one. Right? And when you're handwriting a query you would never self-join a table on I hope on the primary key. Right? But a a very common scenario I've seen is for example you have a view that joins in a table but it only returns two of the columns and you need a third column. So then you join in the base table to the view again and

now that table exists twice in this query and you joined it in on a primary key. Now post will now detect that you did that and only add it once to the query plan and just say hey I'm just going to read all the columns. Uh views are a common way. Another common one is OMS that might you know the abstraction is too deep and they just

don't realize that they're quering the same table twice. But once it reaches the Postgress optimizer it can see that well these things are actually the same thing and the the join path is unique. So we can just remove one of them u as an example here. Yeah, you can see this is just a join from table one to table two. Sorry from T1. It's the same table

aliased as one and two, but the query plan will just scan it once. It decided to choose to scan the second one, but given that they're the same and it's on a primary key, it doesn't matter. It's the same Uh, another big thing for performance is the asynchronous IO framework that has now been added to Postgress. Uh, it can do asynchronous IO instead of synchronous. The general

idea here is to be able to feed like a query today or prior to 18 in postcress would sort of read a disk block handle process that disc block read a dis block process that disc block read a disc block process that disc block you get latency in this process right a lot of the time if you're like table scanning we relied on the operating system kernel

to at least issue read ahead to the hardware but we would still have the latency going back and forth between postgress and the kernel for example the idea behind the asynchronous IO infrastruure structure is that a query running can say, "Hey, I'm going to need all of this data. Go fetch it for me while I start processing the beginning of it." So that when I'm done with

the first page, the second page is already here and I can just keep going. That's sort of the extremely simplified version of it. Uh it will also give us better pre-fetching. Uh it's going to become the foundation for direct IO and bypassing the the operating system buffer cache completely. That's not here yet. Like that's extremely experimental and don't use it. uh but asynchronous IO is not and

it works well. Now Postgress has two implementations of it right now. It has an implementation called worker which basically in in true Postgress fashion spawns a bunch of extra processes when you start. So you can start I think the default is three. It'll start three processes that does your I/IO and then your general queries will tell these processes like hey I'm going to read this data and

those processes will handle that reading and and stuff it up in memory for your queries to use them. Uh it also supports IO uring which is a Linux only. It's a Linux kernel interface that basically does the same thing like the the idea is the same thing. The default value is worker because it's supported everywhere. And it turns out in a surprise I think to a lot

of people as certainly including some of the people who wrote this code uh that worker uh is faster in most cases. It shouldn't be like intuitively you'd think hey the kernel should be faster right but the thing is when we're using worker it also for example distributes this checksum verification with check sums on that get distributed to the workers but the Linux kernel doesn't know how to

validate postgress checksums so if you're using IOuring the query itself has to verify the checksums so that's one of the big deals if you turn off checksums the difference between worker and Iuring mostly goes away there are some other things that can happen there as well. Uh there's still things to learn here. I think one thing that we have really learned about asynchronous the worker implementation in

Postgress 18 is that the default value of three workers is not enough for most people. You're going to need more than three workers. If you have a system that you know generate lots of IO, if you don't have a system that generate lots of IO, it doesn't matter, right? But if you are seeing lots of IO in your system, you're going to want to increase the number

of IO workers almost certainly because your IO system is probably like if you're using NVME or something like it, it can handle pretty much any IO you throw at it and three processes is not enough to saturate uh a modern IO system. There are still, you know, we this has been out for about half a year. there are still more things that we need to learn about

uh this system in real world applications but I think the the conclusion so far is yeah that number needs to be higher so look at that one uh and you can still like turn it off and basically make it emulate the old synchronous mode but from what I've heard what I've seen with people who upgraded like this certainly works well enough even though there are many optimizations

to be done it's already better than what we had before so you should be running with this for the time being also asynchronous IO read only like it it's only the reads the writes are still happening synchronous in each process it's not a fundamental reason it doesn't have to be that way it's just is that way for now because you have to do them piece by piece

uh there are a lot of other infrastructure performance feature and infrastructure features in general uh it will pretty much just run faster uh but these are the things that has sort of more direct visibility uh then obviously there's There's there is going to be more. We have one set, you know, one set of slides, one set of minutes. Uh if you look through the actual release note,

like it's hundreds of things that are in the new version. There's a lot of performance improvements, a lot of everything. There's no time to to cover all of them. But if you haven't had a chance yet, yeah, Postcross 18 has definitely reached the point now where run it like you don't have to worry about Postcross 18 anymore than you worry about Postcross 17. I mean, for database

people, we always worry about these things, right? That's our job. uh but uh Postgress 18 is definitely uh good to go and there are enough gains I'd say to look at upgrading if you're if you're having either any of these uh query level things that will really help you or just the performance stuff uh it'll be worth it. Uh that's the things I have. I think I

have about two minutes or so uh for questions on this or or I guess sort of anything else goes as well. Anyone? We got one back there. De, are you going to run over with a microphone? I think you need to turn it on. I haven't turned it on yet. There is a guide if you on PG upgrades docs if you use link mode on the primary

and you have streaming replicas and you want to quickly upgrade all of those as well by running some complicated rsync command. Uh, is that also possible with swap with PG upgrade? >> Uh, I don't believe you can use that with swap mode. Uh, I think you need link mode, but I haven't I haven't actually researched that, but I don't think it works. Uh, but it might. And

it's I mean, again, if you thought PG upgrade was scary, like those instructions are scary. It took me many times to figure out that they should even be possible when I read them and they're like, no, no, no. It kind of makes sense that this works. But they are extremely just as a note for everyone like you can do it but I I've seen people do like

you have to really do things in exactly the right order and you have to like wait they say stop this thing you have to wait for it to actually stop before you run the next step otherwise you get a corrupt database but yeah do it I would always say if you do that always create like a throwaway replica that you're not planning to upgrade as your roll

back path but uh but yeah they do work but they are scary and I don't think they work with swap but I haven't actually researched it. Anyone else? Okay. And thank you very much. If you have any further questions later, I'll I'll be around here all day and most of tomorrow. So, just feel free to grab me and have a chat. Thank you. Check check check trip

check. Hello. I picked the blue. All right, I'm going to get started. We uh we have an hour and then we have an hour for lunch. Was everyone able to eat in an hour yesterday? I was It was a little tight. So, uh I'd love to say I'm going to finish early, but we have 103 slides, so odds are pretty slim. Um sorry about that. My name

is Bruce Mjgin. I am one of the Postgress core team members and I work for EDB. I am very pleased to be here at scale. It's been a couple years since I've been able to attend and this is a brand new talk. I've written two or three new talks this year and this is one of them called the wonderful world of wall. If you're curious about my

talks or you want to see the slides right now, if you go to this QR code or this URL, uh there are 63 presentations on that website, 3,000 slides, 660 blog entries uh related to Postgress. So, a lot of stuff going on there. Uh the reason that I wrote this talk is because I realized that over the years we've inc improved the effectiveness of the write ahead

log tremendously in terms of what it does and what allows you to do as um as administrators and that's the goal of this talk is to basically exhaustively show you what is possible with the write ahead log. I didn't get into a lot of tuning parameters or practical issues. This is more of a highle what does it do? how does it do it? And just to give

you an idea of of what's possible with the right headlog because again when it started uh year many years ago it had a much more limited scope. I will take questions as we go. Uh we will finish on time. If for some reason I can finish early and get you out of here to have lunch, I will certainly do that. Uh but I am going to do

my best. But again, these slides are online right now. Just put them on this morning. Um and let's get started. So we're going to talk about first the what's inside the right head log. I'm going to show you effectively what is inside that those files. Uh and then we're going to go through five different capabilities of the righthead log. Uh one is crash recovery, point in time

recovery, physical replication, logic replication and finally a feature of write ahead log of the write ahead log called replication slots. So the write ahead log is basically a change log for the database. uh as you can imagine for a database which changes a lot having a a place to look in one having one place to to record all the changes is incredibly effective and as I said

before it enables a lot of very sophisticated features on top of postgress. I also mentioned that Postgress has evolved the write ahead log significantly since it was originally implemented was originally implemented 2001. Um I started in 1996 so six five years in uh to open source development we have the initial verse of the writerhead log. Then 2005 we have point in time recovery 2010 streaming physical replication

um 2014 logical replication slots in 2017 um uh logical replication and replication slots uh or or kind of enhancements to the replication slots in 2017. Uh we're going to see this diagram a lot. So I'm just going to basically give you a really brief uh breakdown on it. At the top in purple we have three different uh Postgress sessions. So we have three backends or three sessions

running. Um and uh below that we have the uh shared buffer cache. This is the place that all the reads and writes happen to the data. Uh it's a basically a shared memory area. And when you want to store that durably, you write it to the kernel and then effectively then the kernel writes it to the heap and index files which exist in the file system in

the PG data directory uh of the system. On the right hand side we effectively have the write ahead log which is what we've talked about. Um in this case it's represented as wall buffers which are inmemory representations of things that are about to be written in the write ahead log. Every time we modify some data in the shared buffer cache, we also write one or more records

to the write ahead log or the write ahead log buffers. Right log buffers are then written to the kernel cache and then eventually flushed to the a separate directory called PG uh PG wall uh which exists in your data directory. Uh so if you've never seen your PG wall directory, that's what's there. Um this represents the base directory of your data directory. Uh any questions so far?

Uh the other thing I want to point out is that we have a blue right here. Um if you download these slides, you can click on the blue and it will take you to a relevant section of of the documentation. Okay. Um now to show you what's inside the write ahead log, I have to install a special extension. In this case, we're going to um install something

called wall inspect. You all are going to see a large number of queries coming in this presentation. And in fact what I've done is I've collected all the queries into this URL. So if you download that ur URL it's an SQL file. You can run it and it will then generate the queries you see in this uh I also wasn't super happy with the internal representations of

some of the of the resource manager labels. So I wrote a little function uh to remap some of the uh some of the labels to make it a little clearer for us. Okay. So here we have a function which is calling the extension uh PG wall inspect um called PG get wall records info a very awkward name uh but what it effectively has done is it's allowing

us to look at the write ahead log while postgress is running and dump the contents out and the code down here at the over here is effectively specifying what range of write ahead log do I want to see that's actually going to change as we move forward. And then down here, this is a common table expression. And here I run some statistics on it. And the output

actually looks like this. This is the output of uh init DB. So an empty cluster. When you run it, it generates these right ahead log records. As you can see, we have various resource managers. Resource managers are effectively ways that we classify different types of resources that get put in in the uh right ahead log. We have B tree resources. We have database resources. We have things

that modify the heap things that modify the write ahead log, the exact which is where we have transaction information and then some other ones down there at the bottom. And you can see that the size of them, the percentage, and then the percentage size. So half of the records are actually PG wall in this case. Uh here's the here's the same query. Um but now we're going

to add a record type field and we're going to run the same query again. Um, I'm just showing you these queries so you so if you ever run it yourself, you don't have to like guess it. You can just run them from the SQL file I give you and they'll all show up. So now we have we have the B tree which is the resource manager and

I'm drilling down into the resource manager and I'm showing the individual types of records that exist within the B tree resource manager. Okay, so we have something called ddoop, something called delete, insert leaf, which again it's a B tree, so it has leaf and um and root pages. We have a new root page. We have a split again a B tree. Uh even a vacuum record. The

database uh record type manager type. We have a file copy and a write ahead log. Um we have a heap, anything that modifies the heap. We have heap type records, a delete, a hot update, an in place, an insert. Again, I don't expect you to know these, but I'm trying to drill down and give you an idea of the detail that's actually in this right ahead log.

Okay, as we can see here, the biggest one is actually the inserts. So, inserts into heap. Of course, we're running this um as an initb. So, we're inserting a whole bunch of rows into the heap. That's exactly what we'd expect. Um heap 2 is another type of heap record, but more uh related to cleanup. Uh, write ahead log. These are right- ahead log issues related to checkpoints.

I'll talk about checkpoint later. FPI stands for full page images. I'll explain what that does maybe. Uh, PG exact to zero the pages realm map uh standby for uh a replica um storage and then transaction type is commit. So we've committed uh 756 transactions as part of initializing our cluster. Again, not super exciting information. Just giving you an idea of how deep this write ahead log is.

It's not just one type of record. It's not 10 types of records. It's a whole set of types of records that keeps Postgress running reliably. So, what I'm going to do now is I'm going to create a table. But before I create a table, what I'm going to do is I'm going to use the select here at this at the top to effectively record what's called an

LSN. And an LSN is merely a physical location inside the write ahead log stream. The right ahead log is a stream. It's always growing, growing, growing, growing, growing. But you can actually record a spec specific position in the write ahead log stream. And that's what the lsn does. And we stall, we store it in a variable uh called start lsn. So now we create a table. When

we run our query, notice here in red, I'm not asking for all the write ahead log records. What am I asking for? I'm asking for all the records that happened after right before my from after my create. So I make a marker right before the create. I run the create and now I can see only the head log records that were generated from the time right before

the by the create command itself. The create table command right there. So, um, I can now see I've got a lot of B tree inserts here. Okay. Not a whole lot of everything else, right? It's 50 inserts, uh, into a B tree. Uh, and then just really nothing like couple five through two, uh, entries. Here's another example. I'm going to now record the start LSN to uh,

before an insert. All right. And now I'm going to do my an insert again. Same start LSN, but now the variable's different. Now for an insert, we see two B tree records. We see an a heap, which is basically initializing a page and inserting and then a commit. That's exactly what we'd expect to see, right? We're creating a heap page and we're inserting one row in it.

If I do an update, the records generated here are going to be a heap, a B tree insert because we this is an index column. We're going to do an update in the heap. We expect that. And a commit, right? three records kind of makes sense, right? Um if we do a delete, then we're going to get a delete command on the heap and a commit. Not

really mysterious, right? It kind of makes sense. If we do a drop table, we're going to get a delete of the heap and then uh prune pruning the file basically and a lock and a commit kind of kind of makes sense. Okay. Um, what's interesting, remember I I just inserted one row. Now, if I insert a second row, okay? Um, or I'm gonna insert a second row

here. Okay. And now, um, I have an insert, one insert, and a and an insert into the leaf. Um, and if I can now drill down and I can look at the actual record. So, it's a heap insert. You can see it's 59 bytes long. Um, and up top here, the these are LSN values. do a ton of talking about insert into the leaf the transaction commit

again a description of what that commit record looks like. Uh if I look at well the if I look at the record itself I can actually see down here this is the data which is in the right headlock record and this this O2 did anyone know what that is? What did what did we update the new value to be? two, right? So that's it. That's it right

there. Okay, that is literally the two that we did. Now there is some header information and so forth, but that's our two. Um, if I go if I do if I turn on logical replication, then I get a fourth record, which is a hint because logical replication would would replicate uh hints that go to the replica. Um, anybody familiar with unlocked tables? Good. Um, unlog tables are

effectively tables that don't generate write ahead log or generate very minimal write ahead log. And we're going to prove that now. We're going to we're going to change the table to unlogged. You can actually do that. You can take an existing table and say I want it to be unlogged. Okay. And we insert the value four into the table right here. Okay. And what do I get

when I run the same query? I get an error. I get an error because it just can't find anything. Why can't it find anything? Because it's it's right. It's an unlocked table. It doesn't generate any wall for an insert, which is what we would expect, right? So again, I'm kind of just showing you. And again down here in blue we have text that shows us about what

anal log table is and how they work. But effectively this write ahead log activity is generating IO. It does have to be flushed down to disk at some point. It particularly we have to wait on a commit for it to be sh flushed down because we need the right to be durable. So there's a lot of overhead involved in all of these write ahead log not only

the IO itself but the flushing of it to durable storage and the fact we have to wait for that flush to durable storage to be acknowledged before we can have a durable write. If you've ever heard of some of the settings in Postgress that turn off durability like unlogged or turning off fsync or making um there's a couple async uh commands that allow you to delay some

of the of the IO waiting for the write ahead log. That's what it's basically doing. It's trying to reduce the right ahead log activity. Okay, let me take questions just about that one first section. Yes, sir. And again, what I've really talked about is just give you a framework. I don't expect you to know all the white log types. You don't need to know. I need to

give you that framework so as we talk about the feature to write ahead log, you have an understanding of what it does and what it's inside of it. Yeah. >> Yeah. When you turn off logged, does replication still work? >> So, okay. So the question is when you turn off the logging does replication still work in fact it does not. So if you have an unlogged table

on your the replica will there'll be nothing there. Okay I think the way it works is that the the the table will ex back from crash recovery we wouldn't have our data. So fsync is a operating system command which says I we don't actually use the f-sync command. use f data sync or whatever, but it just allows us to tell the kernel to flush that down to

the IO subsystem. >> Other questions? Yep. >> Um, if if for streaming replication you would replicate everything but unlock tables are not replicated, is there like a hacky way to actually just partially replicate your tables? Well, the hacky way would be to create a um I assume I don't have to repeat the question. Hacky way would be to create a uh uh basically a partitioned table and

make some of them logged and some of them unlogged and therefore you could kind of control which ones are empty on the replica and which ones are not empty. But you could still query the table on the primary. And if you query the replicate, you'd only see the the the ones that you were were logged and the unlocked tables would just show nothing. I never done it,

but it it should work. And if it doesn't work, something broken. Other questions? Yeah. >> Dirty page to get flushed. >> Is it okay for a dirty page to get flushed? Absolutely. >> The world record. >> Yeah. Is it okay for the dirty page to get flushed? Okay. So, that's a good point. So it is okay for a dirty page to be get flushed even before the

write is written and the reason for that is because the transaction ids on the row would indicate that that transaction was thrown away because because effectively the um PG exact table would show here's a row but that row was a part of a transaction that hadn't committed yet and because it hadn't committed did it the right head log wasn't ref flushed. Right? So you can see how

the cycle goes. The the the the row would say I'm a row and I'm part of this transaction. But because we didn't get it flushed to the raw, therefore anybody who sees that row would just ignore it because it was part of a a a transaction that never finished. Okay. We do handle torn pages. If you were to write to a page, we would force a write

ahead log flush of the of the page image in case the page was torn when we were writing it. So we don't typically do that very much, but it can happen. Yeah, it's very hard to get this right, but fortunately Postgress has 30 40 years of doing this and we've ne really never had a problem. But you're you're starting to understand the types of problems that we

deal with. Yeah. Like can you write a page before the right head log gets down? The answer is if it's a new page, then you might tear it and we have to force the page to go to write a log. But if it's not a new page, we can just write it and we put the transaction ID on there and it's not committed yet and everyone ignores

it. Yeah, it's pretty tricky. Other questions? Okay, so it sounds like people are interested in crash recovery. So that's actually perfect for me, right? Um, let's talk about crash recovery. This is the first use of the write ahead log, right? So again, I've kind of laid out what these two sides do. I think everyone kind of gets that now, right? With the right hand side is the

write ahead log and the durability part, the f-sync down to durable storage. And the and the the left hand side shared buffer cache. We can delay how when we write that down. We can write it down sooner as you're saying, we can write it down later, right? Um, but but either way, we're okay because if we crash, this is what we get. Okay, we get a write

ahead log that we know is good because we've fsynced every single transaction in there. Now, there might be a little bit of dirt at the end. Does that make any sense? We have a check sum on there. So, if we have a partial wall record at the end, it doesn't bother us. We just ignore it because it was we hadn't committed completely. Okay. But in every other

case, all the previous records have check sums on them. We know they're all accurate. Uh in fact, when you do pg reset wall, you're basically saying ignore the check sums, which is not something you should be doing, but that's another issue. So what happens, this is what we get. We have a wall that has the changes, and we have a heap and index files, which are basically

who knows what their state they're in. They might have some records that we haven't committed yet. They might have some stale data where we have the real accurate data in the write ahead log. Right? So how does Postgress fix this? And again this goes back to 2001. It's that it's that old. So what we basically do is we have a a Postgress worker. This is not a

real normal backend. This is a special worker that has access to the system catalogs and so forth. But you're not running queries here. Does that make sense? It's like a special process that we have. So it's what it starts to do. And you might notice from here, you see how these arrows are all going downward. Okay. Okay. When we go here, where are the arrows going? They're

going up because we crashed. So the first thing we do on start after a bad crash is we look at the right head log. Did we get a clean shutdown? If we didn't get a clean shutdown, what do we need to do? We need to we need to make the database consistent. I'll say that again. If we don't have a if we start up Postgress and we

don't have a clean shutdown record at the end of the write ahead log, we know we crashed or somebody pulled a plug, something bad happened. So we know we have to enter rec crash recovery mode. So what we do is we start reading the write ahead log from the previous checkpoint. I'll talk about checkpoints in a minute. But a checkpoint is effectively the safe part where we

know that all the stuff from the shared buffer cache has gotten down. So we effectively start reading the righthead log records. We read them into this Postgress worker. The worker writes into the shared buffer cache and then it writes it down to the heap and index And what happens is you see that remember how you see how this is like brighter. So as you're doing it, it

gets a little dimmer. It gets a little dimmer. See this is my little this is my animation. This is as far as it gets. As exciting as it gets, folks. Okay. And then once we're done, look at that. It's gone. All right, because it's now Yeah, I need to get out more. Um, what happens now is we've read all the write ahead log records up to the

last valid record with a valid check sum and that's the last commit that is considered to be committed. Okay, but what's nice is that by re by doing that replay, we've effectively cleaned the heap and index files. We have no pages. We have accurate data. We may still have some rows in there as this gentleman was saying from records that never committed, but we don't care because

everyone knows and you have to see my NBC start talk to understand it. But there's a bunch of records that we just ignore because we know that they never got committed. So we don't have to worry about those. Um because in fact we don't have any lo we don't have any wall records for those, right? Because by definition we wrote records during our transaction. We didn't get

to send them in the right head log. So they just sit there. They get cleaned up by vacuum later on. Okay. So then we can start and now we're now we're running like normal again just like before, right? We got we have our shared buffers going back and forth. We have three backends and then we also have our right ahead right log buffers that are being fynced

and we're back to normal. So this is what we did in 20 2001. first use of the write ahead log to allow for this crash recovery. Okay. Uh any questions so far I guess we got all the questions in the first half. Okay. Yeah, sure. You can just yell. >> Yeah, >> just connecting the dots a bit. When a checkpoint happens, is that kind of guaranteeing that

the heap and index files are now in sync with the wall? >> Exactly. Well, and I have a checkpoint slide later. I didn't think we'd get I didn't think we'd have such an energetic audience. So, um but effectively what a checkpoint does is it flushes all the dirty shared buffers down to to in to here and at that point we can recycle the right head log that

we don't need anymore. Okay. So, that's what prevents the right head log from growing perpetually, right? Uh so checkpoint and I've been in the database industry since the 90s and I used to hear hear the word checkpoint. I had no idea what that was. Like, oh, it runs a checkpoint. Like, what does that mean? Um, it effectively is basically just forcing the dirty shared buffers down to

storage. So then we don't need to replay. We create we add a we add a a write ahead log record to say we did a clean shutdown, right? Um, right here. And therefore, we we can just get rid of and recycle the righthead log records. And I have a slide later that kind of shows that. Okay. Um, okay. So, let's just look at how we set this

up. So, how do we Let's just I'm going to run through a crash recovery just to give you like a feeling for what it looks like. So, we're going to create a table called crash test and we're going to insert a row that says Postgress is awesome. Right? Again, I now I'm I'm going to do this as crudely as possible. Okay. I'm gonna literally grap the data

directory. Grap data directory and give me all of the files that have Postgress is awesome. And as soon as I do the insert, where do I see that string? That string is in the PG wall directory. Okay, the righthead log directory. and it's in the first file because O1, right? I know this is kind of stupid, but that's that's all there is. Okay, I'm going to go

to sleep for checkpoint timeout seconds. Now, I'm not sure how of you know what checkpoint timeouts does, but checkpoint timeout says I want to force a checkpoint every checkpoint timeout seconds. And but by default for Postgress, I think it's five minutes. Okay. So, I'm going to sleep for five And during those five minutes, I'm guaranteed that the database is going to do a checkpoint on its own,

and I'm going to run the exact same GP command again. And when I run the exact same GP command again, what do I see? I see the same write ahead log in red but now I see it in the data files because what's happened during that checkpoint is that it has it has gone through all the shared buffers in that five minute wait it during that period

and it's forced any dirty pages down to the heap and that's why I got the blue Oh, checkpoint. Here it is. Okay. Here's what we It's the slide I was waiting for. So, effectively the way the checkpoint works is let's suppose we start a checkpoint and in this point we have three dirty buffers. So, what happens is that Postgress starts writing these dirty buffers down to the

in heap and index files. So, in this case it's written um this one because you can see it's missing here, right? But at the same time, it started making new dirty pages. That's okay. We don't care. That's okay. Okay. So, we're we're what's happening is we're actually we're actually moving the wall pointer forward during the checkpoint, but we've we've written one down. Now, we've written these two.

By the time we get here, all of the ones are gone. And now, my right ahead log is here. And what I can do is I I don't need this file anymore. I don't need the write ahead log file anymore because we did the checkpoint and I know what'sever in that check write ahead log file I know that's already been written to the heap and index files

right good cool so now I can this way what exactly what postus does it literally renames the righthead log file in the next open spot so I can start using it again I'm just going to overwrite it but it's quicker for us to move, rename it than it is to delete it and then create another 8K 8 megabyte file or whatever. Okay. And again, if you're curious

about configuration in blue, here we have a session session. I'm going to now simulate a crash. Always exciting. Okay. Um, what I'm going to do is I'm going to say long live relational systems, right? You can tell I've been in the industry way too long. Um, and now I'm going to do the same GP. Uh, okay. Long live relational systems and it now exists only in the

write ahead log which is what we expect. Okay. And now I'm going to do what's called immediate shutdown. So immediate basically crashes the system. You'd never probably want to do this in case like I don't know there's some emergency and nothing else works. So this is not what you want to do but it's great for demos. Okay. So when when I crash the system I don't get

a clean shutdown record in at the end of the writead log. Remember, if there's no clean shutdown the record the write ahead log when it starts up it's going to read the write ahead log and go into crash recovery mode. So that's why I had to use immediate because I wanted to simulate that there's no clean shutdown record. And when I now look at the GP again,

it's exactly the same, right? It's it's only in the wall. Okay. So now I'm going to start Postgress. And in this red here, you can see it reading the right headlog and doing redo operations. It's replaying. It's doing that. Remember when it went up and over? It's doing this. It's doing this thing. Remember, it's reading the redhead log and it's going to apply it and put it

down here. Okay. So, that's exactly what's going on. And then in blue, checkpoint complete. It wrote one buffer. Super duper exciting, right? But now when I do a grap, okay, what do I see? I see the long live relational systems is still in the right ahead log, but now it's in the heap file. And it's in the heap file because it went through crash Previous. Yeah. Here.

Um, so when would when would it disappear from the wild file? >> So when Okay. So it disappears from the wall file only once I do a checkpoint and the all the dirty buffers are pushed down into the heap and then I know I don't need the right head log file anymore. Okay, because what's going to happen is that I mean I guess you could sort of

say by the end of crash recovery you probably don't need the right headlog file anymore. I don't remember if we create a new wall. Yeah, we don't create we create a new wall file after I don't know do we I can't remember if we create a new wall file after crash recovery or we just start writing toward the end but I have a feeling we create a

new one. So I guess you could say we don't need it anymore. But again, it's going to take a checkpoint for us to verify that. And then we're going to have a marker at LSN in the check in the white edg. And we're going to say any files before that last wall file, we don't need anything earlier. And that's going to get recycled toward the and get

moved to the end of the of the queue. Okay. Yes. >> On the next slide, I think where there was that Yeah. that invalid record length. What does that mean? What's going on there? Um, so you remember what I said that that or let me ask you, do you remember I remember I said that when does it go into crash recovery? Goes into crash recovery when there's

not a clean shutdown record at the end of the file. Okay. So how does it determine if it's a clean shutdown record? is effectively reading the right head log and it gets to the to a record which not only is it not a shutdown record, it doesn't even even have a valid check sum. Okay, so it's effectively a partially written record and it's like what's that basically

saying is that I expected a 24, I got a zero. So what's happening is I expected a new record to be there. There's nothing there. Something I I'm I'm verifying that effectively the system is not I I'm throwing away that record. Okay. Okay. Um and so what I do is I'm I'm saying long live relational systems now because of crash recovery. I have it in the write

ahead log and I have in the database I have it in the in the heap files. And then you can see in the select here it's restored that row which was only in the wall before the crash. Right? Because we hadn't run a Questions. Did we get them all? Probably maybe. Okay, great. Okay, let's take a look at point of time recovery. Again, this is the next

feature. So point in time recovery allows us to effectively recover. See in crash recovery you kind of noticed like we're checkpointing every five minutes, right? So we're not accumulating a whole lot of log right ahead log and and effectively you're just worrying about crashes. Okay. But for point in time recovery, this is a case where you want to do continuous archiving. You want to create a base

backup and you may want to go days or weeks using a continuous archive and allow me to recover to any point in those multiple weeks. Um, so normally without this feature, you do a backup. That backup's only good at the time you do it. You back up PG dump. It's good at the time you do the dump, but it's not good any after that. Everything's gone, right?

Crash recovery is not going to help you. Why? Because we're We're checkpointing every five minutes. All this right at log's going away. So what we developed a couple years after crash recovery 2005 I believe is continuous archiving or point in time recovery. And in this case what is happening is instead of recycling the write ahead logs and moving we still move the files over for crash recovery.

We still do the same checkpoint, the same moving of the of the obsolete write ahead log files, but what we add is the ability to store the write ahead log all every single generated writead log in a central location that's not going to get recycled. So if you want to think of this as the nonrecycled wall example, that's exactly what it is. Because crash recovery is recycled

wall. What this does is it says every time I fill a right ahead log, I want it shipped somewhere and I want I want it saved and I may want to save it for days or weeks or months. Okay. So, how does this work? I know this is a little more diagrams. There's a lot of diagrams in here. Um, so what we're doing here is we're creating

first a file system backup. That's what we call base backup. So you can't you really do continuous archiving or point of time recovery without creating a base backup of all of your heap and index files so that we have something to build on. Remember the righthead log is only the changes, So we what about the data that hasn't changed, right? We got to get a file system

backup. So what we do is we effectively take a a copy of the heap and index files and we basically copy them up to this uh location here. Okay, but keep look at this little squiggle here. Does anybody want to guess why that why I did that squiggle there? consistency. That's right. The system is running while I'm doing this base backup. We're not shutting down to do

this. So things are getting flushed from the right ahead from the shared buffer cache all the time. We're running checkpoints happening all all this kind of stuff is happening. So the effective um effectively what's happening is we're getting a a backup but the backup is corrupt. The backup is not something we can run. But the other thing we do for base pgbased backup which we also grab

the right the matching write ahead log files and these matching right ahead log files potentially would allow us to restore that backup by applying the matching right ahead the If I take a corrupt backup like this and I and I possess all of the write ahead log that was generated during the backup, I can really really do like a mini crash recovery or a mega crash recovery

where I take a corrupted backup just like we did over here, right, with my little squiggles here, right? And we replied what was in the wall. What I'm now doing is I'm saying, okay, this is going to be this is going to be kind of big, but I'm now going to going to take all the write backup, and that would actually give me a backup. Doesn't give

me a continuous archive, but it gives me a backup. So, that's what we do there. Okay, so we're going to we're going to back up we're going to back up the write ahead log. We're going to write back up the heap and index files. We're going to back up the write ahead log files that were generated during the backup. And then we're going to start writing from

there forward any generated wall file. We're going to copy it o somewhere else. Okay. And we're going to call it the wall archive. That's usually what it's called. Okay. So how does how do how do I do recovery in this case? Okay. I take my corrupt heap and index files and I restore them over here in back into the data directory. Okay. I take my matching wall

and I put it here in the wall directory. Okay. And then I basically start up the server and I start replaying just like crash recovery. I start replaying the right hand logs and applying them through the shared buffer cache down to the heap and index files. Okay. And it gets it gets dimmer. Okay. And then it gets dimmer and it's go it's done. However, what have I

done? I've recovered my database to the end of my backup, the time of the end of my backup. That's what I've recovered to. But that's not enough. Most people want continuous archiving, which means I want to go past the time of my backup, and I want to perhaps run it days or weeks into the future up until the point where either I crashed or had some corruption

or maybe somebody deleted a file. All right. So, what I start doing now is instead, it's kind of crazy. Instead of reading the wall directly, I'm going to take the archive files. I'm going to start retrieving them. I'm going to start putting them in this directory. And now I'm going to keep doing replay. I'm going to keep doing replay as long as I find files here. I'm

going to keep doing replay either until I I I have no more files or if somebody specified a termination point for the recovery. They may you can actually specify that in the recovery conf. You can say I want to stop recovery to a certain point. If I do that, it'll stop at that point. If I don't do that, then it's going to recover as much as it

can. And once I'm done that, I'm done. I have recovered. How have I done it? I took a base backup. I archived the righthead log files. And then I continued archiving the redhead log files after I was finished. I may do that for days or weeks. And to recover, I brought back the the backup. And then I re basically replayed all the right head log files. not

only the broad log files during the backup, but maybe a whole bunch of whitehead log files that were generated after the backup. Okay, any questions? Wow, everyone got that? Good. That's good. That's good. Okay, let's take a look at just how we set it up. So, I'm going to run this as Postgress. You can see at the top here, I'm going to create a directory called archive.

Again, this is just for demo. You'd put this somewhere else. you don't store the archive on the same machine, but I'm just going to do that for simplicity. Um, I turn archive mode on. I turn the archive command. I tell the archive system, where do you want to how do I store these write ahead log files? And in this case, I'm going to copy them. You could

use rsync, you could use SCP, you can use whatever to to copy them. Typically, you're going to copy them somewhere else, but I didn't do that. Okay. And then I restart the server and all of a sudden um here's my um redhead log directory. Okay, here's my archive directory right here. PU Postgress archive, right? And initially it's empty. And then I run I run a switch wall

which is effectively telling it like give me a new wall file. And when I run that, what happens? Oh, I got a file. Why do I have a file? Because I because I created a new file and it says, "Oh, okay. That means the old file which ended in in O1 that should be copy because now O2 is my file." So, because I told it to switch,

it's going to create O2 and 01 automatically because of this archive command gets copied to the archive And now I can test it. I know I know this is a little asking a little much but this demo is the best I could do. Again, you can download this SQL and run it yourself and I verified it works. Okay, so I created a table called PITR test which

is effectively just a temporary like a just a single table. And now I'm going to insert a message prewall switch message. So that's my pre-wall switch message. Okay. And now if I do a GP for pre-wall switch message, where do I see it? It's in the write ahead log, right? That's we'd expect it, right? Because the transaction committed, right? It gets written there. Okay, now I'm going

to look in the archive directory. And you know what? It's not there. Why is it not there? Because this is the current wall file. And we don't copy a partial wall file. We only copy them when they're finished being used. In this case, it's currently active. Yes, definitely. Yes, you can force the system if you want to automatically create a new wall file and not keep them

around too long if you want to. Um, I didn't get into that, but anyway. Okay. So, so you can see it's in it's not in the archive directory. Okay. But then when I switch all of a sudden it shows up in the archive directory because that's I actually uh forced a new wall. Now the current which is now the current wall file is three. Okay. Now I'm

going to um this was the pre-wall switch message. Now I'm going to have a pre-backup message. Pre-backup message. I'm going to insert it. Now um it appears in I'm going to um I'm going to do a base backup right here. This is my full backup. Okay. And now you can see that it now is in the archive directory because this backup as I told you before will

archive all of the active write ahead log records that were active during the backup. Remember I told you that that the active records during the backup are automatically going to be sent off. So it it actually did that. Okay. And you can also see um now um and you can also see that it's in the data directory which is what we'd expect because it's a committed trans.

Now we're going to have a post backup message. This is where it gets interesting. Um this is after the base backup. Okay. So I'm going to insert it. Um it's not in the archive directory yet. I'm going to force a switch. And now it's in the archive directory. Okay. But it's not in the backup. It is not in the why is it on the backup? because we

did this after we created the backup, right? That's why it's called post backup message, right? This is where we did the backup right here. Okay? Not surprising. It doesn't appear in the backup. So, what we're going to do is we're going to delete the data directory. We're going to create we're going to copy the backup into the data directory. Um, we're then going to simulate the restore

command right here. Okay. Um, and we're basically going to start the server. That's all we're doing. And what's amazing is all three messages appear. Why do all three messages appear? Well, the first message, it's because it was in the data directory. The second message, it was in the backup. And the third message is only there because it was in the archive directory. It never was in the

backup, but it restored it properly. And that's what point in time recovery does is to take data that's in after the backup finished and restores it to create a a current version of Postgress current data directory questions. Yes. Oh two. Okay. >> So when you do the copy or recursive >> Yeah. Is there a risk of overwriting from the backup into the data? >> Uh when you

do a rec Where did I do a recursive copy? >> You have a cp-r, right? >> Oh. Oh, yeah. But right above it, I deleted the whole data directory. Uh what happens if uh our uh recycling uh wall recycling is faster than uh archiving? >> I'm sorry, I didn't understand. What happens if the wall recycling? >> Wall recycle wall recycling uh faster than uh archiving. Wall archiving.

>> I'm sorry. No, wall files are wall files are never recycled before archiving. Yeah, they have to always be sent off and that has to complete before we can recycle anything. In fact, if the archive starts failing, your right ahead log file directory will get huge because we can't recycle anything. Yeah. Um okay. >> Is um wall archival activity on the primary synchronous with any activity on

the primary or does like wall generation happen? >> So is wall archiving on the primary synchronous with anything on the primary? I'm going to get to that in the next section. Yeah. Okay. So um let's take a look. Continuous archiving is still active. So even though I've done recovery arch, it's still archiving. I know he's sound like I did I recovered a system, it's still active. The

archive command is still there. It's all it's all it all worked. So it's I've recovered a system that's still in archive mode, still doing archiving. And you can see that if I look at the write ahead log, because I did a point in time recovery, I get a new timeline. And I'm not going to go into timelines. I have a link over here somewhere about what timelines

are. Um, but effectively it means that I'm on a new branch of of that server. So every time you do a a crash, a point in time recovery, you get a new timeline. Um, and you can see, in fact, if I look in the archive, I've got some things from timeline one, which is before the recovery, and then the two, the one at the bottom there is

from the recovery itself. Okay? And all future raw files will have a two. So that prevents any kind of write overwriting of the write ahead logs. Okay, think we're good. Okay, next one. Physical replication. So, physical replication builds on what we just talked about. I know it's kind of crazy, but it does. Um, you recognize this slide. It's the same slide, right? You have a point in

time recovery. Uh, you have a file system backup. You have write ahead logs matching the backup. And you have a wall archive, right? Exact same setup that we have to create a replica. Okay, a physical replica. Uh, but what we do is instead of recovering it back to the same server, we recover it somewhere else. So remember when we did point in time recovery, we deleted the

data directory. We just recovered it. That was the cp-r that kind of confused you. Um, what I'm doing is I'm now recovering the base backup somewhere else. Okay, looks exactly like that. It's got the squiggles because we know it's not valid. We have the archive directory still somewhere else. Hopefully, it's a place that can be accessed from both servers. Okay. And now we're basically going to re

we're going to create a new cluster by doing effectively point in time recovery somewhere else. Like that's kind of how it works. Okay. So, we basically recover we start our server and effectively we are writing replaying the write ahead log matching the backup because remember I said it's going to be corrupt. So it's going to replay that. It's going to get this uh uh dimmer. Okay. And

then it's going to go away. And once it goes away, all of a sudden something special happens. Now we create a network between this Postgress worker and this cluster this replica. And now every time a write ahead log record is is is replayed, it gets sent over here. And now we actually are using the right a-head log also to get right ahead log records. So we're we're

pulling right ahead log records here to get up to current. And then once we get up to current we can then go back over here and start pulling from the wall and we now have a functioning system. Okay. So effectively what we're doing is we're recovering from the crash which is what happens Then we're recovering from the wall. And then as needed and then we're going to

go do a network connection. We're going to pull from from the primary. And then we're streaming wall records. We're just in continuous crash recovery, continuous replay, whatever you want to call it. Continuous archive recovery. It's the same thing, right? That's kind of got what what got me excited about doing the talk was kind of bringing this all together. Okay. So, how do we set it up? We

basically the the part in red is the most important part. This is our network connection. Okay. How is the replica going to communicate to the primary? So the how do we connect? We we specify it here. And now if I create a table on the primary and I do an insert all of a sudden the record appears in the replica. If I then update the row the

update appears in the replica. And if I delete the table, I drop the table, I get an error because there's no rec there's no table in. Okay, you can see how they're kind of building kind of everything's kind of building on each other. And you can do this in a hierarchal system. It doesn't have to be connecting direct to the primary a replica connect to a replica

connect to another replica, right? There's a whole tree system that you can create here. Any questions? We only got through that section easily because we had all that stuff in the beginning. So let's talk about logical replication. This is so significantly different than everything we've talked about so far because in this setting we are not making an absolute copy of the rep of the primary. We're merely

copying some tables over. Okay. So the way it typically works um and I'm sorry for the text being so small. I probably need to correct that. Um, I'll try and make it I'll try and make it bigger next time. Um, but effectively what happens is you create the table on the on the primary and then you create an empty table on the replica. Okay. You then um

create a publisher uh and a and and and on the on the primary you then create a subscriber on the replica and then effectively what happens is a table copy operation starts over the network between the the primary and the replica basically populating the table and then we'd use logical decoding of the write ahead log to continuously update that table. So again, significantly different than the previous

one. We're having to create the T structure on both sides and effectively we get a copy to get the initial version over and then we we're have now a change log that we're adding to that table. So just quite a bit different the way it works. We do a base backup in this case just like before. Um we set the logical uh the right ahead log level

to logical. Um and then we can effectively do this. We create a table on the primary. We create a publication on the primary. We create the table on the replica. We create a subscription on the replica. And we tell the replica how to communic how to connect to the primary. We give the name of the publication name. And now when I insert into the primary, the replica

gets the row. When I update the primary, the primary gets the row. Okay. Um, so you can set up all sorts of really creative solutions here for logical replication. You can have a publisher which can can have multiple subscribers. This is like a publish but pub sub kind of a broadcast option. You can have an aggregator where you have a bunch of subscribers sending up to a

publisher, right? Where you're basically aggregating all the data up um into a central location. We can do birectional. This would be basically part of a table gets replicated, the other part goes the other way. Like it's very complicated, but as long as the rows don't m don't conflict, you can do it. Um, you can do it with partitioning. So, as I said, so we talked about unlogged

and logged partitions. You can have one partition go one way, the partition go the other way. Kind of cool. And again, we can also do it row-wise as well. Um, but again, you have to worry about Uh we could even do it for upgrades. So major virtual version version upgrades. A lot of people using instead of PG upgrade they do this logical Okay. Any questions? Uh, so

I may have missed it on the slide, but um the subscriber it looked like it had to read through the wall files and is it only picking out the changes to the tables that it cares about, >> but it gets all of the >> it sees everything, but it only picks up the parts. Remember I said there's a thing called, and I'm sorry it's so small, but

it's saying logically decoded wall. So it basically decodes the wall and then only picks apart the ones that it's subscribed to. Okay. Yeah. Kind of When you talked about the continuous archiving, you said uh it was supposed to be in a separate machine, right? Like you you kind of making a backup. But in this situation when you're talking about replication, you kind of have a backup on

the replica. And where would the archive live in this situation? >> Yeah, I this was just for a demo. In every case, your replica would be somewhere else. Your base backup would be stored on a different machine. Your right ahead archive your whole archive would be on a different machine. This was just a demo. >> Okay. But the the two the primary and the replicas will will

talk directly, right? They won't go through a separate machine. have it has to be you're typically going to have some storage is accessible from both machines like NFS or something. Yeah. Okay. So, I just want to finish up with something very simple and that's logical replication slots. You've probably heard of them a lot. I will tell you when I heard of them I thought they were like

a like a slot sounds like something that's like you store it in. It's not that at all. It's just a location. So, this is a checkpoint. You know what this does? But effectively when you're doing streaming if you want to retain like you want to make sure that you don't recycle any of your wall that you need, you create a replication slot and that replication slot gets

advanced and the system kind of knows where all the replication slots are and it will only recycle things that are older than the oldest replication slot. So these replication slots are really just markers that get moved forward. They don't store data. They're just markers in the wall and effectively you can write it down here in the in the blue you can see but it's basically just a

marker and this is how we prevent rep a primary from recycling wall that a replica may need. That's all that replication slots are. Okay, here's how you set it up. You create a replication slot um and effectively that's what the replic you can query the replication slots. um you can and and and you can see where the replication slot is. You can see where the LSN is

and so forth. Okay. So, I just wanted I that was really not a big section. I just wanted to clarify what replication slots aren't and what they are. They're just really markers that we use because if you're worried about the wall going away that a replica might need, replication slots effectively prevent that from happening. Okay, thank you very much. I was not able to finish early. I

knew it wasn't going to happen. Um, but anyway, you have an hour for lunch. I hope you're having a wonderful conference and thank No, I think I think I think you guys Appreciate it. I think it worked well. Sibilance. Civilence. So, so I could start now. Maybe you guys can hear me, right? Like, you know, the trade floor thing is open, so I figure everyone's getting t-shirts

and swag. volume's low. I guess I'll talk louder and I'll hold it closer. Okay. I'll wait for this one guy. He's like, "Yeah, we're waiting for you, man. This is all on you." Oh, well, somebody else coming in now. No pressure. All right, I guess I'll kick this thing off. Um, thank you for coming. I'll start with that. So, this is a vacuuming large tables. Uh, it's

a Postgress talk. Not really much about vacuum, but seems like it's a popular topic, so I figured if I called it that, then people would show up. So, it mostly seems like it worked. like all good talks, this one starts with a problem. So uh going back now it's what is that? Four years, four and a half almost. You're going to turn me up. Hold on. I

couldn't talk loud enough and hold it high enough. I got I got developer arms. You'd think the muscles I could hold this mic closer, but I can't do it. He doesn't know how to Oh, yeah, dude. That definitely that did something. All right, that's I got more buzz. So, that sounds good. More buzz is always Disaster follows me around, as you will soon see. So, okay. So,

I I tweeted this out. Uh I guess we're still allowed to use that word, right? Um I tweeted this out uh about four years ago. Uh, and I just sort of said like, hey, I'm I'm having this issue in my database. Uh, and I put this little SQL in there. So, so what this is is basically like if you select the age of frozen X ID from

PG database. Uh, let me start with this. How many of you familiar with transaction ID wraparound, right? Like I not like you went through it, but you heard it. Okay. So, so the basic concept in Postgress is uh essentially you can do about two billion write transactions and then at that point you will need to have vacuumed like any particular table right or the database shuts down

and explodes basically. So which is it sounds as bad as it is but it doesn't actually happen all that often. Um, but here I faced a situation where I was at 1.9 billion, which is like way closer to that number than you ever really should be because there's actually a lot of fail safes in place. But I I realized I was at this point and I sort

of thought I'm going to put this someplace where I can document it where I might be able to refer to it later in case I don't have access to any of the systems at work because maybe I don't, you know, have a job anymore. Um, and so I posted it on Twitter and I said, you know, feel free to like and subscribe. Um, now I have to

give a few disclaimers. So when I first gave this talk, uh, it was about three years after that incident, which was a really important thing because some of this was like covered by NDAs and stuff and I couldn't talk about it until like three years had elapsed. So that was the first time I gave the talk so I could get past those NDAs. But then this weird

twist of fate happened where I took a job at Amazon and uh some of this is going to involve Amazon and I will speak honestly in this talk about what I know and don't know. Um but but there's going to be things in here that I'm going to say that Amazon will not agree with and will not say. And so you know there's no uh there's no

this is all not official Amazon propaganda. is uh completely the opposite of whatever that would be. So, I just want you to know that. Uh and then once I leave, so any while we're here, you can ask any questions and I'll answer them to the best of my knowledge. Um and then when I walk out the door, I'll give you the official Amazon answers like they would

give you if you were to ask them about this. So, um so those are some disclaimers just up front so you're aware. But so I had this problem database wraparound was going on. Now the database in question uh was a decent sized database uh it was actually on Postgress 10 and I know you're like wow Postgress 10 like that's super old but if you think about the

time that this was so in 2021 Postgress 10 would be like running Postgress 12 or 13 today. So anybody here still running 12 and 13? No this is like oh well okay we have some some honest people some people nobody that looks happy about it. Um so uh so and actually we would have upgraded these databases. So this one is is pretty decent size. So the main

culprit in the story is we have this 10 terabyte table. 5 tab of that is the heap. So it's like the main actual data of the table and then we had 20 indexes on that table uh which comprised about another five terabytes of size amongst the different indexes that were there. We had XID exhaustion. So that's that marker that would happen every 10 days on this system.

So, we were cranking through on traffic pretty decently. Uh, and so vacuum had always been kind of tenuous on this, right? Like we knew like if you're vacuuming every 10 days for wraparound prevention, you're, you know, you're pushing it pretty hard, but for the most part, actually, this thing had been running for months without any problems at all. So, you I will just tell you, we had

bigger problems than this. So this might sound bad but this was not actually one of the bad problems until it really was one of the bad problems. So uh I need to talk a little bit about how vacuum works because there's a lot of people who talk about how vacuum works and then usually we would describe it like this like when you run a vacuum in Postgress

what does vacuum do? Well it goes through and it scans the table. It looks for all the dead rows in your table and like marks that stuff and then once it knows where all the dead rows are, it goes over and cleans up all your indexes and you know shrinks all the space and everything goes fast and it's super awesome. But that's not really how vacuum works.

So how vacuum actually works, there's a whole bunch of steps to it. This is the main thing. Uh so we do this like initialization, right? Then we go scan the heap which is actually scanning all the data and that takes some amount of time. We then vacuum the indexes. So we go try to do some cleanup over there and we go back to the heap and then

we go back to the indexes. Then we got to truncate the heap which can cause its own issues and then we do a final cleanup and that's a full vacuum run. And when I say like how were we managing vacuum in this system? Uh so the way we had this we had custom code. I like that term custom code. It just means a shell script, right? Like

that's it's custom code. Shell script. Um so we had a shell script. I mean we had custom code. Uh, and the main thing like we couldn't really rely on autovacuum because we were very like we were pushing the system hard enough that like autovacuum at the wrong time of day would cause like outages. So what we basically were doing was using our custom code to run vacuums

in the off peak hours based on traffic levels. Uh, and off peak in this system was like 2500 right transactions a second. So not really slow time, right? There was still a fair amount of activity going on, but that's the low point of activity. So that's where we could run it. Uh and so we would run that manually or I mean it's like a chron job, right?

That would kick off our custom code and run the the vacuum processes. And then we had this like sort of series of unfortunate events. Uh which I don't like it's one of those like I don't actually know what happened because I could never get anyone to admit what they did, John. Um but uh so it was five days before I had put this series of tweets out

where for some reason the vacuum like ended up in this position where we didn't think it was going to finish, right? And we were kind of looking at this like well okay when we look at these different phases, right? This vacuum in indexes piece of this puzzle here for us that would take at least two days per loop of the indexes. And we knew that we always

had to loop twice whenever we ran a vacuum on this particular table. So the minimum possible time a vacuum would take would be, you know, four days would be expected. And so at this point, like we're 5 days past that point and we're at 1.5 billion transaction age. That's like when we realize there's a problem going on here. And you may hear like these people that tell

these stories about like, oh, you know, vacuum takes like x many days or whatever to run. Like yeah, sometimes it does take that long for vacuum to run. Like it it's true story, bro. The thing is like vacuum is actually worse than what I'm telling you here. The way vacuum really works, it's not this like series of steps that happens. Like there is this loop piece that

I mentioned, right? So we have these loops that are going on because the way that vacuum works really is when it scans the heap, it tries to find all of the dead rows, right? So everything has been an update or delete or, you know, constraint failures or whatever. So all those dead rows, it collects a list of those and then it tries to go clean up the

indexes. But there is a hard cap in the system as to how many of those dead rows it can actually handle at one time. So if it hits that hard cap, it goes over and starts working on the indexes, but when it gets done, it has to go back and start looking for more dead rows. And you can kind of calculate this out depending on your settings

as to what that cap is going to be, right? And so for us like the scanning the heap and the vacuuming the heap I mean they took time 30 minutes an hour something like that but that wasn't really what the problem was. The problem was doing this like vacuuming indexes and then having to do loops on it. And when you think about like when it goes and

tries to like clean up the indexes it's not there's those aren't indexed lookups right it's basically just a giant sequential scan of the index files because it's looking for these things called cins which are like little item pointers right back to the rows in the table. So it can't do index lookup. it just has to scan the entire table and and and deal with whatever it or

sorry scan the entire index and deal with whatever it finds. So this is what leads to these loops. It's actually kind of worse than that though the larger your database gets. So there's a setting called autovacuum vacuum scale factor it's set to 0 2 which basically means I want you to vacuum at like 20% of dead rows in the table. So like if you got a 100

rows, right, it's going to vacuum when it's got 20 dead rows. The math gets harder from there. But let's say like 100,000, it's going to be like 20,000 dead rows. It's going to it's going to want to do its vacuum, right? So the hard cap that I'm speaking of is there's this setting called maintenance workme. And we tell people like if you're going to do like index

builds or vacuums or whatever, like you can set this higher and you can set it to much larger values than one gigabyte. But for the purposes of keeping track of how many dead rows you have, the maximum it can be is one gigabyte. So it's in the code for real. So what that means is if you do the math on how big is a ced, so that's

what it tracks is these little item pointers, right? You basically with the default setting, you can get 179 million dead rows before it will have to loop more than once. And if you have more than that, it's guaranteed you're going to do a loop. So in our case, these tables were, you know, billions of rows. Uh we actually set our stuff more uh more like aggressive than

this. Um but we would still get more than that many dead rows in the table. Uh so like one I would say don't ever use the default scale factor. So I like on any system I'm on, this is one of the first things I look at. I move it to 10% immediately and then maybe I tune it farther from there. But like certainly 20% is a bad

uh default. Um, but if you have a table, so if you're looking like how big are my tables, am I susceptible to this? If you have the default settings, if you get more than 895 million rows in your table, you're basically saying like, oh, I'm going to start doing loops on my indexes. And maybe that's fine if you don't have a lot, but like again, in my

case, right, we had terabytes of indexes that it had to scan through. So, okay. So, and you don't want these multiple index passes, right? That's bad. Uh so with our thing like again we're doing two days per loop of the indexes and we know we have to do at least two right because we know we have more than 179 million dead rows. Uh so that leads to

four days per vacuum process as we go through. So we can see that like at five days ago what sort of what happened what I can kind of tell you is like somebody anybody have one of those scripts where like uh like when you get too many connections on your database you have like a little script that you run that like kills all the connections like these

things exist out there in the world. I'm not saying that they should but a lot of places have them. So, we had one that was like that and it mostly had fail safes, but somebody figured out how to not use the fail safes, I guess, and ended up killing the vacuum process that we were running from our custom code. For those that walked in late, I'm going

to share a secret with you. Custom code means shell script, but it's still, you know, it's proprietary IP. It's very important. Um, so in any case, so that got killed, right? And then the vacuum process started over like it would normally do. Uh, and then it started running, but then our alarm started going off of like, hey, your transaction age is like way higher than it should

be, right? And I'm like, ah, okay, this seems bad. So, I've posted I start posting stuff on Twitter. I get a response uh from this guy Peter Gagan. Uh, he's a committer on the Postgress project, you know, brilliant guy, does all this work on indexes and vacuums and all that stuff. He he tweets me back. He's like, "Hey, this probably isn't much use to you now, but

all of these like patterns that you're talking about, like there are enhancements in Postgress 12, 13, and 14 that will help you with this, right?" Like before 12, we had like low cardality index storage. Uh, and that has kind of been solved. Uh, right? Large groups of duplicates. I should go into this a little bit more. Uh, there in the past were stored in random order. Now

it's sort of cleaned up and stored in a better way. But it used to in like it would amplify the work that vacuum had to do, right? Uh especially like write related stuff which would slow down your vacuums because they're doing more writes during the vacuum process. Now I remember reading this and I'm thinking to myself like you know he's not wrong. That really wasn't any use

to me at all at the time. I mean yeah I'm I'm a Postgress 10 so it's great that these features are all there and like it's you know wouldn't be a problem for somebody else but uh it was a problem for me. Um one of the other features that he worked on uh and this again like this actually would have helped. So if I can reduce right

amplification during the vacuum process, vacuuming goes faster, maybe I don't run out of time while I'm doing all this stuff, right? Another great feature he worked on and I had to have an AI slide. So this is my AI slide. I I said, "Can you draw me a diagram of what DDUP looks like?" And this is what I got. So um but there's this process uh known

as DDUP. And I actually thought all databases work like this. And I was quite surprised when I found out like oh none of the databases work like this. So but now Postgress does. So that's cool. So in version 13 uh Peter actually did the work on well he was sort of led the work on this I should say um where the idea is like so if you

have an index normally if you think of an index you're like every row in the table right uh think of like a primary key where it's like 1 2 3 4 5 6 7 9 10 right if you go look at the index there's all those same rows over there 1 2 3 4 5 6 7 9 10 and it's all sorted right and if you had

like film titles or whatever right like they'd be sorted in the index alphabetically or Uh but what ddupe does is for indexes where you have you know lots of values or lots of rows that refer to what is essentially the same value. So imagine you had you know shirts uh a shirts table and then a color of shirts right and so there's a lot of white shirts

and a lot of black shirts and a lot of blue shirts. In most databases, the way they actually would implement that is like for every row in the table, you'd have the word white in your index, right? And so the index would be laid out white, white, white, white, white all the way down through, right? And then you get to the black ones, black, black, black, black,

black. And so the idea behind DDUP was basically like, what if we flattened all that and we just put the word white in there once? And we'd still need all the row pointers to keep track of which rows are actually in the table that match this. But the amount of space we would save by taking out especially on things like large text fields we'd save a tremendous

amount of space and it doesn't really help for like primary keys right because every row is unique. So everything is going to point one to one but for any other index and again we had 20 indexes across this table. So three of those were unique because application developers love reinventing Uyu ids um but the other 17 were all like indexes that had duplicate you know rows and

and multiple values. So all of those would have been smaller and again like that loop part on the index was the main thing that was really killing us on these vacuum times. So if all our indexes are smaller, right, it takes less RAM. Again, it's less IO. The whole index pro process goes faster with vacuum. Would have been way better. We we didn't have that. So uh

he also mentioned this thing about bottomup index deletion. uh which is a process where when uh you're doing actual updates or deletes within like a SQL statement if it looks at the page that it's actually writing the new rows on and it sees that it can clean up within that page it'll do the work that vacuum would have done in the past right and it'll just do

that right in the process if it can if it if it can't do it and it fails like it's like almost like an oop so it's like very sort of opportunistic optimization of that process but again it keeps your index es from growing as bloated and getting as large on disk as otherwise would happen. So this is like another thing that that he had put in there

and you know this other guy was like yeah man dude I I love the way it works and you know he's like really excited about it and I'm like I'm glad you're getting excited about these features that aren't helping me with my freaking wraparound problem So but it is actually a cool feature and I'm glad it's there and it does help on newer versions, right? Like so

that's Uh and then I got this other one. So, Alvaro Herrera, I like to call him good Alvaro, uh, because he he helped me here. So, he's, you know, he's a good guy. Um, but so he mentions, he's like, you know, this problem that you're facing right now, like it's pretty hard to manage this without this vacuum option that I've put in where you can say index

cleanup equals off. And it avoids having to scan the indexes. Uh, and that's in Prescrest 12, right? And again, I'm like, wow, that's great. Um, and this actually is a really cool feature. And I knew about this feature. I'd actually had another discussion several months ago. So like in January of that same year, Peter had mentioned this and this is like it's fantastic because like this is

like the foreshadowing of what my life is going to be in about nine months, right? He's like just a public service announcement. If you're ever involved in a Postgress XID wraparound related emergency, consider using vacuum's index cleanup option for vacuum index cleanup table parameter. This way the vacuum just does the freezing stuff, right? And again, that's what our problem was. We were approaching that two billion mark

and we needed the table to be taken care of. We didn't really care about the index blo at the time. So if we'd had the option to just scan the heap and again for us doing that heap scan piece was like maybe an hour or two. So like an hour or two would have gone by and we'd have been out of this wraparound problem. And then we

can go back and deal with indexes and figure that out, right? Uh, so this is totally like exactly the advice that I would, you know, kind of have needed nine months later had I been running on this version. And I even mentioned to him at the time though, I was like, "Hey man, that's pretty awesome. Uh, maybe autovacuum for wraparound should just do that automatically, right?" Because

like if you're in an autovacuum for wraparound, like what's the problem? The problem is you're facing a wraparound. So if it just automatically said, "I don't care about the indexes for you." like it scan the heap. That'd be way less time for everybody and then you're much more likely to avoid any kind of wraparound problem. He's like, "Yeah, that's a good idea." He ended up uh he

pointed me to Mazahiko Sawada. Uh he's a guy who actually does work with me at Amazon now. Um he did go on to implement that uh into Postgress. So I think if you're on Postgress 14 or above, like that is how autovacuum for wraparound already will work for you. you don't have to do anything like it just does that. Um there's a new like fail safe age

parameter that they put in with that. Uh but that you know again like it would have made this problem it would have like completely eliminated the problem because we'd still have to deal with the indexes and whatnot, right? But but it certainly would have made the emergency part of this problem go away. Of course as I said like none of these things were actually helping me and

my problem at the time right because I'm on Postgress 10 and we haven't upgraded and I I will just note like we would have upgraded these databases. We had plans to do it. Um, I don't want to say the name of the company where I was at. Uh, it like maybe rhymes with floor smash. Um, but like this is like pandemic time. So like they had this

like fiveyear growth plan and like suddenly the five-year growth plan was like in five weeks they had hit all those numbers. So uh they got sort of caught. They didn't realize we were going to have a pandemic. I tried to tell them but nobody listened to me. So uh so we got behind in upgrading all our databases anyway. So, so that's why we were still on 10.

Um, anyway, so we had about a few days to work when we figured out this problem was going on and we said like, okay, well, we can't take the system down because then like orders stop all around the world, right? That's bad. Um, so we came up with these three no downtime plans. Uh, one was uh we were going to do we had this idea around emergency

partitioning. Maybe we could do some partitioning jazz and like put new data over here and something over there and that would get us out of this problem. Um we thought about using PG Repack. How many people are familiar with PG Repack? Okay, most people are. I will talk about a little bit more in a second. Uh and then we had this other plan which I called uh

derenate via foreign data wrappers. Anybody here actually know what the word derenate means? Yeah, I wouldn't expect anyone to actually know that word. Um so I well let me just note. So we hit the third index scan loop, right? We're basically watching this thing go, right? And I'm like, if we hit a third index scan loop, it means two more days of vacuum running. And so we

are guaranteed to have wraparound. So we're like refreshing on pgstad vacuum like over and over and over again. Uh and then eventually we hit the third loop. And I was like, well, okay. Um we know we're going to have the database blow up on us if we don't do something drastic. So get ready to do something drastic. Um, so I actually this is one of those times

where I was like I'm gonna go in I'm going to cancel the vacuum process, you know, in psql like I will kill it. Uh, and I sort of felt like I should probably be the one to kill this because like if this goes horribly wrong and it certainly seemed like it might. Uh, I might need a new job. And I'm not saying that I work at Amazon

now and I don't work at the place that I used to work at before because this happened, but we haven't gotten to the end of the talk yet, so we'll see. Um anyway, so I ran the cancel vacuum and like it stopped, right? And then I was like, "Okay, no going back now." Like what what's a we burned the we burned the boats and we're going to

go fix this thing. So, okay. So, what does erase mean? Uh so, this is kind of weird. I I only actually know this. Uh so, there's a hackers conference that they do for Postgress up in Canada and I like this drink called root beer. Everybody know what root beer is? Like, okay. So, they don't sell root beer in Canada, like not with the amount of ease that

you can get it in this country. Um, but I have found it up there and on some of the bottles, but I noticed uh they call it racinet. Uh, and I think that's the French word for root beer, I guess. Um, but I then I was like, why is that the word? And I looked it up. This is like prei, so I had to actually like Google

and like look at dictionary.com and stuff. And it's like the Latin word meaning to like uproot, right? And it's root beer. So like it all comes together I guess. I don't know. Uh the basic idea of what this was was we had been working on this prototype system where we would set up foreign data wrappers on a table. Uh and then all of those would point to

like a different major version of Postgress with triggers to do inserts and updates and deletes across the other system and then like have updates and deletes that could flow back and do like this kind of circular replication thing. We had a prototype of it. It was definitely not ready for prime time but prime time seemed to be ready for us. So, we thought maybe we'll just have

to use that thing and see if we can fix it. Um, this is sort of a diagram, not AI generated in case you weren't sure. Uh, this is a diagram. This is what people, this is what diagrams look like when people make them. So, the idea again, so we have our XID table. This is what we're thinking, right? We put these triggers on. Inserts and updates will

come into the XID table. They go through the foreign data wrapper. They go over to like a version 17, which would have been like the hot spanking new thing. Uh, and then that would have saved us. There were a lot of issues with that like other applications and and like how do you switch the apps from one database to the other. Uh we also knew like the

amount of traffic we had like the foreigner data rappers can't actually go directly to a database like we had to spin up like PG bouncer pools in the middle in order to handle the amount of traffic that we had. Um we didn't really have a way to do rollback. We had a theoretical way that maybe it would work possibly. Uh so we kind of said like you

know maybe that's too experimental. Let's do the other things. Uh so we thought like oh we'll do like removing bloat with pgre repack. Uh and I point to the AWS documentation here because this is the part where I mention that all these systems are actually running on RDS. So we don't actually have like command line access to anything like we're at the you know we're we're trying

to work with the AWS people and we're telling them like we got this wraparound problem going on. Uh and they had written this article. So like pgreg there's other solutions that do this in the postgress community but repack was the one you can use in RDS. So that's why we used it primarily and we had used it before. Uh what pgregac does just sort of a quick

explanation is uh sort of behind the scenes it creates another table that is a copy of the table that you are trying to repack and then copies all of the data out of your table into the new table. It builds a matching set of indexes and then it secretly switches some data in the system catalogs to tell it like oh no that table's not here anymore. It's

actually over there and theoretically nobody notices and it works wonderful. And we have actually used it a lot. Uh so it does work really well. It sounds very terrifying when you describe it that way and it's actually a little bit worse than what I said but um but it's used a lot in production so no concerns that it would like totally destroy your data. Um so we

started doing that. Um we were also very familiar with this concept of using like views and triggers and partitions to like reshape data. So there just happens to be a blog post from the guys at Door Dash uh doing engineering and maybe my name is mentioned somewhere in this article uh about doing this where like you build another table and you or like put a view between

it and you do partitions and move data around. So, so like we had pretty good experience with that and we sort of realized like maybe we need to like merge a few of these solutions Um, so we had our three no downtime plans. We said don't do the foreign data wrappers. We actually ended up doing partitioning and repacking together. Uh, and we thought that was going to

do the thing. So, one of the things that people had a problem with doing partitioning. So you might say like, "Hey, maybe you shouldn't have 5 terabyte tables in your system that aren't partitioned." And if you did say that, you probably sound like somebody who works at Amazon. Um, that's what they told us. So anyways, uh, so people were concerned about doing partitioning because we had queries

that were not like on the primary key. So we didn't really have a good like partition column or set of columns, right? The one unique key that we thought was like the really good unique key was like four columns wide. uh and there were just a ton of queries that didn't use any of those columns. So if you partition those now you're doing like these weird index

scans across partitions and people are like this is going to cause too much latency. Uh and so they had actually resisted partitioning this particular table for probably at least a year at that point. Um the thing is like when you go to your boss, this is like a a pro tip. Uh if you ever have to like you want to force your way into like a partition

table, you go to your boss and you say, "We're going to wrap around and like shut down in like two days. So if we don't partition this thing now, like the whole system shuts down and blows up and we go out of business." And then they'll be like, "Well, okay, let's do partitioning." Like how bad could it be? so yeah, so we got not only did we

get sign off from everybody like, "Okay, go ahead do the partitioning." Like we had application developers in there. They're like, "Oh, how can we optimize these queries?" And we're like, "Oh, we have a whole list. Do you remember all those emails we've been sending you about ways to make this better? And like like we had tables where it was like there's like six different columns that are

all like, you know, ordered time and order sent time and order deliver time and all that. And it was like the same row just gets updated over and over and over again. I'm like, yeah, maybe if we don't like do four updates in a transaction right after you insert the row, like you could just make that one update because you have all the data in one go.

You'd like reduce the number of transactions. Like so we started doing like all these changes, right? applications are a we're oh this is great like we're happy to help you with this stuff. I'm like yeah well we'll see if we have jobs tomorrow. Um so the way we end up doing this we have our XID table, right? This is the one that's going to blow up and

take us all out. Inserts and updates uh and deletes all go into that table. What we wanted to move to was this idea where we we had uh so we used old school partitioning like the table inheritancebased stuff which is I like to call it real partitioning but some people get mad. Um, so we used old school like table inheritance. What we basically did was we created

a new parent partition and then we attached the XID piece to that parent partition and we created another partition underneath it called new partition. And the idea was basically we set it up so all new inserts and updates would flow into the new partition. And our idea was basically like we knew that most rows they're updated, you know, within a few hours is where they get most

of their traffic. And then once they're like a day or two old, like they don't get a lot of updates. So what we were hoping was when we run the repack, we can repack just this partition over here and it'll be mostly static because one of the things that re repack has to deal with is while it's doing that big copy of data and rebuilding all those

indexes, it has to keep track of all the changes that are going on and then apply those before it can do the little system catalog switcheroo. So, we thought if we can keep that as small as possible, like we might actually get the repack to finish in time because it won't have to do all this like transactional replay. So, we set this all up and we got

it and we got the repack going. We felt pretty good about this like we doing the math on like is this thing going to finish in time, right? Um, we did have a lot of bloat so we knew it was like 10 terabytes. We had to talk to AWS about like you we got to make sure there's like storage underneath because we can't run out of storage.

we're, you know, going to blow this thing up like an extra 10 terabytes. Took care of that. And then we're like watching the thing, right? 1.7 billion, 1.8 billion, 1.9 billion. That when it got to the 1.9 is when I started posting on Twitter, right? Because then I'm like, well, this whole like all this documentation going to go away real quick if if I'm wrong about this.

So uh and so our theory with this was like when you do the repack and you copy all the data if you copy all the data from one table into another then you know that like all of that data in the new table like it should be visible to every transaction in the system because it's all just written right it has a new xmin every row is

marked as visible for every transaction that's in the system and we're like that should solve our problem because then it'll know these are all rows that are visible. This is where I take a dramatic pause. So, fun fact, Repack doesn't actually change the XID information in the system cataloges. So, it changes the pointers of like where is your data on disk, but it doesn't actually say anything

about the visibility information. So, we're just like, oh, fantastic. Like, I mean, it finished and we're like, okay, are we good? And it was like, "Oh no, it still thinks all this data is like, you know, years old or whatever." Like, "What the heck is going on here?" So, we go through and we read through the repack code again and we're like, "Ah, okay. I think we

got this figured out." Um, but we're kind of doomed at this point because if Repac doesn't update that stuff, like there's no other actual alternative. We knew all the data should be fine, right? Uh, and so then there's like basically only one option at this point. So, like we know all the data should be visible, but it's not. What we need to do is we need to

go update the system cataloges because we'll go update that we know what you know which columns in the system catalog like you know we can we can read code not good enough to realize repack wasn't going to solve the problem but we felt pretty good that we knew like here's the things we got to update. So, we talked to AWS. We get on a call with them.

We're like, "Listen, I know that you guys are like not big on this concept of updating system tables." Uh, but like we're basically going to go down because we have no other way to solve this problem. Um, we've read through the code. Like, here's exactly the parts that are important. Here's the SQL statements we need you to run to update the system catalogs. Uh, we need you

to do us a favor. I know what the size of our AWS bill is. like you're I know this is not what you want to do, but this is what you have to do. And they looked at us reluctantly and they're like, well, you know, the thing is like uh yeah, we don't update system cataloges for anybody. Not only do we not do it, we can't do

it. It would be impossible for us to do it. That's just not a thing we can do. We're sorry, but you're going to have to come up with some other solution. I said, listen, I just told you I saw the size of our AWS bills. Like, that is not an acceptable answer because you guys have rooe on the box, and if you got rooe on the box,

then you can do whatever you need to do to get it done, right? Like that's a thing. I'm like and just remember that when this system goes down, we're going to tell everybody it's cuz AWS wouldn't help us. And like that won't work for a lot of people because I've had a lot of customers who ran on AWS. Uh but it would definitely work for us because

we are a very big name at the moment, right? During the pandemic, people like to eat and this is one of the few ways they can. I said, 'Well, we'll go talk to our super VP of whatever Jonas in Amazon and we'll see what they say. We'll have our engineers look at it. We can't, you know, we're not we can't say whether we can do this or

like I'm telling you we can't do it. So, you guys should work on another plan, but we'll go run it up the flag and see what happens. I'm like, well, I'm like, okay, but just be aware that like within like, you know, seven hours at the most, this system is going to go down. So, don't take your, you know, too much time with this. So, we go

back and we start brainstorming. We're like, so foreign data rappers, huh? Yeah, this I guess we are going to do that plan after all that that we said was too crazy and isn't going to work. Let's do that. Uh, so, uh, we start looking at the foreign data wrapper thing and, you know, we're sitting there and a couple hours go by and all of a sudden like

our alarms turn off and we're like, what just happened? Something's going on with the database. So, we all jump in and we're like, oh, like transaction age looks good. We're like, everything seems fine. Like, what? I don't I don't understand what's going on here. So, we we're like, you know what? Like, I don't know. Run a vacuum on the table. like we're just going to vacuum it

again and make sure everything's fine. And we call up AWS. We're like, "Hey, we need you to get on the like I was going to say the phone, but obviously it's video calls, right? So get on the Zoom and we need to have a talk with you." And we get on the Zoom with them and we're like, "So, uh, we looked in the system catalog, look like

everything is updated and like did you guys just run that?" because we're thinking like, you know, they're going to get back to us and we're all going to get on a Zoom and we're going to update these catalogs all at the same time, you know, in a coordinated effort so that nothing blows up in case we're wrong about something. And they said, "Well, as it turns out,

we don't update system cataloges. We can't update system catalog. Even if we wanted to, we couldn't do it. So, I don't know what happened on your system, but uh yeah, no, we don't update system catalogs for anybody." And I was like, you know, normally I would have a problem with this, but right now I have a job still, so I guess I'm going to go do that

and then we'll talk about this more later. Uh, and so we went back to work on our system. We ran like vacuum freeze on the table. Uh, because we were paranoid at this point. They're like, did we actually do this correctly? And it turns out we actually had figured it all out uh, more or less uh, and it all worked. So this actually ends up being slightly

antilimmatic because like I don't know what happened apparently. But before it got to the two billion, right, like allegedly there was a system catalog update in the system. Uh, and then we were suddenly like a billion transactions in the clear and everything was good, right? We did our vacuums, we checked it all out, everything was happy. We scheduled this thing to get upgraded to a newer version

as soon as possible. Uh, and that's how it was. So, uh, we did a little bit of follow-up after this. Um, couldn't get any answers from our support friends. Uh but we did go talk to the people that do repack and we said hey you know it'd be fantastic if when you repack the table if you would actually also update the XID visibility and there was actually

previous discussion about this and people are like we don't know if that works and like would it be dangerous or whatever and we're like let me tell you a story. So uh this has actually been merged in so if you run repack today it will actually update visibility information uh and this could save your bacon if you needed it. Um now I have to talk about this

particular feature because this is the feature I didn't have access to that kind of inspired me to like I should tell this story. Uh so in Postgress 17 who here is running Postgress 17 or 18. Wow. So this is like a crazy I'm just gonna tell you I do a fair number of Postgress conferences and I've done this talk a number of times this year and so

now if you think like Postgress like a PG day somewhere is like the hardcore Postgress people because they go to that thing and like maybe scale is like you know we use Postgress for fun maybe that's the case but you are the largest crowd of people I've seen who have actually updated to a recent Postgress I'm the other ones like they're still running like Postgress 10 they're

like basically on the same thing I'm here just what are you doing so anyway so adaptive registry added in pestress 17. Uh it's a pretty complicated thing. There are some talks out there on this topic. I have sort of a simplified version. Uh it was implemented it by Maziko Sawada and John Naylor. Um basically it gets rid of that one gigabyte cap, right? So that thing that

sort of limits you in the number of rows that you can store like it got rid of that. And there had been many conversations that I had been in with people before and other people had had trying to figure out how to do that. And they came up with this idea like we could use radics trees in order to do that. And so it's another nonI generated

uh it's a not accurate but simple and it definitely does not work like this but I think it would give you the idea of like how like radic trees are kind of like self- collolapsing things uh that can hold more data without actually expanding the size of the space they need to hold the data. So if you imagine this first row where it says old way, I

think I actually have a pointer that you can almost see. Uh so if you imagine these are like TIDs in a table. So like page zero, row one, page zero, row two. The old way would be to just store every one of those like little ct IDs in like basically an array, right? And that was the one gigabyte cap. Once you had a gigabyte worth of those

numbers, like you were done. Do your index loops and and you know, go be happy. The new way, the idea of the radic tree is basically like, well, if you look at this sequence of numbers and you say we got 0, one, two, three, four, five, and then we go to page number one, row two. We can just put like a little marker in here that would

say, hey, everything from one to five, we need to track track that, right? We don't actually have to store all the things. We just put a little marker in there and it'll take much less space, right? And then maybe we got page one, row two, page one, row nine, and we can just we can skip past pages, right? everything from nine from nine through the end of

page one uh all the way to page two row two, right? Like we put the marker in. We know we got to keep track of all that stuff. So it turns out actually like the more dead rows that you would have in a table like almost the smaller this data structure has to be in order to hold this information. We did I I did a bunch of

benchmarking. A number of other people did as well when this patch was in there uh before it actually got released to see like does this solve the problem and I was amazed at how well it solved the problem. Uh this whole thing with the index loops like almost kind of goes away if you're on Postgress 17 or above. Um it it like you can actually replicate it.

It's really hard. Uh I've been keeping an eye so now being at Amazon I've actually been like keeping an eye on the sort of bug issue things that come in and I've told the guys that work in the support team uh like if you ever see one that's like XID wraparound on Postgress 17 or above like immediately let me know because I want to go look at

what they're doing because like benchmark wise like you really had to design a thing specifically to break the system that seemed like very not real world real world use case. So, uh, I kind of think it's a solved problem. Uh, and that to me is like that's the biggest game changer that like we don't even really have this problem anymore. I don't think so. If you have

a wraparound on a pre7 or on a post 17 or 18, like feel free to reach out. I'd be interested to see it. Um, I also sort of say this thing just as a general sense like I had posted this back then. Like I would say like this did not go the way we thought it would. Um, but I have seen a lot of stories, you know,

not so much anymore, but I've definitely heard these stories in the past like people are like, "We were running the vacuum and like there was an XID thing and the database shut down and you know, like we just let let the vacuum keep going because we didn't know anything else to do." And it's like, look, like there's Slack, right? There's like IRC, like I don't know, whatever.

There's Twitter, like whatever thing you're on, there's plenty of ways to like get there's mailing lists, like all that stuff. You can call support companies if you find yourself in an XID situation and you think it's all going to go to pot like start asking people start talking about it like people will help you with that stuff uh and there's a lot of things like Postgress gives

you a lot of capabilities as you saw we did this partition thing with the views and repack and all that uh and that would solve your problem now so you could theoretically solve this on your own also just wanted to say like the vacuum landscape has now changed tremendously I mean I really do believe like if you are you know anything pre17 uh that world and the

post7 world are very different you will see a lot of advice there are a lot of people on the internet that like to give conference talks about vacuum it's so popular I just included it as the name of mine just to get it in the sessions here um maybe a lot of what they're telling you could be outdated information uh it may not be oriented on the

problems you really need to be able to solve so just take that with a grain of salt like whenever you're looking when the AI tells you what the answer is like ask it for like where did you get that information because if it's super old like it may not actually apply. So just keep that in mind. Uh you know I'm like vacuum has a special place in

my heart. It looks like a scar. Um so you know like I keep my eye on this area of Postgress because it's done me so much damage. Uh and so I'm watching for like what are the problems that people are having now like on the newer systems. Uh, and it does seem like, you know, bloat has we haven't solved bloat in general, but we've solved like I

think the really big emergency things that require you to give conference talks. So, that is my story. Uh, and I I guess I'm out of time, but if anyone wants to ask me a question I'm not supposed to answer, I I guess well now I guess come up here and I'll I'll tell you, you know, before I get out the door. Um, but but that's it. So,

thanks everybody. Hello. All right, I can tell everybody can hear me. All right, it's time. Um, now I get to talk for an hour about how much I enjoyed making the system. But first, I'm going to put it to the test. So, the these this is the backup slide. Um that is if the demo fails um my demo is actually using the very system that I built

to create the slide so that I can talk about the system that I built and there's a reason I did that. Um aside from the fact that I like doing edgy let's see if it works. Um been having some VPN issues and so I'm using my hotspot with three bars right now. So that's pretty optimistic. Um this might actually work. I have a good feeling about this.

All right. Um, so the main reason that I used slides as my demo is because my my thesis is that if we simplify the AI infrastructure a little bit um using the tools that are time-t tested, we can actually get more out of the system like things like that are grossly missing from AI systems right now like reliability and trust and dependability and robustness I put my

money where my mouth is by generating these slides to show you that it is possible to build a reliable system um with a probabilistic core that is the LLM. It is important to note that the system that I created is not just uh able to power these slides but the same architecture can power your mid to moderately large enterprise systems or your individual use cases. Um yeah

I I guess let's let's look at the architecture problem. the there I think the major problem that I see and I keep hearing about in in all the AI talks here as well is we are hoping too much out of out of LLMs. So we hope that the LLM is going to give us the correct output. We hope that the individual components that we put in place

are going to somehow magically stay glued together. And we hope that uh a person would come in, a savior would come in and notice the failure is going to happen before it actually happens. But all those three problems make each other worse. So firstly, the the inherent nature of LLMs is that it is going to be probabilistic. You're not it is not a deterministic machine. And so

to expect it to come up with the correct output every time without engineering a reliable system around it is setting yourself up for failure. The second thing is if you have multiple components, which is not in itself inherently a bad thing, but if you're starting out with a complex architecture like that, it's going to add additional overhead, additional integration cost, additional coordination, monitoring for that additional work

that you have to do. And I'm not sure if people are building that necessarily because they need it right out of the bat or because there's a lot of information out there that gives you a a boilerplate for hey, you want to build out a rack system, these are the components that you need. And I kind of know that because I followed that at first and I

failed miserably. And sorry, oh my bad. Um, and I finally came to the realization that sometimes it's okay to rely on a 30 or 40 year old um, software system that's time- tested um, to do a a lot of the work that you need to do around ensuring reliability around these systems. One thing that always comes up when when I've discussed this with people, how how do

you enhance reliability of AI systems? People tell me that it's the prompt. The prompt is the way that you enhance and and and ensure rel reliability. If you structure the prompts better if you have JSON prompts or if you have XML prompts, if you have DSPY, if anybody's heard of that, it's a great tool. Um, you can you can get out of the LLM whatever it is

that you want and you can avoid anything that you don't want. The problem with that is no matter how good how perfectly structured your prompt is, the LLM can output a perfectly structured hallucinated response to it because that's how LLM's work. So that gap between a between the generation of content or the generation of information from the LLM and to the governance layer to the verifiability. That

gap is where a lot of money and and effort and time is wasted. So then I I asked myself a question. What if I made this database that I've worked on for so many years as a single source of truth for my AI system that I'm trying to build? And you might ask so what? Well, so if I use Postgress to do as many things as Postgress

can allow me to when I'm when I'm building out these systems, I can not only have full text within Postgress, I can have vector embeddings. I can have functions to operate on that data. I can have constraints and a good schema design to prevent insecure access to that data, which is also a major problem with with AI systems being built these days. By using every capability that

Postgress has, I can build out well Postgress plus some other components. We're going to see we're going to look at that in a in a couple of slides. But I can majorly use Postgress and build out a reliable AI system. Speaking of which, like post on on on the left hand side, we've we've got um things like roll out security and of course full text search and

metadata and vector embeddings and you get all of that for free right out of right out of the bat plus uh the PG vector extension. On the right hand side, we have a proprietary example of a vector database that does one thing and it does it very well, but it does one thing. and AI systems even even if you're looking at just rag systems are are not

just one thing and we're going to look at that but another important point is when you have all of that data together like in Postgress I I I can't have that I can do a single join query on a metadata plus a vector embedding and put an additional filter on it and kind of do a lot of things that to do in the right way the right

column way I would have to use multiple services, deal with syncing, deal with latency and I can avoid all that by just allowing Postgress to do what it does best. Speaking of what Postgress does best, there are a couple of extensions and in in core functionalities that make Postgress especially suitable for retrieval of data for LLM use cases. First of all, the hero PG vector. Um, I'm

sure everybody in here who knows a bit about rag and Postgress has heard of it. PG vector enables semantic search vector comparisons in Postgress. we're going to look into in in detail about we um I think in a few slides we have we might have I I'm not sure because these are generated live um but we might have a single query that shows how I've implemented a

a whole rag pipeline all within Postgress itself. So aside from PG vector there's another extension called PG triagram which enables fuzzy searching. So whereas PG vector does semantic search semantic similarity PG triagram does similarities like what if you are searching for a keyword and you make a mistake um I've seen a lot of people say postgra without the s instead of postgress so if somebody types postgra

pg triagramgram with its fuzzy search capability is going to determine they meant posgress so that's one of the things that pg triagramgram can do for you and finally Finally the third one is not actually an extension it comes with core posgress is TS vector and TS query and what they do is they enable keyword searching and and keyword distance. So if you have so let's say the

title of this talk postgress as an AI control plane um there are functions in TS vector that um the the ranking function and the tokenization it's going to break down each word and ignore the common words like and and it's going to keep postgress and AI let's say and system and it's going to look through all the documents stored in it to see what what documents have

all of these keywords which document have these keywords the most number of times that is the frequency and what is the distance between these keywords in each of the documents. So like if there is a document where there are only two words between Postgress and AI that's going to get a higher priority than a document where there are 500 words between uh an occurrence of the word

Postgress and an occurrence of AI. So that there there are different types of searches that um PG triagram and and TS vector provide in a combined fashion that enables lexical search and that is another core aspect of modern day rack systems and again if you're using a uh specialized vector databases to build a hybrid rack you'll have to have other components with Postgress you can do all

of these in in in one single system. If there's nothing else you take from this talk, this this is the realization I came to after trying and failing multiple times um with building um AI systems that worked. I realized that at its core LLMs are stochastic machines and at its core Postgress is and we use the deterministic architecture. We use the deterministic software to control, govern and

verify the stochastic software's output. And that's how we can guarantee well humanly possible guarantee that your system's going to be trustworthy and reliable that you're not going to get any outputs that you don't want to see. Um you're not going to end up uh majorly messing up your system. I think earlier in the talk I um heard just some some company um uh it was a blog

post by somebody who said they dropped the production database by asking AI to do something with Terraform and now they're without a production database and that that blog post is from today. So yeah that's one very bad example of what happens when you use the raw LLM without putting all the safety constraints around it. with this kind of mentality with this architecture in mind everything that is

verifiable and predictable I can store it in postgris whether it's observability whether it's evaluations whether it's metadata auditing you name it whether it's providence whether it's state management postcrist right now is is carrying out state management of these slides um by the way if you look at the top left corner you'll see a little text moving that is the control plane telemetry that data is being sent

live from Postgress right now and um using a Postgress feature called listen notify which enables serverside events to to be rendered within this this slide system we'll get more into that but but first schema design is one of the basic building blocks of good system design and Postgress has that ability, but you need to be the one in charge to make sure your schema is properly designed.

Postgress cannot out of the box do um like do all of these things that will basically ensure that your LLM or your AI component of your system cannot erase the audit trail. It cannot game the validation. It cannot game the the quality checks that are all in Postgress, right? It cannot do that because you don't allow it to do that because Postgress is a control plane with

all its features. It does not permit the LLM to do all of that. One way is all of the functions that that deal with like interacting with the data that lives in Postgress, including vectors, including metadata, including full text. All of those are only accessible via certain functions that have the security invoker property. And what that basically does is doesn't matter what the LLM is using to

call these functions. Whatever ends up calling these functions, that RO's privileges are activated. So you cannot, for example, let's say we have a commit function that commits each slide into the final deck that we're viewing right now. There's no way an LLM could do that because of security invoker being defined in the function because doesn't matter who created the function even if the super user created it.

But if LLM as long as it's using a a non-privileged non-s super user role and it doesn't have we haven't given it explicit access it cannot actually commit your slide. It can try to but it will fail. Postgress won't let it do that. And finally there's to to to the LLM to the system outside Postgress all of the audit trails everything that the LLM does every reason

it gives every failure that happens that's an appendon log LLM cannot change it to the LLM it does not have any delete or modification permissions it is append only you you don't even have to give it read and so keeping all that schema design and AI primitives in mind. This is the system architecture that I came up with. A better diagram is um in in the open

source repository that I'm going to point to later. And basically I'm using Postgress as the central feature that stores all the quality gates, all the validation gates, all the data, all the vectors, all the functions through which they can interact with. And that that is where MCP comes comes in handy and we're going to talk about that. Um I'm I'm using a certain set of observabilities that

you can effortlessly track because you're using Postgress and that enables evaluation because the better observability you have from the core if you're building the system from the ground up in a good way you're going to have more data and good data to evaluate it on. So all of that gets unlocked with with an architecture that's simplified and and you don't by simplifying architecture you're actually gaining more

than you're losing. Of course there's tradeoffs with all kinds of system designs. There's no system design that works perfectly for every solution, right? But with this architecture, you don't have to guess how complicated or how much you have to scale your system right off the bat. You don't have to copy a a framework that's given online with nine different services right out of the bat. You don't

have to um look at vendor marketing and go buy that and I I'm guilty of that. Initially when I started out I look what's out there and I just tried to replicate it and I failed. um instead you can build out the system from the ground up in in a good in a in a proper industry recommended way and then see what else you need to add

on top of it. So the the purpose of my talk isn't that Postgress can do everything and it can replace every other tool in your system. The purpose of my talk is Postgress is a good base to start at and then see if you even need something else aside from that. That that kind of makes sense, right? it it's it's traditional wisdom. Um having said that now

if you are a Postgress person only who's not interested in AI stuff uh you can tune out for the next two or three slides. Um but I'm going to cover what rag is. It's basically the diagram in there. Um oh and speaking of diagrams I have to say um there's a way to embed You know what? Let me cover RAG first and then I'll talk about different

kinds of RAG is basically an openbook exam. So I when I went to college, any exam that was open book, I liked it more because then I didn't have to cram things up. I could refer to whatever information I had on my desk and then determine the answer. I didn't have to memorize anything. And that's basically what RAG is for the LLM in a way. When LLMs

are created, when they are trained, they are trained on static data. But there are use cases that companies feel that individuals feel like, hey, um I have all of these nodes in Obsidian, which I did. I I did have notes in Obsidian, which I inserted, uh used Drag for to create these slides. And so that kind of notes it it's not possible for a generic LLM just

straight away to generate um slides off of the content of my personal notes, right? And that is a very simple example, but when you take it to enterprise scale, every enterprise has its own confidential documents, its own proprietary stuff that internally needs to be interrogated, interviewed. So your your chat bots, your IT support, your customer support, any kind of bots like that. Moreover, your uh analytics bots.

So, let's say you have a bug tracker or if you're um a team full of like Postgress people and and dealing with production systems, you you have a postmortem tracker. Um and all of that is powered by this this very system that is powering these slides right now. Now, what what rack stands for retrieval access generation. It's basically the process is you get a query from the

from the user or from whatever the input is and you embed that query. You you do a vector generation. You send it to a an external model um like text embedding three small or large depending on how much space you have and your your computing power. And then you get the vector back and then what you do is you look at the chunks already stored in your

database the the vector the PG vector chunks and you calculate the similarity of the queries embedding with the chunks embedding you can do that in multiple ways like I mentioned before. So this slide I want to sp um uh I want to give special attention to because that is the entire hybrid rag pipeline in one single query implemented in Postgress. That is actually a CTE. It's a

it's a common table expression. The first part of that is semantic similarity. That is what PG vector does and that is what your proprietary vector databases do as well. That is only what they do with Postgress. I use PG vector for semantics uh similarity which is um basically we use cosine similarity. If you if you remember calculus and and and and geometry from back in school um

and and we we perform those calculations using PG vector provided supported indexes. Um the major one is hierarchical navigable small world which basically gives you an approximate nearest neighbor search. So it it it brings all all the similar vector embeddings uh pulls out from from the chunks. And then the second part is is the lexical. Now in this case I know earlier I said you can use

fuzzy searching and keyword searching. I did not find much use case for a slide demo for fuzzy searching. So I only have keyword searching in here. But you can just as easily add the fuzzy search in here. But right now the lexical arm of the hybrid drag and posgress is basically using um ts rank cd function. So it's it's converting a a phrase a keyword a group

of words and using a jin index to see where all where it occurs and what the distance is etc. And it's it's calculating the rank that way. So basically the first arm is getting let's say and and all of the these defaults are also in posgress. So I think my default right now is 10 because it's a small deck. So it it the the semantic arm gets

the 10 most relevant most similar um chunks the document chunks. The lexical arm gets 10 different set of logic of of rankings of of chunks because that is using a completely different metric of similarity that is using lexical similarity as opposed to vector similarity. So now we've got two different lists. We got two different rankings. And now we want to combine them. Why? because sometimes vector similarity

doesn't always work in in the real world and real world rack systems. You will hardly ever find vector alone vectors working alone on on a on a on a production scale system. So RRF is what is being used in the final part of the query which is doing a a full outer join on on both the arms on both the rankings. And that formula you see is

is standard how RR RF formula is. I'm not going to go deeper than that. Um, but what what it's basically doing is it's not combining RRF stands for reciprocal rank fusion. It fuses the rankings, not the raw LLM scores. So, it's not combining. It's not looking at the raw similarity score of each document. It's looking at the rank of each document in each arm, right? And it's

then sorting out based on the weights that I give them. I think for for this um I gave more weight to similarity. I I I set it to 0.7 and to the lexical I set it to 0.3. So you can you can change those weights and balances. But what I get is a combined rank of similarity of vector similarity and lexical similarity all all in a single

list. And so that's all of the this would take two or three different services in a usual AI system. And you you don't really do I think you need to ask yourself that. I can't answer that for you. Do you really need There's something Postgress does not do and that's that's another process to further enhance the quality of your retrieval and that is the the right hand

side of of the slide. That is the cross- encoding. Why? Because cross encoding uses neural network. It uses an LLM model. Why do we need that? For better quality results. Basically the first part is casting a very wide net to get anything similar that you have in your carpass. So that that is a cheaper function. That is a cheaper method to get oh I want everything that's

similar. But then you want to further refine that. You want to get the exact document that that that you need right that that you need the content from. And for that you use a high precision costly slower model on a smaller on a final smaller subset of of ranked documents. So that way you don't have to spend time looking at each interaction. So basically what cross encoder

does and for this I've used a hugging face model um minim it's trained on marco it's basically trained on search engine data but it works pretty well for rack systems as well. And there are other options you can use. But what it does is the first stage of of rag is like you get a query and then you compute all the rankings of of the corpus that

you have, right? And then you combine them. The second one is more like if you it's kind of like um a a job interview. So, first you you you get all of the resumeumés that match your job, like the the the the people, the skills that you're looking for, but then you get them into a room to interview them oneonone to see how they interact, how they

how they blend in. And that's basically what the cross encoder is doing. It's putting it's it's combining the query and each of the candidate documents, each of the candidate chunks. It's taking one at a time and it's combining them to see how they interact with each other. And then it's deciding which one interacts the So with that complete um what is MCP? MCP is the way it

it's a protocol. It stands for model context protocol. It was uh it's open- source and it was proposed by anthropic I think um pretty recently. I think last year or late 2024 and it's basically there there's a lot of skeptics um skepticism around it and I kind of get it but also I'm I'm not going to opin opine much on it but it's kind of like your

remote procedure calls over HTTPS and what happens is you you can have a layered architecture with MCP so you for example in my architecture all the major functions that are driving the quality that are driving the retrieval driving the monitoring driving the decision whether to commit or not all of that is implemented in Postgress itself but I do have an MCP layer which is fast MCP which

is a Python module and what it's doing here is not much it's just acting as a wrapper that is a design choice I made because I wanted to have each of the functionality that will modify the data will decide what goes goes into the final output. I wanted to have it as close to the data as possible. So I kept it on in Postgress. And so basically

what MCP here is doing is it's creating wrapper functions and basically calling Postgress. And so that kind of layered architecture enables safety. It enables um the ability for the LLM facing components of your system to interact with the more uh critical systems like your data in a way that's safe. like I covered before like it you can you you can design your schema in a way that

prevents any kind of wrong access or gaming the validation gates and and in this particular system you can see uh and there are more but there's for instance there's the retrieval MCP function um there's there's a layer between the Postgress and GPT and that that's a little bit of Python doing the workflow the coordination of things and what it's doing is it's calling these functions and and

Postgress is running them and then it's determining whether or not the function whether or not Postgress approves right and so the first function you see is a search function happening the second one is orchestration related uh which is it picks the next intent and intent in these slides is what kind of slide do I want Next. So that that's basically my definition. And then we have the

validation and quality checks. Uh two of those are validate slide structure and check grounding. Um and we we're going to look more into exactly how that's implemented. And finally, you have a commit slide uh which is kind of badly named. So the commit slide doesn't actually commit. Only Postgress calls the function that actually commits. The commit slide lets Python know that yes okay um Postgress has passed

this so you can move on to the next slide. Um so you you can see that these these are type tools. There is no raw SQL. There is your LLM facing components or LLM itself does not have DB credentials. All it has access to is an MCP client that calls the MCP server which in this case is the Postgress MCP server. Right? It's it it's it's a

wrapper on top of observability uh and quality. Um so how does validation really happen in this system and why is it scalable? All of these quality gates from G1 to G5 they they're all implemented in a pipeline. So at any stage if any one of these quality checks fail, Postgress immediately rejects it because we have acid transactions. We have acid guarantees. It's atomic. Either a slide passes

and gets committed or it fails. There is no halfbaked here. So all of that that unpredictability of LLMs goes away. You either don't have a slide or if you do have a slide, it's the slide that you want. So first of all, we we've got retrieval quality. It's it's something like if you don't have enough data, if you don't have enough corpus for the question that you're

asking, that's going to fire up and that has its own threshold set. So if there are let's say less than 100 documents being retrieved for a particular query, that's going to be a problem that fails, it goes back to the GPT, right? And that could be your own problem like you could be um you you probably need more documents, you probably need more information to input there.

So that's that's up to you. The second one is citation integrity, but I'm going to cover G2 and G2.5 together because that's another advantage of Postgress. G2 is a cheaper gate to check. All it does is it has a SQL exists function to see if every content that the LLM returned as part of its slide, whether it has a citation, whether it's grounded in the system, right?

And so to do that, it just uses a SQL exists. But LLMs are smarter than that, right? And what they do is it's possible for an LLM to say, okay, yeah, I'm using this citation, but it doesn't really use it. So to do that expensive query, you don't want to do that right off the bat, right? Because the G2.5 is actually doing semantic analysis. It's doing the

same like vector similarity function and that's expensive. With Postgress, I can do the cheaper query first. If that fails, I don't have to do the more expensive one. So that fails, it goes back to the LLM for reddrafting. If it passes, then I do the expensive check to make sure it's actually grounded. If that passes, then I look at the format. Like if if I want a

slide with three bullets and and four images and it has three images, that fails immediately. It goes back to the GPT. I also have like um maximum retries and thresholds. So a slide in this case m maximum times that it would retry is a total of 15 times per slide. And finally the the second last the the the last step before commit is a novelty check which

is basically ensuring that I don't want any repeated slides or repeated with images especially that there are image embedding models there. There are vision models. Um there are there's a clip model. Um couple of those actually. But I'm only using the text model for the images. Why? There's a couple of reasons. Number one, it keeps the architecture simple. And in my case, that's what I need. I

need the the text textual description of the diagrams that I have because my images aren't artistic. They're not high resolution. I don't want to compare pixels. I want to I want to know what it is trying to say, what it is describing and that is best described by by a metadata. So for each image that you're seeing on the screen, I have a corresponding JSON file with

the all text with the context, right? And that is getting embedded and that is related and and with Postgress I I don't have to have images elsewhere. So the images reside right next to it and that gets linked and that gets returned for a slide whichever image is chosen to be best. And we're going to look at the past few generations and we're going to see how

it differs a little bit every time. Um and the final one is not exactly a gate. It's it's the final decision whether you commit it or not. All of this pipeline happening in Postgress. there is no additional uh component that you need to sync or coordinate um or take care of. And finally, a a side effect a very good side effect of doing all of this in

Postgress of building up the system from the ground up the correct way is that observability becomes a side effect. And that that is a huge thing at least in my experience because the first few months that I was struggling to make make these systems work I was missing out on observability and then I tried to make up for it by adding observability components on top of my

system like Langfuse and telemetry related components and they're great but when my base didn't have any to start with there were still gaps that that those other based system couldn't meet. They couldn't make up for it. And so when I when I did it this way, what happened was better observability, better evaluation. I could tell which week or which day or which generation was better than the

last or which one was worse. All of that became absolutely intuitive. And that's because of the power of With that said, here's here's what was built. I think I covered it in the architecture slide but this is the final result of this the system that generated these slides and the system that can power your your enterprise rags as well. You have Postgress at the center acting as

the control plane with MCP functions with quality gates, invariance defined, checks, constraints, foreign keys, good schema design, all of that stuff. Making sure that the system that finally gets to govern and decide whether or not the LLM is allowed to send its output uh to the final stage, whether or not that Then we have a fast API server that's basically getting the server side the listen notify

events and rendering all of the content as well as the telemetry that you're seeing on the top left on this reveal.js um renderer. Then we have a LAN graph orchestrator which is basically Python um coordinating the workflow between the LLM and Postgress. And finally, we have a little CFKA component which I didn't really need to add. Uh there's an extension PGMQ that can do that for you

if your if your system is Postgresscentric. The only benefit of having Kafka is if your system isn't only Postgress. Um then maybe adding other components becomes easier a little bit with Kafka. Um what Kafka is doing in this system is not much. Um it is ensuring continual ingestion. So if at any moment I change any of the documents, it immediately replaces the ingestion. So I can ensure

that the data in the database, the vector embeddings are up to date with whatever anybody's changed or whatever new documentation has been added. With that said, I want to switch. so the I wanted to compare um first of all, what is the difference between an LLM that between a a set of slides that are generated directly using the LLM without any system around it, without any Postgress,

without any checks, just using prompts and and LLMs are getting pretty good at it. They're evolving pretty fast, especially for this kind of demo where you have the output is pretty simple. It's slides. It's not a big deal, right? It's not missionritical. Also, it's it's a topic that's not proprietary. Postgress on rag, you can bet there's a lot of training data for LMS on it, right? So,

I I I generated one and um in you can look at the code on GitHub. uh the prompts that I use. I tried to be fair, but maybe there's some bias. Um and so let me copy the link that it just generated and open it in the browser to look at the difference. And you won't be shocked. For fairness purposes, I kept everything else the same. just

the content is a little bit different. So, going by it quickly, first of all, you can see the format is a bit off. Uh the text is a bit crowded. Um when you actually I'm going to upload this to the um to the repository as an as a as a demo example. Um so, you can read each of the text and you can see that there's a

lot of repetition and a lot of generalization and it can't help it because the LLM is not constrained. So you tell it to generate something about rag, it's going to give you the general answer. So this isn't specific to my system. Um it's also a little bit harder to read. And the big idea slide that that's the slide where I I said that I'm going to use

the deterministic system to control the stochastic system. And I prompted it with like to create such a big slide um big idea slide and it I guess it just didn't get it. So it's like postgress is yo yeah control plane one database to govern prompts tools retrieval security um but it doesn't say anything about the the principle of the architecture that is you're constraining a stochastic system

with a deterministic one um again u badly formatted stuff um I don't know why it does this but it should render the code block but for some reason when LLM is directly sending the code block you can see the formatting here is correct it doesn't render it. And so, and also if you look at the actual code, the raw SQL, it's all hallucinated. Um, but I can't

blame the LLM for that. What do you expect? Like, if you're just setting sending naked calls to it, it's going to hallucinate. But I wasn't satisfied with with just looking at and visually comparing these slides. So, I wanted a a better metric, a better evaluation technique. So, I used LLM as a judge. Um, so here, let me increase. All right, that's big enough. Um, this is the

last run. So, I'm going to run another one. Uh, this is a comparison summary of the last two latest decks that that were generated. One using my system, which is referred in the in the results as baseline, and the other one uh is the raw LLM output. the raw LLM slide deck. Um, so here it says all the gates are validated for the raw LLM none. Um,

it's calling a model from hugging face. It's complaining that I don't have an API key, which is I mean I'm not using it that often. Um, but here's the result of the of just the last two latest slides, one raw and one the baseline. That is the one that my system generated. Um and you can see that the baseline wins either moderately, slightly or significantly um in

technical depth. Okay. Um so yeah and this is just one run. What about I I have created a lot of these slides while while preparing for the talk and I thought that it would be interesting to record all of those results in Postgress to look at the combined comparison of of generally what is it like um and let's let's look at that. So this is this is

the general sentiment across 25 runs based on 21 unique deck pairs that is 21 of the different slides that my system generated and the different slides that the raw LLM generated and you can see even on a topic like Postgress plus rack where there's plenty of training data for the LLM um even with something as simple as slides you can see the aggregated um result has baseline

winning in all of the categories. A in some it's unanimous, in some it's strong confidence um and slightly at repetition and accuracy. Maybe I can improve that next time. Um but but what this shows you is that the system that you can architect the simpler more reliable system that you can architect even when you don't have specialized data that that is context specific even when you don't

have a very complicated critical output even then your rack system is better your postgressbased rack your postgress as a control plane system is doing overall better than Royal LM despite me using I think I've used GPT 5.3 for the Royal LM calls and that that's the most evolved model maybe second to 4.6 six of us. But um it's like I hope I hope that gives some insight.

I'm going to go back to the last slide. Let's see. Okay. And so the key takeaway is that using Postgress as a control plane, you can collapse a lot of the AI infrastructure stack into one single system. And by one single system I don't literally mean one cluster of Postgress like that there are a lot of Postgress talks happening here about how to scale Postgress well. So

one scaled system but Postgress you can you can combine at least six different services and have Postgress do them. Now of course like depending on your extreme use case if you need a like over a billion plus corpus then you might see a subsecond delay in the postgress system versus a proprietary vendor locked in pine cone but even so you'll have to consider that is that one

vector-based latency more important to you than the latency of the whole system because when you have more things that postgress does you have fewer latency and coordination problems. So overall, you might just realize that you're saving on time even though in that very specific vector specialized use case, Postgress is probably some milliseconds slower and and that's that that just depends on your particular use case and and

what your needs are. And with that, I am going to point to the the all of this system is now open source. It's called PG rack slide generator. It's on GitHub. Uh please go through it, play around with it, find problems with it, and email me uh or or reach out to me on LinkedIn. Uh I am very interested in in knowing what you think about this

architecture. Um if you've had similar experiences working on AI systems as well. And thank you And the repository is here. Um that is that is one of the candidate diagrams. Oh and um do I have time remaining? Oh actually I I do I have 15 minutes. So I I wanted to show the past few slides. I'm going to keep my mic down. Um I hope I'm still

audible. So going back to the slides. Um this this was the slide that I just generated I think. Yeah. the previous slide is a little bit different with a different image, right? And a little bit of different text. But it's the system so strictly constraints the LLM that even though the LLM is free to draft its text, the final output isn't all that different. The text is

a little bit different, but what it's trying to is not all that different. So the these are the five generations. All all the images are are in context. Different image every run but different different but within the context. And you can do this for for as many slides as you want. And you can uh run the system yourself and do this yourself. But if you compare the

two, you'll see a little bit of difference, but not so much that I'll be stumbled or I won't have because I know what what I want to say and I know that the slide however different it might be is going to convey what I told it I want to convey and that's how you achieve kind of reliable any questions comments Sorry. >> Yes. Uh so I I

so for my demo I wanted to show that you can use images you can embed them using the same text model. And so if you have multiple images that correspond to say system architecture. So I think I have like four or five system architecture images. I wanted to introduce as much variability to the LLM. I wanted to give it freedom, but just the right amount of freedom

that whichever of the four images it's choosing in each run is relevant. So that that that was the Any other questions, comments? Yes. That's a great question. So, I'm storing the chunks using PG Vector, but I'm also storing the document right beside it. and and that actually unlocks a lot of more capabilities not just for your routine uh daily like quering activities but there's a problem with

rag right now if you want to move to a newer more improved model of rag em embeddings you have to re-mbed the whole corpus because if you embed different documents with different models they cannot be compared the vectors generated by by the neural network that is of different model is vastly different like it doesn't correlate at all. So if you have to rembed using a modern model,

if you were on a specialized vector database, you you probably I'm sure they they've got like good features now, but I doubt that that it can provide you the capability that Postgress does because when I'm storing the documents right next to the chunk and I'm storing all the metadata and the quality gates and the logs and the provenence and the and the state management, what I'm doing

is I'm enabling the versioning of embeddings. I'm storing which model has version has chunked a particular chunk a particular document that enables me to use AB testing that enables me to use canary deployments I don't have to suddenly switch I don't have to take that risk and I feel like with Postgris that kind of capability is enriched because of the amount and the and the different variety

of data that you can store all together. Sure. Great question. So I think it shows in the telemetry. I have about 75 different documents and about uh think over 150 chunks or something like that. I think if you wait long enough it's going to show you. Um so yeah the corpus size for this demo document isn't that large but this very system can power a lot larger

systems. The reason I used demo uh slides is because it makes you all the evaluators in real time with you all being able to see actual dynamic data coming straight from Postgress in the generation panel. um with you all seeing um the quality, the bullets, the images, I thought that it will probably get the point across better. But yeah, you I think if you look online, you

will see that up till about 10 million chunks, there is no visible difference between PG vector and your specialized vector off the record because I haven't benchmarked it but from what I know there are other extensions that you can add to Postgress and you can scale Postgress uh you can add native partitioning you can add Situs which is a great like horizontal sharding mechanism with all of

that you can use half precision scales in PG vector so that basically takes half the amount of storage for for embedding the same vector and if you look at the analysis that's been done you reduce the size by half by losing just 1% accuracy. That that that's a pretty good trade-off. So if you want to scale the system further, theoretically I would say that about half a

billion should not be a problem. Um but then again, I haven't benchmarked it. So that that's off the record. Yes, >> I am so glad you asked that because yes, the system is very pluggable. It's very modular. So, let now I probably shouldn't say this, but lexical analysis is I've heard I haven't tried is probably a a specific type. It's called BM25 is probably better in open

search. So it's possible that you might want to use open search for the lexical arm instead of postgress but you can bring that back to Postgress do the RRF the fusion ranking do a couple of more stuff so another way of like hybrid rags is using graph structures normally you would think that you'll have to install Neo4j or some other graph database postgress has an extension for

that it's called Apache age it's pretty neat right and you can just use that to do the graph part of the graph rag as within it. Um there's another optimal function optimization function called maximum marginal relevance. It's it's used basically when you find that the top ranked candidate chunks are very similar to each other and you don't want it all that similar, right? Because it's almost duplicates.

So you can use that along with RRF within Postgress as well. So you the the hybrid rag pipeline can scale and you can add more and more components and you you I have CFKA in here. So yeah, CFKA could be the event stream for that other component as well. The same Yeah, it it could ingest. Yeah. >> you can absolutely do that in Postgress and um so

basically I think if I'm understanding it correctly you're saying can you route it depending on what you want or what the client is can you can you select the appropriate path that it should use and yes you can absolutely do that. You can also isolate it with role level security policies. You can yeah it yeah it's it's very extensible that way. Um, so the question is, and

I forgot to repeat the other questions, but um, the question is, can you go into more depth about what the trade-offs and the advantages of using this type of implementation is for for rag? The first one is the the three separate uh parts of that query are usually three separate services in a normal rag system in in a normal AI system. So you have uh a specialized

vector database doing your semantic analysis. you have a lexical database like um any anything could be posgress could be something like open search doing your other arm and you have finally RRF that could be implemented in Python just as easily as it could be in Postgress in in SQL so instead of using three different services and having to deal with the complexity of that you're doing it

all in Postgress with minimal um so that that's the biggest advantage um the second one is the ability to route it in in a simpler way and and part of that is is what RRF is doing. It's it's completely bypassing the normalization problem. So every ranking semantic ranking vector similarity is bounded between zero and one. Every rank you get is between zero and one. But when you

do lexical similarity with TS vector, it's not bounded like that. So how are you going to compare the two different types of ranks? And so whe when you have RRF within Postgress, you can actually just not have to deal with the normalization step at all. You can just directly get that, combine it, fuse it, and yeah, it just it just gets the if you want to scale

it to 500 million you might have to use something called half precision vectors for vector similarity in PG vector. That sacrifices 1% of accuracy. To me, that's well worth the tradeoff. Now another aspect to in the interest of being completely honest there might be after uh like 10 or 20 or 30 million chunks there might be a submillisecond delay in postgris versus a specialized vector database but

the point I'm trying to make is is that shaving off that submillisecond worth it if in that kind of architecture you all three systems happen in three different services all three searches and then they're combined Ed and so what is going to be the combined latency of that and it's it's going to be more than the sub submillisecond latency that you're getting for vector search in postgris

alone but again it depends on your system if you are designing systems to that edge to that threshold then maybe it's the right choice for you my my entire point is starting simple and building things the right way it it might save you a lot of effort and and then you can pick and choose Like in your case, you you could choose to include open search. In

your case, you could choose to like use vector DBs. You don't have to include open search, vector DB, uh a separate evaluation framework, a separate telemetry framework. You don't have to do all of that. You can pick and choose because your base system does a pretty decent job at at doing all of those things all right, I think it's time. Thank you so much, Maybe in here.

>> You choose. What do you want? Gold is gold is gold. place. It's nothing at all. All right, welcome. Go get your friends. We wait five minutes and go get some other folks. It'd be great. We should have made this. >> But first, >> but first, >> you are >> fire. >> My one >> desire. It's the end of two days apparently. Um, >> well, we're all

on like totally different timets here. So, you guys, it's it's already midnight and you should be having a drink. I'm ready for my bedtime. Um, all right. So, welcome. Uh, my name is Ryan Booze. I'm just going to be the moderator. This is your esteemed panel. I'm going to have them introduce themselves, tell you uh where they work, um what they how long they've been using Postgress,

been involved in the project that is um and if they want to share anything else like a song, they can do that. We'll start with Mr. Treat because he has the mic. That's a pretty good reason, I suppose. >> Can you hear me now? >> It's treat. It's a treat. It starts with T and that rhymes with P and that stands for pool >> right here in

River City. >> Sorry. Uh I'm Robert Treat. I work at uh Uh I'm on the Postgress contributor what else am I supposed to say? >> How long have you been using Postgress or been involved with the project? I have been using Postgress since the last century and I have been involved with Postgress on and off I guess since uh shortly after that so I don't know like

a long time long enough that's pretty good before there were RPMs that may not be true speaking of >> hi Dundus I started using Postgris in 1998 and Then two years later joined the community. Um I built the RPMs for the Reddit and Zuz and Fedora also uh break the website. If you something is broken in the Postgress website that's me. Um if it's not broken that's

still me. Yeah I used to do that a lot. Yeah. Um I also try to speak at the conferences actually more just go to conferences see people see my friends and also see her my friend. Hello Um, I'm Elizabeth Christensen. I work at Snowflake as a developer advocate on the opensource Postgress team. I am unlike everybody I've ever met, married into the Postgress community. I'm married a

Postgress developer who is no longer that involved in the Postgress community and I am. He's at home with our kids. Um, and I'm here. Um, I've been doing serious posgress community stuff for about the last five years. >> Hey there, my name is Pablo. I started at the beginning of the millennium, so 2000. And interestingly I spent like eight years maybe working with posgress but I never

met people from the community that was my biggest mistake. So don't do it like get to know people around this software around the uh around the posgress. So yeah and after I met all of them I was so excited that now I I'm doing posgress every day during my work and then for my free time and so on. Also I'm responsible for Google summary of code for

posgress organizations. So if you have question about mentoring or this related topics I would be glad to >> Excellent work everybody. Um so this is an AMA. I have a handful of questions that I have pre-written that I would like to not have to ask at all. Uh so and I don't think we might have one plant in the audience. Hopefully we don't need either. So, if

you have this is a time to ask them. You came to an AMA. So, we got a >> You don't get to go first. >> You're You're down on the prices, right? >> It always takes one person. >> I have a lot of questions, but I can I'll I'll start here, I guess. Um, I have accidentally become a DBA at my company. So, >> been there, done

that. you know, that's that's always a good time. Um, but when I joined, we're currently on Postgress 15 and I would like to at least get to 17. Is there any tips for how to do this specifically around like testing and making sure that like production doesn't break or things to look out for when you're like trying to hop from like 15 to 16 and then 16

to 17? >> You first have to tell us where you're hosted and then we'll decide. I'm host I'm on AWS >> uh well I'm I'm also a managed service if that makes a difference. >> Are you on Azure? >> Uh we have time scale. >> Oh yeah >> we're on manage time scale on AWS. Yeah. >> And we hand the microphone to Ryan. Make him answer this.

>> So so >> yeah we're phoning a friend right now. >> Hey Ryan, I got this question about AWS. They're hosting time scale. I think the answer is to use blue green. Is that the right answer? What do you think, man? I don't know what to say. >> I don't think they can do blue green on hosted time scale, can >> you? Yeah, I was afraid of

that. >> you can do the forks now. That is true. Which is really just another database. But anyway, um >> we just We're doing phone or stranger. Give him the mic. >> Yeah. So, I think you're talking >> We phoned the friend. That failed. Phone the stranger. the the live migration toolkit is created by time scale. It's on the time scale open source page and it it's

packaged in a docker container. So what you do is either stand up a VM and they warn you the VM has to be as big as your actual instance. >> So it's an expensive VM for as long as you're running it >> um and it runs one container or you could just put it into a container running service I'm sure on AWS. >> can you replicate out

of the container to a regular instance? the you the the live migration toolkit is what runs in the container and yes it will take you can you can go from their cloud to just a standard RDS instance or you could go from an RDS instance to their cloud yeah >> or you can go from like EC2 to EC2 it will it will just take a source and

a destination >> yeah it's a it's an old go package they redid with some stuff you're absolutely right that's one way to do it I would say maybe the the better question here we could spend the next hour talking about how to migrate 11 terabytes of data anywhere um and So maybe >> there is they will do in place version migrations but I appreciate that. Yeah. Um

I think I don't know maybe one of the answers would be to start with and how do people find out you know we do have in documentation like as we talk with each version what's changed what to look out for what's breaking those kinds of things right? Yeah, I mean the that sort of high level process, right? You like you read each version, the major version docs

uh and then look at like what are the things that have changed and if there's any like special instructions um I'll also give a shout out to Depes who runs uh the magic blog of all Postgress things but he has a tool on there called like why upgrade uh and it basically will show you it's not like diffs but it's like here's a list of all the

things that have changed from one to the other. Uh, I just had him add a feature request on the GitHub the no what's that thing GitLab page for that tool which is I'd like to see major version only comparisons and not the minors in there uh he added that because I want it and he hasn't done it. So if everyone goes and votes up on that thing

and says like yes we'd all like to see that then it might inspire him to add that. So um but that would definitely be useful just for seeing that. In your case though, I would also say time scale is a particularly like gnarly extension to live with, which you've probably started to realize. >> Yeah. So, so you probably also need to have some regular communication with the

time scale people about like I'm about to do this thing like you know what do you guys say? >> Do you have staging applic I would prioritize getting a functional staging application with your application development team before you move if if you have 11 terabytes like you obviously have a production database and this is honestly more of an application problem and less of a database problem in

terms of testing Now, Postgress is pretty solid in terms of version migrations like they're all um there shouldn't be breaking changes from 15 to 18, right? Like there are >> What are the breaking changes, Deb? >> Yeah, I'm a king of breakage. So, uh so >> Yeah. So, u you said about testing. So, do you have test files for your database or application? So what we have

in community so this is my tool I released just like two weeks ago. This is PG cov which means coverage. So you can see what exactly of what exactly of your queries are executed. So it's it's really simple. So you put SQL like schema functions and whatnot. Then you put like the file with a test postix meaning so how you wish to to run this and then

you run it and you see like what exactly was run what what's not and and you can decide maybe you should like remove the old functions like you know I saw that in some schemas we had like function name version two and then 48 more versions of the same functions and no one knows when under which conditions any of those is executed. So yeah, >> what was

that tool? PGV coverage PG posgress coverage PGOV >> COV Yeah. Um, I was just going to also mention, and this is probably stuff you've thought of, but um, I would >> take a peek at your backups before you do this and make sure that they're solid. And then I would also make a like a a roll back plan, right? I mean, it's it's possible the first one

won't work. Actually, for me, in everything I do, the first one never works. So actually let me give an answer from a different point of view. Um as a packager first of all if we exclude AWS and the time scale from this question so I mean I want to upgrade the extension to a new new major version with another extension. So some of the problems that we

have is uh let's say the company or the extension order may not have released the extension version uh for the postquest major version that you would like to move. So make sure that you have the version first then uh for example for time scale DB we may decide to drop support for postgress 14 in the recent releases. So you may end up with switching to like inter

like inter immediate version first say six I'm not again this is not about time scale DB and well this is about time scale DB anyway but uh because they dropped support for postquest 14 uh so you may end up with upgrading the postquest 16 first with the supported time scale version and then upgrade to new major version with a new time scale DB version so like post

has some similar features as well. So make sure that you upgrade you also read the extension documentation and this supported platform supported versions and then plan the whole upgrade process because it's not always smooth just push the button upgrade it will be great but it not it's not the case. >> I just have one more thing to say, which is that I also was a DBA but

did not want to be and and I'm proud of you for doing it and for asking hard questions and it's not easy and like a lot of people end up doing stuff they didn't plan on doing. >> I think you're doing great. >> That's awesome. Who's next? Hey, we have one over here. Hi. So, I'm just going to give a quick introduction and then ask my question.

So, hi, I'm Johan. I'm currently a high schooler. And um you know, my question is um especially >> high schooler. >> Yeah, high schooler. >> Dude. >> Yeah. Yeah. Good job here. Mhm. Um, so as someone who's not really used a database before or like is getting started with learning databases, I guess what my question is like what's the best way to learn Postgress for someone who's

not really used a database before and is it possible? >> May I answer this question because this is how I started using Postgress in 1998 and I still don't know databases. Uh anyway, so I I was trying to learn uh seriously speaking, I was trying to learn PHP and a database and eventually I end up I I just made up a project for me uh from to

that's it's totally from scratch made up a photo gallery something. So then how this how I learned using Postgress and PHP and and Linux and other stuff in 1998. So um you may end up with just making up a just project which is something that you may think of any language that you want any database you want eventually when you eventually when you learn the database basic

you will see that postcris is the best. So start with start with one of them and upgrade to postgress. >> Um I have two ideas for you. Um, one, um, I used to work at Crunchy Data, which was acquired by Snowflake, but Crunchy Data's website has a lot of Postgress tutorials, the vast majority of which I wrote. Um, and there's a whole bunch of stuff in there

that are developer kind of tutorials, and they run Postgress like in a web browser. Um, so you don't have to like actually run Postgress to get the tutorials to run. Um, because setting up Postgress is kind of challenging. Um there's a tutorial in there that says just for kids even though you're an adult by being here. Um the the just for kids like explains like the very

basics of like a relational database like in kind of plain terms. Um so that would be a place to start. Um the other thing is like I assume someone your age is probably vibe coding something right like you've got what are you using cursor claude so you know you're friends okay well I don't know go I mean get your your dad's email address and sign up for

the free trial of um cursor or claude um because like you can just like ask a large language model to teach you stuff now. Like I've done it recently and it's like and if it is too fast or not what you want, you can just tell it like this is too fast. I don't understand this part. Like I don't know. >> Yes. So but I must add

about the like if you know the notebook LM from Google this is like a free version. So you could put only your desired sources that should be probably the manual for posgress and maybe if you can find or buy books you prefer. So limit it to at mo at at most 10 sources and start to interact with it. So it's really good. It can produce some diagrams.

It can produce like podcasts and you can interact the podcast and then interrupt it and ask questions. So it will change on the fly. So that's really funny. >> Oh, yes. >> Please do. LinkedIn has a very good courses that usually require a membership but if you sign up with your library local library which is free and you just if you live here in Pasadena for example

just use the Pasadena library you can get access to all those courses and there are courses about databases about posgress specifically and about other databases about programming languages and they're really really high quality and free. So, I highly recommend. >> My contact is having an issue right now. >> I can see I have one eye. This is great. Um, any other questions? >> And one more thing.

Um, yes, it used to be challenging to launch Postgress or any of those, but nowadays with Docker, as we saw yesterday in the tutorial, it's much easier than it used to be. So don't be afraid afraid to just install Docker and then run the one command from Elizabeth slides and get it >> That's true. I guess I didn't even >> My answer was going to be first

get a time machine and then go back to yesterday and then go to the like all day 101 Postgress like tutorial series that we had here at the conference. >> It's really simple. eventually there should be a recording for it. So >> any tool or extension to track or find out any index uh ever used or not >> that one >> any any tool or any extension

where I can find out this particular index was never used. So the this is going to sound way more flippant than I mean it to be but but now the answer is you go to chatgbt and you say write me a query to find all the unused indexes in my postgress database and I'm using whatever version you're using and then it will write that query for you

because that that query already exists on the internet and like a bunch of different GitHub repos and it's been in slides and all that like but that's Postgress has statistics internally about what is used and not used so it's just a matter of querying the system tables But rather than searching the web or trying to figure out which field means what in the system tables like just

go ask GPT now and and it it will probably write you the right query on the first try even with the most free of free models. Actually um there is one more thing I don't remember the exact URL but postcrist DBA there's a set of scripts collected by someone in the community. Yeah. And then Yeah. And then um you may actually if you load it to the

to your database and if you run it with PSQL you can get some useful reports about the actually not just unused indexes how to roll them how to drop them or how to roll back the drop process as well. So it gives you the whole set of SQL commands that you can run um based on the based on the um information that you So yeah the the

posgress DBA repo is by Nikolai Samalov. So there are a lot of different monitoring uh and analyzing queries. Uh another way is to go for PGA repo. There is a bunch of metrics they are defined as SQL files. So you just choose whatever you need copy paste run and and and use it. But yeah the first idea with Chad Gupt is this easiest. >> Thank you. Great.

I'm going to put Robert on the spot here. You were supporting a lot of early startups with databases. Did did you notice a sudden trend where more startup decide to start using Postgress instead of MySQL? I guess I did. Uh, so I'm trying to think back the uh I don't want to say their name but I like to get the time somebody might know the time frame.

So, uh, like you remember there's a company like Pod Show like and they did like podcasting. It was like right when podcasting got really big. Um and it was one of the last ones that that so just as some background one of the jobs uh or one of the places I worked uh was a company called Omniti and we did scalability consulting and so that's I presume

that's where you're like yeah so we worked with a lot of startups at that time um as part of what we were doing because they all assumed they were going to be the next Facebook so they need to know how to scale uh and so like I think this is probably around like 2010 2011 somewhere in there um where uh they would come to us and they're

like, "Well, we've done all the the right things with the database. So, we've gotten rid of all the foreign keys, right? And we've eliminated like all the joins that we could possibly eliminate and you know, like all these sort of basic things that most people who are like database people I I think would be like, "Oh my god, like why that sounds horrible." Like why would you

get rid of all that stuff, right? Like we've eliminated as many different data types as possible. Like everything's either a text or a number. like it's just you're like this sounds like the absolute worst advice but I I guess like it was the sort of popular advice that you would get. Um it probably actually was so now I'm thinking about the timeline. This is probably right around

the time that Sun bought MySQL and then Oracle bought Sun and then I think like that probably was the switch as I think about which years these involved and then after that it just sort of seemed like it was more Postgress stuff and and less my SQL stuff. So but that that might just be coincidence. So >> I was just going to say that I think one

thing that made Postgress super popular in this time frame is just it being connected to uh language frameworks that also became really popular like Python, Django, Ruby on Rails like you know Postgress was kind of the deacto thing that shipped with all of that stuff right and it they had the OMS and those tools got really popular. So it's kind of like Postgress got really popular but

also at the same time as the language frameworks got popular. >> Um I'm not going to give this some I'm not going to give the similar answer but uh in the user lists about 15 years ago um when there was a popular poker game was introduced available for free and that poker game used Postgress at the back end. So we had like millions of users trying to

download Postgress installer on Windows and trying to install the poker game. Actually that like the most biggest thing that I have ever seen in the Postgress history that our user base has grown a lot. Yeah. I just like so there's like we have this conversation a lot in Postgress land about like why is Postess popular now or when did it start getting popular and who used it

or whatever. like the poker tracker thing was definitely noticeable like for sure um not not with startups but like as a general user thing I think that's the other thing I was just gonna say with startups uh and shout out and pour one out for Heroku um because Heroku really was big in a lot of startups uh as like a platform to initially get started and they

were pretty adamant on Postgress support and providing it so uh I I think and and they're now officially dead so or unofficial officially dead. Maybe they're unofficially. >> Yeah. Mo most like half of that team now works at Snowflake. So >> they're unofficially dead. Um so uh but for startups specifically like that was and this is all right around that same time frame anyways. So yeah, >>

anyone else? >> I think also like I feel like the governance part of Postgress matters to people, right? Like Postgress can't be owned by anybody. Like it can't be controlled. Like the code can't be controlled. The core team can't be controlled. Like they set up a lot of stuff that 30 years ago seemed kind of weird, right? Like who cares where all the people work, right? But

it like the fact that it is like really an open-source community and no one can control it I think appeals to developers um and and people who are building businesses and things with Postgress that ship with Postgress in it because they know that it it's you know going to be reliable and that Oracle isn't going to buy it and just stop producing it or something that there's

like a future to And one more thing probably cloud providers I mean if you need to install run and configurate posgress it's not the easiest thing in the world especially for production but if you have like several cloud providers that provide you with the choice my SQL or posgress why should you choose the first one if you have already running instance and someone is responsible for it

right so okay let's go this way it's Yeah, I think the other thing I would say is like for again thinking of the the original question about startups in particular, I think that is a true thing now. That is not like why we saw the switch or or when it happened or whatever that you know because early on like RDS was kind of like the first sort

of mass scale thing that was out there and it was my SQL only. Um but I do think if you think about like NoSQL databases so like is not available in like every cloud thing, right? So if you look at the market now you would say surely Postgress right like if you're on Google or Azure or Amazon or whatever or yeah or Oracle right if you're an

OCI like they have a Postgress service so like you know you can get Postgress pretty much anywhere and that's not true of basically any other database right like I mean you can get Oracle in Azure and Amazon I I don't know if you can actually get it in Google cloud maybe yeah okay so yeah like you can get Oracle in these other things but um it's just

not as easy, right? And people like uh Snowflake has a Postgress, right? Like and I'm sure somebody uh not Alinity but whatever the thing that Alinity supports. >> Click House. Thank you. >> Yeah. Click House just to announce not so Postgress like yeah like all these companies right like yeah >> everybody gets Postgress. So like now that's definitely a thing uh that that is true. So, you

know, but that's like 10 years, 15 years or whatever down the line from from when I think that momentum changed. I I can add from my experience that maybe 17 years ago, I was looking to migrate out of the Microsoft SQL Server and all the Microsoft stack. And I was looking for an open source solution because I'm a big open source advocate. And I was asking everybody

that I know what are you using and everybody said my and the initial response was okay that sounds like if everybody's using my SQL it's probably the best solution and then my follow-up question was okay why you choose my SQL over Postgress which was the other open source database and the answer was always uh I don't know that's what I inherited and then I decided to start

looking at the different features and there was no comparison. My SQL had a lot of very opinionated settings. For example, the default collation, I think until recently was Swedish, you know, just because the creator was >> Yeah. Well, >> so but you know that so I think always the funny thing with that kind of stuff. Uh so I remember this being a big thing but actually this

was a thing that we got beat up for in the postgress community because for SQL server developers coming over they wanted case insensitive coalation right because it on Windows like when you run SQL server >> like you can search like full text search type stuff and regular expression I guess likes like queries and stuff like that but it's case insensitive by default so it just works magically

and then like like I've mostly seen this with SQL Server developers that come over and they're like wait I have to do what to get like this case and sensitive searching and you're just like well the thing you could do is you could install the Swedish coalitions and do all that stuff because that's what they do in my SQL. So even though like it sounds like a

really weird thing like it actually helped them out I think in the beginning for people that were coming from SQL server because like or coming from Windows so like sometimes it's a positive and sometimes it's a negative >> that was one example another example is that for example when mysql added UTF8 support then they only thought that UTF8 has three bytes. So uh if you try to

insert an emoji because you have let's say a blog or a forum where people post content and they post an emoji your application breaks because the most emojis require four bytes or and then instead of fixing it my SQL decide no we'll keep the UTF8 as three bytes and we'll introduce a new type called UTF8 MB4 if you Anyway, in Postgress it just works properly because these

decisions from my experience go through a discussion and many people with a lot of experience give their two cents and they look at what others do and how it's implemented in other places and what are standards and it's done right. So >> I think we just got lucky. I think that the PO posgress team created their own luck. >> Created their own luck. I like that. >>

It's it's not about time scale. So, um it's about multi-transaction ids, >> you know. Hey, so a lot of folks are from really diverse groups of of of organizations that are all parts of the Postgress ecosystem. And as you mentioned, nobody owns Postgress. There is no central body that says this is what's next for Postgress or this is the features that are coming out. How do you

guys see at this point? So Postgress is a sophisticated product with lots and lots of users and lots of demand from all over the place. How do we find out from the community what they think are the most important deficiencies or areas where they want to see a new feature or see something? And how do we how how do you all see what do you think is

the role of that community in pushing the developer ecosystem to help pick those important features or figure out kind of cohesively as opposed to this is what a person over here decided to contribute and here's what somebody else decided to contribute. How how do we reconcile those two things knowing that that maybe there are big things that need to happen architecturally? >> I feel I'm gonna say

something I probably shouldn't. So I feel like one of you should have first crack at this. >> I I mean I can go into what I'm going to say, but I don't think I should. But >> we all have something to say about this. >> We So >> So Deborah wants to go first. >> No, no, he wants to go first. Tik Tok. Let's go. >> So

yeah. Okay. First of all, I want to recommend the Bruce Mumjun's talk about the top 10 features that we are missing in Posgress, right? That's the name. So Bruce in in his talk describes the top 10 features that we are still missing or partially they are there and how we struggle with this. And I mean like TDE like transparent data encryption it's already like last for how

much 10 years something like that and we couldn't say if we will ever implement it in the core that's the like the the the complexity of of these features and so >> um so I was just going to say that like you know almost all of the committers, the actual people committing the code, right? I mean, there's like contributors who are kind of the, you know, contributing

patches, but a lot of those die, and then there's kind of major contributors that are a little more involved, and some of their patches are more successful, and then there's committers who actually push the code in. Most of the committers are working, you know, at companies that have, you know, needs, right? And so what I've seen you know just kind of anecdotally is that you know Amazon

RDS right they're running I they have to be running the most Postgress servers of anybody right yeah okay I mean I think I think we can agree like I mean they right they are running Postgress at a scale that is probably not being done anywhere else right and so they're running into edge cases they're running into stuff that is like needed by their own user base and

they employ how many committers do you guys have right now? Eight committers, you know. So, I think a lot of what's happening is especially the cloud has kind of democratized this where you know, you're able to get from okay, these are our customers. They need this fixed. We have the committers to do this. I mean I don't know that that works for everything but I I do

know that I think you know there are a lot of people in places that can make things happen that are part of these groups that run that actually are running production Postgress instances. So even though they're probably far apart from a hands-on experience where they're not talking to the customer or seeing a bug, you know, they're they're at least aware of the like operational issues. >> Okay.

Well, Deon, I'm gonna hear what you have to say, but I actually want to ask a follow-up question to see if I understand. Go ahead. >> Um, all the companies have agendas actually they have something to add to Postgress but it doesn't mean that it will be added to Postgress because eventually it's going to be accepted by the community right and a company may may have a

plan like hidden hidden item in their plan to insert something to poss inert at the future to postgress but eventually it doesn't mean that it will be in Postgress. Another thing is which I like about Postgress even though a feature may not be added to core posgress there is a great feature called right so and our extension ecosystem is great and also suffering there are some lots

of goods and bad things about the um ext extension ecosystem but a company or a person or a few people can at this at that future as an extension if the hooks are >> You know, one extension that we could talk about is time skip. Okay. Um, so my question is going to be this because we actually had this conversation the other night at dinner. The source

of your question makes it seem like you think it's broken and I guess I'm cur and maybe that's not the right word, but so my question is what is it that you feel is broken about the current process? That's a sincere question because I think it's something we talk about often. You know, I definitely would call I I don't think the system is is broken. I >>

broken right word. I'm not trying to put anything in your mouth. So, please don't >> more so what I'm curious about is knowing that there's more and more people coming to the ecosystem that it's it consistently ranks as one of the most popular developer choices. It's the most popular for developers database. So who right who who sets that or or how do we how do we set

that roadmap for not just one year out but like the five-year plan where is Postgress going and how do we how do we pull the community in to help say you know since this is a free and open source thing and it lives out there and there's no one company that says we think Postgress should be this how do you reconcile those two very different mentalities >>

uh Bruce do you want to answer this instead what I said. >> I feel like I've got >> I'd like to hear Bruce's answer and then I'll I'll tell you why it's not true. >> So, you can have a five-year plan if you control the resources that go into that plan, but we don't control any resources. So, therefore, we can't make a plan. The plan is whatever

people want to submit to us. Now, I can go to somebody and say, I think your talk was great. In fact, I did yesterday about um that the that Alexander did and I was like there is so much that we that I've been wanting this this is a huge area. I can't feel we can't get off the off the the gra the start line on this and

I just feel like can you talk to several people and I indicated who I thought would be good and let's see if we can get something done a meeting in Vancouver to get people really focused on the optimizer in some of the areas I think we're stuck in. So in in instead of having a five-year plan where we know what's going on and we can put the

resources in it, which we can't, I I feel that our community is more encouraging people when we see really fruitful work being done. I thought TD was something we would love and I really worked on that and it ended up that the cost of code versus the value of the feature didn't match up and I bogged about it. So, a lot of what we do is is

is not a fi a fivey year is great, but what we're really doing is we're incrementally always adjusting, always getting ideas. It's a lot. It's painful. It's a lot of work, but it is actually more effective because every company that I see that does a five-year plan, it it it ends up being a five-year plan and then they just stop working on that feature and they just

kind of go in some other direction and you end up with this sort of Frankenstein idea. A lot of reason that five-year plan, that roadmap is for customers and to keep their big customers paying the money. We don't get any money from anybody. So, we don't have a need for a five-year plan. We have a need just to be continually encouraging people and sort of looking to

see where we see the industry go. When AI came along and we people needed vectors, there was some guy who wrote PG Vector and people would ask for AI and we said, "Well, there's this PG vector extension. we don't know what it does but why don't you give it a try and we were already there we and when we did a JSON people put it together within

a >> I think my only other addition would be I think post pandemic and and I'm just as more of an observer of what's been going on but I think the last two years in particular with uh the changes in in the dev conference is there's been a lot more focus on exactly that how do you number one engage the community more but number two how do

you help the community better engage with what's going on so they can become an advocate for the things that are important like you know a year or two ago Bruce came to a conference uh that was primarily Microsoft SQL Server and it was interesting like to hear his perspective after a session was oh I just I just never I don't have that perspective because it's not my

group right like to hear their comments and their questions was interesting and so like how do we teach some of those folks to come and have a good dialogue about that I think that's I see that happening in the dev and and some people that are really owning that. Um I see Robert Hos trying to do more of that with with the dev community and stuff. >>

Yeah. So I'll just add a quick perspective. During the COVID shutdown, Oracle had a webinar called how to choose an open-source database. So I signed up for that and the first half was pretty good. It was evenhanded. My SQL versus Postgress. They did a good job. And then the second half they started to you know go in a specific direction and what they said was the problem

with the postcris community is that there's a lack of product management. No one is listening to the customers and prioritizing features. Anyways most customers are used to dealing with product companies and they get that type of attention. Postgress is not that. So that's where the disconnect seems to >> um I think I mean I have to maybe I shouldn't say this but we are not a company

and users are not customers. I don't know that I know that hurts but customers I mean there are lots of postress companies in here uh that have customers but from yeah I know I know I know from the community point of view they are not our customers they're our users. I don't I mean I'm I'm not saying this is the best thing but this is a >>

yeah the last thing that just came to mind because someone I guess you said it Devim is the one thing I really see with Postgress and I don't I'm not on the panel sorry I'm g I'm giving opinions um is that because of the extension ecosystem I feel like postgress has been able to move faster in a number of areas that no other database has been able

to because it take you know it takes an entire organization to figure out we're going trying to figure out how to add AI. We have three different extensions that have tried different ways to do or a fourth one to do, you know, AI text search and stuff. And that's kind of cool. It doesn't mean that they go anywhere fully, but it helps give feedback quickly. I don't

know. >> Yeah. I mean, I guess I would say I I actually don't think that model is necessarily wrong. And we but we just do it in a democratized way, right? So that like you may have this idea that like you would go to a product manager to talk to who's going to be your product thing. And the difference here is that it's almost like if you

think like the Linux kernel, right? And like well you don't go to the Linux hackers mailing list if you got like some issue like you go to canonical or red hat or whatever right you arch people gen two people whatever it is like everybody has an interface to somebody who is packaging and delivering that thing to you and I think in this case you know like at

Amazon we have multiple different you know postgresses that we give to people and work with people on and those things all do have product managers right like the the only team that doesn't is my team right because we work on the open source thing where we can't do that. Um, so I think you if you just think of like every user out there if they have a

client relationship and a product relationship, right, it's like whoever is packaging the thing that you're making use of. And so what we've done is we've turned this into like there are thousands of people who you could go get a product that is Postgress to fit whatever niche you have, right? It could even be time scale if that's the thing. So like we do it but again like

we democratize it across a wide group of people so that people can pick and choose whatever they want and that ultimately gives us flexibility and you know the ability to reach more people in a way that a single company that is having control over a product is never really going to be able to do. So, and I expect that actually to get much bigger like you know

in the next like three to five years with the way AI is going like the stuff like TTE where it's like if you want TTE there's at least three different vendors who have a Postgress with TTE that you can go get and I suspect there's going to be more even if we never get it in core there you know there's going to be people who will package

that up and give you an interface to a Postgress product that has TTE so I expect this thing only to get bigger and more widely dispersed Yeah, I was just gonna kind of echo that that I think we're like getting to the point where Postgress is kind of going to be overwhelmed by the e ecosystem around it, right? Like where Postgress is kind of the tiny engine,

right? But like what you really see is the sports car, right? Like it's it's Postgress is not the thing, right? It's kind of the bedrock and then we're we're building a lot of stuff around it. Um when you were talking about like the five and 10-year plan um I recently like in 2025 read the Michael Stone Breaker Postgress paper which is available online like it was a

project that you know they kind of developed and and and sort of set out and you know it I don't know that they thought it would last this long but it's pretty visionary in terms of like what the original goals of Postgress were right they were you you know, create a database that doesn't break. I mean, and there's some stuff in there and that has like a

really robust kind of crash recovery system, which, you know, we all take for granted now, but but wasn't that in the 80s. Um, but also like, you know, creating a a flexible system for data typing and extensions. Um, and I think the the words in the paper are to meet a growing variety of business needs that will need a database, right? And so I think, you know,

these folks that were working on, you know, computer science projects in the 70s and 80s saw what was going to happen, right? That in in this new century, we were going to have people with businesses and they were all going to need databases and they were going to need to make a database that would meet lots of different needs and they kind of did that. Um, so

you know, like some of the vision that we're kind of living through now is is because of that sort of original project vision. Any other questions? Burning question. I I could tell going to come over here to the right first. I'm come back my right, your left. uh in your view what like what additional resources if you could magically like wave a magic wand would help the

project most you know like I don't know like people funding or things of like that nature or maybe like pretty pretty broad question um a couple things so I do a little bit with the Django um community which is like the web framework for Python on um and they have funding for fellows um and so they have a couple fellows every year all they pay those people

to work on the Django core project right um and those things come in from the community kind of in a similar way um you know I I don't know that Postgress is going to get there in the next couple years um but I would I would love to see us have you know a rotating set of fellows so that they're you know the the right now all

of the people committing to Postgress are employed by you know all of our companies right like we our C Cybertech you know AWS EDB you know Microsoft and Google are basically all paying full-time employees to commit and work on Postgress and and that's nice of them but it would be great if we could fund that some directly Postgress also has no paid executive staff um and I

work on you know some of the stuff with the professional association the United States Postgress Association and we have one in Europe too. Um and you know I would love for us to have some kind of e executive staff to help you know run the association run events. Um you know these are things that Python Software Foundation, Django Software Foundation, you know Linux Foundation like they have

paid staff that are doing executive functionings running foundations and we don't have any of that. Um and I think we're going to need that in the next 10 years. Um, yeah. So, if you're interested in that topic, you are welcome to join PGOS. It's $25 a year. Um, and I think it's $10 if you're a student. But, um, yeah, I mean, we're we're definitely talking about stuff

like this, right? Like what is what is stuff like this going to look like in the future? >> Um, one more thing. Um, it's not just we we need more hackers or developers or something. We need more people for infrastructure as well. Um I mean I've been involved I mean being involved daily with the infra team they are doing a lot of work that so that we

can use Postgress download posgress or people even commit code to postgress or the funds group people are working to get funds around for postgress and doing some conference and etc. So contributing to Postgres is not just the code. It's just the contributing the whole ecosystem like Mark Wong organized this and Robert coorganized and Gariel organized this this conference. Uh not this conference but the Postgress booth and

the post Postgress thing. So just help us because we are out of people. We we need more people. I mean that's what I'm going to say. We need really more >> Yeah. I think the this is mostly an echo but also a bit of emphasis that um one of the things that we've tried to do and there's some of us like Mark and and Gabrielle and myself

I mean I know you have for for a very long time uh like we have the problem that all our marketing like companies you know I'm not going to say my employer but I'm sure the other ones like they don't really like to market a thing that's not their own company right like it's sort of weird even to ask them like hey can you do a bunch

of marketing and not put our logo on anything. Like it's just bizarre. But it does create a problem that like a lot of people that I've talked to over the years like they think that Postgress is like whatever the company is that they're buying it from says it is. And so like we've done a lot of this outreach. Uh and we try to do the outreach to

things like you know Pyon and Django and and these sort of other developer events. Um but all of that stuff is done like it's just so rag tag like on a shoestring budget. like you carry the stickers in your suitcase, right? And and try to like keep it as minimal as possible. And like that's if we can actually get the time from our employers because not every

employer is like super excited about like, hey, can I just go to a conference and like I'll work a Postgress booth and you know, give out Postgress stickers and I'm probably not even going to talk about you. Like that is a really difficult thing. And so volunteers, more volunteers for that type of thing certainly makes that easier. Like more money would make it easier. Um, we have

some sort of odd restrictions thanks to certain people in leadership where like we're restricted from spending money in certain ways because like we can't hire people because we can't get money because we can't get benefits if we're volunteers. Like it just it gets there's like weird complications and all that stuff. So, um, people like just and it doesn't have to be that you fly around the world,

right? Like people running local meetups who have a regional presence makes a lot of things easier. like when there's a pyon in that city, like it's great if there's like, oh, well, you know, so and so lives over there, so like they can probably recruit a few people to help out. So, that definitely is a resource that uh I I think like it's just people spending their

time, you know, doing sort of the volunteer effort to talk about community postgress uh as certainly a thing that we need that that I don't think any company is really going to fill that because it's just, you know, it's just weird for them to to market a thing that's not their stuff. So, I wanted to know the current status of um horizontal sharding and do you think

for a feature as big as that over the years products build up on top of Postgress and companies build up and do you think that that affects um motivation or you know just time willingness to to work on these projects? Are you talking about something like PG Edge or or just anything in that that sphere? Yeah. I mean, so the weird thing about that is, you know,

every few years like they get popular again and and there's some that I I really do like, but like even the ones that are out there, like the commercial ones like have a real hard time finding a big enough user base, you know, like I know you use React a little bit. I really liked React a lot and like, you know, but it's written in Erlang, so

nobody wanted to deal with it. And so like that company went away. Um so and even like if you asked everyone would tell you like oh yeah you should have multim masteraster in core postgress like why don't you have that already like clearly everybody wants it. I was like yeah well there's you know like physics involved and it like just gets hard and nobody really wants to

make the trade-offs you have to make you know at the end of the day people would rather just kind of go with the single tenant system with you know read replicas and whatnot. And so that's what we have. Will we ever get it? Like I mean we have the solutions that are out there now. Um I don't you know maybe something it it's probably going to be

up to database researchers to like crack some code that we just haven't cracked yet, right? Like maybe the AI will figure it out and then then we'll have a thing and we'll put that in core. But right now I think we rely on the extensibility aspect, right? You can build situs, you can have like a peachy edge. Like there's a couple other extensions that are out there

in the space. Uh and that's probably the way for the foreseeable future that like Postgress is going to try to address that. So or like I mean I I should I'd be remiss if I didn't say you should try DSQL because like right like companies like Amazon are just going to like invent a new version of Postgress that has this stuff in it and say like go

try to use that thing. So, you know, like those are floating out there. But as far as like how do you get it into core? Like I I I think there's like research that, you know, hasn't solved the problem in a way that gets rid of enough trade-offs that that it'll become a predominant solution hosted Postgress to because they want horizontal sharding and they move to stuff

like Cosmos DB. Um, do you think as a whole that's good for Postgress or is it Postgress doesn't care? >> I think it be a little bit harsh to say Postgress doesn't care. Um, but I think that at the end of the day like you can't please everybody all the time. So I think the other thing is also why do those companies actually make that move? So

that you know like one of the big ones that I think is painful in Postgress that people move to these systems for is like the like making upgrades you know easier or like with less downtime and all that stuff like everybody wants zero downtime upgrades like solving that is a lot of work that most people seem not interested in doing um at least as long as I've

been preaching about it you know in Postgress hacker land so um and there's other things we can do to make the upgrade process easier. So we you know there are people spending a lot of time trying to do that like ultimately will it get to zero downtime you know with just a token ring of Postgress things like I you know PG Edge exists I think you can

do it with that so you know and again there's other solutions that are kind of out there based on those types of things but um but that's the other problem is like even why you go to those systems might be different reasons right some people do it because they need like regional independence and some people do it for other reasons right and so then when again you

go back to the trade-off off problem that like even if we put a thing in core Postgrust like there's a pretty good chance that people are going to be like oh that that wasn't my use case for that system. So like I'm not actually going to use it and why did you put that version of it in? So >> one more note. So um is it having

like a a bunch of forks of pos or not? I think it's really good. I mean if you never ever supported like the fork of possess you have no idea how much work you need to do for every minor release. So the when you support something like like a fork your dream is to put everything into the core upstream. So like working on a fork gives you

more motivation to get back to the upstream. So that's that's a good thing I think. All right, we have time for one more question, >> maybe two >> time series dat time series data. You should anyway uh uh so hey I'll ask I'll ask the the question that has been asked. What are you guys most excited about for uh because we're about to enter uh Postgress 19

like commitfest uh you know last last go and then through the summer. So what do you is there anything you guys are most excited about or something that Ruth's come up >> I'm excited the most that Elizabeth became a reviewer and she reviewed my patch and now I I I'm pretty sure that it will be accepted like immediately. >> What was the patch? I didn't know this.

He's adding last um last last executed timestamp to PG status which I kind of couldn't believe wasn't already in there. >> Oh, I can't wait to talk to you about PG. >> So, three cheers. Yeah, three cheers to your patch. >> No, no, it's insane. >> Oh, really? Okay. >> Um I am super excited about something that's not necessarily in core Postgress, but an extension. Um there's

a host of extensions kind of happening all together right now that are connecting Postgress to the world of object storage and flat files and iceberg and parquet and CSV and JSON and S3 and um I think we are rapidly approaching a world in which you may not even need the Postgress file system and you will be using Postgress as like a front-end query well for some stuff.

Okay. For some things um but you know potentially using Postgress as kind of a a query engine and an engine to um work with um you know a whole bunch of different file formats. I mean we've been living in a world where you know most of the data you had to get in and out of Postgress um with a copy command or a batch load and and

that is no longer the world we live in. There are multiple extensions that connect Postgress to any file format. Um, which I think really opens up a lot of new use cases and new users um into Postgress and and does lots of other things. Um, anyway, >> for more on that, see Elizabeth's latest LinkedIn post. Devrim's not excited about anything. >> You want the final word? >>

I'm excited about the RPM packaging. That gets really swell. >> Hey, should we round of applause for the packaging? >> Come on. What are you excited about, Robert? Go. I yeah, I I don't I'm I don't think I have one big feature yet, so I'm kind of interested to see how we're going to frame this release. Um I there's a lot of good observability stuff that's like

continuing to be improved. Uh and some good performance optim optimization stuff that's coming out. So like it seems like a solid release. Uh it doesn't I haven't found the thing that I'm like, "Oh my god, everybody please immediately upgrade to this version yet." But like it might still be there. So we we definitely well the problem is like there's >> all >> okay group group by all.

Yeah. I don't know if that's that's it. So but we're I mean we're in the last commit fest now. So for those that that don't follow it that close. Um so there's a lot of stuff that may or may not be in there and we just don't know yet but we'll know pretty good idea within the next I don't know five weeks or something like that. So,

um, so that answer could definitely change if you were to just ask us, you know, like two months from now, we might be like, "Oh, yeah, we definitely know like this thing actually got in because there's a few patches that I'm skeptical that they're going to make it." Uh, and so those could be the answer. They're just not yet. So, I I don't have any answer because

this year we don't have we didn't have Postgress. what's what's new even postquest 19 uh talk by Magnus because that's that's my that was my source in last few last 10 years to learn about what's coming up so that's why I didn't give an answer but one thing off the record please off the record okay that I want to say right now I don't want that plan

advice thing to get into posress >> the what thing >> the PG plan advice thing to be committed to postgress I don't want it I don't want to be like the other databases >> oh wow okay Oh, the hint. >> Oh, the hint. >> Which part are we going to just to talk about that more? >> Actually, no, that reminds me. The thing I'm most excited about.

So, first off, PG plan is not hints. It's like some abomination of kurfuffleleness. But the thing I am most excited about in 19 is actually in the extension PG hint plan. There is a flag in there that you can pass a hint in to hide your indexes. So if you want to like drop an index and figure out whether it's going to be used or not and

like safely be able to go back, you can add a hint into your query to say like don't use the index and then it'll show you whatever the plan is going to be and use the plan that's not the index. And if it turns out that was a horrible idea, like you just flip the switch back and like your index is back and being used by the

planer. So that is actually the thing I'm most excited about. It's been in there for a while. I forgot that that was coming, but yeah, bringing that up. That's that's what it is. So everybody immediately go upgrade that extension and make it happen. Okay. Also, wait, I just I also want to say like so we did say there's a community AMA. So I just like I'm glad

that we had people in the community who are actually answering some of these questions, right? Like that is how the Postgress community is supposed to work. We hope that you will find however you want to participate, find a way to participate in the community. There are people out there happy to answer your questions. We get to sit up here and look like we know what we're doing.

I'm not sure we actually accomplished that, but but there are definitely other people out there in the community who know lots of stuff and are also eager to help. So, uh, thanks to all of you who who participated in the AMA and that's a wrap. Thank you everybody. >> Enjoy the rest of scale. >> Thank you Elizabeth. >> Because of because of the training yesterday. >> Tell

me why. Just tell me why.