Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?

I think what you want is related to this post on how to create a FIFO
queue in Postgres:

http://people.planetpostgresql.org/greg/index.php?/archives/89-Implementing-a-queue-in-SQL-Postgres-version.html

The major difference is that you want a FIFO queue per user_id, so the
triggering code would want to bump old records aggregating on user_id
to calculate the "limit" subquery. His original code is this:

DELETE FROM q WHERE id NOT IN
(SELECT id FROM q ORDER BY id DESC LIMIT maxrows);

And that subquery is where (I think!) you'd want to add "where user_id
= [user_id]" - I'm not sure how you'll pass user_id into this function,
maybe someone else can help with that?

Hopefully this is useful?

Steve

At 06:24 AM 1/8/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Date: Tue, 8 Jan 2008 14:24:22 +0000
>From: "Jamie Tufnell" <diesql(at)googlemail(dot)com>
>To: pgsql-sql(at)postgresql(dot)org
>Subject: How to keep at-most N rows per group? periodic DELETEs or
>constraints or..?
>Message-ID:
><b0a4f3350801080624k2088c96aq21c511873959aa87(at)mail(dot)gmail(dot)com>
>
>Hi,
>
>I have a table that stores per-user histories of recently viewed items
>and I'd like to limit the amount of history items to <= 50 per user.
>I'm considering doing this with a query run from cron every so often
>but I'm not happy with what I've come up with so far, and since it's a
>quite active table I thought I'd ask here to see if there's a more
>efficient way.
>
>Right now the table structure is as follows...
>
>user_item_history: id (PK), user_id (FK), item_id (FK), timestamp
>
>For user_ids that have more than 50 rows, I want to keep the most
>recent 50 and delete the rest.
>
>The most obvious way of doing this for me is:
>
>--
>-- Get the user_ids with 50 or more history entries like this
>--
>SELECT user_id, count(*)
>FROM user_scene_history
>GROUP BY user_id
>HAVING count(*) > 50;
>
>--
>-- Then iterate the ids above (_user_id)
>--
>DELETE FROM user_scene_history
>WHERE user_id = _user_id AND id NOT IN (
> SELECT id FROM user_scene_history
> WHERE user_id = _user_id
> ORDER BY timestamp DESC
> LIMIT 50);
>
>I've left out the simple logic tying the above two queries together
>for clarity..
>
>I haven't actually tested this but while I assume it would work I
>imagine there is a neater and possibly more efficient way of attacking
>this. I'm also open to different approaches of limiting the user's
>history too ... perhaps with table constraints so they can simply
>never exceed 50 entries? But I'm not sure how to do this..
>
>Any help would be greatly appreciated..
>
>Thanks,
>Jamie