 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