Date: Sat, 17 Jun 2006 11:16:13 -0400
From: Daniel CAUNE <d.caune@free.fr>
Subject: Re: keeping last 30 entries of a log table
In-reply-to: 
 <Pine.LNX.4.64.0606161924550.27250@glacier.frostconsultingllc.com>
To: 'Jeff Frost' <jeff@frostconsultingllc.com>, pgsql-sql@postgresql.org
Message-id: <0J10001JS4N1G9G0@VL-MH-MR001.ip.videotron.ca>
MIME-version: 1.0
Content-type: text/plain; charset=utf-8
Content-transfer-encoding: 7BIT
Thread-index: AcaRtfJ8slSJN/gvRZ+LLedb9oVJogAaiEnQ

> 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