Tracking mutations in table data

From: Chris Coutinho <chrisbcoutinho(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Tracking mutations in table data
Date: 2020-04-05 22:06:09
Message-ID: CAG+YirQg=4846+XEO0zRBUL52PjQ=MPmsyTV8Fid8nvNGGzMZg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi all,

Relatively new to PostgreSQL, looking for guidance.

I have a schema that looks like this, it's being used to log IoT events:

create table devices (
id serial primary key,
meta1 int,
meta2 text
)

create table events (
datetime timestamp,
device_id int references devices (id),
code int,
primary key (datetime, device_id)
)

In addition to the IoT events themselves, I want to log the mutations
in the metadata of each device. The metadata of each device changes
much less frequently than the rate at which events are inserted,
that's why I've opted to place the data into the devices table.

My question is, what are the best practices regarding tracking this
metadata? I've done a little research into history tables and
(bi-)temporal tables, and I'm little lost based on all of the options
available. In short, I want some kind of history table of mutations in
the devices table so that I can see when the metadata is
inserted/updated/deleted.

I'm also hosting this server on a managed Azure instance, which is
somewhat limited in the number and kinds of extensions available.

Thanks in advance,
Chris

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message David G. Johnston 2020-04-06 02:30:35 Re: Tracking mutations in table data
Previous Message David G. Johnston 2020-03-28 16:02:41 Re: chr(3) and 3::text