"versionable": some version control system is used (such as Git)."reversible": it is possible to "undo" any change, returning to the original state of the schema (and data which, in some cases, may be difficult or impossible).
"incremental": changes are performed in steps.In this article, I'm going to talk about the second meaning of the term – DB schema changes having the following properties: The term "DB migration" may be confusing it's often used to describe the task of switching from one database system to another, moving the database, and minimizing possible negative effects (such as long downtime). 4-byte integer primary keys for large tables
Waiting to acquire an exclusive lock for long ⇒ blocking others) Acquire an exclusive lock + wait in transaction
#ONLINE TOOLS TO GENERATE DBSCHEMA FULL#
I advise you to read GitLab's great documentation – their Migration Style Guide is full of wisdom written by those who have experience in deploying numerous Postgres schema changes in a fully automated fashion to a huge number of instances, including itself. But I'm pretty confident that you'll encounter most of them when your database grows to ~10 TiB in size and its load reaches ~10 5–10 6 transactions per second (of course, some cases will be seen – unless deliberately prevented. Some cases discussed here are hard to notice in small databases with low activity. Most cases discussed in this article can be easily detected by such testing, and it can be done automatically in CI/CD pipelines.Īs usual, I'll be focusing on OLTP use cases (mobile and web apps), for which query execution that exceeds 1 second is normally considered too slow. With it, you can clone a 10 TiB database in 10 seconds, test schema changes, and understand the risks before deployment. Moreover, a strong desire to help people avoid such mistakes led me to invent the Database Lab Engine – a technology for thin cloning of databases, essential for development and testing. Here I'm going to share what I've learned, describing some mistakes I've made or observed – so probably next time you'll avoid them. My personal score: 1000+ migrations designed/reviewed/deployed during 17+ years of using Postgres in my own companies and when consulting others such as GitLab, Chewy, Miro.
In my opinion, both buzzwords are an oversimplification, but if you have experience in reviewing and deploying schema changes in relational databases, you probably understand the level of difficulty, risks, and pain of scaling the process of making schema changes. We all remember how MongoDB entered the stage with two clear messages: "web-scale" (let's have sharding out-of-the-box) and "schemaless" (let's avoid designing schemas and allow full flexibility). This is one of my favorite topics in the field of relational databases. I enjoyed that article, and it inspired me to write about "anti-features" – things that everyone should avoid when working in probably the riskiest field of application development – so-called "schema migrations". In his article "Lesser Known PostgreSQL Features", describes 18 Postgres features many people don't know. But how many times did we step on the same rake? 'rake db:migrate' – a command known to any Ruby developer.
0 kommentar(er)
