public inbox for [email protected]
help / color / mirror / Atom feedTracking mutations in table data
3+ messages / 3 participants
[nested] [flat]
* Tracking mutations in table data
@ 2020-04-05 22:06 Chris Coutinho <[email protected]>
2020-04-06 02:30 ` Re: Tracking mutations in table data David G. Johnston <[email protected]>
2020-04-06 17:49 ` Re: Tracking mutations in table data Mark Kelly <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: Chris Coutinho @ 2020-04-05 22:06 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tracking mutations in table data
2020-04-05 22:06 Tracking mutations in table data Chris Coutinho <[email protected]>
@ 2020-04-06 02:30 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2020-04-06 02:30 UTC (permalink / raw)
To: Chris Coutinho <[email protected]>; +Cc: pgsql-novice
On Sun, Apr 5, 2020 at 3:06 PM Chris Coutinho <[email protected]>
wrote:
> In addition to the IoT events themselves, I want to log the mutations
> in the metadata of each device.
Why?
My question is, what are the best practices regarding tracking this
> metadata
Do the simplest thing allowed while ensuring you retain all of the data and
an associated timestamp or version.
> I've done a little research into history tables and
> (bi-)temporal tables,
This seems like overkill.
> 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.
>
So create one, then populates its contents using insert/update/delete
triggers on the observed table.
I'm also hosting this server on a managed Azure instance, which is
> somewhat limited in the number and kinds of extensions available.
>
Triggers are core functionality.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Tracking mutations in table data
2020-04-05 22:06 Tracking mutations in table data Chris Coutinho <[email protected]>
@ 2020-04-06 17:49 ` Mark Kelly <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Mark Kelly @ 2020-04-06 17:49 UTC (permalink / raw)
To: [email protected]
Hi Chris.
On 05/04/2020 23:06, Chris Coutinho wrote:
> 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.
I've done something similar a few times. If the application that is
creating the device records can generate a UUID for each one I'd do all
of that in the devices table.
create table devices (
id serial primary key,
meta1 int,
meta2 text,
identifier UUID NOT NULL,
deleted BOOLEAN DEFAULT false,
update_time TIMESTAMP DEFAULT now()
)
Give each device a UUID when it is added, then instead of updating or
deleting records just create a new row with the same UUID that reflects
the changes.
Current record for the device is:
SELECT * FROM device
WHERE identifier = [whatever]
ORDER BY update_time DESC LIMIT 1;
Device history is just
SELECT * FROM device
WHERE identifier = [whatever]
ORDER BY update_time;"
Deletion is just flipping a boolean, and you retain the complete history
for the device no matter what, just because all the records are still there.
You can use triggers to block UPDATE or DELETE queries, they are core
and won't need any additional stuff on your server. Or you can just
trust your application :)
I've no idea how this might work at ridiculous scale, the biggest table
I've built using this approach tops out about a million records, so bear
that in mind.
Hope this helps,
Mark
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-04-06 17:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-05 22:06 Tracking mutations in table data Chris Coutinho <[email protected]>
2020-04-06 02:30 ` David G. Johnston <[email protected]>
2020-04-06 17:49 ` Mark Kelly <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox