liyanchang

Yeah. I’d love to see the academic paper with formalizations that help me understand the true scope of this problem. Your example is a great one that prompts many questions. Is it possible to travel directly to the commit o(1) or will the code have to calculate the diff of each commit and apply them one at a time o(n) and how much definition and dependency mapping humans need to do to have it work correctly?

I’ll openly admit that we don’t have everything figured out. You’re absolutely right that currently, we constrain ourselves on what we migrate to admittedly simple migrations.

I think there’s a whole set of problems to be solved in this space and frankly, I’m as surprised as anyone that given how SQL is declarative, we use procedural code to do migrations so part of my post was hoping people would tell me what tool I should be using or how this approach fails over time. So your examples are really helpful for me as I think through if it’s possible to do automatically, workaround, or get by without.

It seems to me that we just lack the ability to express these transitions mathematically that can help us do them. And of those, there’s probably only a subset which are possible to do without taking downtime.

In particular, the class of migrations that you outlines are a combination of DDL and DML changes and also have quite a bit of code complexity to do without downtime. It’s definitely a current weakness.

I’ll openly admit that we don’t have everything ironed out. In fact my next big project is to tackle derived columns (rename is a column where the transformation is the identity function).

It requires a bit more finesse and integration into our code base as it requires multiple deploys with code that knows how to handle both columns.

That's correct. The DML changes as part of CI are somewhat new so we haven't ironed it all out yet.

Here's the scenario that I think you're laying out: 1. Commit A creates column foo 2. Commit B has DML that reference column foo 3. Commit C removes column foo

This works fine if our CI deployer does each commit individually. First roll out any schema changes, then run any DML SQL.

However, our deployer might pick up all those changes and since we roll out the schema migrations first (in this case a create + drop -> NOP) and then runs the DML (which will error), this is an issue because of the rollup.

In practice, we have yet to see this case (most of the time, the dev who write the DML is close enough to the code to know if it's going to be dropped soon and we don't drop that many columns - in part because we know that there be dragons) but truthfully, I haven't thought about it much and need to think through what the impact is beyond this example. Thanks for helping me refine my thinking and I'll have something to ponder on this weekend!

This is good to know. As someone who didn't do much with databases before, I was frankly worried given how it didn't seem like many others were taking this approach when it made so much sense (we did have the advantage of having a defined schema which I know isn't always available). Seems like I just didn't know what to search for.

Git would never have worked it required devs to write the up/down patches - why should we have to write the up/down migrations for my schema?

Excited to see more tooling around declarative schema!

Disclosure: I'm an engineer at USDS and these are my own opinions.

I'm personally appreciative of the work that you've done and your continued work, in spite of the many challenges and frustrations. There are many ways to serve. It's always incredibly rewarding to be working along side dedicated and talented public servants and contractors. Thank you.

Thanks. I appreciate that. I think you actually have a keen eye for the issues.

Since my time in government, I've become more sympathetic to agency employees, contractors, management - pretty much everyone. The entire environment is really challenging to do the right thing.

As an executive, the typical tools you have at your disposal, budget and HR, are circumscribed by congressional appropriations and federal hiring guidelines. Your ability to promote and demote and reorganize are limited by laws and unions and you definitely can't grant stock options. The metrics that you typically use to measure success are much fuzzier because there isn't a PNL but it's really easy for the press to find a single case where your org does a poor job.

I still maintain hope that good things can be done - I have seen it - and good management makes a huge difference.

Disclosure: I'm an engineer at USDS and these are my own opinions.

Disclosure: I'm an engineer at USDS and these are my own opinions.

We are very proud to be working along side the existing agency and contracting staff. You are right that many times, they do know how to fix it and need some help from us getting management to let them do it. Some times they are skeptical, sometimes there is friction - that's all completely understandable.

Ultimately, my time in government has opened my eyes to the talented folks inside government right now and I'm proud to be working along side them. I hope you didn't get a poor impression of us and the work that is happening.

Disclosure: I'm an engineer at USDS and these are my own opinions.

I absolutely agree that the interface that most citizens use to government can be better. President Obama agrees with you (from his sxsw remarks):

"I could change the politics of America faster than just about anything if I could just take control of all the DMVs in the country."

"If their primary interaction with government is the IRS, you just don’t have a good association with government when you’re writing that check."

USDS has been working with the IRS to help them get their Transcript service back online with better identity proofing though admittedly just a small step in a better online experience. (irs.gov/transcript)

A number of state and local governments have been making improvements as well (much harder to see if your state or your local government has not been). I'd encourage you to search them out or even start it.