Postgresql – How to efficiently clone database records together with all child elements

database-designpostgresql

I have an insert-only PostgreSQL database with a Book table, a Page table and an Element table.
Books can have multiple pages and pages can have multiple elements.
The database is insert-only because any time a page is modified, a new version of that page is created in a new record for auditing reasons.

-------------
Book
-------------
id [PK]

-------------
Page
-------------
id [PK]
book_id [FK]
version
created

-------------
Element
-------------
id [PK]
page_id [FK]
version
created

Currently, Element has a Foreign Key pointing to Page.id and Page has a Foreign Key pointing to Book.id.

A requirement is to have independent page drafts (versions) that can contain any number of elements. Every time a new page draft (version) is created, the new draft should be a clone of the previous version and it should contain all elements in the previous version. Elements can be added to or removed from a specific version of the Page, although Elements records are never deleted.

For scalibility reasons, I would avoid cloning all the elements together with the page, since the elements could be a very large number. I would also what to avoid many-to-many relationships between pages and elements.

I was thinking about replacing the Element.page_id FK with an Element.book_id FK so to have a single bucket of elements and pages more loosely coupled with each other. This way, when cloning a page, I wouldn't have to clone all the elements, but I would need a way to associate each element with a particular version of a Page.

Ideally, I should be able to retrieve a page version with all the elements associated to that version with a single SQL query.

I am still in a design/prototyping phase, so any aspect of the databse can potentially be changed to fulfill the requirements.

What could be the best way to associate Pages and Elements according to the requirements above? I hope the requirements are clear, if not, please ask any questions and I'll try to clarify.

Best Answer

a new version of that page is created in a new record for auditing reasons.

How do you know which "version" is the latest, you're going to process on version and select the last one? That'll work, but I think you're barking up a pretty steep tree. Essentially you're building your own revision-control system in a database. Not saying everyone hasn't tackled that task before, but issues likely to come up are

Merging (other users)
Rollbacks (without which, why would you keep old versions)
Diff-views
Delta stores (why store the whole element when you only change one character)
etc, etc.

This gets super complex very fast. If you're wanting an out of the box solution, checkout gitlab. If you have speced to just stop here and not feature creep -- I think this will work just fine.

Related Solutions

Temporal database design, with a twist (live vs draft rows)

Cross-posting the answer as well:

I think I nailed it. Basically, you add a (unique) draft field to the relevant tables, and you work on the drafts as if they were a new post/tag/etc.:

posts (
 id pkey,
 public,
 created_at stamptz,
 updated_at stamptz,
 updated_by int,
 draft int fkey posts (id) unique
)

post_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

tags (
 id pkey,
 public,
 created_at,
 updated_at,
 updated_by,
 draft fkey tags (id) unique
)


tag_revs (
 id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (id, created_at)
)

post2tag (
 post_id fkey posts(id),
 tag_id fkey tags(id),
 public,
 created_at,
 updated_at,
 updated_by,
 pkey (post_id, tag_id)
)

post2tag_revs (
 post_id,
 tag_id,
 public,
 created_at,
 created_by,
 deleted_at,
 pkey (post_id, tag_id, created_at)
)

Postgresql – What happens to the index of a primary key after a DROP CONSTRAINT

Disclaimer

This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in the catalog tables you could easily mess up your database.

For all I know, there are only two differences between a PRIMARY KEY and a UNIQUE constraint in the catalog tables (the index itself is identical):

pg_index.indisprimary:
For PRIMARY KEY constraint ... TRUE
For UNIQUE constraint ... FALSE

pg_constraint.contype:
PRIMARY KEY constraint ... 'p'
UNIQUE constraint ... 'u'

You could convert constraint and index in place, from PRIMARY KEY constraint to UNIQUE constraint, my_idx being the (optionally schema-qualified) index name:

UPDATE pg_index SET indisprimary = FALSE WHERE indexrelid = 'my_idx'::regclass
UPDATE pg_constraint SET contype = 'u' WHERE conindid = 'my_idx'::regclass;

Or upgrade from UNIQUE to PRIMARY KEY:

UPDATE pg_index SET indisprimary = TRUE WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'p' WHERE conindid = 'my_idx'::regclass;

Best Answer

Related Solutions

Temporal database design, with a twist (live vs draft rows)

Postgresql – What happens to the index of a primary key after a DROP CONSTRAINT

Disclaimer

Related Question