DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma;
	h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=HjSF8x1JRnx+1oPe9c1DYxIs23I/saaMfe95l76bWTQ4v/PZynZJlpuxgFM1YMujActDG8M4PPRsQsXGhtde9+9p7QcRBz60yR54f4gGglgVeER+KE+uqBieSY0PfA3sJMiE4PnoUFuJ5GugX9bjIPopmYWv+iIW4PqMcVW5BY8=
Message-ID: <b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.com>
Date: Tue, 8 Jan 2008 14:24:22 +0000
From: "Jamie Tufnell" <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: How to keep at-most N rows per group? periodic DELETEs or constraints
	or..?
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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