Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Show HN: Safe Data Changes in PostgreSQL

github.com

Post reply on HN
Print view View original

11–18 of 18 posts

Re: Show HN: Safe Data Changes in PostgreSQL

#11

Post by mitchpatin »

rr888 wrote: post #10
mitchpatin wrote: post #8

Earlier quoted context omitted.

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 permiss…

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.

Re: Show HN: Safe Data Changes in PostgreSQL

#12

Post by klysm »

pawelduda wrote: post #2

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.

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

Re: Show HN: Safe Data Changes in PostgreSQL

#13

Post by Valodim »

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.

Re: Show HN: Safe Data Changes in PostgreSQL

#14

Post by pawelduda »

klysm wrote: post #12
pawelduda wrote: post #2

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.

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 :)

Re: Show HN: Safe Data Changes in PostgreSQL

#15

Post by mitchpatin »

Valodim wrote: post #13

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 abou…

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.

Re: Show HN: Safe Data Changes in PostgreSQL

#16

Post by ciminelli »

pawelduda wrote: post #6
ciminelli wrote: post #3

Earlier quoted context omitted.

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…

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?

Re: Show HN: Safe Data Changes in PostgreSQL

#17

Post by pbronez »

pawelduda wrote: post #2

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.

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

Re: Show HN: Safe Data Changes in PostgreSQL

#18

Post by klysm »

pawelduda wrote: post #14
klysm wrote: post #12

Earlier quoted context omitted.

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.
Post reply on HN