Re: Index/trigger implementation for accessing latest records

Hi Alastair,

See embedded comments.

On 02/05/18 21:51, Alastair McKinley wrote:
>
> Hi,
>
>
> I have a table that stores a location identifier per person which will
> be appended to many times.
>
> However, for many queries in this system we only need to know the most
> recent location per person, which is limited to about 1000 records.
>
>
> Is the following trigger/index strategy a reasonable and safe approach
> to fast access to the latest location records per person?
>
>
> 1. A boolean column (latest_record default true) to identify the
> latest record per person
> 2. A before insert trigger that updates all other records for that
> person to latest_record = false
> 3. A partial index on the latest_record column where latest_record is
> true
>
Suggest simplest and fastest is to use timestamptz, a timestamp with
time zone (copes with changes of daylight saving and different
timezones. The you have no need of triggers.

Then all you need to do, is search for the person-id with the maximum
value of the timestampz!

[...]

Cheers,
Gavin