On 9/23/05,
Yonatan Ben-Nes <
da@canaan.co.il> wrote:
Hi all,
Every few days I need to DELETE all of the content of few tables and
INSERT new data in them.
The amount of new data is about 5 million rows and each row get about 3
queries (INSERT + UPDATE).
If I understand you well, in transaction you do:
DELETE FROM table; -- remove all columns, you have a table with 5m dead tuples
then you:
INSERT into the table, -- you have 5m dead tuples + 5m new tuples
then you:
UPDATE what you've inserted -- you have 10m dead tuples + 5m new tuples
and then you:
UPDATE once again what you've inserted -- you have 15m dead tuples + 5 new tuples
and then COMMIT;
Alternatively you want to do:
CREATE TEMPORARY temp_table and UPDATE it as you please
BEGIN;
DELETE FROM table;
SELECT INTO table * FROM temp_table;
COMMIT; -- which will leave you with 5m dead and 5m live.
Or you could even try (haven't tested it):
BEGIN;
CREATE new_table;
SELECT INTO new_table * FROM temp_table;
DROP TABLE table;
ALTER TABLE new_table RENAME TO table;
COMMIT; -- leaving you with fresh 5mln new tuples table
...with a risk of loosing all the changes made to old table after BEGIN;
Regards,
Dawid