Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to delete the oldest X number of rows?

How to delete the oldest X number of rows?

From
"John Roberts"
Date:
Hi,

I have a table that logs the performance of a system. I get an entry in this
table each time the system does something.

I want to keep the last 50,000 rows in the table. I'd like to run a cron job
every 5 minutes that will delete all rows > 50,000, oldest first. What's the
most efficient way to do this query/delete?

DELETE FROM log WHERE timestamp < ????

I can come up with a way to do this using COUNT(*), but I'll bet there is a
smarter way.

THANKS for your help!

John

Re: How to delete the oldest X number of rows?

From
Mathieu Arnold
Date:

+-le 12/12/2005 11:29 -0500, John Roberts a dit :
| Hi,
|
| I have a table that logs the performance of a system. I get an entry in
| this table each time the system does something.
|
| I want to keep the last 50,000 rows in the table. I'd like to run a cron
| job every 5 minutes that will delete all rows > 50,000, oldest first.
| What's the most efficient way to do this query/delete?
|
| DELETE FROM log WHERE timestamp < ????
|
| I can come up with a way to do this using COUNT(*), but I'll bet there is a
| smarter way.

Would that :
DELETE FROM log WHERE timestamp < (select timestamp from log order by
timestamp desc limit 1 offset 50000);

do ?

--
Mathieu Arnold

Re: How to delete the oldest X number of rows?

From
"A. Kretschmer"
Date:
am  12.12.2005, um 11:29:58 -0500 mailte John Roberts folgendes:
> Hi,
>
> I have a table that logs the performance of a system. I get an entry in
> this table each time the system does something.
>
> I want to keep the last 50,000 rows in the table. I'd like to run a cron
> job every 5 minutes that will delete all rows > 50,000, oldest first.
> What's the most efficient way to do this query/delete?
>
> DELETE FROM log WHERE timestamp < ????

Something like:

select timestamp from log order by timestamp limit 1 offset 49999;

to get the oldest timestamp to keep and delete the early rows?


*untestet*

delete from fol where timestamp < (select timestamp from log order by
timestamp limit 1 offset 49999);

HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Re: How to delete the oldest X number of rows?

From
"John Roberts"
Date:
Thank you! That works beautifully!

John

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of A. Kretschmer
Sent: Monday, December 12, 2005 11:47 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] How to delete the oldest X number of rows?
Importance: High

am  12.12.2005, um 11:29:58 -0500 mailte John Roberts folgendes:
> Hi,
>
> I have a table that logs the performance of a system. I get an entry in
> this table each time the system does something.
>
> I want to keep the last 50,000 rows in the table. I'd like to run a cron
> job every 5 minutes that will delete all rows > 50,000, oldest first.
> What's the most efficient way to do this query/delete?
>
> DELETE FROM log WHERE timestamp < ????

Something like:

select timestamp from log order by timestamp limit 1 offset 49999;

to get the oldest timestamp to keep and delete the early rows?


*untestet*

delete from fol where timestamp < (select timestamp from log order by
timestamp limit 1 offset 49999);

HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings