pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to keep at-most N rows per group? periodic DELETEs or constraints or..?
13+ messages / 5 participants
[nested] [flat]
* How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-08 14:24 Jamie Tufnell <diesql@googlemail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Jamie Tufnell @ 2008-01-08 14:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-08 15:45 codeWarrior <gpatnude@hotmail.com>
parent: Jamie Tufnell <diesql@googlemail.com>
1 sibling, 1 reply; 13+ messages in thread
From: codeWarrior @ 2008-01-08 15:45 UTC (permalink / raw)
To: pgsql-sql
How about using a trigger to call a stored procedure ? [ON INSERT to
user_item_history DO ...]
and have your stored procedure count the records for that user and delete
the oldest record if necessary...
IF (SELECT COUNT(*) WHERE user_id = NEW.user_id) >= 50 THEN
-- DELETE THE OLDEST RECORD....
END IF;
RETURN NEW.*
""Jamie Tufnell"" <diesql@googlemail.com> wrote in message
news:b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-08 16:30 Erik Jones <erik@myemma.com>
parent: Jamie Tufnell <diesql@googlemail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Erik Jones @ 2008-01-08 16:30 UTC (permalink / raw)
To: Jamie Tufnell <diesql@googlemail.com>; +Cc: pgsql-sql
On Jan 8, 2008, at 8:24 AM, Jamie Tufnell wrote:
> 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.
Create an row trigger that fires after insert containing something
along the lines of :
DELETE FROM user_item_history
WHERE id IN (SELECT id
FROM user_item_history
WHERE user_id=NEW.user_id
ORDER BY timestamp DESC
OFFSET 50);
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-08 16:31 Jamie Tufnell <diesql@googlemail.com>
parent: codeWarrior <gpatnude@hotmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Jamie Tufnell @ 2008-01-08 16:31 UTC (permalink / raw)
To: pgsql-sql
Hi codeWarrior,
codeWarrior wrote:
> > For user_ids that have more than 50 rows, I want to keep the most
> > recent 50 and delete the rest.
> How about using a trigger to call a stored procedure ? [ON INSERT to
> user_item_history DO ...]
[snip]
Thanks for your input! I've implemented this but I'm concerned about
performance. As I mentioned it's frequently being added to and this
function will be called maybe a couple of times a second. In my brief
initial tests it seems like this is running quite slowly...
Just to make sure I haven't done anything obviously wrong, I've
included my implementation below.. if you could look over it I'd
really appreciate it.
CREATE OR REPLACE FUNCTION user_item_history_limit() RETURNS TRIGGER
AS $_$
DECLARE
threshold integer = 50;
numrows integer;
BEGIN
SELECT INTO numrows count(*) FROM user_item_history WHERE user_id
= new.user_id;
IF numrows > threshold THEN
DELETE FROM user_item_history WHERE user_id = new.user_id AND
id NOT IN (
SELECT id FROM user_item_history
WHERE user_id = new.user_id
ORDER BY timestamp DESC LIMIT threshold);
RAISE NOTICE '% rows exceeds threshold of % for user_id %;
trimming..', numrows, threshold, new.user_id;
END IF;
RETURN new;
END;
$_$
LANGUAGE plpgsql;
CREATE TRIGGER user_item_history_limit AFTER INSERT ON user_item_history
FOR EACH ROW EXECUTE PROCEDURE user_item_history_limit();
Any suggestions greatly appreciated!
Thanks again,
Jamie
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-08 17:31 Steve Midgley <public@misuse.org>
0 siblings, 0 replies; 13+ messages in thread
From: Steve Midgley @ 2008-01-08 17:31 UTC (permalink / raw)
To: pgsql-sql
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-...
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@postgresql.org wrote:
>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..?
>Message-ID:
><b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-08 17:41 Jamie Tufnell <diesql@googlemail.com>
parent: codeWarrior <gpatnude@hotmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Jamie Tufnell @ 2008-01-08 17:41 UTC (permalink / raw)
To: pgsql-sql
On 1/8/08, codeWarrior <gpatnude@hotmail.com> wrote:
> Jamie:
>
> I think you are probably having slowdown issues in your "DELETE FROM WHERE
> NOT IN SELECT ORDER BY DESCENDING" construct -- that seems a bit convoluted
> to me....
Hmm so rather than NOT IN ( .. LIMIT 50) would you suggest IN ( ...
OFFSET 50) like in Erik's example? Or something else entirely?
> ALSO: It looks to me like you have a column named "timestamp' ??? This is
> bad practice since "timestamp" is a reserved word... You really ought NOT to
> use reserved words for column names... different debate.
I do realize it would be better to use something else and thanks for
the tip :-) This is an established database and "timestamp" has been
used in other tables which is why I stuck to it here.. one day when
time permits maybe I'll rename them all!
> Why bother deleting records anyway ? Why not alter your query that tracks
> the 50 records to LIMIT 50 ???
The read query does LIMIT 50 and the reason for deleting the rest of
the records is because they're not needed by the application and
there's loads of them being created all the time (currently several
million unnecessary rows) -- I imagine eventually this will slow
things down?
Do you think a regular batch process to delete rows might be more
appropriate than a trigger in this scenario?
Thanks,
Jamie
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-08 17:53 codeWarrior <gpatnude@hotmail.com>
parent: Jamie Tufnell <diesql@googlemail.com>
0 siblings, 1 reply; 13+ messages in thread
From: codeWarrior @ 2008-01-08 17:53 UTC (permalink / raw)
To: pgsql-sql
Jamie:
I think you are probably having slowdown issues in your "DELETE FROM WHERE
NOT IN SELECT ORDER BY DESCENDING" construct -- that seems a bit convoluted
to me....
NOT IN is what is probably slowing you down the most....
ALSO: It looks to me like you have a column named "timestamp' ??? This is
bad practice since "timestamp" is a reserved word... You really ought NOT to
use reserved words for column names... different debate.
Why bother deleting records anyway ? Why not alter your query that tracks
the 50 records to LIMIT 50 ???
""Jamie Tufnell"" <diesql@googlemail.com> wrote in message
news:b0a4f3350801080831l54d4abedme99e2032b70c5608@mail.gmail.com...
> Hi codeWarrior,
>
> codeWarrior wrote:
>> > For user_ids that have more than 50 rows, I want to keep the most
>> > recent 50 and delete the rest.
>> How about using a trigger to call a stored procedure ? [ON INSERT to
>> user_item_history DO ...]
>
> [snip]
>
> Thanks for your input! I've implemented this but I'm concerned about
> performance. As I mentioned it's frequently being added to and this
> function will be called maybe a couple of times a second. In my brief
> initial tests it seems like this is running quite slowly...
>
> Just to make sure I haven't done anything obviously wrong, I've
> included my implementation below.. if you could look over it I'd
> really appreciate it.
>
> CREATE OR REPLACE FUNCTION user_item_history_limit() RETURNS TRIGGER
> AS $_$
> DECLARE
> threshold integer = 50;
> numrows integer;
> BEGIN
> SELECT INTO numrows count(*) FROM user_item_history WHERE user_id
> = new.user_id;
> IF numrows > threshold THEN
> DELETE FROM user_item_history WHERE user_id = new.user_id AND
> id NOT IN (
> SELECT id FROM user_item_history
> WHERE user_id = new.user_id
> ORDER BY timestamp DESC LIMIT threshold);
> RAISE NOTICE '% rows exceeds threshold of % for user_id %;
> trimming..', numrows, threshold, new.user_id;
> END IF;
> RETURN new;
> END;
> $_$
> LANGUAGE plpgsql;
>
> CREATE TRIGGER user_item_history_limit AFTER INSERT ON user_item_history
> FOR EACH ROW EXECUTE PROCEDURE user_item_history_limit();
>
> Any suggestions greatly appreciated!
>
> Thanks again,
> Jamie
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-08 18:28 Erik Jones <erik@myemma.com>
parent: Jamie Tufnell <diesql@googlemail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Erik Jones @ 2008-01-08 18:28 UTC (permalink / raw)
To: Jamie Tufnell <diesql@googlemail.com>; +Cc: pgsql-sql
On Jan 8, 2008, at 11:41 AM, Jamie Tufnell wrote:
> On 1/8/08, codeWarrior <gpatnude@hotmail.com> wrote:
>> Jamie:
>>
>> I think you are probably having slowdown issues in your "DELETE
>> FROM WHERE
>> NOT IN SELECT ORDER BY DESCENDING" construct -- that seems a bit
>> convoluted
>> to me....
>
> Hmm so rather than NOT IN ( .. LIMIT 50) would you suggest IN ( ...
> OFFSET 50) like in Erik's example? Or something else entirely?
Well, that would give you some gain. Think about it like this: once
a given user's history records are at 50 and you insert a row, if you
use the NOT IN clause your comparing each of 51 rows to each of the
50 you want to keep to find the one that can go while with the IN
version your comparing each of the 51 rows to the 1 that can go. Now
how much of a gain that will be I can't say, YMMV. I don't remember
you saying anything about it so I'll also go ahead and point out that
you most likely will want an index on user_id if you don't already.
>> ALSO: It looks to me like you have a column named "timestamp' ???
>> This is
>> bad practice since "timestamp" is a reserved word... You really
>> ought NOT to
>> use reserved words for column names... different debate.
>
> I do realize it would be better to use something else and thanks for
> the tip :-) This is an established database and "timestamp" has been
> used in other tables which is why I stuck to it here.. one day when
> time permits maybe I'll rename them all!
>
>> Why bother deleting records anyway ? Why not alter your query that
>> tracks
>> the 50 records to LIMIT 50 ???
>
> The read query does LIMIT 50 and the reason for deleting the rest of
> the records is because they're not needed by the application and
> there's loads of them being created all the time (currently several
> million unnecessary rows) -- I imagine eventually this will slow
> things down?
>
> Do you think a regular batch process to delete rows might be more
> appropriate than a trigger in this scenario?
That depends on your usage pattern. Assuming you aren't running user
history report queries constantly that's probably what I'd do. Also,
if you're sure you won't need anything but the last 50 records per
user, I'd definitely agree with cleaning out data that's not needed.
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-08 18:49 Jamie Tufnell <diesql@googlemail.com>
parent: Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 13+ messages in thread
From: Jamie Tufnell @ 2008-01-08 18:49 UTC (permalink / raw)
To: pgsql-sql
On 1/8/08, Erik Jones <erik@myemma.com> wrote:
> > Hmm so rather than NOT IN ( .. LIMIT 50) would you suggest IN ( ...
> > OFFSET 50) like in Erik's example? Or something else entirely?
>
> Well, that would give you some gain. Think about it like this: once
> a given user's history records are at 50 and you insert a row, if you
> use the NOT IN clause your comparing each of 51 rows to each of the
> 50 you want to keep to find the one that can go while with the IN
> version your comparing each of the 51 rows to the 1 that can go. Now
> how much of a gain that will be I can't say, YMMV. I don't remember
> you saying anything about it so I'll also go ahead and point out that
> you most likely will want an index on user_id if you don't already.
Thanks for the explanation Erik. I did already have the index, but
I've reimplemented using IN/OFFSET instead of NOT IN/LIMIT and it does
indeed seem to be faster.
> > Do you think a regular batch process to delete rows might be more
> > appropriate than a trigger in this scenario?
>
> That depends on your usage pattern. Assuming you aren't running user
> history report queries constantly that's probably what I'd do. Also,
> if you're sure you won't need anything but the last 50 records per
> user, I'd definitely agree with cleaning out data that's not needed.
OK cool, thanks for your advice Erik.
Cheers,
Jamie
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-09 18:20 Steve Midgley <public@misuse.org>
0 siblings, 1 reply; 13+ messages in thread
From: Steve Midgley @ 2008-01-09 18:20 UTC (permalink / raw)
To: pgsql-sql-owner@postgresql.org; pgsql-sql
At 07:20 AM 1/9/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Tue, 8 Jan 2008 17:41:18 +0000
>From: "Jamie Tufnell" <diesql@googlemail.com>
>To: pgsql-sql@postgresql.org
>Subject: Re: How to keep at-most N rows per group? periodic DELETEs or
>constraints or..?
>Message-ID:
><b0a4f3350801080941x5b4cccc9qbf6220ab35a0bf57@mail.gmail.com>
>
>On 1/8/08, codeWarrior <gpatnude@hotmail.com> wrote:
> > Jamie:
> >
> > I think you are probably having slowdown issues in your "DELETE
> FROM WHERE
> > NOT IN SELECT ORDER BY DESCENDING" construct -- that seems a bit
> convoluted
> > to me....
>
>Hmm so rather than NOT IN ( .. LIMIT 50) would you suggest IN ( ...
>OFFSET 50) like in Erik's example? Or something else entirely?
>
> > ALSO: It looks to me like you have a column named "timestamp' ???
> This is
> > bad practice since "timestamp" is a reserved word... You really
> ought NOT to
> > use reserved words for column names... different debate.
>
>I do realize it would be better to use something else and thanks for
>the tip This is an established database and "timestamp" has been
>used in other tables which is why I stuck to it here.. one day when
>time permits maybe I'll rename them all!
>
> > Why bother deleting records anyway ? Why not alter your query that
> tracks
> > the 50 records to LIMIT 50 ???
>
>The read query does LIMIT 50 and the reason for deleting the rest of
>the records is because they're not needed by the application and
>there's loads of them being created all the time (currently several
>million unnecessary rows) -- I imagine eventually this will slow
>things down?
>
>Do you think a regular batch process to delete rows might be more
>appropriate than a trigger in this scenario?
>
>Thanks,
>Jamie
This is kludgy but you would have some kind of random number test at
the start of the trigger - if it evals true once per every ten calls to
the trigger (say), you'd cut your delete statements execs by about 10x
and still periodically truncate every set of user rows fairly often. On
average you'd have ~55 rows per user, never less than 50 and a few
outliers with 60 or 70 rows before they get trimmed back down to 50..
Seems more reliable than a cron job, and solves your problem of an ever
growing table? You could adjust the random number test easily if you
change your mind of the balance of size of table vs. # of delete
statements down the road.
Steve
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-09 19:09 Scott Marlowe <scott.marlowe@gmail.com>
parent: Steve Midgley <public@misuse.org>
0 siblings, 1 reply; 13+ messages in thread
From: Scott Marlowe @ 2008-01-09 19:09 UTC (permalink / raw)
To: Steve Midgley <public@misuse.org>; +Cc: pgsql-sql-owner@postgresql.org; pgsql-sql
On Jan 9, 2008 12:20 PM, Steve Midgley <public@misuse.org> wrote:
> This is kludgy but you would have some kind of random number test at
> the start of the trigger - if it evals true once per every ten calls to
> the trigger (say), you'd cut your delete statements execs by about 10x
> and still periodically truncate every set of user rows fairly often. On
> average you'd have ~55 rows per user, never less than 50 and a few
> outliers with 60 or 70 rows before they get trimmed back down to 50..
> Seems more reliable than a cron job, and solves your problem of an ever
> growing table? You could adjust the random number test easily if you
> change your mind of the balance of size of table vs. # of delete
> statements down the road.
And, if you always through a limit 50 on the end of queries that
retrieve data, you could let it grow quite a bit more than 60 or 70...
Say 200. Then you could have it so that the random chopper function
only gets kicked off every 100th or so time.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-09 20:17 Erik Jones <erik@myemma.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Erik Jones @ 2008-01-09 20:17 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Steve Midgley <public@misuse.org>; pgsql-sql-owner@postgresql.org; pgsql-sql
On Jan 9, 2008, at 1:09 PM, Scott Marlowe wrote:
> On Jan 9, 2008 12:20 PM, Steve Midgley <public@misuse.org> wrote:
>> This is kludgy but you would have some kind of random number test at
>> the start of the trigger - if it evals true once per every ten
>> calls to
>> the trigger (say), you'd cut your delete statements execs by about
>> 10x
>> and still periodically truncate every set of user rows fairly
>> often. On
>> average you'd have ~55 rows per user, never less than 50 and a few
>> outliers with 60 or 70 rows before they get trimmed back down to 50..
>> Seems more reliable than a cron job, and solves your problem of an
>> ever
>> growing table? You could adjust the random number test easily if you
>> change your mind of the balance of size of table vs. # of delete
>> statements down the road.
>
> And, if you always through a limit 50 on the end of queries that
> retrieve data, you could let it grow quite a bit more than 60 or 70...
> Say 200. Then you could have it so that the random chopper function
> only gets kicked off every 100th or so time.
I like that idea.
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
@ 2008-01-09 23:30 Jamie Tufnell <diesql@googlemail.com>
parent: Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 13+ messages in thread
From: Jamie Tufnell @ 2008-01-09 23:30 UTC (permalink / raw)
To: pgsql-sql
On 1/9/08, Erik Jones <erik@myemma.com> wrote:
> On Jan 9, 2008, at 1:09 PM, Scott Marlowe wrote:
> > On Jan 9, 2008 12:20 PM, Steve Midgley <public@misuse.org> wrote:
> >> This is kludgy but you would have some kind of random number test at
> >> the start of the trigger - if it evals true once per every ten
> >> calls to
> >> the trigger (say), you'd cut your delete statements execs by about
> >> 10x
> >> and still periodically truncate every set of user rows fairly
> >> often.
> >
> > And, if you always through a limit 50 on the end of queries that
> > retrieve data, you could let it grow quite a bit more than 60 or 70...
> > Say 200. Then you could have it so that the random chopper function
> > only gets kicked off every 100th or so time.
>
> I like that idea.
I do too! I'm going to have a shot at implementing this tomorrow.
Thanks for all your opinions guys :-)
Cheers,
Jamie
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2008-01-09 23:30 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-08 14:24 How to keep at-most N rows per group? periodic DELETEs or constraints or..? Jamie Tufnell <diesql@googlemail.com>
2008-01-08 15:45 ` codeWarrior <gpatnude@hotmail.com>
2008-01-08 16:31 ` Jamie Tufnell <diesql@googlemail.com>
2008-01-08 17:53 ` codeWarrior <gpatnude@hotmail.com>
2008-01-08 17:41 ` Jamie Tufnell <diesql@googlemail.com>
2008-01-08 18:28 ` Erik Jones <erik@myemma.com>
2008-01-08 18:49 ` Jamie Tufnell <diesql@googlemail.com>
2008-01-08 16:30 ` Erik Jones <erik@myemma.com>
2008-01-08 17:31 Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..? Steve Midgley <public@misuse.org>
2008-01-09 18:20 Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..? Steve Midgley <public@misuse.org>
2008-01-09 19:09 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-01-09 20:17 ` Erik Jones <erik@myemma.com>
2008-01-09 23:30 ` Jamie Tufnell <diesql@googlemail.com>
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