Show HN: Safe Data Changes in PostgreSQL | Hacker News Reader

Show HN: Safe Data Changes in PostgreSQL

github.com

47 pointsciminelli17 comments

Hi HN, we're excited to share our open source tool with the community! We previously posted here with the tagline “real-time events for Postgres” [0]. But after feedback from early users and the community, we’ve shifted our focus to working on tooling for manual database changes.

We've consistently heard teams describe challenges with the way manual data updates are handled. Seemingly every engineer we spoke with had examples of errant queries that ended up causing significant harm in production environments (data loss/service interruptions).

We’ve seen a few different approaches to how changes to production databases occur today:

Option 1: all engineers have production write access (highest speed, highest risk)

Option 2: one or a few engineers have write access (medium speed, high risk)

Option 3: engineers request temporary access to make changes (low speed, medium risk)

Option 4: all updates are checked into version control and run manually or through CI/CD (low speed, low risk)

Option 5: no manual updates are made - all changes must go through an internal endpoint (lowest speed, lowest risk)

Our goal is to enable high speed changes with the lowest risk possible. We’re planning to do this by providing an open-source toolkit for safeguarding databases, including the following features:

- Alerts (available now): Receive notifications any time a manual change occurs

- Audit History (beta): View all historical manual changes with context

- Query Preview (coming soon): Preview affected rows and query plan prior to running changes

- Approval Flow (coming soon): Require query review before a change can be run

We’re starting with alerts. Teams can receive Slack notifications anytime an INSERT, UPDATE, or DELETE is executed from a non-application database user. While this doesn’t prevent issues from occurring, it does enable an initial level of traceability and understanding who made an update, what data was changed, and when it occurred.

We’d love to hear feedback from the HN community on how you’ve seen database changes handled, pain points you’ve experienced with data change processes, or generally any feedback on our thinking and approach.

[0] https://news.ycombinator.com/item?id=34828169

Open articleView on HN

Sounds like a pretty great idea, executing queries on production is a necessary but scary flow below a certain company size, until you can afford to be slow about it.

Got a question: presumably this adds a trigger, that adds some amount of extra work to every query, achieving a benefit for the relatively negligible number of manual queries. Makes me wonder about the performance impact of that trigger?

An article about some performance characteristics in the docs would help a lot to assuage that concern.

Inquery co-founder here. Great question regarding performance implications - we're working on an article exactly like you described. Additionally, we're exploring a few updates to significantly reduce the impact to the database: (1) adding the filtering at the trigger level, and (2) using the WAL instead of triggers.

I see this doesn't yet support something I've wanted for some time:

What's the simplest way to snapshot a database, perform an operation in app, snapshot it again and get an overview of how many rows were affected in each table? Like an overview of diff. Optionally list changed pkey ids. I tried googling but couldn't find anything like this, so I was thinking of making a DYI solution but wasn't that desperate yet.

We were looking at tooling for snapshotting production data for testing purposes, that use case is interesting to have the diff view based on changes happening from application actions. Would you use it for testing changes or more for debugging production issues?

Well, my main use case would be to speed up project onboarding where I can play around with the app from user perspective and check how my actions impact database... Could help cutting through a lot of frontend/backend layers and just focus on raw data.

If you have multiple DB snapshots and WAL enabled, theoretically you could inspect the log to see what happened inbetween. There's pg_xlogdump for that but I think it will output very raw data...

How would you want to actually look at the database diff? Just a summary view of rows that were changed given a certain time period?

I'd guess that https://neon.tech/ could help with this. Neon is branded as "Serverless Postgres". They have APIs to create branches of your database.

So you could effectively:

1) Create a snapshot of your production DB -> DB_2A

2) Then create a snapshot of that snapshot -> DB_2B

3) Now you have two copies of the exact same database. Run your query/workload/migration on DB_2B.

4) Run some metadata queries against DB_2A and DB_2B and compare the results.

5) If your metadata queries are inline with expectations, delete the snapshots. If not, leave them around for a bit for manual inspection.

Interesting. Will check it out in detail. From a quick glance tho I'm afraid that vendor-specific cloud is a no-go because I would rather not upload any client's data in there, even if anonymized. Besides, it's an overkill to convince anyone to switch to a different vendor for sake of diffing DB alone :)

Why not use aws rds snapshots to create a new replica in no-time?

I wonder if you could utilize templates to snapshot the DB.

This is what tools like `dslr` do and it's very fast. My problem wasn't backing up/restoring though but rather diffing data in these 2 databases :)

It seems like it should be doable to write something that generically diffs tables.

Dolthub does that https://www.dolthub.com/

Its something every support desk should have. I often write my own tools like this. Ideally write a select query, execute it, note how many rows it selets, copy the where clause, start transaction, update using the where clause, execute, check rowcount matches what you selected earlier, commit trans.

Inquery co-founder here. Glad to hear the idea resonates with you! Do you usually just run these command in a local IDE? Would you prefer our solution be a local application or a self-hosted container in your VPC accessible through your web browser?

In the past I've done a few different ways but now I see strict infosec rules, That part is more important than where it runs. eg My last job we had a workflow where you needed a ticket approved by second eyes, which used CyberArk to create a new remote desktop running a DB IDE where you could do your business. Commands were tracked but no real restriction..

New firm you get your personal account temporary RW permissions via a centralized service.

The "temporary access" approach seems to be pretty popular based on our conversations with engineers at large-ish tech companies. We hadn't heard of anyone using a remote desktop for this problem, though.

Reply on news.ycombinator.com