Ask HN: Running UPDATEs in production always feels heavier than it should | Hacker News Reader

Ask HN: Running UPDATEs in production always feels heavier than it should

4 pointsLucy_Bai4 comments

I’m curious how many of you have felt this:

You’re in production. The change is “simple”. A small UPDATE or DELETE with a WHERE clause you’ve read over multiple times.

Still, right before hitting enter, there’s that pause.

Not because you don’t know SQL. Not because you didn’t think it through. But because you know: •If this goes wrong, it’s on you •Rollback isn’t always clean or instant •And the safest option is often… “don’t touch it”

In reality, I’ve seen people deal with this by: •Manually backing up data “just in case” •Having someone else stare at the query with them •Restricting who’s allowed to run anything at all •Or simply avoiding fixing things directly in prod

I’m not asking for best practices or tooling advice.

I’m genuinely curious:

What do you personally do, when you have to change data and can’t be 100% sure it’s harmless?

Is this just an unavoidable part of working with production databases?

That fear is good, it means you know that you're taking a risk - I'd worry if you had to touch production and didn't feel that fear.

If you only have the one or two production systems/databases, and you're having to do this more than a couple of times each year, then you could certainly benefit for improving your tooling.

But if you've inherited a large number of production systems, each with their own local database instance. Then implementing new tooling will be a long process that you might not be able to get Management to agree to.

When I have to touch a production database, I usually use the following steps:

0. Follow your organisation's Change Processes (i.e. if you need to put in a change request and get approval then do that)

1. Snapshot the server itself (VMs are great for this)

2. Backup the database (just be careful not to leave that backup anywhere open, and remember to clear it up when you no longer need it)

3. Write the SELECT version of your SQL first (If you need to delete a record from the userSessions table with an ID of 123, then start by writing

  SELECT * FROM userSessions WHERE id=123;

4. If that shows you just the rows you'd expect then convert it to the delete from of the SQL (making sure that you don't change any part of the WHERE clause)

  DELETE FROM userSessions WHERE id=123;

5. Get someone else, who also understands the database, to check your SQL

6. Run your SQL

7. Do all your sanity checks to make sure that production is working as expected.

8. If it does go wrong then you have more ammunition to use when trying to convince Management to spend some resources on improving your tooling for these sort of changes.

This resonates a lot.

I’ve seen teams handle this exactly the same way — snapshots, SELECT first, second pair of eyes, manual checks.

Out of curiosity: at what point did this stop feeling “rare but scary” and start feeling like “this is happening often enough that the process itself becomes the bottleneck”?

Was it: – number of production systems – frequency of fixes – or cross-team coordination cost?

I’m trying to understand where that line actually is in practice.

I apply "better practices and tooling", advice you are not looking for ;)

yeah definitely don't ever touch production like that, use best practices, and tooling

my 2c

Reply on news.ycombinator.com