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

Поиск
Список
Период
Сортировка

Delete Enhancement Request

От
"Campbell, Lance"
Дата:

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

Re: Delete Enhancement Request

От
Ron
Дата:
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.

Re: Delete Enhancement Request

От
Paul Carlucci
Дата:
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

Re: Delete Enhancement Request

От
"David G. Johnston"
Дата:
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.


Re: Delete Enhancement Request

От
Tom Lane
Дата:
"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


Re: Delete Enhancement Request

От
Sridhara KB
Дата:
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.

Re: Delete Enhancement Request

От
Shreeyansh Dba
Дата:
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.

Re: Delete Enhancement Request

От
Ron
Дата:
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.

Re: Delete Enhancement Request

От
Ervin Weber
Дата:
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.