pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete Enhancement Request
9+ messages / 8 participants
[nested] [flat]
* Delete Enhancement Request
@ 2019-01-14 15:18 Campbell, Lance <lance@illinois.edu>
0 siblings, 3 replies; 9+ messages in thread
From: Campbell, Lance @ 2019-01-14 15:18 UTC (permalink / raw)
To: pgsql-admin
Please consider adding this feature to PostgreSQL.
Use Case:
When deleting a large number of records with constraints and triggers PostgreSQL is exceptionally slow.
Suggested change:
I believe a way to speed this processes up would be to offer an option a user could “turn on” prior to doing the delete.
Example: set delete-no-roll-back=true
Now as PostgreSQL deletes rows it literally deletes them one at a time as though the user was only deleting a single row. This means that if cancel were executed on a delete action then it would only roll back the current row that PostgreSQL was in the process of deleting. Example: if you had 10 million row to delete and then pressed cancel after three minutes maybe 5 million are deleted.
Thanks,
Lance Campbell
Web Services
University of Illinois
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Delete Enhancement Request
@ 2019-01-14 16:08 Ron <ronljohnsonjr@gmail.com>
parent: Campbell, Lance <lance@illinois.edu>
2 siblings, 1 reply; 9+ messages in thread
From: Ron @ 2019-01-14 16:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 1/14/19 9:18 AM, Campbell, Lance wrote:
>
> Please consider adding this feature to PostgreSQL.
>
> Use Case:
>
> When deleting a large number of records with constraints and triggers
> PostgreSQL is exceptionally slow.
>
> Suggested change:
>
> I believe a way to speed this processes up would be to offer an option a
> user could “turn on” prior to doing the delete.
>
> Example: set delete-no-roll-back=true
>
> Now as PostgreSQL deletes rows it literally deletes them one at a time as
> though the user was only deleting a single row. This means that if cancel
> were executed on a delete action then it would only roll back the current
> row that PostgreSQL was in the process of deleting. Example: if you had
> 10 million row to delete and then pressed cancel after three minutes maybe
> 5 million are deleted.
>
You seem to be asking for an unlogged transaction, but that doesn't have
anything to do with efficiently deleting rows.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Delete Enhancement Request
@ 2019-01-15 00:50 Paul Carlucci <paul.carlucci@gmail.com>
parent: Campbell, Lance <lance@illinois.edu>
2 siblings, 0 replies; 9+ messages in thread
From: Paul Carlucci @ 2019-01-15 00:50 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin
Do you mean like each row deleted is a separate transaction? The fact that
you canceled and only half had actually committed makes me say yes.
On Mon, Jan 14, 2019, 10:18 AM Campbell, Lance <lance@illinois.edu wrote:
> Please consider adding this feature to PostgreSQL.
>
>
>
> Use Case:
>
> When deleting a large number of records with constraints and triggers
> PostgreSQL is exceptionally slow.
>
>
>
> Suggested change:
>
> I believe a way to speed this processes up would be to offer an option a
> user could “turn on” prior to doing the delete.
>
>
>
> Example: set delete-no-roll-back=true
>
>
>
> Now as PostgreSQL deletes rows it literally deletes them one at a time as
> though the user was only deleting a single row. This means that if cancel
> were executed on a delete action then it would only roll back the current
> row that PostgreSQL was in the process of deleting. Example: if you had
> 10 million row to delete and then pressed cancel after three minutes maybe
> 5 million are deleted.
>
>
>
> Thanks,
>
>
>
> Lance Campbell
>
> Web Services
>
> University of Illinois
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Delete Enhancement Request
@ 2019-01-15 01:41 David G. Johnston <david.g.johnston@gmail.com>
parent: Campbell, Lance <lance@illinois.edu>
2 siblings, 1 reply; 9+ messages in thread
From: David G. Johnston @ 2019-01-15 01:41 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin
On Mon, Jan 14, 2019 at 8:18 AM Campbell, Lance <lance@illinois.edu> wrote:
> Use Case:
> When deleting a large number of records with constraints and triggers PostgreSQL is exceptionally slow.
You may wish to provide an explicit situation that you are
experiencing so that others may offer their experienced insights as to
whether you could be doing anything different to increase the speed of
your deletes or techniques to batch them yourself.
I fail to comprehend how "ability to abort deleting mid-stream and not
lose any progress" is a solution to "deleting...is exceptionally slow"
- its more likely your proposed feature would increase the total time
spent performing the full delete.
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Delete Enhancement Request
@ 2019-01-15 01:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 2019-01-15 01:46 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Campbell, Lance <lance@illinois.edu>; pgsql-admin
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jan 14, 2019 at 8:18 AM Campbell, Lance <lance@illinois.edu> wrote:
>> When deleting a large number of records with constraints and triggers PostgreSQL is exceptionally slow.
> You may wish to provide an explicit situation that you are
> experiencing so that others may offer their experienced insights as to
> whether you could be doing anything different to increase the speed of
> your deletes or techniques to batch them yourself.
Given the mention of constraints, I'm suspicious that the problem is
foreign key constraint(s) that lack an index on the referencing column(s).
PG will let you do that, but if you care about the speed of deletes on
the referenced table, you don't want to omit the index on the referencing
side.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Delete Enhancement Request
@ 2019-01-15 15:56 Sridhara KB <sridhara.kb@digitalapicraft.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Sridhara KB @ 2019-01-15 15:56 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi can anyone help me to get incremental backup without using third party tool like barman , rman etc...
Thank you,
Sridhara k.b
> On 14-Jan-2019, at 9:38 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>
>> On 1/14/19 9:18 AM, Campbell, Lance wrote:
>> Please consider adding this feature to PostgreSQL.
>>
>> Use Case:
>> When deleting a large number of records with constraints and triggers PostgreSQL is exceptionally slow.
>>
>> Suggested change:
>> I believe a way to speed this processes up would be to offer an option a user could “turn on” prior to doing the delete.
>>
>> Example: set delete-no-roll-back=true
>>
>> Now as PostgreSQL deletes rows it literally deletes them one at a time as though the user was only deleting a single row. This means that if cancel were executed on a delete action then it would only roll back the current row that PostgreSQL was in the process of deleting. Example: if you had 10 million row to delete and then pressed cancel after three minutes maybe 5 million are deleted.
>
> You seem to be asking for an unlogged transaction, but that doesn't have anything to do with efficiently deleting rows.
>
>
> --
> Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Delete Enhancement Request
@ 2019-01-15 17:10 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Sridhara KB <sridhara.kb@digitalapicraft.com>
0 siblings, 1 reply; 9+ messages in thread
From: Shreeyansh Dba @ 2019-01-15 17:10 UTC (permalink / raw)
To: Sridhara KB <sridhara.kb@digitalapicraft.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi Sridhara,
For an incremental backup, you can use pgBackRest.
Please go through the below link...
https://pgbackrest.org/
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Tue, Jan 15, 2019 at 9:26 PM Sridhara KB <sridhara.kb@digitalapicraft.com>
wrote:
> Hi can anyone help me to get incremental backup without using third party
> tool like barman , rman etc...
>
> Thank you,
> Sridhara k.b
>
> On 14-Jan-2019, at 9:38 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> On 1/14/19 9:18 AM, Campbell, Lance wrote:
>
> Please consider adding this feature to PostgreSQL.
>
>
>
> Use Case:
>
> When deleting a large number of records with constraints and triggers
> PostgreSQL is exceptionally slow.
>
>
>
> Suggested change:
>
> I believe a way to speed this processes up would be to offer an option a
> user could “turn on” prior to doing the delete.
>
>
>
> Example: set delete-no-roll-back=true
>
>
>
> Now as PostgreSQL deletes rows it literally deletes them one at a time as
> though the user was only deleting a single row. This means that if cancel
> were executed on a delete action then it would only roll back the current
> row that PostgreSQL was in the process of deleting. Example: if you had
> 10 million row to delete and then pressed cancel after three minutes maybe
> 5 million are deleted.
>
>
> You seem to be asking for an unlogged transaction, but that doesn't have
> anything to do with efficiently deleting rows.
>
>
> --
> Angular momentum makes the world go 'round.
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Delete Enhancement Request
@ 2019-01-15 18:48 Ron <ronljohnsonjr@gmail.com>
parent: Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ron @ 2019-01-15 18:48 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Which is a 3rd party tool.
On 1/15/19 11:10 AM, Shreeyansh Dba wrote:
> Hi Sridhara,
>
> For an incremental backup, you can use pgBackRest.
>
> Please go through the below link...
> https://pgbackrest.org/
>
> Thanks & Regards,
> *Shreeyansh DBA Team*
> www.shreeyansh.com <http://www.shreeyansh.com;
>
>
> On Tue, Jan 15, 2019 at 9:26 PM Sridhara KB
> <sridhara.kb@digitalapicraft.com <mailto:sridhara.kb@digitalapicraft.com>>
> wrote:
>
> Hi can anyone help me to get incremental backup without using third
> party tool like barman , rman etc...
>
> Thank you,
> Sridhara k.b
>
> On 14-Jan-2019, at 9:38 PM, Ron <ronljohnsonjr@gmail.com
> <mailto:ronljohnsonjr@gmail.com>> wrote:
>
>> On 1/14/19 9:18 AM, Campbell, Lance wrote:
>>>
>>> Please consider adding this feature to PostgreSQL.
>>>
>>> Use Case:
>>>
>>> When deleting a large number of records with constraints and
>>> triggers PostgreSQL is exceptionally slow.
>>>
>>> Suggested change:
>>>
>>> I believe a way to speed this processes up would be to offer an
>>> option a user could “turn on” prior to doing the delete.
>>>
>>> Example: set delete-no-roll-back=true
>>>
>>> Now as PostgreSQL deletes rows it literally deletes them one at a
>>> time as though the user was only deleting a single row. This means
>>> that if cancel were executed on a delete action then it would only
>>> roll back the current row that PostgreSQL was in the process of
>>> deleting. Example: if you had 10 million row to delete and then
>>> pressed cancel after three minutes maybe 5 million are deleted.
>>>
>>
>> You seem to be asking for an unlogged transaction, but that doesn't
>> have anything to do with efficiently deleting rows.
>>
>>
>> --
>> Angular momentum makes the world go 'round.
>
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Delete Enhancement Request
@ 2019-01-16 21:08 Ervin Weber <webervin@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Ervin Weber @ 2019-01-16 21:08 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Where do you plan to store backups?
On Tue, Jan 15, 2019, 20:48 Ron <ronljohnsonjr@gmail.com wrote:
> Which is a 3rd party tool.
>
> On 1/15/19 11:10 AM, Shreeyansh Dba wrote:
>
> Hi Sridhara,
>
> For an incremental backup, you can use pgBackRest.
>
> Please go through the below link...
> https://pgbackrest.org/
>
> Thanks & Regards,
> *Shreeyansh DBA Team*
> www.shreeyansh.com
>
>
> On Tue, Jan 15, 2019 at 9:26 PM Sridhara KB <
> sridhara.kb@digitalapicraft.com> wrote:
>
>> Hi can anyone help me to get incremental backup without using third party
>> tool like barman , rman etc...
>>
>> Thank you,
>> Sridhara k.b
>>
>> On 14-Jan-2019, at 9:38 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> On 1/14/19 9:18 AM, Campbell, Lance wrote:
>>
>> Please consider adding this feature to PostgreSQL.
>>
>>
>>
>> Use Case:
>>
>> When deleting a large number of records with constraints and triggers
>> PostgreSQL is exceptionally slow.
>>
>>
>>
>> Suggested change:
>>
>> I believe a way to speed this processes up would be to offer an option a
>> user could “turn on” prior to doing the delete.
>>
>>
>>
>> Example: set delete-no-roll-back=true
>>
>>
>>
>> Now as PostgreSQL deletes rows it literally deletes them one at a time as
>> though the user was only deleting a single row. This means that if cancel
>> were executed on a delete action then it would only roll back the current
>> row that PostgreSQL was in the process of deleting. Example: if you had
>> 10 million row to delete and then pressed cancel after three minutes maybe
>> 5 million are deleted.
>>
>>
>> You seem to be asking for an unlogged transaction, but that doesn't have
>> anything to do with efficiently deleting rows.
>>
>>
>> --
>> Angular momentum makes the world go 'round.
>>
>>
> --
> Angular momentum makes the world go 'round.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2019-01-16 21:08 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-14 15:18 Delete Enhancement Request Campbell, Lance <lance@illinois.edu>
2019-01-14 16:08 ` Ron <ronljohnsonjr@gmail.com>
2019-01-15 15:56 ` Sridhara KB <sridhara.kb@digitalapicraft.com>
2019-01-15 17:10 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-01-15 18:48 ` Ron <ronljohnsonjr@gmail.com>
2019-01-16 21:08 ` Ervin Weber <webervin@gmail.com>
2019-01-15 00:50 ` Paul Carlucci <paul.carlucci@gmail.com>
2019-01-15 01:41 ` David G. Johnston <david.g.johnston@gmail.com>
2019-01-15 01:46 ` Tom Lane <tgl@sss.pgh.pa.us>
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