pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedkeeping last 30 entries of a log table
8+ messages / 4 participants
[nested] [flat]
* keeping last 30 entries of a log table
@ 2006-06-17 02:29 Jeff Frost <jeff@frostconsultingllc.com>
2006-06-17 15:16 ` Re: keeping last 30 entries of a log table Daniel CAUNE <d.caune@free.fr>
2006-06-21 17:49 ` Re: keeping last 30 entries of a log table Ben K. <bkim@coe.tamu.edu>
0 siblings, 2 replies; 8+ messages in thread
From: Jeff Frost @ 2006-06-17 02:29 UTC (permalink / raw)
To: pgsql-sql
I need to write a function which inserts a log entry in a log table and only
keeps the last 30 records. I was thinking of using a subquery similar to the
following:
insert into log (account_id, message) values (1, 'this is a test);
delete from log where account_id = 1 and id not in ( select id from log
where account_id = 1 order by timestamp desc limit 30);
I'm wondering if there is a more performance oriented method of doing the
delete that I'm not thinking of.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: keeping last 30 entries of a log table
2006-06-17 02:29 keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
@ 2006-06-17 15:16 ` Daniel CAUNE <d.caune@free.fr>
2006-06-19 16:16 ` Re: keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 1 reply; 8+ messages in thread
From: Daniel CAUNE @ 2006-06-17 15:16 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; pgsql-sql
> I need to write a function which inserts a log entry in a log table and
> only
> keeps the last 30 records. I was thinking of using a subquery similar to
> the
> following:
>
> insert into log (account_id, message) values (1, 'this is a test);
> delete from log where account_id = 1 and id not in ( select id from log
> where account_id = 1 order by timestamp desc limit 30);
>
> I'm wondering if there is a more performance oriented method of doing the
> delete that I'm not thinking of.
>
Depending on whether id is a kind of auto-incremented column that never cycles, I would suggest something like:
DELETE FROM log
WHERE account_id = 1
AND id < (
SELECT MIN(id)
FROM log
WHERE account_id = 1
ORDER BY timestamp DESC
LIMIT 30);
I think there will be a performance difference with your method when the number of records to be deleted is huge.
--
Daniel
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: keeping last 30 entries of a log table
2006-06-17 02:29 keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
2006-06-17 15:16 ` Re: keeping last 30 entries of a log table Daniel CAUNE <d.caune@free.fr>
@ 2006-06-19 16:16 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-06-19 17:13 ` Re: keeping last 30 entries of a log table Daniel CAUNE <d.caune@free.fr>
0 siblings, 1 reply; 8+ messages in thread
From: Jeff Frost @ 2006-06-19 16:16 UTC (permalink / raw)
To: Daniel CAUNE <d.caune@free.fr>; +Cc: pgsql-sql
On Sat, 17 Jun 2006, Daniel CAUNE wrote:
>> insert into log (account_id, message) values (1, 'this is a test);
>> delete from log where account_id = 1 and id not in ( select id from log
>> where account_id = 1 order by timestamp desc limit 30);
>>
>> I'm wondering if there is a more performance oriented method of doing the
>> delete that I'm not thinking of.
>>
>
> Depending on whether id is a kind of auto-incremented column that never cycles, I would suggest something like:
>
> DELETE FROM log
> WHERE account_id = 1
> AND id < (
> SELECT MIN(id)
> FROM log
> WHERE account_id = 1
> ORDER BY timestamp DESC
> LIMIT 30);
>
> I think there will be a performance difference with your method when the number of records to be deleted is huge.
Thanks Daniel, I'll try and benchmark them both and see if < turns out to be
faster than NOT IN. I guess there's no way to get around the subselect
though.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: keeping last 30 entries of a log table
2006-06-17 02:29 keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
2006-06-17 15:16 ` Re: keeping last 30 entries of a log table Daniel CAUNE <d.caune@free.fr>
2006-06-19 16:16 ` Re: keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
@ 2006-06-19 17:13 ` Daniel CAUNE <d.caune@free.fr>
2006-06-20 13:48 ` Re: keeping last 30 entries of a log table Dirk Jagdmann <jagdmann@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Daniel CAUNE @ 2006-06-19 17:13 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
> >> insert into log (account_id, message) values (1, 'this is a test);
> >> delete from log where account_id = 1 and id not in ( select id from log
> >> where account_id = 1 order by timestamp desc limit 30);
> >>
> >> I'm wondering if there is a more performance oriented method of doing
> the
> >> delete that I'm not thinking of.
> >>
> >
> > Depending on whether id is a kind of auto-incremented column that never
> cycles, I would suggest something like:
> >
> > DELETE FROM log
> > WHERE account_id = 1
> > AND id < (
> > SELECT MIN(id)
> > FROM log
> > WHERE account_id = 1
> > ORDER BY timestamp DESC
> > LIMIT 30);
> >
> > I think there will be a performance difference with your method when the
> number of records to be deleted is huge.
>
> Thanks Daniel, I'll try and benchmark them both and see if < turns out to
> be
> faster than NOT IN. I guess there's no way to get around the subselect
> though.
>
Column id should be indexed indeed. Anyway, I'm not sure about any performance improvement using that last method, as the most consuming operation might be the DELETE operation, not really the SELECT operation, when dealing with a huge volume of data.
--
Daniel
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: keeping last 30 entries of a log table
2006-06-17 02:29 keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
2006-06-17 15:16 ` Re: keeping last 30 entries of a log table Daniel CAUNE <d.caune@free.fr>
2006-06-19 16:16 ` Re: keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
2006-06-19 17:13 ` Re: keeping last 30 entries of a log table Daniel CAUNE <d.caune@free.fr>
@ 2006-06-20 13:48 ` Dirk Jagdmann <jagdmann@gmail.com>
2006-06-20 14:12 ` Re: keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 1 reply; 8+ messages in thread
From: Dirk Jagdmann @ 2006-06-20 13:48 UTC (permalink / raw)
To: Daniel CAUNE <d.caune@free.fr>; +Cc: Jeff Frost <jeff@frostconsultingllc.com>; pgsql-sql
> Column id should be indexed indeed. Anyway, I'm not sure about any performance improvement using that last method, as the most consuming operation might be the DELETE operation, not really the SELECT operation, when dealing with a huge volume of data.
why worry, there are a maximum of 30 Entries in this table anyway. So
even the most unoptimized select and delete combinations should be
very fast...
--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: keeping last 30 entries of a log table
2006-06-17 02:29 keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
2006-06-17 15:16 ` Re: keeping last 30 entries of a log table Daniel CAUNE <d.caune@free.fr>
2006-06-19 16:16 ` Re: keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
2006-06-19 17:13 ` Re: keeping last 30 entries of a log table Daniel CAUNE <d.caune@free.fr>
2006-06-20 13:48 ` Re: keeping last 30 entries of a log table Dirk Jagdmann <jagdmann@gmail.com>
@ 2006-06-20 14:12 ` Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jeff Frost @ 2006-06-20 14:12 UTC (permalink / raw)
To: Dirk Jagdmann <jagdmann@gmail.com>; +Cc: Daniel CAUNE <d.caune@free.fr>; pgsql-sql
On Tue, 20 Jun 2006, Dirk Jagdmann wrote:
>> Column id should be indexed indeed. Anyway, I'm not sure about any
>> performance improvement using that last method, as the most consuming
>> operation might be the DELETE operation, not really the SELECT operation,
>> when dealing with a huge volume of data.
>
> why worry, there are a maximum of 30 Entries in this table anyway. So
> even the most unoptimized select and delete combinations should be
> very fast...
Well, it's 30 entries per account and likely 1000s of accounts, but still that
would only be 30,000-90,000 entries, so shouldn't be too bad.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: keeping last 30 entries of a log table
2006-06-17 02:29 keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
@ 2006-06-21 17:49 ` Ben K. <bkim@coe.tamu.edu>
2006-06-21 17:57 ` Re: keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 1 reply; 8+ messages in thread
From: Ben K. @ 2006-06-21 17:49 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
> I need to write a function which inserts a log entry in a log table and only
> keeps the last 30 records. I was thinking of using a subquery similar to the
> following:
>
> insert into log (account_id, message) values (1, 'this is a test);
> delete from log where account_id = 1 and id not in ( select id from log
> where account_id = 1 order by timestamp desc limit 30);
>
> I'm wondering if there is a more performance oriented method of doing the
> delete that I'm not thinking of.
Just for the sake of alternatives -
create sequence cy30 maxvalue 30 cycle;
insert into log values(select generate_series(1,30), 'dummy');
INSERT 0 30
update log set des='....' where account_id=(select nextval('cy30'));
UPDATE 1
There are details to consider I guess. For example what if an update fails
and the sequence already advanced... Also, since we cycle the id, for
sorting, we'll need to add timestamp or something similar.
My 2 pence...
P.S.
This A) failed me and I wonder if this is supposed to be so or if it's
just a place where no one treaded on ??
B) works fine except it doesn't advance the sequence.
A) update tc set des='b' where id=nextval('cy30')::int;
UPDATE 30
B) update tc set des='c' where id=currval('cy30');
UPDATE 1
Regards,
Ben K.
Developer
http://benix.tamu.edu
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: keeping last 30 entries of a log table
2006-06-17 02:29 keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
2006-06-21 17:49 ` Re: keeping last 30 entries of a log table Ben K. <bkim@coe.tamu.edu>
@ 2006-06-21 17:57 ` Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jeff Frost @ 2006-06-21 17:57 UTC (permalink / raw)
To: Ben K. <bkim@coe.tamu.edu>; +Cc: pgsql-sql
On Wed, 21 Jun 2006, Ben K. wrote:
> Just for the sake of alternatives -
>
> create sequence cy30 maxvalue 30 cycle;
>
> insert into log values(select generate_series(1,30), 'dummy');
> INSERT 0 30
>
> update log set des='....' where account_id=(select nextval('cy30'));
> UPDATE 1
>
>
> There are details to consider I guess. For example what if an update fails
> and the sequence already advanced... Also, since we cycle the id, for
> sorting, we'll need to add timestamp or something similar.
>
Only problem with this approach is that I need to be able to keep track of the
last 30 items per each account_id...so each account_id will have his last 30
messages in the table.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-06-21 17:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-17 02:29 keeping last 30 entries of a log table Jeff Frost <jeff@frostconsultingllc.com>
2006-06-17 15:16 ` Daniel CAUNE <d.caune@free.fr>
2006-06-19 16:16 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-06-19 17:13 ` Daniel CAUNE <d.caune@free.fr>
2006-06-20 13:48 ` Dirk Jagdmann <jagdmann@gmail.com>
2006-06-20 14:12 ` Jeff Frost <jeff@frostconsultingllc.com>
2006-06-21 17:49 ` Ben K. <bkim@coe.tamu.edu>
2006-06-21 17:57 ` Jeff Frost <jeff@frostconsultingllc.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