Thread: Enhancement Request
It would be nice if the DELETE FROM supported LIMIT. I can already feel the dislike for this post coming back in my direction.
I have had numerous times over the years where I need to delete a lot of rows from a table but because each row is linked to so many other tables it will cause a problem in performance if I don’t limit the size of the deletion.
Just throwing this out there.
Thanks,
Lance
Use a CTE with a limit on the select and then delete everything from the CTE.
Campbell, Lance wrote on 1/31/2024 2:29 PM:
It would be nice if the DELETE FROM supported LIMIT. I can already feel the dislike for this post coming back in my direction.
I have had numerous times over the years where I need to delete a lot of rows from a table but because each row is linked to so many other tables it will cause a problem in performance if I don’t limit the size of the deletion.
Just throwing this out there.
Thanks,
Lance
Regards,
Michael Vitale
703-600-9343
Attachment
> On 31 Jan 2024, at 14:32, MichaelDBA <MichaelDBA@sqlexec.com> wrote: > > Cheat a bit.... > Use a CTE with a limit on the select and then delete everything from the CTE. Whoa! Thank you for that idea. That's thinking with portals. -- Alex Balashov Principal Consultant Evariste Systems LLC Web: https://evaristesys.com Tel: +1-706-510-6800
// It would be nice if the DELETE FROM supported LIMIT. I can already feel the dislike for this post coming back in my direction.
// I have had numerous times over the years where I need to delete a lot of rows from a table but because each row is linked to so many other tables it will cause // a problem in performance if I don’t limit the size of the deletion.
I use a query like this to accomplish a limited deletion –
Delete from table1 where table1.id in (select table1.id from table1 limit yourlimitnumber)
Sent: Wednesday, January 31, 2024 2:34 PM
To: Campbell, Lance <lance@illinois.edu>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: RE: Enhancement Request
// It would be nice if the DELETE FROM supported LIMIT. I can already feel the dislike for this post coming back in my direction.
// I have had numerous times over the years where I need to delete a lot of rows from a table but because each row is linked to so many other tables it will cause // a problem in performance if I don’t limit the size of the deletion.
I use a query like this to accomplish a limited deletion –
Delete from table1 where table1.id in (select table1.id from table1 limit yourlimitnumber)
No. I was submitting a request that we could add LIMIT to DELETE FROM. There are work arounds for this. But it would be nice to skip the work arounds.
From: M Sarwar <sarwarmd02@outlook.com>
Sent: Wednesday, January 31, 2024 2:47 PM
To: Hajek, Nick <Nick.Hajek@vishay.com>; Campbell, Lance <lance@illinois.edu>; pgsql-admin@postgresql.org
Subject: Re: Enhancement Request
Nick,
Are you confirming that DELETE FROM - LIMIT is working?
Thanks,
Sarwar
From: Hajek, Nick <Nick.Hajek@vishay.com>
Sent: Wednesday, January 31, 2024 2:34 PM
To: Campbell, Lance <lance@illinois.edu>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: RE: Enhancement Request
// It would be nice if the DELETE FROM supported LIMIT. I can already feel the dislike for this post coming back in my direction.
// I have had numerous times over the years where I need to delete a lot of rows from a table but because each row is linked to so many other tables it will cause // a problem in performance if I don’t limit the size of the deletion.
I use a query like this to accomplish a limited deletion –
Delete from table1 where table1.id in (select table1.id from table1 limit yourlimitnumber)
[ External Email ]
// Are you confirming that DELETE FROM - LIMIT is working?
No, I don’t believe you can use limit in the delete itself but you can use it in a subselect and accomplish the same thing as shown in the example below.
// It would be nice if the DELETE FROM supported LIMIT. I can already feel the dislike for this post coming back in my direction.
// I have had numerous times over the years where I need to delete a lot of rows from a table but because each row is linked to so many other tables it will cause // a problem in performance if I don’t limit the size of the deletion.
I use a query like this to accomplish a limited deletion –
Delete from table1 where table1.id in (select table1.id from table1 limit yourlimitnumber)
Sent: Wednesday, January 31, 2024 3:50 PM
To: M Sarwar <sarwarmd02@outlook.com>; Campbell, Lance <lance@illinois.edu>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: RE: Enhancement Request
[ External Email ]
// Are you confirming that DELETE FROM - LIMIT is working?
No, I don’t believe you can use limit in the delete itself but you can use it in a subselect and accomplish the same thing as shown in the example below.
// It would be nice if the DELETE FROM supported LIMIT. I can already feel the dislike for this post coming back in my direction.
// I have had numerous times over the years where I need to delete a lot of rows from a table but because each row is linked to so many other tables it will cause // a problem in performance if I don’t limit the size of the deletion.
I use a query like this to accomplish a limited deletion –
Delete from table1 where table1.id in (select table1.id from table1 limit yourlimitnumber)
If they allow DELETE FROM-LIMT to work, it is going to harm the general interest of the database.In my opinion, that should not be implemented.
When we use LIMIT clause in the SQL, SQL may be used in with / without ORDER of ASC / DESC, UUID, Pseudo columns, ROWNUM, etc clauses. When we use ORDER by, LIMIT may be ok to DELETE but in the ROWNUM, other pseudo columns presence impact is hard to foresee.
Sent: Wednesday, January 31, 2024 8:31 PM
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Enhancement Request
If they allow DELETE FROM-LIMT to work, it is going to harm the general interest of the database.In my opinion, that should not be implemented.
You may collect the opinion from few others and make a decision on this.
On Wed, Jan 31, 2024 at 3:51 PM Hajek, Nick <Nick.Hajek@vishay.com> wrote:[snip]The IN predicate is only efficient for a very small number of elements, supported by an index. People (including me) who would find DELETE FROM .. LIMIT TO ... useful want to delete a *lot* of rows (but not all in one giant statement).
El jue, 1 feb 2024 2:35, Ron Johnson <ronljohnsonjr@gmail.com> escribió:On Wed, Jan 31, 2024 at 3:51 PM Hajek, Nick <Nick.Hajek@vishay.com> wrote:[snip]The IN predicate is only efficient for a very small number of elements, supported by an index. People (including me) who would find DELETE FROM .. LIMIT TO ... useful want to delete a *lot* of rows (but not all in one giant statement).Deleting large numbers of rows is a complex task with a lot of hidden issues (index management between other things). Adding a LIMIT paradigm will not simplify it in any way.
I remember doing it on tables with over 50 millions rows and had my share of disaster recoveries. Partitions saved my life.
Sent: Thursday, February 1, 2024 9:15 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>; pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Enhancement Request
You may collect the opinion from few others and make a decision on this.
On Fri, Feb 2, 2024 at 3:50 AM Olivier Gautherot <ogautherot@gautherot.net> wrote:
Deleting large numbers of rows is a complex task with a lot of hidden issues (index management between other things). Adding a LIMIT paradigm will not simplify it in any way.Smaller "bites" are easier to manage than giant bites.
I remember doing it on tables with over 50 millions rows and had my share of disaster recoveries. Partitions saved my life.You must have been doing something wrong.
El vie, 2 feb 2024 14:54, Ron Johnson <ronljohnsonjr@gmail.com> escribió:On Fri, Feb 2, 2024 at 3:50 AM Olivier Gautherot <ogautherot@gautherot.net> wrote:Deleting large numbers of rows is a complex task with a lot of hidden issues (index management between other things). Adding a LIMIT paradigm will not simplify it in any way.Smaller "bites" are easier to manage than giant bites.To some extent, yes. But when it comes to large quantities overall, you have to consider vacuum,
and it's best to take the DB offline for that. It depends on your use case.