ificantly higher than x because then the planning is going to go haywire and running analyze on the tables periodically helps ensure that the database statistics are kept up to date and in order to help the planner calculate the costs accurately and this is the cost that you know we were referring to as an arbitrarily or a relative relative number the database parameter the configuration parameters are there for tuning you can adjust these parameters based on your workload and the hardware that is available to you and we are going to do a little bit of a deeper dive into the database parameters in just a little bit so don't worry if you don't yet understand what this means over here now moving on another thing to watch out for a very simple concept is watch out for locks as you program and as you write the sequel code the simplest example that I could come up with let's say there's a there's with one session that starts off a transaction it updates the table foo and it sets the value of id one two and three and then commits it session two is also updating that same table and also tries to update id one now while session one is taking place it is it has locked id one the row with id one because it has updated it and till till the commit happens it will keep an exclusive lock on that row and session number two is going to wait for that lock to be released before it can execute its statement now you want to make sure that such exclusive locks are only used when they're absolutely required and this is especially critical for long running transactions because a long running transaction could hold on to an exclusive lock for a long time and which will prevent other processes from updating that same table thereby impacting the performance of your database so key takeaways from sql and query optimizations monitor your queries make sure you're on top of them that you understand which your longest running queries are what your most frequently run queries are analyze how those queries are being executed to be aware of any anomaly anything that looks wacky or off and code in a way that avoids locks now the next point that i'd like to discuss are some performance features that postgres offers and mostly i would want to talk about indexes over here now postgres offers different types of indexes that you can leverage based on your use case based on how you want to query the data that is in the table the default index if you don't specify anything the default index is the binary tree index and you know as is evident from its name it structures the data in the shape of a binary tree and can be used for for equality access it can be used for range it can be used for greater than less than etc etc hash indexes are focused on equality checks so essentially it stores the hash of the key and it can do a very fast lookup for an equality check now a hash index is really bad for range comparisons which is where the b3 index shines so this is use case dependent a hash index should be used when you need fast equality access a composite index is multiple columns combined which is used when you have queries that frequently access data where the conditional clause is dependent on multiple columns so if you're where has not one but two or more columns involved in it having a composite index on all of those columns will help that query retrieve data faster a partial index is not just on the column but there's a conditional statement or a conditional expression that that it evaluates it makes the index smaller in size it doesn't index the entire data it only indexes the data that satisfied the condition of the expression a covering index is used well there's a very slight difference between a covering index and a composite composite index a covering index also captures multiple columns but this is used when you don't want your queries to hit the table you only want your queries to retrieve data from the index the way it's defined is you define an index on a particular column and you use the include keyword for some of the other columns that you usually retrieve data from as well when you query a conditional clause for column a so let's say there's column a where the index is being implemented and you as a routine also call column b and c in the select queries if you include include b and c in your covering index what postgres will help you do is when you query that particular table using the index it will retrieve data from the index not from the table thereby saving you time and giving you better performance brand index short for block range index essentially what it does is it stores the minimum and the maximum values from the page size and not the entire data so essentially for very large data which is spread across multiple pages it will help you save a lot of disk space it only captures the minimum and maximum values on a single page and then it can zero in very very quickly into a value that you might have queried now with indexes as well it's not a one size fits all so be careful as you use your indexes indexes do take up space and they will cause bloat if you overuse them so please don't overuse them and a few conditions where indexes are probably not recommended if you need most of your data anyways postgres will end up doing a sequential scan on your table and the index is just going to be not utilized and wasted you're wasting disk space if you have a very heavy write or update workload and very few reads index is not going to be used and each time you insert new data or update data in your table the index will need to be updated as well which means that you're actually slowing down your database while not leveraging the index functionality so for write heavy workloads update heavy workloads and workloads that have very seldom reads indexing is not a good idea we've already talked about data bloat if your table is too small and you know a sequential scan will just fetch it very very quickly no point in inserting an index in it and a few highlights and you know these are just examples there are many such examples in postgres performance features that just work you really don't need to do anything to invoke these features or make them work postgres will is intelligent enough to for example plan for parallel query execution leveraging the multiple cores that are available to it in if it can it will avoid index changes using heap only tuples if if you are changing row that is or in a way that is not impacting the index or that's not updating the index column it will use heap only tables tuples to to avoid the index updates and thereby improving the performance uh incremental sort if data is semi sorted postgres will not just start from scratch and sort the entire data it will intelligently leverage the semi sorted nature of the data and help sort it faster auto vacuum i know at least yesterday there were a couple of talks that were focused on mvcc and auto vacuum so as as updates occur and as dead tuples increase in in the table the auto vacuum process will kick in and automatically clean up the dead tuples thereby saving you table bloat and getting rid of all you know the dead tuples now all of these things happen automatically all all postgres asks you to do is to let it do its job and it will do it so you don't need to tinker with anything you know don't need to invoke anything yes so yes but you can optimize them you don't need to tinker with them for this to kick in even if you don't optimize them postgres will still execute queries in parallel with the default values so so yeah optimization definitely i'll come to optimization in in the next section there's lots of optimizations that you can do i i completely agree there are parameters that will help uh there are parameters for uh for example auto vacuum as well right that can help optimize how uh auto vacuum is done but the point over here is that you can go into that uh if you know what you're doing which of course you do hurry but not many people do um so key to key takeaways from the performance features that you can leverage uh indexes are a powerful ally but please don't overuse them and let postgres do its job don't tinker with it too much it will for the most part very intelligently figure out the best way to execute queries now the next point is architectural improvements that you could possibly make in your in your database and the first aspect of it is load balancing and what load balancing does is essentially it distribute queries across multiple nodes or servers in order to make sure that one node is not um is not over utilized or is not burdened with all of the all of the queries and all of the transactions that are taking place the benefits are it prevents the single server to become a bottleneck it can facilitate horizontal scaling and well the byproduct is while we're working on performance but the byproduct also becomes the system overall becomes resilient to disasters because now you have multiple copies of the data and in case one of these nodes break down you can fail over to the other node graphically speaking this is roughly uh what it looks like so on your left over here is a single server that is serving the application which is writing and reading from it and that same database is serving uh the reporting and analytics for the business now all of the the right and the read workload from the application and from reporting and and the analytics have to be handled by this one node and the node can be overwhelmed um we had a very interesting example recently again you know this is this is back from my my hometown I was working with a team that is monitoring well a little bit of context actually before that so Pakistan unfortunately is one of the very few countries left in the world that still has is is endangered by by the polio disease the crippling disease and there's a massive effort ongoing in the country to uh vaccinate and immunize uh you know kids against the disease and there are campaigns that are run and we were working with the the organization that is tracking the uh administration of those doses to all the kids in the entire country and what they were doing is that they were using the cell phones of all the workers that were going door to door all over the country and tracking their their location pings storing their lat long and creating heat maps in a central location to to determine where the workers were actually going and what parts of the country they might have missed right so lots of data streaming in um and a very nice dashboard built up to create a nice decision-making environment and they came to and and well that the entire data was being stored in Postgres now they came to us saying that when when they start running analytics on the data that's coming in the rendering on the screen for the real-time pink that are coming in from the field that slows down significantly right um I actually went to the into their office trying to you know determine uh what what's going on uh now this was a team that uh you know had really good engineers uh good skills within the team uh but one thing that I found surprising was that all of these transactions all of this processing was being done on a single node on a single server so lat long pings from all over the country from all the workers going door to door coming into the same server uh all analytics being run on the same server um and when the management wanted to run some reports uh the pings coming in the the database would just refuse to ingest them and and this is what what we recommended what we said was uh let's let's load balance your database so you'll have one node that is dedicated to ingesting the data and we can set up replication to two standbys and the application that is sending those pings whenever it needs to read the data can pick it up from stand by one and you can run your reports on stand by two and you know just just splitting that load helped uh you know um decrease the pressure on the single server and then you know they just started everything just started working fine so load balancing uh can be a very uh interesting um uh trick or a very simple way to elevate some of the pressure uh or sorry reduce some of the pressure that one node might be feeling if uh your workload is both read and write and there are different parts of the business that are doing different kinds of stuff with the same database you might want to split the database up to serve those different parts of your business um a simple example that I you know came up with actually this was a quick hack into my own laptop using pgbench uh you just run three nodes on the same laptop versus you know one node on the same laptop uh laptop for 60 seconds run a select only pgbench run and even even on this very very simplistic measure on the same hardware we could see performance gains where the tps was about 371 for a single node and it went up to 485 for three nodes again now the hardware is the same it it is running on the same hardware the resources for three nodes combined over here and one node over here was the same and even then Postgres was able to perform about 30 percent better when the load was distributed so this is you know one one easy way to improve performance load balance it another way to or another aspect to look at is partitioning and what partitioning does it it divides large tables into smaller and more manageable pieces and the benefit that you can get is that queries are going to perform better maintenance is easier and indexes also perform better and are smaller an example again you know visually speaking uh let's say you've got an application that writes time series data and you know you have data for the entire year one way to possibly partition it is by quarter which is you know what is being done over here so think about you know these these four partitions as q the first quarter second and third and fourth quarter now if you run a very simple query that is you know select start from through where month is august what the application does is it queries this database right here and it needs to scan the table and pick up the data from from august whereas if and and this of course you know can be a very huge table whereas if you use partitions when you run that same query you can zero in on the third quarter and you are now dealing with a much smaller table much smaller partition over here instead of the larger table and you will significantly improve the performance of your database and of your queries key takeaways from architectural improvements well it's just one takeaway don't overload a single node you know if you if the the node is being overloaded split it up scale it horizontally split up your your database the last point is parameter tuning now we have hinted at this earlier in the presentation there are configuration parameters that you have access to and you can change them in in the database for the most part however for the most part defaults tend to be good enough um and very broadly speaking you can categorize the available configuration parameters uh into allocation um how much memory or resources uh get allocated to the database uh and defining of costs to help the query planner now three examples of easily tuned uh allocation database parameters the first one is shared buffers and what shared buffers define is the amount of cash that uh frequently accessed data can use in in the system uh the default is set to 128 mb usually the recommendation is to set it somewhere between 25 to 40 of available system memory you do want other processes to be able to access the memory as well but uh you know um this the shared buffers is going to define how much cash memory the database is going to use wall buffers write ahead logs again you know this is something that other people have talked about uh the write ahead logs so i won't get into a whole lot of detail about what these are but essentially before data is written to disk it is in memory in buffers and the amount of data that the database is going to hold in memory before writing to disk is defined by wall buffers uh default is set as 3 percent of shared buffers um and in high concurrency workloads uh raising the value up to 16 megabytes can improve performance work memory is the memory that is available for a query operation so uh this is uh a single operation or single atomic operation within a query uh or the memory allocated to a single atomic operation within a query the default is 4 megabytes and uh with very high uh IO activity and increase in work mem can probably help because you know much of that can be done in memory but you have to be careful while setting it too high a value can flood the memory with lots of uh lots of queries because every parallel transaction that is taking place is allocated this amount of memory so 4 4 megabytes is the um is the default and you can set it a little higher based on the amount of resources available in your system uh three examples of parameters that define the costs for query planner the first one is a cpu double cost and essentially it defines the cost of processing a single row of data um and this includes you know the wares and joints involved the default is 0.01 and a lower value indicates that um the cost of processing a row is very very low which encourages the process uh the the query planner to process more rows together and uh it's helpful for for you know IO bound operations where the bottleneck is actually disk IO not cpu so cpu is is used more in case the cpu double cost is low in case the cpu double cost is high uh the query planner tries to use less rows in memory and it it it tends to be more IO oriented so if your system or if your setup is cpu bound if cpu is the is the resource that you're lacking you can set this higher raise the cost so the planner tends to um uh process uh low rows less number of rows and uh help with the operations random page cost defines the cost of accessing a random value within the database the uh default is four a lower value implies that random access is cheap which is good for ssds and uh it encourages index scans if you have a magnetic drive a spin drive uh a higher a higher value is probably more suited because it is more difficult to access random values in a in a in a magnetic disk and uh it thereby encourages sequential scans which are easier to do on a spinning disk effective cache size it's not exactly a cost parameter but it's worth mentioning because this is what the query planner expects uh in terms of the cache size to be available to it and it combines shared buffers and os cache together the default value is four uh gb uh but the point to note over here is that this is not an allocation this is an indicator to to the query planner as to the amount of uh cache size that is available to it so that it can plan its queries accordingly um higher values of course imply that there's more data that can be put in cache um and that encourages index scan because you can very quickly access it and lower values imply that there's less data in cache so you might need to go to the disk um and it encourages sequential scans uh some takeaways uh from parameter tuning you want to tweak your parameters based on not only your hardware but also your workload so you want to know what resources are available to your database and you also want to know what to expect in terms of the type of workload your database is going to be processing and you will need to experiment a little bit especially with the cost parameters you will need to experiment a little bit before you can fully optimize uh your database which also makes parameter setting a very good candidate for AI based tuning and there are you know um at least a couple of startups that are working focused on Postgres on AI based tuning and I find these those projects very very interesting because uh this is actually something that um as consultants we use up a lot of time on and I think that this can be automated so it's good that you know a few people are are working on it uh pretty much to you know moving to the conclusion database performance there is no magic button don't look for it it involves a lot of variables uh and some of them are fairly easy to tackle you don't need very deep database expertise to be able to tackle them tweak those uh those areas leverage your database to the fullest and only once the database has been leveraged to the fullest should you think about using your credit card as you scale brings me to the end the QR code is my LinkedIn profile connect with me on LinkedIn I'll be happy to connect and talk about Postgres and in case you are running into problems you can just call us and of course keep calm any questions questions to enhance my understanding of the difference between a composite and covering index um you said the covering index uh is useful because you've added these rows and now you can retrieve or columns and now you can retrieve those columns from the index rather than the table in a composite in a composite index can you also do that when if you know if I have three columns in my composite index and those are the only three I'm querying for do I get those out of the index in that case too so the the you can see that that the composite index um does everything that a covering index does and a little more so with the composite index um each of the columns are sorted in order a covering index will only sort by one column and include the other uh columns so yes you can you can use competent index sorry um in in in the index sorry I don't quite understand that point okay okay yeah right right yeah yeah yeah yeah exactly yeah right so basically composite allows you to filter by each of the column right and the covering index is filtering by one column and the other data just happens to be in the index as well um what is the overhead difference like there like how you know is if I'm only worried about two columns is it is it worth it to use a covering index or or does that really only come into play if it's 10 columns out of 100 column table I think it will very largely depend on your table on the size of your data how it's structured I think you will need to experiment with it a little bit to understand how much benefit you are getting right there is benefit and the benefit is because uh the columns are not sorted or not indexed in in the covering index um and whether that's worth it or not depends on the use case okay any more questions um you said that uh the uh index and when you when you need to uh access all the data from the table uh and an index is more or less useless so my understanding was you use was might be wrong that the index is used to to look up the position in the table uh to get all the data uh is that different in postgres so um I don't recall the exact percentage but um if you if you query uh data from a from a table that uh is is seeking to retrieve more data than x percentage of the total size of the table like postgres query planner will actually resort to a sequential scan even if the column is indexed okay because that's cheaper to execute you're you're retrieving that much data anyways and if your use case is such that you retrieve a lot of data or a major portion of the data from the table anyways all the time then indexing that column you're just wasting space you're not really using it postgres will uh the query planner will go for a sequential scan in that case anyways so you're just wasting space and can can you see that with the with the um on the beginning you had this uh cost uh measurement can you can you see it with with this yes yes so what you what you will observe is um actually it's a bit um I I don't have the the the slides for it or the you know the database setup for it but it's a very quick and easy experiment that you can run um you know create a table um any table and uh put a lot of data in it indexed by one column run a query that retrieves let's say two percent of the data and run you know an explained plan on it and then run another query uh with just the where clause change such that it retrieves let's say 90 percent of the data and run the explained plan right in the first instance the query planner will show you that it does uh you know the um it looks up the index it does the index scan and in the second instance even though the column is index it will run a sequential scan it will show you there okay thank you and are the slides maybe available online uh I think so yes I think I think that you know once once all of this is wrapped up uh the scale uh this you know scale organizers will make it available on the website okay thank you and the presentation is also available on youtube on scales youtube channel uh any more questions we have a question this is sort of a general question let's say you have uh postgres installed from ubuntu packages right out of the box can you speak a little louder please do uh general question let's say you have ubuntu installed from uh sorry you have postgres installed from ubuntu packages right out of the box on a production server what are you reaching for and and it's a server full ssds for storage what are you reaching for first as far as just general performance improvements to make that perform better right out of the box so right out of the box I think that some of the things that you can do is understand uh what hardware resources are available right what what memory you want to allocate to the database to use so right out out of the box you want to understand uh how many cores you can use for parallel workers uh how much memory you want to allocate to shared buffers to to wall buffers and uh let's say you know you've got a ton of memory available and yet you're allocating only I don't know 12 gigabytes to uh to shared buffers that would be an under utilization of resources so those are some of the things that you can do you're straight out the bat cool thank you okay uh any more questions okay one more got another one um partitioning is that something you can only use when you sort of have a um set range for where the data that you're partitioning on will fall in like you said with a year we divide into quarters or is there a way to set up a table to be partitioned even as I am continually writing new data to it I have a log table say and you know I wanted to create a 2025 q1 when it gets to be that time is that a thing that works yes it does it does so essentially what you do is you define a rule on how the data is partitioned and you can define that rule for the data to be routed to the right partition as you insert it as well so that it can be a dynamic rule right so it can be independent of the year in this particular example it can be independent of the year so all months that are January through March will go to q1 independent of the year so as you write data it will go to the right partition thank you okay I'm afraid we're out of time but thank you okay thank you very much oh I did that yesterday oh okay thank you test test is it working now right sound test sound test if you are sitting in the back can you hear me on the speakers right now we give it 30 more seconds or get started pretty soon how's everyone doing home stretch of the postgres track right all right welcome everyone to my talk titled uh testing your postgres backups um a practical guide uh my name is nick mayer and uh let's get started with uh welcome to pasadena it's a pretty uh it's a pretty fun place um it's been about six years or so since i've lived here full time but i do enjoy coming back here quite a bit um you know i can't point to any specific reason why i'd say it's probably mostly just uh nostalgia speaking of which you know nostalgia it has this way of being this contagious social phenomenon like especially nostalgia around like the roman empire that whether it's on social media or similar um you know alongside it there tends to go this narrative that uh we just don't build things like we used to uh we had this you know there's this notion that there's this ancient knowledge that we have lost that you know we have these we have these ancient structures with roman concrete uh roman roads and yet somehow engineers nowadays you know we can't build things that last more than a few decades and yet this stuff has lasted several millennia so what's up with that and perhaps some of you are uh you know maybe you just walked in or maybe you tuned out for a moment and maybe some of you were wondering okay wait what is going on here who is this person who's talking in front of me right now you know did he introduce himself where were his title slides i thought this was supposed to be a talk about post-dress backups and if you're in that boat don't worry um i promise you you're in the right talk uh we'll get to all of that shortly i just wanted to start out with this analogy in order to explain to you all you know upfront what this talk is not going to be in this talk i will not tell you or give you some uh conception that i know everything there is to know about backups and that i'm just an expert you know an ab the end all be all expert at backups uh because that's not true i am not going to tell you that uh my employer or i have you know formulated a perfect backup testing strategy that will leave you fully confident risk-free and i'm certainly not going to tell you that we've never made mistakes uh and you know the reason i bring up this analogy of the roman empire in the first place is that if we want to if we want to replicate success you know in order to do that we can't just look at examples of what people have done and found success we don't know necessarily whether that success was just due to luck we don't know you know what they tried that didn't work and often you know a common theme i found uh certainly like in my experience with post-dress backups uh but you know i think it applies pretty generally is that a lot of the time what we learn the most from is failure from things that don't work out and i'm hoping to give you all sort of a taste of like both sides of that coin uh from my personal experience uh speaking of which you know you know some uh introductions are in order uh so who do i work for uh academia.edu who are we uh we are an organization whose mission is to ensure that every paper ever written is available on the internet available there to read for free online uh more broadly what our goal is is to accelerate the world's research and you know to go into some of our successes if you will or some stats at least pointing towards our progress towards those goals so far we have about 47 million papers on our site that have been uploaded depending on how you do the rounding uh let's say that we think there's about a hundred million papers that have ever been written um in the academic world it's constantly moving target so maybe we're about like 50 percent of the way there um on the on the theme of accelerating the world's research there's a common story in academic publishing where someone will publish a paper and it gets read maybe by three people total give or take one read by reviewer number one another read by reviewer number two you know you probably see where this is going reviewer number three is the final person to ever glance at that paper and that's just the way it is and at least let me put it this way there's a lot of research out there that deserves better than that and from the perspective of the world you know if this is impactful research that can make a difference in people's lives and in our understanding of the world all the world deserves better than that and you know to that point uh we we have been addressing that problem to the tune of 20 million paper recommendations per day making sure it actually gets read by the right people okay but this is a postgres talk i should probably give you a little bit of context on what we're working with particularly this is a talk about backups maybe i should answer the question up front what you know what is our scale what size of data are we backing up uh because this probably has some implications for you know if you are at a different scale than us whether that's three orders of magnitude larger or three orders of magnitude smaller maybe you'll need to make some adjustments we have about a hundred terabytes of data if i round super aggressively distributed across 15 independent different clusters entirely on aws and no matter how you slice it it's about half and half between aurora rds and our older self-managed ec2 instance clusters um you know one for a variety of reasons ha never-ending need for high uh you know high read throughput in our workloads we have tons of read-only replicas um and you know getting to like the team size since i think this is pretty relevant too this is a practical guide it's about balancing all the things you want to do with all the things you actually can achieve in practice we have maybe about 50 engineers total across the entire organization divide that in 10 for people more on the infrastructure side of things speaking of which who am i uh hi my name is nick mire um i have my hub link there we have to go even earlier than the romans to the greeks for aristocrates so imagine you take aristotle and socrates and mash them together into aristocrates sounds a little uh pretentious too like an aristocrat or something um but yeah uh who am i uh at academia i'm currently the team lead of our platform engineering team my areas of focus uh on that team have included uh the developer experience uh the kind of the interface between our application layer where the rest of the develop engineering organization works and our infrastructure layer um and because you know as i mentioned before we're not like the largest uh engineering team in the world uh there are some interesting staffing tradeoffs which result in the data layer and postgres in particular being parts of my uh you know daily uh daily work and in particular uh speaking of postgres and postgres backups uh allow me to introduce you to academia's old postgres backup solution um you may have heard the advice at some point uh in your life never roll your own backup system um allow me to introduce you to a ruby script all right it's not as bad as it sounds it was a ruby script wrapping pg base backup if you've heard of that tool um in postgres uh but even so i would say that like there there is more to worry about than just the absolute lowest level it's still it's still i'd say not the greatest idea so you know one thing that is really nice about this is it was a great way to learn about backups and all the things that would go you know the all the things that could go wrong with them uh i learned a lot having to maintain the script and ultimately you know replacing it with pg backrest uh if you've heard of that tool uh much nicer inner much nicer a lot of things than that tool was um so you know a great learning experience but otherwise a bad idea i would not recommend this um a little bit on the note of this being a practical guide i think that as as an engineering organization who has that has made mistakes and as an engineering organization that doesn't have like the staffing to do everything that we'd want to do um my goal is to cut out a lot of the noise because a lot of times what is just as important as what we choose to do is what we choose not to do because it's not worth it or it just doesn't pass that trade-off muster um but you know i don't want to give you the impression that we've only ever made mistakes i do think that one reason that why one reason why we're still here today with our data you know more or less mostly intact um as it was intended to be is because we stumbled almost inadvertently onto a pretty solid backup testing strategy um i don't think that we were particularly intentional about how we came about this i think that we ended up stumbling upon it due to mostly business reasons um and i'd love to share that with you today so to sum it in short although i'm not going to be able to teach you all in 45 minutes give or take all the things i learned from having to maintain that ruby script and fix things when things were broken experience may be the best teacher hopefully this talk is going to be a slightly more cost-effective teacher for all of you so that you don't need to go through all those stressful moments yourselves firsthand um okay so i think that it would be useful to have a little bit of structure for this talk just so i don't go on more tangents about the roman empire history or similar things like that so i'm going to flip the order still a little bit of the order of like what i talk about here so i'm going to actually start with the why like why do we have backups that that sounds like a pretty that sounds like a question with a pretty obvious answer but you know if we were to formalize like what are the what what are the problems we're actually trying to address more rigorously with backups uh then after that i'm going to get into the how starting with you know what what is this strategy that i've alluded to like how do we test them uh but also how do we set measurable goals that can be quantified around backups how do we monitor them uh you know part one of that is let's get enough context on how backups work so we know what to monitor and then how do we actually do that uh so let us get started with this thought experiment of what could go wrong so let's say we have let's call it a high availability setup of some sort um you know we don't have all the information here but just trust me for now we have you know a writer node uh we have some read-only replicas following it uh we've even thought a little bit about eliminating some obvious single points of failure that there might be in a setup like this such as for instance uh you know to use like amazon cloud regions you split up the regions so that they're not all in one region um you know side note we don't actually do this this was a little on the expensive side for us with data transfer fees and such but you know let's like make the let's really steel man this example of this several node setup where suppose we don't have any backups yet um okay so maybe we think we've addressed like what if all nodes go down we don't think that's likely because the world has bigger problems if us west two and us east one are both down concurrently uh you know a question we might ask is what if some nodes go down does the application know how to handle that are there errors do things work have we tested it have we thought about it um but you know maybe like a bigger point of concern i would bring up with this is our good old friend uh drop table users or delete from users uh what if that gets run in the wrong environment okay so maybe some of you were thinking like okay this is this is a problem that other people would face at other organizations you know we have stricter controls in place uh then we we have strict enough controls in place that like there's just no way a dev would think they're connected in one environment but actually be connected in another but you know the question i'd pose to you is do you do you really trust your automation that much is it like you know seriously what what is what is the control in place there like at a higher conceptual level like is it is there someplace somewhere where a string might just get mixed up one place and this can get routed to the wrong location so you know i i've seen some pretty crazy stuff that i'm not going to get into uh but you know i think that like we at the very least if we care enough about our data to have backups then we should care enough about our data to want it to be you know we we should we should want some safeguards or some some options or recourse if this were to happen and the problem with this setup is that uh this this dml just replicates immediately to these replicas under our current setup uh maybe some of you are thinking okay but like they're we may or may not be aware that like there's a way to have delayed replicas with post-dress maybe we can put a six hour delay on just one just in case i feel like that's kind of fallen out of favor in the modern day you know because like what if you do what if we don't notice for one millisecond after that amount of time you know i've definitely seen cases where there's been data loss on systems other than post-dress let's just leave it at that um that we didn't notice for a day and just had to recalculate um okay so all right all right you've convinced me or i've convinced myself i've convinced you hopefully we should have backups i'm not going to define exactly what that means yet or go into the details of how that works but there's some notion how let's just say we're going to be sending some sort of copy of what's on post-dress uh could be from the writer maybe from something else to s3 just to keep the example within amazon cloud if you you know if that doesn't apply to your organization just kind of translate in your head whether that's a hard drive on some other computer somewhere else or an equivalent for some other cloud um but you know we're still left with this question what do we actually do if you know let's say a disaster does happen we you know we're supposedly prepared we have a backup a node goes down uh what what actually happens at this point and that brings me to you know my first major point i'd like to make here which is that we don't care about backups we do not because we care about restores not backups um with that i'm going to let that simmer a bit as i take a sip of water so what what is our actual goal like what is our purpose with taking backups because if they're right only if they're just this right only thing that we never use then we're just wasting money on them probably um i'm going to say that what we want with backups is there is some point in time where the data layer was in some state and we want the capability to be able to restore our postgres layer to whatever that state was um you know and we furthermore we want it to be sufficiently decorrelated with other with whatever is running postgres that if postgres were to completely disappear we would still expect the backup to exist we'd still expect it to be usable um so you know maybe a question we have is what exactly are we restoring uh we can have different goals here for instance we might want the capability to restore just anything that was ever written or you know to the greatest descent possible targeting that as our north star so to speak or maybe there's some other use cases for instance we need to audit something that happened a week ago uh we need to for some compliance reason look at something look at like some row that was present in some table a month ago but has since been deleted um so maybe like in the past 30 to 60 days we have reasons to look at it other reasons might be like corruption like there's some problem with our data now but we're pretty confident it was okay in the past it's always good to like have heaven as an option the ability to at least like peek at it um another thing i'd mention is restores need to be fast enough to be useful uh i'm not going to define exactly what that means in clock time because that is obviously a pretty that's that's pretty dependent on like what your workload is what your business requirements are what's you know what your data size is uh i would not hold a 15 terabyte database to the same standard as you know a 20 gigabyte database but generally speaking like you know with most of our workloads like bringing it back to kind of like what do we think how do we think of this at academia we want to be able to replace like a node that fails within hours not days all right so we went over what might go wrong if we don't have any backups um let's say we have backups though and let's say that we are appropriately concerned with restores um allow me to introduce you to the concept of schrodinger's backup which we may be familiar with um or maybe we're familiar with the cat uh this isn't schrodinger's cat this is schrodinger's backup uh schrodinger's backup a little bit similarly to the cat states that the condition of any backup is unknown until we restore is attempted now so okay i i don't i don't know if like this forum spotlight on it series number 2 12 in 2013 i don't know if that was actually the first uh reference of this i'm i'm a little bit skeptical it seems but you know it was the earliest reference i was able to find on the internet um but you know furthermore beyond that maybe maybe some of us are thinking that's a little bit this is a little bit too strict like maybe is it really is it really not possible to just kind of glance at it like let's say it's an s3 there's a file browser that you can use in your there's a file viewer you can just view in your browser you can take a look at it kind of by eye uh what are what are what are some specific reasons why that might not be enough like whether we implement other monitoring or whatnot um so let me to introduce you to some actual backup failures if you will that i have seen in prod all right well let's do the easy case first uh backups just weren't happening a lot of the time uh the way that backups are taken in postgres will you know there will be a component of it at least that looks kind of like cron um whether it's a cron job or something that's kind of isomorphic to that um it's probably like a problem we can all appreciate like maybe whatever's supposed to be scheduling some operation to happen just isn't actually happening for some reason or another um maybe that's easy enough to check like you just look that a file is actually in the destination and maybe it's there maybe it's not okay but leveling up a little bit to let's say a stage two enemy um you know stage two boss fight successful backups we have metadata or some exit status or some indication of success that is telling us that this backup was taken uh self reported by the system that's responsible for taking it of course but actually it's just an empty file uh what now what what are some sort of things that might cause this uh well this particular instance if you're familiar with the concept of pipe fail uh this ruby script that i alluded to earlier uh that our backup solution used to be effectively piped the output of pg base backup to standard out piped that into gzip then piped that to s3 you know more or less with a little bit of not exactly how it worked but conceptually that was pretty much it and if you don't remember to check like the exit status of every single step in that chain you end up with the last step receiving nothing because something earlier has failed the last step thinks that it succeeded it has successfully compressed the empty string and successfully sent it to its destination um and that's how you get an empty file uh that is supposedly a complete backup um and then level three boss fight a backup you know we definitely know it's not an empty file there's stuff there it looks pretty good but if you attempt to restore from it postgres it says it's starting three hours later it still says it's starting um i'm not going to like bore you with the details it never finished starting um i'll touch a little bit on what might cause that later so basically um basically the reason why the reason why i invoke the concept of schrodinger's backup is that if we really are worried about all three of these and i've seen all of these happen the only way that you catch every one of these is if you actually attempted you attempt to restore of a particular backup and then you see it work that is the only way that you can defeat that level three boss fight so to speak okay so some additional things that i am not going to talk about today because i only have 45 minutes maybe like half of that at this point uh ransomware data corruption insider threats a common theme along you know among these concepts is that i mentioned that we want to restore the data layer to what it once was uh this is kind of challenging the assumption that that's even something that we'd want to do like maybe maybe that state that has been persisted into our backups is actually bad in some way and we need some additional layers of defense beyond what we have with backups um i think of backups as i think of backups and like backup testing and restore testing i think of this as a solid foundation on which we can build later to a more thorough data security posture if you will but um you could have a full talk on each of these all right so how do we test restores um we're perhaps familiar with the swiss cheese model but if we're not just a or you know just to give a brief recap the idea is um in there we have these red arrows that are representing something bad happening we have these layers of swiss cheese sometimes the arrows pass through a hole in the cheese sometimes they get blocked think of every layer of swiss cheese here as some defensive measure we've put in place to prevent something bad from happening each individual control that we've put in place is imperfect some things we just know we'll get through but the idea is if we stack enough layers of cheese in front of the adversary hopefully if things are sufficiently decorrelated between layers hopefully nothing will actually get through every single one of them it just takes one to block it that's the idea all right so what is part one of this strategy for testing your restores um so motivated by this business need uh at academia where we need a lot of read capacity uh we churned through a lot of replicas as a result of that is every time you need a new replica you should use your backups uh so you know what this looks like is you restore from s3 and then importantly you know connect it to connect it to your cluster through streaming replication step two once that restore finishes um and then one nice thing about like this part of the strategy is when your backups break uh for one thing you'll notice at least as frequently as you need to bring up a new replica and secondly fixing this you know fixing the situation is actually going to be a priority it's not just going to end up on a jira backlog somewhere as a nice to fix um it'll actually be blocking you know a business need and then okay so part two of the strategy to layer on an additional layer of swiss cheese in our swiss cheese model um something that often comes up for us is we need prod realistic data when testing certain operations that might be an upgrade uh it might be something as simple as just like a big batch of you know data manipulation that someone needs to do uh we often have a need to have benchmarks for that how long is it going to take does it break things uh we need to qa it and in order to get prod realistic data in that staging environment uh we've needed to create tooling to bring up a copy of prod in staging and what i'm saying here is uh you should also use your backups for that uh this is similarly you know maybe you promote it right away so it might not stay a replica but effectively you know falls under that case one you need you need a new copy of some database you restore from your backups and the additional like marginal benefit this is adding on top of that previous point is that you're really confirming that you can restore from nothing you're confirming that there isn't some sort of pollution of data that is leaking in from that point where you connect it to the writer there's some guides online that are great when you're getting started but um kind of bypass this where you just take a base backup live from some node that's running and then you connect things up and to be honest we have that code path so this is we have that code path and you know it's a fear of mine sometimes that will accidentally revert to that and not notice that our backups are broken uh so this part too really like ensures that if you at least do this sometimes you're you're confirming that you haven't like flipped the boolean in that direction so to speak okay so everything else on top of this i'm going to say is an optimization you know another layer of swiss cheese so to speak that's not to say that optimizations are not important um i just think that like these are the two most important things in my opinion to do for testing your backups or restores rather if you're not doing it right now so maybe a question we have is how often how about at least once in the lifetime of your database um that's certainly a good start okay so you know to be to be like a little more serious here you know i'll give you the bad news i don't i don't think that there is a specific threshold after which you know you're bad you're in a bad place before and you're all good to go you know even even if you're testing every single backup like how do you know that something hasn't gone wrong with a backup you know technically you need to just continually be testing restores every millisecond to be confident uh but you know my my rough rule of thumb is like if if the integrity of your backups is keeping you up at night find wherever you are on the left hand side of this chart and level up at least by one going to the right hand side so if you've never done this before now is the time to do it once or if you've only done it once maybe now is the time to do it on some periodic interval like once a year or once a quarter once a month and so on uh the more you test it you know the more often you test it the more confidence you can have in your backups and furthermore you know although shortingers backup technically is always going to be is always going to be something that weighs on the back of our mind like we don't really know if this backup works if we don't attempt to use it if we don't attempt to restore from it the more frequently you test this you know the more frequently you test these things the more confident you can be that they work in general even if you don't literally test everyone and you know one last thing i want to say about this before moving on is that i've seen this you know i've seen the strategy deliver value even if you're just doing this at a frequency of roughly you know once a year or once every couple of years like you still you know the most important thing is just that you do this at least once you know you will still catch problems you will still fix them okay so a side note i noticed that we were i noted earlier that we were 50 percent on aurora 50 percent on ec2 instances um just to like kind of emphasize this uh on the on the theme of like one size does not fit all with the frequency since honestly like the honestly like the frequency at which you test these probably depends on your business needs probably depends on your budget for provisioning new instances uh we generally trust amazon to know what they're doing with backups it's managed for us you know provided that we've configured it correctly and so we we test you know we go through this procedure much less much less frequency much less uh frequently with managed databases than we do with our self-managed ec self-managed databases on ec2 instances uh just you know in a bayesian sense our prior is that we like we just trust our own stuff a little bit less than these managed databases okay so getting further moving further along into the how uh what goals should we set how do we quantify you know are all backups created equal no but how do we like weigh them against each other how do we say one backup is better than another how do we put that into numbers uh allow me to introduce to you the concepts of the recovery point objective and recovery time objective so recovery point objective is really answering the question like how much data loss is tolerable um we put a number on that and what the recovery time objective is answering is the question how long is tolerable until you know what is tolerable for the answer to the question how long until we're back like a disaster happens and we need to come back and i think these are best illustrated with timeline you know in graphical form with timelines uh so let's say uh we have a recovery point objective of four hours of data loss uh that's what's considered acceptable and suppose we decide that that means we should be taking backups let's assume for the sake of this that they act effectively as snapshots in one point in time every four hours um so we can see a problem with this pretty much right away in that let's say there's a disaster right now um you know out is starting right now at 2 a.m our last backup is at 10 p.m um we have data loss of four hours so it's not great that we're just barely achieving um that objective of four hours of data loss we also have an outage ongoing until this gets fixed so um illustrated on this graph this is us just barely achieving our recovery point objective of four hours yay us and the recovery time objective illustrated here is you know that end to end time starting at the moment that a disaster happens and we need to restore ending at the time where everything is back to normal albeit with some data loss up to what we defined as acceptable in a recovery point objective uh that is you know that is the recovery time we're achieving and suppose for sake of argument we're also just barely meeting a recovery time objective of four hours uh that's what this would look like here all right point in time recovery uh is this idea like we you know that idea i mentioned earlier about uh we need to audit something that happened in the past uh maybe there's some specific snapshot that we'd like to restore to whether it's friday sunday um or maybe some points in between is that even possible i'll touch on that a little bit later but that's the idea of point in time recovery is what points in what points in time in between when we're taking our backups can we restore to um so you know some specific numbers that we target let's let's take a hypothetical database that's roughly 15 terabytes large um may or may not be modeled after an actual one that we have a recovery point objective is we're targeting well we want to be able to restore everything with an allowance of several seconds to several minutes for um you know just to allow a small allowance i'll touch on like how that's possible later like is that even possible do you have to take backups every single second the answer is no uh recovery time objective of about like six hours or so to restore a copy of the data multiplied by three if we actually need to do a cold restore and with point in time recovery um as an option for one month and continuous points in between okay so maybe i should touch a little bit along along the lines of how backups work since i've gone a little bit out of order here um there are some concepts that should probably be introduced to for that recovery point objective i mentioned to make a little bit more sense um word of warning before we begin though is that this is not intended as a guide on how to roll your own backup system um as i mentioned earlier we were you know you could say that we were kind of rolling our own backup system we were wrapping a trusted you know trusted tooling at least in some contexts ruby scripts wrapping uh pg base backup but we're not going to go in depth enough to replicate um all of these other tools that we may have heard of such as pg backrest barman wall g etc um our goal really here is to know enough information that we know what to monitor we know how to test our restores we know how to monitor our restores so to touch on the difference between physical you know the physical regime and the the logical realm uh so this you know applies not just to backups but on some other levels as well let's let's define like what is logical um as far as the data is concerned as like what will be seen what is the reality that is seen by sequel clients that are connected to postgres and running sequel commands generally so if you do a select from users with some predicate or if you do like dml you're modifying data you're inserting rows changing them um you know and you're you're avoiding some of those special columns that we may have seen in some other talks like the ctaid or xmin xmax you know we're just looking at the columns that we've actually defined holding our data um in addition you know tools like pg dump for instance uh pg dump can give you like it can output a sequel file that just dumps out something that can recreate at a logical level a database you know your current database if you were to load it into something that's blank um but in contrast the physical realm of postgres is kind of what postgres sees when it's actually looking at disk to do what postgres does which is effectively like mapping our data structures mapping our tables onto what gets persisted into persistent storage and this consists of the write-ahead log um in conjunction with the data in actual files like the ones and zeros in pg data's base directory which i'm not going to go into at all instead i'm going to give an overview of how the wall works and how that fits into backups so um just a quick like recap if you know in case i suspect like some people are familiar with wall maybe some people aren't uh the effective story of like what the write-ahead log is with postgres is that like every time you write postgres action you know every time you write to postgres through a query postgres actually writes twice or you know maybe more than twice but not to get too far into the low level details let's say we're like inserting into a table one row what postgres is going to do is firstly and secondly in some order at least it's going to persist that into its shared memory it's going to persist it actually on disk into this append only file called the write-ahead log that happens very quickly because it's append only um then and only then once that has happened uh it's going to tell the client it's going to return to the client saying like okay i uh i committed that like i committed the transaction uh you are safe to assume that that data has actually been persisted um a note on checkpointing so checkpointing is the process by which postgres eventually makes sure what is in its memory gets flushed in some consistent state onto its structured storage um and in between the check you know the last checkpoint that was taken however long ago that may have been ideally not too long ago but ideally for performance not too far not too close to the present uh there is this string of append only write-ahead log that gives us the instructions on how to recreate a consistent state of the current moment right now even if postgres were to crash and it needs to restart and sort of like a dirtier state than we'd like it to uh and so as a part of any physical backup system that involves taking physical snapshots of whatever is on disk and structured storage um the write-ahead log is actually a component that is not only essential for filling those gaps in between which backups you take but it's actually you know on a on a lower level of how the backups get taken it's an essential component of taking any physical backup so there is this parameter called archive command that we may be familiar with or maybe not uh but basically whenever a wall file is uh written or you know postgres is done with it uh postgres manages you know postgres just calls this archive command which we can configure to send it to s3 if you're using tooling like barman wall g uh pg backrest um they'll just have configuration guides on exactly how to set this up and so if we go back to that graph from earlier on meeting our recovery point objective we're taking backups every four hours that's just you know that's just barely enough to sometimes meet our recovery point objective uh with the wall we actually shrink that data loss window to much much lower than uh what we had without it you know and furthermore if we think back to uh point in time recovery that's how we fill in the gaps between uh the backups that we take all right so comparing physical and logical you know i'm not going to go i'm not going to get into the debate of whether pg dump is technically a backup tool or is technically not a backup tool or is like uh angrily not a backup tool um i i feel like it's safer to stay away from that but you know even even if it were a backup tool it's probably it's safe to say that it's not a very good one at least measured quantitatively along the lines of uh the recovery point objective and the recovery time objective uh compared to logical backups physical backups give you a better recovery point um you can achieve a better recovery point objective and you also get continuous point in time recovery you know you can restore not just to like every every two days on the dot for the past month but all the time in between as well um and in addition just like as a performance consideration restores tend to be a lot faster with physical backups than with logical backups so your recovery time objective is a lot better as well and that brings us to the concept of super physical backups um maybe there's a proper term for this that someone can shout at me at the end of this talk uh but i basically took everything that is below the level of what postgres even sees you know file system or block device snapshots whether that's zfs lvm ebs you know elastic block store on on amazon cloud things like there are some things like that that can take consistent snapshots of whatever they have um at some moment of time and i'd say that like that's certainly a possibility for you to use uh some advantages of it even might be that if you use mysql as well or a uh collection of different databases and you want consistency in your backup solution for all of them super physical is probably an option for you there uh the main thing i'd point out though is that uh physical you know just normal physical backups can be a little bit less fragile for instance if we you know if you've heard of the create table space command that lets you put some tables in one db onto another physical disk um you know you you might you can instantly break your backup solution if you're not careful with something like that if you're depending on super physical backups um in addition i'd point out that there is just better postgres specific tooling for physical backups and this is a very hard thing to roll yourself so for that reason the focus of my talk is on these physical backups but super physical it's an option as well um i'm just not going to touch very much on it monitoring all right so good monitoring is loud when it needs to be like something is broken a human needs to intervene someone needs to figure out how to fix it but great monitoring is quiet the rest of the time you know yesterday at a talk someone um you know there was a quote from there were some quotes from brendan greg mentioned and i'm gonna roughly paraphrase one of my favorite personally which is that good monitoring or good debugging methodologies should not just identify when a system is broken it also needs to exonerate like if you really want to have great monitoring it needs to be able to exonerate a healthy system um and by that i mean like it shouldn't have a high false positive rate it should be providing more signal than noise okay so on that theme what are some ways to notice when restores are failing in the first place since this is this is like what we actually care about this is what this is how we prove to ourselves that we don't have a schrodinger's backup we have a real macroscopic real backup that we can use should we have alerts should we have dashboards one strategy that has worked particularly well for us at academia is just a slack bot you know we use slack so if you're on teams uh or something else just translate this to whatever you actually use uh and it will just tell us when uh well it tells us multiple things one thing it posts a message whenever a setup starts along with some metadata like where is it um what environment is it in what type of database is it uh and then it'll do one of three things it'll either uh when it fails if it's destined to fail it'll tell us that it failed or if it worked it'll tell us that it worked or we'll see nothing at all about you know we'll see nothing no updates for a long enough that someone gets suspicious and looks into it like something must have crashed before it got to the point where it reported its progress another thing that i'd like to point out that i really like about this is that you can just like see you can just see your recovery time right there like what is the recovery time you achieved this one was 10 minutes because it's a qa instance it basically doesn't have much of a data set at all this is just uh the time it takes to do all the other things we need to do like install packages postgres included um but to give an example of a production system uh this actually happened just yesterday um so today was yesterday you know 3 p.m um i noticed that we needed to replace um a well a roughly 15 terabyte database uh there had been some suspected degradation in its disc in the disc performance of a node so we decided to just bring up a replacement for it and it completed around 9 17 p.m six hours ish give or take all right so let's think about digging a little bit deeper into the step by step process by which restores happen in postgres though uh so what happens with a physical restore is i divide this into roughly three stages um we could maybe break it down a little bit more uh but there is a stage where we are restoring you know the physical snapshot of the base directory in our case from s3 on to disk that takes some time um you know in this particular case we were able to get roughly three terabytes per hour um if you are also using s3 and you're also using you know streaming to like an ec2 instance and you're noticing you're not getting that despite the concurrency set one recommendation i would make is to check your disk throughput setting especially if you're using gp3 volumes or similar um but you know moving on to stage two what happens after that is it needs to pull wall which hopefully is stored in the same place um if any of those are missing you might get one of those stage three boss fights where postgres just never boots up because you actually don't have a backup at all you have a bunch of different files um you know you don't have a consistent snapshot of the base directory regardless of like what tooling you used if you're missing any of these wall files so it's going to need to restore enough wall files to boot in the first place and then depending on what you've configured a recovery target to it's going to need to replay those wall from your backup destination as well to catch up to the point where we can move on to stage three which is we connect it um i'm i'm i'm drawing the arrow as the direction in which like data flows with streaming replication when we connect this new node to the existing uh node that we want to stream from uh whether that's the writer or some cascading setup uh but you know the parameter is actually set like on the uh subscribers side or the uh you know the standby side uh primary con info and so in principle we want to maybe dig into monitoring each of these steps how do we do that uh disk usage is kind of useful for stage one you know in datadog which is what we use i'll give some graphs from datadog translate it to whatever observability tooling you use if you don't use datadog but we get uh free disk right out of the box um assuming the nodes characteristics are consistent you know the new nodes characteristics are consistent with the existing ones we can see that as it comes up it starts with more free disk than it eventually ends up with if we're catching this like mid setup we can kind of just extrapolate to the point in time that we expect it to catch up and then we're done with stage one um in addition another metric that we get just out of the box we don't need to code anything custom is a replication delay time metric uh so we can just look at a graph and see these slopes you know these downward slopes ideally downward if they're going upward that's a problem but ideally downward slopes indicating when a new node has finally caught up and it's streaming continuously it's at the same point in time as whatever it is following from um in the you know in most cases our writer and in addition to that um yeah so like in addition to all of this so one nice thing about all of that is that restores are what we actually care about and it's what we're actually measuring with all of those approaches but the problem with that the problem with just leaving it at that is that broken restores are fundamentally a lagging indicator of broken backups so we may ask ourselves whether there are any leading indicators that we should monitor as another layer of swiss cheese so to speak or as an optimization just to save us time in the future just to make our system more resilient uh so this one is a little bit pg-backrest specific but i assume you know i've never used barman or wall g but i assume they might have something similar to that or maybe someone's built something on top of it that's similar to this but uh pg-backrest has an info command you can pipe it to head and there is a status row that says okay or not okay you know we could say okay or maybe give you some information around an error and this is really great for humans you know if you're writing automation around this you should probably look into the json output option that pg-backrest has but just like as a visual i find that this is like a pretty good you know a pretty good illustration of a really easy check you could implement right now um you might not get it like out of the box with data dogs postgres monitoring but it's not the hardest check to write just check if the status is okay alert someone if not uh similarly if you pipe it to tail you can get useful information such as when was the most recently recent backup taken um i did not take this yesterday i took this about a week ago so at the time this was a little bit more recent than appears today there's a lot of other useful metadata here as well like you get um you get how much space is being taken i'm not going to touch much on costs today for sake of time but you should be able to cross correlate what is being reported from this tool to what you see in your cloud billing later also um on the theme of you know let's say we don't want to just trust the tool that is supposed to be taking our backups to monitor itself for leading indicators like maybe there's some general notion of corruption we're worried about or we just want like independent uh independent checks here to make sure that our backups are solid we can check s3 what are we checking it for well we can just check if anything is there at all that's a good test generally um there's like there's a bit of a directory structure that you expect to be here we can maybe reverse engineer some of it um i wouldn't get too complicated here this is just defending against that level one boss fight earlier uh is anything actually in s3 is a pretty simple check you can make um in addition while archiving i mentioned that the right ahead log not only gives us is what gives us that power of physical backups it is what gives physical backups it's um you know better characteristics compared to logical backups on recovery points you can achieve and recovery times you can achieve but the downside of that is it is a point of failure like if you have a snapshot of pg data um the way postgres backups work at a low level just depends on wall being present continuously and if any of those are gone or if they're not getting sent to s3 or whatever your backup destination is for some reason or another that means your backups are broken so um some nice things that you get out of the box with datadog um is you get archiving throughput you can put it on a graph uh check you know i have it here split between different clusters but if you zoom into one um a good sign would be that you see it non-zero um at all points of time a bad sign would be if you see it just suddenly disappear unless this is unless you expect that like if you don't expect it to disappear it's a bad sign if it disappears and you should look into it um in addition there is an archiving failure metric that you get out of the box um so you know we can hear we can see some blips here where some clusters apparently have been failing at least once um at least once every once in a while and you know going back to the theme that i mentioned earlier that great monitoring should be quiet when it doesn't need to interrupt someone we may ask the question like why why is this stat non-zero like isn't this bad isn't this something that should wake the dead in the middle of the night or you know wake all the sre's at 2am if this is happening um and i'm not going to dive into the details of here all i'm going to say is like you you need to get a sense with whatever backup solution you're using of what is expected what is anomalous and what is just something you can ignore in this case there are some like weird edge cases you might run into if you are using asynchronous archiving um as an option if you know often you only need this if you are at a certain throughput or above there are some weird things that can lock up with pg backrests implementation in particular um you might not expect this with certain other tools it's um honestly probably a subject for a completely other talk if someone wanted to give it on the current status of asynchronous archiving capabilities with the way that postgres has its archive command system set up um but you know to put it short we don't alert on this we alert you know we don't alert on like we don't we have not tuned our sensitivity of this alert to alert on every single instance where this fails we have a threshold that we've tuned to balance the needs of uh business needs for good backups that we can rely on but also we value our developers we value our members of the on call rotation we value not waking them up in the middle of the night and making their lives uh unhappy so recap every time you need a replica use your backups at least as you know stage one of getting that replica into being in addition to that periodically test a cold restore in a qa or staging environment particularly if you have a need for that to exist anyways if you're doing some sort of test that would benefit from it um on the theme of monitoring you know do as much as you can to visualize the restore process especially if it doesn't have to involve actually alerting someone actually interrupting someone um you know like that slack channel people who are interested can just join and see and that works well enough for us if you're provisioning you know if your throughput of how often you turn through replicas is three orders of magnitude more than us that probably does not scale for you you probably need some other solution or some alerting so context is important um in your organization and last but not least make sure you're monitoring you know especially your alerting monitoring pulls its own weight um and if you do all of this hopefully you have enough layers of swiss cheese to keep your backups working well and keep your you know keep yourself sleeping well at night not worrying about your data disappearing a brief list of acknowledgments i'd like to acknowledge you know my employer for giving me this learning opportunity especially with that old script and helping me prioritize replacing it with more trusted tooling also my team who have sat through this presentation several times as i practiced it for scale in addition michael from you know the founder of pg mustard i practiced this talk with him last week and regardless of what you thought about the delivery here today um i think it's safe to say i did a better job here than back then and that's a wrap any questions biggest horror story um related to well i'd say that like that level three boss fight like i'm i thought the backup was good it all of my tooling and scripting that i've written to check in s3 you know doing everything i can that isn't literally testing the backup testing a restore directly and yet it still doesn't work like that's pretty scary although i'd say that like when i see empty files that have metadata saying they're good that's pretty scary too um yeah i did run into an interesting well it's a little bit diverging from you know the subject of this talk when i just in the process of migrating us from our built-in you know our home rolled backup solution to pg backrest um we were using an archiver known as uh well if you've heard of wall g there was a predecessor to that called wall e that i think is fallen quite a bit out of fashion for a number of reasons but you know it worked for us at the time our you know our restore testing process um our restore testing process pretty much is what i would credit to it working since we'd have to make adjustments but you know there are just a lot of pain points in moving one archiver to another like we had to replace what wall e was doing with what pg back you know with how pg backrest does its archiver process and running two archivers at the same time is just pretty it's pretty complicated there are some bad things i think there's like a ticket that i i created a github issue on on walle's github not not because i expect anyone to fix it i think like the project is archived at this point but just like for visibility if anyone else is running into it um effectively it would you know it's it's it's like a it's like a race condition if you're archiving quickly enough because of how each side is handling uh asynchronous archiving that leaves like gaps in your wall record on the pg backrest side because walle is like directly writing into the um you know archive you know it's kind of writing to something that postgres assumes it's not going to but um yeah i don't have time to i probably probably don't have time for uh this question you know q and a section of the talk to get into all the details there but you know i'd certainly be happy to rant all about that uh offline hand in the back do you have a question oh oh okay what's the alarm any questions so the wall is constantly being backed up or uh yes the question the question was like is the wall being constantly backed up uh yeah well ideally yes uh it is it's a thing that you have to monitor uh one one you know particular failure modes that one should be aware of when monitoring like a postgres backup and recovery system is is the you know is wall archiving falling behind since sometimes if postgres gets a lot of writes whatever is sending you know whatever process is supposed to be sending the right ahead log or you know wall set wall file segments as the terminology goes whatever is supposed to be sending those might just not be able to keep up temporarily you know usually and that's part of why i mentioned like our internal goal um our internal like recovery point objective has a small allowance for a few seconds to a few minutes because sometimes you know we get a burst of writes and we have some things that have been committed on postgres but have not made it into s3 yet uh but yeah more or less you know it's continuously being backed up or at least it should and if it's not then there will be problems oh yeah how big are the log file segments oh the question was how big are the wall file segments and honestly i i so i think it's like a compile time option that you can it's either a compile time option or like a grand unified config option that you can set i i don't remember off the top of my head it's it's on the order of several megabytes and you know another thing to keep in mind is there tends to be like your archive whatever process is doing the archive or tends to compress it beforehand with either z standard or something else uh so you know depending on like how much logical change is actually in there on our qa systems we'll see it ends up in s3 as like these tiny files with almost nothing in it because it just compressed so well and it's just been it's just been switching segments on like kind of a timer process or a scheduled process rather than because it needs to switch to one because so much has been written uh but you know it it shouldn't be too large uh to put it short any more questions thank you for listening thank you test test can everyone hear me yeah cool right so welcome to the second longest titled talk at scale uh i lost too heady um so recovering from data loss despite not having a backup a postgres story my name is jimmy angiolacos uh a little bit about me it's my first time at scale i identify as a systems and database architect i'm based in edinburgh scotland but i'm originally greek i have been using open source for over 24 year for over 25 years and i try to contribute to projects where i can uh one of those projects is postgres i have been using postgres exclusively as a database for uh more than 16 years now i'm writing a book called postgres qr mistakes and how to avoid them and i have co-written a book which is the postgres ql 16 administration cookbook with some other brilliant fellows um and i have also written a postgres extension for performing time series analysis of postgres internal statistics and plotting them into charts which is called pgstat viz so back to the title of the talk recovering from data loss despite not having a backup it's what um that's right the company had no backup it was a real company you're not going to see screenshots from the actual procedure because you know that doesn't i don't have permission to share that and it's been more than a year so it's based on my recollection of this odyssey but it was an actual company with customers that lost their entire database and these things happen more often than you would think so let's set the scene the phone rings on a friday evening at five p.m everyone's ready to go home um i was at home because i work from home so no change there and maybe it wasn't the phone maybe it was zoom because nobody uses phones anymore especially for business but the gist of it is uh the cto of the company comes on the call and he sounds extremely tired and he says that they lost all their data he says they have no backup at that point i'm ready to commiserate him and then he asks can we recover the data so i say no that's it thank you no no wait so i said uh let's figure this out what's happening first of all the database that they lost was critical to their operation the company cannot work without this database because their website is the database it's the contents of the database the service they provide is all in the database their website i find out on the call has been down for more than a week and there's a there's a page that says we're sorry we're having technical difficulties and no information at all for more than a week and their users are starting to complain because a week's fine but more than a week that's an outrage which also means that if the users are starting to grumble their stakeholders are starting to worry as well who you know the people that run the company the people who invest in the company they're starting to worry so what happened they had a disk crash that wiped out their production database server and you may think okay it was just one server with no redundancy so their postgres is gone gonzo their most recent backup that they did have is months old so they really can't use it for the kind of service they provide because it relies on recent on the recency of the data it needs to be up to date they get a quote from a database recovery company and they said we'll see what we can do in two weeks no guarantees it's going to take two weeks but we don't know if we'll be able to recover the data so how does one find themselves in this situation the company used to be a startup and they grew in size and customers and importance over time but and they transitioned to postgres from a less serious database decades ago so they they know postgres they've been using postgres for a long time but they were using pg dump for their backups pg dump is not a backup tool it can complement a backup tool or be used as a backup in an emergency but it's not a backup tool because it doesn't do anything on its own you need to script something which will use pg dump to take a backup and unfortunately the lovely script that they had over you know the past 20 years or so had started silently failing and you know there's all these things that can go wrong without you noticing when you move things around so you may think oh i'll just take this here server and put it on the cloud or put it in a kubernetes pod or something and one of the scripts doesn't like the new environment the very reliable script that you've used for years and years and suddenly there's no error message nothing and it's been silently failing so that's why their last usable backup in quotes was months old and i'm thinking is this really bad luck were they unlucky we know that hardware fails it is not uncommon but there's a glimmer of hope not the database recovery company but a data recovery company has looked at the hard drive and they have managed to recover some files it looks like the physical disk is not damaged and it was probably a controller failure and that's why they recovered the files so the company gives me the dump of the recovered files in order to see if we can construct a database out of those files but they give me files that are randomly distributed in recovery directories that are named 0001 0002 to 100 plus and i'm looking inside these folders and i don't see any structure it's just files jumbled and they're not in the folder where well we've lost the folder names right so but also the files look like they belong to different directories on disk so they shouldn't be together so there's no guarantee that the files in 001 belong in the same directory why is this bad you have to consider how Postgres stores things on disk let's look at that for a little bit so the physical structure of a Postgres database on disk and if you can see if you assume that you're using red hat or a red hat like system it will probably be under var lib pg sql diversion of Postgres especially if you're using the community packages this is where you're going to find your Postgres database and when you perform an ls on this directory you see things like global conf d some configuration files and you also see a directory called base this is the one that holds your data mostly so your tables your indexes they're going to be in base why is it called base haha it's data slash base so that's where the database is so the base directory contains uh subdirectories that hold individual databases the name of these directories is the oid or object identifier that identifies the that database in the postgres catalog so they're going to be incomprehensible names to you so if you have a database called jimmy you won't find anything on the disk that's called jimmy you will see things like one 16582 16587 4 5 and pg sql tmp for temporary files so we mentioned the postgres catalog so there's a view called pg database that you can select from the catalog and if you select object id 16587 that is an identifier that we saw on disk for one of the databases so select star from pg database where oid is this and you get oid the database name finally and you find out that that database is called pg bench which is the example i'm going to use in these slides and other things such as the owner of the database and coding and so on as we mentioned each of these directories subdirectories of base contains the tables and the indexes for these individual databases how do you find which file belongs to which table by selecting the table name or relation name and object identifier of the table and rel file node so relation file node is the file name for the identifier for the file that holds the table from pg class where the the table name is pg bench accounts you can find out how it's stored on disk and which files it's using and you can see that the table pg bench accounts that has oid 16594 is stored in rel file node 1600 1600 so what does that tell you that tells you that unlike databases tables mean their oid may not match their rel file node so you can have a table that has in the postgres catalog oid 16000 something and you can have a different rel file node for that table and you will also find some other files on disk such as the fsm and vm which is the free space map for the table and the visibility map and other useful things and that's how it works for indexes as well they have their own individual files on disk now postgres has something called the segment size and the segment is how big a file can be on disk before they need a second file so tables that are more than one gigabyte because the default segment size in postgres is one gigabyte they get split into multiple files and what you see on disk is one file that's one gigabyte and then the next file that it spills over into is called dot one and if there's another gigabyte then there's the third file called dot two and so on so you can see how your table can keep growing without any restrictions apart from disk space okay so that's how postgres puts files on disk so what is their recovery plan if any the recovery plan is to recreate the data directory structure that we just saw for their lost database with the files that we got from the recovery operation so we basically copy the files inside the directory where we believe they should be so the next step will be after we've copied the files we try to start postgres with the folder that we put the structure in which we will call opt recovery and if postgres starts then we will attempt to perform a pg dump to perform a logical dump of the database so that we can ensure that everything can be read correctly from disk because if there's any corruption then pg dump will stop and we'll say i can't read this file i can't read this index and so on so after we have the dump of the data the plan is to restore the dump to a fresh database you we really shouldn't trust this directory anymore we should extract the data from it and try to start a new from a fresh server next step question marks profit so how do we copy the files inside the data directory it looks scary it's all numbers there's things like hundred and twelve hundred thirteen twelve hundred and if there's hundreds of tables it looks daunting but the reality of it is that oid's below sixteen thousand three hundred eighty four are reserved for system use so they're probably not the tables uh they're certainly not the tables of their database so i can focus on the ones that are over sixteen hundred first yes yes fortunately the file names were saved but there was no structure to the file system it was just a dump of files so i can skip most of these things because they might be common across databases and parts of postgres uh default tables and views so after a lot of typing and copying files back and forth we need to see if postgres will start so the first attempt is we use pgctl to start postgres and we pass the parameter d for the data directory and they say try to start the postgres server from this data directory which is opt recovery so it says waiting for server to start stopped waiting could not start server examine the log output that was not unexpected right let's see what the log says when we examine it so the log says fatal error could not access the status of transaction 803 could not open a file called pg exact slash zero zero zero zero no such file or directory well that's a problem it means that the file either we didn't find the file in in the dump or it wasn't recovered so i start looking for these files and i can't find them in the dump and it's obvious that postgres won't start if that file is not there why it says a boarding startup because of this failure the database is shut down so what is pg exact and what is this zero zero zero file it holds transaction commit state for each transaction on disk so these are files that are usually 256k in size and they hold they store data on the transaction outcome whether it was committed or aborted etc in a weird format which stores the outcome of four transactions per byte so it's two bits per transaction state what we want because now i've decided to fake this file since we don't have it right we want status zero one which is committed for all transactions referred to in that file so we need to fill it with zero one zero one zero one zero one for each byte for these four transactions and the way to do it easily is db so you create a file with 256k of zeros but we actually translate the zeros to octal 125 which gives us zero one zero one zero one zero one and we fill the whole 256k with zero one straightforward so we put the file there and does postgres start now well originally it didn't and then it complained that zero zero zero one was missing and then zero zero zero two so i had to fake all these files and finally postgres started at some point and it said waiting for server to start done server started i'm stunned that this collection of files on disk appears to think it's a working database can we connect to the server so i try psql the default to psql is it tries to connect to database postgres as user postgres uh sorry as the current user but i was logged in as postgres so connection failed fatal database postgres does not exist that is not good but it tells me why it thinks that the database doesn't exist and it says that the database sub directory base slash five is missing i'm like oh okay so i need to find this uh i need to recreate this directory five which is the oid of the postgres database and i need to fill it with files so i find the files for the directory five click the clock does postgres start now yes postgres starts and i am starting to become hopeful at this point so let's say that we want to connect to database pgbench to see what's in there so connect pgbench fatal database pgbench doesn't exist the database sub directory is missing but now i know the oid and i know which directory to recreate and put the uh table files and index files etc that i found inside this directory some more typing and i can connect to pgbench after i've created the directory and copied the files in there so what follows next the next step is to perform the pg dump so let's do it people are waiting right this is taking course over eight hour days on saturday and sunday i said i can't work more than eight hours a day but i will help you so but they're waiting they haven't slept in days right they're waiting for news so i try a pg dump of database pgbench into the file pgbench.dump and it works i am at this point i am ecstatic i call them immediately and i say i have a dump of your data i don't know how corrupt it is but i have a dump of your data are we done is the recovery complete no we need to take this dump and restore it into an empty database server to see what we get it so restoring the dump is pretty simple you just call psql you create an empty database called pgbench and you pass it to the file pgbench dump it's an sql text format file so it just runs sql commands to populate to recreate the schema and populate the database so i get error could not create a unique index um from a table and it says this key is duplicated now that is weird but not really if you consider what we did so what we did was we said every transaction was committed when we faked those files that were missing that we needed in order to start the server so some rows that were dead have now been resurrected and we need to figure out what the current version of the row is so we can get rid of the duplicate that has the same or different data so that repeated itself for about a hundred times and i had to go to the customer and ask them you know i have this customer account that has this data and then this customer account and they said yeah that's more recent so i did that about a hundred times and they confirmed which row we could keep fortunately it seemed to be only a few tables that had this issue and it was only like a hundred times so it didn't take more than a few hours to work through so in the end this was a dramatic save for them we completed the restore and they had a working database with data that appeared to be correct and intact verified by them by sunday afternoon which was less than 48 hours after they got in touch meanwhile their stakeholders were holding a conference call and they were deciding whether to pull the plug on the company this if they lost this data it would have been an extinction event for the company they would have had to work very very hard to regain the trust of all their customers that they lost the data for right so the stakeholders in a conference call the cto jumps in on the call and says we've recovered the data so applause brooks breaks out on the call everyone is relieved because they knew that someone was working on this for the past couple of days but before then they hadn't seen any evidence that the data was actually recoverable so they're really relieved but i'm not relieved because i can't relax until we create a backup of this database that they can actually use because for all i know their disk might fail in the next five minutes and we might have to go through all of this again so i say okay tell your stakeholders but i'm taking a backup this moment so i create a streaming replica of the server immediately and i set it up with rep manager so that they can fail over and i set up backups immediately again with barman is the use the tool that i used and then after i had these things in place i said i can now relax and it's time for a beer so what is the final analysis for this adventure they were extremely lucky extremely lucky because most of their files were recoverable but they were also lucky that postgres has such a clean structure i was able to describe to you so even if you weren't familiar with postgres internals someone can spend like 30 minutes explaining to you how files are made on are put on disk and that gives you an understanding of this cryptic directory with numbers in it right so it's not black magic there's a logic behind all of this and this is why we were able to recover their data it wasn't just one binary blob on disk it was a collection of files and as you pointed out fortunately they had the file names so we could attempt to reconstruct it took a few iterations but eventually we got there so even if you've lost some of the files you can use this methodology to recover as much of your database as is recoverable save whatever can be saved and you can also fake files or reconstruct them correctly files such as pg file node dot map or pg control you can recreate those or you can fake those or you can take them from a running database and they will be the same so the good thing is that postgres complains at startup that this file is missing so it doesn't so it doesn't say error i'm throwing my hands up i don't know what's wrong right it tells you what's wrong and this is why we were able to find identify the files that were causing the problem because they were missing and so on if the file that holds the data for the table is gone then the table is gone but you can attempt to recover the other tables right you can just remove that table from the catalog and proceed or you can dump each table individually right you don't have to perform a full pg dump of the database now what about the right ahead log that is in pg wall that wasn't this wasn't that important for this recovery because whatever was in there is going to be used to recover to the last feasible point when the server starts up so whatever you have is whatever you have if you've lost some files from wall they just won't get replayed and you may have lost the last let's say 16 megabytes of transactions i think that's acceptable compared to losing your entire database so let's see what you should not do and so that you can avoid finding yourself in this situation first of all you should not use pg dump as a backup i mentioned in the other talk i by magnus he said that the same thing he said pg dump is not a backup and i told them then we should change our documentation because as someone pointed out to me a few weeks ago when i told them that pg dump is not a backup he said your documentation says that it is a utility that you can use for backing up postgres so we probably need to send a documentation patch which will get scrutinized and rejected but we need to tell people that pg dump is not suitable as a backup tool because it doesn't support point-in-time recovery the thing that wall is there for right so when you have a pg dump it only captures that moment in time you cannot roll back you cannot recover up to a certain point it's a snapshot essentially of the database plus as a backup tool it is sorely lacking because it doesn't have automation for me if your backup is not automated it's not a backup right manual back backups are not backups because you may need to go to the dentist or you may have something to do and forget about it or someone else forgets to take the backup it needs to be automated it needs to be monitored so that you know if the backup is failed there needs to be alerting to notify someone you know you have no backups you need to run and take a backup now because we'll lose data if something happens now and of course backups need to be tested because as GitLab found out a few years ago they had a backup they hadn't tested it and so they lost a lot of data so in order to avoid doing all this you can just use one of the ready-made backup tools that exist for Postgres like PG Backrest or Barman the two open source tools that dominate the field they're also proprietary solutions that you can use I know that some of them work with Postgres now so really this should come as no surprise you should not use PG dump as a backup and what you should also not do is maintain radio silence because the people that need to use the database will start coming up with theories this is real they came up immediately with a theory that the database was unrecoverable that it was ransomware that some hacker broke into the database and that's why the company is not saying anything the company was not saying anything because they didn't know right when they found out that the files could be recovered but they shared that with their customers when I told them that I now have a dump that we can attempt to recreate they shared that with their customers and when I told them that the database is up and running they immediately shared that with you need to keep everyone informed otherwise it you can suffer reputational damage which sometimes is worse than the actual damage of the data loss right so it wasn't a security breach it was just one of those things that happens a disk went boom and what you should do in that situation is not to panic you don't start running around waving your hands in the air saying oh my god where are we gonna do inform the president inform everyone well you need to think rationally and examine the situation see what you do have see what you don't have and see whether that can lead to a successful recovery what you should do in order not to find yourself in that position is have redundancy streaming replicas are dead easy to set up if you even if your server goes boom if you have a streaming replica you can just start using that immediately you should have automated tested backups as we said when you do recover the data don't touch them make a copy of the recovered files and operate on the recovered files and as we said you need to keep the team informed so even if you're the database person that is attempting to stay in front of a screen for hours and hours trying to recover the data you need to keep the stakeholders informed with what you're doing so another thing that we can learn from this is that the degree of recovery how much data you recover matters but also the speed matters as well that's why we took the decision me and the company stakeholders we decided to fake the transaction IDs to proceed I couldn't it would have taken a very long time to to figure out the correct outcome of every transaction right so it was in the end it was faster to just fake the files pretend that all the transactions were successful and deal with a few duplicates it's better to bring something out faster rather than wait for complete correctness sometimes but of course that is not a decision that I should make right you should as a tech person you should always ask the customer what they want to do because what may be obvious to them may not be obvious to us right they have business requirements they say this table matters recover this table only or recover this table first thank you very much any questions there's one yeah can you hear me just out of curiosity at no point did I hear you it could have been in the clickety-clackety but did you not use the backup the months old backup to simulate the file structure the table like to try and identify there was no file structure it was a logical dump so it was sql commands so I couldn't use that so the pg dump that they took well every pg dump is a logical dump but it doesn't store any of the structure of the database it just stores the data and the schema as sql and sometimes it puts it in the binary format but they only had the text format I was wondering if you had any any thoughts or ideas on like whether there's any room for Postgres core to build out something to protect I guess users of Postgres from themselves in this case or you know you know kind of like is there some strategy that might at least help avoid this failure mode of like we don't know what we don't know like we don't know that our backups aren't really working like I don't know like a really silly thing that probably isn't very useful that I just thought up is like what if Postgres just like refuse to start or right with an error if you know you don't have an archive command set or some things pertaining you know at least as far as Postgres has visibility onto it some things that pertain to backups like Postgres will just say okay you know well you can set some other gc saying like right I don't care about my data this is just a test DB with underscores or camel case or whatever and then then I'll start up but I don't know if you you have any thoughts like maybe not that specifically yeah so Postgres leans on the side of letting the user decide what to do instead of telling them you should do this which gives you more freedom and becomes less annoying because as you said you may have a test database that you don't want to backup you don't want Postgres complaining oh you don't have backups for this database right so what Postgres tends to do is let you shoot yourself in the foot and also there is no official backup tool for Postgres in core intentionally because everyone else has the freedom to develop what they like so there's pgbackrest has focused on different areas of the backup procedure than let's say barman or walley or wall g right there's all these solutions that you're free to use and Postgres doesn't make you use any of them now archive command is not used by most of these tools so you wouldn't get a warning anyway so Postgres does warn you when there's something preventing it to start up but it doesn't warn you against things that you should know such as take a backup they at least using a raid system on their new server I don't know that I remember we restored on to some cloud instance so I guess that's taken care of but it wasn't really my job to tell them what to do I told them what to do for not getting into this situation again in the future I can't tell them where to run their databases or which provider to use or what hardware to use but I could tell them that you really should have a backup you should test it you should have redundancy and so on and that's why I ensured before I left for the day that I created those because if I said well you need to do those things and didn't check to see if they did them they probably wouldn't given their track record what percentage of their weekly expected revenue did you charge for your 48 hours of work um let's say I got something out of this it wasn't my engagement I was working for a company that said you know it's up to you if you want to work during the weekend or we can tell them that we'll start work on this on on Monday so I took the decision because I felt sorry for them really there when when they told me that their database had been down for a week I saw that this company was in deep trouble and I tried to help them uh it's it's it's not really about uh well at that point it wasn't about money for me because I would make the same amount of money whether I help them on a Saturday or a Monday or wherever but okay any more questions great thanks very much thank you Jimmy