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..?
Date: Tue, 8 Jan 2008 14:24:22 +0000
Message-ID: <b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: diesql@googlemail.com
Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
In-Reply-To: <b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox