Subject: Re: Tracking mutations in table data
To: pgsql-novice@lists.postgresql.org
References: 
 <CAG+YirQg=4846+XEO0zRBUL52PjQ=MPmsyTV8Fid8nvNGGzMZg@mail.gmail.com>
From: Mark Kelly <pgsql@wastedtimes.net>
Message-ID: <67124df7-a9da-ca42-e736-cc39c32c4cda@wastedtimes.net>
Date: Mon, 6 Apr 2020 18:49:03 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.4.1
MIME-Version: 1.0
In-Reply-To: 
 <CAG+YirQg=4846+XEO0zRBUL52PjQ=MPmsyTV8Fid8nvNGGzMZg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-GB
Content-Transfer-Encoding: 7bit
Precedence: bulk

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