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

61–70 of 92 posts

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

#61

Post by entropicdrifter »

quectophoton wrote: post #48

On the topic of PostgreSQL and git. As crazy as this sounds, I once entertained the idea of writing a VCS that used PostgreSQL's large object facility for storing the data. I always have PostgreSQL installed on my personal devices so communication would be through Unix Domain Sockets anyway (or through a gigabit LAN in worst case). And the transactions and SQL interface (for metadata) were just that tempting. But I r…

Assuming that the full 32 bits are used, that means there can be up to 4,294,967,295 chunks.

4,294,967,295 * 2 = 8,589,934,590 KB

So, around 8.5 TB. Unless your hypothetical VCS is for a large library of videos, you should be fine

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

#62

Post by djbusby »

iamwil wrote: post #52
cactusfrog wrote: post #4

We are using a Datomic derivative at my work because Postgres doesn’t have this feature. I don’t think anyone has actually used Daromic’s capabilities for this purpose.

Honest question: Why do you suppose no one at work used this feature, even though the stack was picked for it explicitly?

Project manager needed to check a box on some form for the skip-boss to be happy.

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

#63

Post by shepherdjerred »

tynorf wrote: post #58
iamwil wrote: post #56

> Naively when you read a git diff or a pull request, you might think like me, that git is storing a diff between A and B and just displays it when needed. But in reality it stores the full file in the object storage. I thought logically git stored every file, but implementation-wise, it does git-object compaction. So in reality, it's not actually storing every file on disk, no?

The objects directory stores every file (and tree, commit, etc). Pack files are an optimization storing diffs.

Which is such a genius implementation. You get the straightforward implementation of using plain files (e.g. not deltas), while also being able to get the storage boost from storing deltas.

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

#64

Post by rudasn »

mock-possum wrote: post #43
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…

Are you overwriting the previous version of the JSON when committing then merging? Or are you just archiving a new copy of the JSON alongside all the old ones?

He's doing diffs: https://github.com/simonw/simonwillisonblog-backup/commit/82...

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

#65

Post by GordonS »

_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 .

In a perfect world, I wouldn't disagree; but we don't live in a perfect world where everything always goes your way.

Sometimes the planner needs a little help, and that's OK.

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

#66

Post by pphysch »

PeterZaitsev wrote: post #11

Creates lots of complexity and Performance issues. I agree using PostgreSQL Extension for Temporal tables is a better idea. I wish PostgreSQL would allow something as Oracle Flashback feature plus allowing to control how far history do you want to keep for specific tables, as typically only part of your data needs full auditing.

I am curious what success stories people have with versioned databases, and whether those success stories could be replicated by a combination of bitemporal schema design and infrequent snapshots/backups. That is, to avoid the complexity and performance issues of adding a temporal dimensions to 100% of your data instead of the X% that actually needs it.

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

#67

Post by westurner »

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…

pgreplay parses not the WAL Write Ahead Log but the log file, " extracts the SQL statements and executes them in the same order and relative time against a PostgreSQL database cluster": https://github.com/laurenz/pgreplay

"A PostgreSQL Docker container that automatically upgrades your database" (2023) https://news.ycombinator.com/item?id=36748041 :

pgkit wraps Postgres PITR backup and recovery: https://github.com/SadeghHayeri/pgkit#pitr :

  $ sudo pgkit pitr backup  
  
  $ sudo pgkit pitr recover  
  $ sudo pgkit pitr recover  latest

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

#68

Post by _a_a_a_ »

mannyv wrote: post #47
_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 .

Totally untrue...but adding hints makes your queries dependent on current performance. Assuming you're a good DBA, you can be better than the optimizer because you understand the whole system. Even databases are general-purpose machines. That's the whole point of adding indexes, etc. I mean, has there ever been a database that auto-adds indexes based on profiler/optimizer feedback? (To answer my own question, apparen…

> adding hints makes your queries dependent on current performance

I don't understand this, can you explain.

> Assuming you're a good DBA, you can be better than the optimizer because you understand the whole system

That's inaccurate. As a DBA I can understand the system better at higher level, but the database has statistics which gives it typically better understanding of the data distribution at a lower level. Indeed I could get that information and feed it into the query plan via hints, but that's going to be an enormous amount of my time, and I would have to do it every time the query is run whereas the database can keep an eye on the statistics as it varies and rebuild the query over time.

Equally, if an index is added you can expect the database to start using it immediately without adding hints. Etc

I am a fairly(?) skilled DBA who has a reasonable idea of what goes on underneath the hood, I do have some idea what I'm talking about.

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

#69

Post by renegade-otter »

_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 .

Around the same time, I wrote an extremely convoluted MySQL query that changed its plan based on the column order in the SELECT clause.

I wouldn't post snooty and dismissive comments about this - assume that you are in a company of people who have been to more rodeos than you have.

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

#70

Post by zaptheimpaler »

_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?

No one is saying that. Your original comment can be read like you're saying using hints is a sign of failure of the person writing the query which is probably why the downvotes. Maybe you meant a sign of failure for the optimizer, which is also debatable given they really cannot be perfect. An optimizer will quickly run into NP-hard problems like join ordering - which is just one small reason among many that they won't always find the fastest query plan.
Post reply on HN