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..?
Date: Tue, 8 Jan 2008 17:41:18 +0000
Message-ID: <b0a4f3350801080941x5b4cccc9qbf6220ab35a0bf57@mail.gmail.com> (raw)
In-Reply-To: <fm09ql$29q5$1@news.hub.org>
References: <b0a4f3350801080624k2088c96aq21c511873959aa87@mail.gmail.com>
<fm028v$106a$1@news.hub.org>
<b0a4f3350801080805p3d3a0325rb70936c9fe3a812a@mail.gmail.com>
<b0a4f3350801080831l54d4abedme99e2032b70c5608@mail.gmail.com>
<fm09ql$29q5$1@news.hub.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: diesql@googlemail.com
Subject: Re: How to keep at-most N rows per group? periodic DELETEs or constraints or..?
In-Reply-To: <b0a4f3350801080941x5b4cccc9qbf6220ab35a0bf57@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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