How to maintain fixed number of rows per foreign key? I would like to keep a record of the last N (say, 10) objects a user has viewed. I'm thinking a basic way to store this information might be the following table:

CREATE TABLE history (
  id            SERIAL PRIMARY KEY,
  user_id       integer REFERENCES users (id),
  object_id     integer REFERENCES objects (id),
  view_date     timestamp DEFAULT current_timestamp
);


Since I only want to record the last 10 object views, I never need more than 10 rows for a given user_id. Is there an efficient way to maintain this limit whenever a user views an object and a new entry is created?

Or am I going about this the wrong way? Perhaps I should just limit to 10 results in during SELECT and periodically purge extra entries in some other manner?
It will be much more efficient to limit the SELECT and purge periodically.

If you had to maintain a hard limit of 10 for business requirements, what you'd do is define a trigger on history before insert or update or delete for each row that:


does a SELECT ... FOR UPDATE on the parent object_id to lock it, so it serves as a lock to prevent concurrent insert/delete of other history entries for the same object;
If this is an insert, counts rows for this object and gets the id of the oldest
If the number of entries will be increased to 11 by this insert, deletes the oldest row


Deadlocks can occur, so you need to be able to retry the whole transaction if there's a deadlock.