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

51–60 of 92 posts

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

#51

Post by _a_a_a_ »

mardifoufs wrote: post #45
_a_a_a_ wrote: post #40

Earlier quoted context omitted.

Poster said oracle not PG

Total db noob here: is Oracle's query planner better? What's the difference between the two?

This is not about Oracle versus postgres, it's about whether or not to use hints. My position is that needing hints indicates a failure of the database optimiser. The optimiser theoretically should do a perfect job. In practice it doesn't, and never can so sometimes hints are necessary, but IME and speaking as an MSSQL guy, I very rarely need them.

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

#52

Post by iamwil »

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?

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

#53

Post by pstuart »

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…

Not entirely crazy. The creater of SQLite has done just that: https://fossil-scm.org/home/doc/trunk/www/index.wiki

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

#54

Post by wodenokoto »

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

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

#55

Post by refset »

traceroute66 wrote: post #27
refset wrote: post #9

Earlier quoted context omitted.

Expanding on the the above, > The original version of PostgreSQL from the 1980s did not remove dead tuples. The idea was that keeping all the older versions allowed applications to execute “time-travel” queries to examine the database at a particular point in time [via https://ottertune.com/blog/the-part-of-postgresql-we-hate-th... ] Postgres deprecated support for time-travel in ~1997 and the more general notion of…

> (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 indicating the desired time when defining a tuple variable.

> [...] Finally, POSTGRES provides support for versions. A version can be created from a relation or a snapshot. Updates to a version do not modify the underlying relation and updates to the underlying relation will be visible through the version unless the value has been modified in the version.

https://dsf.berkeley.edu/papers/ERL-M85-95.pdf

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

#56

Post by iamwil »

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

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

#57

Post by l-vincent-l »

h1fra wrote: post #14
DaiPlusPlus wrote: post #8

Better idea: just install the Postgres extension that enables ISO SQL Temporal Tables, just like what MSSQL and MariaDB have had for 8 years now: https://pgxn.org/dist/temporal_tables/

author here: Yes I agree, and I mention it at the end. However it's not possible to install the extension everywhere, for example it's not available in GCP Cloud SQL unfortunately

It was reimplemented in pure SQL here https://github.com/nearform/temporal_tables for this purpose

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

#58

Post by tynorf »

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.

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

#59

Post by mnahkies »

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…

It's not automatically creating them AFAIK, but alloydb has an index suggestion tool https://cloud.google.com/alloydb/docs/use-index-advisor

Generally this seems like a nice balance to me - watch real queries, look for expensive plans and sniff out likely missing indexes based on the data distribution, but give the DBA final say on whether it's a good idea

Post reply on HN