Обсуждение: How to delete the oldest X number of rows?
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
+-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
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 ===
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