hin the individual index how far along it is But it will tell you so in this case is here have five indexes three are done So it basically takes the face of vacuuming indexes and gives us some more detail as it's running To give you a little bit of a clue how much longer you're gonna have to wait Because usually when you're at the point of looking into this you are waiting, right? It was it was slow enough that you felt the need to look at it So that can be a nice Further input into that one Okay, leaving statistics, let's talk about copy. Who's ever seen an error somewhat like this it goes off But you know I copy I copied my little dummy table from a test file in CSV and it goes down There's a boom invalid input syntax for integer now Usually it's not online to Right usually you've loaded a few million lines and then it happens So copy in version 17 has gained the ability to direct and act on errors What you can say here is you can say we see here copy Tommy from format CSV on error ignore And as I copy it now it just as one row will skip due to date type incompatibility for example now in 17 The only on error operation supported is ignore But the idea in the future is to be able to do you know on error log or on error Write it to a different table that where every column might be text or like to allow you to Proceed through it in this version all we can do just ignore it, but you know depending on what your data is That's a good start But the the goal coming hopefully then in Postgres 18 is to be able to actually send the incompatible data somewhere Sorry Sorry, it will be Any error? Yes Yes, or any I mean any error that's based on on the source data if you run out of disk space It's your your screwed But like wrong number of columns or you know batting yeah Yeah, all our of that type it will be able to ignore. Yes, Jimmy If one line breaks the rest of file I think that that is in the end going to end up how it breaks it like if you get like a runaway quoted string or something, you know all bets are off You're then you're probably just gonna fall into a different error later. And yeah, so it'll depend on on exactly how But if you do if it's single line CSV one line tends to not break the next one except possibly for the quoting errors And and yeah, they're gonna go weird. Whatever we do. Yes No indication of which line no Because the idea would be then you'd have to log like every one of them And there might be millions of entries that would be in this upcoming ability to say for each line log Which we don't have at this point Okay, speaking of things that were in postgres 16 and then they were not in postgres 16 and now they're back a New permission that is called the maintenance permission You can grant this to a non So to allow somebody who's not the table owner to be able to do things like vacuum Analyze cluster reindex and refresh materialized view unlock table Why was this removed in postgres 16? it was removed because we did not have this feature to secure the search path for expressional indexes and In that case this could have been used to gain higher privileges in the database and to be exploited as a security issue therefore it was removed the Search path lock-in was properly put in and then it was put back in now that it can be done in a safe way And this one works similar like you can grant maintain on a table to a user Then they will get this permission on the individual table or you can grant this new role called PG Maintain to a user and then they will get the maintenance permission on every table So you can create a special user that can run all those lovely vacuums for you Who loves dealing with locales Okay, cool. Let's add another one. It's not actually another one But postgres 17 will come with a built-in locale provider So, you know previous versions of postgres for a few years at least have been have had the libc provider and the icu provider And now we have a built-in one Big so you get a little bit more to choose from this is only for the C locale for now So postgres will just bring its own version of handling the C locale It is faster than using icu. It is faster than using glibc The most important part is that it is stable or at least if it's broken then we broke it and then we can fix it within postgres It does not for those who don't know the C locale is the one that basically is not a locale, right? It doesn't sort things correct But it does sort things consistently and a lot of the time. That's enough, right? If you're just indexing and texturing and you're looking it up by equals lookups It doesn't matter which order it's in the index as long as it's consistent And that's exactly the problem with particular glibc locales is that they they're not necessarily consistent Who has been hit by the locale problems in the glibc upgrades? I feel your pain. I've done it too. Who has never heard of the locale issues around glibc upgrades? That's very few people who haven't heard about it. Who thinks they're not affected by these? Okay, it's almost a trick question if you think you're not affected by them Or and if you have a or if you've never heard of them, you should really go to Joe Conway's talk That's right after lunch here He will talk about the problems in detail that these hold the built-in locale provider is not a fix like a Perfect fix for this. It's a step on the way It's infrastructure, but it can be used already right now Okay, let's move on take a look at some of the more SQL exposed Things and more maybe a little bit more developer oriented the things that everyone's always been waiting for right? You can now actually convert things to binary and octal sweet, isn't it? Was it I think it was 16 that we added the ability to actually specify literals in binary and octal But there was no way to go the other way well now there is Very simple, but still useful If you are dealing with Timestamps and intervals we can now hand have infinite intervals This was a little bit weird because we could have infinite timestamps, but not infinite differences between two timestamps So if you were to compare two timestamps and one was infinite you would get an error Now if you do that like if you did this infinity minus now you now actually get infinity That's you know, it's kind of obvious and it's kind of annoying if it crashes when you do that. So Simple addition that just plugs a bunch of holes where previously you had to manually do it You know case when x is in equals infinity then return this else return that If you're using partition tables identity columns now work properly as we'll say it has to do like when you attach and detach tables You could get into some weird positions with identity columns That you don't anymore Postgres 17 will support what is officially known as temporal primary keys and unique constraints This is again one of those things that doesn't technically add any new features to postgres But this is part of the SQL standard And it's a way to deal with any of the SQL standards called temporal keys basically it allows you in this case to create a primary key which has to have an First a column that is not temporal and then you can say valid without overlaps where valid is a range type Now in the SQL standard they don't they talk about periods Which is basically a range type for time or timestamps and they don't support the other kind of range types In postgres this will support all of our range types, but you still have to have one column that is not a range type in it And under the hood this will create an exclusion constraints Postgres had exclusion constraints for ranges for a long time But we couldn't use them as an official primary key And we couldn't declare them as a unique constraint because they would be an exclusion constraint So this is sort of a subset of exclusion constraints that can be declared as this Which will be SQL standard compatible and therefore it'll work in like when you're populating IDEs and Data types and things in applications out of it. Yes. Oh, I didn't do this somebody else will do Yeah, there are we'll see we're slowly moving towards this so piece by piece Yes So yeah, so this this this part only gives us the key that the ability to use this as a primary and unique key The SQL standard also has the ability we can say, you know, select this as you ask of a certain time We don't have that syntax yet So today if you just query it by ID today, you will get all the rows back and look at it So you yeah in in reality, you need to also do the where clause on the validity time How many cookies are needed to fix this? Yeah Yeah See Yeah, it is progress is ongoing. There are patches out there for the actual Code to do as of time. They're not done yet, but you know 18 or 19. Hopefully they're they're actively being developed. Yes Time travel was in postgres and we took it out The problem with that was the time traveling postgres was not optional and It was ridiculously slow But yes, some of the things that you are doing in in these temporal parts of SQL really They're solving the same problem that that postgres and elastra did back in those days. Yes Okay, moving on There is now a new function in live pq called pq change password I bet most of you aren't really using live pq But again, this becomes infrastructure that can be used by most other drivers that are sitting on top of it This functionality used to be in psql in psql in the client It's now moved into the driver which will then hopefully make it available to the other ones What it will do is it will make you stop putting alter user set password or create user password and put the password That will then drop out in log files and things like that This is the equivalent of the backslash password function in psql Which will you know encrypt the passwords properly and so that what goes in your log files is not a clear text password So again, we'll we'll have to wait a little bit to see this bubble up into the next layer drivers But at least now they're available to do that and Avoid leaking too much of the passwords into the log files now Obviously leaking the passwords into the log files is only a small part of the problem You still need to treat your postgres log files as Security sensitive data because there can be a lot of other security sensitive stuff that ends up in the log file at some point Certainly at the application level security sensitive stuff The json path support in postgres has been I'll temporarily use this one there we go so yeah, so Jason path has gained a whole bunch of new operators which mostly are I See converting between and then put data types within double quotes because really Jason doesn't really have data types You can convert between time stamps and things Inside of json path like you can convert to strings you can turn a string into a boolean and thing to be used in the Jason path query language inside of it as well So again, there is no sort of really Revolutionary json path functionality. It's just chipping away at the small missing parts to gain Full compatibility and to give you the full power of the json path language That's me. Well, it was someone. Okay Let's talk back up and replication. I well everybody loves replication. Everybody hates backups, right? I think that's the usual We've got a fair amount of good stuff in both of these cases that I think are quite exciting First of all, I start with PG dump because it's neither backups nor replication But it is dumps and they're still useful, right? The new thing in PG dump is you can now specify a list of what you want to do in a file instead of in a massively long command line So in this example here, I say, you know include table foo include table bar Include with a wild card in a schema But exclude the data for this particular table making it just the schema dump like you could do all of this on The PG dump command line before but once you start adding a few number of tables here that becomes very very un-maintainable So it's useful in that way and you just add filter. Okay, I'm gonna switch back to the other microphone. Here we go Yes, still works new batteries. We're back at it It's like one of those, you know the formula one pit stops, right? It's about the same thing So as I said PG dump a great tool not backups What is a good backup tool PG base backup is pretty good backup tool, right? At least if you're Can live with the limitations that it has one of the limitations it's had is that it can only do full backups Well, no more PG base backup in 17 can do incremental base backups Yes So I agree wholeheartedly that our documentation should not should not say that PG dump is a backup tool But I will continue to fight this windmill It's a hard fight everyone don't agree. They're wrong So PG base backup now can do incremental backup What is this moon? Well, it will back up compared to the previous backup only those disc blocks that are changed Which hopefully means that your backups will be much smaller I mean that's the idea if the entirety of your database gets changed between two backups There is no point in using an incremental backup, then you should just use a regular full backup Now the way that this works in PG base back as you've already known if if anyone in here is using PG backrest You know, you've been able to do incremental backups in PG backrest for many years and they were used to be at this granularity of a gigabyte file and Then they added block level incremental backups a couple of years back but still the PG backrest way of doing it is it reads the whole files and Basically compares the check sums to figure out what has changed PG base backup create has a new process in Postgres called a while summarizer Which you have to turn on you say summarized while equals on it'll start another background process that basically listens to the transaction log the while process and Picks out of it and say oh this block is changed Oh, this block has changed though this block has changed and Generate summarizer files that PG base backup can then use and say okay. Well from this to this Here is a list of all the blocks that have changed because you can get that information from the while and then it will back up Those specific blocks so the difference can be said that when you run the actual incremental backup with PG base backup It's faster. It uses a lot less Power of your machine because it doesn't have to read all the data Instead you have this while summarizer that runs all the time that costs you a little bit of performance all the time Versus more performance at the one time. Which one's going to be the best? I have no idea I don't we need a lot of more real-world experience before we can figure out exactly Which one's going to be right and wrong? I'm pretty sure it's going to depend on the scenario right in some scenarios the backrest methods can be faster in some scenarios This one's going to be faster Then you set you can't set a parameter called while summary keep time which tells Basically this while summary so how much data should I keep around default is 10 days? That means you can do incremental backups going back for 10 days So for example, if you do a full backup once a day You can do incremental every day and you're fine with 10 days But if you do a full backup every two weeks You don't leave this at 10 days or you will have no backups. Yes Yes, yes PG PG base backup is always a binary backup And that's one of the reason like you couldn't do this on a logical dump. It has to be on the binary backups And the idea here, of course when you then so you run your first backup You just run a regular base backup PG base backup give a directory When you then run your second one you run PG base back and you say dash dash Incremental equals and you point to the backup manifest of your previous backup So if you point it back to the full backup It will back up all the differences since the full backup you can also point it to your previous incremental backup at which point You'll get a chain of incremental backups That will work and then in the rest you just tell it to okay write this one over here And it will read the manifest Get the wall data out of that go talk to the wall summarizer and figure out which blocks have been changed in between them Now to restore this when you restore a general PG base backup, right? It's very easy. You just copy the files back and you're done That obviously doesn't work when you're doing them incremental. So for this there is a new tool called PG combine backup Where you just say, you know PG combine backup into this directory use the full backup and then the incremental backup then what goes here in the combined will be exactly what it would have looked like if the incremental backup had been a full one like it'll Put them all together and if you have multiple ones, you can just add them like a whole bunch of Haven't long chain of things if you had incremental every day. You want to back up to Thursday Well, you start from the full and then Monday incremental Tuesday incremental and etc And it will go through them all and generate one output. That is then the one that you just Restore as you did a full backup directly. Yes Yes Can you restore director? Yes, you can point PG like you can combine the This slash backup slash combine could be you know your var lib pg SQL data if you have cleaned it out first Or if you're restoring it to a brand new Somewhere, yes So if a block has been like if the block is in both the full and both of the incrementals For example, will it be written more than once? I don't know. I Would guess it's probably written more than once But so I think what it is if it's so in this while summarizer It will get written It'll it'll get written multiple times, but it generates basically a bitmap where it's only listed once Because so it's only listed once there, but if it's in I don't think the combined backup tool Is smart enough because it would potentially need a very large amount of memory to keep track of that So I think this one will just write it multiple times But I am not certain so Yes, so yeah, so the wall summarizer is Only needed to take the incremental backup not to restore it So when you're running this backup command if I'm pointing this to the full backup Then I need the wall summarizer to cover the time back there But if I had this scenario then when I took this incremental backup that would point to the full backup But this incremental backup points to this backup So at that point when when this one is taken the wall summarizer only needs to have the data back to here And then it will follow the chain across in the restore. Yes To start from a new full backup. I believe you need a new base backup To like start a new completely new chain, but you can have and you know There's no limit on how long your chain can be only of course it will take longer to restore The more you have changes in there. Yes Yeah, exactly. I think yeah, I think also the question was can you use the combined as a full bug? I don't think you can but again, I'm not sure it might be possible, but I don't think you can at this point Okay, so let's leave backups. This is I mean PG dump is not backups Some people think it is replication is definitely not that what about upgrades? There's somewhere in between, but there are some interesting things Postgres 17 will be able to preserve Subscriptions fully across upgrades. So when you PG upgrade today all your logical replication subscriptions go boom And you have to start over You will no longer have to do that. You will be able to upgrade without rebuilding your subscribers. That's kind of neat, right? Of course, you will only be able to upgrade from version 17 Because it's it's required on on that side as well, but you know, we like to prepare for the future For now, you're still in in the you need a new node that runs version 17 and logically replicate into this node But this is one of those things another classic issue with The logical replication is when you're combining logical and physical replication, right? You have a high availability cluster for Something that publishes logical replication. It fails over and now everything breaks To do that postgres 17 adds replication slots synchronization between the nodes As it basically means between physical replicas you can synchronize your logical replication slots. So today your Replicate the source of your replication publication really is an individual node But with this you can conceptually make it your ha cluster can be now to do that You need a multiple different things. You need to enable something called failover on each replication slot That's either done when you're manually doing PD create logical replication slot If you're you might be using this for something that is not postgres on the receiver side Or when you say create subscription, you just give it the flag failover on Then you need to enable sync replication slots on all of your standbys So that they will be receiving this data And then you need to configure standby slot names on The system as well because that's for postgres to know that it has to wait for these synchronous Workers that run on the standbys to get the data Before it's allowed to invalidate the data out of the slot Otherwise the problem would be if your logical replication runs faster than your physical replication It and then you failed over you would be broken again Now usually logical replication does not run faster than physical replication But you could have a temporary hiccup on the network to one of your standbys or something So you do need to set all of these and what will happen in practice is that this The sync replication slots when you turn this on it'll start an extra background worker that will just periodically synchronize the state of these so that when you do a failover your Subscribers can just connect to the new primary and what data they need it will still be there Okay, let's talk a little bit about performance. We all love performance Postgres is Almost touch that almost always faster in the newer version you will always be able to find something that's lower But as a general rule there are as usual there are a lot of infrastructure features That are really interesting to postgres hackers the giving access to you know new types of Structures for arranging tables internally in memory and things like that We don't necessarily care about those as an end user to an end user to us. It just looks like things are running faster That's great. We love it Signaling out a couple of specific ones copy can be made significantly faster I think the numbers so so one interesting one is specifically if you're copying data out There's a function called uuid out that's called every time you write the uuid out a string And if you have a sim if you have the Specific case of a single table with just a new uid column and everything is in cache and you copy it out It's now 60 percent faster Like that's a non-trivial speed up and obviously you don't have quite that ideal case in reality But it will be much faster and also simple things like if you're actually using copy to It would postgres would actually convert the encoding from The from itself to itself if the coatings were the same it didn't realize that they were different It does now so copy to in encoding cases. We're also just simply be faster Some of the more advanced things that are getting in there is something called self-join removal Which basically means if you run a query where you join a table to itself on Save the primary key in the simplest case and you just query one column from each of those two tables that join is completely unnecessary right Because you can just read both columns without joining in the table And postgres will now detect a lot of cases where this happens and just remove the join and get the column from the One scan of the table instead This is I mean you can look at in goes like the answer for a long time has been well Just fix your query like don't do that stupid join right but a lot of us are stuck in cases where we have automatically generated queries They are ORM queries or reporting system generated queries and now postgres will just just detect this remove the join and it'll run faster Another thing that I think and I was looking I was even surprised myself was going through the commit messages And I've read this and I found what I think is the best part of this feature in like the fourth paragraph in the commit message the sort of a side note thing And this is postgres will now deal with redundant not null calls as it's called much more efficiently So just setting the stage here. I'm if I create a table here. I have a single column. It's not null I insert a thousand values, right? whichever I do there If I run this query on that in postgres 16 and I explain select star from who where a is null, right? Or sorry, where we a is not null That's kind of a we know that a is not null. It's defined, right? Here's how a postgres 16 will run this query and I also realize this is a different test data because that was a thousand rows And this was run with 10,000 rows, but it works the same way Now if I run this one in postgres 17 it runs like that You know, that's a pretty simple thing. We no longer need this filter. No, it's gone We can just scan the table. We know that every row will match Now this is not actually the part of this one that I find cool, but this is the part that's you know, the headline feature for this What if we run the query like this select star from who where a is null? There is a not null constraint on this table. How does postgres run this in postgres version 16? Well, it runs a sequential scan across the table filter a is null. How does postgres 17 run this query? One time filter falls result This the again auto-generated queries Often don't realize this in fairness are my queries might also very well include things like this every now and then But I've seen it many times coming out of systems that are Auto-generated queries of an ORM system where it literally does an is null query where we know that can't happen And now we'll just detect it and not run it So I actually think this is Is the more important part than this? But you know, they're both good obviously and avoiding any work that we can avoid it based on the fact that we have declarative constraints And again, this is yet another reason that you should remember to put those pesky not null constraints on your columns Not only because once you get nulls in there everything bugs out when you didn't expect it. It will also run faster That's an easier cell Most versions to postgres include some enhancements to the parallelism It's not too much around the parallelism in 17, but you can now create index can now be parallelized for brin indexes Previously you if you were creating in a brin index, it would only use one CPU now It works the same way as B3 where it'll use Maximum parallel maintenance workers number of workers to create brin indexes as well The SLRU caches does anyone know what SLRU caches are in postgres? Okay, a couple of people pretty much all of them are postgres hackers and you know what they are. Do you also know what they mean? It's actually that part is much simpler it simply means a simple LRU so simple least recently used cash They're just like separated special caches the thing is there's been like one And now they're all divided up into separate cash banks That's not supposed to be an eye there. They're just divide cash in banks Which with separate lockings and you can now configure each one of these separate SLRU caches to a different size if you need to and these are things like you know multi-exacts in postgres Transaction ID caches these like very low level caches can be configured individually This is the same We already had something on PG stat SLRU which will tell you things like the cash hit ratio across the different parts of it But you couldn't actually control it before and now in 17 you will be able to override the defaults for these cash sizes And control them specifically now obviously like most of those things that like most of you are probably not gonna have to do that But particularly on like really heavily loaded system in with high concurrences Increasing some of these caches is going to make a potentially a substantial difference around the things we're doing So that's the list of things that I am planning to go through there's obviously always more and specifically now that we're only about Halfway through the final commit fast. There will be more things There are more things already now that I just decided not to include because again, what was it 16? thousands of commits There will definitely be more there are many many smaller things their performance improvements All over the place. It's no way to go through them all but This is also the perfect time for you to help out All right, we haven't released it yet. We haven't even put the beta out there yet, but you can actually get both RPM and yam and apt packages all the snapshot versions you can put them on your systems And you can run them maybe not in production. I mean you can I wouldn't recommend it But like if you have testing systems If you have automated tests, please throw postgres 17 at them and see what happens. Let us know if something broke Do you have performance tests for your applications? Run them against postgres 17. Hopefully everything will be faster Maybe it won't if you can let us know that now rather than after the release the likelihood of this getting fixed before the release Increases drastically So please do download these help us run these tests. Let us know how it works out. Yes Yes, I believe these packages are a certain able Devrem says yes for the RPMs. Do you know if that's true for the WM packages as well? Okay It's difficult to compare performance straight up between them in that case. Yes that is true But but yeah, and you can compare plans and you can certainly compare the fact that things work That your data comes back the right way But yes, that that is a very good point that from a performance perspective They're not compiled with exactly the same switches as the production builds So you can't do a direct comparison between those So yeah, please do grab us those help us out with the testing help us out reporting what works and what doesn't work You are the ones who have the real world applications Our tests are mostly done on you know synthetic workloads. We need to see this with real workloads And once you've done that hopefully we will have a Postgres 17 ready for you after summer With that, I thank you very much. I think we're all running out We're all running out to get to lunch I guess I have about two more minutes if we have any questions for now And if not, you can always catch me in the hallways later Okay, then off to lunch it is. Thank you What else to say You like tune this like to adjust it for the timber of my voice is it Test one to test one to that's a little bit on the quiet side. So I'm gonna bump it up just a touch All right testing testing one two Testing one two just someone go to the back of the room and see Testing one two testing. Hello. Hello. I mean, I can I can bring the mic closer, but it's more comfortable right about here Okay, testing one two Testing one two. All right. Yeah Test one two test one two. Yeah, this sounds about right so All right testing one two My name is Joe And I'm here to talk Yeah, that's not bad How does this sound? I'm not really coming through the audio, but I can crank this one up a bit too And let's see where are we at this one's at zero DB. So I want to go up by maybe Test one two. There we go. That should be about right. Can y'all hear me? Yeah, I can hear that just slight echo in the room Which tells me I've got a good level because when you pack people in here, they'll deaden some of the sound sound good Cool. All right Second that feedback. No, that was just me futzing the moon. This is in the back of the room That hits We're gonna wait a couple extra minutes for people to show up after lunch hopefully We get a few more Devrim, can you hear me back there? Is it all right? Is it is it kind of low I have to hold this closer? Yeah, I'll talk to At least I remembered my jacket. Oh, I should hear my teeth clinking as I was talking You're all right. You want me to talk louder? I know Just give me a sign Devrim. All right. I guess we're gonna get started So I'm Joe Conway. I've been around the postgres community for since the late 1990s. I've been a committer since like 2003 I If you've ever used set returning functions, that was a feature. I added long long ago.  Mostly these days. I kind of manage people So I do less coding, but I still fix bugs here and there and do a feature here and there I am also on the sysadmin team for the project and the security team for the project and The team that recognizes contributors among other things And I am a as I mentioned I'm a manager at Amazon. I run the team that does the upstream postgres contributions so Our team is all about contributing to the project and this is where you can get a hold of me I'll have that on the the last slide as well, and I'll make the slides available Somewhere. I'm not sure how scale does it. I can't remember Okay, so we're here to talk about the problem with glibc coalations How many people are aware of the fact that there's a problem? Oh, I expected like all of you since you're here, but I I'm gonna I'm gonna show you some some examples about That they make it obvious that this is a problem. I think it's a bigger problem than most people recognize I'm gonna show you if you run into it what you would have to do to fix it And then at the end of the talk I'm gonna talk about an alternative approach that I developed and it's been in use now for well over a year I Can't say exactly how many instances is running on but it's it's on quite a few Okay, so We'll start out if you're you're on a rel 7 system, right? And I mean is everyone here familiar with the basic, you know syntax of setting up a postgres database in it db and then PG control start right and then I'm into postgres So that's all I did was just build a new database on a rel 7 machine and This query here is going to just show us information about the encoding that the database is using The encoding in the coalition I should say And so you can see that the coalition it's using on a rel 7 machine is 2.17 and that's because on a rel 7 machine Glibc is 2.17 dash 326 at least the last time I checked it was 326 Might be something new right now, and if you do the same thing Excuse me on a rel 9 machine You'll see that you've got glibc 2.34 You say oh no problem. That's just like a minor version change, right? Well, no actually that The way that glibc does their versioning each of 2.17 2.18 2.19 each one of those would be a major version change and Typically for a given distribution They'll freeze that major version. So that's why rel 7.9 still has 2.17 But it the dash 326 means like 326 versions of that package have been released I'm going to get into that later on why that's important, but Suffice it to say as you change Your OS version you're going to get a new glibc version major version All right, so this is the this is what I call the in your face slide For anyone who wasn't aware there wasn't a problem or anyone who thought well Maybe there's a problem, but I probably won't be affected because I don't use I don't do anything fancy, right? Well, you can't get much simpler than this right we've got three values one dash a one a and one dash a a and On a rel 7 machine if you sort those you're going to get this order One a one dash a one dash a On a rel 9 machine you're going to get this order one dash a one a one dash a Okay, so If you thought this was like an esoteric problem that you probably won't run into because you just do simple stuff You may not be simple enough maybe you are but Maybe not you can be just using the ASCII characters and run into this problem and Remember I just basically built the database with all the defaults, right when I did a knit DB so I got En us utf-8 because that's what all my that's what my system is and that's what Postgres will pick up All right, so we're going to drill in a little bit deeper now. Is there any question about that before I go on? Okay Hopefully I've got you beginning to get scared All right, so we're going to start out by creating a table on rel 7 and So I'm it's you know one column table with the text column. I'm going to make it a primary key That's important for the purposes of this demonstration I'm going to insert those three same values into that and Then I just do a order by a select order by from that table and I get that same order We just saw right no big surprise there So now if I take that machine and upgrade it to rel 9 underneath my database This could happen in a number of ways, right? But you know you may not as the person responsible for the database you may not even have a choice right someone else may decide that for you One of the things you should notice though is When I first start up the database if I immediately do that same Select order by I get the same order So you look at that you say well, what's wrong? This is working perfectly There's nothing wrong with my database and So now the next thing you do is you go insert another row Except remember I had a primary key on this column and I'm inserting another row with the same value that I already had that should be an error But guess what it succeeds So now not only am I getting sorting in the wrong order? But I have a corrupted table and if I try and re-index that table to fix this problem That's gonna fail now because I'm trying to re-index a Unique index with my primary key, so that's not gonna work So this is just the the very simplest scenario and Already you can see how it can bite you So I'm gonna go back to this query that we used earlier and I and you can see this part here where it does PG database Coalation actual version this function will actually look basically it uses the libc call to say what version am I? Right and it shows you that and so you can see that you know, we were built on to 2.17 What's on the system now is 2.34? That kind of explains why we're in this hot water now All right, so what if I got to get out of this mess? well First thing I'm gonna do is actually drop my primary key and then how many people are familiar with the CT column Just a few So the CT is a system column It's not normally shown if you do select star from some table, but it's basically a physical row identifier and What I'm gonna do is use that to show me to disambiguate these two rows So I can pick one to delete Now have this been a more complicated table with more columns and If one of the columns was different across those two row versions, you got to figure out which one to keep so You're still you know, it's not clean. You're gonna have a problem. So the lesson here is One of the things you got to be careful about is not only these G-Libc versions, but if you're gonna rebuild all your indexes, which is what we're gonna do to get ourselves out of this You really have to do that before you let anyone into your database after the OS upgrade You cannot let anyone insert update or delete Before you rebuild those indexes or you're gonna wind up with a problem or Shouldn't say you will but I should say You very well might so you can see here. I deleted the second one just because C-tid Larger indicates it's probably the newer one that it was inserted in error Although if you're familiar with the term wraparound in Postgres If Postgres wraps around The transactions wrap around if you've heard of that It's possible that the the newer c-tid could actually be the older row So you can't necessarily count on that either, but in any case you'll have to figure out one of them and get rid of it if you want to rebuild that You want to rebuild that at re add the primary key, which will rebuild that unique index and Then if you do this alter database refresh coalition version What that will do is it'll go update the system catalog to say all right now we recognize that We're running with the newer version of g-lobsy It actually that doesn't fix anything for you. It just like Updates the catalog to say that's what I'm doing. So in fact, you could run this and still have corrupt indexes And it would it would look like you know, you're using the newer version of g-lobsy, but you wouldn't actually have fixed anything yet So now once I've done all that if I run my order by select order by again now I'm finally seeing the order that I would have expected on rail nine for those three values and if I go back to my Query to look at the system table. I can see that my That call version matches whatever the actual on the system now that I've done the refresh Okay, so now we're gonna take it up a notch This is This is something I call the coalition torture test Jeremy Schneider who's back here Created a github repo Which produces So let's see I've got this right here. Oops Sorry again This repository that he created basically generates Roughly 26 million Strings that are in the like One to five or six character length, but they're all like these crazy Unicode characters and He built that as I understand it. He Jeremy's got a lot of experience dealing with Customers who have done strange things So he's he's seen a lot and so he's got some good intuition as to what sorts of things might cause problems with these Coalations changing from version to version of glibc and so he kind of built this test There's no way to exhaustively test every possibility but this tests a lot of stuff right and This test is not a database test. It just produces basically a File of strings and he did a bunch of across different OSes and you can look through his read me And it's got some interesting data in and of itself But what I did was I took that the data from that and I just used a little Python magic to to properly Double up the double quotes that were in those strings and then and then put double quotes around them So it was easy to suck it into a postgres table and so Getting back to the slides If I create this table unsorted table just one column again and I'm going to bulk copy that Formatted Unicode text which is his output with my quotes And I do a vacuum freeze whenever you bulk load you really should vacuum freeze afterwards for a lot of good reasons But that's like unrelated to the stock And now this query here, which I'll use repeatedly is basically doing an order by all of the strings in that table and Then doing a string egg with no delimiter So it basically just jams them all together into one long string and then calculates an MD5 over it So you can consider that like a signature or a fingerprint For our sort right so if that value is the same we know that the sorting is the same over these 26 million crazy strings Now keep in mind as we go through the talk this 71 30 That's the kind of the signature. We're looking for And I also want you to notice this timing here. This was about three minutes To do this sort in a few slides You're gonna see why I want wanted to point that out and I'll again. I'll talk about that in a little bit more depth later on but there's links in here for Jeremy's github as well as the formatted Text that I pulled into my table is available online as well if you wanted to play with this Okay, so now I'm gonna build an index an indexed version of that table So basically I'm just doing an insert into a new table from the unsorted table and I'm gonna create an index on it and You can see when I do that same select again. I get the 70 130. So it's the same order And you can see now it's like three seconds So really fast to use the index and by now everything was cached Okay, there's an extension that comes with Postgres called am check One of the functions available am check will let you check the Validity of an index at a very low level Think I think that some of those functions were written by Peter Gagan. I think for the index checking part of it Most of them. Yeah, so Peter Gagan is also on our team at Amazon Spends a lot of time with his head like way down deep in the index code So when you run those checks against the both the primary key on our little table and on on the index table It comes back with nothing which basically means there was no error So those indexes are currently good So now I'm going to upgrade this same machine again, so I'm kind of flipping back and forth between rel 7 rel 9 I've now taken that Same table that was built on rel 7 onto a row. I've upgraded the OS to rel 9 and now when I do that Select order by from the unsorted table. You notice my Signature has changed because I'm getting a different order you also notice it now takes almost an hour Now I'm like I said, I'm going to talk about this a little bit later, but the the short Thing to catch there is this is a later version of G-Lib C So like I said, there's there's a performance regression that May or may not affect you But if you've got a lot of multi bite, you know unicode Strings that are being sorted. It may matter a lot Now the other thing you can see here and this shouldn't be a surprise at this point But if I select from that index table, I'm still getting the same signature But remember we're on rel 9. This is actually a corrupt index. It's not producing the correct result If it was the correct result, it should match that now right and it still takes about three seconds and Now when I use my Amcheck on the rel 9 machine after the upgrade You'll see I get errors So I get this item order invariant violated So this means basically you've got a corrupt index So both the little table and the big table have corrupt indexes now So this time I was smart enough not to let anyone go in and do any Updates or deletes to my to my big table fortunately, right? So at this point all I really need to do is re-index So when I re-index you'll see now I get the expected order for a rel 9 machine and I no longer get an error from Amcheck Okay, so that's kind of the end of that part of the The scare tactics Any questions about all that Yeah, go ahead This behavior is what you'll get if you started out on say a rel 7 machine with postgres And then you upgraded the OS Without rebuilding your indexes if it's clean install then right out of the gate You're just gonna get the rel 9 sorting semantics, so there won't be a problem But eventually when you upgrade rel 9 to rel 10 or whatever you're gonna have a problem Well, yeah, and this is I'm gonna get I'm gonna get into this in a minute, but yeah So Devin Devin's point is actually covered on the next slide, which is Anytime you have a distributed cluster you have to worry about the OS is not matching Go ahead John. Oh, I see Jonathan is is Feeding me a leading question. Yes, actually it's that's a good point. There's There have been spotted in the wild cases where Minor versions of G-Lib C Changing Has caused changes Unintentional changes in coalition so indexes have been broken by what should have been a minor version change of G-Lib C and by the way, it's also been spotted in the wild where there was a Minor version of ICU that broke coalition So I mean, it's not just G-Lib C. ICU has had this problem One of the things and again, I'll talk about this a little bit more with the alternate solution but one of the things you find out when you really start digging into this is G-Lib C uses some data files and it uses some shared object libraries that it pulls in in order to do all this locale magic and so it's dependent on data and code and You can break you can change coalition unintentionally with either If the data changes you could change coalition, but if the code changes you can definitely have changes in coalition occur Michael so which this was Susie So Susie actually did a major version in a service back. Wow. I Didn't know that one Okay, that's that's good. That's good to know Go ahead. Oh, so you you've basically facilitated database loading in the github repo so Jeremy is has made his His stuff more convenient to use if you want to test the database Awesome All right, so this is a different example. This is though. This is the distributed cluster example so in this case I'm already I'm on a rel 9 machine and I'm creating the Postgres foreign data wrapper Right, and then I create a foreign server that points back at a rel 7 machine And I'm still using that little three-row table, right? And so I create a mapping for a user and then I create a foreign table that Points to that three column that three row table on the rel 7 machine from the rel 9 machine Is that all clear as mud Now You notice one of the things I did here with these options I wanted to force a certain plan in order to facilitate what I'm trying to show here But you know, suffice it to say that you could run into this in the wild within real real life use cases So once I've done that now I'm going to do this Join of a local table on the rel 9 machine to my Foreign table that's mapped to the rel 7 machine I'm just trying to join on that column right and those three rows should be the same in both tables and It should just work right, but instead I get this error merge join input data is out of order And why is that because a merge join assumes that the data is sorted consistently on both sides of the join And because one of them is sorted differently on rel 7 than rel 9 It detects that that it doesn't know what to do Postgres doesn't know how to handle that right so it throws an error and you can see here in The explain plan you can see this is one of the things Postgres will do is Try and push down the order by if it can right and so By forcing this plan where it pushed down the order by I got on the rel 7 machine I got its order and I tried to join on the rel 9 machine with its order, and that's why this didn't work Okay, so here's a third type of problem, and I'm not going to say by any means These are the only three types of problems you might run into I just You know really couldn't get any more slides into this talk and thought this was enough to convince people They need to be worried about it, but This is another kind of legitimate thing that you might run into If you've got partition tables and you partition on a range So you can see here I've created a partition by range and Then part one is from min to one dash a and Part two is from one dash a to max right and so when you do this It's basically inclusive on this side and exclusive on that side, right? So this takes everything up to but not including 1a and this takes 1a and everything greater, right? And so on the rel 7 machine when I do an insert of 1a That value ends up in the first partition But now on a rel 9 machine if I insert that same value, it's going to go to the second partition so if you were depending on You know with partition tables you really can't have a primary key that spans the partition, right? so if you're depending on their petitioning scheme to maintain uniqueness you just failed and if you're looking for your 1a value on Your rel 9 machine it may not be in the place the partition where you expect it So again, this is just a third type of problem All right, so I'm just going to summarize everything. I just talked about your your coalition Was almost certainly provided by g-libc if you're on postgres 15 or earlier on 16 There were some changes made that made it more More reasonable to have icu sort of database wide I don't think many people use icu database wide. However So probably all of you are using g-libc for all of your coalitions So it most likely affects you Sword order relies on coalition indexes persist sword order Constraints may depend on order. I mean that's another thing I didn't really show But you could have like a check constraint that depends on ordering Partitioning depends on ordering merge join and other operations may depend on ordering distributed clusters Might depend on ordering being the same and by the way, this is not necessarily just postgres, right? There are lots of distributed systems out there that today and to the extent that they depend on g-libc or even icu for their ordering You could be getting incorrect results as things get merged together things. I don't know how Hadoop works, but That type of use case comes to mind So here's the other half of why it's important Actually in my my slide until this morning I thought rel 7 was end of life in 2026 and I think maybe a year ago when I first built these slides It was and I think they may have pulled the date in but it now very clearly says that the end of June in 2024 is end of life for rel 7 and I'm willing to bet a lot of rel 7 machines are still out there and Debian also ended June and Ubuntu 14.04, which is one of their long-term support versions is end of life in April Maybe my battery finally died and doing that before Okay, so so the problems we need to tackle are broken indexes, you know again don't Don't rebuild your index don't allow any DML To occur in your database before you rebuild the indexes if you've done an OS upgrade Otherwise, you'll have a bigger mess to solve Distributed systems you have to pay attention Replicas can have problems Foreign servers can have problems All right, so now I'm going to get into the third part of the talk Which is the solution that I worked on for this problem So anyone have questions about any of the preceding stuff before I move on How am I doing time-wise Devon? How much 25 minutes? Oh, I got plenty of them. Okay. Good All right, go ahead 28 was the was the huge change Well, I think Jeremy's conclusion. I think has been that every major version of G-Lib C has some changes To 2.28 was like a huge change So that one was like the obvious one, but every major version has Potentially some changes somewhere No, there have been some where nothing changed Right. Yeah, it doesn't it doesn't mean that just past just because you're past 2.28 doesn't mean like you'll never ever problem and you know to some extent some of this depends on What Unicode does too, right? I mean, I think some of the problems that people run into are when They have data in the database. That's an unassigned code point So it's an unassigned Unicode code point, right? So some data gets in there through some user form or whatever, right? It's it's It's really not an assigned character and then some later version of Unicode comes out and it like assigns it and then it needs to put it in a proper sorter and So that character is suddenly going to start sorting differently, right? I think I got that right So you didn't see any changes between 8 and 9 or Okay, so the solution I worked on like we called it lib compact coalition after lots of discussion You know one of the hardest problems and computer science is naming things, right? What it is is a method to build an extracted The locale functionality extracted out of glibc by itself standalone kind of in a nutshell and that is Done in a way that makes it Portable at least in theory I mean it in practice. I mean we've I've tested that across several Major versions and it does work can't promise it'll work everywhere for everything But for the things that we've needed it for it's worked great It allows you to basically pin to a specific Coalation semantic based on a very specific glibc and You know we've tested this both for x86 64 as well as a arch 64, so it works with arm and you can either Link postgres to this library and use those versions of the locale Functions or you can use LD preload Either one that you prefer Now this is available in On github I was going to talk about this. I think actually I talk about this later so this is this is the location on github and One of the things to note here because I've gotten this question a few times if you come into this Repository you just look at the like the main There's like nothing there. It looks like there's nothing there and this this is it's an odd way to handle the repository But after a lot of discussion about the details of how this stuff works. We decided it made sense So I'm sorry if you don't like it, but this is the way it is So you'll see there are two branches here two seventeen three twenty six el seven and two twenty six fifty nine Amazon two so basically what we've done is we've created this Based on very specific rpm package versions one of which was from rel seven and one of which was from al two and the reason that these are so specific is Because as we talked about even on a minor version change You can get subtle changes in behavior So how you would you if you wanted to use this you would clone the github repository? You check out one of those two branches right and then you just basically run G-libc compact collation build and then install the rpm and that rpm has no dependencies You build it on a rel seven machine You should be able to install it on your rel nine machine and I've taken the binaries out of that rpm and installed them on my Linux mint desktop And they work just fine Okay, so you know again the way this is built is if you go back and look at this repository You'll see that it's built on top of specific G-libc sources including it's basically what what happens is if you did an rpm source install let's say for G-libc 2.17 You would get a sources directory and a spec directory right and in the sources directory You see this? I'm just gonna scroll this really quick. See all these Those are all patches that get applied by the rpm Maintainer so whenever you install rpms on a Linux system the maintainer of those rpms are applying patches to the upstream tar ball Which may or may not change the behavior of coalition? Right, so in the spirit of like I don't know if you've ever heard of Intel copy exact But the idea here is we want copy exact we want exactly the same you could call it bug compatible Whatever you want to call it we want exactly the same behavior of as we get from one very specific rpm version of g-libc and So that's what this is built on top of is it basically we do an rpm source install And apply our patches on top of it and that's what's in this repository There are two distinct types of changes there's a few small changes to the g-libc source code itself and then there's some Somewhat larger changes that are required to actually get it to build The goal was to minimize the changes because again, we want to make sure that It was easy to reason about the changes, especially the ones to g-libc itself To make sure that we weren't going to inadvertently change coalition And they kind of fit into four categories. There's their g-libc had has a lot of Kind of hard-coded assumptions about file paths So like I said earlier g-libc locale functionality depends on libraries that it loads as well as data files that it loads And those are in a very specific path on your system. Typically something like user lib locale or something like that, right? Well, those are hard-coded at build time for g-libc and we want to have our own set in Parallel with the system g-libc So we had to modify those paths so we could have a path that we could control what's in it We also wanted to have G-libc is a is kind of very Cross-linked in that, you know since virtually everything that you build on a Linux system It's coming from libc, right the all the basic functionality, right? If you want to allocate memory that's going to come from from libc, right? So these things are all tightly cross-linked and what we wanted to do is just extract just the stuff that was needed for locale But in order to make this portable we wanted to be able to run on a system with a different version of libc so we didn't want to pull out the The functionality that wasn't specifically related to the locale and then kind of related to that G-libc Exhaustively kind of versions that symbols and I'm not sure how many people here are familiar with symbol versioning And libraries kind of sort of so like if I've got a Function call string call in G-libc under the covers. It's really string call G-libc dash To dot whatever right and that will indicate which version of G-libc that Particular version of that function comes from and when you link that into your program, that's what your program is going to look for so we wanted to replace those with the versions from This compatibility library so that when you inspect the binary it was really clear Which of the function calls were actually coming from libc versus which were coming from the compatibility library? As I alluded to you can preload in that case you have to build slightly differently Which I'll show in a couple of slides and when you do that it will actually preserve the original G-libc symbols and And then some really minor changes to the functionality Really, I think mostly just that that one function that report reports the version of G-libc Instead of reporting 2.17. It's now going to report 2.17-326.el 7 so that is very specifically tells us What this came from so for the package building code? We had to touch the spec file basically get it to build what we wanted to not build all the stuff all the other G-libc stuff that We didn't care about The custom build support. There's three main files this build files dot text Basically in G-libc every Every function that you would call from libc like again mem copy or something has its own C file in G-libc tree And so this build files text basically shows you all of the functions that were extracted out of libc in order to build this collation library The libcompact collation map is the symbol map so for the all of the symbols that we wanted to remap to our own symbol names That control is controlled by that and then there's the build scripts themselves And then as I talked about if you did wanted to use this and you wanted to preload and not Not specifically build postgres against the compatibility library. You would just have to tweak this one setting in the build file Okay, so I just want to go through some lessons learned that I had as I was developing this thing As I talked about when you build G-libc There's all this functionality that we didn't want and Much of that functionality actually is is Intimately tied to another library in your system, which is called LD so LD is the dynamic loader So when you load a shared object library when you're when postgres starts and it loads all these shared objects for extensions and whatnot LD is what loads them LD has some data structures specifically this 10 minutes, okay RT LD.global and the art read-only version of that that keep track of things like about your CPU and other locks and stuff that it's holding and those structs as you can imagine change from one version of G-libc to another and so you didn't want anything left in the compatibility library that referenced one of these structs And at first it took a while to figure out where all those were and get them out of there But that was fixed pretty early on Again that that rel9 timing that I talked about there's actually a commit This is the commit in 2014. I don't know if you can read that It says improve performance by removing a cache Now To the author's credit he goes through a lot of he has some data here that apparently tested something and Proved to himself that this was faster in most cases The way people use G-libc. I I have I haven't really looked at it that closely But I have to assume he just he didn't use certainly didn't use Jeremy's 26 million string torture test Because this this commit is what took us from three minutes to 60 minutes for that sort so One of the benefits that you get out of this if you were to stick with the This library from 2.17 as you get the old faster version on the newer system But by the way, if you I ran the same test with ICU and they were about the same speed as the faster earlier version of G-libc it It changed it changed sort order. Yes That's an example of C code Only changes that affected sort order, okay, so next thing I ran into an issue with C type in it. This was a threads thing didn't get picked up initially because Postgres is not multi-threaded So didn't run into the problem, but it turns out things like PG bench PG dump can run multi-threaded and What happens is the C type in it is needed to initiate Some structures that are used for the C type calls, which are things like upper and lower and what type You know, what type of character is this and those happen to be thread local variables and so when Libc does this it's it runs this at startup So when light libc loads it runs this but then it reruns it every time it starts a thread Well, the problem was is since That's running in libc not in the compatibility library It wasn't Re-initializing these variables in the threads and so that was causing problems, but that's been fixed as well And then there were some cases P-SQL was the was probably the best case. It was linked both to the compatibility library But also to libpq libpq the client side libpq was not getting the Compatibility library linked because of a filter in the make file. So actually if you want to fix that I have a patch which is Linked somewhere on one of my slides That will just it's like a really simple patch to some make files in the Postgres tree Which get them to actually use libcompact call for some of the tools But because of that set locale was actually getting called in one place in The compatibility library and in another place it was getting called in libc, and of course that didn't work very well and then the final problem I ran into is that The difference when you build with optimization or not you build when you build a binary in in a Analytics system with GCC you typically say dash o 2 mean means optimization level 2 so that kicks in Certain things like inline functions, but if you build with dash o zero Which is as a developer you often will do because you don't want the optimizations because it makes it harder to debug So I was building with o zero. I wasn't seeing this problem. We had the real builds with O2 and suddenly it was like well, why is this breaking and that's because there were some things missing from the exported symbol map All right, so real quick. I think I'm I have five minutes Is that allowing for questions I get until 330 All right. I got ten minutes All right. Thank you It's like daylight savings time. I just got more time All right, so I'm on on a rel 9 system I install the rpm for my 2.17 rpm for the compact call and Then in when I configure this now, this is a source build a postgres I assume if you're gonna get into this you're at least comfortable with This sort of thing when you run configure you have to have libs and then include that on there and the way GCC will build if you build with a library Specifically on the link line It's linked before libc and therefore if the symbol names are the same Your version will get used instead of the libc version. Oh, and this is I referenced that patch earlier So if you really want to capture all of the tools You have to fix those make files. That's what this patch does. It's maybe something I should get upstreamed I haven't really tried too hard though All right, so you test it out So you remember we're on a rel 9 system But now we're seeing the same sorting that we would see if we were on a rel 7 system And if we try and insert a duplicate value into that table we're gonna get an error which we'd expect Doesn't go in there and corrupt the index and now if we run that same query again The actual coalition is showing up. This is that change. I was talking about in the glibc version function it now shows The specific version that we're running at and so when we use when we do our string test again Now you're getting back to that signature that we expect from a rel 7 machine But it's on the rel 9 machine and the timing is back to our three minutes and of course our indexed version is also getting the right answer and Amcheck shows no problems with our indexes Okay, so just a couple more slides. This is really just if you're interested in all that all of this topic If you wanted to play with this This is one way that you can examine your Postgres binary and see which symbols Have been linked to the compatibility library versus which symbols were linked to glibc itself And so if you see something like set locale is limp is link link to libc then Something went wrong and then this is sort of the same kind of thing This script will go through your entire build tree and make sure all the binaries all the binary objects in the build tree Have been built all the symbols that are in the map file are actually linked against the compatibility library and not libc so these are just some helper things that I developed along the way to Kind of validate that everything was working so in summary G-libc coalitions can be hazardous And kind of covered it in some ways that you could fix that or at least deal with it and I showed a an alternative approach So I think I'm got a few minutes left for any additional questions Five minutes Yeah, go ahead So the question was is Devrim gonna include that in the RPMs and Devrim gave an emphatic tone Jonathan right so Jonathan's question is what do we need to do upstream to make this better? Part of that answer. I actually meant to talk about this Yesterday Jeff Davis who's also on our team at Amazon while I was working on this kind of how do we deal with the legacy stuff? I asked Jeff Why don't you please look at fixing this going forward and so Jeff has actually gotten the very first commit That provides a built-in coalition provider. I think it's not complete yet. I Understand from him. He he's hoping to get everything in there So it's 17 at least has an option for a built-in coalition Which then means this stuff is all in the hands of the Postgres developers and we're not dependent Excuse me depending on what's going on in the system So that's part of what needs to happen. I think it doesn't solve all problems but for a lot of use cases, this is just perfect because You're you're really depending on your indexes to do Lookups fast and joins fast and there's only some cases where you care about, you know does 1a sort before 1-a or not right and in those cases you can actually put a collate clause on your SQL and Get it to use a different collation so and and this will be a lot faster even than The 217 G libc to boot and it'll be faster than ICU because it's just basically doing binary Comparers so that's part of it. The other part of it. I think is it's something that's come up Thomas Monroe posted like a proof of concept of been I guess a year or two ago now, but He he posted a proof of concept for a way to load multiple Coalation libraries Simultaneously into Postgres and really ultimately we need that so that when you go to the new OS We still need dev room to like support old versions of ICU Right, so when you go to the new new OS you have to have the old ICU and the new ICU and then your indexes will Still work off the old ICU, but then you have a migration path Online to start building indexes concurrently that use the new ICU and so that you can kind of in a controlled way Cut yourself over from the older to the newer so that's ultimately I think what we're gonna need to get to I that's not gonna happen in 17 Maybe that'll happen in Postgres 18. You know we can cross our fingers well, unless you build it with the The preload preload option so that it can only be used preloaded So if you do that then basically they just have to install the rpm and set up LD preload I mean the reason we didn't default to using preload is because it's a little bit It's a little bit harder to verify that you're actually getting the symbols pulled out of the right library when you're preloading and If someone like messes with your preloading settings and all of a sudden you go from being using the compatibility library To using libc that would be that would basically break your indexes. So I think preloading is a little bit more fragile But it would be easier because then you don't have to rebuild all the postgres binaries. You could just it would just work Michael did you have another? I think we got like one Absolutely. It's an open source project. So if you wanted to you know, I We needed the al2 version I did the the red hat 7 version because I figured that covers a lot of people, right? But if you had another version and you and I'll I'll be happy to you know I'll be happy to help you Get through doing that. It's not What I originally developed this for al2 and when I did the rel 7 version I think it took me like a day and a half Of hacking to get it to work So it wasn't like it wasn't like a moon launch, but it wasn't like trivial Well, yeah, that's true if it's If it's debbie, and I'm happy to work with you to try and make it work even on debbie And it's just that would be a heavier lift. All right, I think that's all the time we've got so Thank you very much. If you've got any other questions, I'll be around in the booth on the weekend. Come visit us Hello Is this thing on? Test test test test test. Oh, wow. So it See what we can do Yeah, so I was in brussels and I did this presentation and you know, so super quick You got to get it out. I thought it took it. I got home Because I have a real night like a spotlight I emailed odds. I was like, oh my goodness. I really like that clicker a month. I've been searching.  I have no idea. So is this actually coming through out there at all? Yeah, yeah, yeah, okay. Hello. Hello Yeah, it's not the same thing was happening. If I hold it really close, but They had adjusted it yesterday for some reason and it seems like it's Who uh, who quit me out? I actually I'm gonna type at the end. So I Want both hands free I guess I could put you check check check. I could put that on the stand, I guess Transforming data with the power post. It's really quiet, right? Hello. Hello So that's better, but it's still I have a loud voice and that's not really loud. Oh well It's okay. All right It's mostly for the recording. I wouldn't need this otherwise, but Are we're going to give another minute or two? Uh, it's just cold in here. I'm sorry about that. I wish I could fix it I've tried like three times ask them and nothing's changed. It's slightly warm up front All my fonts hopefully are big, but maybe you'll see better if you come up How many folks are regular postgres users? How many of you are regular postgres users? All right But not everyone These speakers really are not loud like I am eating this thing and it's really not you can hear we just fine No, you're close Can you hear okay back there? Yeah, okay Anyway, I'll do this But that's great. A lot of people have done a lot of stuff. Yeah, there are so there are so many possibilities at this point for sure All right, I'm going to go ahead and get started. It is 346 Uh, probably we'll definitely have time at the end for questions Uh, the title of this talk has changed like three times as I give it I learned, you know, I changed a little bit and realized like, okay, got to figure out a Better way to identify exactly what we're trying to go through here. So for me I'm going to talk about really taking raw data Transforming it into something that hopefully is usable for doing whether it be analytics You know or some kind of transformation, which we may have done previously Outside of the database There's a lot of power in postgres to help us do that internally So this is me ryan booze. I currently work at redgate Redgate is a database tooling software company based in england They have primarily for 25 years been in the sql server and microsoft platform space They acquired flyway. We acquired flyway, which a lot of you probably have heard of it's a migration tool for doing database migrations as you upgrade and change your schema Acquired it in 2019 and been putting a lot of energy into it. There's still an open source version of it And we now support that even that open source version supports upwards of like 60 some database connections To do migrations to snowflake and all the others. So I am ryan booze just about everywhere So, you know linked in wherever you you have You can come find me. I love to chat. Don't be afraid to reach out I'll leave this here for a second and it's also at the end. So I was doing the I had this conversation on twitter yesterday You're welcome to take as many pictures as you want But I just want you to know that this deck is already up there You could download it right now and follow along if you want Um, but it is there. It's a pdf and it looks like this So this is the repo. I have a presentations folder and all my presentations are there I try and get everything, you know, anytime I do a talk a second time or a third time It's always updated. Um, so this will take you to that. It has a sequel script I'm going to show you at the end and the actual pdf of The talk Oh, sure. Yes, you can you can post a picture. I totally get that you're right all right And I also So videos like this if something's recorded and it's public I just link it to a playlist for each year So my playlists are here, you know, if the talk is helpful and you want to go back and watch it I'll make sure it gets linked here once it's public. So All right, so youtube again at ryan booze. You'll find the stuff there All right, so here's the talk Right five basic sections and we'll do a little demo at the end We're going to talk about elt versus etl All right, we're actually etl versus elt and then talking about how we can use various features of postgres To set up the building blocks to then go even deeper Once you've learned the building blocks, all right And when I've come to learn about myself and as I go to conferences and talk to people Is sometimes it's easy to assume that everyone understands exactly how recursive cte works Or what function in postgres allows you to split strings in a specific way to get Values out and this you know this that and the other thing Because it feels like once you've done it for a year or two like oh Everyone must know this and realize that that's not the case. So when I first did this talk It was this monstrosity of all the fun puzzles I had done with advent of code and all the fun things I did to solve all those puzzles And after about 50 minutes, I'd gotten through three puzzles. I was like, okay I have to rethink this Um, and the reality is I realized that most of the folks that were coming Didn't fully understand just some of the building blocks that would help them do these kinds of things in their environments So that's what the majority of this is I also if you go to that github repo you will find some of the puzzles I've solved over the last two years With advent of code using sequel and that was a challenge by the postgres community. It was fun I love solving puzzles. I've learned a ton About how to better use postgres and sequel so advent of code A lot of this has been uh, you know this Uh, I kind of really got into this a couple years ago trying to better understand how to take what seems like Non-relational data and making it relational with the portal Back in 2021. I guess I was trying to do a sample of I wanted to take those emoticons or the emojis Sorry Those little squares and turn them into data. I can analyze And sure I could do it in a hundred other ways. I could do it in python I could do whatever language this and that but I thought what if I could just take the In this case the tweet the text of the whatever we call them now Shoved it into a database and then with sequel alone transform it Into a table of data that I could actually analyze And in learning how to do that I discovered new things about postgres. I had no idea about I didn't understand what was going on And that opened up the opportunities So both of these were kind of the on-ramp for me and that's why I'm going to use them as some examples today Uh, most everything you'll see here is available in postgres 9 6 forward There are one or two functions that I will show you or are in the sequel that are only in postgres 14 plus. All right I'll point out the one. I think that's in here. There are a couple others So when we get to the samples some of the sequel I'll show you in slides and then when I do the demo I'm primarily using these two puzzles. So you can go to advent of code You can go look at the the content the puzzles what you're supposed to solve Almost I think for any of the years they've done it. This has been on for I don't know Does anyone know 12 13 years something like that? So I picked day 7 for both years. It was totally a coincidence I'd actually didn't realize that till I put the slide together so day 7 december 7th puzzle for 2023 is really simple and You know, it's a very simple kind of input and allows me to show a lot of this and then Last year's day 7. So basically the the 2023 puzzle is just a simple line It represents a hand of five playing cards and then a value And the puzzle was to split all these things out understand the cards And then you do some addition and multiplication based on a bunch of other things But you got to take that string and do something with it And so that it just allows me to show you something pretty easily And then the order of the cards in the hand left to right 1 2 3 4 5 is important to later solving the problem in the puzzle Last year's day 7 Is going to familiar to a lot of us and that's one of the reasons we use things like recursive cte's It's basically about moving up and down Through a folder system on a computer and so we can take the input the command cd and You know up down whatever and find Where all the files are within the folder structure and again the the puzzle was basically defined I think the folders that had the least amount of data To delete that folder because you needed to add a file You needed free upspace But again, it's moving up down through file system, which all of us have done at some point So let's talk about etl versus elt. So who has heard of etl Most of us have who has heard of elt a little Fewer of us So the whole point of etl Is to take Non-relational data And convert it into something that's relational so that we can use the power of sequel To analyze that data Now the data can be A text file The data can be a grouping of you know a text file and connecting to another system like snowflake and bringing that together Connecting to a parquet file with a text file with you know, whatever the sources are Could be many different sources, but you're trying to get that related turning into something You can get into a database and do analytics on The differences are really minor, but really You know powerful depending on which way you want to handle this data, right extract transform and load Is primarily external There are tools that have been built over the last 25 and 30 years to do this for you, right? They're specialized tools and they can do all kinds of transformations But they're special they cost money And it's outside of the thing you're ultimately going to use which is the database Extract load transform is just that small difference. We're still extracting We're getting the data from wherever it's at But then we're going to load it into the database first And then use the power of sequel postgres and the things we can do there to transform it So why would we do one over the other? Well, let's talk about etl briefly. Why has it been so popular? And it has been popular for a long time. This is the primary way That we take all this disparate data and turn it into something we can query It's been popular for a couple reasons. It's not reliant on a specific database, right? It's often external tool That can pull all things together Often kind of in a pipeline you in events together to process data in some form that eventually gets it into a table like thing That you insert into a database and then you move forward Database is the other, you know, probably the primary reason is as the web grew Most of the databases we use at least until the last couple of years did not speak the language of the web very well Right, so it used to be xml a long time ago And then the last 15 plus years we've been using json for everything And most databases didn't have a way to store json efficiently and to query it well Right, so we had to have something else to transform that thing to get it into the database so we could do it Well, that's that's change or starting to change pretty dramatically Then honestly one of the other reasons it still exists and still popular is It developed an entirely new class of jobs And so people have learned and they become experts in these tools The tools often cost a lot of money for the licenses You might only have a team of two or three people But they're really valuable and it's hard to want to get rid of that like You know special team if you're not sure that you can do everything you need to otherwise, all right One of the problems at least in my experience has been though with etl is because it's external Iterating when new things happen can be really slow Because you're relying on an external tool. You're relying on people Usually you have to have these different requests. You have to then decide as a dba or developer Okay, what's that mean to my schema? What do I need to change if I add the columns I need and I give that request forward They're going to understand it. Am I going to actually get the data I need? So there's a lot of iteration that's outside of the database and it slows things down. You can't kind of go as quickly as you like Now if you can keep that processing in the database Closer to the database at least you get a lot more control over those iterations But you you have things like temporary tables You can try something without actually impacting your schema yet see if it's going to work and move forward You don't need to rely on someone else to do it And that's really then where I started to learn more about this concept of ELT now. I actually in my career I'm 22 or 23 years into this whole tech thing I did a lot of etl a couple jobs ago. We had you know thousands of client databases We would pull stuff together to have metrics and to you know Pull is this client? Is there kind of data for building data similar to the other clients within this group? this region what have you But as I started to realize that I had all this information now with apis I could grab and I could start to do this in the database as concept of ELT became really important to me Or really interesting at the very least so A couple of benefits number one it retains the whole transactional nature Right one of the problems with an external tool is you don't have that control It's going to process what it's going to process But if it fails in some way If you were inserting that data into the database you would know the rows that got committed got committed Right And so you lose some of that because it usually ends up being a batch process and some things You know you might lose some of the nature of the data And postgres has added over many releases a plethora Of functions that can do you know all kinds of transformations with A lot of data types Whether it's json, whether it's text whether it's array data There's a lot that you can do and and honestly for me one of the things that was most helpful or interesting as a developer You know my kind of developer Experience coming to the foreground It's just something simple like arrays You know when I understood years ago that postgres had an array type that was mind blowing to me And then to recognize a lot of these functions End up using or exporting array types and there's a lot that you can do with that So this all works together to say I can get some data And then with a few functions all of a sudden it's a whole new it's a whole new thing that I can start to analyze All right, so etl versus elt one is external insert the final result Elt is insert as much as you can as raw as you can and then transform it I want to talk about inserting data because obviously that's you need to get started if you're going to load this data And you have to worry about this with etl as well But when you're trying to get this raw stuff in as quickly as possible just need to know how to do it a couple quick slides A couple things I would say is if you're going to try and work through something like elt You want to do your best to get The simplest form of data Simplest schema that you're going to add it to First right so if I have a text file and maybe it's a csv Maybe I don't know what columns I want. Maybe I don't even want to split it apart yet Because I don't know you could just save rows of of the csv to a table JSON just save the JSON document pull out what you want, but keep it simple Don't get too complex initially And then post process all the stuff that's in there, right? So I just have the staging table with all the rows and chunks of data and then I can figure out later How to start to do that that saves you that step of a lot of time up front What do I do with this thing first? Well, if I just have access to it I can go ahead and just start trying testing playing with it If you're going to copy it in, you know, just a couple quick things Use copy if it's something like a file. It's probably the easiest thing now um supported by you know most um Sorry is the most important method method for getting data in I'm going to talk to on the next slide Typically csv, you know, there are other things you can do you can name your own delimiters if you have custom formats But it's a good way to get data in and then I'll just say this I worked at timescale for a couple years and most of my history is about time series data And when you get lots of data coming in quickly, you learn that it's best to work in batches And so if you're going to start to ingest a lot of data with postgres and you're using some kind of You've developed something python go whatever it is Don't insert one row at a time batch up 10 batch up 50 and then do an insert and you'll see the performance Go up pretty dramatically just by doing that Um go to 500 go to a thousand test it see how fast you can get some data into your database Now two words about copy and I just put this here because again You're going to walk away if you haven't used copy much you go to play with it I just want to get this stuff in front of you So copy is a sequel command in postgres, but it's a postgres command. It is not in the sequel standard it actually is a Predates postgres as we know it it was in the older version of postgres And it's one of the things that came over And it requires a file. So if you use the sequel command copy It's looking locally to the server the file system of the postgres server Now most of us in a production system don't have access to that Now there are ways you use a hosted solution like amazon There are ways you can use copy to access like lns 3 and so forth, you know, whether it be in redshift in some other ways But you're looking local well, most of us don't have that access Honestly, even if you're using docker locally You probably aren't putting your file in the docker container, right? So they're just on your file system So what you usually then do is use the pcql Meta command copy so the slash commands we call them meta commands And so it's a meta command called copy and what that does is it reads the file off your local system It simply streams it in these in the copy format Into standard in on the server. So you're getting the same benefit But you don't have to be local to the server Now the only thing you have to know about copy are there are a couple quick gotchas number one It requires things to be in order if you're doing a csv You can't just like name various columns. You can exclude columns, but you have to know the order of the columns going in The types have to match. So if you're trying to insert into an integer column and it's text It's not going to work. I have no idea if this is behind me, but I kind of want to listen Anyway And there's no data conversion So if you have a date format and the string is different than what postgres expects It's just going to fail and the problem is any failure Is an error it stops and any work that's been done is actually in the database But it's not visible and so it's taking space and it won't go away until you vacuum sometime later You could have inserted three million rows of data and three million and one fails And you lost three million rows of data Right, you got it to start over again. So that's one of the problems with a copy the larger, you know, the more data you insert However, there are other tools So pg loader is one that I refer people to if this is a process you're going to do It's a tool written a number of years ago started by Dmitri Fontaine A number of people contributed to it and it basically takes the file It allows you to specify various Transformations if you want so it can identify that the date format is wrong and it will transform it Before it does the copy for that row And then if there's an error it can basically save that row out Ahead of time and then just keep going. So That's one tool. There are some others similar to it if you Again, this is in that playlist. I shared earlier, but this is the talk I've given a few times And that's just four plus. There are a whole bunch more that you can play with But a lot of these same principles just to to go through Any questions right now? Yes That's correct.  Yep It's not helpful or things like that. So that's where often great question So the question was you know is I should have said that earlier. I apologize um You know just copy handle something other than just kind of textual data whether bcsv or some of a delimiter It's not set up to do that right so it won't handle copying json in now If delimiter correctly and there's a call in this json There are ways to actually get that in and it will work and sometimes it gets a little bit wonky and people have trouble but Just inserting it directly and trying to transform in some way wouldn't work That's where you have to use probably a programming language because you are interfacing with an api and you're getting data out Well, then just use that Whatever language you're using to save into the table and then you go about your business all right So when you're doing something like elt My what i've learned over four or five years of just playing with this stuff And helping some clients do things because they're trying to iterate quickly Is just keep it simple Back in my older days of etl I would you know, we'd spend a lot of time thinking about the schema we wanted So that we could transform this data and it was a really long process But if i'm going to do it in the database, I can just get the data in there And so keep the initial table simple. The only thing I will say is if When you're inserting data, you're going to see me do this in a little bit Generate an id column for every row that gets inserted. You don't know when that might be helpful for you later Right, so just so you have the order that things are brought in there that might not exist in the data itself Should just create an identity column. You'll be surprised. It might be helpful at some point If it's time series data, maybe put the time series the time stamp of when it was inserted, right? So you have a time order in some way that might be might be something you need now There's probably a time stamp of that thing a metric or reading whatever But you might it might be helpful for you to know It was read five minutes ago, but it was inserted right now in the rows that were inserted in the batches and so forth Again, I've found that to be helpful when it's time series like data um, and then again Just preprocess what makes sense.  So this is that december 7th puzzle It's just rows of text So I didn't do anything with the text externally I simply said, you know, I'm going to copy the data in and notice I generated an id column Right. It wasn't part of the file But that way I have that number in case I need to reference order in some way Maybe I maybe I need to do window function and I need to look back to some number of rows in some way on some id You have no idea when that's going to be helpful Now wordl This is an example. This is a portion of the json From the twitter api a couple years ago I was reading in a couple million wordl tweets over the span of a couple weeks And this was a chunk of what the the json looked like I didn't need all of this So initially I just wanted to make sure I was saving them. I didn't want anything to error I just wanted to get the data in so my initial table Was just this I was pulling out the time you know the timestamp. I was saving it I was pulling out the tweet id so I could reference it later if needed And then I just saved the raw json And I just let that thing go then I knew I was getting the data and I could work separately on transforming it This became a part of what I did then as I transformed the data now I kept a separate table Of the raw json and why I did that is This wasn't my initial go-around. I thought oh, I only need the author handle and you know the tweet text And something else And then I would iterate a little bit more and said oh, you know what I do want to know The location because maybe I could start to correlate tweets of where people are at And so I could change the schema go back to The you know I could link on the id of the tweet and I could just update this data because I had access to it Now if you're storing all that data a second time. Yeah, that's gonna be it's gonna cost right you're gonna have to store that somewhere Now in most cases if you're in a hosted solution, you'd have some kind of times You know, I'm gonna kind of store it forever. I'll archive it or I'll delete it But in Postgres you could save it to a table space that's really cheap storage like if you're in the cloud That table could be stored out to s3 in some way right or something some kind of object So it's really cheap you have access to it if you need it. It might be slow to get it back later, but you still have it all right Does that make sense get the raw stuff in as quickly as you can And this probably this table went through two or three iterations before I got to this Because I just wasn't sure what I would need from that other data I want to quickly show you this as just an idea. So Everything I just talked about was taking your data and putting it into a simple table first Now what I have learned to do particularly with the advent of code puzzles Is has anyone done the advent of code? Any of those puzzles okay cup of you have So the way they're set up is each day a puzzle gets revealed and there are two puzzles for one set of input data It's always about Christmas and usually about elves doing something and it's always funny and complex and weird But it is what it is And I would realize in in every puzzle you get a Little chunk of sample data right the actual input is a larger file But in the descriptions they give you you know 5 10 15 rows And it was just easier for me to do a derived table Often to start I didn't want to deal with schema. I didn't want to create tables I didn't want to download the the file and import it yet Because the sample data They give you what the solution should be for that sample data And so I could work on my Solution without importing data creating tables or anything So in this case it was five rows of playing cards five playing cards space and then the number is a bid It's a value you do something with later And so I just copy those into into this um Reg X split to table So I essentially got rows of data and I could just then start iterating through ideas of how I was going to process this And then when it worked I could create the table import the main thing and get my solution So you'll see me do this sometimes and honestly Think about it like think about as an option if you have a new puzzle You know something you're trying to solve grab a couple rows of sample data or sample text or whatever it is sample csv lines Throw it in something like this you can get rows and see how you're going to transform it I'll show you a little bit later if it doesn't quite make sense to you I'll show it to you in the demo. You'll understand a little bit more Now normally what I will do because it's really hard. This is what we call drive table Right, it's it's well. This isn't quite a drive table, but this is the output from a function I would have to put this in a sub select if I want was not going to use a cte We're going to talk about cte's in a minute And so normally what I do is I grab my text I throw it in a cte So it looks like the table I want I'm probably going to do later And the other reason I do it this way is again. I don't have to iterate any schema I'm simply changing this There's no table action yet going on. It's just function output if I end up with more columns It just is here. I can iterate and then I can create the table that I am ultimately going to want for the final solution Now I want to quickly point this out. Who knows about dollar quoting who's used it in postgres all right Super helpful There's a lot of ways you can go about it. So there's a couple places you have probably used this if you have programmed within postgres That is When you do like a function you have to do dollar quotes Around the function because there's text inside or you have to do a an anonymous function, right? So something that's just you're creating on the fly It basically creates a block of code In this case dollar quoting is really about the text. This becomes a string literal And I don't have to worry about you know escaping characters and escaping character returns It's just helpful. I use it often in formatting So rather than doing a single quote and putting the stuff in and doing backslash or whatever I'll just do $2 signs in case the string And then I have a literal string to start to work with all right. So that's what I'm doing here I'm taking the literal five lines out of the text file I have to worry about the carriage returns or anything else and then I'm feeding into a function in some way Just a helpful hint Hopefully you've already caught one or two things that you didn't know about All right, so let's run through this basic building blocks So I have data I figured out some way to Quickly start to get to it and iterate it And I have lines I have table rows coming out somehow Now I'm going to take that data and do something with it. So there are a couple functions that I think have Maybe you're doing it and you don't fully understand what's happening. So that's why I'm going to go through The first is cross joining. We've probably heard about cross join cross joining is when you Take two tables or more than two tables and you join them with what we call cross join And it basically says for every row in the table on the left of that join iterate every row in the table on the right The output is a product of both tables You know, if there's 10 rows in one table and 10 rows in another table, you're going to get 100 rows We can demonstrate this really easily with something like generate series, right? I'm saying generate a series from one to two Cross join it with another series from one to four and you'll notice that I get eight rows So One iterated through each of the other rows two iterated through each of the other rows Why do I care about cross joints? Well, we get to cross join laterals And this is so tremendously helpful for taking data and iterating and processing it through a SQL query Here's what this means When you cross join when the right hand table of a cross join Is a function. It's a set returning function. I gave you an example earlier like the reg xp split the table I think that's what it was We it is actually considered it is implicitly what we call lateral join And that means the table on the right can reference any output From the table or function on the left And all of that output from all of those tables or functions gets fed back up to the select and it can be referenced This becomes kind of cool, which hopefully you'll see in a minute So again functions can anything on the left and then I call it kind of the reach back because Later in the query I can pick and choose from any of those outputs However, they were processed to make my final query in some way One quick helper in all of my demos. I don't use I don't actually say cross join lateral It's just a lot of typing. I don't care The comma means cross join and again because in this case The right hand table, which is actually the second generate series. They're both Set returning functions, but in this case, it's on the right that comma implicitly means cross join lateral All right, that's just how postgres works. It's in the documentation. You can read it I don't have to say cross join lateral. It's implicit When it's a it's a set returning function on the right And so this is kind of what you can look at it as like on the you know, I have a select statement I'm selecting from table t And then I have a comma And I'm feeding some kind of function in here in this case a string to table, right? So I have string to table as my comma, right? It's a function and I'm referencing table t column a And whatever that is, I'm basically saying split on nothing and it's going to iterate the rows You'll see examples of this in a minute Now I do another comma and because the second thing is also a set returning function It can refer to any of the output above it or on the left as it were And then all of this stuff gets piped back up to the top and notice in my select statement I can reference all of those things as tables and any columns that come out of those functions It's a lot of back and forth Why would you do it? You'll see a couple of examples later It's just a really fun way and really useful way to process complex data as we're trying to transform it So it also can simplify sequel higher up And there's some fun ways if you go and look at some of the advent of code solutions You can actually then use something like values which you can kind of reiterate and reform some of the data A lot of things you can do with it but Then I threw this in the middle Now that we have a function in the from clause, it's going to become really valuable We can any function That is a set returning function in the from clause So it's a table right to returning a set of data Postgres has this unique thing called with ordinality Now that means not only do I get the output of that function But it will simply add an ordinal value with every single row as a new column I don't have to do Order buys row numbers hiring the query anything like that It's much faster and you'll see why this becomes very helpful Again, when you need to know the order of things coming out of that function This is the best way to do it Anything else here? Yeah retains the order of the rows coming out So you wouldn't have to necessarily Do something later to order it to get the row number What that means is you can take something like this which is again that that cte with the the Function inside so i'm saying hey add the row number From reg x split to table and it's going to get me these five lines it works Now in a much larger file The biggest problem with row number as a window function Is it requires it must scan the entire data set to add the row number right it has to do the order by Everything has to be done first and then it iterates the entire data set again to add the row number So you're adding overhead I could instead take the output of that function and simply say with ordinality And then i'm going to get the output of the function that's in this case lines And then the id i just without you can call it whatever you want. I'm giving it an alias of id And I get the exact same thing Check out with ordinality. I'm going to show you a couple of examples And hopefully a little light bulb will go off and you'll see how can be useful in what you're doing And then the other thing I said so we have with ordinality. I wanted to make sure I covered that so this wasn't just thrown out of nowhere One of the other reasons that I've found to start to use these functions and cross drawing laterals Really becomes about what happens up top So this select statement i'm starting to transform it right i'm now taking I need to get those Cards and I want to get each individual card out. Okay, so i'm going to move that to the from clause I can take any row so I uh, let me see this is not my yeah, I do have it. This is not my clicker So this is the lines column out of december 7 Right and so this is splitting it apart. So i'm getting the cards and then i'm splitting the five values Right there and the first row it's three two t three k And i'm splitting them out into three two t three k individual rows Because I need to know the hand it was played in in the order of the cards in that hand But it's still kind of messy and so actually I could take that split part Put it in the from as another returning function So that my query up top just looks nicer I see a lot of people do that like it again It might be valuable and helpful to just clean things up a little bit And then you can iterate down below if you want to change lines or something gets more complex I just throw it out there because I've seen a lot of people do it like you know what like I kind of like that I can hide a lot of things here's an example that I actually had to get some help on this one This is from last year advent of code This is about It's a really complex thing you're removing two ends of a rope throughout a whole grid And you had to track the position of every end of the rope blah blah blah blah So there's an h and a y and x into this And so notice the select query eventually became pretty simple Right I had the ends of the rope and the h and the y the the head and the tail of the rope That looked really nice and I could hide all the calculations down below and some cross joins, right? So I'm just transforming all this data and when I'm getting out with um, a couple aliases Right here made it much easier to just Work on it down below and the query up top was a whole lot easier to work with Then comma and doing all the stuff up there Any questions on cross join lateral sweet All right comment table expressions now most of us Have used comment table expressions. I'm sure but I just want to make sure that we understand a couple points So we can get to recursives. They're often called with queries Depending on the database you come from because they start with a keyword with Most of us do cte's because they are more readable. That's kind of the thing that everyone talks about, right? um, and so Uh reference yeah, so that you're naming a query It's a virtual table essentially with a name that you can reference throughout your query Just note prior to postgres 12 It was they were what we called Materialized so kind of the query to be run it would be stored in memory memorized in some way So that anytime you reference that table it wasn't queried again Now in postgres 12 and above it ended up becoming inlined is what we call it We try to inline it which can help the query plan actually be better But that also impacted some people cte's So you can add the materialized keyword to have it take the old effect of materializing it first You can chain multiple of these together. You're gonna see that And again one of the things that some people don't know is you can actually name the output columns At the cte so it doesn't matter what the columns are named in your query If you want make sure they have a specific name you can do that above So this is what they look like right we have width. I name this query cte one Now the next query has access to any cte above it Right so I can name any table or cte above it and so this query currently only has access to the first cte Now if I add a third cte it technically has access it can reference either the other two Whatever you want to do one both none doesn't matter and then eventually you have to have a final query That at least references one of these cte's to get an output pretty simple right And one of the reasons we do this is again looking back This is a little bit further down the road for this puzzle that I was trying to solve with the Positions and the card numbers and other things. This is a little bit further into the puzzle And if you don't use cte's you probably do two sub-selects in some way to join it together because you're iterating That's just ugly like it's hard to read And then you like the select is actually way up top, but you're doing all these named alias functions It's just as annoying And so we switch this around a little bit We make two cte's and now notice how much some sort of the query is below This is why most of us do it it makes the query itself a lot easier to rationalize about However Readability is one of the primary reasons we cite It doesn't mean that it's more performant right so cte's can become less performant Particularly the more complex they get because you're asking the planner to basically plan a bunch of separate things And then you know put them together and you're not going to get the right row estimations all the time out of each cte This becomes annoying And so it's not always going to be performant. Honestly, it might be less a lot of the time But it's probably worth a hit for a number of the things you're trying to do All right, so cte's great so we can see examples, but then there's recursive cte's Who loves recursive cte's? I love when hands go up who has never used a recursive cte All right, it's there they They feel hard right, but hopefully they won't seem that way in a second So the sequel language is declarative And it's basically batch-based Right, which means There's no way to iterate The the query as we go you get the output And yes, you might iterate it through like a cross join seems that way Right, we're for every row here. We're iterating here, but it's not the same kind of thing as Looping over a data set That's where recursive cte's come in Before we had recursive cte's Sequel was not a turn-complete language But with recursive cte's we can now do complex calculations Which is kind of the marker for a turn-complete language and so When recursive cte's were added we're marked as a turn-complete language kind of cool And it really doesn't look it's it's it's a cte, right? We just did two things we added with recursive So we had to add the recursive keyword and that simply tells the planner One of the one or more of the cte's in this Chain of a query is going to theoretically refer back to itself And that becomes the looping looping mechanism. So in this case cte2 I'm referencing Both cte1 in my select statement. I you know, I didn't want to take too much space and I'm referencing Myself as the cte2 cte And this is going to cause this cte to first iterate and loop over the process that you set up The output of which Goes on to the rest of what's happening We often will hear these called hierarchical recursive or cte's because this is where they're most useful I want to recurse through something like a folder structure, which we've all done We've all seen And so you'll hear this called hierarchy, right? Whether it's Buildings and I used to work in Utility data, so we'd have buildings meters divisions, you know, all these things related and we need to correlate them up to some kind of Tree that we were going to work through So here's how cte's work Recursive cte's There's two parts to it And this is where I always have gotten messed up until I start using them more The first is what we call the initial query. It is a static query It only gets run once and it's the setup For the rest of the recursive cte The output of this query will determine the data types in the columns for this the output So in this case, I had this simple folder that this is assumed. This is the a table in my database I have three columns I selected out the name the parent folder in the size Where parent folder is not I only had one parent folder and so I get one row And I see that the name is folder a That output becomes the input to the next iteration Which I can then join on So when I refer to that's the files on disk is the table files on disk And I'm joining to myself. I called the cte files I'm joining the output of the previous iteration one or many rows In this case, I'm joining it on the parent folder On the table to the name of the output from the previous iteration And that's how we start to recurse And so the output of this This is what the second iteration would look like at this point. I'm sorry. And then we union them all together Right, so we have to union that's that's a requirement for the recursive cte So we take the first one and never gets run again And every iteration takes the output and runs it through the second query And so iteration one I got the first row iteration two I got five rows because all of them had folder a as my parent I'll show you How this completes in the demo now recursion. This is a caution right recursion continues until it's Until it finishes until there's no output or you put a limit on it Well, if you don't put a limit on it and you don't know when it's going to end you end up in an infinite loop So you got to know that you're going to have an output or your cpu is going to go And everyone's unhappy So just make sure there's an end point or add one arbitrarily a where clause that you know is going to end the thing in some way Or a limit whatever that might be and then this is my caution I have like four or five talks like I love arrays. I love range types And I find for myself every time I learn something fun and new All of a sudden it becomes have you ever heard this the saying When all you have is a hammer everything looks like a nail That becomes these tools for me. I'm like, oh, I love recursive ctes now Every puzzle I'm going to find a way to use recursive ctes And then it's like the stupidest thing and I just needed a window function and it would have been solved So just be cautious once you get used to them. You're like, this is really fun Don't use it for everything So all right, let's get to a demo This is mostly for the recording So I'm going to hopefully see if I can put this here it will at least get recorded So I can show you a couple of these things So I just want to run through top to bottom. We have 15 minutes This will take about 10 now time for a few questions I can increase the font just a little bit more So I'm going to create this table. This is puzzle one five cards space in a bit, right? So I just want you to see in action Now in this case, I'm creating the table I'm cheating here. I'm not using copy because I just wanted to get it done quickly I didn't want to have to go out to a terminal for you, whatever I'm just doing a multi-line value insert I get those five sample rows in great And this is all it looks like All right Now I told you about dollar quoting This just means it's a literal, right? So I'm just selecting the text inside of it whatever that string literal is The other cool thing you might not know about is this just can be they just have to match So you can do something like that and it will still work So you can identify your beginning and ending Right so that it's the two dollar signs You can actually put a word in the middle as some kind of tag kind of cool, right? I don't know why I'm taking this out because it's taking time And then again, I often use this in procedures and functions when I need to do a large A long format of some sort like if I'm generating a query Inside of a function in some way that gets hard. I want it to be I write my query and then I want to replace the various things This becomes really helpful. I put two dollar signs around my string And then I can just keep it mentally it works for me and I'm just formatting the literal All right, so this is what I was saying, you know, I get the output Whether I often start like this I take a select statement run it through something like split to table so I get rows of data I can work with Now to use that I either have to put it in a cte or I start to do a bunch of sub-selects And that's annoying no one wants to do that. So I often just put it in a cte So that I can start to look at this like everything below the cte Is where I'm starting to think about how I'm going to transform this data, right? This is just my sample data. It was a one-off. I don't have to create any schema And now I'm like, okay, if this was a real table, I'd say slick star from that table Looks exactly the same. Sorry. I'm using dbeaver If you know the tool and I just have to hit control enter So I'm not hitting buttons that will execute the query that I'm in. I forgot to say that Now I showed you earlier. We could do something like this where I could do sub-select to get the row number And that works I get the row number But If I do, um, sorry, this is in the cte who cares about that But I can do the same thing with that ordinality I still get the same thing out Lots of ways to deal with it. I'm using that sample data and then I can start to iterate below So in this case, we're just going to do the first part Right. I now have this fake table of data Called December 7 underscore dt. I'm calling it drive table. No kind of isn't And I need to get the parts out, right? I need the id that I created. That's the hand Right. So each hand of cards And then I'm taking the first one. I'm calling it the cards. I'm taking the second one calling it the bid And now I have three columns I took that string used a couple functions And I split it apart. Cool Now if I have the data internally, which I do I can now say, oh, well that just would have been the exact same thing but I'm selecting from the table itself All right, so you can see that I took the sample data I got the maybe the initial query And now I could import the data do the select and it just works because I know my sample matches the other input So now I have December 7th. I'm not going to keep using the temporary stuff And in this case, we need to do second parts. So now I don't need to just know the hand of cards Later in the puzzle, I actually need to know the order of the cards So how do I turn a string into something tabular? I pivot it, right? I just break each of those things apart and I pivot it so I can identify each individual card within the hand So I'm going to have two IDs in some way So the first one is the ID from my table that indicates the hand from all of these plays And then I have the card that comes from my split to table down here So I'm saying of the string Split it into two parts take the first part That's my five characters for the hand I'm adding with ordinality Because I'm splitting it to a table. So look at what happens. So first you see I have hand one two three four five down below Look at hand one When I take this and I split it a second time And I add ordinality to it All right, so I'm starting to chain these things together and I can do that because I cross join That string to table it can reference stuff from the actual table data Oops not that I had the highlight and my apologies So now I have the first hand Notice I kept that ID column And then I have five individual cards in that hand numbered one to five This is just building on top of each other kind of cool, right? And and then you can just keep iterating through this now again what I showed you earlier is I can clean up that top Query a little bit I can just move more functions if they're set returning into the from clause Is this the most performant thing to do? No, probably not But it might help you You know come to the solution understand the way these things are working together And so I just simply moved this split part down below And now I just reference it up top Again, I am hitting control enter to see the exact same data set that comes out Does that make sense? All right, let me show you one really fun thing. This is the wordle thing really quickly the exact same principle We have guesses Right, you can do up to five guesses in a wordle puzzle One two three four five and then I need to know each individual letter within the guess So here's what the string look like So again, I'm taking an example making a string literal out out of it And I'm using a different function here. It's called reg X matches That's going to get out The the the data as a an array So notice the guess is an array And I kept with the ordinality the number of guesses in this case they gave five three guesses Now I can take that output. I put it in a CTE just to make it simpler But now I can take that CTE Called wordle score I can cross join it to another reg X matches So the output of the CTE goes into the next function And there I say hey the first item of the Array, which there is only one item in the array And now I'm going to look for any existence of one of these Emojis and split it apart and do with ordinality So now I have each guess Each letter in the guess the order they were Okay, that's cool. So I'm turning this stuff into tabular data And then to finish all out I can now take that tabular data in my select query and I went from emojis to With each guess how many correct letters there were how many partial guesses were correct and how many incorrect And I get down to by the third guess this person had guessed everything correctly Do that across lots of queries and I have this really cool tabular data. I can With millions of wordle stuff is pretty fun All right, so um, I just want to show this part and then I'll stop here all of this stuff is there I try and do my best at putting a lot of comments So you know what's happening if you were to take this example And I show you where to get the data all of that stuff I just want to quickly go over recursive queries again So that you can see it and I'll have one or two minutes for some questions And always feel free to reach out to me So this is the most simple example, right? I set up the initial iteration by saying select one That's going to be the input to the next iteration So value was one plus one. That's going to get me two I'm going to keep doing that now the next input is going to say two Plus one is three and I'm going to do that until value Is less than 10 because if I put 10 in It's going to do one more iteration and get me 11. I don't want that you do it all kinds of ways I keep highlighting stuff my apologies And we just counted one to ten That's cool Now you can do with something at the Fibonacci sequence. It's another one. I like to show right we all do that We all learned it with agile, right? and uh, hey, what is Fibonacci to five five rows so I started with This is my initial query This is what it started as right the zero was to start the First value is eventually going to be t