1

I'm working on a small content management system database and I wanted to use an append-only architecture, having snapshots of content (revisions) that can be used at any time, kinda like Git. But unlike git, i don't think it's feasible to store deltas, but just an entire snapshot.

Does anyone know of a way to do this in PostgreSQL in a way that supports relationships? How can i store a snapshot of relations between tables?

1
  • Essentially asking how to create a revision control system is a gigantic question. Jan 27, 2018 at 19:57

2 Answers 2

4
+25

The SQL:2011 standard has a feature known as temporal tables or system-versioned tables which might be helpful for this type of use-case.

Basically, whenever you update or delete a record in the database, the database automatically holds on to the previous version of the record, including the time period when it was valid.

PostgreSQL doesn't support this feature natively, but there's an extension called temporal_tables. A tutorial can be found here. A few other DBMSes support temporal tables natively, including SQL Server 2016 and MariaDB 10.3.

1

There are lots of ways to go about this, and it would require a lot of information about your desired workload and what you need. Revision control and rollback-support are very complex topics.

  • Do you need to roll-back, and jump-forward?
  • Do you need branching and merging? What happens if you roll-back and then continue from that point? Do you lose the changes on the abandoned branch and if so do they get purged from the database?
  • Do you need to track different users and permissions in the content revisions?
  • When you say relationships, what exactly do you mean? What kind of guarantees do you require?

Most CMS systems (like every system I've ever encountered) suck because they do it wrong. The chances of you getting this right are pretty slim. If you really need git, my suggestion is to use git. If you need a concurrent transactioning with git, you have two options:

  • Start from the database and implement git. (Usually ends in epic failure)
  • Start from git and implement MVCC or ties to the database. (Often ends in epic failure, see wiki.js)

Either way, good luck with those tasks by yourself. There are numerous git abstraction layers, and other CMS frameworks to start from.

1
  • Yeah, i need to move between revisions. No branching or merging, just full snapshots. Kinda like binaries in git.
    – vinnylinux
    Feb 6, 2018 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.