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

Versioning data in Postgres? Testing a Git like approach

specfy.io

Post reply on HN
Print view View original

31–40 of 92 posts

Re: Versioning data in Postgres? Testing a Git like approach

#31

Post by simonw »

In case it's useful to anyone, I've been running the cheapest possible implementation of Git-based revision history for my blog's PostgreSQL database for a few years now.

I run a GitHub Actions workflow every two hours which grabs the latest snapshot of the database, writes the key tables out as newline-delimited JSON and commits them to a Git repository.

https://github.com/simonw/simonwillisonblog-backup

This gives me a full revision history (1,500+ commits at this point) for all of my content and I didn't have to do anything extra in my PostgreSQL or Django app to get it.

If you need version tracking for audit purposes or to give you the ability to manually revert a mistake, and you're dealing with tens-of-thousands of rows, I think this is actually a pretty solid simple way to get that.

Re: Versioning data in Postgres? Testing a Git like approach

#33

Post by nikita »

devaiops9001 wrote: post #19

https://neon.tech/docs/introduction/branching is exactly what the author is looking for and is the best way to do highly efficient point-in-time versioning of Postgres data I have seen so far. I deployed NeonDB in a large enterprise client in Q2 of this year and, yes, the initial data migration is the hard part. We added 2x100gbe network cards directly on the VMware hosts where the data was so the migration to the Ku…

Neon CEO here. Did you deploy Neon on prem?

Re: Versioning data in Postgres? Testing a Git like approach

#34

Post by nikita »

h1fra wrote: post #13
danielvaughn wrote: post #6

Neon does something like this I believe. They allow you to "branch" your databases: https://neon.tech

That's interesting. Seems to be made for CI, testing, not inside a single production though.

Neon is designed to run in production. It runs on top of out storage that is designed for webscale and production usage.

Re: Versioning data in Postgres? Testing a Git like approach

#35

Post by nikita »

noworriesnate wrote: post #21
dewey wrote: post #16

I didn’t see it in the article but that’s exactly what https://postgres.ai/ is doing already unless I’m missing some thing.

I haven’t read about NeonDB before but a brief search of their docs doesn’t mention ZFS. Postgres.ai uses ZFS to implement the snapshots, branching, etc. I’d be very interested in a comparison of the two.

No ZFS. ZFS is not "cloud native" - we build our storage for the cloud and for Postgres from the ground up.

Re: Versioning data in Postgres? Testing a Git like approach

#36

Post by _a_a_a_ »

_a_a_a_ wrote: post #28
renegade-otter wrote: post #5

Earlier quoted context omitted.

Oracle, with all the baggage attached to the namesake company, is a remarkable database. I was working in a dual MySQL/Oracle environment 20 years ago exactly, and MySQL - still at version 3 o 4 - was a toy DB, compared. I was writing Oracle queries that could make your head spin, with optimizer hints, for example: https://renenyffenegger.ch/notes/development/databases/Oracl...

Using hints is typically a sign of failure. The DB optimiser should typically not need them .

Ok downvoter, why do you think a DB that needs hints for good performance is better than one that usually doesn't? That is, why do you think it better a person does a computer's job instead of the computer?

Re: Versioning data in Postgres? Testing a Git like approach

#38

Post by [deleted] »

simonw wrote: post #31

In case it's useful to anyone, I've been running the cheapest possible implementation of Git-based revision history for my blog's PostgreSQL database for a few years now. I run a GitHub Actions workflow every two hours which grabs the latest snapshot of the database, writes the key tables out as newline-delimited JSON and commits them to a Git repository. https://github.com/simonw/simonwillisonblog-backup This gives…

[deleted]

Re: Versioning data in Postgres? Testing a Git like approach

#39

Post by mickeyp »

_a_a_a_ wrote: post #36
_a_a_a_ wrote: post #28

Earlier quoted context omitted.

Using hints is typically a sign of failure. The DB optimiser should typically not need them .

Ok downvoter, why do you think a DB that needs hints for good performance is better than one that usually doesn't? That is, why do you think it better a person does a computer's job instead of the computer?

You've never run into a query planner that suddenly changes its mind once your tables grow.

Try using row level security in postgres - it's awfully slow and you need to be a master architect and sql developer to make it run fast because it's too dumb on its own. With query planner hints I could at least guide it. Shame pg doesn't believe in that.

Re: Versioning data in Postgres? Testing a Git like approach

#40

Post by _a_a_a_ »

mickeyp wrote: post #39
_a_a_a_ wrote: post #36

Earlier quoted context omitted.

Ok downvoter, why do you think a DB that needs hints for good performance is better than one that usually doesn't? That is, why do you think it better a person does a computer's job instead of the computer?

You've never run into a query planner that suddenly changes its mind once your tables grow. Try using row level security in postgres - it's awfully slow and you need to be a master architect and sql developer to make it run fast because it's too dumb on its own. With query planner hints I could at least guide it. Shame pg doesn't believe in that.

Poster said oracle not PG
Post reply on HN