Postgres Update Limit

Question

I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID.

For example, say I have a users table and login_coordinates table. Every time the user logs in, the most current coordinates get fed to a column in the users table, as well as inserting into the login_coordinates table.

I only want to keep the last 10 records, and deleting the 11th(oldest) record in the login_coordinates table for all users.

Users

 user_id | current_coordinates | 
----------------------+----------------------------+
 1 | aaaa.bbbbb, aaaaa.bbbbbb | 
 2 | zzzz.xxxxxx, xxxxx.xxxcxx | 
 3 | dddd.xxxxxx, xxxxx.xxxcxx | 

Login Coordinates

 coordinates_id | old_login_coordinates | user_id |
----------------------+----------------------------+--------------------------+
 1 | aaaa.bbbbb, aaaaa.bbbbbb | 1 |
 2 | xxxxx.xxxxxx, xxxxx.xxxcxx | 1 |
 3 | xxxxx.xxxxxx, xxxxx.xxxcxx | 1 |

Is there anything that would limit the records to 10 coordinate_id per user, always deleting the oldest records?

I'm using PostgreSQL 9.5.

• Erwin Brandstetter: Please always declare your version of Postgres. Also essential here: do you have concurrent write access? Can a user log in multiple times at once or is this impossible?
• unseen_damage: Concurrent write access will be allowed, but we will be doing row locks, and the likelihood of concurrent writes will be low as the main system is primarily read only. A user can login multiple times.

Answer 1

as well as inserting into the login_coordinates table

I don't see the benefit of redundant storage. Just write to the table login_coordinates. Easy enough to access with only 10 rows per user. Don't update the user row as well.

Basically, what @Ziggy already suggested, with more flesh. Based on this table:

CREATE TABLE login_coordinates (
 login_coordinates_id serial PRIMARY KEY
, user_id integer NOT NULL -- REFERENCES users
, login_at timestamptz NOT NULL DEFAULT now()
, coordinates point NOT NULL
);

Using OFFSET 9 LIMIT 1 to pick nr. 10:

DELETE FROM login_coordinates lc
WHERE login_at <= (
 SELECT login_at
 FROM login_coordinates
 WHERE user_id = lc.user_id
 ORDER BY login_at DESC
 OFFSET 9 -- only found if at least 10 rows present
 LIMIT 1
 )
AND user_id = 1;
INSERT INTO login_coordinates (user_id, coordinates)
VALUES (1, '(1, 2)');

• unseen_damage: Redundant storage is mostly for auditing purpose. We don't want to fill our users table with auditing information like time and place of past logins. When a user logs in, we will be keeping the most recent login in the users table, along with a json web token. For auditing, we will keep the last 9 + current login (10 total) in a separate table. We will allow multiple logins, but limit that based on location (3 total).
• Erwin Brandstetter: @unseen_damage: But: A user can login multiple times. If each login updates the row in users, I see concurrency trouble ahead. The current session might consider the login of a later session with the same user as its own. Etc.
• unseen_damage: Unless I have implemented this wrong, the above query deletes all entries after #10 is entered in. Is that the expected behavior?
• Erwin Brandstetter: @unseen_damage: Sorry, that was an error on my side. Sort order must be descending to get the "oldest" timestamp. Consider the fixed query.

Answer 2

You could implement an ON INSERT trigger and delete, for the same user, any rows with a timestamp older than the oldest (MIN) of a subselect ordered by timestamp DESC limit 10.

Edit, using an array in users:

An alternative is using an array straight in the users table:

Add a type and a new array in users:

CREATE TYPE past_logon AS (login_at timestamptz, coordinates point);
ALTER TABLE users ADD past_logons past_logon[];

This can be added to the function that handles the logon (replace point(1,1) and = 1 with point and user in logon function parameters)

UPDATE users SET
 past_logons = (now(), point(1,1))::past_logon || past_logons[1:9]
WHERE user_id = 1;

Example for retrieving the list of past logons from the array

SELECT u.user_id, pl.*
FROM users u
LEFT JOIN LATERAL unnest(u.past_logons) pl ON true
ORDER BY pl.login_at;

• Ezequiel Tolnay: Edited the answer to include an example using an array instead of another table for purposes like this.