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

Поиск
Список
Период
Сортировка
От btober@computer.org
Тема Re: [GENERAL] efficiently migrating 'old' data from one table toanother
Дата
Msg-id 1464496899.139156708.1484252359001.JavaMail.zimbra@broadstripe.net
обсуждение исходный текст
Ответ на [GENERAL] efficiently migrating 'old' data from one table to another  (Jonathan Vanasco <postgres@2xlp.com>)
Ответы Re: [GENERAL] efficiently migrating 'old' data from one table to another  (Merlin Moncure <mmoncure@gmail.com>)
Список pgsql-general

----- Original Message -----
> From: "Jonathan Vanasco" <postgres@2xlp.com>
> To: "pgsql-general general" <pgsql-general@postgresql.org>
> Sent: Thursday, January 12, 2017 3:06:14 PM
> Subject: [GENERAL] efficiently migrating 'old' data from one table to another
>
> I'm just wondering if there's a more efficient way of handling a certain
> periodic data migration.
>
> We have a pair of tables with this structure:
>
>     table_a__live
>         column_1 INT
>         column_2 INT
>         record_timestamp TIMESTAMP
>
>     table_a__archive
>         column_1 INT
>         column_2 INT
>         record_timestamp TIMESTAMP
>
> periodically, we must migrate items that are 'stale' from `table_a__live ` to
> `table_a__archive`.  The entries are copied over to the archive, then
> deleted.
>
> The staleness is calculated based on age--  so we need to use INTERVAL.  the
> "live" table can have anywhere from 100k to 20MM records.
>
> the primary key on `table_a__live` is a composite of column_1 & column_2,
>
> In order to minimize scanning the table, we opted to hint migrations with a
> dedicated column:
>
>     ALTER TABLE table_a__live ADD is_migrate BOOLEAN DEFAULT NULL;
>     CREATE INDEX idx_table_a__live_migrate ON table_a__live(is_migrate) WHERE
>     is_migrate IS NOT NULL;
>
> so our migration is then based on that `is_migrate` column:
>
>     BEGIN;
>     UPDATE table_a__live SET is_migrate = TRUE WHERE record_timestamp <
>     transaction_timestamp() AT TIME ZONE 'UTC' - INTERVAL '1 month';
>     INSERT INTO table_a__archive (column_1, column_2, record_timestamp) SELECT
>     column_1, column_2, record_timestamp FROM table_a__live WHERE is_migrate IS
>     TRUE;
>     DELETE FROM table_a__live WHERE is_migrate IS TRUE;
>     COMMIT;
>
> The inserts & deletes are blazing fast, but the UPDATE is a bit slow from
> postgres re-writing all the rows.
>
> can anyone suggest a better approach?
>
> I considered copying everything to a tmp table then inserting/deleting based
> on that table -- but there's a lot of disk-io on that approach too.


Review manual section 7.8.2. Data-Modifying Statements in WITH


https://www.postgresql.org/docs/9.6/static/queries-with.html


-- B


В списке pgsql-general по дате отправления:

Предыдущее
От: Jonathan Vanasco
Дата:
Сообщение: [GENERAL] efficiently migrating 'old' data from one table to another
Следующее
От: John R Pierce
Дата:
Сообщение: Re: [GENERAL] efficiently migrating 'old' data from one table toanother