Обсуждение: Delete Enhancement Request

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.

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.

"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

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.

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.