Modelling a web CMS in Postgres ... a little advice needed

From: Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch>
To: postgre <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Modelling a web CMS in Postgres ... a little advice needed
Date: 2022-08-11 07:35:22
Message-ID: 6NtGy9Qbxtvte6bfGm7s85U2rAzIr90N6UhrCfs_ca4kd_ol6MlhZ0RsMsASDduc4hIwzf7VroZj9ZRotrsLO5P62EgXpuxJ2BH7DvdjJlM=@protonmail.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I'm looking at using pgsql as a backend to a web CMS but could do with a little advice from the crowd on the wiseness of my schema thinking.

TL;DR the design is centered around two tables "pages" and "page_content", where "pages" has a jsonb column that refers to "page_content" in a key-value style (key for content block location ID on the web page, value for the database lookup).  Probably both, but certainly "page_content" would need to be versioned.

My present thinking is along the following lines (table columns minimised for this post):

create table pages (
  page_id uuid primary key not null,
  page_metadata jsonb not null
);

create table page_content(
  content_id uuid not null,
  content_version_id uuid not null
  content_valid tstzrange not null default tstzrange(now(),'infinity'),
  content_data text,
EXCLUDE USING gist (content_id WITH =, content_valid WITH && ) DEFERRABLE INITIALLY DEFERRED
);
create unique index if not exists on page_content(content_version_id);
CREATE OR REPLACE VIEW current_content AS select * from page_content where content_valid @> now();

An example "page_metadata" entry might look something like :
{
"page":"foo",
"description":"bar",
"content":[
"pageHeader":"E52DD77C-F3B5-40D9-8E65-B95F54E1C76B",
"pageMainLeft":"0BEFA002-7F9B-4A6A-AD33-CA916751B648"
]
}

So I guess my questions here are :
Am i nuts with this thinking ? Is there a saner way to do this ? Should I be using pgsql at all for this, e.g. the cool kids will probably say I should be using a graph database ?  (N.B. I did consider a pure old-school relational model with no jsonb, but I considered it too difficult to model the dynamic nature of the fields, i.e. unknown many-many relationship between page content locations and content ... but I'm willing to be proven wrong by wiser minds)

Then, on a more technical level .... what would an optimal query for looping through the json content array look like ?  I have always been pretty useless when it comes to CTE expressions !

Thanks all

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Karsten Hilbert 2022-08-11 08:10:56 Aw: Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
Previous Message Bryn Llewellyn 2022-08-11 05:05:44 Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario