Robert Treat wrote:
On Fri, 2005-09-23 at 14:53, Dawid Kuroczko wrote:
On 9/23/05, Yonatan Ben-Nes < da@xxxxxxxxxxxx <mailto:da@xxxxxxxxxxxx> >
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).
<snip>
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;
yeah, i was thinking
create newtable;
~~ load data into newtable
begin;
drop oldtable;
alter table newtable rename to oldtable
commit;
this seperates the data loading piece from the piece where you promote
the data to live data, plus then the time you have to hold the
transaction open is only for the drop and rename, which will be quite
fast.
the only potential issues would be making sure you dont have FK/View
type issues, but it doesn't sound like it would apply here.
Robert Treat
Sorry everyone for not responding... I just didnt know that the
discussion continued :)
Anyway I saw the idea:
BEGIN;
CREATE new_table;
SELECT INTO new_table * FROM temp_table;
DROP TABLE table;
ALTER TABLE new_table RENAME TO table;
COMMIT;
Where if I understood correctly "table" is the final table, "temp_table"
is the table that receive all the proccess and at the end of it got
10mil delete tuples and 5mil active and finally "new_table" is the
receipent of all of the active tuples from "temp_table".
Its looking quite promising to me but I did alittle check and saw that
between the drop table command & the commit I get a lock on the table
(obvious but problematic to a 24/7 site) so im wondering to myself how
much time such a transaction will take from the drop command point?
If users wont be able to access the table for some extremly small amount
of time (less then a second obviously) then though I dont like it much
it is better then running a vacuum full which will slow all my server
for a considerable amount of time...
So anyone know how much time does such a process take? (tried to explain
analyze it with no success :)).
Thanks alot everyone!
Ben-Nes Yonatan
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
