Index/trigger implementation for accessing latest records

[Alastair McKinley <a.mckinley@xxxxxxxxxxxxxxxxxxxx>]

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

Aside from performance, is it safe to update other records in the table from the insert trigger in this way?

Minimal example is shown below:

create table location_records

(

        id bigserial,

        person_id bigint,

        location_id bigint,

        latest_record boolean not null default true

);

create function latest_record_update() returns trigger as

$$

BEGIN

        update location_records set latest_record = false where person_id = new.person_id and latest_record is true and id != new.id;

        return new;

END;

$$ language plpgsql;

create trigger latest_record_trigger before insert on location_records

for each row execute procedure latest_record_update();

create index latest_record_index on location_records(latest_record) where latest_record is true;

insert into location_records(person_id,location_id) values (1,1);

insert into location_records(person_id,location_id) values (1,2);

insert into location_records(person_id,location_id) values (1,3);

insert into location_records(person_id,location_id) values (2,3);

insert into location_records(person_id,location_id) values (2,4);

select * from location_records;

Best regards,

Alastair