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
