The title of my talk is Auto Database at Sanity. Another title is Syntax Error because there is no such command in Postgres. And I will be talking about preventing disasters. Now Gabriel from Second Squadron delivered a very nice talk on Barman and how you can recover from disasters using Barman. But my talk will be how do you prevent disasters because you don't want them to happen altogether. And I look for Zalanda. Zalanda is a big company in Europe. We probably sell more shoes than any other company in Europe. At least most of the companies. And our marketing department is very sensitive about the numbers of customers we have but I will just say that we have millions of transactions and Postgres is the system that supports most of them. So we have a big technology stack and really a lot of technologies are involved in making our customers happy and making us happy as well and sleep at night. So all our components are interconnected. Somehow unfortunately they are interconnected through the database and this requires additional attention from us because we want to make sure that no single change will break the database and the shop should run without any down times. And our customers are really crazy. Sometimes they shop at 2 a.m. Sometimes they shop at during the day, during the holidays, 24-7. So we really have to make sure that our database is also run 24-7 and we don't have a possibility for down times. We don't have this luxury. So I want to talk to you about how do we change the databases. So Valentin is here. He was talking about how Postgres is easy but to make Postgres really easy you have to think about your process of development and not only the software development but the database development and I would first talk about the changes in your database about what possible problems one can encounter by changing the data in the database and I will also cover some tools that makes this process more easy and I will talk how in Zalando we use those and other open-source tools to make sure that our databases are always available. So let's cover the database changes. Usually there are three of them, the model change which changes the scheme of your databases. This is what you do when you develop your application and for instance add columns or add tables and add foreign keys. All these involves model changes and these changes are pretty... So applications are usually very sensitive to these changes because if the application expects a certain table and the table is not there then the application just breaks. So you have to make sure that changes are tracked. The others one are not really less important. The data changes, usually it's data migrations. For instance you may decide to populate your table after you edit it for instance with the customers or you may decide to update your table to correct some error and the table may be 10 gigabytes and it might take quite a lot of time for you to do this. And the third level, third type of changes is the changes to your code and to database functions if you use database functions and this also involves some issues which I will cover. There are different types of changes and yeah, so this guy is expert for changes, changes that work for you but sometimes they don't work in production. So we want to... I want to talk shortly about some horror stories and these horror stories are based on real events but all the table names and code names are changed. So one of them starts like this. You get an internal error and you see developers running to you and saying some loud stuff and they are definitely not happy. So you start looking at the logs to see what caused this error and you get to the application, you get to the Postgres work and you see this query. This is just a select. So you check the table is there but you see that the column that is trying to use is not there and you go to the logs a little bit upward and you see that this column was dropped by the developer. So some developer decides it's a good idea because the column is obsolete and why would you keep this? We can save the space in the database and it just went to the database with IP SQL and dropped this column and now we have an application that is not running. For sure, once we diagnose this problem it may be easy to fix or it may not depend on the content of this column but what we want to do is not to fix the problem afterwards. We want to prevent such kinds of problems and for this we need to be able to track what statements are executed against the live database and better, we want to actually test these statements before on some staging systems before running them directly on production. So I want to also cover such one thing that is related to the logs. Unfortunately it's a little bit too dark but we have a tool that allows you to show the running processes on the database. It is similar to the top but specific to Postgres. It shows you some system statistics and it also shows you what kind of queries are executed currently, basically the contents of PgStartActivity at a given time and it's updates in real time. So what we can see here is that Altatable took a lock on the database so forced all other processes to wait on this lock and the developer thought this Altatable is very lightweight because the table it tries to alter is almost an empty table, it has 8 kilobytes so what can possibly go wrong? The 8 kilobytes table Altatable will run in an instance but what actually happened is that someone was doing select from this table before and how Postgres works? So the Altatable acquires a very heavyweight lock the access-exclusive lock. It conflicts with anything. The select statement acquires a very lightweight lock access-share lock, it conflicts with nothing but this access-exclusive lock and what happens is that Altatable have to wait for the long running select to finish and all the subsequent processes were just queued no matter what type of lock they tried to acquire they were put in the queue waiting for this Altatable to finish and this causes the application to get timeouts and this causes the customers to be unhappy and we are unhappy customers so we want to test everything so if you are this guy and you test everything on production then you may not leave this talk and continue because we have some good news for you and we have some good tools for you to prevent you from doing these things but I first want to cover another horror story that involves sprocks and also show one of our tools the open-source tool called PGObserver it doesn't have a light mode unfortunately it's only dark mode in our database team but what you can see is the development is the running time for the sprock the stored procedure that was recently deployed and the red line delimits the running time of the stored procedure before the deployment and after the deployment and you can immediately see that the time after the deployment just skyrocketed so jump ten times or even more and this resulted in a lot of timeouts and you know the rest of the story you don't want to be this guy so the sprocks also has to be tracked the sprocks has to be tested and the one has to be aware which sprocks are running on your database so what I want to take are some techniques that are usually used for the source code application and transfer these techniques to the database development so this are for instance version controlling how many of you have your application on the version control good how many of you have your database code on the version control okay quite a lot of people I can say and unit testing how many of you do unit testing for your database changes Valentin, raise your hand you do it okay okay so also how many of you have staging environments for your databases yes this is very good awesome and the last thing is the special deployments for your database changes so you may be aware that at the given time your database changes and it's not like your database is changing constantly because someone go in and drop in your columns and then your application stops working so this is things we will have tooling for and I want to mention that Postgres is really very good for doing structural changes and for supporting these kinds of tools insanely great for same database changes because it's fully ACID compliant meaning that you always have your once you edit your database you have your data there who actually knows how ACID is translated no one okay Atomic, consistent acceleration very good and the last one? durability exactly so Postgres is durable once you put the data there the data stays there this is very good because you cannot test the database it just doesn't retain the data you put there it's not a database the one thing that is very important is transaction of DDLs it's amazingly important for testing because you can actually say something like auto database auto table at column and you roll back this transaction and the column is not added the database is in the state like before the transaction and you can write a lot of tests like adding a column putting some data into the column adding a foreign key seeing if it works or not so all of this is possible with transaction of DDL is Postgres is quite unique in this regard there are a lot of other commercial databases that doesn't support this feature so you can write stored procedures in different languages and this encourages you to use the stored procedures instead of writing your code inside writing your SQL inside your code and the last thing I will cover is also allows you to select which function which stored procedure you run via this mechanism called the search path so all of this makes writing tools to have structural deployment and to change management in Postgres quite easy and the first tool I want to cover code sketch is the tool that is written by David Wheeler from Portland he is a Postgres hacker that usually goes by the name of Theory and he wrote quite a lot of amazing tools and sketch is one of the tool I learned and actually evaluated quite extensively so you use this tool to create atomic database changes step by step he says about this agile database development so you don't throw all the schema at once but you create table by table column by column in small change sets and it actually integrates with your version control system for instance like Git so it has support for merging different changes from different branches and resolving the conflicts that may occur during this merging this tool is also so it has an explicit change plan that allows you to write changes and always know in which direction in which order these changes will be applied to the database the process that you use with this tool is a deploy, verify deploy, reward, verify process so you write the deployment scripts you write the scripts to roll back your deployments in case something is wrong or in case you just want to reward your testing environment to the initial stage and you also write the verify scripts and this is very interesting concept so you write scripts to test your changes and I will show you how these scripts work so one of the ideas here is you run your change sets you then deploy it on your testing environment on your staging database which might even run on your local laptop then you make sure that these changes actually work and then you put them into production so how it works it's very easy to convert your existing code into a sketch basically here I just initialize the new Git repository but it's not necessary to be Git then I just said give me the engine Postgres give me the project name pgconf.us and give me the URI which allows you to have multiple projects and have interdependencies between projects and create some files it creates the configuration which allows you to specify the connection screen for the database among other things the sketch plan which shows the order of execution of your change sets and these three directories that will contain the actual change sets and so let's create our first change set here I will add the application role that will control how my application which role will run my application and it creates three files for me and says that it added this change to the sketch plan so this is how the change looks like it's always inside a transaction because if it's inside the transaction you can guarantee because of this atomic function of databases that you either apply it completely or nothing is applied so this is a transaction and there there is a simple statement that just creates a role for me and sketch is also adding some commands to the beginning of this file it says which deployment is it so the verify script is also quite easy what we have to verify is this role that this role exists and what we do is we just check that Postgres has this role and we have usage rights on this role so if we run this statement and the role is not there then it will throw an error and sketch will actually work with you and process this error as a failed test and report that you cannot deploy your change because the test has failed and this one has a role back at the end because we don't sometimes we don't want the tests to affect our databases and how do we deploy these things this is very easy you create the data I create a test database I say deploy with a given with a given database stream this database stream can actually be hard-coded inside not hard-coded but add it to the sketch conf so you don't have to specify it all the time and then it will just apply my change to database and says ok this change is applied I can add another change and in the second change I say that this change depends on the first one so I want to create a schema for my database and to create a schema I want to have a user in place because I will grant this schema to the user so I do this and this basically the same command just minus r thing that says it's a dependency and it adds these new files for the change each change has its own file and it also adds a string to the sketch plan so this is how the change with the dependency works looks like it's basically the new string in the command that says it requires a pro it requires the previous dependency and you won't be able to deploy this script if you didn't deploy the up schema first now I want to know that Sketch does not try to resolve the dependencies for you this is still your job what it tries to do is to make sure that you don't deploy your dependency that you have all the dependencies in place before you deploy your scripts so this is how the verify part sorry this is how the reverse part looks like I just drop the schema if I want to roll back this change and the verify part is also quite easy what I want to do is to check that the information schema contains this name and if it doesn't contain this name I want it to throw an error and one technique to throw an error is to just do divide by zero so here I just divide by zero and if there is no schema it will show me the divide by zero error and Sketch will process it and say something is wrong okay so this is what I do afterwards on my test environment I want to start to roll out all the changes from scratch I don't want so I want to have a clear database before I roll out all of my changes so I just say revert to my previous approach change and then I deploy and during the deploy it will roll out all the changes from the plan so now I have a database which has this approach and app schema and all of this depends on the plan file so this plan file contains the some metadata like syntax version and name of the project which is important because we have multiple projects in your database and what it actually has is the exact change names the timestamps and the author of these changes and the commands that you specified when you created your changes and you can also see that for the app schema it has a dependency in the brackets for the app roll so you can actually take a look at the plan or you can even edit this plan manually if you don't like how the changes are structured for you then you can just load it so Sketch also supports development in branches so you might actually have a branch for each of your database change then do something in this branch and then might merge this branch back to the master branch and the problem with the merge happens because the plan file is the single file in the project and every change adds something to this plan file so if two different changes in two different branches modify your plan file then some of the changes then during the merge file you'll get a conflict and you'll get a conflict like this so one change edit the talk table to our PGCon project another change edit the speaker table and since both of them try to modify the plan file the merge just failed and we have to do something to resolve it but happily we have the support for resolving such conflicts in Git this you can specify what kind of merge you use in Git and you can say that you want a union merge and the union merge instructs Git to actually take non-matching lines from both files and combine them together so at the end you get a file which contains changes which contains lines from both of your changes and after you do this merge with this union there is a command that you can go to your repository put this command and it will convert the merge to union merge for this specific file which is only sketch plan since the other changes all reside in their separate files you only need to take care of sketch plan and then there is a special command called sketch base and what this command does is it changes for you and then applies them back so once you merge your branches you have the content from both of them you also want to start from scratch and test your changes and this is what sketch base allows you to do of course sometimes you want to see what kind of changes you already applied and this is easy you do sketch log and it will show you what kind of changes you did for instance here it says I deploy a change called git log so I couldn't put it in one slide on the second slide this continuation this is a reward command that says that I rewarded the app schema so this important that sketch log works everything even your database is in an empty state because you applied some changes and then rewarded the changes everything is locked in sketch log so you can always see who applied the change to the broker application or you can track these changes sketch revise on the metadata that is in your database there is a special schema that is called sketch and in the schema there are tables to track the changes to track the dependencies to track the projects because you have multiple of them and some other information for instance information about the tags so for instance the tags is a very important feature in sketch to roll out your changes from the release to the production what you want to do is to tag your changes saying okay this change the set of changes from the current one and before current one all are ready for production so you say sketch tag for instance release 1.0 and then out of your repository you just say sketch bundle and the bundle will create a bundle for you basically it's similar and then you can put your data for instance copy the data on the production server and write with this bundle directory you go inside and you say sketch deploy the name of the database and instantly your changes are deployed on production so it's a very easy release cycle you test it on staging you bundle you tag it you bundle it and then you roll out your bundle on production environment and you get exactly the changes one interesting feature that I also want to cover is rebooking usually when you create a new change in sketch you do it in the separate file but sometimes it's not convenient for instance if you create a new version of your function and you only change the couple of lines in this function you don't want a second copy of your function in your file so you have an git to see the history and to track your changes to the functions you always want to see who changed what git is very good for this so there is a concept on in place changes in sketch and these changes so instead of writing new files for the change the rebook command copies the existing files this tag actually marks the original change and this leaves you free to edit the original file so this is a little bit complicated but I want to show you is an example suppose I have a function that says set talk and that sets the information for this talk file and the new name of this file contains a tag that I said before so this tag distinguishes between the original file and the renamed one and the new files that I have with the new that I want that I will change are those files that are below so this set talk further and they will implicitly depend on the previous version of this changes which are marked with this tag so if I do get status you will see this untracked files these are the files with original changes that were added by a sketch and there are also changes that are there are also changes in the in the that deploys the changes as a script that rewards the changes so that you can reward the new changes to this to the structure that was before to the structure of version 1.0 of your file so if you want to to reward your reworked changes a sketch wants to make sure that you will reward it to the original version and this it works very good for functions because you can do create and replace multiple times and it will always bring you the version of the function that you want and probably won't work for things like alter table at column because you cannot do alter table sorry not alter table at column but if you just put the new if you add the column it just won't work so there are only certain types of changes that you can rework and these changes should be important you should be able to apply it multiple times so this is sketch it's quite a powerful tool so the concepts are you have a deploy verify and reward cycle and you can instantly bundle your changes the next tool that I want to cover is a little bit more simple than sketch it's actually very simple it's called versioning and for me all the tools that I see for managing database changes has very awkward names this is probably the most confusing name I have for the tool what tool did you use I use versioning what? versioning what kind of versioning but this is the name of this tool it's written by Depeche it's capitalized yes it's written by Depeche the hacker from Poland and the idea of this tool is that instead you try to isolate your changes in the simple transactions simple atomic transactions and so instead of making changes on development and finding differences between production and development you just make changes on development and then roll out the same changes on transactions so the concept is very similar to what we have in Sketch but as I said it's much much easier so it has also the notion of changes as atomic files contain the transactions it can track already applied files it allows you to write rollback changes and it also can maintain a change has to go before the before which one so how the patch looks in this versioning tool it's very easy you get the transaction and inside this transaction you have to register this patch and you have to supply the unique name for this patch actually the unique among the databases then you get a change here like alter table for instance and then you commit this change so once you do this it will apply it's second time then the register patch function will catch that you already have this file in your database and it will not allow you to do this so every change can be applied only once and not more than once and also has the notion of rollbacks you can write your rollback diff and this rollback diff should cancel what you did in the table the tracks which patches are currently applied so you can apply this patch for instance once again so the rollback patches are especially recommended for some mission critical applications if you if you're not sure that your change will work with your software and you want to quickly revert this change then you just write that you can set in the register patch you can supply the dependencies and it will make sure that the dependencies are already in the database before it tries to apply your page patch and it also one patch can depend on multiple other patches so it also have a concept of conflicts I don't throw it here but it works pretty much the same you can say that a single patch conflicts with your patch will never be applied until you revert it and this it it's a very simple tool it basically couple of screens written in PLPG SQL it's very easy to audit it's very easy to see what it does it's very easy to modify if you like and it has only one table in the database in the special schema which has the name of each patch the timestamp requires and conflicts to check the dependencies which patches are required by the given one and which patches conflicts with the given one so it's it's really very lightweight you can really start using it without much preparations just roll out a single SQL file on your database and start writing your divs files in patches and calls in transactions so these are very similar but different tools one is very simple and it doesn't it's very easy to see what it does very easy to understand the other is a little bit more complex but it's also more powerful it has the notion of tests for your patches and it cooperates with your version control system so I want to also mention some other tools for this PG type it's also written by theory and this tool is designed to create unit tests inside your database so if you solve the tests that we wrote here these tests are pretty crude so we use the functions that are supplied by Postgres because the schema is there but PG type is much more powerful to ask you to see for instance if the table is there if the table has the structure that you expected to have if the table has the proper defaults if the table has foreign keys if the function for instance that you defined has a certain string in the body if the function is volatile or security defined functions that you have in PG type so it's very easy to write unit tests with PG type and it's also very easy to combine this with PG type and write your full scale unit tests for your changes the other honorable mention is the schema evolution manager made by guild group it's similar to two tools that I already absorbed it's a little bit different for instance it doesn't have a notion of rollbacks because this guy said that if you drop a column across the data but it may work I may not work for you it's written on Ruby so if you like Ruby you may check this out another tool I want to cover to mention only briefly is the FDIF tool as I said all these tools have very awkward names and this tool is designed to make sure that the modifications of your database functions stay in sync with what you have in your version control system and basically it prevents so you get a snapshot of your functions in your database and once you apply it the new function you can always check that the function in the database after you applied it is the same that the function in your version control system so it's basically designed for the environment when multiple people can modify your database concurrently at the same time and it might be or might not be a very good idea but if you have such an environment so this is a tooling and I want to show you the actual example of how do we use some of these tools in production and we end on the very so we like the process that we have for our databases sorry and this process consists of three parts we have database diffs basically the schema changes which we use version info there we have the schema based API deployment and we also have some tooling to make data migrations to make sure that one running transactions won't kill our databases so first how do we access the data all our data access comes through these tool procedures and we use the Java layer which codes the special middleware called SproKrapper and this SproKrapper makes all the hard work of translating Postgres types to Java types and back and forth and it also creates the proper function inside the database from Java standpoint you only write a certain class you give some decorators and then your function is magically translated to the function inside the database this thing is open source if you go to the GitHub for Zalanda then you will find all these tools and SproKrapper as well and all the Java layer talks to the Postgres layer talks to the data layer the stored procedures don't allow access via simple SQL statement so we can control who actually access our databases what operations are performed on the databases we can always review the functions that we execute before they come into production so our deployment procedures are very agile we have weekly deployment cycle for our code changes and this also covers the database changes and this means that every every week basically and up to hundreds of changes to our databases so we really want to track these changes we really want to make sure that we know what we put inside our database what schema changes we do so that it won't break the production because as I said we have to have a maintained 24-7 environment and we use the Git to track our code and we also put the database files in Git as well and we have the notion of release branches so once we have a deployment the deployment always goes from the release branch and this release branch is the branch that was tested and this testing and reviewed and tested and this review and testing includes the changes in the database and as I said we don't allow any downtime and we don't have any downtime during our deployments so this is our Git structure looks like basically our database files recite in the directory under the database and the data directory contains full definitions for our schema for instance such things as create table the complete statements or create type or create function and we also have the API and the notion of API this is the functions that interact directly with the application code in Java so it goes to the separate directory and we also have these dbdfs the change sets actually patches that we apply on top of our existing database schema that is running on production so if you want to add a column in the database you write the change set that says auto table at column and we put it into the dbdfs and then we have a special tooling that can find out which dbdfs we have to roll out depending on the current week number because we are from Germany we use number in for everywhere marked with weeks so we find the proper week we get the dbdfs from there and then we apply it first on staging and then on production so yeah I think I already covered this I want to talk now about the database divs these change sets we use the versioning system that I was talking before and this changes what is important our development team is very big I mean hundreds of people more than 500 get it twice as big as currently but the number of database people are database people we only have 10 persons there maybe even less now and it's physically not possible for database people to write all changes to the data schema and to write all sprocks so we don't write these things by ourselves we let feature teams to do this our task is actually to review these changes and to make sure that these changes are in the database and also we have a policy of avoiding exclusive walks in our change sets and we try to make sure that we try to avoid exclusive walks and we have special tricks to do this for instance here is the dbdif that actually runs the transaction that adds the column and adds the new column with the now default value and basically if you do this in a single statement outer table at column not no default something it will lock your table for the duration until it rewrites it completely and for big tables it might take quite a long time and during this time as I was throwing on the previous slides everything that tries to access this table is blocked so basically we can just use the normal in this inside the transaction we only add the column without the not now statement and we set a default value and this thing doesn't lock the doesn't lead to table rewrite this thing is very fast basically it's just a change in a system catalog and all the heavyweight part that does the rewrite of the table that updates every row from the table we put outside of the transaction and as a final stage after we stated we set it to not now so at the end the locks that are quiet on this table are very minimal we only acquire one lock for the outer table inside the transaction which is very small because it's a catalog rewrite sorry catalog change and the second one is we acquire the lock after the updates statement and it's also quite fast because it doesn't involve rewriting of the table so and this process also runs for has to change the schema and has to acquire heavyweight work we try to avoid this so we try to do not to avoid duplicating the definitions of our database files and for this P-SQL cannot work with us because there is a notion of adding the data to so inserting other scripts in P-SQL basically here we have the patch and in this patch we just add the new table which is to just put create table inside this dbdif but then we had to do it twice we had to put create table inside this dbdif and we also had to take to put the create table in the directory which holds all our schema definitions in the data directory that so basically here in the data directory we would also put the same create table statement command which inserts the full contents of the given file inside the database when we run this div so each dbdif proceeds we proceed with dbdif in a certain life cycle first it's tested locally with the feature developers we have a background box and we have a virtual box that allows them to run these things on the release staging and on the release staging our qa teams goes there and tests the changes to the database as a part of the overall testing and after we make sure that the release staging everything works the patch is applied to the patch staging which is a special environment we use to apply hot fixes to the production before they run on at least three databases and reviewed by at least two database engineers so we try really hard to structure the process in such a way that no dbdif is left unattended and no changes is left unnoticed and also have some tooling to actually reviews these dives so you have a patch and you get the email saying you have to work on this patch more here's the suggestions of how to fix it and then they fix it and then we review it once again so this makes the process a little bit more streamlined and we also have some naming conventions for our dives so as I said this versioning tool is very simple very easy just one one screen of functions and but what the power of this tool is that you can put some additional policies on top of this versioning and customize it for your specific process and for your specific company so in our case we use for instance Gira and in the name of our deployment in the name of our versioning scripts we have the Gira version of the ticket that is we can actually check before applying the dif which is related to this dif is in the proper state so it's not in the state of still working on it or it's not in the state QA didn't work on it anymore but it should be in the state like completed ready for production and we also have some names of the databases inside our dives that allows us to write tools to actually locate these dives so if I say something like okay this part of our application we can write we can run the tool and the tool will locate all the changes for us and it will present it to us and then we can just deploy these changes and also verify that these changes are in the proper state and you can also locate a rollback dif if necessary and for some environments like payment environment we do require so this is about the schemas but what about the functions since I said that we have all the data access gone through the functions we also have to make sure that the functions are properly tracked and deployed and for the functions we use the trick that is called the search path the search path is the feature of Postgres which allows you to switch the function that you are running instantly and in runtime so here in the example if the search path is set to first one then you get one function to the second one you get another one and the function name is absolutely the same so we use this trick in our deployment to switch between different versions of APIs as I said we have one API per week so each week we deploy the new version of API and in order to select which APIs we use we just switch the search path inside our application just one statement very easy and if something goes wrong we can always switch it back simply roll out the previous version of our database code or we can just test the new version of our database code before the production application will pick it up which is also quite powerful and we use it and our API deployments look like this so suppose we have two instances of the application running Java and they are trying to so they connect to the Postgres database and initially they use the old version of the application and when we do the API deployment we just roll out the next version of stored functions the next version of stored procedures basically by by executing these sequels against the database and now we have the two versions of API two versions of our stored procedures running concurrently in the database but the application is only using the old one the new one is just in the database it's not used yet and the QA can test it and we can take a look and see that it's rolled out correctly and this is the version of the database is really the version that we want to and afterwards after we make sure that this is the proper version we just switch one instance we are the load balancer so sorry we are not we are the load balancer we just switch one instance we are the search path so in one instance we say okay search path to the new API and this instance picks up the new API the old instance is still running with the old one and what is important that we maintain the compatibility in the schema between the old and the new version so every dbd that we try to roll out has to make sure that it does not break the schema it does not change the schema to be incompatible with the old API we always say that the new changes to the schema has to be compatible with the still running API in the database and by giving this we can achieve a step we can run multiple versions in parallel and actually use multiple versions in parallel and once we tested this once we made sure that this thing is working correctly we can switch the whole application or other instances to the new API and suddenly we run the new API and this deployment happened without any downtime and without any troubles from the database part so the last part I want to cover is how do we boot strap the databases these are very easy because the databases are running in Git and we actually don't use something like plan files because we have very many changes and these changes has to be processed by these changes are produced by a lot of developers concurrently so we don't want these then constantly resolve the conflicts when modifying the single plan file so we just use this version in and we write the changes in such a way that so we number our database scripts the most simple process and when we try to when we roll out the database on staging or on production we just use the find command to find all SQL files sort them by the numbers in the files and then just apply them to production and do it in one transaction so if everything happens you still have the database that is in the same and in the consistent state and by doing this I mean by having these full files we can actually make sure that the the current code can always be rolled out on our testing database with a very simple command so one thing that we also do is the data migrations and since we have tables that are multiple tens of gigabytes in size we cannot just run update on these tables because this update will probably block other processes trying to modify the data table so what we do instead is that we split updates in chunks and we have a special Python script that can run these updates chunk by chunk and it also can run them concurrently on different shards because we also use sharding and what is important when you run the updates and what we will come in across multiple times is that when you run a big transaction on your database you may not only block the existing processes for instance may run out of storage for your right-hand lock and then your database will just crash or you may even bump up the load on the server so much that it will be unusable for every other process so we have these things covers in our tool and we try to control the load and we try to control the wall storage and we also think that we have to run vacuums after a certain number because otherwise we will just lose this space and we run vacuums and this is also configured so this tool is not yet open source but I mean we are just trying to eliminate our internal dependencies on some other tools that we have we will open source eventually so the idea is that you use the select function to get you the ideas of the roles that you want to change and then the second statement just changes and you can also configure the vacuum actually in 9.5 it will be very easy you may roll out your own scheme of migration without such a tool because it has a new feature called skip locked which allows you to select some roles for update from your table and skip the roles that are already locked so you may combine this in a common table expression with an update statement to actually get your roles selected then update these roles and then return the count of updated roles and run this command until you have zero updated roles which means that you updated all the table so in 9.5 it will be much easier but so far we have to rely on our tools and something that we only want to implement is the testing of our changes testing but we actually make changes in two different places we write dbdfs for the changes and they also make changes in the schema files for instance in the create table if we want to add a column we write a dbdf that adds the column and they also modify the table definition itself to add this column so the idea is to combine these two sources and to test that the dbdfs actually put the database into the whole system but so far we are not yet implemented this because git is a little bit problematic on how to reach how to find the path in the git how to find the branches in the git how to find path in the git from a certain commit to a certain commit so we are working on the schema comparison tools that allows you to find all the difference between several environments which is quite important if you have a staging environment is it the same as a production and you can use something like pgtap for this or you can write your own tool to do this and this is what we are doing and one thing we also want to do is to handle the dependencies between the schema changes and the migrations if you want to add a column in the table and then populate this column with a big update statement then you don't want to fire update statement before the column is added or something in the future and we are constantly updating our open source tools and we are constantly we are trying to present them in the conferences so stay tuned we are going to roll out new things yes so now do you have any questions to me yes can you go back to the slide with the links with the links to the tools yeah actually those are links to the yeah here this one yeah I will put these slides to the somewhere I mean in Postgres Wiki for instance and I will also add the links to some other talks that covers the same topic so I want to have some comprehensive repository of these practices so that you can choose between them and whatever you want yes we also forgot to mention is that we are not only checking the reviewing all the changes that developers write but we also provide the training for developers that they know programs that can happen when they create changes we do this yes when the developers come aboard we make sure that they can write SQL they know what Postgres is they know how Postgres works and they are aware of our infrastructure and this is part of the developer training development team we just want developers to do everything by and themselves so we can focus on other tasks like helping the community for instance or writing open source tools yes this is a good addition yes so how do we check the performance of the queries this is a very good question first of all we have this sorry it was too fast we have this tool that allows you to view the currently running queries and for how long are they running and also some other metrics I think it's somewhere in beginning yes here yeah it's also open source on our github and when it comes to testing we just we usually test try to test big data select big statements that extract data on the same data set we use in production so we try to we don't have some profiling tools I mean there is a profiling tools for PLPG SQL but we only recently started using it so it's not very very widespread in our company but we try to provide developers with adequate data sets so we periodically get the data from the production and populate the staging environment with it obfuscating it a little bit because of the regulations and then the developers can test their data sets and this is how we make sure that the queries perform adequately and we also have this PLPG SQL profiler as I mentioned okay any more questions okay then I will be here and if you have any questions about our process about these tools or you have any suggestions on these on other tools that you use then I will be happy to talk to you afterwards so thank you very much


All right everybody, how are we? What is going on? Welcome back to another video. In today's video I'm coming to you as a rugby player sitting here in Australia from New Zealand Absolutely love rugby. I love everything about it. I don't follow it as much as I probably used to but you know At the end of the day a rugby nut is a rugby nut just like a football nut as a football nut And if you followed football religiously from age 5 to 15 doesn't matter if you're 25 35 45 or 55 You won't have forgotten what you learned and that's how I feel about rugby today. We're gonna crack back into the reactions It's Christmas Day, believe it or not. I've got my little tree over there with a few presents waiting for my daughters to come around But before they do I'm gonna get some of these videos out of the way And we're gonna crack into it with none other Than the beast Saquon Barkley. What actually is his nickname? He should be called the Beast. He really should he's he's six foot one He's 230.  I mean this guy is an Athletic specimen and he's only at the very beginning of his career And I feel so blessed and so lucky and so excited to watch him over the next few years build up to his peak his peak as a running back and You know become a Hall of Fame running back. I believe that's gonna happen I believe we've got I believe we've got at least three running backs in the moment that will become Hall of Fame running backs and They're my favorite ones to watch and they are in no particular order Saquon Barkley Christian McCaffrey and Leonard Fournette. There are plenty of other good ones I mean Derek Henry is very interesting the fact that he's like six foot three. I love seeing him play You've also got Alvin Kamara, who's absolutely killing it. You've got Josh Jacobs who I like don't make me rack my brain now But today today's not about any of those it's about Saquon Barkley And he is probably the top racer running back at the moment So we are gonna look at a few videos today, but the first thing we're gonna do is check out wikipedia I want to see how his season has gone. I believe he's had an injury an ankle injury Which he was out for a fair few weeks But all I've been able to find on YouTube is his highlights for week one week two Now he scores a touchdown in his return from injury and he has his first hundred yard game since week number two So I've got four videos of his highlights this year to watch and we're gonna get into that, but first let's check wikipedia Okay I'm hoping you got a thousand yards this year, but I'm not sure I'm hoping So let's find out He is 22.  It's gonna really annoy me 233 pounds went pick number two In the 2018 draft after a successful rookie campaign Barkley was ranked number 16 on the top 100 players of 2019 Yes, he was Barkley was chosen by his teammates as one of the seven team captains for the 2019 season and Honour really bestowed on players after only one year on the team and week one against the Cowboys He rushed 11 times for 120 yards including a 59-yard run in this game He fumbled the ball for the first time in his career Which is crazy because he played all 16 games the year before and never fumbled at once in week two against the Bills He rushed 18 times for 107 yards in a touchdown and also caught three passes for 28 yards in The 28-14 loss in week three against the Buccaneers. He suffered a high ankle sprain It was expected to miss four to eight weeks. He made his return in week seven against the Cardinals Yep, Sakeman Barkley scores one touchdown in return from injury So he must have scored a touchdown in the game Barkley rushed 18 times for 72 yards and a touchdown in Week eight against the Lions. He rushed a season high 19 times for 64 yards Which is very interesting because that's not a hell of a lot of yards per carry In fact, I'd love to see what those rushes were like because the defense must have really stepped up And caught a season high eight passes for 79 yards and a touchdown in week 15 against the Dolphins He rushed 24 times for 112 yards and two touchdowns caught four passes for 31 yards in week 16 He rushed for a career high a hundred and eighty nine yards on 22 carries and caught four passes for 90 yards and two total touchdowns.  We've got to have a look Okay, this is what we want. Sakeman Barkley's monster game two touchdowns and all highlights Hell yeah Actually, that's probably not the best one. I think we should have a look at the NFL touchdowns even though I think we should have a look at the NFL footage even though It's not going to be monetized Unfortunately, okay, so we got Barkley against the Cowboys week number one Barkley against the Bills week number two Barkley comes back from injury week number seven against the Cardinals Barkley in week number 14 or 15 against Miami and Barkley in week number 16 Against the Redskins. We haven't got the statistics in For the full year yet. So that's okay. So last year. He got 1300 yards. How many yards has he got this year? I love me some stats. You guys know that he's played 12 games this season. He must have missed four 200 attempts 911 yards. No No He didn't make a thousand yards I'm gusset I'm absolutely gusset. Oh well It's okay five touchdowns longest run of 67 yards. Well, you know, he played 12 games What do you expect if he had to play 16 games? He definitely would have got a thousand yards Let's give it to him anyway Okay, guys first video of the day. Let me cast your minds back if you will to September 8th 2019 cast your minds back if you will to 1972 or thereabouts and spot the tune Barkley versus the Cowboys Fakes it Thumbelder I was gonna say how did okay. Okay. I don't really I don't quite get this So Barkley Barkley breaks through Okay, but which linemen brushes off his defender and starts running down the field as his blocker It's 71 allowed to do that. I don't know that linemen were allowed to travel down the the field like that It worked but he fumbled it straight on the helmet Can't do much about that man, that's having a full full-sized helmet come through your grip rather than just a hand or a few fingers Or a fist it's gonna pop the ball out. Unfortunate mate See I'm coming into these games not having watched anything of this season and it's like What have I been doing first career fumble in 353 touches Imagine how he felt or is this this is the place straight after okay. Let's see how he responds Watch this He responds motherfucker. See ya. See ya.  I mean that's I knew it was gonna happen I I could have predicted it and lo and behold the same drive first drive of the game. We're here second and six Ten yards out. What are you gonna do? Tuck him down First drive of the game still Actually, I just saw Leonard for net do a nice counter Exactly like that and really bang his foot in it was very effective, but that one wasn't let's watch it again See you bet the first offender if he had of just pinned his ears back and gone right for the corner right for the Pylon he probably would have got there Stay on your feet Stays in his feet so well And Honestly, I'll tell you second Barclays legs don't look ridiculously huge right, but they are Nice catch Against the defense Yeah, I Like this commentator I really do I like all of the commentators actually I'm trying to rack my brain as to what's who's my favorite, but I couldn't say I'm sure he says it to every crew that's calling their games and he really is and he's just he's a special special But no one knew whether he's gonna be able to continue his college form into the NFL and and lo and behold He had 353 touches for for 1300 yards and no fumbles No You can't Interesting get him rolling, mate.  Why aren't you why aren't you looking man? That seems strange and it would have been very very effective if it had it been further down the field too Counter beautiful. Keep going Yeah, wow, all right, yeah, let's see that let's see that again. I Want to see that how close did he get to the ground man? Nice counter though, isn't it? I love that move I really do and I love it ever since I saw Christian Wade get given the ball in that preseason game for the Buffalo Bills And he did not do a proper counter bang So he beats him he could easily get past this guy This guy this fella is coming across to to make this block on him So Barclay could have bounced out to the right. This fella would have probably got taken This guy probably would have got taken if the wide receiver does this job and this fellow van de Esch I think it's been dish is Gonna come through and try and take him out Okay, okay, so so this guy didn't actually block this man.  There's enough force in there to start ripping one of these jerseys. You kidding? How does that even happen? Does something cut that or is someone just grabbed it? With the superhuman like strength. I love that run Reminds me of Adrian Peterson mixed with Leonard for near little spin on the end Beautiful There he is See it have to be it have to be he'd have to be upset with that that's two drops now And that is the point there. I'm very excited when I see someone like Barclay, especially do that swing route And get the ball with open space because honestly It's like an artist with a blank canvas He plays what's in front of him and that is the most exciting thing because then tell you what? He had to grab that ball on the burst.  Of course they do if Barclay's out there if he's the captain of the offense He's gonna make that happen But even still some of these passes are just Oh, I mean that's that's that's like a beautiful rugby pass right out in front Making sure the man runs onto the ball the ball's not behind you You don't have to stop and start again. That is actually a beautiful pass. He runs on to that with the most pace possible Let's see what happens next Not much You know, it's a bit That's a bullet pass. He's only about ten yards away We trying to do to him He fumbled again So what's that? That is a draw play. Is that right because it looks like a pass and then at the last minute Barclay gets the run. I Love the draw play. I do go Barclay I Love the counter and I love and I love the draw. I gotta say but that was video number one for the day All right second video second sake one video is going to be against the bills Plus a look at his injury, which was in week number three Following that our third video is going to be his return from injury scoring one touchdown and then having his first 100-yard game since week number two Following that we're gonna have our last video of the day, which is gonna be his highlights versus the redskins and We'll probably include something else as well So stay tuned guys if you have enjoyed this video if you do like sake one Barclay if you like my content Hit that fucking like button and that's all I gotta say Merry Christmas everyone.


Okay, so here on a heating issue call, we've got us a 2015 unit here that's three tons of cooling slash heating, and they're saying it's not heating. I can feel just a slight warmth there on the discharge slash hot gas line, and I checked the air filter and it's fine. So let's go pack in here and grab the tools and see what we can find out's going on. So basically, filter's fine, fans running, so why the electric strips didn't come on, it's beyond me. Got a little bit of a shake going on there. Okay, let's, I'm going to put my gauges on it honestly, it's going to jump right to it. Just really no, no real warmth coming out of that thing, so let's put the gauges on and see what we get. Alright, we're going to take off the hat here, it's not, not really good for keeping my ears warm when we've got the snow. Outdoor temperature today is probably about 18 degrees I bet, something like that. Let's see what we've got going on here for pressures, I can just about guarantee you from my experience that it's low on refrigerant, like bleeding it that way, that way I know I'm clean up here. Alright, so let's see what our chart says here, alright it appears it's gotten a little warmer out here, it's 25, so, and of course, you know that's just the way it rolls, that's the way it rolls. It's going to defrost right when we're ready to get started, got to love it, got to love it. So this unit is a 36, like I said, so indoor temperatures in the 70s and outdoor temperatures between 20 and 30s.  What did we do here? I just taped it kind of together-ish, is this two stages, is it? It sure looks like it might be, it's building a little bit of pressure there, I don't want to jump the gun too quickly here, but like we said, we should be somewhere in that 300 to 325 mark area, and then somewhere around the 80 to 60 suction, yeah we're off a little bit there, curiosity, see what our sub cooling is, sub cooling technically is 23, that's going off the liquid line there, which liquid line is running 60 degrees, coming back from the evaporator, and we got 83 there, so that's 23, huh, it's not extremely low if that is what's wrong with it, it sure is seaming that way, that's a lot warmer than what it was, I wonder if we got something weird going on here, can't help but wonder if we had something to do with the thing going into defrost like it did, and usually that defrost control I think closes, it's normally open, so closes when it starts to count, it's going to open when it's warm again, so drop of temperature, see how we did right by yanking that, that kept it out, so usually with like a Linux board, if I had the speed up terminals it would have kicked right out, but evidently not with this one, so let's just run a little longer here before we jump in to add anything to it, which I hate charging these things in the winter, but like I said I bought guarantees as we scan that evaporator, we're gonna find a leak, I guess one of the things that kind of bothers me is why the electric strips heat didn't kick on and bring the temperature up, so we probably got more than just a refrigeration issue, which is kind of concerning, let's go ahead and see what our actual discharge temperature is off that evaporator, so I finally found one of these in duck work somewhere, and they're kind of slow about getting to the temperature, holy crap, everybody made fun of my mom that I normally use because it's been a little bit, there's reason why, slow that thing is, and why am I getting 58 degree air coming back through here, I mean it's got a lacious amount of cold air coming back to the furnace, 58 degrees, I mean it ain't no 58 degrees inside the house, they got a wood burner running in there, so 58 degrees coming there, let's see what we got up here, let's just see what we got, it's probably why the freaking pressures are so flipping low, all right so we're up here in the attic, you can see right there some of the plastic is ripped off that return, a lot of it actually, what the world's going on with that, it goes down there, so we have to follow it, which is not really easy to do, but yeah this insulation crap's got some issues, that's the return air, I mean that's that's not very good, I don't know who sliced that thing, or if I just getting brittle, but that's not, that's not good, not good at all, there's another one for return, it's all chimichangud, that right there don't look good on the side, it don't look like a good 90, well it's gonna be real good to get down in there, not fall through the ceiling, so I can't do that with the phone, so we'll get over there and see what we can find, all right just looking at how they did this duct work, there's no way our people did this, this must have been existing, so this is some hack-looking stuff, you can tell the cheap stuff they used just deteriorated, it's completely missing off that, I've never seen anybody run it round to the end and then split off the end of a round with ducts coming in, so this is return here and there, oh that's great, look at that, magical wires up in the attic like that, that's nice, they don't really have that much insulation up here either, you can see the return there, I crawled all through there with all that sheathing, which I know ain't a lot of our value, but it ain't helping it, none not having it, but I mean all the duct work muzzles, if you're gonna fix some of it, muzzles replace all of it, it's bad shape, they really can use some new insulation, more insulation up here, this is like cardboard cellulose stuff, they do not have that much up here, so I don't know what all I can do to help out with that, I don't carry enough to do a whole house on the truck, so go ahead and crawl back over there and see what we can do with this thing, make sure it's working as good as we can get it, all right, but the only thing I can do with that is tape the thing back up, so at least isn't completely losing all of its R value, but that there, none of it's split open, even though it's missing it there and there, if you guys think it makes a humongous difference, I'm sure it don't hurt, but I mean I don't feel no air leaking out through it, but you guys leave a comment down below, but one thing I can do for right now is seal that, I don't think that's the, let's give me an extra 10 plus degrees of gain in the return, but go ahead and get that done real quick, you can see how much that ate up and that's what it looks like which ain't pretty, but at least holding the insulation together, not just the spiral wrap just being shown, so I don't see if it makes a difference, but I'm telling you, good chance it wouldn't be a bad idea to replace all of it, but you know what kind of money that's gonna cost, so it is what it is, gonna go ahead and go down there and see what we can do with this, all right, I think it was 50 something for me at 61.3, notice this isn't insulated from what it sounds like, at least that is, and when this door is shut in here, it probably keeps it a lot warmer in here, the present is bad, but that's not nothing to write home to mama about, you can tell electric strips are probably on, all right, so now that we know the incoming temperature is more like 60, we'll come over here to the 30 and 20 mark, should be between 262 and 286, so we aren't off by much, unfortunately, I'm gonna say he's just, he uses a wood burner most of the time, so it could be as high as 286 if you wanted to go with the 30 degree mark, 65 on the liquid temperature, 85 for the condensing temperature, 19 degree subcooling, not too far out of line, what I'm guessing, so I don't think that's gonna make a big difference, we could try and hook in some of the strips so they're not running, see what the actual true rises across it, I would estimate probably today, you'd be lucky to get somewhere between 14 and 17 degree rise, all right, so we're checking them right here, and once again, as always guys, don't do this if you're not trained, not responsible for you killing yourselves and getting electrocuted, these are for entertainment purposes only, so all my strips are working, I just basically covered door up, see what we got, so we got basically, looks like 15kW there, yep, that's what we got, so electric strips are working just fine, wiring looks okay, there's our speed settings, comfort is what we got there, 10,000m, strip heat there, pretty much made this thing brain dead, can't even stand on low, so I mean it's wired right, set up right, the fridge is pretty darn close, I'm gonna say there ain't nothing wrong other than the poor duck work, everything looks pretty good, yeah so we're running 110, 111 up there, was 60, so 70, 80, 90, 100, just 40, there's 51 degree rise, so the extra strips must have came on, go out and check our pressures again, see where we're at, I don't like going off pressures only, we can give it a little squirt but I don't think it's gonna do much, don't think it's gonna do much at all honestly, especially with the sub-cooling looking like it's about there, you really aren't supposed to charge these in the winter time, it, what's this normally run for sub-cooling, 13 for a for summer time cooling, we're running about 20 and heating which they never usually tell you that, but sub-cooling, sub-cooling in all reality kind of gives you a good idea if you do it more often, so we'll weigh in maybe a half a pound, something like that, see where that puts us at, I don't want to go extraordinary here and go stupid, this beast probably holds a crud ton, yeah 13.7 pounds, I'd be kind of tempted maybe to check that evaporator for leaks first, let's do that for precautionary reasons first, this is 2015, that's an aluminum coil, I haven't had too many leaks on these, have had leaks on the TX-V, I'll do a scan on it real quick, but this is not the one I was thinking, so it's probably fine, I'll do a scan real quick, let it build up some residual refrigerant if it's leaking any, all right so we made some changes in there, the thermostat was set up for three degree differential for the auxiliary heat, they had I think auto mode for first stage to second stage, I set it for 10 minute minimum or maximum runtime before it up stages to second stage, basically left the auxiliary at three degrees, changed it to the setting where anytime it's below 40 degrees, carrier has an option where you can shut the fan G signal off and it'll lower the fan speed so that you get better heat out of it when it's colder outside anytime below 40, that will get you a little bit better air which obviously it's already doing some of that now, so right now we should be on first stage and we're at 70 ish so second stage should kick in here in less than 10 minutes and then it'll still be a three degree lag for the auxiliary heat to kick on, customers are aware of what's going on and he just basically said as long as he knows what's going on and what to expect that you know he'll deal with it when he gets back from vacation, there's still about 61 and a half there, we're 73 here, still waiting to see whether or not first or second stage just kicked on yet which should be or real close to it here, so right now it's kind of sad, I mean 61 and a half minus 73, about 12 degrees, so not 11 and a half not real impressive, I would say we're definitely in first stage, look at that head pressure it's only at 235 and we had a little bit of a leak when I walked away, my hose is leaked around the fitting there, hopefully that's didn't lose that much, you can see we got quite a bit of oil which is great, let's see if there's some cooling in, yeah it's only nine, I don't have the suction probe hooked up because it's not worth checking right now, give it a couple more minutes it should jump up to high stage, it must have just changed, you can see the pressure is jumping up pretty quicker, stage two is now on, we're still at 61.5 with 81, that made a little bit of difference, that's a lot better than what it was doing, so combination of the fan speed because that's raised my head pressure a little bit, so if you subtract those two, 61.7 minus 81.1, let's just round it to the nearest degree, let's just say 62 minus 81, it's about 19 degree rise which is pretty good when it's 25 degrees, 23 degrees outside, a lot better than what mine's doing, so I think we're gonna go ahead and leave it there, I gave them a little bit of a squirt just to make up for what I lost when my hose is leaked and I think that's gonna wrap this one up, basically make sure that you guys check all of your supply and return temps, you know going off to supply only would have screwed me if I would have not noticed that the return air was warm, I sometimes do that more often when I'm in a situation out here where it's a garage and the attic is where the ductwork has to go through, so it appears to me that it's working pretty good and always double check your thermostat to make sure the programming on it's set correctly, so it's gonna wrap this one up guys, just be aware of your surroundings and always keep looking until you find all of the problems, if you guys enjoyed the video you know what to do, until next time we will catch you on the next one