I've been there before. I recently came across Jamis Buck's talking about his experience https://www.youtube.com/watch?v=71suekjBV9Y and I strongly recommend it. If only to hear someone eloquently talk about going through the process.
Previously, I saw his talk about generating mazes and it has always stuck with me. (Unfortunately that one wasn't recorded). Speaking as a developer, I have been writing code and solving problems professionally for so long that I have forgotten what it's like to just write code for fun. For me, and not for anyone else. Without any expectations or stress. And listening to him talking about rediscovering fun by exploring different ways to create mazes really clicked with me.
I've only recently gotten back into that, and it has been embarrassingly awkward to knock off the rust and put myself in the mindset of "Let's just do this because it could be cool".
I even bought his book on generating mazes. Not because I'm particularly into them, but to help exercise those sore muscles and I'm thinking I can perhaps feed on his passion. A coworker told me he also put one out about building ray tracers. To branch out beyond that, I've also begun putting together a list of fun hacks that interest me personally.
Now I say all of this, but I do think while time away from your day-to-day is very important (take some vacation time! spend time exploring other hobbies!), even after a significant time away, when you come back to your day job you may still be unmotivated/numb/demoralized. I'm hoping rediscovering the fun in what I do helps put things in perspective more than just listening to others preach. Helps rekindle the feelings and reasons why I pursued my career in the first place beyond just making a living.
Good luck. I wish you the best.
Edit: OH! I completely forgot. Make sure to prioritize the easy-to-ignore-things. Spend time with friends/family. Exercise. Make sure you're getting enough sleep.
Those sound so simple, but I strongly believe it's incredibly tough when you ignore those basics.
There is a big difference. You're only considering the "slowness" from the application perspective of querying a table without the index.
You need to also realize that database server itself takes a load hit when it kicks off these operations on large tables. I'm not sure on PostgreSQL, but I know sometimes you can not immediately cancel these operations in the middle on MySQL (it also takes time to revert).
With gh-ost you have full control over how fast this process goes and can even pause/resume it if you're experiencing issues.
But let's say it IS instantly cancelled. You're also ignoring the fact that you will take the same hit on any replicas if this statement is successful.
Some production environments don't take kindly to having all replicas lagging as the replication thread is blocking on the DDL change. My team has the luxury of being able to have our entire production environment served by a single master server (though we avoid it as much as possible), but it won't be long before we outgrow that and require at least one up to date replica. Many teams are already in that situation and for that, gh-ost is a godsend.
However, it can take a while if MySQL is evaluating the consistency. But you can disable that with `SET FOREIGN_KEY_CHECKS = 0` which turns it into a metadata change (nearly instantaneous).
You still will need to check for violations, but you can do that in a more friendly-to-load manner, and of course will need to deal with any violations manually.
But that strategy is a good middle ground to all-or-nothing FKs.
Edit: Whoops, looks like I was wrong on the table-copy part. Per "Otherwise, only the COPY algorithm is supported." So it does copy the data when `FOREIGN_KEY_CHECKS=1` (the default)
We use gh-ost at Harvest[1] and it's a dream in comparison to manually migrating on a replica and switching master/slave roles [2].
Also the linked post[3] in the readme hit us very close to home. We originally tried some of our migrations with pt-online-schema-change, which was great in theory but caused a lot of locking contention during the actual process.
I see many people hammering on the lack of foreign key support which is interesting to me. At some point, a database system grows to where relying on MySQL's Online DDL[4] "works" but not really with production load. I feel like a team knows when they need to bring in a tool like this.
The dev in me understands how wonderful FKs are for consistency. But the db-guy in me that has had to deal with locking issues recognizes FKs as a tradeoff, not dogma.
If you shy away from migrating your large or busy tables, or are scheduling frequent maintenance down times in order to migrate these tables, that's when gh-ost (and others) are appropriate to evaluate.
So for us it's not an immediate red flag that gh-ost doesn't support FKs. We just have to work around that limitation[5] because the alternatives are much worse.
For the record, we don't gh-ost all of our migrations. Only the ones that are deemed sufficiently large enough are gh-osted and those heuristics will change from team-to-team.
But as a guy who has had to deal with our database issues AND as a developer who doesn't want to be chained by a database design decision from a decade ago, I love the flexibility gh-ost gives us as we continue to grow.
I don't work for GitHub but we had the same problems using pt-online-schema-change independently of their issues (lots of locking contention affecting the app negatively). We're finally moving to gh-ost for our large/risky migrations and so far it's amazing.
Previously, I saw his talk about generating mazes and it has always stuck with me. (Unfortunately that one wasn't recorded). Speaking as a developer, I have been writing code and solving problems professionally for so long that I have forgotten what it's like to just write code for fun. For me, and not for anyone else. Without any expectations or stress. And listening to him talking about rediscovering fun by exploring different ways to create mazes really clicked with me.
I've only recently gotten back into that, and it has been embarrassingly awkward to knock off the rust and put myself in the mindset of "Let's just do this because it could be cool".
I even bought his book on generating mazes. Not because I'm particularly into them, but to help exercise those sore muscles and I'm thinking I can perhaps feed on his passion. A coworker told me he also put one out about building ray tracers. To branch out beyond that, I've also begun putting together a list of fun hacks that interest me personally.
Now I say all of this, but I do think while time away from your day-to-day is very important (take some vacation time! spend time exploring other hobbies!), even after a significant time away, when you come back to your day job you may still be unmotivated/numb/demoralized. I'm hoping rediscovering the fun in what I do helps put things in perspective more than just listening to others preach. Helps rekindle the feelings and reasons why I pursued my career in the first place beyond just making a living.
Good luck. I wish you the best.
Edit: OH! I completely forgot. Make sure to prioritize the easy-to-ignore-things. Spend time with friends/family. Exercise. Make sure you're getting enough sleep.
Those sound so simple, but I strongly believe it's incredibly tough when you ignore those basics.