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

71–80 of 92 posts

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

#71

Post by galaxyLogic »

refset wrote: post #55
traceroute66 wrote: post #27

Earlier quoted context omitted.

> (bi-)temporal versioning I once spent some time trying to find a way to do bi-temporal versioning in Postgres. The only thing I found was a half-dead abandonware external project and an associated presentation PDF from some conference the author once spoke at. I was unaware that they previously had some form of temporal queries and deprecated it. That is a great shame.

A shame indeed, they were only a few decades too early! The support for "Time Varying Data" gets discussed briefly in this 1995 paper by Stonebraker "The Design of Postgres" > POSTQUEL allows users to save and query historical data and versions. By default, data in a relation is never deleted or updated. Conventional retrievals always access the current tuples in the relation. Historical data can be accessed by indic…

Those are cool features, so why were they dis-continued?

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

#72

Post by galaxyLogic »

wodenokoto wrote: post #54
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…

You probably already know of fossil, which is based on SQLite, but just in case, I’ll share a link with you: https://sqlite.org/whynotgit.html

Why not git? Specifically as relates to the current discussion?

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

#73

Post by MenhirMike »

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…

> (To answer my own question, apparently Azure SQL does)

Which is fun when you add a future schema migration that drops a column, test in locally and in some test cases, and then see it bomb in production because the column has an Index that you weren't aware of. (That's an easy thing to solve though, but definitely one of those cases where "The database is messing with its schema on its own" does do funny things)

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

#74

Post by aardvark179 »

It is possible to produce a long transaction versioned database, but the tricky bit is normally merging the structured data. For most applications this either needs to be entirely automated (so no user intervention is required) or the conflict resolution needs to surface in a lot of UI.

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

#75

Post by password4321 »

aboodman wrote: post #23

You mentioned you hadn't looked at it in the article, but FWIW this is exactly the approach Dolt takes.

Dolt is Git for data

2022 https://news.ycombinator.com/item?id=31847416

2021 https://news.ycombinator.com/item?id=26370572

2020 https://news.ycombinator.com/item?id=22731928

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

#76

Post by somat »

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

The postgress team is generally opposed to hints. And their reasoning sort of makes sense.

When do you need a hint? why do you need a hint? If you can answer those questions wouldn't it be better to codify those answers into the query planner than a one off hint?

Which nicely sidesteps how hard it actually would be to first, understand the query planner to the degree needed to change it and second, the amount of effort it would take transform a one off hint into a general purpose optimization engine.

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

#77

Post by _a_a_a_ »

renegade-otter wrote: post #69
_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 .

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.

I've >25 years of DB rodeoing. Perhaps I know something about this by now. See my other answers on this thread.

> that changed its plan based on the column order in the SELECT clause.

Hmm? Could you elaborate; you mean the order of the cols or using ORDER BY? I assume you mean the latter but it sounded like the former.

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

#78

Post by _a_a_a_ »

zaptheimpaler wrote: post #70
_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?

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…

You're right, I meant a failure of the DB/optimiser not the person. Thanks.

No they're not perfect but I need them rarely with MSSQL

> An optimizer will quickly run into NP-hard problems like join ordering

As a start, dynamic planning based on cardinality estimation based on stats. And then some. It explodes horribly, yes.

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

#79

Post by koito17 »

pspeter3 wrote: post #29
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.

Which derivative?

If I had to guess, they are probably using XTDB, which is bitemporal, i.e. every entity has an associated "transaction time" and "validity time". You can use the validity time to deal with situations like "Company reported X assets and Y liabilities in their 2020 balance sheet, and in 2022 it was discovered there was some 2020 assets unaccounted for", and in addition to this, answer questions like "How much assets did Company believe it had in 2020 given the facts we know in 2020? How about given the facts we know in 2022?"

I am not sure how useful this is for versioning data though. It seems like an orthogonal problem to me.

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

#80

Post by gen220 »

pphysch wrote: post #66
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.

Just to pile on, I think that adopting bitemporal schemas is the solution.

There are various schemes you can employ to keep "archived" data within postgres (e.g. offloading it to another instance accessible behind a FDW).

I've thought about this problem a lot, and I think every alternative solution I've seen is just an approximation of bitemporal schemas with some limitations that are typically not worth the cost:benefit trade-off.

The main difficulty is there isn't a standardized framework/FOSS solution for bitemporal data. I feel like it'll have to get into the SQL standard before we see widespread adoption.

Post reply on HN