Re: [GENERAL] efficiently migrating 'old' data from one table to another

On Jan 12, 2017, at 5:52 PM, Merlin Moncure wrote:

> On Thu, Jan 12, 2017 at 2:19 PM, btober@computer.org
> <btober@broadstripe.net> wrote:
>>
>> Review manual section 7.8.2. Data-Modifying Statements in WITH
>>
>>
>> https://www.postgresql.org/docs/9.6/static/queries-with.html
>
> this.
>
> with data as (delete from foo where ... returning * ) insert into
> foo_backup select * from data;

Thanks, btober and merlin.  that's exactly what i want.


On Jan 12, 2017, at 4:45 PM, Adrian Klaver wrote:
> Maybe I am missing something, but why do the UPDATE?
> Why not?:
> ...
> With an index on record_timestamp.

That's actually the production deployment that we're trying to optimize.  Depending on the size of the table (rows,
width)it performs "less than great", even with the index on record_timestamp. 

The UPDATE actually worked faster in most situations.  I honestly don't know why (the only thing that makes sense to me
isserver-load)... but the update + bool test ended up being (much) faster than the timestamp comparison.