Mistakes related to the correctness of steps – logical issues.Acquired an exclusive lock and left transaction open for long.Some examples (skipping details we'll talk about them soon): This is the largest category, usually determining a significant part of an application DBA's experience. I distinguish three big categories of DB schema migration mistakes: Three categories of DB migration mistakes DDL – data definition language ( CREATE …, ALTER …, DROP …).DML – database manipulation language ( SELECT / INSERT / UPDATE / DELETE, etc.).The term "Application DBA" was explained by in "Some SQL Tricks of an Application DBA".įinally, the usual suspects in our small terminology list: However, we need to remember that many schema changes imply data changes – for example, changing a column data type from integer to text requires a full table rewrite, which is a non-trivial task in heavily-loaded large databases.Īpplication DBA – a database engineer responsible for tasks such as DB schema design, development and deployment of changes, query performance optimization, and so on, while "Infrastructure DBA" is responsible for database provisioning, replication, backups, global configuration. I prefer using the adjusted term, "DB schema migration". "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 Three categories of DB migration mistakes.If you missed it, no worries – who has organized the conference since 2006 (thank you, Dan!), promises to publish talk videos in a few weeks. On Thursday, May 26, I'll give two talks, and one of them is called "Common DB schema change mistakes", you find the slide deck here. I also encourage everyone to watch PGCon-2022 – one of the key Postgres conferences this time, it's happening online again. 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 Comments
Leave a Reply. |