Re: [GENERAL] Deletion Challenge

Jim Nasby wrote: On 12/9/15 7:59 PM, Berend Tober wrote: This project is a game, btw, described at You might be interested in https://schemaverse.com/ Schemaverse looks somewhat interesting. Seems like it and Fairwinds share in common Postgresql as a foundation, but they are very

Re: [GENERAL] Deletion Challenge

On Saturday, December 05, 2015 11:08:05 AM Berend Tober wrote: > WITH max_click AS ( >SELECT > cash_journal.fairian_id, > max(cash_journal.click) AS click > FROM cash_journal > GROUP BY cash_journal.fairian_id > ) >delete from cash_journal j > using max_click

Re: [GENERAL] Deletion Challenge

> test=> delete from cash_journal where ARRAY[click, cash_journal_id] NOT in > (select max(ARRAY[click,cash_journal_id]) from cash_journal group by > fairian_id); DELETE 7 For what it's worth, we've run into *severe* performance issues using in() if there are a large number of values in

Re: [GENERAL] Deletion Challenge

On 12/9/15 7:59 PM, Berend Tober wrote: The issue is that I'd like the application (that is, the data base and its stored procedures) to be robust enough to be a "long-running" application, i.e. one that doesn't suffer gradual performance degradation as time and the accumulated data increase.

Re: [GENERAL] Deletion Challenge

Steve Crawford wrote: The two general solutions are the "keep the last one" proposed by Adrian "keep the last N" that I sent. But it might be worth stepping back a bit. You said you are having performance problems that you feel would be improved by removing only a million rows which doesn't

Re: [GENERAL] Deletion Challenge

Steve Crawford wrote: If I understand correctly the value of "click" always advances and within a "click" the "cash_journal_id" always advances - not necessarily by single steps so within a fairian_id, ordering by "click" plus "cash_journal_id" would return the records in order from which you

Re: [GENERAL] Deletion Challenge

Adrian Klaver wrote: On 12/05/2015 08:08 AM, Berend Tober wrote: /* Deletion Challenge I want to delete all but the most recent transaction, per person, from a table that records a transaction history because at some point the transaction history grows large enough to adversely effect

Re: [GENERAL] Deletion Challenge

On Wed, Dec 9, 2015 at 1:31 PM, Adrian Klaver wrote: > On 12/09/2015 12:24 AM, Berend Tober wrote: > >> Adrian Klaver wrote: >> >>> On 12/05/2015 08:08 AM, Berend Tober wrote: >>> /* Deletion Challenge I want to delete all but the most recent

Re: [GENERAL] Deletion Challenge

The two general solutions are the "keep the last one" proposed by Adrian "keep the last N" that I sent. But it might be worth stepping back a bit. You said you are having performance problems that you feel would be improved by removing only a million rows which doesn't sound like that much to me.

Re: [GENERAL] Deletion Challenge

On 12/09/2015 12:24 AM, Berend Tober wrote: Adrian Klaver wrote: On 12/05/2015 08:08 AM, Berend Tober wrote: /* Deletion Challenge I want to delete all but the most recent transaction, per person, from a table that records a transaction history because at some point the transaction history

[GENERAL] Deletion Challenge

/* Deletion Challenge I want to delete all but the most recent transaction, per person, from a table that records a transaction history because at some point the transaction history grows large enough to adversely effect performance, and also becomes less relevant for retention. I have devised

Re: [GENERAL] Deletion Challenge

If I understand correctly the value of "click" always advances and within a "click" the "cash_journal_id" always advances - not necessarily by single steps so within a fairian_id, ordering by "click" plus "cash_journal_id" would return the records in order from which you want the most recent 5 for

Re: [GENERAL] Deletion Challenge

On 12/05/2015 08:08 AM, Berend Tober wrote: > /* > > Deletion Challenge > > I want to delete all but the most recent transaction, per person, from a > table that records a transaction history because at some point the > transaction history grows large enough to adversely effect performance, >