CODEWITHSUNDEEP
postgresqltruncatevacuum
Pawel P
Pawel P

Reputation: 131

Move truncated records to another table in Postgresql 9.5

Problem is following: remove all records from one table, and insert them to another.

I have a table that is partitioned by date criteria. To avoid partitioning each record one by one, I'm collecting the data in one table, and periodically move them to another table. Copied records have to be removed from first table. I'm using DELETE query with RETURNING, but the side effect is that autovacuum is having a lot of work to do to clean up the mess from original table.

I'm trying to achieve the same effect (copy and remove records), but without creating additional work for vacuum mechanism.

As I'm removing all rows (by delete without where conditions), I was thinking about TRUNCATE, but it does not support RETURNING clause. Another idea was to somehow configure the table, to automatically remove tuple from page on delete operation, without waiting for vacuum, but I did not found if it is possible.

Can you suggest something, that I could use to solve my problem?

Upvotes: 0

Views: 305

Answers (1)

Michel Milezzi
Michel Milezzi

Reputation: 11135

You need to use something like:

--Open your transaction

BEGIN;

--Prevent concurrent writes, but allow concurrent data access

LOCK TABLE table_a IN SHARE MODE;

--Copy the data from table_a to table_b, you can also use CREATE TABLE AS to do this

INSERT INTO table_b AS SELECT * FROM table_a;

--Zeroying table_a

TRUNCATE TABLE table_a;

--Commits and release the lock

COMMIT;

Upvotes: 1

Related Questions