5 ms·

> rust-query manages migrations and reads the schema from the database to check that it matches what was defined in the application. If at any point the databas

by mjr00 2y ago

> rust-query manages migrations and reads the schema from the database to check that it matches what was defined in the application. If at any point the database schema doesn't match the expected schema, then rust-query will panic with an error message explaining the difference (currently this error is not very pretty). IMO - this sounds like "tell me you've never operated a real production system before without telling me you've never operated a real production system before." Shit happens in real life. Even if you have a great deployment pipeline, at some point, you'll need to add a missing index in production fast because a wave of users came in and revealed a shit query. Or your on-call DBA will need to modify a table over the weekend from i32 -> i64 because you ran out of primary key values, and you can't spend the time updating all your code. (in Rust this is dicier, of course, but with something like Python shouldn't cause issues in general.) Or you'll just need to run some operation out of band -- that is, not relying on a migration -- because it what makes sense. Great example is using something like pt-osc[0] to create a temporary table copy and add temporary triggers to an existing table in order to do a zero-downtime copy. Or maybe you just need to drop and recreate an index because it got corrupted. Shit happens! Anyway, I really wouldn't recommend a design that relies on your database always agreeing with your codebase 100% of the time. What you should strive for is your codebase being compatible with the database 100% of the time -- that means new columns get added with a default value (or NULL) so inserts work, you don't drop or rename columns or tables without a strict deprecation process (i.e. a rename is really add in db -> add writes to code -> backfill values in db -> remove from code -> remove from db), etc... But fundamentally panicking because a table has an extra column is crazy. How else would you add a column to a running production system? [0] https://docs.percona.com/percona-toolkit/pt-online-schema-change.html https://docs.percona.com/percona-toolkit/pt-online-schema-ch...

threeseed 2y ago
> Even if you have a great deployment pipeline, at some point, you'll need to add a missing index in production fast because a wave of users came in and revealed a shit query. This sounds more like a CI/CD and process issue. There is no reason why adding a new index in code and deploying it into Production should be more complex or error prone than modifying it on the database itself.
- mjr00 2y ago
  Direct execution of `CREATE INDEX...` on a database table is always going to be faster than going through a normal deployment pipeline. Even if we assume your pipeline is really fast, which is probably not the case at most orgs, you are still comparing a single SQL statement execution, to a single SQL statement execution + git push + code reviews + merge + running through Jenkins/Circle/whatever. How long does that overhead take? How much money have you lost because your website won't load when your post is on the frontpage of HN? Seconds and minutes count. I don't want my code crashing because an unexpected index exists in this scenario.
  - threeseed 2y ago
    You should be able to deploy end to end to Production in less than a minute. Companies should be focused on solving that problem first before doing insanely short-sighted workarounds like skipping pushing to Git and code reviews.
    - mjr00 2y ago
      > You should be able to deploy end to end to Production in less than a minute. When I was at AWS (RDS) our end-to-end production deployment process was 7 days. We were also pulling $25million/day or so in profit. I'm sure that number is much higher now. There's a large difference between what the theoretical "right" thing is from a textbook perspective, and what successful engineering teams do in reality. edit: besides, it doesn't even make sense in this context. I have 100 servers talking to the database. I need to create an index, ok, add it to the code. Deploy to server 1. Server 1 adds the index as part of the migration process, and let's say it's instant-ish (not realistic but whatever). Do the other 99 servers now panic because there's an unexpected index on the table?
    - kelnos 2y ago
      That's a lovely ideal, but I'm the real world, there are relatively few companies that meet that metric.
      - 2y ago
kelnos 2y ago
It's a bummer that you've been downvoted, because it really does seem like people here have not operated databases at scale. I will never claim that we were great at managing databases at Twilio, but often a schema change would take hours, days, or even a week or two to complete. We're taking about tables with hundreds of millions of rows, or more. We'd start the change on a DB replica. When it would finish, we would have to wait for the replica to catch up with the primary. Then we would bring up new replicas, replicating from the replica with the new schema. Finally that replica would get promoted to primary, with all the old replicas (and the old primary, of course) removed from service, and the new replicas brought in. Only then could we deploy code that was aware of and used the updated schema. The previous code of course had to ignore unknown columns, and if we ever wanted to drop a column, we had to first deploy code that would stop using that column. Any column type changes would need to be backwards-compatible. If that wasn't possible, we'd have to add a new column and backfill it. Adding indexes would usually be fine without preparatory code changes, but if we wanted to drop an index we'd first have to make sure there were no queries still depending on it. Even for a "small" schema change that "only" took minutes or a few tens of seconds to complete, we'd still have to use this process. What, do you think we'd shut part or all of a real-time communications platform down while we do a schema change? Of course not. The idea that the application could or should be in control of this process, or could always be in sync with the database when it came to its understanding of the schema, is impossibly unrealistic.
- mjr00 2y ago
  Yep, sounds like we have similar experiences! I first had to start thinking about this stuff at Hootsuite, back in the exciting 1million+ DAU days a decade ago. Before then, to me databases were just a thing that got deployed along with the application, and deploys only happened on a Friday night so who cares about downtime? By the time anyone tries logging into the app on Monday morning, the code and database will all be up to date. Going to a place where deploys were happening constantly and nonzero downtime was unacceptable was eye-opening. > The idea that the application could or should be in control of this process, or could always be in sync with the database when it came to its understanding of the schema, is impossibly unrealistic. These days my attitude is to treat databases as a completely separate service from the application code, which they effectively are. They're on a different set of servers, and the interface they provide is the columns/tables/views/etc, accessed through SQL. So yeah, no breaking changes, and the only thing application code should care about is if the queries it tries to execute return the expected sets of data, not if the schema itself matches. And certainly not about things like views, triggers or indexes. This does end up being more overhead than migrations alongside the application code, which I know a lot of developers prefer because they're easier to use, but the approach just doesn't work after a certain scale. (to be clear, I still use Liquibase etc to manage migrations, the process for applying those changes is just completely separate from deploying application code.)
jessekv 2y ago
IMO "parse, don't validate" can apply to data coming out of the database too.
clutchski 2y ago
This is the correct answer.

by mjr00 2y ago

threeseed 2y ago

> Even if you have a great deployment pipeline, at some point, you'll need to add a missing index in production fast because a wave of users came in and revealed a shit query. This sounds more like a CI/CD and process issue. There is no reason why adding a new index in code and deploying it into Production should be more complex or error prone than modifying it on the database itself.

mjr00 2y ago
Direct execution of `CREATE INDEX...` on a database table is always going to be faster than going through a normal deployment pipeline. Even if we assume your pipeline is really fast, which is probably not the case at most orgs, you are still comparing a single SQL statement execution, to a single SQL statement execution + git push + code reviews + merge + running through Jenkins/Circle/whatever. How long does that overhead take? How much money have you lost because your website won't load when your post is on the frontpage of HN? Seconds and minutes count. I don't want my code crashing because an unexpected index exists in this scenario.
- threeseed 2y ago
  You should be able to deploy end to end to Production in less than a minute. Companies should be focused on solving that problem first before doing insanely short-sighted workarounds like skipping pushing to Git and code reviews.
  - mjr00 2y ago
    > You should be able to deploy end to end to Production in less than a minute. When I was at AWS (RDS) our end-to-end production deployment process was 7 days. We were also pulling $25million/day or so in profit. I'm sure that number is much higher now. There's a large difference between what the theoretical "right" thing is from a textbook perspective, and what successful engineering teams do in reality. edit: besides, it doesn't even make sense in this context. I have 100 servers talking to the database. I need to create an index, ok, add it to the code. Deploy to server 1. Server 1 adds the index as part of the migration process, and let's say it's instant-ish (not realistic but whatever). Do the other 99 servers now panic because there's an unexpected index on the table?
  - kelnos 2y ago
    That's a lovely ideal, but I'm the real world, there are relatively few companies that meet that metric.
    - 2y ago

kelnos 2y ago

It's a bummer that you've been downvoted, because it really does seem like people here have not operated databases at scale. I will never claim that we were great at managing databases at Twilio, but often a schema change would take hours, days, or even a week or two to complete. We're taking about tables with hundreds of millions of rows, or more. We'd start the change on a DB replica. When it would finish, we would have to wait for the replica to catch up with the primary. Then we would bring up new replicas, replicating from the replica with the new schema. Finally that replica would get promoted to primary, with all the old replicas (and the old primary, of course) removed from service, and the new replicas brought in. Only then could we deploy code that was aware of and used the updated schema. The previous code of course had to ignore unknown columns, and if we ever wanted to drop a column, we had to first deploy code that would stop using that column. Any column type changes would need to be backwards-compatible. If that wasn't possible, we'd have to add a new column and backfill it. Adding indexes would usually be fine without preparatory code changes, but if we wanted to drop an index we'd first have to make sure there were no queries still depending on it. Even for a "small" schema change that "only" took minutes or a few tens of seconds to complete, we'd still have to use this process. What, do you think we'd shut part or all of a real-time communications platform down while we do a schema change? Of course not. The idea that the application could or should be in control of this process, or could always be in sync with the database when it came to its understanding of the schema, is impossibly unrealistic.

mjr00 2y ago
Yep, sounds like we have similar experiences! I first had to start thinking about this stuff at Hootsuite, back in the exciting 1million+ DAU days a decade ago. Before then, to me databases were just a thing that got deployed along with the application, and deploys only happened on a Friday night so who cares about downtime? By the time anyone tries logging into the app on Monday morning, the code and database will all be up to date. Going to a place where deploys were happening constantly and nonzero downtime was unacceptable was eye-opening. > The idea that the application could or should be in control of this process, or could always be in sync with the database when it came to its understanding of the schema, is impossibly unrealistic. These days my attitude is to treat databases as a completely separate service from the application code, which they effectively are. They're on a different set of servers, and the interface they provide is the columns/tables/views/etc, accessed through SQL. So yeah, no breaking changes, and the only thing application code should care about is if the queries it tries to execute return the expected sets of data, not if the schema itself matches. And certainly not about things like views, triggers or indexes. This does end up being more overhead than migrations alongside the application code, which I know a lot of developers prefer because they're easier to use, but the approach just doesn't work after a certain scale. (to be clear, I still use Liquibase etc to manage migrations, the process for applying those changes is just completely separate from deploying application code.)

jessekv 2y ago

IMO "parse, don't validate" can apply to data coming out of the database too.

clutchski 2y ago

This is the correct answer.