On Thu, Jan 12, 2017 at 2:19 PM, btober@xxxxxxxxxxxx <btober@xxxxxxxxxxxxxxx> wrote: > > > ----- Original Message ----- >> From: "Jonathan Vanasco" <postgres@xxxxxxxx> >> To: "pgsql-general general" <pgsql-general@xxxxxxxxxxxxxx> >> Sent: Thursday, January 12, 2017 3:06:14 PM >> Subject: 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 this. with data as (delete from foo where ... returning * ) insert into foo_backup select * from data; -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
